当业务运行一段时间后,会出现有些表数据量很大,可能对系统性能产生不良的影响,常见的如订单表、登录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,形成备份语句
usereport;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: 负责恢复数据
usereport;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/bash2
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.sql6
7 rm -f /data/backup/incrementalbackup/*.txt
4)loadlog3.sh:将备份服务器上导出的数据文件拷贝到本地服务器
1 #!/bin/bash2 scp root@10.10.67.114:/data/backup /*.txt /data/backup/
5)loadlog4.sh:加载数据到本地服务器
1 #!/bin/bash2
3 /data/mysql/bin/mysql -h 127.0.0.1 -uroot -p'*******' < /data/backup/scripts/c.sql
6)loadlogall.sh:用于统一执行loadlog1-4,并输入日志。
1 #!/bin/bash2
3 echo '------start-loadlog.sh-----'>> /data/backup/scripts/logs/loadlog.log4
5 date "+%Y-%m-%d %H:%M:%S" >> /data/backup/scripts/logs/loadlog.log6 /data/backup/scripts/loadlog.sh
7
8 echo '----end--loadlog.sh-----' >> /data/backup/scripts/logs/loadlog.log9
10 /data/backup/scripts/loadlog2.sh
11
12 echo '----end---loadlog2.sh------' >> /data/backup/scripts/logs/loadlog.log13
14 /data/backup/scripts/loadlog3.sh
15
16 echo '------end-loadlog3.sh------------' >> /data/backup/scripts/logs/loadlog.log17
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脚本里。
未完待续。。。