一 、建测试库表
qogir_env@localhost : (none) 03:07:20> CREATE DATABASE `xiaoboluo`;
qogir_env@localhost : (none) 03:07:20> CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`test` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`test2` varchar(100) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
二 、使用binlog server
2.1. binlog server原始格式转储
qogir_env@localhost : (none) 03:13:05> flush logs;
Query OK, 0 rows affected (0.01 sec)
qogir_env@localhost : (none) 03:18:23> show binary logs;
| mysql-bin.000032 | 4721608 |
| mysql-bin.000033 | 281 |
| mysql-bin.000034 | 3273 |
| mysql-bin.000035 | 1777 |
| mysql-bin.000036 | 1777 |
| mysql-bin.000037 | 655 |
| mysql-bin.000038 | 234 |
+------------------+-----------+
38 rows in set (0.00 sec)
# 先创建一个用于存放binlog server转储的文件目录,并进入到这个目录下启动mysqlbinlog进程,因为mysqlbinlog使用--raw选项时无法指定输出路径,只能转储到工作目录下,所以需要先使用cd命令切换路径
[root@4ee3a2ca-0be4-4057-a415-0ac5c05363ba ~]# mkdir /data/backup/binlogserver/
[root@4ee3a2ca-0be4-4057-a415-0ac5c05363ba ~]# cd /data/backup/binlogserver/
# 启动mysqlbinlog进程并挂后台执行
[root@4ee3a2ca-0be4-4057-a415-0ac5c05363ba binlogserver]# mysqlbinlog --host=10.10.30.250 --password=password --user=admin --read-from-remote-server mysql-bin.000038 --raw --stop-never &
# 查看工作目录,可以发现文件已经被同步过来了,此时主库还没有写入任何东西
[root@4ee3a2ca-0be4-4057-a415-0ac5c05363ba binlogserver]# ll
total 4
-rw-r----- 1 root root 123 May 22 16:08 mysql-bin.000038
qogir_env@localhost : (none) 03:51:47> insert into xiaoboluo.test(test) values('2017-05-22 00:03:26');
Query OK, 1 row affected (0.00 sec)
qogir_env@localhost : (none) 04:09:46> insert into xiaoboluo.test(test) values('2017-05-23 00:03:26');
Query OK, 1 row affected (0.00 sec)
qogir_env@localhost : (none) 04:09:50>
[root@4ee3a2ca-0be4-4057-a415-0ac5c05363ba binlogserver]# mysqlbinlog -vv mysql-bin.000038
...... #这里我们直接查看最后一个事务,即最后一个BEGIN开始往后的内容即可
BEGIN
/*!*/;
# at 746
#170522 16:09:50 server id 3306250 end_log_pos 832 CRC32 0x434a1500 Rows_query
# insert into xiaoboluo.test(test) values('2017-05-23 00:03:26')
# at 832
#170522 16:09:50 server id 3306250 end_log_pos 890 CRC32 0x8fbe85a5 Table_map: `xiaoboluo`.`test` mapped to number 249
# at 890
#170522 16:09:50 server id 3306250 end_log_pos 951 CRC32 0x41154915 Write_rows: table id 249 flags: STMT_END_F
BINLOG '
zpwiWR0KczIAVgAAAEADAACAAD5pbnNlcnQgaW50byB4aWFvYm9sdW8udGVzdCh0ZXN0KSB2YWx1
ZXMoJzIwMTctMDUtMjMgMDA6MDM6MjYnKQAVSkM=
zpwiWRMKczIAOgAAAHoDAAAAAPkAAAAAAAEACXhpYW9ib2x1bwAEdGVzdAADAw8PBCwBLAEGpYW+
jw==
zpwiWR4KczIAPQAAALcDAAAAAPkAAAAAAAEAAgAD//z4wQIAEwAyMDE3LTA1LTIzIDAwOjAzOjI2
FUkVQQ==
'/*!*/;
### INSERT INTO `xiaoboluo`.`test`
### SET
### @1=180728 /* INT meta=0 nullable=0 is_null=0 */
### @2='2017-05-23 00:03:26' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ #这里可以看到在master中写入的第二行数据,日期为2017-05-23的那一行
### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
ROLLBACK /* added by mysqlbinlog */ /*!*/; #留意这里,这里按照正常逻辑来讲,应该是一个带commit语句和xid号的 event,然而这里却是一个rollback语句
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@e710d318-d5b4-4bc7-a606-d09f06ff5f5d binlog]# mysqlbinlog -vv mysql-bin.000038
......
BEGIN
/*!*/;
# at 746
#170522 16:09:50 server id 3306250 end_log_pos 832 CRC32 0x434a1500 Rows_query
# insert into xiaoboluo.test(test) values('2017-05-23 00:03:26')
# at 832
#170522 16:09:50 server id 3306250 end_log_pos 890 CRC32 0x8fbe85a5 Table_map: `xiaoboluo`.`test` mapped to number 249
# at 890
#170522 16:09:50 server id 3306250 end_log_pos 951 CRC32 0x41154915 Write_rows: table id 249 flags: STMT_END_F
BINLOG '
zpwiWR0KczIAVgAAAEADAACAAD5pbnNlcnQgaW50byB4aWFvYm9sdW8udGVzdCh0ZXN0KSB2YWx1
ZXMoJzIwMTctMDUtMjMgMDA6MDM6MjYnKQAVSkM=
zpwiWRMKczIAOgAAAHoDAAAAAPkAAAAAAAEACXhpYW9ib2x1bwAEdGVzdAADAw8PBCwBLAEGpYW+
jw==
zpwiWR4KczIAPQAAALcDAAAAAPkAAAAAAAEAAgAD//z4wQIAEwAyMDE3LTA1LTIzIDAwOjAzOjI2
FUkVQQ==
'/*!*/;
### INSERT INTO `xiaoboluo`.`test`
### SET
### @1=180728 /* INT meta=0 nullable=0 is_null=0 */
### @2='2017-05-23 00:03:26' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ #日期为2017-05-23的数据在这里
### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
# at 951
#170522 16:09:50 server id 3306250 end_log_pos 982 CRC32 0x60d092ff Xid = 1156777
COMMIT/*!*/; # 可以看到master中记录的binlog是正常的,代表这个事务在主库已经正常commit了
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
2.2.binlog server文本格式转储
[root@4ee3a2ca-0be4-4057-a415-0ac5c05363ba binlogserver]# killall mysqlbinlog
[root@4ee3a2ca-0be4-4057-a415-0ac5c05363ba binlogserver]# ps aux |grep mysqlbinlog
root 11878 0.0 0.0 103252 840 pts/0 S+ 16:34 0:00 grep mysqlbinlog
[1]+ Terminated mysqlbinlog --host=10.10.30.250 --password=password --user=admin --read-from-remote-server mysql-bin.000038 --raw --stop-never
[root@4ee3a2ca-0be4-4057-a415-0ac5c05363ba binlogserver]# rm -rf *
[root@4ee3a2ca-0be4-4057-a415-0ac5c05363ba binlogserver]# ll
total 0
[root@4ee3a2ca-0be4-4057-a415-0ac5c05363ba binlogserver]#
qogir_env@localhost : (none) 04:09:50> flush logs;
Query OK, 0 rows affected (0.01 sec)
qogir_env@localhost : (none) 04:35:04> show binary logs;
| mysql-bin.000034 | 3273 |
| mysql-bin.000035 | 1777 |
| mysql-bin.000036 | 1777 |
| mysql-bin.000037 | 655 |
| mysql-bin.000038 | 1029 |
| mysql-bin.000039 | 234 |
+------------------+-----------+
39 rows in set (0.00 sec)
[root@4ee3a2ca-0be4-4057-a415-0ac5c05363ba ~]# cd /data/backup/binlogserver/
# 启动mysqlbinlog并挂后台执行(注意:为了演示需要,把base-64编码解析为可读格式的sql语句,加上了-vv选项,实际生产环境如果要用于重放,请不要添加-vv选项)
[root@4ee3a2ca-0be4-4057-a415-0ac5c05363ba binlogserver]# mysqlbinlog --host=10.10.30.250 --password=password --user=admin --read-from-remote-server mysql-bin.000039 \
--result-file=binlog_parse --stop-never -vv &
# 查看工作目录,可以发现目录下多了一个binlog_parse文件,此时主库还没有写入任何东西
[root@4ee3a2ca-0be4-4057-a415-0ac5c05363ba binlogserver]# ll
total 4
-rw-r----- 1 root root 741 May 22 16:38 binlog_parse
qogir_env@localhost : (none) 04:35:11> insert into xiaoboluo.test(test) values('2017-05-22 00:03:26');
Query OK, 1 row affected (0.01 sec)
qogir_env@localhost : (none) 04:39:15> insert into xiaoboluo.test(test) values('2017-05-23 00:03:26');
Query OK, 1 row affected (0.00 sec)
qogir_env@localhost : (none) 04:39:18>
[root@4ee3a2ca-0be4-4057-a415-0ac5c05363ba binlogserver]# cat binlog_parse
...... #这里我们直接查看最后一个事务,即最后一个BEGIN开始往后的内容即可
BEGIN
/*!*/;
# at 746
#170522 16:39:18 server id 3306250 end_log_pos 832 CRC32 0xe3c8e631 Rows_query
# insert into xiaoboluo.test(test) values('2017-05-23 00:03:26')
# at 832
#170522 16:39:18 server id 3306250 end_log_pos 890 CRC32 0x7debb044 Table_map: `xiaoboluo`.`test` mapped to number 249
# at 890
#170522 16:39:18 server id 3306250 end_log_pos 951 CRC32 0xabb8de46 Write_rows: table id 249 flags: STMT_END_F
BINLOG '
tqMiWR0KczIAVgAAAEADAACAAD5pbnNlcnQgaW50byB4aWFvYm9sdW8udGVzdCh0ZXN0KSB2YWx1
ZXMoJzIwMTctMDUtMjMgMDA6MDM6MjYnKTHmyOM=
tqMiWRMKczIAOgAAAHoDAAAAAPkAAAAAAAEACXhpYW9ib2x1bwAEdGVzdAADAw8PBCwBLAEGRLDr
fQ==
tqMiWR4KczIAPQAAALcDAAAAAPkAAAAAAAEAAgAD//z8wQIAEwAyMDE3LTA1LTIzIDAwOjAzOjI2
Rt64qw==
'/*!*/;
### INSERT INTO `xiaoboluo`.`test`
### SET
### @1=180732 /* INT meta=0 nullable=0 is_null=0 */
### @2='2017-05-23 00:03:26' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ #日期为2017-05-23的event在这里
### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
# at 951
#170522 16:39:18 server id 3306250 end_log_pos 982 CRC32 0x29f585cc Xid = 1156784
COMMIT/*!*/; #这里可以看到commit语句在不带--raw时被正确转储了
[root@e710d318-d5b4-4bc7-a606-d09f06ff5f5d binlog]# mysqlbinlog -vv mysql-bin.000038
......
BEGIN
/*!*/;
# at 746
#170522 16:39:18 server id 3306250 end_log_pos 832 CRC32 0xe3c8e631 Rows_query
# insert into xiaoboluo.test(test) values('2017-05-23 00:03:26')
# at 832
#170522 16:39:18 server id 3306250 end_log_pos 890 CRC32 0x7debb044 Table_map: `xiaoboluo`.`test` mapped to number 249
# at 890
#170522 16:39:18 server id 3306250 end_log_pos 951 CRC32 0xabb8de46 Write_rows: table id 249 flags: STMT_END_F
BINLOG '
tqMiWR0KczIAVgAAAEADAACAAD5pbnNlcnQgaW50byB4aWFvYm9sdW8udGVzdCh0ZXN0KSB2YWx1
ZXMoJzIwMTctMDUtMjMgMDA6MDM6MjYnKTHmyOM=
tqMiWRMKczIAOgAAAHoDAAAAAPkAAAAAAAEACXhpYW9ib2x1bwAEdGVzdAADAw8PBCwBLAEGRLDr
fQ==
tqMiWR4KczIAPQAAALcDAAAAAPkAAAAAAAEAAgAD//z8wQIAEwAyMDE3LTA1LTIzIDAwOjAzOjI2
Rt64qw==
'/*!*/;
### INSERT INTO `xiaoboluo`.`test`
### SET
### @1=180732 /* INT meta=0 nullable=0 is_null=0 */
### @2='2017-05-23 00:03:26' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
# at 951
#170522 16:39:18 server id 3306250 end_log_pos 982 CRC32 0x29f585cc Xid = 1156784
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
三、总 结