首页 > 代码库 > MySQL 日志

MySQL 日志

MySQL 日志

================================================================================

概述:


================================================================================

MySQL的日志分类:

查询日志:general_log

慢查询日志:log_slow_queries

错误日志:log_error, log_warnings

二进制日志:binlog

中继日志:relay_log

事务日志:innodb_log

日志相关的参数:

MariaDB [(none)]> SHOW VARIABLES LIKE ‘%log%‘;
+-------------------------------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name                             | Value                                                                                                        |
+-------------------------------------------+--------------------------------------------------------------------------------------------------------------+
| aria_checkpoint_log_activity              | 1048576                                                                                                      |
| aria_log_file_size                        | 1073741824                                                                                                   |
| aria_log_purge_type                       | immediate                                                                                                    |
| aria_sync_log_dir                         | NEWFILE                                                                                                      |
| back_log                                  | 50                                                                                                           |
| binlog_annotate_row_events                | OFF                                                                                                          |
| binlog_cache_size                         | 32768                                                                                                        |
| binlog_checksum                           | NONE                                                                                                         |
| binlog_direct_non_transactional_updates   | OFF                                                                                                          |
| binlog_format                             | STATEMENT                                                                                                    |
| binlog_optimize_thread_scheduling         | ON                                                                                                           |
| binlog_stmt_cache_size                    | 32768                                                                                                        |
| expire_logs_days                          | 0                                                                                                            |
| general_log                               | OFF                                                                                                          |
| general_log_file                          | centos7.log                                                                                                  |
| innodb_flush_log_at_trx_commit            | 1                                                                                                            |
| innodb_locks_unsafe_for_binlog            | OFF                                                                                                          |
| innodb_log_block_size                     | 512                                                                                                          |
| innodb_log_buffer_size                    | 8388608                                                                                                      |
| innodb_log_file_size                      | 5242880                                                                                                      |
| innodb_log_files_in_group                 | 2                                                                                                            |
| innodb_log_group_home_dir                 | ./                                                                                                           |
| innodb_mirrored_log_groups                | 1                                                                                                            |
| innodb_recovery_update_relay_log          | OFF                                                                                                          |
| innodb_use_global_flush_log_at_trx_commit | ON                                                                                                           |
| log                                       | OFF                                                                                                          |
| log_bin                                   | OFF                                                                                                          |
| log_bin_trust_function_creators           | OFF                                                                                                          |
| log_error                                 | /var/log/mariadb/mariadb.log                                                                                 |
| log_output                                | FILE                                                                                                         |
| log_queries_not_using_indexes             | OFF                                                                                                          |
| log_slave_updates                         | OFF                                                                                                          |
| log_slow_filter                           | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_queries                          | OFF                                                                                                          |
| log_slow_rate_limit                       | 1                                                                                                            |
| log_slow_verbosity                        |                                                                                                              |
| log_warnings                              | 1                                                                                                            |
| max_binlog_cache_size                     | 18446744073709547520                                                                                         |
| max_binlog_size                           | 1073741824                                                                                                   |
| max_binlog_stmt_cache_size                | 18446744073709547520                                                                                         |
| max_relay_log_size                        | 0                                                                                                            |
| relay_log                                 |                                                                                                              |
| relay_log_index                           |                                                                                                              |
| relay_log_info_file                       | relay-log.info                                                                                               |
| relay_log_purge                           | ON                                                                                                           |
| relay_log_recovery                        | OFF                                                                                                          |
| relay_log_space_limit                     | 0                                                                                                            |
| slow_query_log                            | OFF                                                                                                          |
| slow_query_log_file                       | centos7-slow.log                                                                                             |
| sql_log_bin                               | ON                                                                                                           |
| sql_log_off                               | OFF                                                                                                          |
| sync_binlog                               | 0                                                                                                            |
| sync_relay_log                            | 0                                                                                                            |
| sync_relay_log_info                       | 0                                                                                                            |
+-------------------------------------------+--------------------------------------------------------------------------------------------------------------+
54 rows in set (0.00 sec)

查询日志:general_log

作用:

  • 记录查询语句;

日志存储位置:

  • 文件:file

      ·general_log_file=HOSTNAME.log (主机名加log的文件)

  • 表:table

      ·mysql.general_log

相关参数:

  • general_log={ON|OFF} 默认是OFF

定义输出位置

  • log_output={FILE|TABLE|NONE}

