网站定制 北京,wordpress管理账户,北京城建集团官网,房屋装修设计培训学校背景
在GreatSQL主从复制环境中#xff0c;有时候可能会出现一些误操作#xff0c;将本应该写入到主库的数据写入到了从库#xff0c;导致主从数据不一致#xff0c;影响数据同步。是否可以将写入从库的数据同步写入主库呢#xff1f;
测试环境
角色IP地址数据库开放端…背景
在GreatSQL主从复制环境中有时候可能会出现一些误操作将本应该写入到主库的数据写入到了从库导致主从数据不一致影响数据同步。是否可以将写入从库的数据同步写入主库呢
测试环境
角色IP地址数据库开放端口版本主库192.168.137.1793308GreatSQL 8.0.32从库192.168.137.1803308GreatSQL 8.0.32
复制链路
greatsql show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.137.179Master_User: rootMaster_Port: 3308Connect_Retry: 60Master_Log_File: binlog.000001Read_Master_Log_Pos: 157Relay_Log_File: oracle_dts-relay-bin.000002Relay_Log_Pos: 367Relay_Master_Log_File: binlog.000001Slave_IO_Running: YesSlave_SQL_Running: Yes
表数据
主库
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: 0greatsql 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)
主库写入的数据正常同步到从库
在从库写入数据
greatsql insert into dept select 80,IT,SZ;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0greatsql insert into dept select 90,SALES,SZ;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
从库数据
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)
主库数据
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)
此时从库写入的数据在主库中并没有出现
解析从库的二进制日志
$ mysqlbinlog -vv --base64-outputdecode-rows binlog.000002b002.sqlBEGIN
/*!*/;#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_key0
#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
###180 /* INT meta0 nullable0 is_null0 */
###2IT /* VARSTRING(56) meta56 nullable1 is_null0 */
###3SZ /* VARSTRING(52) meta52 nullable1 is_null0 */
#at 462
#240221 16:10:25 server id 18001 end_log_pos 493 CRC32 0xab795e4a Xid 34
可以看到写入的从库写入的数据在 binlog.000002我们可以通过 grep 从库的 server id 确定日志文件中有没有在从库写入的数据。
复制从库日志到主库
$ scp binlog.000002 192.168.137.179:/tmp/
Warning: Permanently added 192.168.137.179 (ECDSA) to the list of known hosts.
root192.168.137.179s password:
binlog.000002 100% 836 1.1MB/s 00:00
应用从库的二进制日志
应用从库的日志到主库
$ mysqlbinlog binlog.000002|mysql -uroot -p -h127.1 -P3308
主库应用从库二进制日志时从库二进制日志信息未发生变化
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)
主从复制链路状态正常
greatsql show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.137.179Master_User: rootMaster_Port: 3308Connect_Retry: 60Master_Log_File: binlog.000001Read_Master_Log_Pos: 1059Relay_Log_File: oracle_dts-relay-bin.000002Relay_Log_Pos: 1269Relay_Master_Log_File: binlog.000001Slave_IO_Running: YesSlave_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 replicas server ID (this optimization helps save disk usage). )出现主键冲突导致复制状态出错
查看主库数据
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)
后续测试主库写入数据可正常同步到从库。 Enjoy GreatSQL :)
关于 GreatSQL
GreatSQL是适用于金融级应用的国内自主开源数据库具备高性能、高可靠、高易用性、高安全等多个核心特性可以作为MySQL或Percona Server的可选替换用于线上生产环境且完全免费并兼容MySQL或Percona Server。
相关链接 GreatSQL社区 Gitee GitHub Bilibili
GreatSQL社区 社区有奖建议反馈 https://greatsql.cn/thread-54-1-1.html
社区博客有奖征稿详情 https://greatsql.cn/thread-100-1-1.html
对文章有疑问或者有独到见解都可以去社区官网提出或分享哦~
技术交流群
微信QQ群
QQ群533341697
微信群添加GreatSQL社区助手微信号wanlidbc 好友待社区助手拉您进群。
