目的,检查mysql主主模式,mysqlbinlog的工作模式
下面按步骤开始,masterA、masterB
1、在masterA上建立数据
create database mytest;
use mytest;
CREATE TABLE`mytest`.`student` (
`id` int(10) UNSIGNED NOT NULLAUTO_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> usemytest;
Databasechanged
mysql> select * fromstudent;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 2 | 张三 | 10 |
| 4 | 李四 | 10 |
| 6 | 王五 | 10 |
+----+------+-----+
3 rows in set (0.08 sec)
再查看masterB上的数据
mysql> usemytest;
Databasechanged
mysql> select * fromstudent;
+----+------+-----+
| 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.01sec)
Records: 3 Duplicates: 0 Warnings: 0
masterB上查询
mysql> select * fromstudent;
+----+------+-----+
| 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 * fromstudent;
+----+------+-----+
| 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=0SET 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 98flags: 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 = 93COMMIT/*!*/;
结论,双机热备模式,mysql增量备份在单一某个mysql服务器上也可以使用mysqlbinlog备份所有的数据增量