Slave被誤寫入數(shù)據(jù)如何恢復到主庫

作者：GreatSQL 2024-02-26 09:46:04

在GreatSQL主從復制環(huán)境中，有時候可能會出現(xiàn)一些誤操作，將本應該寫入到主庫的數(shù)據(jù)寫入到了從庫，導致主從數(shù)據(jù)不一致，影響數(shù)據(jù)同步。是否可以將寫入從庫的數(shù)據(jù)同步寫入主庫呢？

背景

測試環(huán)境

角色	IP地址	數(shù)據(jù)庫開放端口	版本
主庫	192.168.137.179	3308	GreatSQL 8.0.32
從庫	192.168.137.180	3308	GreatSQL 8.0.32

復制鏈路：

greatsql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.137.179
                  Master_User: root
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 157
               Relay_Log_File: oracle_dts-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

表數(shù)據(jù)

主庫

greatsql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     60 | it         | 成都     |
+--------+------------+----------+
5 rows in set (0.00 sec)

greatsql> insert into dept select 70,'IT','CTU';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

greatsql> commit;
Query OK, 0 rows affected (0.00 sec)

從庫

greatsql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     60 | it         | 成都     |
|     70 | IT         | CTU      |
+--------+------------+----------+
6 rows in set (0.00 sec)

主庫寫入的數(shù)據(jù)正常同步到從庫

在從庫寫入數(shù)據(jù)

greatsql> insert into dept select 80,'IT','SZ';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

greatsql> insert into dept select 90,'SALES','SZ';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

從庫數(shù)據(jù)

greatsql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     60 | it         | 成都     |
|     70 | IT         | CTU      |
|     80 | IT         | SZ       |
|     90 | SALES      | SZ       |
+--------+------------+----------+
8 rows in set (0.00 sec)

主庫數(shù)據(jù)

greatsql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     60 | it         | 成都     |
|     70 | IT         | CTU      |
+--------+------------+----------+
6 rows in set (0.01 sec)

此時從庫寫入的數(shù)據(jù)在主庫中并沒有出現(xiàn)

解析從庫的二進制日志

$ mysqlbinlog -vv --base64-output=decode-rows  binlog.000002>b002.sql

 BEGIN
/*!*/;

#at 354
#240221 16:10:25 server id 18001  end_log_pos 416 CRC32 0xcc81584b      Table_map: `scott`.`dept` mapped to number 101
#has_generated_invisible_primary_key=0
#at 416
#240221 16:10:25 server id 18001  end_log_pos 462 CRC32 0x5149e38a      Write_rows: table id 101 flags:
 STMT_END_F

###INSERT INTO `scott`.`dept`
###SET
###@1=80 /* INT meta=0 nullable=0 is_null=0 */
###@2='IT' /* VARSTRING(56) meta=56 nullable=1 is_null=0 */
###@3='SZ' /* VARSTRING(52) meta=52 nullable=1 is_null=0 */
#at 462
#240221 16:10:25 server id 18001  end_log_pos 493 CRC32 0xab795e4a      Xid = 34

可以看到寫入的從庫寫入的數(shù)據(jù)在 binlog.000002，我們可以通過 grep 從庫的 server id 確定日志文件中有沒有在從庫寫入的數(shù)據(jù)。

復制從庫日志到主庫

$ scp binlog.000002  192.168.137.179:/tmp/
Warning: Permanently added '192.168.137.179' (ECDSA) to the list of known hosts.
root@192.168.137.179's password: 
binlog.000002                                                        100%  836     1.1MB/s   00:00

應用從庫的二進制日志

應用從庫的日志到主庫

$ mysqlbinlog binlog.000002|mysql -uroot -p -h127.1 -P3308

主庫應用從庫二進制日志時，從庫二進制日志信息未發(fā)生變化

greatsql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       498 | No        |
| binlog.000002 |       836 | No        |
| binlog.000003 |       237 | No        |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)

主從復制鏈路狀態(tài)正常

greatsql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.137.179
                  Master_User: root
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 1059
               Relay_Log_File: oracle_dts-relay-bin.000002
                Relay_Log_Pos: 1269
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

可以看到主庫在應用從庫產生的二進制日志時，從庫沒有重復應用這些二進制日志(By default, the replication I/O (receiver) thread does not write binary log events to the relay log if they have the replica's server ID (this optimization helps save disk usage). )，出現(xiàn)主鍵沖突，導致復制狀態(tài)出錯

查看主庫數(shù)據(jù)

greatsql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     60 | it         | 成都     |
|     70 | IT         | CTU      |
|     80 | IT         | SZ       |
|     90 | SALES      | SZ       |
+--------+------------+----------+
8 rows in set (0.00 sec)

后續(xù)測試，主庫寫入數(shù)據(jù)可正常同步到從庫。

責任編輯：武曉燕來源： GreatSQL社區(qū)

Slave 數(shù)據(jù)GreatSQL