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

Delay node solution

Deleting the database and running away is also an old problem. It can be seen that mistakenly deleting data in the process of operation and maintenance database, or developing code with bug, resulting in erroneous deletion of data is not uncommon. However, there are many solutions to restore or prevent erroneous deletion, such as the SQL management system, where the SQL to be executed is first reviewed by the administrator, then the administrator backs up a mirror database, executes the SQL on the mirror, and restores the image after execution. In this way, through layers of checks, the probability of misoperation can be greatly reduced.

In addition, binlog logs can also be used to recover misoperated data, so databases running online will turn on the binlog log function. There is also the delay node to be introduced in this section: in a Replication cluster, you can set a delay node whose data synchronization time is slower than that of other nodes in the cluster. If the data of the delay node has not been affected, it can be restored from the delay node after the misoperation of other nodes.

But if all the existing databases are built on PXC clusters, can we adopt this solution without Replication clusters? It is also possible that the PXC cluster and the Replication cluster are not mutually exclusive. We can set a node in the PXC cluster to Master, and then add a delay node to set to Slave, so that the two nodes form a Replication cluster for data synchronization. As follows:

This section briefly demonstrates how to build a delay node under this heterogeneous cluster. I have prepared a PXC cluster and a database to be used as a delay node in advance:

Here, PXC-Node3 in the PXC cluster is used as the Master, which is made up of master and slave with DelayNode, and DelayNode is naturally used as a delay node.

For more information on the construction of PXC cluster and Replication cluster, please refer to the following article, which is not explained here because of the limited space:

Set up a PXC cluster under CentOS8, build a highly available Replication cluster, archive a large number of cold data, configure delay nodes for PXC nodes.

To start the hands-on practice, you first need to stop the MySQL services on both nodes:

Systemctl stop mysqld

GTID must be enabled in the configuration files of the master and slave nodes, otherwise the data cannot be recovered by using the delayed node. The configuration that needs to be added to the primary node is as follows:

[root@PXC-Node3 ~] # vim / etc/percona-xtradb-cluster.conf.d/mysqld.cnf [mysqld]. # set the node's idserver_id=3# to enable binloglog_bin=mysql_bin# and enable GTIDgtid_mode=ONenforce_gtid_consistency=1

The configuration that needs to be added to the slave node is as follows:

[root@delay-node ~] # vim / etc/ my.cnf [mysqld]... server_id=102log_bin=mysql_bin# slave node needs to enable relay_logrelay_log=relay_bingtid_mode=ONenforce_gtid_consistency=1

After completing the configuration of the configuration file, start the two nodes:

Systemctl start mysqld

Then configure the master-slave relationship between Slave and Master, enter the MySQL command line terminal of Master, and query the binary log currently being used by Master and the location of the current binary log execution with the following statement:

Mysql > flush logs;-- refresh log mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | PXC-Node3-bin.000003 | 154 | | | +-+ 1 row in set (0.00 sec) |

After recording the above execution result, go to the MySQL command line terminal of Slave and execute the following statements:

Mysql > stop slave;-- stop master-slave synchronization mysql > change master to master_log_file='PXC-Node3-bin.000003', master_log_pos=154, master_host='192.168.190.134', master_port=3306, master_user='admin', master_password='Abc_123456';-- configure the connection information of the Master node, and where to start copying mysql > start slave from the Master binary log -- start master-slave synchronization Tips: usually configure master-slave synchronization to create a separate account for synchronization. Here, the existing account is directly used for simplicity. In addition, if you do not want to set the binlog offset of the main library, you can use the master_auto_position=1 parameter

After configuring the master-slave relationship, use the show slave status\ G; statement to view the master-slave synchronization status. The values of Slave_IO_Running and Slave_SQL_Running are both Yes to indicate that the master-slave synchronization status is normal:

After the master-slave relationship is configured, then test whether the master-slave data synchronization is normal. Execute some SQL statements on Master, as follows:

Mysql > create database test_db;mysql > use test_db;mysql > CREATE TABLE `student` (`id` int (11) NOT NULL, `name` varchar (20) NOT NULL, PRIMARY KEY (`id`)); mysql > INSERT INTO `test_ db`.`student` (`id`, `name`) VALUES (1, 'Jack')

When the execution is complete, see if there is a normal synchronization on the Slave:

Mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | | test_db | +-+ 5 rows in set (0.00 sec) mysql > use test_db;mysql > select * from student +-+-+ | id | name | +-+-+ | 1 | Jack | +-+-+ 1 row in set (0.00 sec) mysql >

