Mysql 主从库数据在线差异比对流程
1. 获取需要比对表的列名、主键等信息
SET sql_mode=“”; # 需要忽略多个主键的情况，只拿其中一个主键
SELECT pk , COLUMNS , a.`TABLE_NAME` , a.`TABLE_SCHEMA`
FROM
(SELECT group_concat(`COLUMN_NAME`) as COLUMNS, `TABLE_SCHEMA`, `TABLE_NAME` FROM `information_schema`.COLUMNS GROUP BY `TABLE_SCHEMA`, `TABLE_NAME` ) a
,
(SELECT `COLUMN_NAME` AS pk , `TABLE_SCHEMA`, `TABLE_NAME` FROM `information_schema`.COLUMNS WHERE COLUMN_KEY="PRI" ) b
WHERE a.`TABLE_SCHEMA` = b.`TABLE_SCHEMA`AND a.`TABLE_NAME` =b. `TABLE_NAME`
2. 组装为查询SQL 。 Tips:以下步骤需要重复多次，可以减少误差
# 分大范围的按每1000行一次计算结果 及小范围的按每10行一次计算结果
SELECT concat( "SELECT '",sch,".",tab,"' as `table_name` , min(",pk,") as min_id, max(",pk,") as max_id, count(1) as row_count, MD5(GROUP_CONCAT(MD5(CONCAT(IFNULL(",col,",''))) ORDER BY ",pk,"))AS md5_value FROM ",sch,".",tab," GROUP BY (",pk," DIV 1000) ;” ) sql
FROM
(
SELECT pk , REPLACE(COLUMNS , "," , ",''),IFNULL(") col , a.`TABLE_NAME` tab , a.`TABLE_SCHEMA` sch
FROM
(SELECT group_concat(`COLUMN_NAME`) AS COLUMNS, `TABLE_SCHEMA`, `TABLE_NAME` FROM `information_schema`.COLUMNS GROUP BY `TABLE_SCHEMA`, `TABLE_NAME` ) a
,
(SELECT `COLUMN_NAME` AS pk , `TABLE_SCHEMA`, `TABLE_NAME` FROM `information_schema`.COLUMNS WHERE COLUMN_KEY="PRI" ) b
WHERE a.`TABLE_SCHEMA` = b.`TABLE_SCHEMA`AND a.`TABLE_NAME` =b. `TABLE_NAME`
) oo
3. 主从库同时执行查询SQL，将查询结果写入结果表，Tips: 在数据库低峰期间运行，可以减少差异数量
CREATE TABLE `somedb_tms_columns_md5` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`tablename` varchar(200) NOT NULL,
`min_id` bigint(11) NOT NULL,
`max_id` bigint(11) NOT NULL,
`row_count` int(11) NOT NULL,
`md5_value` char(64) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_md5_minid_tabname` (`md5_value`,`min_id`,`tablename`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO aliyun_tms_columns_md5 (tablename , min_id ,
max_id ,
row_count ,
md5_value )
SQL union all SQL # 步骤2 生成的SQL
4. 主从库同步运行后， 2个结果表 做差异比对。
SELECT
-- DISTINCT a.tablename
a.* , i.row_count , i.md5_value
-- concat("SELECT * from ",a.tablename," where id between ", a.min_id, " and " , a.max_id ," union all")
FROM dba_tms_columns_md5 a LEFT JOIN dbb_tms_columns_md5 i ON i.tablename = a.tablename AND i.`min_id` = a.`min_id`
WHERE
i.`md5_value` != a.`md5_value`
5. 根据差异行，将主库数据 replace insert 到从库中
Tips： 查询主数据时，先停止从库的 sql_thread , 避免旧数据覆盖新数据。
