MySQL如何对主从数据不一致的情况进行校验并继续同步
2023-09-11 14:21:14 时间
两台MySQL,发生了种种种种,导致了两个表的数据不一致,但是同步还在正常进行,后来意识到这种问题(可能之前skip啊,或者一开始搭建的时候就是不一致的状态),该如何修复呢?
我们看可以来看下percona-toolkit这个工具是如何修复这种情况的
主库:192.168.100.8 3306
从库:192.168.100.12 3305
MySQL version:MySQL-5.6.30
1.master 服务器安装yum依赖包
【MySQL学习笔记】添加数据、查询数据、修改数据、删除数据 MySQL中用insert语句向数据表中添加数据,根据操作的不同目的一般分为两种,一种是为所有字段添加数据,一种格式为部分字段添加数据。但如果要添加的数据过多,重复操作很麻烦,此时就需要一次添加多行数据。
yum install perl perl-devel perl-Time-HiRes perl-DBI perl-DBD-MySQL2.安装percona-toolkit工具包
wget http://www.percona.com/get/percona-toolkit.tar.gz tar zxf percona-toolkit-2.2.13.tar.gz cd percona-toolkit-2.2.13 perl Makefile.PL make make install3.master与slave数据库创建以及用户授权
Create database pt CHARACTER SET utf8; GRANT UPDATE,INSERT,DELETE,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO backup@192.168.100.8 identified by abc123; GRANT ALL ON pt.* TO backup@192.168.100.8 IDENTIFIED BY abc123; flush privileges;
use pt; CREATE TABLE IF NOT EXISTS checksums ( db char(64) NOT NULL, tbl char(64) NOT NULL, chunk int NOT NULL, chunk_time float NULL, chunk_index varchar(200) NULL, lower_boundary text NULL, upper_boundary text NULL, this_crc char(40) NOT NULL, this_cnt int NOT NULL, master_crc char(40) NULL, master_cnt int NULL, ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (db, tbl, chunk), INDEX ts_db_tbl (ts, db, tbl) ) ENGINE=InnoDB;4.进行校验 master上执行
pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --databases tmp -ubackup -pabc123 -h192.168.100.8 -P3306#-h -u -p -P -S -d 连接信息 #--nocheck-replication-filters 检测中忽略mysql 配置参数binlog_ignore_db等。 #--nocheck-binlog-format 不检测日志格式 #--replicate 指定checksum 存储的db和表, 如pt.checksum # --chunk-size, --chunk-size-limit 用于指定检测块的大小。 可控性更强 # --ignore-databases/tables/column 跳出指定元素的过滤 # --lock-wait-timeout innodb 锁的超时设定, 默认为1 # --max-load 设置最大并发连接数 # --replicate-check-only 只输出数据不一致的信息。 # --help
# A software update is available: # * The current version for Percona::Toolkit is 2.2.14. TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 07-21T12:21:59 0 0 3523 4 0 0.385 tmp.COMM_REGION 07-21T12:21:59 0 0 0 1 0 0.011 tmp.UCT_USER 07-21T12:22:04 0 0 115020 1 0 5.007 tmp.UCT_USER_1 07-21T12:22:04 0 0 0 1 0 0.017 tmp.UCT_USER_2 07-21T12:22:04 0 0 710 1 0 0.009 tmp.VOX_APPLICATION_MICROPHONE 07-21T12:22:04 0 0 3778 1 0 0.084 tmp.VOX_CLASSTS :完成检查的时间。 ERRORS :检查时候发生错误和警告的数量。 DIFFS :0表示一致,1表示不一致。当指定--no-replicate-check时,会一直为0,当指定--replicate-check-only会显示不同的信息。 ROWS :表的行数。 CHUNKS :被划分到表中的块的数目。 SKIPPED :由于错误或警告或过大,则跳过块的数目。 TIME :执行的时间。 TABLE :被检查的表名。 要是在执行命令的过程遇到找不到从服务器的错误:
Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.默认是通过show processlist 找到host的值或show slave hosts 找到host的值。 关于--recursion-method参数的设置有:
METHOD USES =========== ============================================= processlist SHOW PROCESSLIST hosts SHOW SLAVE HOSTS cluster SHOW STATUS LIKE wsrep\_incoming\_addresses dsn=DSN DSNs from a table none Do not find slaves
pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --recursion-method dsn=h=10.0.1.73,D=stats,t=dsns -ubackup -pabc123 --host=10.0.1.72 -P3306 --databases=HS_Order(校验)
[root@goufu data]# pt-table-sync --print --sync-to-master h=10.0.1.73,P=3306,u=backup,p=abc123 --replicate pt.checksums --databases=HS_Order --tables=VOX_REWARD_ORDER zzz.sql (也可直接加--execute自动修复)
mysql show slave hosts; +-----------+------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+------+-----------+--------------------------------------+ | 123305 | | 3305 | 533306 | 9645c4a3-178a-11e6-ac5d-d4ae52d034a7 | +-----------+------+------+-----------+--------------------------------------+ 1 row in set (0.00 sec)在从库的配置文件里加: report_host = 192.168.200.25 #设置成本地地址
mysql show slave hosts; +-----------+----------------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+----------------+------+-----------+--------------------------------------+ | 123305 | 192.168.100.12 | 3305 | 533306 | 9645c4a3-178a-11e6-ac5d-d4ae52d034a7 | +-----------+----------------+------+-----------+--------------------------------------+最后再执行以上命令(多加--recursion-method=hosts 参数):
pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --recursion-method=hosts --databases tmp -ubackup -pabc123 -h192.168.100.8 -P3306先校验出哪些库的表不同步,然后指定库与表生成语句,采用第二种方法, 将生成的SQL语句在从库执行即可。 5.数据同步,复制,消除差异(Master服务器运行) 要是有中文的则需要加上:--charset=utf8,防止乱码。 i.自动消除差异(不推荐)
pt-table-sync --print --execute --sync-to-master h=192.168.100.87,P=3341,u=backup,p=abc123 --databases=test --tables=goufuii.打印出sql语句,人工干预到Slave库执行(推荐)
[root@goufu data]# pt-table-sync --print --sync-to-master h=192.168.100.87,P=3341,u=goufu,p=abc123 --databases=test --tables=goufu REPLACE INTO `test`.`goufu`(`a`) VALUES (1) /*percona-toolkit src_db:test src_tbl:goufu src_dsn:P=3340,h=192.168.100.87,p=...,u=goufu dst_db:test dst_tbl:goufu dst_dsn:P=3341,h=192.168.100.87,p=...,u=backup lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:7020 user:root host:VM-TEST-87*/; pt-table-sync --print --sync-to-master h=10.1.1.7,P=3306,u=backup,p=abc123--replicate pt.checksums#--sync-to-master :指定一个DSN,即从的IP,他会通过show processlist或show slave status 去自动的找主。 #--replicate :指定通过pt-table-checksum得到的表,这2个工具差不多都会一直用。 #--print :打印,但不执行命令。 #--execute :执行命令。 7.实际解决:
[root@goufu ~]# pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --recursion-method=hosts --databases bbs,HomeworkSitter_UAT --tables=dz_common_session,VOX_VOICE_RECOMMEND_DATA_SENTENCE_REF -u backup -pgoufu -h192.168.100.8 -P3306 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 07-21T15:37:03 0 2 106683 4 0 0.588 HomeworkSitter_UAT.VOX_VOICE_RECOMMEND_DATA_SENTENCE_REF 07-21T15:37:03 0 1 3 1 0 0.026 bbs.dz_common_session由于其中一个表的数据较大,采用dump导出的方式在从库上恢复 1.第一个执行采用pt-table-sync方式恢复
pt-table-sync --print --sync-to-master h=192.168.100.12,P=3305,u=goufu,p=abc123 --databases=bbs --tables=dz_common_session2.第二个表采用导出导入的方式恢复
pt-table-sync --print --sync-to-master h=192.168.100.12,P=3305,u=goufu,p=abc123 --databases=bbs --tables=dz_common_session同步完成后再校验:
[root@goufu ~]# pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --recursion-method=hosts --databases bbs,HomeworkSitter_UAT --tables=dz_common_session,VOX_VOICE_RECOMMEND_DATA_SENTENCE_REF -ubackup -pabc123 -h192.168.100.8 -P3306 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 07-21T16:50:19 0 0 106683 5 0 1.006 HomeworkSitter_UAT.VOX_VOICE_RECOMMEND_DATA_SENTENCE_REF 07-21T16:50:19 0 0 3 1 0 0.013 bbs.dz_common_session1.采用replace into来修复主从不一致,必须保证被replace的表上有主键或唯一键,否则replace into退化成insert into,起不到修复的效果。这种情况下pt-table-sync会采用其他校验和修复算法,但是效率非常低,例如对所有列的group by然后求count(*)(表一定要有主键!)。 2.主从数据不一致需要通过replace into来修复,该sql语句必须是语句级。pt-table-sync会把它发起的所有sql语句都设置为statement格式,而不管全局的binlog_format值。这在级联A-B-C结构中,也会遇到pt-table-checksum曾经遇到的问题,引起行格式的中继库的从库卡库是必然。不过pt-table-sync默认会无限递归的对从库的binlog格式进行检查并警告。 3.由于pt-table-sync每次只能修复一个表,所以如果修复的是父表,则可能导致子表数据连带被修复,这可能会修复一个不一致而引入另一个不一致;如果表上有触发器,也可能遇到同样问题。所以在有触发器和主外键约束的情况下要慎用。pt-table-sync工具同样也不欢迎主从异构的结构。pt-table-sync工具默认会进行先决条件的检查。 4.pt-table-sync在修复过程中不能容忍从库延迟,这正好与pt-table-checksum相反。如果从库延迟太多,pt-table-sync会长期持有对chunk的for update锁,然后等待从库的master_pos_wait执行完毕或超时。从库延迟越大,等待过程就越长,主库加锁的时间就越长,对线上影响就越大。因此要严格设置max-lag。 5.对从库数据的修复通常是在主库执行sql来同步到从库。因此,在有多个从库时,修复某个从库的数据实际会把修复语句同步到所有从库。数据修复的代价取决于从库与主库不一致的程度,如果某从库数据与主库非常不一致,举例说,这个从库只有表结构,那么需要把主库的所有数据重新灌一遍,然后通过binlog同步,同时会传递到所有从库。这会给线上带来很大压力,甚至拖垮集群。正确的做法是,先用pt-table-checksum校验一遍,确定不一致的程度:如果不同步的很少,用pt-table-sync直接修复;否则,用备份先替换它,然后用pt-table-sync修复。 说明: 这实际提供了一种对myisam备份的思路:如果仅有一个myisam的主库,要为其增加从库,则可以:先mysqldump出表结构到从库上,然后启动同步,然后用pt-table-sync来修复数据。
【MySQL学习笔记】添加数据、查询数据、修改数据、删除数据 MySQL中用insert语句向数据表中添加数据,根据操作的不同目的一般分为两种,一种是为所有字段添加数据,一种格式为部分字段添加数据。但如果要添加的数据过多,重复操作很麻烦,此时就需要一次添加多行数据。
相关文章
- Mysql加锁过程详解(3)-关于mysql 幻读理解
- Sqoop将MySQL表结构同步到hive(text、orc)
- MySQL 存储引擎
- 【MySQL高级】MySql中常用工具及Mysql 日志
- Mysql中与时间相关的统计分析
- 【MySQL进阶-09】深入理解mysql执行的底层机制
- Mysql mysql lost connection to server during query 问题解决方法
- 【mysql问题】解决2003-Can‘t connect to MySQL server on ‘ ‘(10060“Unknown error“)
- Mysql高可用架构(主从同步)
- mac ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
- 使用navicat连接mysql连接错误:Lost connection to Mysql server at 'waiting for initial communication packet'
- MYSQL导入csv类型的数据出现The MySQL server is running with the --secure-file-priv option
- solr与mysql数据同步的方案
- 四、Mysql主从同步
- 【Docker】Docker安装MySQL,并解决中文乱码和配置数据备份同步到宿主机
- MySQL 上亿大表如何优化?
- 有关Mysql的mysql_store_result函数返回NULL的情况以及其他注意事项
- MySQL数据的主从复制、半同步复制和主主复制详解-转
- 配置Mysql数据库主从同步
- 两种方法解决MySQL主从不同步
- (5.8)mysql高可用系列——MySQL中的GTID复制(实践篇)
- (5.5)mysql高可用系列——MySQL半同步复制(实践)
- MySql 主从同步 (库名不同)
- MYSQL --延时同步-恢复数据
- mysql-数据库的主从同步,实现读写分离
- 同步mysql部分表数据到docker容器中的mysql数据库供其他程序使用,附实际例子源码和镜像
- MySQL UTF8 编码下生僻字符插入失败/假死问题的分析/ETL 同步数据失败
- Ambari组件状态从MySQL同步到SQLServer或者MySQL
- DataX 同步mysql到clickhouse