MySQL数据库迁移快速导出导入大量数据(外发)

数据库迁移是我们经常可遇到的问题，对于少量的数据，迁移基本上不会有什么问题。生产环境中，有以下情况需要做迁移工作：

磁盘空间不够。比如一些老项目，选用的机型并不一定适用于数据库。随着时间的推移，硬盘很有可能出现短缺；
业务出现瓶颈。比如项目中采用单机承担所有的读写业务，业务压力增大，不堪重负。如果 IO 压力在可接受的范围，会采用读写分离方案；
机器出现瓶颈。机器出现瓶颈主要在磁盘 IO 能力、内存、CPU，此时除了针对瓶颈做一些优化以外，选择迁移是不错的方案；
项目改造。某些项目的数据库存在跨机房的情况，可能会在不同机房中增加节点，或者把机器从一个机房迁移到另一个机房。再比如，不同业务共用同一台服务器，为了缓解服务器压力以及方便维护，也会做迁移。

MySQL迁移通常使用的有三种方法：

1、数据库直接导出，拷贝文件到新服务器，在新服务器上导入。
2、使用第三方迁移工具。
3、数据文件和库表结构文件直接拷贝到新服务器，挂载到同样配置的MySQL服务下。

第一种方案的优点：会重建数据文件，减少数据文件的占用空间，兼容性最好，导出导入很少发生问题，需求灵活。缺点:使用传统导出导入时间占用长。
第二种方案的优点：设置完成后传输无人值守，自动完成。缺点：不够灵活，设置繁琐，传输时间长，异常后很难从异常的位置继续传输。
第三种方案的优点：时间占用短，文件可断点传输，操作步骤少。缺点：新旧服务器中MySQL版本及配置必须相同，可能引起未知问题。

假如数据库迁移是因为业务瓶颈或项目改造等需要变动数据表结构的(比如分区分表)，我们便只能使用第一种方法了。

使用MySQL的SELECT INTO OUTFILE 、LOAD DATA INFILE快速导出导入数据

LOAD DATA INFILE语句从一个文本文件中以很高的速度读入一个表中。MySQL官方文档也说明了，该方法比一次性插入一条数据性能快20倍。

当用户一前一后地使用SELECT ... INTO OUTFILE 和LOAD DATA INFILE 将数据从一个数据库写到一个文件中，然后再从文件中将它读入数据库中时，两个命令的字段和行处理选项必须匹配。否则，LOAD DATA INFILE 将不能正确地解释文件内容。

下面是具体的操作过程：
环境：Centos 6.10
MySQL:8.0.22

首先在MySQL中执行指令：
在要导入的MySQL上，执行如下命令，确认可写（有些在集群中的机是不可写的）：
select @@read_only;
set global read_only=0;
COMMIT;

在要导入的机上：
show variables like 'require_secure%';
show variables like '%secure%';
set GLOBAL require_secure_transport=ON;  (注意：执行此语句会造成数据库不可使用工具和其它程序连接，只能使用本机命令行连接，OFF其它程序可以连接，ON不可以连接)
COMMIT;

使用：
show variables like '%secure%'; 
应该可以看到：
require_secure_transport    OFF
secure_file_priv    /var/lib/mysql-files/
注意这个路径，后面会使用到，现在需要确定几件事情：
1、你是否真计划把你的导出文件放到这个目录下，如果确定，那一定要确定你导出的文件大小，由于这个目录一般情况下较小，导出的数据过多会撑满根目录。
2、如果确认修改或使用这个目录，后面的语句中必须与这个一致

修改secure_file_priv的方法（这个值不可以通过命令行修改）：
mkdir /u01/mysql-files
chown -R mysql:mysql /u01/mysql-files
vim /etc/my.cnf
在其中添加：
secure_file_priv=/u01/mysql-files

这里修改后必须重启MySQL:
service mysqld restart

重启完成后，再使用“show variables like '%secure%' ”查看，即可确认这个目录已切换至指定的目录。

再次强调，这个目录是后面导入导出使用的目录。

做好以下工作后，即可使用以下脚本进行导出：
export.sh ：
----------------------------------------------------------------------------------------------------------------------------
#!/bin/bash
rm -fr ./tables
chown -R mysql:mysql ../
mysql mysql -h localhost -P3306 -uroot -p123456 <<!  #连接数据库
    USE ${1};   #设置数据库名参数
    SELECT t.TABLE_NAME into outfile '/u01/mysql-files/tables' FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n'  FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA = '${1}'  AND t.TABLE_NAME NOT LIKE 'V_%';
!

while read line
do
   #mysql -uroot -p123456 ${1} -e "LOAD DATA INFILE '/u01/mysql-files/$line' INTO TABLE $line  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'";
   echo "开始导出 $line 表,目标文件:/u01/mysql-files/$line"
   mysql -h localhost -P3306 -uroot -p123456 ${1} -e "select * into outfile '/u01/mysql-files/$line' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' from $line";

done < tables

exit;
----------------------------------------------------------------------------------------------------------------------------

授权：
chmod 777 export.sh

执行：
./export.sh 数据库名

