MySQL同步工具比选

留言

https://www.percona.com/blog/dump-performance-comparison-mysqldump-vs-mysql-shell-utilities-vs-mydumper/


我们之前用的mysqldump..看起来速度最差

现在用的mysqlshell, 是mysql官方推出的,用C写的一个工具~


20241014

mysqlsh –js -utaskon_rw_rolling -ppassport -h172.22.0.33 -P3306 -e “util.dumpSchemas([‘taskon_community’], ‘/data/mysql-to-ck/newversion/clickhouse-sink-connector/sink-connector/python/downloadtaskonprod_community_daily’);” 这样导数据会导致cpu过高，mysql压力也很大，如何能降低数据导出的频次？花费的时间可是适度延长

在使用 mysqlsh 导出 MySQL 数据时，如果 CPU 负载过高且 MySQL 压力较大，你可以通过以下几种方式来降低数据导出的频次，同时适度延长数据导出的时间。这些方法可以帮助减少对数据库的冲击。

1. 使用 --threads 参数控制导出并发线程数

mysqlsh 的 util.dumpSchemas 方法支持 threads 参数，可以通过减少并发线程数来降低对系统资源（CPU、磁盘I/O等）的占用。

你可以通过 maxThreads 参数设置最大并发线程数，减少并发度来减轻 MySQL 的负载：

1
2
mysqlsh --js -utaskon_rw_rolling -ppassport -h172.22.0.33 -P3306 \
 -e "util.dumpSchemas(['taskon_community'], '/data/mysql-to-ck/newversion/clickhouse-sink-connector/sink-connector/python/downloadtaskonprod_community_daily', { threads: 2 });"

此处 threads: 2 设置导出任务最多使用2个线程，默认值可能是系统CPU逻辑核心数。你可以根据服务器的负载情况尝试不同的线程数。

2. 使用 --compress 参数减少 I/O

数据导出过程中，I/O 也是数据库和系统的压力来源。可以启用压缩来减少 I/O 负担：

1
2
mysqlsh --js -utaskon_rw_rolling -ppassport -h172.22.0.33 -P3306 \
 -e "util.dumpSchemas(['taskon_community'], '/data/mysql-to-ck/newversion/clickhouse-sink-connector/sink-connector/python/downloadtaskonprod_community_daily', { threads: 2, compress: true });"

compress: true 会启用数据压缩来减少磁盘和网络I/O的压力，代价是会稍微增加一些CPU开销，但整体效果通常是正面的。

3. 分批导出数据

如果数据库表特别大，可以分批导出数据，避免一次性全量导出。你可以使用定期小批量导出的方法，结合 --where 参数来过滤数据，或者在应用层实现分批导出逻辑。例如导出过去一天的数据：

1
2
mysqlsh --js -utaskon_rw_rolling -ppassport -h172.22.0.33 -P3306 \
 -e "util.dumpSchemas(['taskon_community'], '/data/mysql-to-ck/newversion/clickhouse-sink-connector/sink-connector/python/downloadtaskonprod_community_daily', { threads: 2, where: 'date >= CURDATE() - INTERVAL 1 DAY' });"

这样可以减少一次性导出的数据量，降低数据库压力。

4. 使用 --no-locks 降低表锁定时间

util.dumpSchemas 默认会锁定表以确保一致性。如果你可以容忍一些微小的不一致性（例如对实时性要求不高的场景），可以通过 consistent: false 选项禁用表锁定：

1
2
mysqlsh --js -utaskon_rw_rolling -ppassport -h172.22.0.33 -P3306 \
 -e "util.dumpSchemas(['taskon_community'], '/data/mysql-to-ck/newversion/clickhouse-sink-connector/sink-connector/python/downloadtaskonprod_community_daily', { threads: 2, consistent: false });"

这会减少表锁定时间，从而降低对数据库的负载。

5. 调整 MySQL 配置

如果你有权限修改 MySQL 的配置文件，可以考虑以下几种优化以减少 MySQL 压力：

  • innodb_flush_log_at_trx_commit
    将这个值从 1 改为 2 或 0，可以减少磁盘I/O。这个配置决定了事务提交时日志的刷新频率。

  • innodb_buffer_pool_size
    确保 innodb_buffer_pool_size 足够大，以便更多数据能够缓存到内存中，减少磁盘I/O。

  • max_connections
    确保你的 MySQL 实例有足够的连接数上限，以处理导出任务和其他应用的并发连接。

