mysql 历史数据表迁移方案
当业务运行一段时间后，会出现有些表数据量很大，可能对系统性能产生不良的影响，常见的如订单表、登录log表等，这些数据很有时效性，比如我们一般很少去查上个月的订单，最多也就是报表统计会涉及到。
在我们的数据库中，用户登录表就是这种类型的表，一般而言，表中的数据是不可逆的，只有插入操作没有删除或者修改操作，表示在过去一段时间内完成的事实业务数据。比如登录表表示一段时间内用户的登录信息，登录一次游戏就会在数据库中记录一条数据。
1、对于备份这类表的策略是：
- 第一次备份全表的数据导入到备份表中，也就是全备。
- 以后每天只将增加的量自动迁移到备份表中，这里通过自增列的值相比较得到增量，如第一次全备导入备份表，这时自增列的最大值为max(idx)，通过这个值到从库中查询，只要大于这个值的所有记录形成增量。
- 备份完成后，分批删除主库上的数据。
2、下面是具体操作步骤：
- 第一次全备和恢复
select * from table into outfile "/data/backup/xxxx.txt"; --导出备份数据 load data infile "/data/backup/ xxxx.txt " into table bak_table; --在另外服务器备份表中恢复
- 脚本程序实现：拉取线上服务器上的增量数据，然后导入本服务器，实现增量备份
1）需要在同一目录下建立三个sql脚本，如a.sql b.sql c.sql
a.sql: 拼接增量备份sql，形成备份语句
use report;
select concat('use report;select * from t_user_session where user_session_idx>',max(user_session_idx),' into outfile "/data/backup/incrementalbackup/t_user_session_ios1.txt";') from t_user_session;
b.sql:接受a.sql的输出
c.sql: 负责恢复数据
use report; load data infile '/data/backup/incrementalbackup/t_user_session_ios1.txt' into table t_user_session_ios1;
2）loadlog1.sh:通过输入a.sql，生成备份语句到b.sql中。用于在线上服务器执行增量备份数据。
#!/bin/bash /data/mysql/bin/mysql -h 127.0.0.1 -uroot -p'******' -N < /data/backup/scripts/a.sql > /data/backup/scripts/b.sql
3）loadlog2.sh：连接到线上服务器，删除以前备份文件，并通过b.sql进行数据导出，然后删除本地服务器上上次备份的文件。
1 #!/bin/bash 2 3 ssh root@1.1.1.1 "/data/backup/scripts/del_bak_file.sh" 4 5 /data/mysql/bin/mysql -uroot -p'*****' -h'1.1.1.1' < /data/backup/scripts/b.sql 6 7 rm -f /data/backup/incrementalbackup/*.txt
4）loadlog3.sh：将备份服务器上导出的数据文件拷贝到本地服务器
1 #!/bin/bash 2 scp root@10.10.67.114:/data/backup /*.txt /data/backup/
5）loadlog4.sh：加载数据到本地服务器
1 #!/bin/bash 2 3 /data/mysql/bin/mysql -h 127.0.0.1 -uroot -p'*******' < /data/backup/scripts/c.sql
6）loadlogall.sh:用于统一执行loadlog1-4，并输入日志。
1 #!/bin/bash 2 3 echo '------start-loadlog.sh-----'>> /data/backup/scripts/logs/loadlog.log 4 5 date "+%Y-%m-%d %H:%M:%S" >> /data/backup/scripts/logs/loadlog.log 6 /data/backup/scripts/loadlog.sh 7 8 echo '----end--loadlog.sh-----' >> /data/backup/scripts/logs/loadlog.log 9 10 /data/backup/scripts/loadlog2.sh 11 12 echo '----end---loadlog2.sh------' >> /data/backup/scripts/logs/loadlog.log 13 14 /data/backup/scripts/loadlog3.sh 15 16 echo '------end-loadlog3.sh------------' >> /data/backup/scripts/logs/loadlog.log 17 18 /data/backup/scripts/loadlog4.sh 19 20 echo '-----end-loadlog4.sh----------' >> /data/backup/scripts/logs/loadlog.log
7）将loadlogall.sh加入定时目录，每隔1个小时拉取一次。
crontab -e
--编辑文件 分 时 天 月 周 年 命令
到这里备份就完成了，然后可以清理部分的历史数据了，当然了，这里几个loadlog*.sh显得有些繁琐，这里为了一步一步看得更清楚些，完全可以把这些整合到一个shell脚本里。
未完待续。。。
