mysql 日志
mysql日志主要有四种类型：
二进制日志、错误日志、慢查询日志、通用查询日志
二进制日志：
记录对数据库数据更改的sql语句，默认关闭，开启后可根据该日志恢复数据
错误日志：
记录mysql启动、运行、停止时出现的问题，默认开启
慢查询日志：
记录sql语句执行时间超过指定时间的sql语句，默认关闭
通用查询日志：
记录数据库查询语句，默认关闭
********************
二进制日志
注意：
mysql8中，update语句如果没有造成数据变更，则不会记录到binlog中
mysql> update lihu.test set value=3 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update lihu.test set value=3 where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 2612 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> update lihu.test set value=3 where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
# changed=0 没有造成行记录变更，该语句不会记录到binlog中
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 2612 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> update lihu.test set value=4 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# changed=1 造成行记录变更，该语句记录到binlog中
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 2912 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
相关参数：
log_bin[=name]：开启二进制（mysql8默认开启），设置名称
如不设置，默认为：主机名-bin.000001、主机名-bin.000002、...
mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
expire_logs_days=10：设置二进制日志过期时间，默认为0（永不过期）
mysql> show variables like "expire_logs_days";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 0 |
+------------------+-------+
1 row in set (0.00 sec)
max_binlog_size=1g：二进制日志文件大小，默认为1g，超过后自动创建新的文件(序列号加1)
binlog_cache_size=32k：二进制日志缓冲，默认大小为32k
使用InnoDB存储引擎时，所有未提交事务的二进制日志都会写到缓存中，事务提交时再写入日志文件
每个session都会分配一个cache，因而cache不能设置的太大；
cache也不能设置的太小，若未提交的二进制日志记录超过缓存大小，则会写入临时文件
# 可使用运行参数查看缓存使用状况
binlog_cache_use：缓存使用次数
binlog_cache_disk_use：临时文件使用次数
mysql> show status like "binlog_cache%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
+-----------------------+-------+
2 rows in set (0.01 sec)
sync_binlog[=N]：事务每提交N次，就将缓存的日志数据刷盘，默认为1
即每次事务提交时，都将缓存内的日志数据刷盘
mysql> show variables like "sync_binlog";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1 |
+---------------+-------+
1 row in set (0.01 sec)
设置为1可能问题：二进制日志同步写入磁盘，此时事务还没有提交，数据库发生宕机；
数据库重启后，事务会回滚，但是二进制日志已经写入，不能回滚
解决方法：innodb_support_xa=1，设置为1可保证二进制日志与InnoDB数据文件之间的同步
说明：mysql8移除了innodb_support_xa，默认支持xa
binlog_do_db：需要写入的数据库日志，默认所有数据库都需要写入
binlog_ignore_db：忽略写入的数据库日志
log_slave_updates：如果当前数据库是slave，不会将从master获取的二进制日志写入本机的二进制日志
如果是master ==> slave ==> slave架构，则需要设置该参数，默认开启
mysql> show variables like "log_slave_updates";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| log_slave_updates | ON |
+-------------------+-------+
1 row in set (0.00 sec)
binlog_format：
日志格式，
可选值如下
statement：记录sql语句
row：记录行记录更改情况
mixed：同时支持statement、row，默认使用statement，如下情况使用row
存储引擎为NDB，DML(insert、update、delete)操作
使用UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数
使用insert delay语句(异步插入数据，命令发送到服务器就直接返回，insert为同步插入)
使用用户自定义函数
使用临时表（tempory table）
binlog_format：
statement
mysql> set @@session.binlog_format="statement";
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: 13b18152a242-bin.000006
Position: 1420
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> insert into test(id,value) values(4,2);
Query OK, 1 row affected (0.00 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: 13b18152a242-bin.000006
Position: 1727
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
***************
mysqlbinlog 查看二进制日志
root@13b18152a242:/# mysqlbinlog --start-position=1420 /var/lib/mysql/13b18152a242-bin.000006
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 156
#210616 14:39:33 server id 1 end_log_pos 125 CRC32 0xd9d84e0f Start: binlog v 4, server v 8.0.25 created 210616 14:39:33 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
JQ3KYA8BAAAAeQAAAH0AAAABAAQAOC4wLjI1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAlDcpgEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBD07Y2Q==
'/*!*/;
# at 1420
#210616 16:18:14 server id 1 end_log_pos 1499 CRC32 0x5364265e Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=no original_committed_timestamp=1623860294128787 immediate_commit_timestamp=1623860294128787 transaction_length=307
# original_commit_timestamp=1623860294128787 (2021-06-16 16:18:14.128787 UTC)
# immediate_commit_timestamp=1623860294128787 (2021-06-16 16:18:14.128787 UTC)
/*!80001 SET @@session.original_commit_timestamp=1623860294128787*//*!*/;
/*!80014 SET @@session.original_server_version=80025*//*!*/;
/*!80014 SET @@session.immediate_server_version=80025*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1499
#210616 16:18:14 server id 1 end_log_pos 1581 CRC32 0xdad9a13d Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1623860294/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
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=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 1581
#210616 16:18:14 server id 1 end_log_pos 1696 CRC32 0xded59d86 Query thread_id=9 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1623860294/*!*/;
insert into test(id,value) values(4,2) #此处记录sql语句
/*!*/;
# at 1696
#210616 16:18:14 server id 1 end_log_pos 1727 CRC32 0xe3a5331a Xid = 47
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
binlog_format：
row
mysql> set @@session.binlog_format="row";
Query OK, 0 rows affected (0.00 sec)
mysql> show master status \G;
*************************** 1. row ***************************
File: 13b18152a242-bin.000006
Position: 1727
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> insert into test(id,value) values(5,2);
Query OK, 1 row affected (0.01 sec)
mysql> show master status \G;
*************************** 1. row ***************************
File: 13b18152a242-bin.000006
Position: 2007
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
***************************
mysqlbinlog查看二进制日志(带上参数-vv)
root@13b18152a242:/# mysqlbinlog --start-position=1727 -vv /var/lib/mysql/13b18152a242-bin.000006
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 156
#210616 14:39:33 server id 1 end_log_pos 125 CRC32 0xd9d84e0f Start: binlog v 4, server v 8.0.25 created 210616 14:39:33 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
JQ3KYA8BAAAAeQAAAH0AAAABAAQAOC4wLjI1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAlDcpgEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBD07Y2Q==
'/*!*/;
# at 1727
#210616 16:24:09 server id 1 end_log_pos 1806 CRC32 0xd6b0a282 Anonymous_GTID last_committed=6 sequence_number=7 rbr_only=yes original_committed_timestamp=1623860649520577 immediate_commit_timestamp=1623860649520577 transaction_length=280
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1623860649520577 (2021-06-16 16:24:09.520577 UTC)
# immediate_commit_timestamp=1623860649520577 (2021-06-16 16:24:09.520577 UTC)
/*!80001 SET @@session.original_commit_timestamp=1623860649520577*//*!*/;
/*!80014 SET @@session.original_server_version=80025*//*!*/;
/*!80014 SET @@session.immediate_server_version=80025*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1806
#210616 16:24:09 server id 1 end_log_pos 1881 CRC32 0xf5a646b2 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1623860649/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
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=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 1881
#210616 16:24:09 server id 1 end_log_pos 1932 CRC32 0xed091aa3 Table_map: `test`.`test` mapped to number 91
# at 1932
#210616 16:24:09 server id 1 end_log_pos 1976 CRC32 0x23bd09e6 Write_rows: table id 91 flags: STMT_END_F
# 二进制日志记录的内容
BINLOG '
qSXKYBMBAAAAMwAAAIwHAAAAAFsAAAAAAAEABHRlc3QABHRlc3QAAgMDAAIBAQCjGgnt
qSXKYB4BAAAALAAAALgHAAAAAFsAAAAAAAEAAgAC/wAFAAAAAgAAAOYJvSM=
'/*!*/;
### INSERT INTO `test`.`test`
### SET
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=1 is_null=0 */ #此处记录的是插入的数值
# at 1976
#210616 16:24:09 server id 1 end_log_pos 2007 CRC32 0xd750cdb2 Xid = 52
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
binlog_format：
mixed
mysql> set @@session.binlog_format="mixed";
Query OK, 0 rows affected (0.00 sec)
mysql> show master status \G;
*************************** 1. row ***************************
File: 13b18152a242-bin.000006
Position: 2007
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> insert into test(id,value) values(6,2);
Query OK, 1 row affected (0.00 sec)
mysql> show master status \G;
*************************** 1. row ***************************
File: 13b18152a242-bin.000006
Position: 2314
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
***************************
mysqlbinlog 查看二进制日志
root@13b18152a242:/# mysqlbinlog --start-position=2007 /var/lib/mysql/13b18152a242-bin.000006
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 156
#210616 14:39:33 server id 1 end_log_pos 125 CRC32 0xd9d84e0f Start: binlog v 4, server v 8.0.25 created 210616 14:39:33 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
JQ3KYA8BAAAAeQAAAH0AAAABAAQAOC4wLjI1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAlDcpgEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBD07Y2Q==
'/*!*/;
# at 2007
#210616 16:29:09 server id 1 end_log_pos 2086 CRC32 0x49deb275 Anonymous_GTID last_committed=7 sequence_number=8 rbr_only=no original_committed_timestamp=1623860949398557 immediate_commit_timestamp=1623860949398557 transaction_length=307
# original_commit_timestamp=1623860949398557 (2021-06-16 16:29:09.398557 UTC)
# immediate_commit_timestamp=1623860949398557 (2021-06-16 16:29:09.398557 UTC)
/*!80001 SET @@session.original_commit_timestamp=1623860949398557*//*!*/;
/*!80014 SET @@session.original_server_version=80025*//*!*/;
/*!80014 SET @@session.immediate_server_version=80025*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2086
#210616 16:29:09 server id 1 end_log_pos 2168 CRC32 0x0b575e45 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1623860949/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
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=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 2168
#210616 16:29:09 server id 1 end_log_pos 2283 CRC32 0xee409898 Query thread_id=9 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1623860949/*!*/;
insert into test(id,value) values(6,2) //此处记录的是sql语句
/*!*/;
# at 2283
#210616 16:29:09 server id 1 end_log_pos 2314 CRC32 0x783186b4 Xid = 56
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
查看二进制文件列表：
show binary logs；
mysql> show binary logs;
+-------------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-------------------------+-----------+-----------+
| 13b18152a242-bin.000001 | 179 | No |
| 13b18152a242-bin.000002 | 3118706 | No |
| 13b18152a242-bin.000003 | 179 | No |
| 13b18152a242-bin.000004 | 156 | No |
| 13b18152a242-bin.000005 | 156 | No |
| 13b18152a242-bin.000006 | 2314 | No |
+-------------------------+-----------+-----------+
6 rows in set (0.40 sec)
查看二进制文件状态：
show master/slave status;
mysql> show master status \G;
*************************** 1. row ***************************
File: 13b18152a242-bin.000006
Position: 2314
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
查看二进制日志事件：
show binlog events [in “binlog_file”]；
mysql> show binlog events;
+-------------------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------------+-----+----------------+-----------+-------------+-----------------------------------+
| 13b18152a242-bin.000001 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.25, Binlog ver: 4 |
| 13b18152a242-bin.000001 | 125 | Previous_gtids | 1 | 156 | |
| 13b18152a242-bin.000001 | 156 | Stop | 1 | 179 | |
+-------------------------+-----+----------------+-----------+-------------+-----------------------------------+
3 rows in set (0.00 sec)
mysql> show binlog events in "13b18152a242-bin.000001";
+-------------------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------------+-----+----------------+-----------+-------------+-----------------------------------+
| 13b18152a242-bin.000001 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.25, Binlog ver: 4 |
| 13b18152a242-bin.000001 | 125 | Previous_gtids | 1 | 156 | |
| 13b18152a242-bin.000001 | 156 | Stop | 1 | 179 | |
+-------------------------+-----+----------------+-----------+-------------+-----------------------------------+
3 rows in set (0.00 sec)
mysql> show binlog events in "13b18152a242-bin.000006";
+-------------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------+
| 13b18152a242-bin.000006 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.25, Binlog ver: 4 |
| 13b18152a242-bin.000006 | 125 | Previous_gtids | 1 | 156 | |
| 13b18152a242-bin.000006 | 156 | Anonymous_Gtid | 1 | 233 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 13b18152a242-bin.000006 | 233 | Query | 1 | 341 | create database test /* xid=21 */ |
| 13b18152a242-bin.000006 | 341 | Anonymous_Gtid | 1 | 418 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 13b18152a242-bin.000006 | 418 | Query | 1 | 553 | use `test`; create table test(id int primary key,value int) /* xid=27 */ |
| 13b18152a242-bin.000006 | 553 | Anonymous_Gtid | 1 | 632 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 13b18152a242-bin.000006 | 632 | Query | 1 | 714 | BEGIN |
| 13b18152a242-bin.000006 | 714 | Query | 1 | 829 | use `test`; insert into test(id,value) values(1,2) |
| 13b18152a242-bin.000006 | 829 | Xid | 1 | 860 | COMMIT /* xid=32 */ |
| 13b18152a242-bin.000006 | 860 | Anonymous_Gtid | 1 | 939 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 13b18152a242-bin.000006 | 939 | Query | 1 | 1014 | BEGIN |
| 13b18152a242-bin.000006 | 1014 | Table_map | 1 | 1065 | table_id: 91 (test.test) |
| 13b18152a242-bin.000006 | 1065 | Write_rows | 1 | 1109 | table_id: 91 flags: STMT_END_F |
| 13b18152a242-bin.000006 | 1109 | Xid | 1 | 1140 | COMMIT /* xid=41 */ |
| 13b18152a242-bin.000006 | 1140 | Anonymous_Gtid | 1 | 1219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 13b18152a242-bin.000006 | 1219 | Query | 1 | 1294 | BEGIN |
| 13b18152a242-bin.000006 | 1294 | Table_map | 1 | 1345 | table_id: 91 (test.test) |
| 13b18152a242-bin.000006 | 1345 | Write_rows | 1 | 1389 | table_id: 91 flags: STMT_END_F |
| 13b18152a242-bin.000006 | 1389 | Xid | 1 | 1420 | COMMIT /* xid=43 */ |
| 13b18152a242-bin.000006 | 1420 | Anonymous_Gtid | 1 | 1499 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 13b18152a242-bin.000006 | 1499 | Query | 1 | 1581 | BEGIN |
| 13b18152a242-bin.000006 | 1581 | Query | 1 | 1696 | use `test`; insert into test(id,value) values(4,2) |
| 13b18152a242-bin.000006 | 1696 | Xid | 1 | 1727 | COMMIT /* xid=47 */ |
| 13b18152a242-bin.000006 | 1727 | Anonymous_Gtid | 1 | 1806 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 13b18152a242-bin.000006 | 1806 | Query | 1 | 1881 | BEGIN |
| 13b18152a242-bin.000006 | 1881 | Table_map | 1 | 1932 | table_id: 91 (test.test) |
| 13b18152a242-bin.000006 | 1932 | Write_rows | 1 | 1976 | table_id: 91 flags: STMT_END_F |
| 13b18152a242-bin.000006 | 1976 | Xid | 1 | 2007 | COMMIT /* xid=52 */ |
| 13b18152a242-bin.000006 | 2007 | Anonymous_Gtid | 1 | 2086 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 13b18152a242-bin.000006 | 2086 | Query | 1 | 2168 | BEGIN |
| 13b18152a242-bin.000006 | 2168 | Query | 1 | 2283 | use `test`; insert into test(id,value) values(6,2) |
| 13b18152a242-bin.000006 | 2283 | Xid | 1 | 2314 | COMMIT /* xid=56 */ |
+-------------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------+
33 rows in set (0.00 sec)
删除所有二进制日志：
reset master；
删除部分二进制日志
purge binary logs to “log-name”：
删除log-name之前的二进制日志，
注意要加引号
purge binary logs before “date”：
删除date之前的二进制日志，
注意要加引号
mysql> show binary logs;
+-------------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-------------------------+-----------+-----------+
| 13b18152a242-bin.000001 | 179 | No |
| 13b18152a242-bin.000002 | 3118706 | No |
| 13b18152a242-bin.000003 | 179 | No |
| 13b18152a242-bin.000004 | 156 | No |
| 13b18152a242-bin.000005 | 156 | No |
| 13b18152a242-bin.000006 | 2314 | No |
+-------------------------+-----------+-----------+
6 rows in set (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> show binary logs;
+-------------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-------------------------+-----------+-----------+
| 13b18152a242-bin.000001 | 156 | No |
+-------------------------+-----------+-----------+
1 row in set (0.00 sec)
暂停二进制日志：
set sql_log_bin=0
恢复二进制日志：
set sql_log_bin=1
根据二进制日志恢复数据：
mysqlbinlog [ option ] binlog-filename | mysql -u username -p
********************
错误日志
log_error=path/filename：
开启错误日志，并设置数据目录，默认为数据库目录，
文件名默认为hostname.err
删除错误日志：
进入客户端执行
flush logs，
会删除错误日志，创建新的日志
********************
通用查询日志
log [ = path/filename ]：
开启通用查询日志，设置存储位置及文件名称，默认关闭，
文件名默认为
hostname.log
删除通用查询日志：
进入客户端执行
flush logs，
删除旧的查询日志，创建新的查询日志
********************
慢查询日志
slow-query-log = [ 0|1]：
为0表示关闭，1表示开启
slow-query-log-file = [ path/filename ]：
设置存储位置及文件名，如果不设置，
文件名默认为hostname-slow.log
long-query-time：
记录执行时间超过该时间的sql语句
删除慢查询日志：
进入客户端执行
flush logs，
删除旧的慢查询日志，创建新的慢查询日志
版权声明：本文为weixin_43931625原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。