SQL数据库所有表数据传输实战指南从零基础到高效迁移的完整解决方案与常见问题排查 – 资料库

引言：数据库迁移的重要性与挑战
在现代软件开发和运维中，SQL数据库的数据传输和迁移是一项常见但复杂的任务。无论是从开发环境迁移到生产环境、从本地数据库迁移到云端，还是进行数据库版本升级，数据传输都扮演着关键角色。根据行业统计，超过70%的企业每年至少进行一次数据库迁移操作，而其中约30%的项目会遇到数据丢失、性能瓶颈或兼容性问题。
数据传输不仅仅是简单的复制粘贴，它涉及数据完整性保障、性能优化、错误处理等多个维度。一个成功的迁移方案需要考虑源数据库和目标数据库的版本兼容性、网络带宽限制、数据量大小、业务连续性要求等因素。例如，对于一个包含数亿条记录的电商平台数据库，如果采用简单的
mysqldump
全量导出导入，可能需要数小时甚至数天时间，这期间业务将无法正常运行。
本文将从零基础开始，系统性地介绍SQL数据库表数据传输的完整流程，涵盖主流数据库（MySQL、PostgreSQL、SQL Server）的迁移方案，提供详细的代码示例和实战技巧，并深入分析常见问题及其排查方法。无论您是数据库管理员、开发人员还是运维工程师，都能从本文中获得实用的指导。
第一部分：数据传输基础概念
1.1 数据传输的定义与场景
数据传输（Data Transfer）是指将数据从一个数据库系统移动到另一个数据库系统的过程。这个过程可以是同构的（如MySQL到MySQL），也可以是异构的（如Oracle到PostgreSQL）。常见的应用场景包括：
环境迁移
：开发环境 → 测试环境 → 生产环境
硬件升级
：旧服务器 → 新服务器
云迁移
：本地数据库 → 云数据库（如AWS RDS、阿里云RDS）
数据库重构
：单体数据库 → 分库分表
数据同步
：主从复制、读写分离、灾备恢复
1.2 数据传输的核心挑战
数据一致性
：确保迁移过程中数据不丢失、不重复、不损坏
性能瓶颈
：大数据量下的传输速度优化
业务连续性
：最小化停机时间（RTO）
兼容性问题
：不同数据库版本、字符集、数据类型的映射
安全性
：数据加密、访问控制、审计日志
1.3 数据传输的基本流程
一个标准的数据传输流程通常包括以下步骤：
需求分析
：评估数据量、业务影响、迁移目标
环境准备
：网络配置、权限设置、存储空间检查
数据备份
：源数据库全量备份，确保可回滚
方案设计
：选择迁移工具和方法（逻辑迁移 vs 物理迁移）
测试验证
：在测试环境进行完整演练
执行迁移
：正式执行数据传输
数据校验
：对比源和目标数据，确保一致性
业务切换
：应用连接切换到新数据库
监控优化
：迁移后性能监控和调优
第二部分：主流数据库迁移方案详解
2.1 MySQL数据库迁移
2.1.1 使用mysqldump进行逻辑迁移
mysqldump
是MySQL官方提供的逻辑备份工具，适合中小数据量（<100GB）的迁移场景。基础用法： # 导出整个数据库 mysqldump -h localhost -u root -p --databases mydb > mydb_backup.sql

# 导出单表
mysqldump -h localhost -u root -p mydb users > users_table.sql

# 导出所有数据库
mysqldump -h localhost -u root -p –all-databases > all_backup.sql
高级参数优化：
# 使用单事务模式减少锁表时间
mysqldump -h localhost -u root -p –single-transaction –quick –lock-tables=false mydb > mydb_backup.sql

# 包含存储过程和函数
mysqldump -h localhost -u root -p –routines –triggers mydb > mydb_backup.sql

# 压缩输出
mysqldump -h localhost -u root -p mydb | gzip > mydb_backup.sql.gz
导入数据：
# 解压并导入
gunzip < mydb_backup.sql.gz | mysql -h localhost -u root -p mydb # 或者直接导入 mysql -h localhost -u root -p mydb < mydb_backup.sql 实战案例：假设我们有一个电商数据库 ecommerce ，包含用户表 users 、订单表 orders 和商品表 products ，总数据量约50GB。 # 步骤1：在源数据库上导出 mysqldump -h 192.168.1.100 -u root -p \ --single-transaction \ --quick \ --routines \ --triggers \ --skip-lock-tables \ ecommerce > ecommerce_backup.sql

# 步骤2：传输到目标服务器
scp ecommerce_backup.sql root@192.168.1.200:/tmp/

