mysql主主模式查看mysqlbinlog是如何记录的

目的，检查mysql主主模式，mysqlbinlog的工作模式

下面按步骤开始，masterA、masterB

1、在masterA上建立数据

create database mytest;

use mytest;
CREATE TABLE `mytest`.`student`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` smallint(5) UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

insert into student(name , age) values('张三',10 ),('李四',10),('王五',10);

查看masterA上的数据

mysql> use mytest;
Database changed
mysql> select * from student;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | 张三 |  10 |
|  4 | 李四 |  10 |
|  6 | 王五 |  10 |
+----+------+-----+
3 rows in set (0.08 sec)

再查看masterB上的数据

mysql> use mytest;
Database changed
mysql> select * from student;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | 张三 |  10 |
|  4 | 李四 |  10 |
|  6 | 王五 |  10 |
+----+------+-----+
3 rows in set (0.04 sec)

数据同步已完成；

在masterB上执行SQL，继续插入数据

mysql> insert into student(name , age) values('小红' , 6),('小明',7),('小强' ,8);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

masterB上查询

mysql> select * from student;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | 张三 |  10 |
|  4 | 李四 |  10 |
|  6 | 王五 |  10 |
|  7 | 小红 |   6 |
|  9 | 小明 |   7 |
| 11 | 小强 |   8 |
+----+------+-----+
6 rows in set (0.13 sec)

masterA上查询

mysql> select * from student;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | 张三 |  10 |
|  4 | 李四 |  10 |
|  6 | 王五 |  10 |
|  7 | 小红 |   6 |
|  9 | 小明 |   7 |
| 11 | 小强 |   8 |
+----+------+-----+
6 rows in set (0.13 sec)

下面分别查看两个binlog的变化

mysqlbinlog --base64-output=decode-rows -v --start-datetime="2019-10-18 14:40:00" --stop-datetime="2019-10-18 15:10:00" /data/mysql-files/mysql-bin.000004 >/data/masterB_bin.sql

mysqlbinlog --base64-output=decode-rows -v --start-datetime="2019-10-18 14:40:00" --stop-datetime="2019-10-18 15:10:00" /data/mysql/mysql-bin.000003 >/data/masterA_bin.sql

查看还原后的sql，发现每个SQL执行都有server id，mysql-binlog日志下是全部的执行SQL

# at 1412
#191018 14:32:42 server id 10  end_log_pos 1491 CRC32 0xef2d3048     Anonymous_GTID    last_committed=4    sequence_number=5    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1491
#191018 14:32:42 server id 10  end_log_pos 1573 CRC32 0xf2dd3fdb     Query    thread_id=19    exec_time=0    error_code=0
SET TIMESTAMP=1571380362/*!*/;
BEGIN
/*!*/;
# at 1573
#191018 14:32:42 server id 10  end_log_pos 1635 CRC32 0x4cb22c69     Table_map: `mytest`.`student` mapped to number 98
# at 1635
#191018 14:32:42 server id 10  end_log_pos 1712 CRC32 0xb69ba142     Write_rows: table id 98 flags: STMT_END_F
### INSERT INTO `mytest`.`student`
### SET
###   @1=2
###   @2='张三'
###   @3=10
### INSERT INTO `mytest`.`student`
### SET
###   @1=4
###   @2='李四'
###   @3=10
### INSERT INTO `mytest`.`student`
### SET
###   @1=6
###   @2='王五'
###   @3=10
# at 1712
#191018 14:32:42 server id 10  end_log_pos 1743 CRC32 0xc9db5fe2     Xid = 93
COMMIT/*!*/;

 结论，双机热备模式，mysql增量备份在单一某个mysql服务器上也可以使用mysqlbinlog备份所有的数据增量