After verifying that the master-slave node can synchronize data normally, we can set the synchronization delay of the Slave node. Execute the following statements on the Slave node:

Mysql > stop slave;mysql > change master to master_delay=1200;-set synchronization delay to 1200 seconds mysql > start slave

Similarly, after reconfiguring the master-slave relationship, you need to verify that the master-slave synchronization state is normal:

Simulated erroneous deletion of data

Then demonstrate the role of the delay node. First, go to the Master node, delete the data in the student table, and simulate the case of erroneous deletion:

Mysql > use test_db;mysql > delete from student;-- Delete all data in the student table mysql > select * from student;-- No data can be queried on Master Empty set (0.00 sec) mysql >

At this point, due to delayed synchronization, the deleted data can still be queried normally on the Slave node:

Mysql > use test_db;mysql > select * from student;+----+-+ | id | name | +-+-+ | 1 | Jack | +-+-- + 1 row in set (0.00 sec) mysql >

Now it's GTID's turn to play. We have to let the Slave node skip the GTID of the delete operation before Master can recover data from the Slave. Otherwise, if Slave synchronizes the GTID, the data on the Slave node will also be deleted, even if the Master data is restored before synchronization, it will cause the problem of master-slave data inconsistency.

GTID is recorded in binlog, and since the erroneous deletion operation is performed on Master, first use the show master logs; statement on the Master node to query the binlog log name:

Next, we need to find the record of the erroneous deletion operation and its GTID in the binlog file, because the sequence number of the binlog file is incremented, so the most recent operation is generally recorded in the binlog file with the largest sequence number. So execute the show binlog events in 'PXC-Node3-bin.000003'; statement and find the record of the erroneous delete operation and its GTID from the result set. As shown in the following figure:

When the GTID of the erroneous delete operation is found on the Master node, copy the GTID. Then execute the following statements on the Slave node:

Mysql > stop slave;-- stop master-slave synchronization mysql > set gtid_next='d36eaafb-c653-ee15-4458-5d6bc793bd7amysql 4synchronization;-- set the GTIDmysql > begin; commit; to be skipped-- start and commit the transaction, that is, if the simulation Slave synchronizes the GTID, it will not be synchronized later, thus achieving the skipping effect mysql > set gtid_next='automatic';-- restoring the setting of gtid > change master to master_delay=0 Setting the synchronization delay to 0 is to skip the GTIDmysql > start slave for synchronization immediately.

After the above operations are completed, the deleted data still exists on the Slave:

The student table on Master is still empty:

After completing the above operations, restore the setting of synchronization delay:

Mysql > stop slave;mysql > change master to master_delay=1200;-set synchronization delay to 1200 seconds mysql > start slave; to recover data mistakenly deleted by Master node

After letting the Slave node skip the GTID of the erroneous delete operation, you can begin to recover the data of the Master node. First of all, stop the read and write operations of the business system to the PXC cluster where the Master nodes are located to avoid data confusion in the process of restore. Then export the data for the Slave node:

Create a temporary library on the Master node to avoid accidents by first verifying the correctness of the data in the temporary library before importing it into the business library:

Create database temp_db

Then import the data:

Rename the data table on the Master node:

Rename table test_db.student to test_db.student_bak

Migrate the data tables from the temporary library to the business library:

Rename table temp_db.student to test_db.student

At this point, the mistakenly deleted data on the Master node is successfully restored:

Log flashback scheme

As mentioned earlier, in addition to the delay node solution, data recovery can also be achieved by using binlog logs, which is often referred to as log flashback. The reason why this scheme is also introduced here is that the delayed node scheme has some limitations: once the problem is not found and solved in the delay phase, then when the master-slave data is synchronized, it is also impossible to use slave nodes to achieve the recovery of erroneous deletion.

Compared with the delayed node scheme, the log flashback scheme is simpler, there is no need to add additional nodes, and the current node can be used to recover data. However, this scheme is not omnipotent. For example, the binlog log will not record the data deleted by drop table, truncate table and other operations, so it cannot be recovered through the log. However, the two schemes do not conflict and can be used at the same time to improve the possibility of data recovery.

The premise of log flashback is to open the binlog log, then parse the binlog log into SQL through some flashback tools, and then convert the delete statement in SQL into insert statement, or find the insert statement of the data that has been deleted by mistake. Finally, these insert statements are executed again in the database, thus achieving data recovery:

