一、 测试环境
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
二、遇到的一些问题
- 在遇到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
- 关于编码问题
可以在/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的测试方式同理,不再赘述。