查看mysql的主库binlog信息_mysql主主模式查看mysqlbinlog是如何记录的

目的，检查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备份所有的数据增量