导入脚本：
import.sh
----------------------------------------------------------------------------------------------------------------------------
#!/bin/bash
 
while read line
do
   echo "开始导入 /u01/mysql-files/$line 至 TABLE $line"
   mysql -h localhost -P3306 -uroot -p123456 ${1} -e "LOAD DATA INFILE '/u01/mysql-files/$line' INTO TABLE $line  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'"
   echo "完成导入 /u01/mysql-files/$line 至 TABLE $line"
done < tables

----------------------------------------------------------------------------------------------------------------------------
授权：
chmod 777 import.sh

执行：
./import.sh 数据库名

往往导出的文件可能会较大，所以需要对文件进行分割。
使用如下工具进行分隔：
linux split 命令
　　功能说明：切割文件。
　　语　　法：split [–help][–version][-<行数>][-a <长度>][-b <字节>][-d][-d <字节>][-l <行数>][要切割的文件][输出文件名]
    示    例：split -a 2 -d -l 1000000 T_CHECK_RECORD.txt T_CHECK_RECORD_SPLIT
　　补充说明：split可将文件切成较小的文件，预设每1000行会切成一个小文件。

　　参　　数：
　　-<行数>或-l<行数> 　指定每多少行就要切成一个小文件。
　　-a<长度> 　指定后缀长度,即自动生成的编号长度
　　-b<字节> 　指定每多少字就要切成一个小文件。支持单位:m,k
　　-C<字节> 　与-b参数类似，但切割时尽量维持每行的完整性。
　　-d 　使用数字后缀而不是字母。
　　–help 　显示帮助。
　　–version 　显示版本信息。
　　[输出文件名] 　设置切割后文件的前置文件名，split会自动在前置文件名后再加上编号。

使用例子：
split -b 100m 1111.log (按照字节分隔)
split -l 1000000 1111.log(按照行数分隔)

split -a 6 -d -l 100000 T_ALARM_LAST_1.txt T_ALARM_LAST_SPLIT

分隔文件脚本：
splitfile.sh
----------------------------------------------------------------------------------------------------------------------------
#!/bin/bash

while read line
do   
   echo "开始分割 /u01/mysql-files/$line"
   split -a 6 -d -l 100000 /u01/mysql-files/$line /u01/mysql-files/${line}_SPLIT
   mkdir /u01/mysql-files.bak
   echo "分割完毕 /u01/mysql-files/$line ,将期移入/u01/mysql-files.bak目录下"
   mv /u01/mysql-files/$line /u01/mysql-files.bak/
   echo "移动 /u01/mysql-files/$line 完毕"
done < tables
----------------------------------------------------------------------------------------------------------------------------
授权：
chmod 777 splitfile.sh

执行：
./splitfile.sh

导入脚本(注意其中的路径及连接信息，如果是空表导入的话，可以直接用INTO，而不用IGNORE INTO)：
slipt_import.sh
----------------------------------------------------------------------------------------------------------------------------
#!/bin/bash

db_host='localhost'
db_user='root'
db_pass='123456'
db_port='3306'

db_db='fmmp'
mysql_files='/var/lib/mysql-files'
#mysql_files='/u01/mysql-files'
cd $mysql_files
p="/u01/mysql-files"
for f in `ls $p`
do
echo $f
eval "mv '$p/$f' $mysql_files"
eval "chown -R mysql:mysql '$mysql_files/$f'"
import_cmd="mysql -h $db_host -u$db_user -p$db_pass -P$db_port $db_db <<!
 LOAD DATA INFILE '$mysql_files/$f' IGNORE INTO TABLE ${f%_*} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\n';
!"
echo "$import_cmd"

#eval $import_cmd
mysql -h $db_host -u$db_user -p$db_pass -P$db_port $db_db <<!
 LOAD DATA INFILE '$mysql_files/$f' IGNORE INTO TABLE ${f%_*} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\n';
!

eval "mv '$mysql_files/$f' /u01/mysqlfile/"

done

echo 'finished!'

exit

----------------------------------------------------------------------------------------------------------------------------
授权：
chmod 777 slipt_import.sh

执行：
./slipt_import.sh 数据库名

关于单个表的导入请参考：
----------------------------------------------------------------------------------------------------------------------------
mysql -h localhost -uroot -p123456 -P3306 fmmp <<!
 USE fmmp;
 DELETE FROM T_CHECK_RECORD_01;
 LOAD DATA INFILE '/var/lib/mysql-files/T_CHECK_RECORD_04.txt' IGNORE INTO TABLE T_CHECK_RECORD_01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
!
----------------------------------------------------------------------------------------------------------------------------

关于单个表的导出请参考：
----------------------------------------------------------------------------------------------------------------------------
mysql mysql -h localhost -P3306 -uroot -p123456 <<!  #连接数据库
    USE fmmp;   #设置数据库名参数
    select * into outfile '/var/lib/mysql-files/T_CHECK_RECORD_03.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' from T_CHECK_RECORD WHERE CHECK_RECORD_ID IN (32341776,32341777,32341778);
!
----------------------------------------------------------------------------------------------------------------------------