MySQL全库备份脚本设计

深碍√TFBOYSˉ_ 2022-02-20 01:15 203阅读 0赞

针对MySQL全库备份设计如下：  
1.在本地实例中创建backinfo.backupinfo表，在备份开始前记录开始时间，备份结束后记录结束时间，开始时间可用于恢复校验；  
2.创建远端实例和backinfo.backupinfo表，同上，集中记录多个MySQL实例备份的相关信息，包括备份开始时间，结束时间，备份文件大小，rsync传输时间等，便于观察历史情况；  
3.备份过程中，利用xtrabackup的流式备份，边备份边压缩，备份结束后，传输至备份服务器统一存放；  
4.本地创建相关日志文件，记录备份过程输出信息，用于备份异常排查;  
5.备份服务器上对备份文件进行恢复演练，利用第一点记录的备份时间，可以校验备份的有效性和一致性，恢复脚本不在本篇范围内。

backupinfo表结构：

Create Table: CREATE TABLE `backupinfo` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `ip` varchar(20) DEFAULT NULL,
      `beginbackup` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      `endbackup` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      `file_size` varchar(10) DEFAULT NULL,
      `endrsync` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB

备份脚本：

#!/bin/bash
    
    #本地信息
    #本机ip
    ip=192.168.xx.xx                             
    #mysql执行文件直接路径
    mysql=/usr/local/mysql/bin/mysql   
    mysql_defaults_file=/etc/my.cnf
    #用于备份的用户                
    mysqluser=root           
    #备份用户的密码    
    mysqlpassword=sam123 
    #本地备份目录   
    backuptodir='/fullbackup'           
    
    #远端实例的登陆信息，用于记录每次备份的相关信息，包括备份开始时间，结束时间，备份文件大小，rsync传输时间等    
    infouser=bakrec
    infopassword=sam123
    infohost=192.168.xx.xx
    infoport=3307
    
    #备份服务器rysnc相关信息
    #rsync server端用户
    rsyncuser=sam
    #备份主机IP                          
    rsync_hostip1=192.168.xx.xx                   
    #rsync的模块，对应server端rsyncd.cnf 
    rsyncmodule=mysqlbackup            
    
    #备份时间标签，用于校验备份  
    insert_value=`date +"%Y-%m-%d %H:%M:%S"`           
    sql_insert="insert into backupinfo.backupinfo (ip,beginbackup) values('$ip','${insert_value}');"
    
    #若备份目录不存在则创建
    if [ ! -d $backuptodir ];then
        mkdir -p $backuptodir
    fi
    
    #备份前记录当前时间
    $mysql --user=$mysqluser --password=$mysqlpassword -e "${sql_insert}"
    $mysql -u$infouser -p$infopassword -h$infohost -P$infoport -e "${sql_insert}"
    
    #进行全备份
    echo "   -----------------`date`-----------------   ">>/tmp/backup_time.log
    echo "innobackupex_start_time:`date +%F_%H:%M:%S`">>/tmp/backup_time.log
    backup_time=`date +"%Y-%m-%d_%H-%M-%S"`
    
    innobackupex --defaults-file=$mysql_defaults_file --user=$mysqluser --password=$mysqlpassword $backuptodir  --stream=tar | gzip - > ${backuptodir}/mysql_full_${ip}_${backup_time}.tar.gz 2> /dev/null
    #无法通过$?来检查是否成功，用检测日志最后是否completed OK来判断
    bakdone=`tail -1 bak.log|grep -w "completed OK\!"|wc -l`
    if [ $bakdone -eq 0 ];then
    echo "backup failed!">>${logdir}/backup_time.log
    exit 1
    
    #记录备份结束时间
    echo "innobackupex_finish_time:`date +%F_%H:%M:%S`">>/tmp/backup_time.log
    
    file_size=`du -sh ${backuptodir}/mysql_full_${ip}_${backup_time}.tar.gz|awk '{print $1}'`
    update_value=`date +"%Y-%m-%d %H:%M:%S"`
    sql_update="update backupinfo.backupinfo set endbackup='${update_value}',file_size='${file_size}' where ip='$ip' and beginbackup='${insert_value}';"
    $mysql --user=$mysqluser --password=$mysqlpassword -e "${sql_update}"
    $mysql -u$infouser -p$infopassword -h$infohost -P$infoport -e "${sql_update}"
    
    #将压缩文件rsync同步到备份服务器
    echo "fullbak_rsync_start_time:`date +%F_%H:%M:%S`">>/tmp/backup_time.log
    rsync -az --password-file=/etc/rsyncd.password  ${backuptodir}/mysql_full_${ip}_${backup_time}.tar.gz $rsyncuser@${rsync_hostip1}::$rsyncmodule || exit 1
    echo "fullbak_rsync_finish_time:`date +%F_%H:%M:%S`">>/tmp/backup_time.log
    
    #check_data.txt中记录备份开始时间，用于备份校验
    echo "beginbackup">/tmp/check_data_${ip}.txt
    echo ${insert_value} >> /tmp/check_data_${ip}.txt && rsync -az --password-file=/etc/rsyncd.password /tmp/check_data_${ip}.txt $rsyncuser@${rsync_hostip1}::$rsyncmodule
    
    #rsync结束时间
    update_value=`date +"%Y-%m-%d %H:%M:%S"`
    sql_update="update backupinfo.backupinfo set endrsync='${update_value}' where ip='$ip' and beginbackup='${insert_value}';"
    $mysql --user=$mysqluser --password=$mysqlpassword -e "${sql_update}"
    $mysql -u$infouser -p$infopassword -h$infohost -P$infoport -e "${sql_update}"
    
    #删除压缩文件
    cd $backuptodir
    rm -rf ${backuptodir}/mysql_full_${ip}_${backup_time}.tar.gz

执行脚本：  
`sh mysql_full_backup.sh > bak.log`