MySQL Database full standby and add-on, incremental data recovery cases, and scheduled cleanup of Binlog logs

One, MySQL full-scale backup and incremental backup
1, the full amount of backup command:

/application/mysql/bin/mysqldump-uroot-p123456--lock-all-tables-a-b-f--master-data=2--single-transaction-- Events|gzip >/opt/mysql_backup/all_backup/all_backup.sql.gz

如上一段代码所示，其功能是将所有数据库全量备份。其中 MySQL 用户名为：root ，密码为：123456。备份的文件路径为：/opt/Mysql_Backup/all_backup，当然这个路径是按照个人意愿修改的。备份的文件压缩包名为 all_backup.sql.gz参数 --lock-all-tables：锁定所有数据库;参数 -A：备份所有库；参数 -B：指定多个库，增加建库语句和 use 语句；参数 -F：刷新 binlog 日志；参数 --master-data=0|1|2：        0: 不记录        1：记录为CHANGE MASTER语句        2：记录为注释的CHANGE MASTER语句；参数 --single-transaction：适合 innodb 事务数据库备份；参数 --events：导出事件；参数 gzip：备份压缩文件。2、全量备份脚本：

> #!/bin/bash> . /etc/init.d/functions> user=root> password="123456"> Backup_Tools=/application/mysql/bin/mysqldump> BackupDir=/opt/Mysql_Backup> All_Backup=$BackupDir/all_backup> mkdir -p $All_Backup> echo ‘==========‘$(date +"%Y-%m-%d %H:%M:%S")‘==========‘ “备份开始” >>$All_Backup/all_backup.log> $Backup_Tools -u$user -p$password -A -B -F --master-data=2 --single-transaction --events|gzip >$All_Backup/all_backup_$(date +%Y%m%d).sql.gz> if [ $? -eq 0 ]> then>   echo ‘==========‘$(date +"%Y-%m-%d %H:%M:%S")‘==========‘ "备份完成" >>$All_Backup/all_backup.log>   action "Mysql full backup is ok" /bin/true> else>   action "Mysql full backup is not ok" /bin/false> fi    3   、恢复全量备份命令：

cd /opt/Mysql_Backup/all_backupgzip -d all_backup.sql.gzmysql -uroot -p123456 < all_backup.sql

    或者：    mysql> source /opt/Mysql_Backup/all_backup/all_backup.sql  

MySQL Database full standby and add-on, incremental data recovery cases, and scheduled cleanup of Binlog logs