正在阅读:

mysql备份脚本(线上使用的)

4,797

线上使用的mysql备份脚本,使用mysqldump备份,在mysql master上使用的,在本地备份完成之后,同时传输一份备份文件至远程的ftp服务器上,实现异地备份,本机保存20天的备份文件,远端保存60天的备份文件。

wordpress_mysql_backup

建立备份用户

mysql> grant select,lock tables,reload,super,file,show view on *.* to 'mysqlbackup'@'localhost' identified by 'mysql_ritto'; 
mysql> flush privileges; 

备份脚本

#!/bin/bash

USERNAME=mysqlbackup
PASSWORD=mysql_ritto
 
DATE=`date +%Y-%m-%d`
OLDDATE=`date +%Y-%m-%d -d '-20 days'`
FTPOLDDATE=`date +%Y-%m-%d -d '-60 days'`

MYSQL=/usr/local/mysql/bin/mysql
MYSQLDUMP=/usr/local/mysql/bin/mysqldump
MYSQLADMIN=/usr/local/mysql/bin/mysqladmin
SOCKET=/tmp/mysql.sock
 
BACKDIR=/data/backup/db
[ -d ${BACKDIR} ] || mkdir -p ${BACKDIR}
[ -d ${BACKDIR}/${DATE} ] || mkdir ${BACKDIR}/${DATE}
[ ! -d ${BACKDIR}/${OLDDATE} ] || rm -rf ${BACKDIR}/${OLDDATE}
 
for DBNAME in mysql db1 db2 db3
do
   ${MYSQLDUMP} --opt --master-data=2 --tz-utc=true -u${USERNAME} -p${PASSWORD} -S${SOCKET} ${DBNAME} | gzip > ${BACKDIR}/${DATE}/${DBNAME}-backup-${DATE}.s l.gz
   logger "${DBNAME} has been backup successful - $DATE"
   /bin/sleep 5
done 
 

HOST=10.1.2.22
FTP_USERNAME=db1
FTP_PASSWORD=db1_ritto
 
cd ${BACKDIR}/${DATE}
 
ftp -i -n -v << !
open ${HOST} 
user ${FTP_USERNAME} ${FTP_PASSWORD}
bin
cd ${FTPOLDDATE}
mdelete *
cd ..
rmdir ${FTPOLDDATE}
mkdir ${DATE}
cd ${DATE}
mput *
bye
!

备份后的目录结构是这个样子的

[root@db1 db]# ls 
2011-11-10  2011-11-12  2011-11-14  2011-11-16  2011-11-18  2011-11-20  2011-11-22  2011-11-24  2011-11-26  2011-11-28 
2011-11-11  2011-11-13  2011-11-15  2011-11-17  2011-11-19  2011-11-21  2011-11-23  2011-11-25  2011-11-27  2011-11-29 
[root@db1 db]# ls -lhtr 2011-11-29/ 
total 3.1G 
-rw-r--r-- 1 root root 133K Nov 29 02:01 mysql-backup-2011-11-29.sql.gz 
-rw-r--r-- 1 root root 1.5G Nov 29 02:06 db1-backup-2011-11-29.sql.gz 
-rw-r--r-- 1 root root  35M Nov 29 02:06 db2-backup-2011-11-29.sql.gz 

原文地址:http://ritto.blog.51cto.com/427838/729851

留下脚印,证明你来过。

*

*

流汗坏笑撇嘴大兵流泪发呆抠鼻吓到偷笑得意呲牙亲亲疑问调皮可爱白眼难过愤怒惊讶鼓掌