总结

可以通过以下几点来降低 MySQL 导出时的压力：

  1. 降低并发线程数，使用 { threads: N } 参数。
  2. 启用数据压缩，使用 { compress: true } 参数减少I/O。
  3. 分批导出数据，通过 where 语句控制每次导出的数据量。
  4. 禁用表锁定，使用 { consistent: false } 来减少锁定时间。
  5. 优化MySQL配置，特别是I/O和内存相关的参数。

根据你的实际情况，可以灵活组合这些方法，来减少导出数据时的负载。

能否改为按表导？每导出一张表sleep几秒钟？

你可以通过脚本的方式按表导出，并在导出每张表后使用 sleep 函数暂停几秒钟。这样可以有效降低数据库的负载，同时让导出过程更平稳。

下面是一个示例脚本，展示如何按表导出，并在每次导出后暂停几秒钟（假设你使用的是 bash 脚本）：

示例 Bash 脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
#!/bin/bash

# MySQL 登录信息
USER="taskon_rw_rolling"
PASSWORD="passport"
HOST="172.22.0.33"
PORT="3306"
SCHEMA="taskon_community"
OUTPUT_DIR="/data/mysql-to-ck/newversion/clickhouse-sink-connector/sink-connector/python/downloadtaskonprod_community_daily"
SLEEP_SECONDS=5

# 获取所有表名
TABLES=$(mysqlsh --js -u$USER -p$PASSWORD -h$HOST -P$PORT \
  -e "var schema = session.getSchema('$SCHEMA'); schema.getTables().map(table => table.getName());")

# 循环遍历每个表并进行导出
for TABLE in $TABLES; do
  echo "正在导出表: $TABLE"
  
  # 导出当前表
  mysqlsh --js -u$USER -p$PASSWORD -h$HOST -P$PORT \
    -e "util.dumpTables(['$TABLE'], '$OUTPUT_DIR/$TABLE')"
  
  # 检查导出是否成功
  if [ $? -eq 0 ]; then
    echo "表 $TABLE 导出成功，等待 $SLEEP_SECONDS 秒..."
  else
    echo "表 $TABLE 导出失败，跳过等待..."
  fi

  # 等待指定时间
  sleep $SLEEP_SECONDS
done

echo "所有表导出完成！"

脚本说明

  1. MySQL 登录信息：
    你需要提供正确的用户名（$USER）、密码（$PASSWORD）、主机地址（$HOST）、端口（$PORT）以及数据库名称（$SCHEMA）。

  2. 获取表列表：
    脚本使用以下命令获取数据库中的所有表：

    1
    TABLES=$(mysqlsh --js -u$USER -p$PASSWORD -h$HOST -P$PORT -e "var schema = session.getSchema('$SCHEMA'); schema.getTables().map(table => table.getName());")

    这会返回一个表名列表。

  3. 按表导出：
    使用 util.dumpTables() 方法导出每张表，并将其保存到指定的输出目录。

    1
    2
    mysqlsh --js -u$USER -p$PASSWORD -h$HOST -P$PORT \
      -e "util.dumpTables(['$TABLE'], '$OUTPUT_DIR/$TABLE')"

  4. 导出后等待：
    每次导出一张表后，脚本会调用 sleep $SLEEP_SECONDS，暂停几秒（你可以通过修改 $SLEEP_SECONDS 来调整等待时间）。

  5. 错误处理：
    如果某张表导出失败，脚本会跳过等待并继续导出下一个表。通过检查上一条命令的返回码 $? 实现这个逻辑。

运行脚本

  1. 将这个脚本保存为一个 .sh 文件，例如 export_tables.sh。

  2. 给予脚本执行权限：

    1
    chmod +x export_tables.sh

  3. 运行脚本：

    1
    ./export_tables.sh

其他优化

  • 并发控制：
    如果希望进一步控制并发度，可以在 util.dumpTables 函数中添加 threads 参数，例如 { threads: 2 } 来限制导出时的线程数。

  • 错误日志：
    你可以将导出日志保存到文件：

    1
    ./export_tables.sh > export_log.txt 2>&1

    这样可以在后台查看导出过程是否有错误。

总结

通过这种按表导出并在每次导出后 sleep 几秒的方式，可以有效降低导出过程中的数据库负载，并让导出过程更加平滑。根据你的具体需求，可以调整 sleep 时间和导出参数。