Goal: Make a full backup every 7 days, make a binlog log every day, delete the previous backup and generate a new full backup and Binlog log in the second week, and the backup requirement is automatically completed 2:00 every day.
MySQL version: mysql5.5
1. Turn on Binlog
Vim/etc/my.cnf
[Mysqld]
Log-bin=binlog 2. Restart the MYSQLD service
/etc/init.d/mysqld Restart Verification Binlog Open successfully
Binlog.0000001,binlog.index files will appear under/usr/local/mysql/var/3. Refresh the log to produce a new binlog
Mysql-u root-p flush-logs 4. Delete the previous period's log except for the last
Rm-f ' sed-n ' $!p ' Binlog.index ' 5. Create a new full backup
Mysqldum--all-databases-u root-p 6. Delete last Log
Rm-f ' sed-n ' $p ' binlog.index ' 7. Generating a new incremental backup
Mysqladmin-u root-p flush-logs 8. Delete last Log
Rm-f ' sed-n ' $!p ' Binlog.index ' 9. Re-writing new log information to Binlog.index
Sed-n ' $p ' Binlog.index >tmp
Cat TMP >binlog.index
Procedure: First, a daily:01 to produce a binlog, with crontab to achieve
Crontab-e
* * * * * mysqladmin-u root-pdbpass flush-logs two. Script to generate a new full backup every week and delete the original incremental backup completely
1.vim mysql_bak.sh #!/bin/bash
#This script would backup MySQL database mysql_dir=/usr/local/mysql #delete old binlogs except the last one
Rm-f ' sed-n ' $!p ' $MYSQL _dir/data/binlog.index ' #backup db
Mysqldump-u root-pdbpass dbname >/var/backup/dbname_ ' date + '%y%m%d '. SQL #delete the last Binlog
Rm-f ' sed-n ' $p ' $MYSQL _dir/data/binlog.index ' #create new Binlog by Flush-logs
Mysqladmin-u root-pdbpass flush-logs #write new binlog into Binlog.index
Sed-n ' $p ' $MYSQL _dir/data/binlog.index >/tmp/tmp.index
Cat/tmp/tmp.index > $MYSQL _dir/data/binlog.index2.mv mysql_bak.sh/usr/local/mysql/bin/3.crontab for every Sunday backup
Crontab-e
XX * * 7/usr/local/mysql/bin/mysql_bak.sh
This article from: The Chinese Federation Hacker (China's biggest hacker training center website) detailed source reference: http://www.hmhack.com/article/html/493.html
MySQL Automatic backup policy