Mysql的全量和增量备份

一、 测试环境
Ubuntu16.04，
mysql 14.14 Distrib 5.7.23, for Linux (x86_64) ，
Windows 10，
mysql：mysql-server-5.7.22.0（mysql-installer-community-5.7.22.0）
workbench：8.0.11 RC

full_backup.sh

#!/bin/bash

db_user="root"
myisam_db=test
db_passwd="root"
host=localhost
mkdir -p full_backup/mysql
backup_dir=full_backup/mysql
log_file=full_backup/mysql/mysql_backup.log
keep_days=7
mysql=$(which mysql)
mysqldump=$(which mysqldump)
export PATH=/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
dbs=$(echo $db_list|tr '\n' ' ')
myisam_dump_file=$backup_dir/MyISAM-$(date +"%Y-%m-%d")-full.sql.gz
innodb_dump_file=$backup_dir/InnoDB-$(date +"%Y-%m-%d")-full.sql.gz
old_file_myisam=$backup_dir/MyISAM-$(date --date="$keep_days days ago" +"%Y-%m-%d")-full.sql.gz
old_file_innodb=$backup_dir/InnoDB-$(date --date="$keep_days days ago" +"%Y-%m-%d")-full.sql.gz
info() {
  if [ $? -eq 0 ];then
    echo "Full backup $1 successfully." >>$log_file
  else
    echo "Full backup $1 failed." >>$log_file
    echo "Full backup $1 failed."|mail -s "Full backup $2 failed" root@localhost
  fi
}


delete_old_files() {
if [ -f $1 ];then
  rm -rf $1
  echo "Delete old file '$1' successfully" >>$log_file
fi
 
}
 
start_time=$(date +"%F %H:%M:%S")
[ ! -d $backup_dir ] && mkdir -p $backup_dir
[ ! -f $log_file ] && touch $log_file
echo "Starting mysql full backup database at $start_time" >>$log_file
$mysqldump -h$host -u$db_user -p$db_passwd -F -B -x --events --triggers \
--routines --master-data=2 $myisam_db|gzip >$myisam_dump_file 2>>$log_file 
info $myisam_dump_file "MyISAM"
$mysqldump -h$host -u$db_user -p$db_passwd -F -B --single-transaction \
--events --triggers --routines --master-data=2 $dbs|gzip >$innodb_dump_file 2>>$log_file 
info $innodb_dump_file "InnoBD"
stop_time=$(date +"%F %H:%M:%S")
echo "End mysql full backup database at $stop_time" >>$log_file
delete_old_files $old_file_myisam
delete_old_files $old_file_innodb
echo " " >>$log_file

incremental_backup.sh

db_user="root"
myisam_db=test
db_passwd="root"
host=localhost
mkdir -p incremental_backup/mysql/backup/daily
mkdir -p incremental_backup/mysql/data
backup_dir=incremental_backup/mysql/backup
BakDir=incremental_backup/mysql/backup/daily
BinDir=/var/lib/mysql    
LogFile=incremental_backup/mysql/backup/bak.log
BinFile=/var/lib/mysql/mysql-bin.index
/usr/bin/mysqladmin -uroot -proot flush-logs
keep_days=7
mysql=$(which mysql)
mysqldump=$(which mysqldump)
export PATH=/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
dbs=$(echo $db_list|tr '\n' ' ')
#used for create mysql-bin.00000*
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0

myisam_dump_file=$backup_dir/MyISAM-$(date +"%Y-%m-%d")-inc.sql.gz
innodb_dump_file=$backup_dir/InnoDB-$(date +"%Y-%m-%d")-inc.sql.gz
old_file_myisam=$backup_dir/MyISAM-$(date --date="$keep_days days ago" +"%Y-%m-%d")-inc.sql.gz
old_file_innodb=$backup_dir/InnoDB-$(date --date="$keep_days days ago" +"%Y-%m-%d")-inc.sql.gz

