MySQL数据迁移备份方案

MySQL数据迁移备份方案

一、概要说明

MySQL数据迁移备份方案有很多，根据实际场景可以选择主从同步(Master-->Backup)或(Master-->Backup-->Backup),如果是不同机房可以使用Otter,如果是大数据一次性备份导入可以使用移动硬盘+XtraBackup。不过这里说的是小数据量场景，数据迁移备份规模在100G级别以下的情况以及单独备份一张表或多张表的情况下的另一种选择。

二、my.cnf参数配置

mysql> show VARIABLES like '%max_allowed_packet%';

max_allowed_packet=1024M

三、表分析或数据准备

SHOW INDEX FROM jeedev.jeedev_log_re_backup20171009;

ALTER TABLE jeedev.jeedev_log_re_backup20171009 CHANGE id id INT(11);--删除自增长
ALTER TABLE jeedev.jeedev_log_re_backup20171009 DROP PRIMARY KEY;--删除主键
DROP INDEX `idx_logre_logCode` ON jeedev.jeedev_log_re_backup20171009;
SHOW CREATE TABLE jeedev.jeedev_log_re_backup20171009;

CREATE INDEX `idx_logre_logCode` ON jeedev.jeedev_log_re_backup20171009 (`logCode`);

RENAME TABLE jeedev_log_re TO jeedev_log_re_backup20171009,jeedev_log_re_new  TO jeedev_log_re;

四、迁移脚本

mysql_transfer.sh

#!/bin/sh
#
# @file mysql_transfer.sh
# @brief backup mysql data
# @author Jack
# @version 0.1
# @date 2017-10-09
#
WORK_DIR=/data/scripts
DATA_DIR=${WORK_DIR}/dataTemp/

if [ ! -d $DATA_DIR ];
	then
		mkdir -p $DATA_DIR
fi

databaseName="jeedev_history_2017"
newDbPass="123456"
TableList="jeedev_app_2017_01,jeedev_app_2017_02"
for tb  in `echo $TableList | sed 's/,/ /g'`  
do
	/usr/bin/mysqldump -h192.168.1.107 -ureadonly -preadonly -q $databaseName $tb > $DATA_DIR$tb --lock-tables=false
	mysql -uroot -p$newDbPass $databaseName < $DATA_DIR$tb
	rm -rf $DATA_DIR$tb
done

五、另一种的迁移方案

在实际使用中，为了防止业务表数据过大影响查询性能，还使用了一种方案，通过定时计划，明天凌晨定时按天备份指定的表前一天数据，备份成功后再删除原表中前一天的数据。
备份的数据自动按年创建库，按月创建表。而原表中根据配置的参数，只保留最近N天的数据。
例如jeedev_history_2017.jeedev_myapp_201710表示jeedev_history_2017

三、参考资料

 Mysql 大量数据快速导入导出 
http://blog.csdn.net/xiaobaismiley/article/details/41015783


mysqldump备份还原和mysqldump导入导出语句大全详解
http://www.jb51.net/article/49589.htm