注意:

  • 想要永久有效,要写到配置文件当中,但实际工作中,没有特殊要求,一般不建议开启,因为数据库查询非常多,开启会对I/O造成严重的影响。

演示:

 1.开启查询日志,设定输出位置为table和file;

MariaDB [hellodb]> SET GLOBAL  general_log = ON;    # 开启查询日志
MariaDB [mysql]> SET GLOBAL log_output = ‘TABLE,FILE‘; # 设定输出位置为TABLE和FILE

MariaDB [mysql]> SHOW VARIABLES LIKE ‘log_output‘;    # 查看输出位置
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| log_output    | FILE,TABLE |
+---------------+------------+
1 row in set (0.01 sec)

MariaDB [mysql]> SELECT @@log_output;
+--------------+
| @@log_output |
+--------------+
| FILE,TABLE   |
+--------------+
1 row in set (0.01 sec)

 2.执行查询语句,并查看表和文件是否记录相关的数据

MariaDB [hellodb]> SELECT * FROM mysql.general_log;
+----------------------------+---------------------------+-----------+-----------+--------------+----------------------------------+
| event_time                 | user_host                 | thread_id | server_id | command_type | argument                         |
+----------------------------+---------------------------+-----------+-----------+--------------+----------------------------------+
| 2016-12-01 10:45:23.715603 | root[root] @ localhost [] |         3 |         0 | Query        | SELECT * FROM mysql.general_log  |
| 2016-12-01 10:45:28.195891 | root[root] @ localhost [] |         3 |         0 | Query        | select * from students           |
| 2016-12-01 10:45:36.486132 | root[root] @ localhost [] |         3 |         0 | Query        | SELECT @@log_output              |
| 2016-12-01 10:45:44.909488 | root[root] @ localhost [] |         3 |         0 | Query        | SELECT * FROM mysql.general_log  |
| 2016-12-01 10:46:11.410438 | root[root] @ localhost [] |         3 |         0 | Query        | SHOW VARIABLES LIKE ‘log_output‘ |
| 2016-12-01 10:46:20.335378 | root[root] @ localhost [] |         3 |         0 | Query        | SELECT * FROM mysql.general_log  |
+----------------------------+---------------------------+-----------+-----------+--------------+----------------------------------+
6 rows in set (0.00 sec)
# 在数据库目录中可以看到centos.log的文件,就是查询日志的记录文件
[root@centos7 ~]# cd /var/lib/mysql/
[root@centos7 mysql]# ls
aria_log.00000001  centos7.log  ibdata1      ib_logfile1  mysql       performance_schema  test    ultrax
aria_log_control   hellodb      ib_logfile0  mydb         mysql.sock  Syslog              testdb  zabbix

# 查看此文件,可以发现我们刚刚执行过的查询语句
[root@centos7 mysql]# cat centos7.log 
/usr/libexec/mysqld, Version: 5.5.44-MariaDB (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
161201 10:45:23	    3 Query	SELECT * FROM mysql.general_log
161201 10:45:28	    3 Query	select * from students
161201 10:45:36	    3 Query	SELECT @@log_output
161201 10:45:44	    3 Query	SELECT * FROM mysql.general_log
161201 10:46:11	    3 Query	SHOW VARIABLES LIKE ‘log_output‘
161201 10:46:20	    3 Query	SELECT * FROM mysql.general_log

慢查询日志:log_slow_queries

慢查询作用

  • 运行时间超出指定时长的查询;

指定时长参数:

  • long_query_time

存储位置:

  • 文件:FILE

  • 表:TABLE,mysql.slog_log

相关参数

  • log_slow_queries={ON|OFF}   //启用或关闭慢查询

  • slow_query_log={ON|OFF}

  • slow_query_log_file=HOSTNAME.log  //慢查询文件记录位置

  • log_output={FILE|TABLE|NONE}      //定义查询输出位置

  • log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk  

        //这些慢查询语句会被过滤掉,是不被记录的

  • log_slow_rate_limit   //慢查询日志的速率限制

  • log_slow_verbosity    //是否记录详细格式信息

演示:

  1.查看慢查询日志的时长相关参数,并设定时长

MariaDB [hellodb]> SELECT @@long_query_time;  # 查看慢查询时长
+-------------------+
| @@long_query_time |
+-------------------+
|         10.000000 |
+-------------------+
1 row in set (0.01 sec)

MariaDB [hellodb]> SET @@long_query_time =1;  # 设定当前会话时长为1s
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SELECT @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
|          1.000000 |
+-------------------+
1 row in set (0.00 sec)

 2.查看慢查询日志的相关参数,并开启慢查询日志

# 查看当前会话中的慢查询相关参数,可以发现log_slow_queries 和slow_query_log  均为OFF
MariaDB [hellodb]> SHOW VARIABLES LIKE ‘log_slow%‘;
+---------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name       | Value                                                                                                        |
+---------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_filter     | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_queries    | OFF                                                                                                          |
| log_slow_rate_limit | 1                                                                                                            |
| log_slow_verbosity  |                                                                                                              |
+---------------------+--------------------------------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)

