MySQL日志功能详解
一.查询日志
它是用来保存所有跟查询相关的日志，这种日志类型默认是关闭状态的，因为MySQL的用户有很多，如果将每个用户的查询操作都记录下来的话，对服务器的资源开销也是一件令人烦恼的事情。查询日志常见的几个参数：
mysql> show global variables like 'log%'; #查看是否记录所有语句的日志信息于一般查询日志文件（general_log），默认是关闭状态 +----------------------------------------+---------------------+ | Variable_name | Value | +----------------------------------------+---------------------+ | log_bin | OFF | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | log_error | /var/log/mysqld.log | | log_output | FILE | #它有三个值，即{TABLE|FILE|NONE}，分别表示记录在表中（table），文件（file）中或是不记录（none）。注意，只有og_output的值等于 FILE时，general_log_file的参数才会有意义。且 table和file 可以同时出现，用逗号分隔即可 | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | log_throttle_queries_not_using_indexes | 0 | | log_warnings | 1 | +----------------------------------------+---------------------+ 13 rows in set (0.01 sec) mysql> show global variables like 'general_log' #查看是否启用查询日志功能 -> ; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> show global variables like 'general_log'; #定义了一般查询日志保存的文件 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> show global variables like 'general_log_file'; +------------------+----------------------------+ | Variable_name | Value | +------------------+----------------------------+ | general_log_file | /var/lib/mysql/iso-all.log | +------------------+----------------------------+ 1 row in set (0.00 sec)
mysql> set global log_output='table'; Query OK, 0 rows affected (0.00 sec) mysql> set global general_log='on'; Query OK, 0 rows affected (0.00 sec) mysql> show tables from mysql; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 28 rows in set (0.00 sec) mysql> select * from mysql.general_log; +---------------------+---------------------------+-----------+-----------+--------------+---------------------------------+ | event_time | user_host | thread_id | server_id | command_type | argument | +---------------------+---------------------------+-----------+-----------+--------------+---------------------------------+ | 2020-11-21 10:59:01 | root[root] @ localhost [] | 45 | 0 | Query | select * from mysql.general_log | +---------------------+---------------------------+-----------+-----------+--------------+---------------------------------+ 1 row in set (0.01 sec)
三.错误日志
顾名思义，这是用来记录错误的日志，但是不仅仅是记录错误信息，还包括MySQL启动，关闭，复制线程（指的是从服务器）的信息哟。错误日志默认是开启的。它主要记录的信息如下：
1>.服务器启动和关闭过程中的信息；
2>.服务器运行过程中的错误信息
3>.事件调度器运行一个事件时产生的信息
4>.在复制架构中的从服务器上启动从服务器线程时产生的信息
常见参数如下：
mysql> show global variables like 'log_error'; #指定错误日志文件位置 +---------------+---------------------+ | Variable_name | Value | +---------------+---------------------+ | log_error | /var/log/mysqld.log | +---------------+---------------------+ 1 row in set (0.00 sec) mysql> show global variables like 'log_warnings'; #是否将经过日志也记录在错误日志文件中去 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_warnings | 1 | +---------------+-------+ 1 row in set (0.00 sec)
四.二进制日志
1>.什么是二进制文件
只记录修改相关的操作，记录了当前服务器的数据修改和有潜在可能性影响数据修改的语句。它用来实现复制的基本凭据。也就是说，你可以将生成环境中的MySQL的二进制文件拿到线下的服务器上运行一下，理论上你会拿到和生成环境中一样的数据哟，因此，二进制日志也叫复制日志。二进制日志文件默认在数据目录下，通常情况下为mysql-bin#（例如：mysql-bin.000001，mysql-bin000002）
2>.开启二进制日志
[root@iso-all ~]# cat /etc/my.cnf|grep -v ^# [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES log-bin=mysql-bin # 新添加放[mysql]下 binlog_format=mixed #新添加放[mysql]下 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [root@iso-all ~]# systemctl stop mysql [root@iso-all ~]# systemctl start mysql
3>.查看MySQL日志文件
由于二进制文件格式是二进制类型的，我们不能用cat等查看普通文本类命令去查看这些二进制日志，我们可以通过mysqlbinlog来查看。注意“show master status; ”查看当前使用的二进制日志和下一个事件开始时的基于的位置。
mysql> show binlog events\G #查看mysql的日志 *************************** 1. row *************************** Log_name: mysql-bin.000001 Pos: 4 Event_type: Format_desc #事件类型 Server_id: 1 #指定服务器的唯一标识 End_log_pos: 120 Info: Server ver: 5.6.50-log, Binlog ver: 4 1 row in set (0.00 sec)
4>.日志滚动
为了避免一个文件过大，我们可以适当的将文件的内容分开存储，这就是日志滚动，比如：当超过1G，日志会滚动。当然，你也可以按照文件大小自定义， 时间定义。想要手动滚动日志，执行“flush logs;”即可。
5>.查看当前正在使用的log日志
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 120 | +------------------+-----------+ 1 row in set (0.00 sec)
6>.二进制的主要两个功能是：
时间点恢复;(它的功能不亚于事务日志！)
复制;
7>.清除日志
不要手动去删除，而是用专业的工具去干这件事情，即purge工具
mysql> help PURGE Many help items for your request exist. To make a more specific request, please type 'help <item>', where <item> is one of the following topics: PURGE BINARY LOGS PURGE MASTER LOGS
8>.Mysql 记录二进制日志的格式
基于语句（statement）： 只把语句服务器执行的SQL语句记录下来，但是可能存在不精准的情况。例如：“INSERT INTO t1 value(current_date())”，明锐的你可能发现有"current_date()"这个参数，如果从服务器执行了想用的语句，如果存在网络延迟的情况，就会导致主从同步存在误差！ 基于行(row)： 可以更精确的记录数据，但是会面临记录的数据量过大的情况，可能一个语句的操作，匹配了1w多行，那么这1w多行数据的修改都会记录在这个二进制文件中去的。 混合模式（mixed）： 既有了statement的模型，也用了mixed的特点。这种模式据反映不是很好，建议还是用基于行的模式，因为它能够保证数据更加精确，换来更加精确的同时，可能对你的存储空间和I/O的使用率会有所提高哟。 复制代码
9>.指定从那个位置开始读取
mysqlbinlog的常用的[options]： a>--start-time #起始时间 b>.--stop-time #结束时间 c>.--start-position #基于起始位置来显示信息 d>.--stop-position #指定结束位置来显示 命令行查询方式如下（可以将读取的内容保存下来，在另一台服务器上可以情景再现）： [root@iso-all ~]# mysqlbinlog --start-position=120 mysql-bin.000001 >/yanhuihuang/backup.sql SQL命令查询方法如下： MariaDB [(none)]> show binlog events in 'mysql-bin.000001' from 120\G
10>.二进制日志文件内容格式
事件发生的日期和时间（会在关键字“at”）
服务器ID（server id）
事件结束位置（end_log_pos）
事件的类型（如：Query，Stop等等）
原服务器生成此事件时的线程ID号（thead_id，可以通过“show processlist;”进行查询）
语句时间戳和写入二进制文件的时间差，单位为秒（exec_time，表示记录日志所用的时间戳，当他等于0时表示没有用到1秒钟。）
错误代码，0表示正常执行（error_code,排查方法就得查看官方文档。）
事件内容（修改的SQL语句）
事件位置（相当于下一事件的开始位置，还是用“at”关键字标志）
11>.二进制日志文件常用的相关参数详解
mysql> show global variables like 'log_bin'; #查看是否开启二进制日志功能，当然我们可以在MySQL的配置文件（my.cnf）中指定文件路径（如：log_bin=/yinzhengjie/log/mysql-bin）； +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> show global variables like 'log_bin_trust_function_creators'; #不阻止任何存储函数，存在一定风险，默认关闭即可； +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin_trust_function_creators | OFF | +---------------------------------+-------+ 1 row in set (0.00 sec) mysql> show global variables like 'sql_log_bin'; #当前会话是否将二进制文件进入进二进制文件，默认为ON； +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> show global variables like 'sql_log_off'; #是否将一般查询日志记入查询日志 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_off | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> show global variables like 'sync_binlog'; #同步缓冲中的二进制到硬盘的时间，0不基于时间同步，只在事件提交时同步 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sync_binlog | 0 | +---------------+-------+ 1 row in set (0.00 sec) mysql> show global variables like 'binlog_format'; #指定记录二进制日志的格式 有三种格式：基于语句（statement），基于行（row），混合模式（mixed） +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | MIXED | +---------------+-------+ 1 row in set (0.01 sec) mysql> show global variables like 'max_binlog_cache_size'; #mysql二进制日志的缓冲区大小，仅用于缓存事务类的语句 +-----------------------+----------------------+ | Variable_name | Value | +-----------------------+----------------------+ | max_binlog_cache_size | 18446744073709547520 | +-----------------------+----------------------+ 1 row in set (0.00 sec) mysql> show global variables like 'max_binlog_size'; #二进制日志文件的上限，单位为字节 +-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | max_binlog_size | 1073741824 | +-----------------+------------+ 1 row in set (0.00 sec)
MySQL的很多默认设置并不适合生成环境，我们需要调整很多东西。给出两点提示：
a>.切勿将二进制日志与数据文件放在同一设备；
b>.可以临时通过sql_log_bin来控制二进制的写入；
五.中继日志
从服务器上的二进制日志。说白了中继日志其实就是从主服务器上的二进制日志中取数据，然后写入中级之日里面，在从服务器上，执行中继日志的sql信息，这样从服务器就会得到和主服务器一样的内容，与此同时每次执行之后从服务器的二进制日志也会记录，聪明的你可能也会想到，这个从服务器的二进制日志内容应该是和主服务器是一致的，所以我们通常采取的操作就是将从服务器的二进制日志关闭掉。 对于非从服务器的中继日志并没有启用，可能会用到以下两个参数： relay_log_purge = {ON|OFF} # 是否自动清理不在需要的中继日志 relay_log_space_limit #中继（空间）大小是否限制
六.事务日志.
先暂存事物提交的数据而后在同步到数据文件中去的一种日志。它的主要目的是将随机I/O转换为顺序I/O并保证事物的兼容性的。（顺序I/O是早期提升写入的速度一个不错的解决方案，它比随机I/O的性能可能会高出100倍呢！不过后来固态硬盘的出现顺序I/O起到的作用就不是很明显。）
事务日志我们也称之为日志文件组，它至少要存在两个日志文件以实现轮询。我们知道，MySQL的innodb是支持事物的，当启动一个事务时，修改的数据是存储在innodb的缓存（innodb_buffer）中的,当这个缓存存储不下之后，它就会将数据写入到日志文件组（事务日志）中的一个文件，当其中的一个文件写满之后，又开始写第二个文件，与此同时，第一个文件的内容开始网磁盘中写，已达事务持久化的特性之一。这也就意味着当事务回滚时，很可能会将已经写入磁盘中的数据进行删除操作，这样性能就会降低，而如果事务较小的话，也就不会存在将数据写入到磁盘中，甚至不用写入事务日志中，直接在innodb的缓存中就将问题解决，因此，尽可能使用小事务来替代大事务来提升事务引擎的性能。
当存储事务日志的磁盘坏掉是，数据是无法恢复的哟！因此选择一个可靠的磁盘还是相当有必要的，比如我们可以给我们的数据做raid10或者raid1（推荐使用raid10）来提供这种保障。事务日志不能帮助我们恢复数据，它的作用在于当操作系统崩溃时（比如异常断电）它能够保障已经提交的事物不丢失，而未提交的事物能回滚。如果想要恢复日志还得依赖于二进制日志。
