binlog即binary log,二进制日志文件,这个文件记录了MySQL所有的DML操作。通过binlog日志我们可以做数据恢复,增量备份,主主复制和主从复制等等。对于开发者可能对binlog并不怎么关注,但是对于运维或者架构人员来讲是非常重要的。
binlog开启成功之后,binlog文件的位置可以在my.cnf配置文件中查看。也可以在MySQL的命令行中查看。如:
查看bin_log开启情况
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/log/log |
| log_bin_index | /usr/local/mysql/log/log.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)
查看当前记录情况
mysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| log.000009 | 13862 | | | |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
File:当前记录文件名,当重启Mysql服务或者flush logs的时候该文件就会更新成新的
Position:当前记录文件的位置
查看binlog日志文件内容
[root@bogon log]# mysqlbinlog 'log.000001'
/*!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
#181214 14:44:48 server id 1 end_log_pos 120 CRC32 0x79b6cd10 Start: binlog v 4, server v 5.6.40-log created 181214 14:44:48 at startup
ROLLBACK/*!*/;
BINLOG '
YDIUXA8BAAAAdAAAAHgAAAAAAAQANS42LjQwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABgMhRcEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAARDN
tnk=
'/*!*/;
# at 120
#181214 14:45:20 server id 1 end_log_pos 199 CRC32 0x10dec193 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1544827520/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 199
#181214 14:45:20 server id 1 end_log_pos 303 CRC32 0x9ec5f952 Query thread_id=1 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1544827520/*!*/;
insert into t1 values('8','7')
/*!*/;
# at 303
#181214 14:45:20 server id 1 end_log_pos 334 CRC32 0xfd659542 Xid = 10
COMMIT/*!*/;
# at 334
#181214 14:45:35 server id 1 end_log_pos 413 CRC32 0x43929486 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1544827535/*!*/;
BEGIN
/*!*/;
# at 413
#181214 14:45:35 server id 1 end_log_pos 517 CRC32 0x4f1284f2 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1544827535/*!*/;
insert into t1 values('9','7')
/*!*/;
# at 517
#181214 14:45:35 server id 1 end_log_pos 548 CRC32 0x67231f2b Xid = 20
COMMIT/*!*/;
# at 548
#181214 14:45:39 server id 1 end_log_pos 627 CRC32 0x82b39b3e Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1544827539/*!*/;
BEGIN
/*!*/;
# at 627
#181214 14:45:39 server id 1 end_log_pos 732 CRC32 0x92d645bc Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1544827539/*!*/;
insert into t1 values('10','7')
/*!*/;
# at 732
#181214 14:45:39 server id 1 end_log_pos 763 CRC32 0xf9b885f0 Xid = 30
COMMIT/*!*/;
# at 763
#181214 14:45:42 server id 1 end_log_pos 842 CRC32 0x57f89e4d Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1544827542/*!*/;
BEGIN
/*!*/;
# at 842
#181214 14:45:42 server id 1 end_log_pos 947 CRC32 0x3dc317ca Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1544827542/*!*/;
insert into t1 values('11','7')
/*!*/;
# at 947
#181214 14:45:42 server id 1 end_log_pos 978 CRC32 0x0b73f113 Xid = 40
COMMIT/*!*/;
# at 978
#181214 14:45:46 server id 1 end_log_pos 1057 CRC32 0xaf31a01d Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1544827546/*!*/;
BEGIN
/*!*/;
# at 1057
#181214 14:45:46 server id 1 end_log_pos 1162 CRC32 0x00815e33 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1544827546/*!*/;
insert into t1 values('12','7')
/*!*/;
# at 1162
#181214 14:45:46 server id 1 end_log_pos 1193 CRC32 0x9defefcf Xid = 50
COMMIT/*!*/;
# at 1193
#181214 14:45:50 server id 1 end_log_pos 1272 CRC32 0x0313d053 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1544827550/*!*/;
BEGIN
/*!*/;
# at 1272
#181214 14:45:50 server id 1 end_log_pos 1377 CRC32 0xf3c5c465 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1544827550/*!*/;
insert into t1 values('13','7')
/*!*/;
# at 1377
#181214 14:45:50 server id 1 end_log_pos 1408 CRC32 0x5780c8a5 Xid = 60
COMMIT/*!*/;
# at 1408
#181214 14:45:54 server id 1 end_log_pos 1487 CRC32 0x8348ba5e Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1544827554/*!*/;
BEGIN
/*!*/;
# at 1487
#181214 14:45:54 server id 1 end_log_pos 1592 CRC32 0x6a384ca9 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1544827554/*!*/;
insert into t1 values('14','7')
/*!*/;
# at 1592
#181214 14:45:54 server id 1 end_log_pos 1623 CRC32 0x5731e7bc Xid = 70
COMMIT/*!*/;
# at 1623
#181214 15:00:48 server id 1 end_log_pos 1646 CRC32 0x7e89c8dc Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
更清晰的查看具体记录
mysql> show binlog events in 'log.000001';
+------------+------+-------------+-----------+-------------+---------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------+------+-------------+-----------+-------------+---------------------------------------------+
| log.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.40-log, Binlog ver: 4 |
| log.000001 | 120 | Query | 1 | 199 | BEGIN |
| log.000001 | 199 | Query | 1 | 303 | use `test`; insert into t1 values('8','7') |
| log.000001 | 303 | Xid | 1 | 334 | COMMIT /* xid=10 */ |
| log.000001 | 334 | Query | 1 | 413 | BEGIN |
| log.000001 | 413 | Query | 1 | 517 | use `test`; insert into t1 values('9','7') |
| log.000001 | 517 | Xid | 1 | 548 | COMMIT /* xid=20 */ |
| log.000001 | 548 | Query | 1 | 627 | BEGIN |
| log.000001 | 627 | Query | 1 | 732 | use `test`; insert into t1 values('10','7') |
| log.000001 | 732 | Xid | 1 | 763 | COMMIT /* xid=30 */ |
| log.000001 | 763 | Query | 1 | 842 | BEGIN |
| log.000001 | 842 | Query | 1 | 947 | use `test`; insert into t1 values('11','7') |
| log.000001 | 947 | Xid | 1 | 978 | COMMIT /* xid=40 */ |
| log.000001 | 978 | Query | 1 | 1057 | BEGIN |
| log.000001 | 1057 | Query | 1 | 1162 | use `test`; insert into t1 values('12','7') |
| log.000001 | 1162 | Xid | 1 | 1193 | COMMIT /* xid=50 */ |
| log.000001 | 1193 | Query | 1 | 1272 | BEGIN |
| log.000001 | 1272 | Query | 1 | 1377 | use `test`; insert into t1 values('13','7') |
| log.000001 | 1377 | Xid | 1 | 1408 | COMMIT /* xid=60 */ |
| log.000001 | 1408 | Query | 1 | 1487 | BEGIN |
| log.000001 | 1487 | Query | 1 | 1592 | use `test`; insert into t1 values('14','7') |
| log.000001 | 1592 | Xid | 1 | 1623 | COMMIT /* xid=70 */ |
| log.000001 | 1623 | Stop | 1 | 1646 | |
+------------+------+-------------+-----------+-------------+---------------------------------------------+
23 rows in set (0.00 sec)