MariaDB [hellodb]> SHOW VARIABLES LIKE ‘slow%‘;
+---------------------+------------------+
| Variable_name       | Value            |
+---------------------+------------------+
| slow_launch_time    | 2                |
| slow_query_log      | OFF              |
| slow_query_log_file | centos7-slow.log | # 慢查询日志文件的记录位置
+---------------------+------------------+
3 rows in set (0.01 sec)

#开启慢查询日志
MariaDB [hellodb]>  SET @@GLOBAL.log_slow_queries=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [hellodb]>  SET @@GLOBAL.slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)

错误日志:log_error, log_warnings

记录信息:

  • mysqld启动和关闭过程 输出的信息;

  • mysqld运行中产生的错误信息; 

  • event scheduler运行时产生的信息;

  • 主从复制架构中,从服务器复制线程启动时产生的日志;

参数:

  • log_error   //给定文件直接记录,永久生效要写在配置文件中,默认是启动的

  • log_warnings={ON|OFF}  //警告信息是否记录在错误日志中

演示:

 1.开启错误日志,及错误日志文件的记录位置

MariaDB [(none)]> SHOW VARIABLES LIKE ‘log_warnings‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings  | 1     |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE ‘log_error‘;   # 默认就已经启动了
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| log_error     | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
1 row in set (0.01 sec)

二进制日志:binlog

作用:

  • 用于记录引起数据改变或存在引起数据改变的潜在可能性的语句(STATEMENT)或改变后的结果(ROW),也可能是二者混合;

功用:“重放”

记录方式:

binlog_format={STATEMENT|ROW|MIXED}

  • STATEMENT:语句;

  • ROW:行;

  • MIXED:混编;

相关命令

查看二进制日志文件列表:

  • SHOW MASTER|BINARY LOGS;

查看当前正在使用的二进制日志文件:

  • SHOW MASTER STATUS;

查看二进制日志文件中的事件:

  • SHOW BINLOG EVENTS [IN ‘log_name‘] [FROM pos] [LIMIT [offset,] row_count]

服务器变量:

  • log_bin=/PATH/TO/BIN_LOG_FILE

        //指明记录二进制文件的路径,为了数据安全,建议放在不同的存储设备中

  • sql_log_bin={ON|OFF}        

        //指明是否记录日志于二进制文件中

  • max_binlog_size=1073741824

        //二进制日志文件大小的上限值,超过会自动滚动

  • sync_binlog={1|0}

        //一旦有事件提交,就立刻从内存中同步到文件中去,建议启用

mysqlbinlog:客户端命令

技术分享


二进制日志事件格式:

技术分享

演示:

  1.查看二进制日志的当前相关参数:

# 可以发现记录二进制文件的路径log_bin为OFF
MariaDB [hellodb]> SHOW VARIABLES LIKE ‘log_bin‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

# 是否记录二进制文件的参数sql_log_bin为ON,表示开启
MariaDB [hellodb]> SHOW VARIABLES LIKE ‘sql_log_bin‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.00 sec)

 2.编辑mysql的配置文件/etc.my.cnf,添加启动记录二进制文件路径的参数,然后重启mysql服务器

技术分享

 3.再次查看二进制日志文件的参数,可以发现都为ON

MariaDB [hellodb]> SHOW VARIABLES LIKE ‘log_bin‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.02 sec)

MariaDB [hellodb]> SHOW VARIABLES LIKE ‘sql_log_bin‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.00 sec)

#====================================================================================

 4.查看二进制文件列表,及产生的二进制文件的个数

# 显示二进制文件列表,二进制文件是滚动的,一旦重启服务器或者执行FLIUSH LOGS都会不断向上累加新的日志文件