There are many flashback tools. Binlog2sql is used in this article, which is Dianping's open source MySQL log flashback tool based on Python.

Install binlog2sql

The installation steps for the tool are as follows:

# install the front tool [root@PXC-Node3 ~] # yum install-y epel-release [root@PXC-Node3 ~] # yum install-y git python3-pip# clone binlog2sql source code library, and enter the source directory [root@PXC-Node3 ~] # git clone https://github.com/danfengcao/binlog2sql.git & & cd binlog2sql# installation binlog2sql depends on the Python library [root@PXC-Node3 ~ / binlog2sql] # pip3 install-r requirements.txt

Configure the following parameters in the MySQL configuration file because binlog2sql is resolved based on binlog in row format:

[mysqld]... binlog_format = rowbinlog_row_image = full simulate erroneous deletion of data

I have a list of goods here, which contains the following data:

Use the delete statement to delete the data in the table to simulate the erroneous deletion:

Delete from flash.goods

Then insert some data to simulate the new data after being deleted by mistake:

INSERT INTO `flash`.`goods` (`id`, `name`, `room`, `status`) VALUES (6, 'Apple', 'xxxx',' 1'); INSERT INTO `flash`.`goods` (`id`, `name`, `goods`, `status`) VALUES (7, 'banana', 'xxxx',' 1'); restore data through binlog2sql

Preparations before recovery:

Stop applying read and write operations to the database and avoid overwriting the newly written hot backup database after restore, so as to ensure that the restore work is foolproof. You can refer to: various backup and restore postures of the database to clear all the records of the data tables that need to be restored, so as to avoid the conflict between primary key and unique key constraints.

Because you are restoring the commodity table, empty all the records of the commodity table:

Delete from flash.goods

As mentioned earlier, the most recent operations are generally recorded in the binlog file with the largest sequence number, so you have to query the binlog file name in the database:

Then use binlog2sql to parse the specified binlog log, as follows:

[root@PXC-Node3 ~ / binlog2sql] # python3 binlog2sql/binlog2sql.py-uadmin-paired Abcept 123456'-dflash-tgoods-- start-file='PXC-Node3-bin.000003' > / home/PXC-Node3-bin.000003.sqlbinlog2sql/binlog2sql.py: executed Python file-u: account used to connect to the database-p: password of the database account-d: specify the name of the logical library-t: specify the name of the data table-start- File: specify the file name of the binlog to be parsed / home/PXC-Node3-bin.000003.sql: specify the file to which the parsed generated SQL is written

Then take a look at the parsed SQL content: cat / home/PXC-Node3-bin.000003.sql. The useful parts are intercepted here, as shown in the following figure. You can see that both the delete statement and the insert statement have the data we want to recover:

It's easy to get these statements, either convert the delete statements into insert statements, or directly copy the SQL statements from the insert section to the database for execution. I'll just copy the insert statement here:

INSERT INTO `flash`.`goods` (`id`, `name`, `status`) VALUES (1, 'cake', 'yummy','1'); # start 3170 end 3363 time 2020-01-27 18:00:11INSERT INTO `flash`.`goods` (`id`, `name`, `status`) VALUES (2, 'lemon tea', 'enjoy smoking marijuana','1') # start 3459 end 3664 time 2020-01-27 18:00:56INSERT INTO `flash`.`goods` (`id`, `name`, `status`, `status`) VALUES (3, 'soy milk', 'delicious','0'); # start 3760 end 3953 time 2020-01-27 18:01:10INSERT INTO `flash`.goods` (`id`, `name`, `room`, `status`) VALUES (4, 'nest head', 'four for one yuan','1') # start 4049 end 4254 time 2020-01-27 18:01:37INSERT INTO `flash`.`goods` (`id`, `name`, `status`, `status`) VALUES (5, 'chicken leg', 'chicken you are too beautiful','0'); # start 4350 end 4549 time 2020-01-27 18:02:08INSERT INTO `flash`.goods` (`id`, `name`, `status`, `status`) VALUES (6, 'apple', 'status`') # start 5052 end 5243 time 2020-01-27 18:06:24INSERT INTO `flash`.`goods` (`id`, `name`, `status`) VALUES (7, 'banana', 'xxxx',' 1'); # start 5339 end 5530 time 2020-01-27 18:06:24

After executing the above SQL, you can see that the deleted data in the merchandise table has been recovered successfully:

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.