# 步骤3：在目标数据库上导入
mysql -h 192.168.1.200 -u root -p -e “CREATE DATABASE IF NOT EXISTS ecommerce;”
mysql -h 192.168.1.200 -u root -p ecommerce < /tmp/ecommerce_backup.sql 2.1.2 使用Percona XtraBackup进行物理迁移对于大数据量（>100GB）或需要最小化停机时间的场景，推荐使用Percona XtraBackup进行物理备份。
安装Percona XtraBackup：
# Ubuntu/Debian
sudo apt-get install percona-xtrabackup-80

# CentOS/RHEL
sudo yum install percona-xtrabackup-80
创建备份：
# 全量备份
xtrabackup –backup –user=root –password=your_password –target-dir=/backup/full/

# 准备备份
xtrabackup –prepare –target-dir=/backup/full/
传输备份：
# 压缩传输
tar -czf – /backup/full/ | ssh root@192.168.1.200 “tar -xzf – -C /backup/”
恢复数据：
# 停止MySQL服务
systemctl stop mysql

# 备份原数据目录
mv /var/lib/mysql /var/lib/mysql_backup

# 恢复数据
xtrabackup –copy-back –target-dir=/backup/full/ –datadir=/var/lib/mysql

# 修改权限
chown -R mysql:mysql /var/lib/mysql

# 启动MySQL
systemctl start mysql
2.1.3 使用MySQL Shell进行并行迁移
MySQL 8.0+提供了强大的Shell工具，支持并行导出导入，大幅提升迁移效率。
安装MySQL Shell：
# Ubuntu/Debian
sudo apt-get install mysql-shell

# CentOS/RHEL
sudo yum install mysql-shell
并行导出：
mysqlsh root@192.168.1.100:3306 — util dumpSchemas ecommerce /backup/ecommerce_dump \
–threads=8 \
–compression=GZIP \
–consistent=false
并行导入：
mysqlsh root@192.168.1.200:3306 — util loadDump /backup/ecommerce_dump \
–threads=8 \
–compression=GZIP \
–schema=ecommerce
2.2 PostgreSQL数据库迁移
2.2.1 使用pg_dump进行逻辑迁移
pg_dump
是PostgreSQL的标准备份工具，支持选择性备份和并行导出。
基础用法：
# 导出整个数据库
pg_dump -h localhost -U postgres -Fc mydb > mydb_backup.dump

# 导出单表
pg_dump -h localhost -U postgres -Fc -t users mydb > users_table.dump

# 并行导出（PostgreSQL 10+）
pg_dump -h localhost -U postgres -Fc -j 4 -f mydb_backup.dump mydb
导入数据：
# 创建目标数据库
createdb -h localhost -U postgres mydb

# 恢复数据
pg_restore -h localhost -U postgres -d mydb -j 4 mydb_backup.dump
2.2.2 使用pg_basebackup进行物理迁移
对于需要时间点恢复的场景，使用
pg_basebackup
进行物理备份。
# 创建基础备份
pg_basebackup -h localhost -U postgres -D /backup/pg_basebackup -Ft -z -P -X stream