MariaDB [hellodb]> SHOW BINARY LOGS; 
+-------------------+-----------+  
| Log_name          | File_size |
+-------------------+-----------+
| master-log.000001 |       245 |
+-------------------+-----------+
1 row in set (0.01 sec)

MariaDB [hellodb]> FLUSH LOGS;
Query OK, 0 rows affected (0.02 sec)

MariaDB [hellodb]> SHOW BINARY LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-log.000001 |      1462 |
| master-log.000002 |      1964 |
+-------------------+-----------+
2 rows in set (0.00 sec)
# 在数据库目录中可以查看相应的二进制文件
[root@centos7 ~]# cd /var/lib/mysql/
[root@centos7 mysql]# ls
aria_log.00000001  centos7.log       hellodb  ib_logfile0  master-log.000001  master-log.index  mysql       performance_schema  test    ultrax
aria_log_control   centos7-slow.log  ibdata1  ib_logfile1  master-log.000002  mydb              mysql.sock  Syslog              testdb  zabbix

# 记录产生的二进制文件个数
[root@centos7 mysql]# cat master-log.index  
./master-log.000001
./master-log.000002

 5.测试二进制文件记录的事件内容

MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taijiquan      |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|       12 | Nagios         |
|       13 | Puppet         |
+----------+----------------+
9 rows in set (0.00 sec)

MariaDB [hellodb]> INSERT INTO courses (Course) VALUES (‘Zabbix‘);
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> SHOW BINARY LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-log.000001 |      1462 |
| master-log.000002 |      3375 |
+-------------------+-----------+
2 rows in set (0.00 sec)

MariaDB [hellodb]> DELETE FROM courses WHERE CourseID=13;
Query OK, 1 row affected (0.04 sec)

MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taijiquan      |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|       12 | Nagios         |
|       14 | Zabbix         |
+----------+----------------+
9 rows in set (0.00 sec)

MariaDB [hellodb]> SHOW BINARY LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-log.000001 |      1462 |
| master-log.000002 |      4993 |
+-------------------+-----------+
2 rows in set (0.00 sec)

# 查看当前正在使用的二进制日志文件
MariaDB [hellodb]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000002 |     4493 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

# 查看记录的信息,可以发现只是记录了跟数据发生改变相关的语句,查询语句SELECT不记录;
MariaDB [hellodb]> SHOW BINLOG EVENTS IN ‘master-log.000002‘;  
+-------------------+------+-------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name          | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                                                                                                                                                          |
+-------------------+------+-------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| master-log.000002 |    4 | Format_desc |         1 |         245 | Server ver: 5.5.44-MariaDB-log, Binlog ver: 4                                                                                                                                                                                                                                 |
| master-log.000002 | 3236 | Query       |         1 |        3348 | use `hellodb`; INSERT INTO courses (Course) VALUES (‘Zabbix‘)                                                                                                                                                                                                                 |
| master-log.000002 | 3348 | Xid         |         1 |        3375 | COMMIT /* xid=45 */                                                                                                                                                                                                                                                           |
| master-log.000002 | 3375 | Query       |         1 |        3445 | BEGIN                                                                                                                                                                                                                                                                         |
| master-log.000002 | 3445 | Intvar      |         1 |        3473 | INSERT_ID=6788                                                                                                                                                                                                                                                                |
| master-log.000002 | 3473 | Query       |         1 |        3769 | use `Syslog`; insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values (‘Started Session 45 of user root.‘, 3, ‘centos7‘, 6, ‘20161201145001‘, ‘20161201145001‘, 1, ‘systemd:‘)                         |
| master-log.000002 | 3769 | Intvar      |         1 |        3797 | INSERT_ID=6789                                                                                                                                                                                                                                                                |
| master-log.000002 | 3797 | Query       |         1 |        4094 | use `Syslog`; insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values (‘Starting Session 45 of user root.‘, 3, ‘centos7‘, 6, ‘20161201145001‘, ‘20161201145001‘, 1, ‘systemd:‘)                        |
| master-log.000002 | 4094 | Xid         |         1 |        4121 | COMMIT /* xid=48 */                                                                                                                                                                                                                                                           |
| master-log.000002 | 4121 | Query       |         1 |        4191 | BEGIN                                                                                                                                                                                                                                                                         |
| master-log.000002 | 4191 | Intvar      |         1 |        4219 | INSERT_ID=6790                                                                                                                                                                                                                                                                |
| master-log.000002 | 4219 | Query       |         1 |        4521 | use `Syslog`; insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values (‘(root) CMD (/usr/lib64/sa/sa1 1 1)‘, 9, ‘centos7‘, 6, ‘20161201145001‘, ‘20161201145001‘, 1, ‘CROND[3654]:‘)                   |
| master-log.000002 | 4521 | Xid         |         1 |        4548 | COMMIT /* xid=52 */                                                                                                                                                                                                                                                           |
| master-log.000002 | 4548 | Query       |         1 |        4619 | BEGIN                                                                                                                                                                                                                                                                         |
| master-log.000002 | 4619 | Query       |         1 |        4720 | use `hellodb`; DELETE FROM courses WHERE Course=13                                                                                                                                                                                                                            |
| master-log.000002 | 4720 | Query       |         1 |        4792 | COMMIT                                                                                                                                                                                                                                                                        |
| master-log.000002 | 4792 | Query       |         1 |        4863 | BEGIN                                                                                                                                                                                                                                                                         |
| master-log.000002 | 4863 | Query       |         1 |        4966 | use `hellodb`; DELETE FROM courses WHERE CourseID=13                                                                                                                                                                                                                          |
| master-log.000002 | 4966 | Xid         |         1 |        4993 | COMMIT /* xid=57 */                                                                                                                                                                                                                                                           |
+-------------------+------+-------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

