mysql定时备份

1. 修改my.cnf

vim /etc/my.cnf
#开启binlog
[mysqld]
log-bin=mysql-bin
server-id=1

#设置mysqldump用户名和密码
[client]
host=localhost
user=root
password=root

2. mysql服务器与备份服务器之间使用无密钥登陆

ssh-keygen -t rsa
ssh-copy-id -i .ssh/id_rsa.pub root@192.168.68.154

3. 备份脚本

vim bak_mysql.sh
#!/bin/bash
backDir=/data/backup
remoteBackDir=/data/mysql
remoteIP=192.168.68.154
[ -d $backDir ] || mkdir -p $backDir
mysqldump -A -B --master_data=2 --single_transaction | gzip > /$backDir/$(date +%F).sql.gz
scp $backDir/$(date +%F).sql.gz root@$remoteIP:$remoteBackDir
find $backDir -type f -name "*.sql.gz" -mtime +7 |xargs rm -f

chmod +x bak_msyql.sh

4. 定时任务
   制作定时任务每天0点执行备份脚本

vim /etc/crontab
00 00 * * * root /usr/bin/sh /root/bak_mysql.sh