tidb-091-TiDB迁移数据一致性校验

权限准备：

# 在源数据库中创建校验用户
CREATE USER ‘check_user’@’%’ IDENTIFIED BY ‘password’;
GRANT SELECT ON *.* TO ‘check_user’@’%’;

# 在目标数据库中创建校验用户
CREATE USER ‘check_user’@’%’ IDENTIFIED BY ‘password’;
GRANT SELECT ON *.* TO ‘check_user’@’%’;

工具准备：

安装必要的校验工具
准备校验脚本
配置校验环境

3.2 数据量校验

# 源数据库数据量
mysql -h 192.168.1.5 -P 3306 -u check_user -p -e “SELECT table_schema, table_name, table_rows FROM information_schema.tables WHERE table_schema NOT IN (‘information_schema’, ‘mysql’, ‘performance_schema’, ‘sys’);” > source_counts.txt

# 目标数据库数据量
mysql -h 192.168.1.20 -P 4000 -u check_user -p -e “SELECT table_schema, table_name, table_rows FROM information_schema.tables WHERE table_schema NOT IN (‘information_schema’, ‘mysql’, ‘performance_schema’, ‘sys’);” > target_counts.txt

# 对比数据量
diff source_counts.txt target_counts.txt

3.3 数据内容校验

3.3.1 使用Checksum

# 源数据库Checksum
mysql -h 192.168.1.5 -P 3306 -u check_user -p -e “CHECKSUM TABLE test.table;”

# 目标数据库Checksum
mysql -h 192.168.1.20 -P 4000 -u check_user -p -e “CHECKSUM TABLE test.table;”

3.3.2 使用自定义脚本

#!/bin/bash

# 源数据库连接信息
SOURCE_HOST="192.168.1.5"
SOURCE_PORT="3306"
SOURCE_USER="check_user"
SOURCE_PASS="password"
SOURCE_DB="test"

# 目标数据库连接信息
TARGET_HOST="192.168.1.20"
TARGET_PORT="4000"
TARGET_USER="check_user"
TARGET_PASS="password"
TARGET_DB="test"

# 要校验的表
TABLES=("table1" "table2" "table3")

for TABLE in "${TABLES[@]}"; do
    echo "校验表: $TABLE"
    
    # 获取源数据
    mysql -h $SOURCE_HOST -P $SOURCE_PORT -u $SOURCE_USER -p$SOURCE_PASS -e "SELECT * FROM $SOURCE_DB.$TABLE ORDER BY id;" > source_$TABLE.txt
    
    # 获取目标数据
    mysql -h $TARGET_HOST -P $TARGET_PORT -u $TARGET_USER -p$TARGET_PASS -e "SELECT * FROM $TARGET_DB.$TABLE ORDER BY id;" > target_$TABLE.txt
    
    # 对比数据
    DIFF_RESULT=$(diff source_$TABLE.txt target_$TABLE.txt)
    if [ -z "$DIFF_RESULT" ]; then学习交流加群风哥QQ113257174
        echo "✓ 表 $TABLE 数据一致"
    else
        echo "✗ 表 $TABLE 数据不一致"
        echo "差异: $DIFF_RESULT"
    fi
    
    # 清理临时文件
    rm source_$TABLE.txt target_$TABLE.txt
done

3.4 索引和约束校验

# 源数据库索引
mysql -h 192.168.1.5 -P 3306 -u check_user -p -e “SHOW INDEX FROM test.table;” > source_indexes.txt

# 目标数据库索引
mysql -h 192.168.1.20 -P 4000 -u check_user -p -e “SHOW INDEX FROM test.table;” > target_indexes.txt

# 对比索引
diff source_indexes.txt target_indexes.txt

# 源数据库约束
mysql -h 192.168.1.5 -P 3306 -u check_user -p -e “SHOW CREATE TABLE test.table;” > source_schema.txt

# 目标数据库约束
mysql -h 192.168.1.20 -P 4000 -u check_user -p -e “SHOW CREATE TABLE test.table;” > target_schema.txt

# 对比约束
diff source_schema.txt target_schema.txt

3.5 性能校验

# 源数据库性能测试
mysql -h 192.168.1.5 -P 3306 -u check_user -p -e “SET profiling = 1; SELECT * FROM test.table WHERE id = 1; SHOW PROFILES;”

# 目标数据库性能测试
mysql -h 192.168.1.20 -P 4000 -u check_user -p -e “SET profiling = 1; SELECT * FROM test.table WHERE id = 1; SHOW PROFILES;”

更多视频教程www.fgedu.net.cn

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html