# 显示事件从指定的地方开始
MariaDB [hellodb]> SHOW BINLOG EVENTS IN ‘master-log.000002‘ FROM 9191;
+-------------------+------+------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name          | Pos  | Event_type | Server_id | End_log_pos | Info                                                                                                                                                                                                                                                        |
+-------------------+------+------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| master-log.000002 | 9191 | Intvar     |         1 |        9219 | INSERT_ID=6802                                                                                                                                                                                                                                              |
| master-log.000002 | 9219 | Query      |         1 |        9516 | use `Syslog`; insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values (‘Starting Session 48 of user root.‘, 3, ‘centos7‘, 6, ‘20161201151001‘, ‘20161201151001‘, 1, ‘systemd:‘)      |
| master-log.000002 | 9516 | Xid        |         1 |        9543 | COMMIT /* xid=96 */                                                                                                                                                                                                                                         |
| master-log.000002 | 9543 | Query      |         1 |        9613 | BEGIN                                                                                                                                                                                                                                                       |
| master-log.000002 | 9613 | Intvar     |         1 |        9641 | INSERT_ID=6803                                                                                                                                                                                                                                              |
| master-log.000002 | 9641 | Query      |         1 |        9943 | use `Syslog`; insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values (‘(root) CMD (/usr/lib64/sa/sa1 1 1)‘, 9, ‘centos7‘, 6, ‘20161201151001‘, ‘20161201151001‘, 1, ‘CROND[3720]:‘) |
| master-log.000002 | 9943 | Xid        |         1 |        9970 | COMMIT /* xid=100 */                                                                                                                                                                                                                                        |
+-------------------+------+------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

# 限制显示的行数
MariaDB [hellodb]> SHOW BINLOG EVENTS IN ‘master-log.000002‘ FROM 9191 LIMIT 3;
+-------------------+------+------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name          | Pos  | Event_type | Server_id | End_log_pos | Info                                                                                                                                                                                                                                                   |
+-------------------+------+------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| master-log.000002 | 9191 | Intvar     |         1 |        9219 | INSERT_ID=6802                                                                                                                                                                                                                                         |
| master-log.000002 | 9219 | Query      |         1 |        9516 | use `Syslog`; insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values (‘Starting Session 48 of user root.‘, 3, ‘centos7‘, 6, ‘20161201151001‘, ‘20161201151001‘, 1, ‘systemd:‘) |
| master-log.000002 | 9516 | Xid        |         1 |        9543 | COMMIT /* xid=96 */                                                                                                                                                                                                                                    |
+-------------------+------+------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

 6.有的时候我们不需要开启二进制日志的记录功能(如:导入sql脚本文件,会产生大量的语句)可以在当前会话中关掉记录二进制日志的服务,如下:

