Slave被誤寫入數(shù)據(jù)如何恢復(fù)到主庫
作者：GreatSQL
在GreatSQL主從復(fù)制環(huán)境中，有時(shí)候可能會出現(xiàn)一些誤操作，將本應(yīng)該寫入到主庫的數(shù)據(jù)寫入到了從庫，導(dǎo)致主從數(shù)據(jù)不一致，影響數(shù)據(jù)同步。是否可以將寫入從庫的數(shù)據(jù)同步寫入主庫呢？
背景
在GreatSQL主從復(fù)制環(huán)境中，有時(shí)候可能會出現(xiàn)一些誤操作，將本應(yīng)該寫入到主庫的數(shù)據(jù)寫入到了從庫，導(dǎo)致主從數(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 |
復(fù)制鏈路：
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í)從庫寫入的數(shù)據(jù)在主庫中并沒有出現(xiàn)
解析從庫的二進(jì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ù)。
復(fù)制從庫日志到主庫
$ 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應(yīng)用從庫的二進(jìn)制日志
應(yīng)用從庫的日志到主庫
$ mysqlbinlog binlog.000002|mysql -uroot -p -h127.1 -P3308主庫應(yīng)用從庫二進(jìn)制日志時(shí)，從庫二進(jìn)制日志信息未發(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)主從復(fù)制鏈路狀態(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可以看到主庫在應(yīng)用從庫產(chǎn)生的二進(jìn)制日志時(shí)，從庫沒有重復(fù)應(yīng)用這些二進(jìn)制日志(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)主鍵沖突，導(dǎo)致復(fù)制狀態(tài)出錯(cuò)
查看主庫數(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ù)可正常同步到從庫。
責(zé)任編輯：武曉燕
來源：
GreatSQL社區(qū)
