Shulou(Shulou.com)06/01 Report--

Write at the front:

Imagine that your online environment uses a master-slave replication architecture. If you accidentally implement updates such as drop database db1, drop table tb1, or delete,update without where conditions, do you want to have a chance to remedy the problem when it occurs? Do you want the Slave host not to repeat the execution of the Master host? Can I skip this harmful SQL and continue to copy it? The answer is: yes. Master-slave delayed replication realizes this function.

Environmental preparation:

Build a master-slave architecture (the traditional replication method adopted by the author)

Set the master-slave delay variable (e.g. CHANGE MASTER TO master_delay=180)

Create a test table (described in more detail below)

If drop database db1 or drop table tb1 harmful SQL is performed: (drop database and drop table recover in the same way, but the scope of influence is different)

Test table:

CREATE TABLE `edusoho_ e`.`t1` (

`id`int (11) unsigned NOT NULL AUTO_INCREMENT

`xname`varchar (20) NOT NULL DEFAULT''

`address`char (20) NOT NULL DEFAULT''

`sex` tinyint (1) NOT NULL DEFAULT'1'

`hobby` varchar (30) NOT NULL DEFAULT''

`age`tinyint (2) DEFAULT '18'

PRIMARY KEY (`id`)

KEY `idx_ name` (`xname`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `bbs`.`myhash _ 0` (

`id`int (10) unsigned NOT NULL AUTO_INCREMENT

`c1`int (10) NOT NULL DEFAULT'0'

`c2` int (10) unsigned DEFAULT NULL

`c5` int (10) unsigned NOT NULL DEFAULT'0'

`c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

`c4`varchar (200) NOT NULL DEFAULT'

PRIMARY KEY (`id`)

KEY `idx_ c1` (`c1`)

KEY `idx_ c2` (`c2`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

The Master host changes the data normally:

INSERT INTO `edusoho_ e`.`t1` (`xname`, `address`, `hobby`) VALUES ('lzb',' Shijiazhuang', 'MySQL')

INSERT INTO `edusoho_ e`.`t1` (`xname`, `address`, `hobby`) VALUES ('Python',' Beijing', 'Games')

INSERT INTO `bbs`.`myhash _ 0` (c1rect c2meme c5pr c3rec c4) VALUES (2meme 3meme 4Jing now (), 5)

UPDATE `bbs`.`myhash _ 0` SET id=2 WHERE id=5

Before the above normal data change is finished, a harmful SQL is suddenly executed on the Master:

DROP DATABASE `bbs`

Stop Slave replication as soon as you find a problem:

Mysql > stop slave

At this point, other libraries on the Master host are normal:

INSERT INTO `edusoho_ e`.`t1` (`xname`, `address`, `hobby`) VALUES ('PHP',' Shenzhen', 'Learning')

Analysis:

When the drop statement occurs, the data before the drop statement may not be fully synchronized to the Slave host (which is very likely, especially if you have a large amount of data). Therefore, you need to analyze the binlog of the Master host, find the position that occurs in the drop statement, make the Slave host synchronize before the drop statement, and then skip the drop statement to make Slave continue to synchronize other Master data.

Analyze the binlog of Master:

Mysqlbinlog-v-- base64-output=decode mysql-bin.000001 | grep-I-C 10-- color 'drop'

# @ 3room2

# @ 4room3

# @ 5cm 1556612931

# @ 6 girls'

# at 1053

# 190430 16:28:51 server id 2 end_log_pos 1084 CRC32 0x7644c8d2 Xid = 2068

Com _ MIT _ blank /

# at 1084

# 190430 16:28:51 server id 2 end_log_pos 1180 CRC32 0x8fd4727e Query thread_id=11 exec_time=0 error_code=0

SET timestamp 1556612931

DROP DATABASE `bbs`

/ *! * /

# at 1180

# 190430 16:28:52 server id 2 end_log_pos 1262 CRC32 0xcfe6ddb1 Query thread_id=11 exec_time=0 error_code=0

SET timestamp 1556612932

BEGIN

/ *! * /

# at 1262

# 190430 16:28:52 server id 2 end_log_pos 1323 CRC32 0x539e7626 Table_map: `edusoho_ e`.`t1` mapped to number 312

# at 1323

# 190430 16:28:52 server id 2 end_log_pos 1383 CRC32 0xd286a3c0 Write_rows: table id 312 flags: STMT_END_F

View detailed binlog information:

Mysql > show binlog events in 'mysql-bin.000001' from 1053 limit 10

Skip harmful SQL and continue copying:

1. View the positon currently executed

Mysql > show slave status\ G

Exec_Master_Log_Pos: 120

2. Temporarily turn off the synchronization delay to enable Slave to synchronize Master data immediately.

Mysql > change master to master_delay=0

3. Synchronize the data before the drop statement occurs

Mysql > start slave until master_log_file='mysql-bin.000001',master_log_pos=1084 user='repliter' password='123456'

4. Check the position executed again

Mysql > show slave status\ G

Exec_Master_Log_Pos: 1084 (the data before the drop statement has been synchronized and verified by the corresponding data table in Slave, but the data after the drop statement has not been synchronized yet)

Now, after skipping harmful SQL, continue Master's data replication:

Mysql > stop slave

Mysql > change master to master_log_pos=1262 [master_delay=180]; (with or without addition)

Mysql > start slave user='repliter' password='123456'

Mysql > show slave status\ G

Exec_Master_Log_Pos: 1414

To verify whether the data after the drop statement has passed

In this way, harmful SQL is skipped, keeping a backup of the data that has not been deleted by Slave, and then whether to switch between master and slave or import the data back to Master depends on your own situation.

Here, the author demonstrates the process of importing the database of the same name of Slave back to Master (if the amount of data is large, it is recommended to switch between master and slave, because the cost of importing back may be much higher than the cost of switching, self-assessment, personal advice)

1. First, import the Slave library into a SQL file, here is bbs_new.sql (be sure to have a statement to create the library, if you forget, you can create it yourself)

2. Change permissions to import SQL files

Chown mysql.mysql bbs_new.sql

3. Mysql-uroot-p bbs-e "SET @ @ session.sql_log_bin=0;source / root/bbs_new.sql;" (be sure to add sql_log_bin=0, otherwise you know)

At this point, drop database statement, successfully skipped!

If delete from table (without where conditions) or truncate table is harmful to SQL:

The Master host changes the data normally:

INSERT INTO `edusoho_ e`.`t1` (`xname`, `address`, `hobby`, age) VALUES ('Games',' Hainan', 'just play', 28)

UPDATE `edusoho_ e`.`t1` SET xname=' Liu Bei 'WHERE id=5

Before the above normal data change is finished, a harmful SQL is suddenly executed on the Master:

DELETE FROM `edusoho_ e`.`t1`

Because it is delete full table data, the table structure is still there, according to which new data will be generated and changed:

INSERT INTO `edusoho_ e`.`t1` (`xname`, `address`, `hobby`) VALUES ('Cao Cao', 'State of Wei', 'three Kingdoms')

UPDATE `edusoho_ e`.`t1`SET age=40 WHERE xname=' Cao Cao'

DELETE FROM `edusoho_ e`.`t1` WHERE xname='lzb'; # Delete a piece of data that does not exist (no log will be generated)

UPDATE `edusoho_ e`.`t1` SET xname=' Sun Quan 'WHERE xname='Python'; # updates a piece of data that does not exist (no log will be generated)

Stop Slave replication as soon as you find a problem:

Mysql > stop slave

Analysis:

At this point, other databases and tables on the Master host are also unaffected. When the delete full table statement occurs, the data before the delete full table statement may not be fully synchronized to the Slave host (which is very likely, especially if you have a large amount of data). Therefore, you need to analyze the binlog of the Master host, find the position that occurs in the delete full table statement, make the Slave host synchronize to the delete full table statement, and then skip the delete full table statement so that Slave can continue to synchronize other Master data.

Analyze binlog according to time on the Master host (because the author uses ROW format, there will be many delete statements. Before the full table statement of delete, there will probably be normal delete statements, and you will not be able to tell which harmful SQL should be skipped. Therefore, when a problem occurs, be sure to know the time of occurrence as much as possible, and the analysis of binlog can be more effective.)

Mysqlbinlog-v-- base64-output=decode mysql-bin.000001 | grep-I-C 10-- color 'delete from' (author's own test, production environment must be filtered with time)

Com _ MIT _ blank /

# at 622

# 190505 8:34:35 server id 2 end_log_pos 704 CRC32 0xd237cd1f Query thread_id=3 exec_time=0 error_code=0

SET timestamp 1557016475

BEGIN

/ *! * /

# at 704

# 190505 8:34:35 server id 2 end_log_pos 765 CRC32 0x9335b52a Table_map: `edusoho_ e`.`t1` mapped to number 281

# at 765

# 190505 8:34:35 server id 2 end_log_pos 913 CRC32 0xb6da4487 Delete_rows: table id 281 flags: STMT_END_F

# DELETE FROM `edusoho_ e`.`t1`

# WHERE

# @ 1room1

# @ 2 thanks lzb'

# @ 3Qing 'Shijiazhuang'

# @ 4room1

# @ 5 thanks to MySQL'

# @ 6x18

# DELETE FROM `edusoho_ e`.`t1`

# WHERE

# @ 1y3

# @ 2 thanks Python'

# @ 3Qing 'Beijing'

# @ 4room1

# @ 5 games

# @ 6x18

# DELETE FROM `edusoho_ e`.`t1`

# WHERE

# @ 1: 5

# @ 2Qing 'Liu Bei'

# @ 3Qing 'Shenzhen'

# @ 4room1

# @ 5percent 'Learning'

# @ 6x18

# DELETE FROM `edusoho_ e`.`t1`

# WHERE

# @ 12007

# @ 2 thanks Games`

# @ 3 'Hainan'

# @ 4room1

# @ 5percent 'just play'

# @ 6x28

# at 913

# 190505 8:34:35 server id 2 end_log_pos 944 CRC32 0x215741c7 Xid = 605

Com _ MIT _ blank /

View detailed binlog information:

Mysql > show binlog events in 'mysql-bin.000001'; (online binlog is very large, be sure to add from for filtering)

+-- +

| | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | |

+-- +

| | mysql-bin.000001 | 4 | Format_desc | 2 | 120 | Server ver: 5.6.16-log, Binlog ver: 4 |

| | mysql-bin.000001 | 120 | Query | 2 | 202 | BEGIN |

| | mysql-bin.000001 | 202 | Table_map | 2 | 263 | table_id: 281 (edusoho_e.t1) |

| | mysql-bin.000001 | 263 | Write_rows | 2 | 328 | table_id: 281 flags: STMT_END_F |

| | mysql-bin.000001 | 328 | Xid | 2 | 359 | COMMIT / * xid=587 * / | |

| | mysql-bin.000001 | 359 | Query | 2 | 441 | BEGIN |

| | mysql-bin.000001 | 441 | Table_map | 2 | 502 | table_id: 281 (edusoho_e.t1) |

| | mysql-bin.000001 | 502 | Update_rows | 2 | 591 | table_id: 281 flags: STMT_END_F |

| | mysql-bin.000001 | 591 | Xid | 2 | 622 | COMMIT / * xid=596 * / | |

| | mysql-bin.000001 | 622 | Query | 2 | 704 | BEGIN |

| | mysql-bin.000001 | 704 | Table_map | 2 | 765 | table_id: 281 (edusoho_e.t1) |

| | mysql-bin.000001 | 765 | Delete_rows | 2 | 913 | table_id: 281 flags: STMT_END_F |

| | mysql-bin.000001 | 913 | Xid | 2 | 944 | COMMIT / * xid=605 * / | |

| | mysql-bin.000001 | 944 | Query | 2 | 1026 | BEGIN |

| | mysql-bin.000001 | 1026 | Table_map | 2 | 1087 | table_id: 281 (edusoho_e.t1) |

| | mysql-bin.000001 | 1087 | Write_rows | 2 | 1150 | table_id: 281flags: STMT_END_F |

| | mysql-bin.000001 | 1150 | Xid | 2 | 1181 | COMMIT / * xid=614 * / | |

| | mysql-bin.000001 | 1181 | Query | 2 | 1263 | BEGIN |

| | mysql-bin.000001 | 1263 | Table_map | 2 | 1324 | table_id: 281 (edusoho_e.t1) |

| | mysql-bin.000001 | 1324 | Update_rows | 2 | 1416 | table_id: 281flags: STMT_END_F |

| | mysql-bin.000001 | 1416 | Xid | 2 | 1447 | COMMIT / * xid=623 * / | |

+-- +

Skip harmful SQL and continue copying:

1. Temporarily turn off the synchronization delay to enable Slave to synchronize Master data immediately.

Mysql > change master to master_delay=0

2. Synchronize the data before the drop statement occurs

Mysql > start slave until master_log_file='mysql-bin.000001',master_log_pos=622 user='repliter' password='123456'

3. Check the position executed again

Mysql > show slave status\ G

Exec_Master_Log_Pos: 622 (the data before the delete full table statement has been synchronized and verified by the corresponding data table in Slave, but the data after the delete full table statement has not been synchronized yet.)

Now, after skipping harmful SQL, continue Master's data replication:

Mysql > stop slave

Mysql > change master to master_log_pos=1026 [master_delay=180]; (with or without addition)

Mysql > start slave user='repliter' password='123456'

Mysql > show slave status\ G

Exec_Master_Log_Pos: 1447

To verify whether the data after the delete full table statement has passed.

In this way, harmful SQL is skipped, keeping a backup of the data that has not been deleted by Slave, and then whether to switch between master and slave or import the data back to Master depends on your own situation.

Here, the author demonstrates the process of importing the database of the same name of Slave back to Master (if the amount of data is large, it is recommended to switch between master and slave, because the cost of importing back may be much higher than the cost of switching)

If your datasheet has a small amount of data, you can add a global write lock to the Master datasheet after the execution of the above until statement, and then synchronize the data on the Slave host, because the datasheet is small and will not have much impact on the business.

Add a write lock to the data table on the Master host: (if you know that the pk value of your data table will not be inserted, but will be generated by self-increment, you may need to empty the table first, import the old data, and then import the new data in order to ensure the consistency of the data.)

LOCK TABLE `edusoho_ e`.`t1` WRITE

Then export the data before the until statement on the Slave host:

INSERT INTO `t1` VALUES (1 minicomial lzbendary, 'Shijiazhuang', 1), (1 pyrronomical Pythonium, 'Beijing', 1), (3), (5), (5) 'Liu Bei', 'Shenzhen', (1) 'learning', 18), (7)

Switch to Master:

Mysql > show master status\ G

* * 1. Row *

File: mysql-bin.000001

Position: 1447

SET @ @ session.sql_log_bin=0; (be sure to do it, you should know exactly why)

Export the data back (if it is a SQL file, perform a source import)

INSERT INTO `t1` VALUES (1 minicomial lzbendary, 'Shijiazhuang', 1), (1 pyrronomical Pythonium, 'Beijing', 1), (3), (5), (5) 'Liu Bei', 'Shenzhen', (1) 'learning', 18), (7)

Mysql > show master status\ G

* * 1. Row *

File: mysql-bin.000001

Position: 1447

Release the lock:

UNLOCK TABLES

At this point, delete full table statement, successfully skipped!

If update table (unrestricted) is performed, it is harmful to SQL:

Test table:

CREATE TABLE `orders` (

`id`int (11) unsigned NOT NULL AUTO_INCREMENT

`xname`varchar (10) NOT NULL DEFAULT''COMMENT' user name'

`chongzhi` int (11) NOT NULL DEFAULT'0' COMMENT 'recharge amount'

`amount `int (11) NOT NULL DEFAULT'0' COMMENT 'remaining amount'

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' user order form'

Normal Master data changes:

INSERT INTO `edusoho_ e`.`orders` (xname,chongzhi,amount) VALUES ('Zheng Qianzi', 10000100)

INSERT INTO `edusoho_ e`.`orders` (xname,chongzhi,amount) VALUES ('Sun WuKong', 200600)

INSERT INTO `edusoho_ e`.`orders` (xname,chongzhi,amount) VALUES ('Conan', 666888)

INSERT INTO `edusoho_ e`.`orders` (xname,chongzhi,amount) VALUES ('I', 1jin0)

UPDATE `edusoho_ e`.`orders` SET chongzhi=chongzhi+1000 WHERE id=3

UPDATE `edusoho_ e`.`orders` SET amount=amount-200 WHERE id=5

UPDATE `edusoho_ e`.`orders` SET amount=amount-100

At this point, there is a user order table online that executes a UPDATE statement with no WHERE condition:

UPDATE `edusoho_ e`.`orders` SET chongzhi=chongzhi+1000

After the execution, users are very happy, because there is no money, for nothing, 1000 yuan; but your boss, absolutely want to beat you to death, in order not to be beaten, so, you have to quickly restore your data

Stop Slave replication as soon as you find a problem:

Mysql > stop slave

Analysis:

After finding a problem, immediately add a write lock to the Master, because although the data exists at this time, it is already the wrong data; then identify the position that is harmful to the SQL, then skip it and continue the Master replication

LOCK TABLE `edusoho_ e`.`orders` WRITE

Analyze binlog according to time on the Master host (because the author uses ROW format, there will be a lot of update statements. If there are normal update statements before update does not add restrictions, you will not be able to tell which harmful SQL should be skipped. Therefore, when a problem occurs, you must know the time of occurrence as much as possible, and the analysis of binlog can be more effective.)

Analyze the Master log to find the position that occurred in the execution problem SQL:

Mysqlbinlog-v-- base64-output=decode mysql-bin.000001 | grep-I-C 10-- color 'update'

Com _ MIT _ blank /

# at 3554

# 190505 10:04:20 server id 2 end_log_pos 3636 CRC32 0xd95ad4e9 Query thread_id=3 exec_time=0 error_code=0

SET timestamp 1557021860

BEGIN

/ *! * /

# at 3636

# 190505 10:04:20 server id 2 end_log_pos 3695 CRC32 0xa8208a81 Table_map: `edusoho_ e`.orders` mapped to number 282

# at 3695

# 190505 10:04:20 server id 2 end_log_pos 3897 CRC32 0xdb6fe2c1 Update_rows: table id 282 flags: STMT_END_F

# UPDATE `edusoho_ e`.`orders`

# WHERE

# @ 1room1

# @ 2Qing 'Zheng Qianzi'

# @ 3 million 10000

# @ 420th 100

# SET

# @ 1room1

# @ 2Qing 'Zheng Qianzi'

# @ 3room11000

# @ 420th 100

# UPDATE `edusoho_ e`.`orders`

# WHERE

# @ 1y3

# @ 2Qing 'Sun WuKong'

# @ 331 1200

# @ 4room600

# SET

# @ 1y3

# @ 2Qing 'Sun WuKong'

# @ 3room2200

# @ 4room600

# UPDATE `edusoho_ e`.`orders`

# WHERE

# @ 1: 5

# @ 2 Conan

# @ 334 666

# @ 44th 688

# SET

# @ 1: 5

# @ 2 Conan

# @ 3percent 1666

# @ 44th 688

# UPDATE `edusoho_ e`.`orders`

# WHERE

# @ 12007

# @ 2'I'

# @ 3room1

# @ 4500

# SET

# @ 12007

# @ 2'I'

# @ 3percent 1001

# @ 4500

Mysql > show binlog events in 'mysql-bin.000001' from 3554

+-- +

| | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | |

+-- +

| | mysql-bin.000001 | 3554 | Query | 2 | 3636 | BEGIN |

| | mysql-bin.000001 | 3636 | Table_map | 2 | 3695 | table_id: 282 (edusoho_e.orders) |

| | mysql-bin.000001 | 3695 | Update_rows | 2 | 3897 | table_id: 282flags: STMT_END_F |

| | mysql-bin.000001 | 3897 | Xid | 2 | 3928 | COMMIT / * xid=893 * / | |

+-- +

4 rows in set (0.00 sec)

Skip harmful SQL and continue copying:

1. Communicate with people who have problems and confirm how update is implemented.

Execute on Master:

SET @ @ session.sql_log_bin=0; (be sure to add it, otherwise you know)

UPDATE `edusoho_ e`.`orders` SET chongzhi=chongzhi-1000

At this point, the data of both Master and SLave are consistent, as long as Slave skips harmful UPDATE statements.

2. Skip harmful SQL and continue to copy

Mysql > change master to master_log_pos=3928 [master_delay=180]; (with or without addition)

3 、 start slave user='repliter' password='123456'

4. Release the write lock of the table

UNLOCK TABLES

At this point, update full table statement, successfully skipped!

Out of the question:

In this paper, according to my own understanding, imagine some of the problems that may occur online, targeted use of master_delay parameter characteristics, data recovery test, and did not go through any actual testing. On the one hand, only for the majority of colleagues to do a reference; on the other hand, to record the author's own experience and ideas for problem-solving to make a summary, when the problem really occurs, there is a direction for reference, and will not be in a hurry, do not know what to do, so, there are mistakes and understanding is not in place, please leave a message below to correct, grateful!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.