重建mysql从库、表数据迁移
一、背景
1、当集群的mysql主从同步异常，使用xtrabackup工具从主库中备份的数据来恢复从库，使主从同步恢复正常
2、docker 部署mysql主从
3、systemd管理
cat /etc/systemd/system/mysql_slave.service [Unit] Description=MySQL Slave Container After=docker.service Requires=docker.service [Service] TimeoutStartSec=60 RuntimeDirectory=mysqld RuntimeDirectoryMode=0777 ExecStartPre=-/usr/bin/docker rm -f mysql_slave ExecStartPre=-/usr/bin/docker pull "ip:5000/mysql:5.7" ExecStartPre=/usr/bin/docker volume create mysqls_data ExecStartPre=/usr/bin/docker volume create mysqls_log ExecStart=/backup/mysql/mysql_slave.run ExecStop=-/usr/bin/docker stop mysql_slave Restart=on-failure RestartSec=10 [Install] WantedBy=multi-user.target
4、启动文件
cat /backup/mysql/mysql_slave.run
#!/bin/bash
/usr/bin/docker run -i \
-e MYSQL_ROOT_PASSWORD=xxx \
-e MYSQL_DATABASE=xxx \
-e LANG=C.UTF-8 \
-v /backup/mysql/mysql_slave_my.cnf:/etc/mysql/my.cnf \
-v /var/run/mysqld:/var/run/mysqld \
-v mysqls_data:/var/lib/mysql \
-v mysqls_log:/var/log/mysql \
--privileged=true \
--net=host \
--rm \
--name=mysql_slave \
"ip:5000/mysql:5.7"
二、实操
在mysql slave节点操作
1、停止mysql服务
systemctl stop mysql_slave.service
2、重建mysql从库的数据卷
docker volume rm mysqls_data docker volume rm mysqls_log docker volume create mysqls_data docker volume create mysqls_log
注意:清理目录后，不要再启动mysql从库服务
3、临时开放防火墙端口
firewall-cmd --zone=public --add-port=20000/tcp
4、开启接收服务
nc -l 20000|xbstream -x -C /healsci/docker/volumes/mysqls_data/_data/
5、主库操作
xtrabackup --defaults-file=/backup/mysql/mysql_master_my.cnf --backup --user=root --password=xxx --socket=/var/run/mysqld/mysqld.sock --datadir=/healsci/docker/volumes/mysqlm_data/_data/ --parallel=8 --compress --compress-threads=8 --stream=xbstream | nc node3 20000
备份期间，不能执行ddl操作，否则备份会失败。
当执行结束后，最后一行显示有 completed OK!，代表备份操作顺利完成。
6、在mysql从库节点执行以下命令：
firewall-cmd --zone=public --remove-port=20000/tcp xtrabackup --decompress --remove-original --parallel=8 --target-dir=/heal/docker/volumes/mysqls_data/_data xtrabackup --prepare --use-memory=4G --target-dir=/heal/docker/volumes/mysqls_data/_data
7、启动mysql从库
systemctl start mysql_slave.service mysql -uroot -pxxx -S /var/run/mysqld/mysqld.sock CHANGE MASTER TO MASTER_HOST='ip',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx;
MASTER_LOG_FILE和MASTER_LOG_POS值获取：
当用主库备份时，查询备份目录（/heal/docker/volumes/mysqls_data/_data/xtrabackup_info）文件中的binlog_pos行，对应filename和position
8、启动mysql主从同步
start slave; show slave status\G;
三、mysql 表数据迁移
1、MySQL中的.cfg、.ibd和.frm文件是与InnoDB存储引擎相关的文件。它们存储了有关数据库表的不同类型的信息：
.frm 文件
.frm文件是MySQL数据库中的表定义文件。对于每个表，MySQL都会在表所在的数据库目录下创建一个与表同名的.frm文件。此文件包含了表结构的元数据，包括列的定义（如数据类型、大小），索引信息，以及其他表属性。无论使用哪种存储引擎，都需要.frm文件来描述表的结构。从MySQL 5.7.31开始，.frm文件逐渐被废除，表结构元数据被保存在数据字典中。
.ibd 文件
.ibd文件包含InnoDB存储引擎的表数据和索引数据。在MySQL的文件式表空间模式中，当你为InnoDB表启动了innodb_file_per_table配置选项后，每个InnoDB表都会对应有一个独立的.ibd文件。这使得管理、备份单独的表变得更容易，同时可以减小系统表空间ibdata1的大小，因为表的数据已经分散在各自的.ibd文件中了。.ibd文件使得InnoDB表具有了更好的扩展性和更高效的磁盘空间管理。
.cfg 文件
对于运行在MySQL 5.6和更高版本上的InnoDB表，.cfg文件用于保存表的压缩信息。当你使用mysqldump工具的—innodb-optimize-keys选项导出表结构为SQL时（或者其他表传输操作，比如Transportable Tablespaces功能），此文件会被创建。.cfg文件中存储了有关如何压缩表的信息，如行格式、键块大小等。这个文件不是用于常规操作，仅在表导出和导入之间传输压缩信息时才会用到。
请注意，从MySQL 8.0 版本开始，MySQL引入了Data Dictionary的概念替代.frm文件，数据字典是一个包含所有服务器对象元数据的事务性数据结构。在MySQL 8.0及以后的版本中，.frm文件和一些相关文件已经不再使用。
2、迁移脚本
#!/bin/bash
src_db_host='node1'
dest_db_host='node3'
dest_file_host='node2'
src_volume_name='mysqlm_data'
dest_volume_name='mysqlm_data'
dest_file_path_root='/dasda/database_backup/'
mysql_user='root'
mysql_password='xxxx'
date=$(date +%Y%m%d)
databases_tables1=(
'pbm_app.a_predict_flowing'
'pbm_app.patient_service_record'
'pbm_app.wait_beds_event'
)
start_time=$(date +'%Y-%m-%d %H:%M:%S')
start_timestamp=$(date +%s)
# 导出表结构并创建表
for d_t in "${databases_tables1[@]}"
do
IFS="." read -ra database2table <<< "${d_t}"
db="${database2table[0]}"
tb="${database2table[1]}"
# 建库
mysql -h"${dest_db_host}" -u"${mysql_user}" -p"${mysql_password}" -e "CREATE DATABASE IF NOT EXISTS \`${db}\`;" 2> /dev/null
# 建表
sql=$(mysqldump --column-statistics=0 -u"${mysql_user}" -p"${mysql_password}" -h"${src_db_host}" "${db}" "${tb}" --no-data 2>&1 | tail -n +2)
mysql -h "${dest_db_host}" -u"${mysql_user}" -p"${mysql_password}" -e "USE ${db}; ${sql}" 2> /dev/null
# 目标实例上删除.ibd文件
mysql -h "${dest_db_host}" -u"${mysql_user}" -p"${mysql_password}" -e "USE "${db}"; ALTER TABLE "${tb}" DISCARD TABLESPACE;" 2> /dev/null
# 原实例上导出.ibd 和 .cfg
mysql -u"${mysql_user}" -h"${src_db_host}" -p"${mysql_password}" -e "FLUSH TABLES "${db}"."${tb}" FOR EXPORT; SELECT SLEEP(7200);" 2> /dev/null &
export_pid=$!
# 在后台开始同步.ibd 和 .cfg 文件到目标实例
ssh root@"${src_db_host}" "rsync -aP /healsci/docker/volumes/"${src_volume_name}"/_data/"${db}"/"${tb}".{ibd,cfg} root@"${dest_db_host}":/healsci/docker/volumes/"${dest_volume_name}"/_data/"${db}"/" &
rsync_pid=$!
# 确认文件同步完毕并验证stat值
while true; do
local_stat_ibd=$(stat -c%s "/healsci/docker/volumes/${dest_volume_name}/_data/${db}/${tb}.ibd" 2> /dev/null)
remote_stat_ibd=$(ssh root@"${src_db_host}" "stat -c%s /healsci/docker/volumes/${src_volume_name}/_data/${db}/${tb}.ibd" 2> /dev/null)
local_stat_cfg=$(stat -c%s "/healsci/docker/volumes/${dest_volume_name}/_data/${db}/${tb}.cfg" 2> /dev/null)
remote_stat_cfg=$(ssh root@"${src_db_host}" "stat -c%s /healsci/docker/volumes/${src_volume_name}/_data/${db}/${tb}.cfg" 2> /dev/null)
if [[ -n "$local_stat_ibd" && -n "$remote_stat_ibd" && "$local_stat_ibd" == "$remote_stat_ibd" && -n "$local_stat_cfg" && -n "$remote_stat_cfg" && "$local_stat_cfg" == "$remote_stat_cfg" ]]; then
break
fi
sleep 5
done
# .cfg和.ibd文件一致，可结束MySQL会话
kill "${export_pid}"
# 确保rsync进程已经完成
wait "${rsync_pid}" 2> /dev/null
# 解锁原表
mysql -h"${src_db_host}" -u"${mysql_user}" -p"${mysql_password}" -e 'UNLOCK TABLES;' 2> /dev/null
# 同步文件到备份节点
ssh root@"${dest_file_host}" "mkdir -p "${dest_file_path_root}""${date}"/"${db}"/"
scp /healsci/docker/volumes/"${dest_volume_name}"/_data/"${db}"/"${tb}".* root@"${dest_file_host}":"${dest_file_path_root}""${date}"/"${db}"/
# 新实例刷新
mysql -h"${dest_db_host}" -u"${mysql_user}" -p"${mysql_password}" -e "USE "${db}"; ALTER TABLE "${tb}" IMPORT TABLESPACE;" 2> /dev/null
done
end_time=$(date +'%Y-%m-%d %H:%M:%S')
end_timestamp=$(date +%s)
elapsed_seconds=$((end_timestamp - start_timestamp))
hours=$((elapsed_seconds / 3600))
minutes=$(( (elapsed_seconds / 60) % 60))
seconds=$((elapsed_seconds % 60))
echo "Total time taken: $hours hours $minutes minutes $seconds seconds."
关键点：
mysql会话一旦关闭cfg、ibd文件就会自动删除，当文件比较大的时候，就需要考虑保持会话的存在直至数据传输完成
