mysql的binlog说明
主从复制依赖于二进制日志文件,简称为binlog
binlog里面有存放了偏移信息
mysql主库binlog信息查看命令
[root@master ~]# mysql -u root -p123456 #登录到mysql里
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connectionid is 4Server version:5.7.26-log MySQL Community Server (GPL)
Copyright (c)2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.
mysql>show master logs; #binlog列表查看,也可以直接到/data/mysql查看+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 177 |
| master-bin.000002 | 398 |
+-------------------+-----------+
2 rows in set (0.00sec)
mysql>show master status; #记录目前的binlog+偏移信息+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 | 398 | | | | #398即是偏移
+-------------------+----------+--------------+------------------+-------------------+
创建一个库和表,观察日志
mysql>create database darren;
Query OK,1 row affected (0.01sec)
mysql>show databases;+--------------------+
| Database |
+--------------------+
| information_schema |
| darren |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00sec)
mysql>use darren;
Database changed
mysql> create table test (id int);
Query OK,0 rows affected (0.01sec)
mysql>show tables;+------------------+
| Tables_in_darren |
+------------------+
| test |
+------------------+
查看binlog日志
mysql> mysql> show binlog events in 'master-bin.000002'; #全部查看+-------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------+
| master-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| master-bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| master-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| master-bin.000002 | 219 | Query | 1 | 398 | ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| master-bin.000002 | 398 | Anonymous_Gtid | 1 | 463 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| master-bin.000002 | 463 | Query | 1 | 563 | create database darren |
| master-bin.000002 | 563 | Anonymous_Gtid | 1 | 628 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| master-bin.000002 | 628 | Query | 1 | 732 | use `darren`; create table test (id int) |
+-------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------+mysql> mysql> show binlog events in 'master-bin.000002' limit 2; 只查看前两个语句+-------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+----------------+-----------+-------------+---------------------------------------+
| master-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| master-bin.000002 | 123 | Previous_gtids | 1 | 154 | |
+-------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00sec)
mysql> show binlog events in 'master-bin.000002' from 398; #查看偏移量是398以后的语句+-------------------+-----+----------------+-----------+-------------+------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+----------------+-----------+-------------+------------------------------------------+
| master-bin.000002 | 398 | Anonymous_Gtid | 1 | 463 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| master-bin.000002 | 463 | Query | 1 | 563 | create database darren |
| master-bin.000002 | 563 | Anonymous_Gtid | 1 | 628 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| master-bin.000002 | 628 | Query | 1 | 732 | use `darren`; create table test (id int) |
+-------------------+-----+----------------+-----------+-------------+------------------------------------------+mysql> mysql> show binlog events in 'master-bin.000002' from 398 limit 1; #查看偏移量为398的前一条+-------------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+----------------+-----------+-------------+--------------------------------------+
| master-bin.000002 | 398 | Anonymous_Gtid | 1 | 463 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
+-------------------+-----+----------------+-----------+-------------+--------------------------------------+
直接使用命令查看二进制文件
[root@master ~]# cd /data/mysql/
[root@master mysql]# mysqlbinlog master-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER/*!*/;
# at4#190703 11:37:51 server id 1 end_log_pos 123 CRC32 0x7ae93041 Start: binlog v 4, server v 5.7.26-log created 190703 11:37:51at startup
# Warning: this binlog is eitherinuse or was not closed properly.
ROLLBACK/*!*/;
BINLOG'z8scXQ8BAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADPyxxdEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AUEw6Xo=
'/*!*/;
# at 123#190703 11:37:51 server id 1 end_log_pos 154 CRC32 0x81e643cc Previous-GTIDs
# [empty]
# at154#190703 11:50:19 server id 1 end_log_pos 219 CRC32 0x0a3cc640 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at219#190703 11:50:19 server id 1 end_log_pos 398 CRC32 0xbd85ba24 Query thread_id=3 exec_time=0 error_code=0SET TIMESTAMP=1562169019/*!*/;
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=1436549152/*!*/;
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/*!*/;
ALTER USER'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
/*!*/;
# at398#190703 12:18:27 server id 1 end_log_pos 463 CRC32 0x674f9414 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at463#190703 12:18:27 server id 1 end_log_pos 563 CRC32 0x2106de6f Query thread_id=4 exec_time=0 error_code=0SET TIMESTAMP=1562170707/*!*/;
create database darren/*!*/;
# at563#190703 12:19:26 server id 1 end_log_pos 628 CRC32 0xbf9b983e Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at628#190703 12:19:26 server id 1 end_log_pos 732 CRC32 0x773a1598 Query thread_id=4 exec_time=0 error_code=0use `darren`/*!*/;
SET TIMESTAMP=1562170766/*!*/;
create table test (id int)/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /*added by mysqlbinlog*/ /*!*/;
DELIMITER ;
# End of logfile
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
增删改查,然后查看binlog
mysql> insert into test values (1); #写入一个数据,有事务的提交
Query OK,1 row affected (0.01sec)
mysql> select *from test; #查一个数据+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00sec)
mysql> update mysql> update test set id 1; #改一个数据,有事务的提交
Query OK,1 row affected (0.00sec)
Rows matched:1 Changed: 1 Warnings: 0mysql> select *from test;+------+
| id |
+------+
| 3 |
+------+
1 row in set (0.00sec)
mysql>delete from test; #删除数据,有事务的提交
Query OK,1 row affected (0.00sec)
mysql> show binlog events in 'master-bin.000002' from 463; #查看日志+-------------------+------+----------------+-----------+-------------+------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+------+----------------+-----------+-------------+------------------------------------------+
| master-bin.000002 | 463 | Query | 1 | 563 | create database darren |
| master-bin.000002 | 563 | Anonymous_Gtid | 1 | 628 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| master-bin.000002 | 628 | Query | 1 | 732 | use `darren`; create table test (id int) |
| master-bin.000002 | 732 | Anonymous_Gtid | 1 | 797 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| master-bin.000002 | 797 | Query | 1 | 871 | BEGIN |
| master-bin.000002 | 871 | Table_map | 1 | 920 | table_id: 108 (darren.test) |
| master-bin.000002 | 920 | Write_rows | 1 | 960 | table_id: 108 flags: STMT_END_F |
| master-bin.000002 | 960 | Xid | 1 | 991 | COMMIT /*xid=44*/ |
| master-bin.000002 | 991 | Anonymous_Gtid | 1 | 1056 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| master-bin.000002 | 1056 | Query | 1 | 1130 | BEGIN |
| master-bin.000002 | 1130 | Table_map | 1 | 1179 | table_id: 108 (darren.test) |
| master-bin.000002 | 1179 | Update_rows | 1 | 1225 | table_id: 108 flags: STMT_END_F |
| master-bin.000002 | 1225 | Xid | 1 | 1256 | COMMIT /*xid=49*/ |
| master-bin.000002 | 1256 | Anonymous_Gtid | 1 | 1321 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| master-bin.000002 | 1321 | Query | 1 | 1395 | BEGIN |
| master-bin.000002 | 1395 | Table_map | 1 | 1444 | table_id: 108 (darren.test) |
| master-bin.000002 | 1444 | Delete_rows | 1 | 1484 | table_id: 108 flags: STMT_END_F |
| master-bin.000002 | 1484 | Xid | 1 | 1515 | COMMIT /*xid=52*/ |
+-------------------+------+----------------+-----------+-------------+------------------------------------------+
#错误sql不会写入binlog
mysql主库binlog清空,测试教学环境用,线上环境慎用。或者初搭建mysql主从用
mysql>reset master;
Query OK,0 rows affected (0.01sec)
mysql>show master logs;+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 154 |
+-------------------+-----------+
1 row in set (0.00sec)
mysql> show binlog events in 'master-bin.000001';+-------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+----------------+-----------+-------------+---------------------------------------+
| master-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| master-bin.000001 | 123 | Previous_gtids | 1 | 154 | |
+-------------------+-----+----------------+-----------+-------------+---------------------------------------+
mysql主库刷新binlog,一般来说也是测试环境用
mysql>flush logs; #新增一个日志
Query OK,0 rows affected (0.01sec)
mysql>show master logs;+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 202 |
| master-bin.000002 | 154 |
+-------------------+-----------+
2 rows in set (0.01sec)
mysql>use darren;
Database changed
mysql> insert into test values (1);
Query OK,1 row affected (0.01sec)
mysql>show master logs;+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 202 |
| master-bin.000002 | 413 |
+-------------------+-----------+
2 rows in set (0.00sec)
mysql> show binlog events in 'master-bin.000001';+-------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+----------------+-----------+-------------+---------------------------------------+
| master-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| master-bin.000001 | 123 | Previous_gtids | 1 | 154 | |
| master-bin.000001 | 154 | Rotate | 1 | 202 | master-bin.000002;pos=4 |
+-------------------+-----+----------------+-----------+-------------+---------------------------------------+mysql> show binlog events in 'master-bin.000002'; #查看日志内容+-------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+----------------+-----------+-------------+---------------------------------------+
| master-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| master-bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| master-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| master-bin.000002 | 219 | Query | 1 | 293 | BEGIN |
| master-bin.000002 | 293 | Table_map | 1 | 342 | table_id: 108 (darren.test) |
| master-bin.000002 | 342 | Write_rows | 1 | 382 | table_id: 108 flags: STMT_END_F |
| master-bin.000002 | 382 | Xid | 1 | 413 | COMMIT /*xid=62*/ |
+-------------------+-----+----------------+-----------+-------------+---------------------------------------+
测试数据删除,让两个数据库的内容保持一致
mysql> show binlog events in 'master-bin.000002 ^C;
mysql>drop database darren;
Query OK,1 row affected (0.02sec)
mysql>reset master;
Query OK,0 rows affected (0.01 sec)