Mysql数据的迁移
mysqldump
mysqldump比较适合几十万上百万的较小数据的迁移使用
mysql load data
load data infile 语句可以从一个文本文件中以很高的速度读入一个表中，性能大概是 insert 语句的几十倍，比较适合上千万级及更高的海量数据迁移使用
MysqlDump
mysqldump --single-transaction --quick --skip-lock-tables --no-tablespaces -h your_db_host -u your_db_user -p your_db_name your_table_name | gzip > [导出文件.sql.gz]•--single-transaction: 对InnoDB表开启一个事务，以保证数据的一致性，并避免长时间的表锁定。(InnoDB保证数据一致性且不会锁表)
•--quick: 逐行导出数据，而不是将整个表的数据读入内存，适用于大表的导出。
•--skip-lock-tables: 禁用表锁，这样可以避免在导出过程中锁定表，适用于并发读写较多的环境。(保证不会整个锁表)
•--no-tablespaces: 不导出表空间信息，减少不必要的数据，可能会加快导出速度。
•-h your_db_host: 指定数据库服务器的地址。
•-u your_db_user: 数据库用户名。
•-p: 提示输入数据库密码。出于安全考虑，不建议将密码直接写在命令中。
•your_db_name: 要导出的数据库名。
•your_table_name: 要导出的表名。
•| gzip > : 导出过程中直接压缩数据，可以减少磁盘空间占用和传输时间：。
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"
STRUCTURE_FILE="$SCRIPT_DIR/structure.sql" # 表结构输出文件
DATA_FILE="$SCRIPT_DIR/data.sql" # 数据输出文件
# 记录开始时间
start_time=$(date +%s)
echo "Starting mysqldump for structure at $(date)"
# 导出表结构
mysqldump -h $DB_HOST -u $DB_USER -p$DB_PASS --no-data --skip-lock-tables --no-tablespaces $DB_NAME $TABLE_NAME > $STRUCTURE_FILE
echo "Structure dump completed at $(date)"
echo "Starting mysqldump for data at $(date)"
# 导出数据
mysqldump -h $DB_HOST -u $DB_USER -p$DB_PASS --no-create-info --single-transaction --quick --skip-lock-tables --no-tablespaces $DB_NAME $TABLE_NAME > $DATA_FILE
# 记录结束时间
end_time=$(date +%s)
# 计算总耗时
elapsed_time=$(($end_time - $start_time))
echo "mysqldump completed at $(date)"
echo "Total time taken: $(($elapsed_time / 60)) minutes and $(($elapsed_time % 60)) seconds"直接执行sh,导出的sql文件会在当前sh所在目录下
千万级数据要么用loaddata,要么用程序分批同步
SELECT * INTO OUTFILE导出千万级大数据量数据
MYSQL对千万级数据（10个G左右）的迁移，耗时控制在半小时内
检查 secure-file-priv 设置
SHOW VARIABLES LIKE 'secure_file_priv';secure-file-priv 是 MySQL 的一个安全设置，用于限制 LOAD DATA INFILE 和 SELECT INTO OUTFILE 操作的文件读写路径。具体来说：
•如果 secure-file-priv 被设置为一个目录路径，那么只有该目录下的文件才能被 LOAD DATA INFILE 读取或被 SELECT INTO OUTFILE 写入。
•如果 secure-file-priv 被设置为空字符串''，允许在服务器上的任何路径使用 LOAD DATA INFILE 和 SELECT INTO OUTFILE，但这通常不推荐使用，因为它没有路径限制，存在安全风险。
•如果 secure-file-priv 被设置为 NULL，禁用 LOAD DATA INFILE 和 SELECT INTO OUTFILE，这两个操作将无法执行。
修改 MySQL 的配置文件 my.cnf 或 my.ini
[mysqld]
secure-file-priv = /path/to/your/allowed/directory/LOAD DATA迁移千万级数据
步骤概述
1.导出数据: 使用 SELECT INTO OUTFILE 将数据导出到文本文件。
2.传输文件: 将文件传输到目标服务器。
3.导入数据: 使用 LOAD DATA 命令将数据导入到目标表中。
导出数据
在源数据库中，将数据导出到一个文本文件中
SELECT * INTO OUTFILE '/path/to/data.txt'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM your_table;将导出的文件从源服务器传输到目标服务器，可以使用 scp、rsync 等工具进行传输。
导入数据
LOAD DATA INFILE '/path/to/data.txt'
INTO TABLE your_table
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';导入性能优化建议
•禁用或延迟索引: 如果表中有大量索引，导入数据时可能会变慢。可以考虑在导入数据之前禁用索引，导入完成后再重新启用索引。
-- MyISAM 引擎，可以使用 ALTER TABLE 命令来禁用或延迟索引的更新
ALTER TABLE your_table_name DISABLE KEYS;
ALTER TABLE your_table_name ENABLE KEYS;•禁用外键检查: 如果表中存在外键，导入数据时可能会有性能损耗。可以在导入数据之前禁用外键检查：
SET foreign_key_checks = 0;
SET foreign_key_checks = 1; 如果这篇文章对你有用，可以关注本人微信公众号获取更多ヽ(^ω^)ﾉ ~
