Mysql ROW mode master-slave synchronization test and error repair

The reason for the test is that PXC is gradually launched. The Online Database Synchronization mode is gradually changed from the previous STATEMENT mode to the ROW mode. Some synchronization problems are caused by changes in the synchronization mode. Test Objective To solve R to a certain extent

The reason for the test is that PXC is gradually launched. The Online Database Synchronization mode is gradually changed from the previous STATEMENT mode to the ROW mode. Some synchronization problems are caused by changes in the synchronization mode. Test Objective To solve R to a certain extent

Test reason

With the gradual release of PXC. The Online Database Synchronization mode is gradually changed from the previous STATEMENT mode to the ROW mode. Some synchronization problems are caused by changes in the synchronization mode.

Purpose

To some extent, the problem of master-slave synchronization in ROW mode is solved. As an operation document for manual repair after the PXC cluster is down.

Test Environment

Masterold02: 7301

Masterold03: 7302

Skavetest178: 7303

Master database operations

Vim my. cnf: Add the following sentence

Binlog_format = ROW database binlog use ROW mode for synchronization

Grant permissions to the Database Synchronization users respectively.

Grant all on *. * to okooo_rep @ '192. 192. %. % 'identified by 'bjfcmlc @ Mhxzkhl ';

Flush privileges;

Test started

Test the basic synchronization function

?. Use test178 as the slave to synchronize old02 data

Change master to MASTER_HOST = '192. 168.8.72 ', MASTER_USER = 'okooo _ rep', MASTER_PASSWORD = 'bjfcmlc @ Mhxzkhl ',

MASTER_PORT = 7301, MASTER_LOG_FILE = 'logbin. 100', MASTER_LOG_POS = 4;

? Check the Master/Slave status. We can see that test178 will be consistent with old02 soon.

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

? Let test178 synchronize old03 data from the slave node. We can see that test178 is also consistent with old03 soon.

Stop slave;

Change master to MASTER_HOST = '2017. 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

Conclusion: after the basic synchronization test is completed, test178 can synchronize data with any master database in old02 and old03 normally when the database is consistent with the data in the database when the database is newly established.

Write Test

? Create new databases and tables on old02 and old03 respectively

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 '2017-00-00 00:00:00 ',

'Update _ time' datetime not null default '2017-00-00 00:00:00 ',

Primary key ('id') ENGINE = InnoDB AUTO_INCREMENT = 1;

? Old02 write data

Insert into row_test values (1, 'old02', '2017-12-11 00:00:00 ', '2017-12-11 00:00:00 ');

Insert into row_test values (2, 'old02', '2017-12-11 00:00:00 ', '2017-12-11 00:00:00 ');

Insert into row_test values (3, 'old03', '2017-12-11 01:00:00 ', '2017-12-11 01:00:00 ');

Insert into row_test values (4, 'old03', '2017-12-11 01:00:00 ', '2017-12-11 01:00:00 ');

? You can view old02, old03, and test178.

Mysql> select * from row_test;

+ ---- + ---------- + --------------------- +

| Id | hostname | create_time | update_time |

+ ---- + ---------- + --------------------- +

| 1 | old02 | 00:00:00 | 00:00:00 |

| 2 | old02 | 00:00:00 | 00:00:00 |

| 3 | old03 | 01:00:00 | 01:00:00 |

| 4 | old03 | 01:00:00 | 01:00:00 |

+ ---- + ---------- + --------------------- +

? Old03 writes data. At this time, old03 (master) and test178 (cluster) are synchronized

Insert into row_test values (5, 'old03', '2017-12-11 02:00:00 ', '2017-12-11 02:00:00 ');

Insert into row_test values (6, 'old03', '2017-12-11 02:00:00 ', '2017-12-11 02:00:00 ');

? Check old03 and test178. At this time, the test178 and old02 data are inconsistent, and the cluster database has two more data IDs = 5, 6 than old02.

+ ---- + ---------- + --------------------- +

| Id | hostname | create_time | update_time |

+ ---- + ---------- + --------------------- +

| 1 | old02 | 00:00:00 | 00:00:00 |

| 2 | old02 | 00:00:00 | 00:00:00 |

| 3 | old03 | 01:00:00 | 01:00:00 |

| 4 | old03 | 01:00:00 | 01:00:00 |

| 5 | old03 | 02:00:00 | 02:00:00 |

| 6 | old03 | 02:00:00 | 02:00:00 |

+ ---- + ---------- + --------------------- +

? When old02 writes data, the master and slave databases are still synchronized with test178 and old03, which is irrelevant to old02.

Insert into row_test values (7, 'old02', '2017-12-11 03:00:00 ', '2017-12-11 03:00:00 ');

Insert into row_test values (8, 'old02', '2017-12-11 03:00:00 ', '2017-12-11 03:00:00 ');

? View the binlog of old02 to find the pos point with id = 7, 8 inserted.

Cd/home/okooo/apps/tmp_slave01/logs

../Bin/mysqlbinlog -- no-defaults -- base64-output = decode-rows-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 = 03:00:00/* DATETIME meta = 0 nullable = 0 is_null = 0 */

###@ 4 = 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 = 03:00:00/* DATETIME meta = 0 nullable = 0 is_null = 0 */

###@ 4 = 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

? Change the synchronization point of test178 and old02

Stop slave;

Change master to MASTER_HOST = '2017. 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