# 传输到目标服务器
scp /backup/pg_basebackup/* root@192.168.1.200:/var/lib/postgresql/backup/

# 在目标服务器恢复
systemctl stop postgresql
rm -rf /var/lib/postgresql/12/main/*
tar -xzf /var/lib/postgresql/backup/base.tar.gz -C /var/lib/postgresql/12/main/
tar -xzf /var/lib/postgresql/backup/pg_wal.tar.gz -C /var/lib/postgresql/12/main/
chown -R postgres:postgres /var/lib/postgresql/12/main/
systemctl start postgresql
2.3 SQL Server数据库迁移
2.3.1 使用SQL Server Management Studio (SSMS)
SSMS提供了图形化界面进行数据库迁移，适合初学者。
步骤：
在SSMS中右键点击源数据库 → Tasks → Export Data-tier Application
保存为.bacpac文件
在目标服务器上，右键点击Databases → Import Data-tier Application
选择.bacpac文件进行导入
2.3.2 使用bcp工具进行批量迁移
bcp
是SQL Server的命令行批量复制工具，适合大数据量迁移。
导出数据：
# 导出表数据到CSV
bcp “SELECT * FROM ecommerce.dbo.users” queryout “C:\backup\users.csv” -c -t, -S “localhost” -T

# 导出整个数据库（需要编写脚本循环导出所有表）
导入数据：
# 从CSV导入数据
bcp ecommerce.dbo.users in “C:\backup\users.csv” -c -t, -S “localhost” -T
2.3.3 使用Always On Availability Groups
对于企业级高可用迁移，可以使用Always On Availability Groups实现零停机迁移。
配置步骤：
— 在主副本上创建可用性组
CREATE AVAILABILITY GROUP [AG_Migration]
WITH (CLUSTER_TYPE = NONE)
FOR DATABASE ecommerce
WITH (
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
);

— 添加监听器
ALTER AVAILABILITY GROUP [AG_Migration]
ADD LISTENER ‘AG_Migration_Listener’ (WITH IP ((‘192.168.1.200’, ‘255.255.255.0’)), PORT = 1433);

— 在辅助副本上连接并加入组
ALTER AVAILABILITY GROUP [AG_Migration] JOIN WITH (CLUSTER_TYPE = NONE);
第三部分：高效迁移的最佳实践
3.1 迁移前的准备工作
3.1.1 环境检查清单
#!/bin/bash
# 数据库迁移前检查脚本

echo “=== 数据库迁移前检查清单 ===”

# 1. 检查源数据库连接
echo “1. 检查源数据库连接…”
mysql -h $SOURCE_HOST -u $SOURCE_USER -p$SOURCE_PASS -e “SELECT 1;” || exit 1

# 2. 检查目标数据库连接
echo “2. 检查目标数据库连接…”
mysql -h $TARGET_HOST -u $TARGET_USER -p$TARGET_PASS -e “SELECT 1;” || exit 1

# 3. 检查磁盘空间（源端需要2倍数据量）
echo “3. 检查磁盘空间…”
SOURCE_SIZE=$(mysql -h $SOURCE_HOST -u $SOURCE_USER -p$SOURCE_PASS -N -e “SELECT SUM(data_length+index_length)/1024/1024/1024 FROM information_schema.tables WHERE table_schema=’$DB_NAME’;”)
AVAILABLE_SPACE=$(df /backup | awk ‘NR==2 {print $4/1024/1024}’)
if (( $(echo “$AVAILABLE_SPACE < $SOURCE_SIZE * 2" | bc -l) )); then echo "ERROR: 磁盘空间不足！需要${SOURCE_SIZE}GB，可用${AVAILABLE_SPACE}GB" exit 1 fi # 4. 检查网络带宽 echo "4. 检查网络带宽..." ping -c 3 $TARGET_HOST || exit 1 # 5. 检查数据库版本兼容性 echo "5. 检查数据库版本..." SOURCE_VERSION=$(mysql -h $SOURCE_HOST -u $SOURCE_USER -p$SOURCE_PASS -N -e "SELECT VERSION();") TARGET_VERSION=$(mysql -h $TARGET_HOST -u $TARGET_USER -p$TARGET_PASS -N -e "SELECT VERSION();") echo "源版本: $SOURCE_VERSION" echo "目标版本: $TARGET_VERSION" echo "=== 检查完成 ===" 3.1.2 数据备份策略 3-2-1备份原则： 3份数据副本 2种不同存储介质 1份异地备份 MySQL全量+增量备份示例： # 全量备份 xtrabackup --backup --user=root --password=pass --target-dir=/backup/full_$(date +%Y%m%d) # 增量备份（基于上一次全量） xtrabackup --backup --user=root --password=pass --target-dir=/backup/inc1 --incremental-basedir=/backup/full_20240101 # 准备备份（合并增量） xtrabackup --prepare --apply-log-only --target-dir=/backup/full_20240101 xtrabackup --prepare --apply-log-only --target-dir=/backup/full_20240101 --incremental-dir=/backup/inc1 3.2 迁移过程中的性能优化 3.2.1 并行处理 MySQL Shell并行导出示例： # 根据CPU核心数设置线程数 THREADS=$(nproc) mysqlsh root@source_host:3306 -- util dumpSchemas \ --threads=$THREADS \ --compression=GZIP \ --consistent=false \ --skip-binlog \ --includeSchemas="ecommerce" \ /backup/ecommerce_dump 3.2.2 网络传输优化使用rsync进行增量传输： # 首次全量传输 rsync -avz --progress /backup/ecommerce_dump/ root@target_host:/backup/ecommerce_dump/ # 后续增量传输（仅传输变化部分） rsync -avz --progress --delete /backup/ecommerce_dump/ root@target_host:/backup/ecommerce_dump/ 使用tar流式传输（避免中间存储）： # 源服务器 tar -czf - /backup/ecommerce_dump/ | ssh root@target_host "tar -xzf - -C /backup/" # 或者使用nc进行大文件传输 # 源服务器 tar -czf - /backup/ecommerce_dump/ | nc -l 9999 # 目标服务器 nc source_host 9999 | tar -xzf - -C /backup/ 3.2.3 数据库参数调优迁移期间MySQL参数优化： # my.cnf 配置 [mysqld] # 增加缓冲区大小 innodb_buffer_pool_size = 4G # 根据内存调整，通常为总内存的50-70% # 禁用双写缓冲（提升写入性能，迁移后恢复） innodb_doublewrite = 0 # 增加日志文件大小 innodb_log_file_size = 2G # 并行复制（如果使用） slave_parallel_workers = 8 # 禁用查询缓存（MySQL 8.0已移除） query_cache_type = 0 query_cache_size = 0 # 增加最大连接数 max_connections = 500 3.3 数据一致性校验 3.3.1 行数校验 MySQL表行数对比脚本： #!/bin/bash # 对比源和目标数据库表行数 SOURCE_HOST="192.168.1.100" TARGET_HOST="192.168.1.200" DB_NAME="ecommerce" # 获取源数据库所有表及行数 mysql -h $SOURCE_HOST -u root -p -N -e "SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA='$DB_NAME';" > /tmp/source_counts.txt

# 获取目标数据库所有表及行数
mysql -h $TARGET_HOST -u root -p -N -e “SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA=’$DB_NAME’;” > /tmp/target_counts.txt

# 对比并输出差异
echo “=== 表行数差异报告 ===”
join -j 1 /tmp/source_counts.txt /tmp/target_counts.txt | awk ‘{if($2 != $3) print $1″: 源=”$2″ 目标=”$3}’
3.3.2 数据校验工具
使用pt-table-checksum（Percona Toolkit）：
# 安装Percona Toolkit
sudo apt-get install percona-toolkit

# 执行校验
pt-table-checksum h=source_host,u=root,p=pass –databases=ecommerce

# 输出示例：
# TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
# 2024-01-01T10:00:00 0 1 100000 2 0 0.501 ecommerce.users
# 2024-01-01T10:00:01 0 0 500000 5 0 1.200 ecommerce.orders
自定义数据校验脚本（MD5校验）：
#!/usr/bin/env python3
import hashlib
import mysql.connector

def get_table_md5(host, user, password, database, table):
“””计算表数据的MD5值”””
conn = mysql.connector.connect(
host=host,
user=user,
password=password,
database=database
)
cursor = conn.cursor()

# 获取表的所有列名
cursor.execute(f”SHOW COLUMNS FROM {table}”)
columns = [col[0] for col in cursor.fetchall()]

# 按主键排序并拼接数据
cursor.execute(f”SELECT * FROM {table} ORDER BY id”)
rows = cursor.fetchall()

# 计算MD5
md5 = hashlib.md5()
for row in rows:
row_str = ‘|’.join(str(col) for col in row)
md5.update(row_str.encode(‘utf-8’))

cursor.close()
conn.close()
return md5.hexdigest()

# 对比源和目标
source_md5 = get_table_md5(‘192.168.1.100’, ‘root’, ‘pass’, ‘ecommerce’, ‘users’)
target_md5 = get_table_md5(‘192.168.1.200’, ‘root’, ‘pass’, ‘ecommerce’, ‘users’)

if source_md5 == target_md5:
print(f”✓ users表数据一致”)
else:
print(f”✗ users表数据不一致！源MD5: {source_md5}, 目标MD5: {target_md5}”)
第四部分：常见问题排查与解决方案
4.1 连接与权限问题
4.1.1 连接超时
问题现象：
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘host’ (110)
排查步骤：
# 1. 检查网络连通性
ping target_host

# 2. 检查端口是否开放
telnet target_host 3306
# 或使用nc
nc -zv target_host 3306

# 3. 检查防火墙
sudo ufw status
sudo iptables -L

# 4. 检查MySQL服务状态
systemctl status mysql
sudo netstat -tlnp | grep 3306

# 5. 检查MySQL配置（bind-address）
sudo grep bind-address /etc/mysql/my.cnf
# 应该为 0.0.0.0 或注释掉
解决方案：
— 在MySQL中创建允许远程访问的用户
CREATE USER ‘迁移用户’@’%’ IDENTIFIED BY ‘强密码’;
GRANT ALL PRIVILEGES ON *.* TO ‘迁移用户’@’%’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

— 修改MySQL配置
— /etc/mysql/my.cnf
[mysqld]
bind-address = 0.0.0.0
4.1.2 权限不足
问题现象：
ERROR 1044 (42000): Access denied for user ‘user’@’%’ to database ‘db’
排查与修复：
— 查看用户权限
SHOW GRANTS FOR ‘user’@’%’;

— 授予必要权限
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX ON *.* TO ‘user’@’%’;
GRANT ALL PRIVILEGES ON migration_db.* TO ‘user’@’%’;
FLUSH PRIVILEGES;

— 如果是迁移系统表，需要额外权限
GRANT RELOAD, REPLICATION CLIENT, SUPER ON *.* TO ‘user’@’%’;
4.2 数据完整性问题
4.2.1 字符集不匹配
问题现象：
迁移后中文显示为乱码
表情符号显示为问号
排查：
— 查看数据库字符集
SHOW VARIABLES LIKE ‘character_set_database’;
SHOW VARIABLES LIKE ‘collation_database’;

— 查看表字符集
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ‘ecommerce’;

— 查看列字符集
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = ‘ecommerce’ AND CHARACTER_SET_NAME IS NOT NULL;
解决方案：
# 导出时指定字符集
mysqldump -h source_host -u root -p \
–default-character-set=utf8mb4 \
–set-charset \
ecommerce > ecommerce_utf8mb4.sql

# 导入时指定字符集
mysql -h target_host -u root -p \
–default-character-set=utf8mb4 \
ecommerce < ecommerce_utf8mb4.sql # 或在SQL文件开头添加 sed -i '1s/^/SET NAMES utf8mb4;\n/' ecommerce_utf8mb4.sql 4.2.2 自增主键冲突问题现象： ERROR 1062 (23000): Duplicate entry '12345' for key 'PRIMARY' 解决方案： -- 方案1：重置自增ID（适用于允许ID重新分配的场景） -- 导入前在目标数据库执行 SET FOREIGN_KEY_CHECKS = 0; ALTER TABLE users AUTO_INCREMENT = 1; SET FOREIGN_KEY_CHECKS = 1; -- 方案2：保留原始ID（推荐） -- 使用mysqldump的--no-create-info和--insert-ignore mysqldump -h source_host -u root -p --no-create-info --insert-ignore ecommerce users > users_data.sql

— 或者使用REPLACE INTO
mysqldump -h source_host -u root -p –no-create-info –replace ecommerce users > users_data.sql
4.3 性能问题
4.3.1 导入速度慢
问题现象：
导入100GB数据需要数小时
CPU使用率低，磁盘IO不高
排查与优化：
1. 检查慢查询日志：
# 启用慢查询日志
sudo tee -a /etc/mysql/my.cnf < import_tables.sh <<'EOF' #!/bin/bash TABLES=$(mysql -h source_host -u root -p -N -e "SHOW TABLES FROM ecommerce") for table in $TABLES; do mysqldump -h source_host -u root -p ecommerce $table > /tmp/${table}.sql &
wait
mysql -h target_host -u root -p ecommerce < /tmp/${table}.sql & done wait EOF # 使用xargs并行执行 echo $TABLES | xargs -P 4 -I {} sh -c 'mysqldump -h source_host -u root -p ecommerce {} | mysql -h target_host -u root -p ecommerce' 3. 调整目标数据库参数： # 临时调整（导入期间） SET GLOBAL innodb_flush_log_at_trx_commit = 2; -- 默认为1，改为2提升性能 SET GLOBAL sync_binlog = 0; -- 禁用binlog同步 SET GLOBAL unique_checks = 0; -- 禁用唯一性检查 SET GLOBAL foreign_key_checks = 0; -- 禁用外键检查 4.3.2 内存不足问题现象： ERROR 2013 (HY000): Lost connection to MySQL server during query 解决方案： # 调整max_allowed_packet [mysqld] max_allowed_packet = 1G # 调整net_buffer_length net_buffer_length = 1M # 增加临时表大小 tmp_table_size = 256M max_heap_table_size = 256M 4.4 兼容性问题 4.4.1 数据类型不兼容常见问题： MySQL的 UNSIGNED 类型在PostgreSQL中无直接对应 SQL Server的 DATETIME2 与MySQL的 DATETIME 精度差异 JSON类型在不同数据库中的实现差异解决方案： -- MySQL UNSIGNED INT → PostgreSQL BIGINT -- 原表 CREATE TABLE users ( id INT UNSIGNED PRIMARY KEY, name VARCHAR(50) ); -- 迁移后 CREATE TABLE users ( id BIGINT PRIMARY KEY CHECK (id >= 0), — 添加检查约束
name VARCHAR(50)
);

— JSON类型迁移
— MySQL
CREATE TABLE products (
id INT,
attributes JSON
);

— PostgreSQL
CREATE TABLE products (
id INT,
attributes JSONB — 使用JSONB提升性能
);
4.4.2 存储引擎差异
MySQL InnoDB → PostgreSQL：
— MySQL的AUTO_INCREMENT
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY
);

— PostgreSQL的SERIAL
CREATE TABLE users (
id SERIAL PRIMARY KEY
);

— 或使用IDENTITY（PostgreSQL 10+）
CREATE TABLE users (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
4.5 迁移后验证
4.5.1 完整性验证清单
#!/bin/bash
# 迁移后验证脚本

echo “=== 迁移后验证清单 ===”

# 1. 数据库对象数量对比
echo “1. 对比表数量…”
SOURCE_TABLES=$(mysql -h source_host -u root -p -N -e “SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=’ecommerce’;”)
TARGET_TABLES=$(mysql -h target_host -u root -p -N -e “SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=’ecommerce’;”)
echo “源表数量: $SOURCE_TABLES, 目标表数量: $TARGET_TABLES”

# 2. 数据行数对比
echo “2. 对比数据行数…”
mysql -h source_host -u root -p -N -e “SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA=’ecommerce’;” > /tmp/source_rows.txt
mysql -h target_host -u root -p -N -e “SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA=’ecommerce’;” > /tmp/target_rows.txt

DIFFS=$(join -j 1 /tmp/source_rows.txt /tmp/target_rows.txt | awk ‘{if($2 != $3) print $1″: 源=”$2″ 目标=”$3}’ | wc -l)
if [ $DIFFS -eq 0 ]; then
echo “✓ 所有表行数一致”
else
echo “✗ 发现$DIFFS个表行数不一致”
join -j 1 /tmp/source_rows.txt /tmp/target_rows.txt | awk ‘{if($2 != $3) print $1″: 源=”$2″ 目标=”$3}’
fi

# 3. 关键数据抽样验证
echo “3. 关键数据抽样验证…”
mysql -h source_host -u root -p -N -e “SELECT COUNT(*), MD5(GROUP_CONCAT(CONCAT(id,username,created_at) ORDER BY id)) FROM ecommerce.users LIMIT 1000;” > /tmp/source_sample.txt
mysql -h target_host -u root -p -N -e “SELECT COUNT(*), MD5(GROUP_CONCAT(CONCAT(id,username,created_at) ORDER BY id)) FROM ecommerce.users LIMIT 1000;” > /tmp/target_sample.txt

if diff /tmp/source_sample.txt /tmp/target_sample.txt > /dev/null; then
echo “✓ users表抽样数据一致”
else
echo “✗ users表抽样数据不一致”
fi

# 4. 索引验证
echo “4. 验证索引…”
mysql -h target_host -u root -p -N -e “SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=’ecommerce’;” > /tmp/target_indexes.txt
echo “目标数据库索引数量: $(cat /tmp/target_indexes.txt)”

echo “=== 验证完成 ===”
4.5.2 业务验证
— 验证关键业务查询性能
EXPLAIN SELECT COUNT(*) FROM orders WHERE created_at > ‘2024-01-01’;
EXPLAIN SELECT * FROM users WHERE email = ‘test@example.com’;
EXPLAIN SELECT o.*, u.username FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = ‘paid’;

— 验证存储过程和触发器
SHOW CREATE PROCEDURE update_order_status;
SHOW CREATE TRIGGER before_order_insert;

— 验证外键约束
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = ‘ecommerce’;
第五部分：高级迁移场景
5.1 零停机迁移方案
5.1.1 双写方案
架构设计：
应用层 → 写入源数据库
→ 写入目标数据库（异步）

数据同步：使用Canal/Debezium监听binlog，同步到目标库
实现步骤：
# 应用层双写示例（Python）
import mysql.connector
from threading import Thread
import queue

class DualWriter:
def __init__(self, source_config, target_config):
self.source_conn = mysql.connector.connect(**source_config)
self.target_conn = mysql.connector.connect(**target_config)
self.write_queue = queue.Queue()

def write_both(self, sql, params=None):
# 写入源库
cursor = self.source_conn.cursor()
cursor.execute(sql, params)
self.source_conn.commit()
cursor.close()

# 异步写入目标库
self.write_queue.put((sql, params))

def target_writer_worker(self):
while True:
sql, params = self.write_queue.get()
try:
cursor = self.target_conn.cursor()
cursor.execute(sql, params)
self.target_conn.commit()
cursor.close()
except Exception as e:
print(f”目标库写入失败: {e}”)
# 记录失败操作，后续补偿
self.write_queue.task_done()

# 使用示例
writer = DualWriter(
source_config={‘host’: ‘192.168.1.100’, ‘user’: ‘root’, ‘password’: ‘pass’, ‘database’: ‘ecommerce’},
target_config={‘host’: ‘192.168.1.200’, ‘user’: ‘root’, ‘password’: ‘pass’, ‘database’: ‘ecommerce’}
)

# 启动目标库写入线程
Thread(target=writer.target_writer_worker, daemon=True).start()

# 业务代码调用
writer.write_both(“INSERT INTO orders (user_id, amount) VALUES (%s, %s)”, (123, 99.99))
5.1.2 使用数据库复制
MySQL主从复制迁移：
— 在目标库（作为从库）执行
CHANGE MASTER TO
MASTER_HOST=’192.168.1.100′,
MASTER_USER=’repl’,
MASTER_PASSWORD=’repl_pass’,
MASTER_LOG_FILE=’mysql-bin.000001′,
MASTER_LOG_POS=456;

START SLAVE;

— 监控复制状态
SHOW SLAVE STATUS\G
— 确保 Seconds_Behind_Master = 0
切换步骤：
# 1. 停止应用写入
# 2. 确认从库延迟为0
mysql -h target_host -u root -p -e “SHOW SLAVE STATUS\G” | grep Seconds_Behind_Master

# 3. 停止从库复制
mysql -h target_host -u root -p -e “STOP SLAVE; RESET SLAVE ALL;”

# 4. 将应用配置指向目标库
# 5. 启动应用
5.2 异构数据库迁移
5.2.1 MySQL → PostgreSQL
使用pgloader工具：
# 安装pgloader
sudo apt-get install pgloader

# 创建迁移脚本
cat > migrate_mysql_to_pg.load < /tmp/${TABLE}_${current_date}.sql

# 导入分片
mysql -h target_host -u root -p $DB_NAME < /tmp/${TABLE}_${current_date}.sql current_date=$next_date done 5.3.2 使用外部工具使用DataX（阿里云开源）： { "job": { "content": [{ "reader": { "name": "mysqlreader", "parameter": { "username": "root", "password": "pass", "connection": [{ "querySql": ["SELECT * FROM orders WHERE created_at >= ‘2023-01-01′”]
}]
}
},
“writer”: {
“name”: “postgresqlwriter”,
“parameter”: {
“username”: “postgres”,
“password”: “pass”,
“connection”: [{
“table”: [“orders”]
}]
}
}
}],
“setting”: {
“speed”: {
“channel”: 8
}
}
}
}
第六部分：迁移工具对比与选型
6.1 工具对比表
工具
适用数据库
数据量
停机时间
学习成本
推荐场景
mysqldump
MySQL
<100GB 长低开发测试、小数据量 xtrabackup MySQL >100GB
短
中
生产环境、大数据量
MySQL Shell
MySQL 8.0+
任意
短
中
MySQL 8.0+迁移
pg_dump
PostgreSQL
<100GB 长低开发测试、小数据量 pg_basebackup PostgreSQL >100GB
短
中
生产环境、大数据量
DataX
跨数据库
任意
短
高
异构迁移、ETL
Debezium
跨数据库
任意
零
高
实时同步、零停机
6.2 选型决策树
graph TD
A[开始选型] –> B{数据量?}
B –>|< 100GB| C{同构?} B -->|> 100GB| D{同构?}
C –>|是| E[mysqldump/pg_dump]
C –>|否| F[DataX/自定义脚本]
D –>|是| G{xtrabackup/pg_basebackup}
D –>|否| H[Debezium/DataX]
E –> I[评估停机时间]
F –> I
G –> I
H –> I
I –>|可接受| J[执行迁移]
I –>|不可接受| K[双写/复制方案]
第七部分：迁移后的监控与优化
7.1 性能监控指标
MySQL监控脚本：
#!/bin/bash
# 监控MySQL迁移后性能

while true; do
echo “=== $(date) ===”

# 查询响应时间
mysql -h target_host -u root -p -e “SHOW GLOBAL STATUS LIKE ‘Slow_queries’;”

# 连接数
mysql -h target_host -u root -p -e “SHOW GLOBAL STATUS LIKE ‘Threads_connected’;”

# 缓存命中率
mysql -h target_host -u root -p -e “SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_read%’;” | \
awk ‘NR==2{reads=$2} NR==3{hit_rate=100-($2/(reads+$2)*100); print “Buffer Pool Hit Rate: ” hit_rate “%”}’

# 磁盘IO
iostat -x 1 3 | tail -n 3

sleep 60
done
7.2 慢查询优化
分析迁移后慢查询：
— 开启慢查询日志
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

— 分析慢查询日志
mysqldumpslow /var/log/mysql/slow.log

— 或使用pt-query-digest
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

— 检查缺失的索引
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;
7.3 定期维护任务
创建维护脚本：
— 优化表（定期执行）
OPTIMIZE TABLE users, orders, products;

— 分析表统计信息
ANALYZE TABLE users, orders, products;

— 检查表完整性
CHECK TABLE users, orders, products FOR UPGRADE;
第八部分：安全最佳实践
8.1 数据加密
传输加密：
# 使用SSL加密传输
mysqldump –ssl-mode=REQUIRED -h source_host -u root -p ecommerce > backup.sql

# 或使用SSH隧道
ssh -L 3307:localhost:3306 user@source_host
mysqldump -h 127.0.0.1 -P 3307 -u root -p ecommerce > backup.sql
静态加密：
# 使用GPG加密备份
mysqldump -h source_host -u root -p ecommerce | gpg –cipher-algo AES256 –compress-algo 1 –symmetric –output backup.sql.gpg

# 解密
gpg –decrypt backup.sql.gpg | mysql -h target_host -u root -p ecommerce
8.2 审计与日志
启用MySQL审计：
# my.cnf
[mysqld]
plugin-load-add = audit_log.so
audit_log_format = JSON
audit_log_file = /var/log/mysql/audit.log
audit_log_policy = ALL
记录迁移操作：
# 创建迁移日志
exec > >(tee -a /var/log/migration.log)
exec 2>&1

echo “=== 迁移开始: $(date) ===”
echo “源数据库: $SOURCE_HOST”
echo “目标数据库: $TARGET_HOST”
echo “操作用户: $(whoami)”

# 执行迁移命令…

echo “=== 迁移结束: $(date) ===”
第九部分：故障恢复方案
9.1 快速回滚计划
回滚脚本：
#!/bin/bash
# 迁移回滚脚本

# 1. 停止应用
systemctl stop myapp

# 2. 恢复数据库连接到源库
# 修改应用配置
sed -i ‘s/target_host/source_host/g’ /etc/myapp/config.ini

# 3. 如果已经切换到目标库，需要回滚数据
# 使用之前备份的源库数据
mysql -h source_host -u root -p ecommerce < /backup/source_pre_migration.sql # 4. 启动应用 systemctl start myapp echo "回滚完成，请验证业务" 9.2 数据修复方案数据不一致修复： -- 1. 识别不一致的数据 -- 假设users表的id 123在源和目标值不同 SELECT * FROM users WHERE id = 123; -- 在源和目标分别执行 -- 2. 使用pt-table-sync修复（Percona Toolkit） pt-table-sync --execute h=source_host,u=root,p=pass h=target_host,u=root,p=pass --databases=ecommerce --tables=users -- 3. 或者手动修复 -- 在目标库执行 UPDATE users SET email = (SELECT email FROM source_db.users WHERE id = 123) WHERE id = 123; 第十部分：总结与最佳实践清单 10.1 迁移前检查清单 [ ] 确认数据量大小和增长趋势 [ ] 检查源和目标数据库版本兼容性 [ ] 验证网络带宽和延迟 [ ] 确保目标服务器磁盘空间充足（至少2倍数据量） [ ] 创建完整备份并验证可恢复性 [ ] 准备回滚方案 [ ] 通知相关业务方迁移时间窗口 [ ] 准备监控告警 10.2 迁移中最佳实践 [ ] 使用 --single-transaction 减少锁表 [ ] 启用压缩减少网络传输 [ ] 分批导入避免内存溢出 [ ] 实时监控迁移进度 [ ] 保留详细日志用于排查 [ ] 定期检查数据一致性 10.3 迁移后验证清单 [ ] 表数量和行数对比 [ ] 关键数据抽样验证 [ ] 索引和约束完整性检查 [ ] 存储过程和触发器验证 [ ] 业务查询性能测试 [ ] 应用连接测试 [ ] 监控指标正常 [ ] 备份策略重新配置 10.4 常见误区避免忽视字符集问题：务必统一使用utf8mb4 跳过备份直接迁移：永远先备份再操作低估数据量：实际数据量可能比统计的大忽略权限问题：提前测试连接和权限不验证数据一致性：迁移后必须进行数据校验没有回滚计划：准备随时回滚的方案附录：常用命令速查表 MySQL # 导出 mysqldump -h host -u user -p --single-transaction db > backup.sql

# 导入
mysql -h host -u user -p db < backup.sql # 并行导出（MySQL Shell） mysqlsh user@host:3306 -- util dumpSchemas db /path/to/dump --threads=8 PostgreSQL # 导出 pg_dump -h host -U user -Fc db > backup.dump

# 导入
pg_restore -h host -U user -d db -j 4 backup.dump
SQL Server
# 导出
bcp “SELECT * FROM db.dbo.table” queryout “table.csv” -c -S server -T

# 导入
bcp db.dbo.table in “table.csv” -c -S server -T
通用工具
# 数据传输
rsync -avz /backup/ user@host:/backup/

# 压缩传输
tar -czf – /backup/ | ssh user@host “tar -xzf – -C /backup/”

# 进度监控
pv /backup/backup.sql | mysql -h host -u user -p db
通过本文的详细指导，您应该能够应对大多数SQL数据库迁移场景。记住，成功的迁移关键在于充分的准备、详细的规划和严格的验证。在实际操作中，建议先在测试环境进行完整演练，确保万无一失再进行生产环境迁移。