MySQL简单备份实践

给MySQL做定时备份的简单实践

线上数据库备份场景：

1. 全量备份：每天晚上2点执行全备脚本
2. 增量备份：每天13点备份binlog日志

前提：

1. 一定要打开binlog日志功能
2. 准备目录空间，不能和数据库放在同一磁盘或存储，安全+性能

mkdir -p /mysql/backup/backup-db
mkdir -p /mysql/backup/backup-binlog
mkdir -p /mysql/backup/scripts
chown -R mysql:mysql /mysql/backup/
chmod -R 755 /mysql/backup/

3. /etc/my.cnf

   server_id = 3306 ##唯一的， 数字：IP+端口
   log_bin=/bak/backup/binlog/itpuxdb-binlog ## 这个名字会自动加后缀
   log_bin_index=/bak/backup/binlog/itpuxdb-binlog.index ## 记录所有二进制列表
   binlog_format='row' ##默认为row(row,statement,mixed)
   binlog_rows_query_log_events=on ## 需要打开
   

4. 准备备份脚本

backup-mysqldump-full.sh

#!/bin/bash
# script from www.itpux.com and fgjy,use mysqldump to Full backup mysql data per day!
DataBakDir=/bak/backup/backup-db
LogOutFile=/bak/backup/backup-db/bak-db.log
LogErrOutFile=/bak/backup/backup-db/bak-db-err.log
BinLogBakDir=/bak/backup/backup-binlog
#MyCNF=/mysql/data/3306/my.cnf
mysql_host=192.168.99.80  #根据自己的实际情况设置
mysql_port=3306  #根据自己的实际情况设置
mysql_user=root  #根据自己的实际情况设置
mysql_pass=123qweASD  #根据自己的实际情况设置
Date=`date +%Y%m%d`
Begin=`date +"%Y-%m-%d %H:%M:%S"`
cd DataBakDir
DumpFile="dbbackup-alldb-Date.sql"
GZDumpFile=dbbackup-alldb-Date.sql.gz
/usr/local/mysql/bin/mysqldump -u{mysql_user} -p{mysql_pass} --single-transaction --master-data=2 --routines --flush-logs --flush-privileges --all-databases --log-error=LogErrOutFile > DumpFile 
/usr/local/mysql/bin/mysqldump -u{mysql_user} -p{mysql_pass} --skip-lock-tables --databases performance_schema information_schema sys  | gzip>dbbackup-per-inf-sys-Date.sql.gz
tar -zcvf GZDumpFileDumpFile MyCNF
Last=`date +"%Y-%m-%d %H:%M:%S"`
#Function export user privileges
mysql_exp_grants()
{   mysql -B -u{mysql_user} -p{mysql_pass} -N -P{mysql_port}   @ -e "SELECT CONCAT(  'SHOW CREATE USER   ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -u{mysql_user} -p{mysql_pass} -N -P{mysql_port}  -f  @ | \
  sed 's##;#g;s/^\(CREATE USER for .*\)/-- \1 /;/--/{x;p;x;}' 

  mysql -B -u{mysql_user} -p{mysql_pass} -N -P{mysql_port}@ -e "SELECT CONCAT(  'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -u{mysql_user} -p{mysql_pass} -N -P{mysql_port}  -f@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'   
}  
mysql_exp_grants > ./mysql_exp_grants_out_Date.sql
echo "data-backup---Start:Begin;Complete:Last;GZDumpFile Out Complete!" >> LogOutFile
#findBinLogBakDir -mtime +7 -name "*bin*.*" -exec rm -rf {} \;
find DataBakDir -mtime +1 -name "*.sql" -exec rm -rf {} \;
findDataBakDir -mtime +15 -name "*.gz" -exec rm -rf {} \;

-------------------------------------------------------------------------------------------------------------------

backup-mysql-binlog.sh

#!/bin/bash
# #script from www.itpux.com and fgjy,use cp to backup mysql binlog everyday!
BinLogBakDir=/bak/backup/backup-binlog
BinLogDir=/bak/backup/binlog                              
LogOutFile=/bak/backup/backup-binlog/bak-bin.log
/usr/local/mysql/bin/mysqladmin -uroot -proot -h10.0.17.173 flush-logs
BinIndexFile=/bak/backup/binlog/binlog.index
NextLogFile=`tail -n 1 BinIndexFile`
LogCounter=`wc -lBinIndexFile |awk '{print 1}'`
NextNum=0
echo "--------------------------------------------------------------------" >>LogOutFile
echo binlog-backup---`date +"%Y-%m-%d %H:%M:%S"` Bakup Start... >> LogOutFile
for binfile in `catBinIndexFile`
do
    base=`basename binfile`
    #basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./
    NextNum=`exprNextNum + 1`
    if [ NextNum -eqLogCounter ]
    then
        echo base skip! >>LogOutFile
    else
        dest=BinLogBakDir/base
        if(test -e dest)
        #test -e用于检测目标文件是否存在，存在就写exist!到LogFile去
        then
            echo base exist! >>LogOutFile
        else
            cp BinLogDir/base BinLogBakDir
            echobase copying >> LogOutFile
         fi
     fi
done
echo binlog-backup---`date +"%Y-%m-%d %H:%M:%S"` Bakup Complete! Next LogFile is:NextLogFile  >> LogOutFile
findBinLogBakDir -mtime +30 -name "*binlog.**" -exec rm -rf {} \;

4. 配置crontab任务

crontab -e
00 02 * * * /mysql/backup/scripts/backup-mysqldump-full.sh /dev/null 2>&1
00 13 * * * /mysql/backup/scripts/backup-mysql-binlog.sh /dev/null 2>&1

测试脚本报错：

[root@server-4d46a18b-14da-4a91-90a6-7bd17c2f79cd scripts]# ./backup-mysqldump-full.sh 
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `GLOBAL_STATUS`': The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56' (3167)

mysql> show variables like '%show_compatibility_56%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| show_compatibility_56 | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> set global show_compatibility_56=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%show_compatibility_56%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| show_compatibility_56 | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> exit
Bye

在my.cnf中加入

“`show_compatibility_56 = 1 “`，然后重启数据库，永久生效。