# 可以在会话中暂时关闭记录数据于二进制日志文件
MariaDB [hellodb]> SET sql_log_bin = OFF;
Query OK, 0 rows affected (0.00 sec)

 7.我们也可以使用客户端命令mysqlbinlog,执行查看二进制日志的信息

[root@centos7 mysql]# mysqlbinlog master-log.000002 | head -30
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#161201 14:32:47 server id 1  end_log_pos 245 	Start: binlog v 4, server v 5.5.44-MariaDB-log created 161201 14:32:47
# Warning: this binlog is either in use or was not closed properly.
BINLOG ‘
D8Q/WA8BAAAA8QAAAPUAAAABAAQANS41LjQ0LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAvdx/zw==
‘/*!*/;
# at 245
#161201 14:32:49 server id 1  end_log_pos 315 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1480573969/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 315
#161201 14:32:49 server id 1  end_log_pos 343 	Intvar
SET INSERT_ID=6781/*!*/;

# 指定从哪个事件开始
[root@centos7 mysql]# mysqlbinlog --start-position 9991 master-log.000002 

#从哪个事件开始到哪个事件结束
[root@centos7 mysql]# mysqlbinlog --start-position 13132 --stop-position 13462  master-log.000002 

# 指明从什么时间开始
[root@centos7 mysql]# mysqlbinlog --start-datetime "2016-12-01 15:40:01"  master-log.000002

 8.binlog的其他参数

MariaDB [hellodb]> SHOW VARIABLES LIKE ‘%binlog%‘;
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_annotate_row_events              | OFF                  |
| binlog_cache_size                       | 32768                |
| binlog_checksum                         | NONE                 |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | STATEMENT            |
| binlog_optimize_thread_scheduling       | ON                   |
| binlog_stmt_cache_size                  | 32768                |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           | # 大小
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sync_binlog                             | 0                    | # 从内存同步到文件,建议启用
+-----------------------------------------+----------------------+
12 rows in set (0.00 sec)

中继日志:relay_log

作用:

  • 从服务器上记录下来从主服务器的二进制日志文件同步过来的事件;

事务日志:innodb_log

作用:

  • 事务型存储引擎innodb用于保证事务特性(ACID)的日志文件:

事务日志包括:

重做日志redo

  • Redo记录的是已经全部完成的事务,就是执行了commit(提交)的事务,记录文件是ib_logfile0 ib_logfile1;

回滚日志undo

  • Undo记录的是已部分完成并且写入硬盘的未完成的事务,默认情况下回滚日志是记录下表空间中的(共享表空间或者独享表空间);

执行过程:

  • 一般情况下,mysql在崩溃之后,重启服务,innodb通过回滚日志undo将所有已完成并写入磁盘的未完成事务进行rollback,然后redo中的事务全部重新执行一遍(后台启动相关线程自动执行)即可恢复数据,但是随着redo的量增加,每次从redo的第一条开始恢复就会浪费长的时间,所以引入了checkpoint(检查点)机制;

事务日志相关配置参数:

  • innodb_log_files_in_group  //一组中有几个文件,默认为2个

  • innodb_log_group_home_dir  //日志目录的存放位置,默认在数据目录中

  • innodb_log_file_size       //日志文件大小

  • innodb_mirrored_log_groups //镜像日志组

演示:

  1.查看事物日志相关的参数

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%innodb%log%‘;
+-------------------------------------------+---------+
| Variable_name                             | Value   |
+-------------------------------------------+---------+
| innodb_flush_log_at_trx_commit            | 1       | 要不要在提交时从内存中同步数据到事物日志所在磁盘上,1表示ON开启
| innodb_locks_unsafe_for_binlog            | OFF     |
| innodb_log_block_size                     | 512     |
| innodb_log_buffer_size                    | 8388608 |
| innodb_log_file_size                      | 5242880 | 日志文件大小,默认为5M
| innodb_log_files_in_group                 | 2       | 一组中有几个文件,默认为2个
| innodb_log_group_home_dir                 | ./      | 日志目录的存放位置,默认在数据目录中
| innodb_mirrored_log_groups                | 1       | 镜像日志组(自己)
| innodb_recovery_update_relay_log          | OFF     |
| innodb_use_global_flush_log_at_trx_commit | ON      |
+-------------------------------------------+---------+
10 rows in set (0.00 sec)















本文出自 “逐梦小涛” 博客,请务必保留此出处http://1992tao.blog.51cto.com/11606804/1878595

MySQL 日志