# 问题分析

1. 检查字符集
$ mysql -e “SHOW VARIABLES LIKE ‘character_set%’;”
+————————–+——–+
| Variable_name | Value |
+————————–+——–+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_server | utf8 |
+————————–+——–+

$ obclient -e “SHOW VARIABLES LIKE ‘character_set%’;”
+————————–+———+
| Variable_name | Value |
+————————–+———+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8mb4 |
| character_set_server | utf8mb4 |
+————————–+———+

# 发现字符集不一致

2. 查看乱码数据
$ obclient -e ”
SELECT user_name FROM fgedudb.fgedu_user LIMIT 5;
”
+———–+
| user_name |
+———–+
| ??? |
| ??? |
| ??? |
+———–+

3. 解决方案
# 步骤1：停止业务写入
# 步骤2：备份数据
$ obclient -e ”
CREATE TABLE fgedudb.fgedu_user_backup AS
SELECT * FROM fgedudb.fgedu_user;
”

# 步骤3：修改字符集
$ obclient -e ”
— 修改数据库字符集
ALTER DATABASE fgedudb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

— 修改表字符集
ALTER TABLE fgedudb.fgedu_user
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

— 修改连接字符集
SET GLOBAL character_set_client = utf8mb4;
SET GLOBAL character_set_connection = utf8mb4;
”

# 步骤4：重新导入数据
$ mysql -e ”
SELECT * FROM fgedudb.fgedu_user
INTO OUTFILE ‘/tmp/user_backup.csv’
CHARACTER SET utf8mb4;
”

$ obclient -e ”
LOAD DATA INFILE ‘/tmp/user_backup.csv’
INTO TABLE fgedudb.fgedu_user
CHARACTER SET utf8mb4;
”

# 步骤5：验证
$ obclient -e ”
SELECT user_name FROM fgedudb.fgedu_user LIMIT 5;
”
+———–+
| user_name |
+———–+
| 张三 |
| 李四 |
| 王五 |
+———–+

# 问题解决

# 问题分析

1. 检查迁移速度
$ omscli show-sync –project fgedu_migration
Progress: 10%
Speed: 1MB/s
ETA: 100 hours

# 速度远低于预期

2. 检查系统资源
$ top
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
10001 root 20 0 10g 8g 10m R 99.9 25.0 10:00 oms_worker

# CPU使用率100%

$ iostat -x 1
Device r/s w/s rkB/s wkB/s await %util
sda 10.0 100.0 100.0 1000.0 50.0 100.0

# 磁盘IO饱和

3. 检查网络
$ iftop
192.168.1.100 => 192.168.1.101 10Mb 10Mb 10Mb
<= 1Mb 1Mb 1Mb # 网络带宽不足 4. 解决方案 # 步骤1：降低并发度 $ omscli config-sync --project fgedu_migration \ --parallel=4 --rate-limit=50MB # 步骤2：优化源库 $ mysql -e " -- 降低binlog级别 SET GLOBAL binlog_row_image = MINIMAL; -- 优化查询 SET GLOBAL query_cache_size = 0; " # 步骤3：扩容网络 # 申请专线带宽升级 # 步骤4：分批迁移 # 按表分批，优先迁移小表 # 步骤5：监控优化 #!/bin/bash # performance_monitor.sh # from:www.itpux.com.qq113257174.wx:itpux-com while true; do SPEED=$(omscli show-sync | grep Speed | awk '{print $2}') if [ "$SPEED" -lt 10 ]; then echo "Speed too low: $SPEED MB/s" | mail -s "Migration Alert" dba@fgedu.net.cn fi sleep 60 done # 优化结果 Progress: 100% Speed: 50MB/s Actual Time: 20 hours # 问题解决

# 问题分析

1. 检查数据行数
$ mysql -e “SELECT COUNT(*) FROM fgedudb.fgedu_order;”
+———-+
| COUNT(*) |
+———-+
| 10000000 |
+———-+

$ obclient -e “SELECT COUNT(*) FROM fgedudb.fgedu_order;”
+———-+
| COUNT(*) |
+———-+
| 9995000 |
+———-+

# 缺失5000条数据

2. 分析缺失数据
$ mysql -e ”
SELECT order_id
FROM fgedudb.fgedu_order
WHERE order_id NOT IN (
SELECT order_id FROM fgedudb.fgedu_order
);
” > /tmp/missing_ids.txt

# 查看缺失数据特征
head /tmp/missing_ids.txt
5000001
5000002
5000003
…

# 发现是某个时间段的数据

3. 检查迁移日志
$ grep -i “error” /ob/app/oms/log/migration.log
[ERROR] Failed to insert row: order_id=5000001
[ERROR] Duplicate entry ‘5000001’ for key ‘PRIMARY’

# 发现主键冲突导致插入失败

4. 解决方案
# 步骤1：找出冲突原因
$ mysql -e ”
SELECT order_id, COUNT(*)
FROM fgedudb.fgedu_order
GROUP BY order_id
HAVING COUNT(*) > 1;
”

# 发现源库有重复数据

# 步骤2：清理源库重复数据
$ mysql -e ”
DELETE t1 FROM fgedudb.fgedu_order t1
INNER JOIN fgedudb.fgedu_order t2
WHERE t1.order_id = t2.order_id
AND t1.create_time < t2.create_time; " # 步骤3：重新迁移缺失数据 $ mysql -e " SELECT * FROM fgedudb.fgedu_order WHERE order_id BETWEEN 5000000 AND 5001000; " > /tmp/missing_data.csv

$ obclient -e ”
LOAD DATA INFILE ‘/tmp/missing_data.csv’
INTO TABLE fgedudb.fgedu_order;
”

# 步骤4：重新校验
$ ./row_count_check.sh
Table: fgedu_order
Source: 9995000
Target: 9995000
Status: PASSED

# 问题解决