CentOS自动备份mysql的方法很多很多我随便就找到几个利用shell写的非常不错的一些脚本了，下面我整理一下希望对各位朋友会帮助
一，定时备份mysql数据库
安全起见，直接用Root执行的：
| 代码如下 |
|
|
/root/mysql_backup.sh
# everyday 3:00 AM execute database backup
3 0 * * * /root/mysql_backup.sh
|
以下是自动自动备份shell，只保留最新5天
| 代码如下 |
|
|
#!/bin/sh
# mysql_backup.sh: backup mysql databases and keep newest 5 days backup.
#
# db_user is mysql username
# db_passwd is mysql password
# db_host is mysql host
# —————————–
db_user="root"
db_passwd="zhoz.com"
db_host="localhost"
# the directory for story your backup file.
backup_dir="/home/zhozdbbackup"
# date format for backup file (dd-mm-yyyy)
time="$(date +"%d-%m-%Y")"
# mysql, mysqldump and some other bin's path
MYSQL="/usr/bin/mysql"
MYSQLDUMP="/usr/bin/mysqldump"
MKDIR="/bin/mkdir"
RM="/bin/rm"
MV="/bin/mv"
GZIP="/bin/gzip"
# check the directory for store backup is writeable
test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0
# the directory for story the newest backup
test ! -d "$backup_dir/backup.0/" && $MKDIR "$backup_dir/backup.0/"
# get all databases
all_db="$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse 'show databases')"
for db in $all_db
do
$MYSQLDUMP -u $db_user -h $db_host -p$db_passwd $db | $GZIP -9 > "$backup_dir/backup.0/$time.$db.gz"
done
# delete the oldest backup
test -d "$backup_dir/backup.5/" && $RM -rf "$backup_dir/backup.5"
# rotate backup directory
for int in 4 3 2 1 0
do
if(test -d "$backup_dir"/backup."$int")
then
next_int=`expr $int + 1`
$MV "$backup_dir"/backup."$int" "$backup_dir"/backup."$next_int"
fi
done
exit 0;
|
上面备份不会自动上传到ftp服务器了，我们可以参考下面实例
二，自动备份mysql并上传到远程ftp脚本
生产需要，在centos 5.4下自动备份mysql数据库并上传到远程ftp，花了点时间，脚本如下：
| 代码如下 |
|
|
#!/bin/bash
#auto ftp bakup
#by xhat 2010.06.08
#要备份的数据库
DBName=wwwdbname
#ftp账号信息
ftphost="test.com"
ftpuser=username
ftppass=passwd
#mysql账号信息
DBUser=root
DBPasswd=123456
BackupPath=./
NewFile="$BackupPath"db$(date +%y%m%d).tar.gz
DumpFile="$BackupPath"db$(date +%y%m%d).sql
OldFile="$BackupPath"db$(date +%y%m%d --date='1 days ago').tar.gz
#LogFile="$BackupPath"db$(date +%y%m%d --date='1 days ago').txt
echo "---------------------------"
echo $(date +"%y-%m-%d %H:%M:%S")
echo "---------------------------"
#删除
if [ -f $OldFile ]; then
rm -f $OldFile
echo "[$OldFile]Delete Old File Success!"
fi
#新
if [ -f $NewFile ]; then
echo "[$NewFile] 已经存在，不再备份! "
else
/opt/mysql/mysqldump -u$DBUser -p$DBPasswd $DBName > $DumpFile
sleep 5
tar czvf $NewFile $DumpFile
rm $DumpFile -rf
echo "[$NewFile]备份成功"
fi
echo "打开ftp"
ftp -i -in < open $ftphost 21
user $ftpuser $ftppass
cd /
mput *.tar.gz
bye
!
echo "备份完成"
|
完成上面脚本后，在crontab里面加入每天凌晨定时执行即可。
| 代码如下 |
|
| 10 3 * * * /opt/bak/mybak |
小结
其实方法一我们可以使用rsync同步来进行同步己经备份好的数据，在ftp与rsync相比rsync性能与稳定性是ftp无法比较的。
