mysql行模式（ROW）主从同步测试及错误修复
- WBOYOriginal
- 2016-06-07 17:37:331132Durchsuche
测试原由随着PXC的逐步上线。线上数据库的同步方式慢慢由之前的STATEMENT模式转换到了ROW模式。由于同步方式的改变引发了一些同步问题。测试目的一定程度上解决R
测试原由
随着PXC的逐步上线。线上数据库的同步方式慢慢由之前的STATEMENT模式转换到了ROW模式。由于同步方式的改变引发了一些同步问题。
测试目的
一定程度上解决ROW模式下主从同步的问题。作为以后PXC集群down掉，人工修复的操作文档。
测试环境
masterold02:7301
masterold03:7302
skavetest178:7303
主库操作
vim my.cnf 加入下一面一句
binlog_format=ROW 数据库binlog使用ROW模式同步
分别赋予丛库同步用户的权限
grant all on *.* to okooo_rep@'192.168.%.%' identified by 'Bjfcmlc@Mhxzkhl';
flush privileges;
测试开始
测试基础同步功能
?.让test178作为从去同步old02的数据
CHANGE MASTER TO MASTER_HOST='192.168.8.72',MASTER_USER='okooo_rep',MASTER_PASSWORD='Bjfcmlc@Mhxzkhl',
MASTER_PORT=7301,MASTER_LOG_FILE='logbin.000001',MASTER_LOG_POS=4;
? 查看主从状态，我们看到很快test178就可以和old02保持一致了。
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.72
Master_User: okooo_rep
Master_Port: 7301
Connect_Retry: 60
Master_Log_File: logbin.000006
Read_Master_Log_Pos: 332
Relay_Log_File: relay.000007
Relay_Log_Pos: 475
Relay_Master_Log_File: logbin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
? 让test178作为从去同步old03的数据，我们看到很快test178也和old03保持一致了。
stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.8.73',MASTER_USER='okooo_rep',MASTER_PASSWORD='Bjfcmlc@Mhxzkhl',MASTER_PORT=7302,MASTER_LOG_FILE='logbin.000001',MASTER_LOG_POS=4;
start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.73
Master_User: okooo_rep
Master_Port: 7302
Connect_Retry: 60
Master_Log_File: logbin.000005
Read_Master_Log_Pos: 332
Relay_Log_File: relay.000006
Relay_Log_Pos: 475
Relay_Master_Log_File: logbin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
总结：基础同步测试完成，说明在数据库新搭建结束的时候数据库中数据一致的情况下，test178可以正常的和old02和old03中任意主库同步数据。
写入测试
? 分别在old02，old03上建立新的数据库和表
create database row_slave;
CREATE TABLE `row_test` (
`id` int(10) unsigned NOT NULL,
`hostname` varchar(20) NOT NULL default '',
`create_time` datetime NOT NULL default '0000-00-00 00:00:00',
`update_time` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 ;
? old02写入数据
insert into row_test values(1,'old02','2013-12-11 00:00:00','2013-12-11 00:00:00');
insert into row_test values(2,'old02','2013-12-11 00:00:00','2013-12-11 00:00:00');
insert into row_test values(3,'old03','2013-12-11 01:00:00','2013-12-11 01:00:00');
insert into row_test values(4,'old03','2013-12-11 01:00:00','2013-12-11 01:00:00');
?查看old02，old03，test178 皆可以查出来
mysql> select * from row_test;
+----+----------+---------------------+---------------------+
| id | hostname | create_time | update_time |
+----+----------+---------------------+---------------------+
| 1 | old02 | 2013-12-11 00:00:00 | 2013-12-11 00:00:00 |
| 2 | old02 | 2013-12-11 00:00:00 | 2013-12-11 00:00:00 |
| 3 | old03 | 2013-12-11 01:00:00 | 2013-12-11 01:00:00 |
| 4 | old03 | 2013-12-11 01:00:00 | 2013-12-11 01:00:00 |
+----+----------+---------------------+---------------------+
?old03写入数据，此时old03（主）和test178（丛）在同步
insert into row_test values(5,'old03','2013-12-11 02:00:00','2013-12-11 02:00:00');
insert into row_test values(6,'old03','2013-12-11 02:00:00','2013-12-11 02:00:00');
?查看old03，test178 皆可查出。此时test178和 old02数据已经不一致了，丛库比old02多出2条数据id=5,6。
+----+----------+---------------------+---------------------+
| id | hostname | create_time | update_time |
+----+----------+---------------------+---------------------+
| 1 | old02 | 2013-12-11 00:00:00 | 2013-12-11 00:00:00 |
| 2 | old02 | 2013-12-11 00:00:00 | 2013-12-11 00:00:00 |
| 3 | old03 | 2013-12-11 01:00:00 | 2013-12-11 01:00:00 |
| 4 | old03 | 2013-12-11 01:00:00 | 2013-12-11 01:00:00 |
| 5 | old03 | 2013-12-11 02:00:00 | 2013-12-11 02:00:00 |
| 6 | old03 | 2013-12-11 02:00:00 | 2013-12-11 02:00:00 |
+----+----------+---------------------+---------------------+
?old02写入数据 此时主从库还是test178和old03在同步，和old02没有关系
insert into row_test values(7,'old02','2013-12-11 03:00:00','2013-12-11 03:00:00');
insert into row_test values(8,'old02','2013-12-11 03:00:00','2013-12-11 03:00:00');
?查看 old02的binlog 来找到插入id =7,8的 pos点
cd /home/okooo/apps/tmp_slave01/logs
../bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v -v ./logbin.000007
# at 1399
#131211 11:36:42 server id 1287301 end_log_pos 1472 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1386733002/*!*/;
BEGIN
/*!*/;
# at 1472
# at 1529
#131211 11:36:42 server id 1287301 end_log_pos 1529 Table_map: `row_slave`.`row_test` mapped to number 33
#131211 11:36:42 server id 1287301 end_log_pos 1585 Write_rows: table id 33 flags: STMT_END_F
### INSERT INTO row_slave.row_test
### SET
### @1=7 /* INT meta=0 nullable=0 is_null=0 */
### @2='old02' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
### @3=2013-12-11 03:00:00 /* DATETIME meta=0 nullable=0 is_null=0 */
### @4=2013-12-11 03:00:00 /* DATETIME meta=0 nullable=0 is_null=0 */
# at 1585
#131211 11:36:42 server id 1287301 end_log_pos 1612 Xid = 40
COMMIT/*!*/;
# at 1612
#131211 11:36:43 server id 1287301 end_log_pos 1685 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1386733003/*!*/;
BEGIN
/*!*/;
# at 1685
# at 1742
#131211 11:36:43 server id 1287301 end_log_pos 1742 Table_map: `row_slave`.`row_test` mapped to number 33
#131211 11:36:43 server id 1287301 end_log_pos 1798 Write_rows: table id 33 flags: STMT_END_F
### INSERT INTO row_slave.row_test
### SET
### @1=8 /* INT meta=0 nullable=0 is_null=0 */
### @2='old02' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
### @3=2013-12-11 03:00:00 /* DATETIME meta=0 nullable=0 is_null=0 */
### @4=2013-12-11 03:00:00 /* DATETIME meta=0 nullable=0 is_null=0 */
# at 1798
#131211 11:36:43 server id 1287301 end_log_pos 1825 Xid = 41
COMMIT/*!*/;
DELIMITER ;
# End of log file
?改变test178的同步点和old02同步
stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.8.72',MASTER_USER='okooo_rep',MASTER_PASSWORD='Bjfcmlc@Mhxzkhl',MASTER_PORT=7301,MASTER_LOG_FILE='logbin.000007',MASTER_LOG_POS=1399;
start slave;
show slave status\G