info() {
  if [ $? -eq 0 ];then
    echo "incremental backup $1 successfully." >>$LogFile
  else
    echo "incremental backup $1 failed." >>$LogFile
    echo "incremental backup $1 failed."|mail -s "Full backup $2 failed" root@localhost
  fi
}
delete_old_files() {
if [ -f $1 ];then
  rm -rf $1
  echo "Delete old file '$1' successfully" >>$LogFile
fi
 
}

#compare $Counter,$NextNum existed or newest！
for file in `cat $BinFile`
do
    base=`basename $file`
    #basename  for acquire mysql-bin.00000*filename，drop./mysql-bin.000005 front./
    NextNum=`expr $NextNum + 1`
    if [ $NextNum -eq $Counter ]
    then
        echo $base skip! >> $LogFile
    else
        dest=$BakDir/$base
        if(test -e $dest)
        #test -e  used for test file existence，if so,then write exist! to $LogFile
        then
            echo $base exist! >> $LogFile
        else
            cp $BinDir/$base $BakDir
            echo $base copying >> $LogFile
         fi
     fi
done
#echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ! >> $LogFile

start_time=$(date +"%F %H:%M:%S")
[ ! -d $backup_dir ] && mkdir -p $backup_dir
[ ! -f $LogFile ] && touch $LogFile
echo "Starting mysql incremental backup database at $start_time" >>$LogFile
$mysqldump -h$host -u$db_user -p$db_passwd -F -B -x --events --triggers \
--routines --master-data=2 $myisam_db|gzip >$myisam_dump_file 2>>$LogFile 
info $myisam_dump_file "MyISAM"
$mysqldump -h$host -u$db_user -p$db_passwd -F -B --single-transaction \
--events --triggers --routines --master-data=2 $dbs|gzip >$innodb_dump_file 2>>$LogFile 
info $innodb_dump_file "InnoBD"
stop_time=$(date +"%F %H:%M:%S")
echo "End mysql incremental backup database at $stop_time" >>$LogFile
delete_old_files $old_file_myisam
delete_old_files $old_file_innodb
echo " " >>$LogFile

二、遇到的一些问题

1. 在遇到sql导入mysql出现乱码的情况。
   Running: mysql.exe --defaults-file=“c:\users\14241\appdata\local\temp\tmplssd4t.cnf” --protocol=tcp --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments --database=test < “C:\Users\14241\Desktop\mysql_backup\MyISAM-2018-08-11-full.sql (7)”

Operation failed with exitcode 1
11:21:45 Import of C:\Users\14241\Desktop\mysql_backup\MyISAM-2018-08-11-full.sql (7) has finished with 1 errors

再要导入的sql文件中的第一行加入/*!40101 SET NAMES utf8 */;即可

11:53:05 Restoring C:\Users\14241\Desktop\MyISAM-2018-08-11-full.sql
Running: mysql.exe --defaults-file=“c:\users\14241\appdata\local\temp\tmpplqzzp.cnf” --protocol=tcp --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments --database=pft < “C:\Users\14241\Desktop\MyISAM-2018-08-11-full.sql”
11:53:06 Import of C:\Users\14241\Desktop\MyISAM-2018-08-11-full.sql has finished

2. 关于编码问题
   

可以在/etc/mysql/mysql.conf.d/mysqld.cnf中进行修改，理论上按照网上的教程在mysqld下方添加default-character-set = utf8,在重新启动mysql可以解决问题，*但是本人ubuntu系统存在一定问题，故而如若该设置不能奏效，依然建议将生产的sql文件内第一行加上/*!40101 SET NAMES utf8 /;即可，另外，如果有的时候下载的数据的zip文件无法正常显示，考虑sudo apt update!

三、 crontab设置
在root权限下先crontab –e 打开脚本，输入以下crontab指令即可：
#每个星期日中午12:00执行完全备份脚本
0 12 * * 0 /bin/bash -x /home/kevin/full_backup.sh >/dev/null 2>&1
效果如下图：

四、 测试
1.crontab的执行

2.sql文件的导入windows中的mysql


3.Refresh All以后，可以看到：

4.对于该表，查看其表内容：Select Rows –limit 1000

5.至此，测试全部结束，full_back.up工作正常。

incremental的测试方式同理，不再赘述。