停机mysql数据库数据迁移脚本

背景

服务器到期，需要更换云服务器，迁移数据库

方案

1、mysqldump 实测速度太慢，弃用

2、将数据导入txt文件，压缩传到新的服务器，导入数据库

数据备份脚本

#!/bin/bash

txt_data_dir='/usr/local/mysql/xxl-backup/'
password='pwd'

# 创建文件保存目录并赋予权限
sudo mkdir -p ${txt_data_dir} 

sudo chown -R mysql:mysql ${txt_data_dir}
sudo chmod -R 755 ${txt_data_dir}

table_txt=$txt_data_dir'xxl_tables.txt'
binlog_txt=$txt_data_dir'binlog_status.txt'
xxl_sql=$txt_data_dir'xxl.sql'

# 导出数据库结构
mysqldump -h localhost -uroot -p$password --databases  --no-data xxl_job > ${xxl_sql}


# 锁定主库，只能读: 以下命令执行不生效，需要登录数据库执行(可选)
# mysql -uroot -p$password xxl_job -e "FLUSH TABLES WITH READ LOCK;" >>  ${binlog_txt}

# 记录锁库后binlog 位置
echo "----- 记录锁库后binlog -----"  >> ${binlog_txt}
mysql -uroot -p$password xxl_job -e "show master status;">> ${binlog_txt}


# 将数据库所有的表都导入到一个文件里面
mysql -uroot -p$password xxl_job -e "show tables" >  ${table_txt}

# 删除文件第一行数据：Tables_in_xxl_job 
sed -i '1d' ${table_txt}

# 遍历每个表名, 并将表的数据导入到txt文件中
while read line
do
    echo '表名:'$line
	if [ "$line" != "xxl_job_log" ]; then
		mysql -uroot -p$password xxl_job -e "select * from $line into outfile '${txt_data_dir}$line.txt'";
	else
        echo "跳过表名为 'xxl_job_log'";
    fi
done < ${table_txt}

# 查看binlog 状态
echo "----- 记录数据导出成txt后binlog -----"  >> ${binlog_txt}
mysql -uroot -p$password xxl_job -e "show master status;" >> ${binlog_txt}

# 解锁表
# mysql -uroot -p$password xxl_job -e "UNLOCK TABLES;"

# 压缩文件
cd ${txt_data_dir}
zip -r txt_data_xxl.zip xxl-backup

# 上传到新服务器 scp
scp -P 22 txt_data_xxl.zip root@123.129.123.323:/usr/local/xxl-data/txt_data_xxl.zip

读数据脚本

#!/bin/bash

txt_data_dir='/usr/local/xxl-backup/'
password='pwd'

# 所有表文件的txt文件
table_txt=${txt_data_dir}'xxl-backup/xxl_tables.txt'

# 解压文件
unzip ${txt_data_dir}txt_data_xxl.zip

# 赋予读写权限
# chown -R mysql:mysql ${txt_data_dir}
chmod -R 755 ${txt_data_dir}

cd ${txt_data_dir}xxl-backup

# 加载数据库
mysql -uroot -p$password -e "source xxl.sql";

# 遍历每个表名, 并将表的数据导入到txt文件中
while read line
do
    echo '表名:'$line
	mysql -uroot -p$password xxl_job -e "load data infile '${txt_data_dir}xxl-backup/${line}.txt' into table $line";
	
done < ${table_txt}