Mac导出Mysql千万级表数据

MysqlDump

mysqldump -h [服务器地址] -u [用户名] -p[密码] --single-transaction --quick [数据库名] [表名] > [导出文件.sql]

•--single-transaction 保证了数据的一致性，并且不会在导出期间锁定表(只针对InnoDB有效)。
•--quick 选项进一步减少了内存使用，因为它逐行导出数据。

single-transaction 的作用：

--single-transaction 只适用于支持事务的存储引擎（如InnoDB），对于MyISAM等不支持事务的存储引擎，这个选项无效。在导出数据量非常大的表时，--single-transaction 能够有效避免锁表问题，从而不影响数据库的其他读写操作。
1.数据一致性：
•当使用 --single-transaction 选项时，mysqldump 会在导出数据时启动一个事务。这个事务会在导出整个数据库或表的过程中保持打开状态。
•这个事务快照可以确保在导出期间，表中的数据不会因为其他事务的修改而发生变化，从而保证导出的数据与数据库的某一特定时间点完全一致。

2.避免锁表：
•使用 --single-transaction 导出数据时，不会锁定表，允许其他事务在导出期间对表进行读写操作。相比于 LOCK TABLES 这种方式，这对生产环境的影响更小。
•由于不需要加锁，--single-transaction 对于大数据量的表非常有用，可以避免长时间锁表导致的阻塞。

千万级MyISAM导出需要注意问题

影响线上业务的因素

•表锁定：MyISAM表在导出数据时可能会锁定整个表。这会导致其他写操作（如 INSERT、UPDATE、DELETE）被阻塞，从而影响线上业务的性能。读操作也会受到一定的影响，因为MyISAM会在读取数据时加读锁。
•性能下降：由于表锁定，导出过程可能会影响数据库的性能，特别是在高并发情况下，可能导致系统响应变慢。
•磁盘空间：导出千万级数据时，生成的SQL文件可能非常大，需要足够的磁盘空间来存储。

减少对业务的影响

a. 选择低峰期
•尽量在业务低峰期进行导出，以减少对用户操作的影响。
b. 锁表策略
•使用 LOCK TABLES 锁定表可以确保在导出过程中数据一致性，但也会阻塞其他写操作。确保在业务低峰期进行操作，或者分批导出数据以减少锁定时间。

mysqldump -h [服务器地址] -u [用户名] -p[密码] --lock-tables [数据库名] [表名] > [导出文件.sql]

c. 分批导出
•对于非常大的表，可以考虑将数据分批导出，以减少每次导出对业务的影响。可以使用 --where 选项来限制每次导出的数据范围：

mysqldump -h [服务器地址] -u [用户名] -p[密码] --lock-tables [数据库名] [表名] --where="id BETWEEN 1 AND 1000000" > [导出文件_part1.sql]

d. 优化导出
•--quick：使用 --quick 选项可以减少内存使用，因为它逐行读取数据而不是将所有数据加载到内存中：

mysqldump -h [服务器地址] -u [用户名] -p[密码] --quick --lock-tables [数据库名] [表名] > [导出文件.sql]

•压缩导出文件：导出过程中直接压缩数据，可以减少磁盘空间占用和传输时间：

mysqldump -h [服务器地址] -u [用户名] -p[密码] --quick --lock-tables [数据库名] [表名] | gzip > [导出文件.sql.gz]

Mac脚本

新建sh脚本文件

#!/bin/bash

# 获取当前脚本所在的目录
SCRIPT_DIR=$(cd "$(dirname "$0")"; pwd)

# 定义变量
DB_HOST="your_db_host"
DB_USER="your_db_user"
DB_PASS="your_db_password"
DB_NAME="your_db_name"
TABLE_NAME="your_table_name"
OUTPUT_FILE="$SCRIPT_DIR/output.sql"  # 输出文件保存在脚本所在目录

# 记录开始时间
start_time=$(date +%s)

echo "Starting mysqldump at $(date)"

# 执行 mysqldump 命令
mysqldump -h $DB_HOST -u $DB_USER -p$DB_PASS --single-transaction --quick --no-create-info $DB_NAME $TABLE_NAME > $OUTPUT_FILE

# 记录结束时间
end_time=$(date +%s)

# 计算耗时
elapsed_time=$(($end_time - $start_time))

echo "mysqldump completed at $(date)"
echo "Time taken: $(($elapsed_time / 60)) minutes and $(($elapsed_time % 60)) seconds"

直接执行sh,导出的sql文件会在当前sh所在目录下