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]
  • 1.

•--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"
  • 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.
  • 36.

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

Mac迁移导出Mysql库表_数据


千万级数据要么用loaddata,要么用程序分批同步

SELECT * INTO OUTFILE导出千万级大数据量数据

MYSQL对千万级数据（10个G左右）的迁移，耗时控制在半小时内

检查 secure-file-priv 设置

SHOW VARIABLES LIKE 'secure_file_priv';
  • 1.

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/
  • 1.
  • 2.

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;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.

将导出的文件从源服务器传输到目标服务器，可以使用 scp、rsync 等工具进行传输。

导入数据
LOAD DATA INFILE '/path/to/data.txt' 
INTO TABLE your_table 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n';
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.

导入性能优化建议
•禁用或延迟索引: 如果表中有大量索引，导入数据时可能会变慢。可以考虑在导入数据之前禁用索引，导入完成后再重新启用索引。

-- MyISAM 引擎，可以使用 ALTER TABLE 命令来禁用或延迟索引的更新
ALTER TABLE your_table_name DISABLE KEYS;
ALTER TABLE your_table_name ENABLE KEYS;
  • 1.
  • 2.
  • 3.

•禁用外键检查: 如果表中存在外键，导入数据时可能会有性能损耗。可以在导入数据之前禁用外键检查：

SET foreign_key_checks = 0;
SET foreign_key_checks = 1;
  • 1.
  • 2.

如果这篇文章对你有用，可以关注本人微信公众号获取更多ヽ(^ω^)ﾉ ~

Mac迁移导出Mysql库表_导入数据_02