MySQL Backup and Binlog

Modify the/etc/my.cnf below four configurations, if not, add these four.

Log-bin=mysql-bin

Binlog_format=mixed

Expire_logs_days=7

binlog-do-db=your_db

View Binlog

Mysqlbinlog--no-defaults mysql-bin.000001 | More

Recovery Binlog

./mysqlbinlog--start-date= "Yyyy-mm-dd HH:mm:ss"--stop-date= "Yyyy-mm-dd HH:mm:ss"/usr/local/mysql/data/ Mysql-bin.xxxxx/usr/local/mysql/data/mysql-bin.xxxxxx |mysql-u Root-ppassword

MySQL scheduled backup script

#!/bin/bash
#Name: bakmysql.sh
#This is a shellscript for Auto DB backup and Delete old Backup
Bakpath=/data/bakmysql
Time= ' Date +%y%m%d%h '
Mysqldump-uxxxx-pxxxx database_xxxx | gzip > $bakpath/backname$time.sql.gz
#
Find $bakpath-name "backname_*.sql.gz"-type f-mtime +7-exec rm {} \; >/dev/null 2>&1

Modify Crontab

5 3 * * * root/usr/sbin/bakmysql.sh

/etc/init.d/crond restart

MySQL Backup and Binlog