mysql主备数据比对 mysql主备方案

现在通用的mysql主从方案已经很成熟，例如，现在公司做的是一主+两从方案，如果主机宕掉，采用域名方式切换从机，也非常方便。

现在增加一套方案，实现以下功能：

1、方便的备份可刻盘，或者灵活的备份到第三主机。

2、如果出现误删除，可恢复。

第一点是必要的，如出现重大物理灾难，例如整个机房瘫痪，刻盘恢复是很重要的，当然这种情况极低。

设计整体备份图如下：

 

备份方案

数据库主机是 ***.***.***.245，***.***.***.246,  ***.***.***.39。

过程如下：

1、39从机会每周执行一次定时脚本database_dump_senior.sh一次，生成dump出一份备份，举例来说每周日上午10点。每次dump出一份都会记录当天的日期，并将导出的databases_product.sql和binlog_info.txt文件保存到当前日期下，例如 /home/data/mysql_bak/dumps/2013_08_06/ 下。然后rsync同步到245和246的文件夹下。 

2、245会有定时脚本每隔十分钟执行一次（可以按需设定），读取dumps文件夹下的从机binlog信息，读取binlog_info.txt里的日期信息生成日期文件夹，读取binlog_info.txt文件找到要备份的binglog日志，cp到binlog文件夹下。如/home/data/mysql_bak/binlog/2013_08_06/下。然后同步一份到246和39

3、39和245都会在每次脚本执行时清除8天未被修改过的数据，这样可以保持每台服务器至少有两个版本的备份。在网络断掉的情况下是有用处的。

 

目录结构

/home/data/mysql_bak/ 下

------dumps
     ------2013_08_06
　　　　　　------databases_product.sql
　　　　　　------ binlog_info.txt
　　　------2013_08_13
　　　　　　------databases_product.sql
　　　　　　------ binlog_info.txt
　　　------binlog_info.txt

------binlog
     ------2013_08_06
             　------mysql-bin.000385
            　 ------mysql-bin.000386
　　　------2013_08_13
            　 ------mysql-bin.000386
            　 ------mysql-bin.000387

为了避免转储时主机压力过大，采用在从机备份的方式，39从机在stop slave 后开始 dump一份全库的备份，然后记录从机备份的位置，保存在binlog_info.txt里。binlog_info.txt 保存内容如下：

           

current_date:2013_08_15
　　　　  Master_Log_File: mysql-bin.000386
　 Read_Master_Log_Pos: 90835176
               Relay_Log_File: netease-test-namenode-relay-bin.000022
              Relay_Log_Pos: 253
   Relay_Master_Log_File: mysql-bin.000386
   Exec_Master_Log_Pos: 90835176
               Until_Log_File:
               Until_Log_Pos: 0

current_date 是由脚本根据系统时间生成，其余是读取从机变量。

 

出现风险的状况

1、  从机39坏了，binlog_info.txt未被替换成新的，这时主机会按从原有的binlog_info.txt读取到的位置信息，一直保存最新的binlog。

2、  主机245坏了，由于在246和39备份了binlog，可以将数据恢复精确到到最后十分钟（主机定时任务时间）。

3、  从机 246 坏了，无任何影响。

4、采用binlog在主机备份是保证数据始终与主机一致，更高的可靠性。

5、通过删除脚本，保证无论什么时候都会有两份备份，一份最新的，一份上周的。

 

这些都要配合从机状态监测。

脚本

从机39上跑的脚本：

#!/bin/sh 
current_date=`date +%Y_%m_%d`;
back_path="/home/data/mysql_bak/dumps";
`mkdir -p ${back_path}/${current_date}`;
#停止与主机同步
mysql --socket=/home/mysql/data_product/mysql.sock -e "stop slave";
echo "now, slave stopped!";

#导出所有数据库
echo "dump all databases begin!";
`mysqldump --all-databases --socket=/home/mysql/data_product/mysql.sock > ${back_path}/${current_date}/databases_product.sql`;
echo "dump all databases end!";

#保存当前日期
`echo "current_date:${current_date}" > ${back_path}/${current_date}/binlog_info.txt`;
#保存现在导出时的binlog位置
`mysql --socket=/home/mysql/data_product/mysql.sock -e "show slave status \G"|egrep 'Log_File|Log_Pos' >> ${back_path}/${current_date}/binlog_info.txt`;

#拷贝一份到备份的根目录
`cp ${back_path}/${current_date}/binlog_info.txt ${back_path}/binlog_info.txt`;

#重新开启与主机同步
mysql --socket=/home/mysql/data_product/mysql.sock -e "start slave";
echo "now, slave started!";

#删除8天前备份，这样每次保存最新一份，和最新一份的上一份。
find ${back_path}/  -type f -ctime +8 -exec rm -rf {} \;

#删除空文件夹
find ${back_path}/ -type d -regex ".*/[0-9_]+$"|(while read arg;do if [ `ls  $arg |wc -l` -eq 0 ]; then rmdir $arg ; fi; done)

echo "old backups has been deleted!";

#将备份binlog位置信息文件，传送给主库
rsync -avz --delete ${back_path}/ ***.***.***.245::database_bak/dumps/
rsync -avz --delete ${back_path}/ ***.***.***.246::database_bak/dumps/

主机245上跑的脚本

#!/bin/sh save binlogs

#全局变量
back_path="/home/data/mysql_bak";
mysql_path="/home/mysql/data_product";

#判断如果不存在binlog的备份信息文件，则退出
if [ ! -f "${back_path}/dumps/binlog_info.txt" ];then
    echo "ERROR,binlog_info.txt not exist,please check your file!";
    exit;
fi

cd ${mysql_path};

#读取备份binlog位置以及日期信息
echo "now,get the backup binlog position info!"
current_date=`cat ${back_path}/dumps/binlog_info.txt|grep 'current_date'|sed 's/current_date://g'|tr ' ' '\0'`; 
logFile=`cat ${back_path}/dumps/binlog_info.txt |grep 'Relay_Master_Log_File'|sed 's/Relay_Master_Log_File://g'|tr ' ' '\0'`;
echo -e "current date is : ${current_date}\nBinlog file is : ${logFile}\n";

#如果不存在则，创建备份文件夹
`mkdir -p ${back_path}/binlog/${current_date}`; 
 
#备份记录binlog文件之后的剩余binlog文件
cat mysql-bin.index |grep  -A100 ${logFile}|sed "s/.\///"|awk -v back_path=${back_path} -v current_date=${current_date} \
'{print "now save:" $0 ;system("cp " $0 " "back_path"/binlog/"current_date"/"$0);}';

echo "backup binlog ended!";

#删除8天前备份，这样每次保存最新一份，和最新一份的上一份。
find /home/data/mysql_bak/binlog/  -type f -ctime +8 -exec rm -rf {} \;

#删除空文件夹
find /home/data/mysql_bak/binlog/ -type d -regex ".*/[0-9_]+$"|(while read arg;do if [ `ls  $arg |wc -l` -eq 0 ]; then rmdir $arg ; fi; done)

echo "old backups has been deleted!";

#将备份binlog信息同步到从库
rsync -avz --delete ${back_path}/binlog/ ***.***.***.39::database_bak/binlog/
rsync -avz --delete ${back_path}/binlog/ ***.***.***.246::database_bak/binlog/