MySQL 备份和恢复策略

在数据库表丢失或损坏的情况下，备份你的数据库是很重要的。如果发生系统崩溃，你肯定想能够将你的表尽可能丢失最少的数据恢复到崩溃发生时的状态。本文主要对MyISAM表做备份恢复。

<wbr></wbr>

备份策略一：直接拷贝数据库文件（不推荐）

备份策略二：使用mysqlhotcopy备份数据库（完全备份，适合小型数据库备份）

备份策略三：使用mysqldump备份数据库（完全+增量备份，适合中型数据库备份）

备份策略四：使用主从复制机制(replication)（实现数据库实时备份）

备份策略一、直接拷贝数据库文件

直接拷贝数据文件最为直接、快速、方便，但缺点是基本上不能实现增量备份。为了保证数据的一致性，需要在备份文件前，执行以下<wbr>SQL<wbr>语句：</wbr></wbr>

FLUSH<wbr>TABLES<wbr>WITH<wbr>READ<wbr>LOCK;</wbr></wbr></wbr></wbr>

也就是把内存中的数据都刷新到磁盘中，同时锁定数据表，以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单，直接拷贝回原来的数据库目录下即可。

<wbr></wbr>

备份策略二、使用mysqlhotcopy备份数据库

mysqlhotcopy<wbr><span style="font-family:宋体">是一个<wbr>PERL<wbr>程序，最初由Tim<wbr>Bunce编写。它使用<wbr>LOCK<wbr>TABLES、FLUSH<wbr>TABLES<wbr>和<wbr>cp<wbr>或<wbr>scp<wbr>来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件（包括数据表定义文件、数据文件、索引文件）所在的机器上</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span><span style="font-family:宋体">，并且</span>mysqlhotcopy<wbr><span style="font-family:宋体">只能用于备份<wbr>MyISAM</wbr></span><span style="font-family:宋体">表。</span></wbr></wbr>

本备份策略适合于小型数据库的备份，数据量不大，可以采用mysqlhotcopy程序每天进行一次完全备份。

备份策略布置：

（1）、安装DBD-mysql<wbr>perl模块，支持mysqlhotcopy脚本连接到MySQL数据库。</wbr>

shell><wbr>tar<wbr>-xzvf<wbr><wbr>DBD-mysql-4.005.tar.gz</wbr></wbr></wbr></wbr>

shell><wbr>cd<wbr>DBD-mysql-4.005</wbr></wbr>

shell><wbr>unset<wbr>LANG</wbr></wbr>

shell><wbr>perl<wbr>Makefile.PL<wbr>-mysql_config=/usr/local/mysql/bin/mysql_config<wbr>-testuser=root<wbr>-testpassword=UserPWD</wbr></wbr></wbr></wbr></wbr>

shell><wbr>make</wbr>

shell><wbr>make<wbr>test</wbr></wbr>

shell><wbr>make<wbr>install</wbr></wbr>

（2）、设置crontab任务，每天执行备份脚本

shell><wbr>crontab<wbr>-e</wbr></wbr>

0<wbr>3<wbr>*<wbr>*<wbr>*<wbr>/root/MySQLBackup/mysqlbackup.sh<wbr>&gt;/dev/null<wbr>2&gt;&amp;1</wbr></wbr></wbr></wbr></wbr></wbr></wbr>

每天凌晨3:00执行备份脚本。

<wbr></wbr>

mysqlbackup.sh注释：

#!/bin/sh

# Name:mysqlbackup.sh

# PS:MySQL DataBase Backup,Use mysqlhotcopy script.

# Write by:i.Stone

# Last Modify:2007-11-15

#

# 定义变量，请根据具体情况修改

# 定义脚本所在目录

scriptsDir=`pwd`

# 数据库的数据目录

dataDir=/usr/local/mysql/data/

# 数据备份目录

tmpBackupDir=/tmp/tmpbackup/

backupDir=/tmp/mysqlbackup/

# 用来备份数据库的用户名和密码

mysqlUser=root

mysqlPWD=111111

# 定义eMail地址

eMail=[e-mail]

# 如果临时备份目录存在，清空它，如果不存在则创建它

if [[ -e $tmpBackupDir ]]; then

<wbr> rm -rf $tmpBackupDir/*</wbr>

else

<wbr> mkdir $tmpBackupDir</wbr>

fi

# 如果备份目录不存在则创建它

if [[ ! -e $backupDir ]];then

<wbr> mkdir $backupDir</wbr>

fi

# 清空MySQLBackup.log

if [[ -s MySQLBackup.log ]]; then

<wbr> cat /dev/null &gt;MySQLBackup.log</wbr>

fi

# 得到数据库备份列表，在此可以过滤不想备份的数据库

for databases in `find $dataDir -type d | \

<wbr> sed -e "s<span style="color:#ff0000">/\/usr\/local\/mysql\/data\///</span>" | \</wbr>

<wbr> sed -e "s/test//"`; do</wbr>

<wbr> if [[ $databases == "" ]]; then</wbr>

<wbr><wbr><wbr> continue</wbr></wbr></wbr>

<wbr> else</wbr>

# 备份数据库

<wbr><wbr><wbr> /usr/local/mysql/bin/mysqlhotcopy --user=$mysqlUser --password=$mysqlPWD -q "$databases" $tmpBackupDir</wbr></wbr></wbr>

<wbr><wbr><wbr> dateTime=`date "+%Y.%m.%d %H:%M:%S"`</wbr></wbr></wbr>

<wbr><wbr><wbr> echo "$dateTime Database:$databases backup success!" &gt;&gt;MySQLBackup.log</wbr></wbr></wbr>

<wbr> fi</wbr>

done

# 压缩备份文件

date=`date -I`

cd $tmpBackupDir

tar czf $backupDir/mysql-$date.tar.gz ./

# 发送邮件通知

if [[ -s MySQLBackup.log ]]; then

<wbr> cat MySQLBackup.log | mail -s "MySQL Backup" $eMail</wbr>

fi

# 使用smbclientmv.sh脚本上传数据库备份到备份服务器

# $scriptsDir/smbclientmv.sh

<wbr></wbr>

smbclientmv.sh注释

#!/bin/sh

# Name:smbclientmv.sh

# PS:Move the data to Backup Server.

# Write by:i.Stone

# Last Modify:2007-11-15

#

# 定义变量

# 备份服务器名

BackupServer="BackupServerName"

# 共享文件夹名

BackupShare="ShareName"

# 备份服务器的访问用户名和密码

BackupUser="SMBUser"

BackupPW="SMBPassword"

# 定义备份目录

BackupDir=/tmp/mysqlbackup

date=`date -I`

# Move the data to BackupServer

smbclient //$BackupServer/$BackupShare \

$BackupPW -d0 -W WORKGROUP -U $BackupUser \

-c "put $BackupDir/mysql-$date.tar.gz \

mysql-$date.tar.gz"

# Delete temp files

rm -f $BackupDir/mysql-$date.tar.gz

(3)、恢复数据库到备份时的状态
mysqlhotcopy 备份出来的是整个数据库目录，使用时可以直接拷贝到 mysqld 指定的 datadir (在这里是 /usr/local/mysql/data/)目录下即可，同时要注意权限的问题，如下例：
shell> cp -rf db_name /usr/local/mysql/data/
shell> chown -R mysql:mysql /usr/local/mysql/data/ (将 db_name 目录的属主改成 mysqld 运行用户)
本套备份策略只能恢复数据库到最后一次备份时的状态，要想在崩溃时丢失的数据尽量少应该更频繁的进行备份，要想恢复数据到崩溃时的状态请使用主从复制机制(replication)。

备份策略三、使用mysqldump备份数据库

mysqldump<wbr><span style="font-family:宋体">是采用SQL级别的备份机制，它将数据表导成<wbr>SQL<wbr>脚本文件，在不同的<wbr>MySQL<wbr>版本之间升级时相对比较合适，这也是最常用的备份方法。</wbr></wbr></wbr></wbr></span>mysqldump<wbr><span style="font-family:宋体">比直接拷贝要慢些。关于mysqldump的更详细解释见最后的附录。</span></wbr></wbr>

对于中等级别业务量的系统来说，备份策略可以这么定：第一次完全备份，每天一次增量备份，每周再做一次完全备份，如此一直重复。而对于重要的且繁忙的系统来说，则可能需要每天一次全量备份，每小时一次增量备份，甚至更频繁。为了不影响线上业务，实现在线备份，并且能增量备份，最好的办法就是采用主从复制机制(replication)，在<wbr>slave<wbr>机器上做备份。</wbr></wbr>

备份策略布置：

（1）、创建备份目录

Shell><wbr>mkdir<wbr>/tmp/mysqlbackup</wbr></wbr>

Shell><wbr>mkdir<wbr>/tmp/mysqlbackup/daily</wbr></wbr>

（2）、启用二进制日志

采用<wbr>binlog<wbr>的方法相对来说更灵活，省心省力，而且还可以支持增量备份。</wbr></wbr>

启用<wbr>binlog<wbr>时必须要重启<wbr>mysqld。首先，关闭<wbr>mysqld，打开<wbr></wbr></wbr></wbr></wbr></wbr>/etc/my.cnf，加入以下几行：

[mysqld]

log-bin

然后启动<wbr>mysqld<wbr>就可以了。运行过程中会产生<wbr></wbr></wbr></wbr>HOSTNAME-bin.000001<wbr><span style="font-family:宋体">以及<wbr></wbr></span>HOSTNAME-bin.index<span style="font-family:宋体">，前面的文件是<wbr>mysqld<wbr>记录所有对数据的更新操作，后面的文件则是所有<wbr>binlog<wbr>的索引，都不能轻易删除。关于<wbr>binlog<wbr></wbr></wbr></wbr></wbr></wbr></wbr></span><span style="font-family:宋体">的更详细</span><span style="font-family:宋体">信息请查看手册。</span></wbr>

（3）、配置SSH密钥登录，用于将MySQL备份传送到备份服务器（如果备份服务器为Windows，请跳过此部）。

1）、在MySQL所在服务器（192.168.0.20）生成SSH密钥

[root@lab<wbr>~]#<wbr>ssh-keygen<wbr>-t<wbr>rsa</wbr></wbr></wbr></wbr>

Generating<wbr>public/private<wbr>rsa<wbr>key<wbr>pair.</wbr></wbr></wbr></wbr>

Enter<wbr>file<wbr>in<wbr>which<wbr>to<wbr>save<wbr>the<wbr>key<wbr>(/root/.ssh/id_rsa):<wbr><wbr>//<span style="font-family:宋体">直接回车</span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

Enter<wbr>passphrase<wbr>(empty<wbr>for<wbr>no<wbr>passphrase):<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>//<span style="font-family:宋体">直接回车，不使用密码</span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

Enter<wbr>same<wbr>passphrase<wbr>again:<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>//<span style="font-family:宋体">直接回车，不使用密码</span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

Your<wbr>identification<wbr>has<wbr>been<wbr>saved<wbr>in<wbr>/root/.ssh/id_rsa.</wbr></wbr></wbr></wbr></wbr></wbr>

Your<wbr>public<wbr>key<wbr>has<wbr>been<wbr>saved<wbr>in<wbr>/root/.ssh/id_rsa.pub.</wbr></wbr></wbr></wbr></wbr></wbr></wbr>

The<wbr>key<wbr>fingerprint<wbr>is:</wbr></wbr></wbr>

c2:96:9f:2d:5a:8e:08:42:43:35:2f:85:5e:72:f8:1c<wbr>root@lab</wbr>

<wbr></wbr>

2）、在备份服务器（192.168.0.200）上创建目录，修改权限，并传送公钥。

[root@lab<wbr>~]#<wbr>ssh<wbr>192.168.0.200<wbr>"mkdir<wbr>.ssh;chmod<wbr>0700<wbr>.ssh"</wbr></wbr></wbr></wbr></wbr></wbr></wbr>

The<wbr>authenticity<wbr>of<wbr>host<wbr>'192.168.0.200<wbr>(192.168.0.200)'<wbr>can't<wbr>be<wbr>established.</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

RSA<wbr>key<wbr>fingerprint<wbr>is<wbr>37:57:55:c1:32:f1:dd:bb:1b:8a:13:6f:89:fb:b8:9d.</wbr></wbr></wbr></wbr>

Are<wbr>you<wbr>sure<wbr>you<wbr>want<wbr>to<wbr>continue<wbr>connecting<wbr>(yes/no)?<wbr>yes</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

Warning:<wbr>Permanently<wbr>added<wbr>'192.168.0.200'<wbr>(RSA)<wbr>to<wbr>the<wbr>list<wbr>of<wbr>known<wbr>hosts.</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

root@192.168.0.200's<wbr>password:<wbr><wbr><wbr><wbr><wbr>//<span style="font-family:宋体">输入备份服务器的root密码</span></wbr></wbr></wbr></wbr></wbr></wbr>

[root@lab<wbr>~]#<wbr>scp<wbr>.ssh/id_rsa.pub<wbr>192.168.0.200:.ssh/authorized_keys2</wbr></wbr></wbr></wbr>

root@192.168.0.200's<wbr>password:<wbr></wbr></wbr>

id_rsa.pub<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>100%<wbr><wbr>218<wbr><wbr><wbr><wbr><wbr>0.2KB/s<wbr><wbr><wbr>00:00<wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

3）、测试SSH登录

[root@lab<wbr>~]#<wbr>ssh<wbr>192.168.0.200<wbr><wbr><wbr><wbr><wbr><wbr><wbr>//<span style="font-family:宋体">测试SSH登录</span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

Last<wbr>login:<wbr>Fri<wbr>Nov<wbr>16<wbr>10:34:02<wbr>2007<wbr>from<wbr>192.168.0.20</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

[root@lib<wbr>~]#<wbr></wbr></wbr>

<wbr></wbr>

（4）、设置crontab任务，每天执行备份脚本

shell><wbr>crontab<wbr>-e</wbr></wbr>

#每个星期日凌晨3:00执行完全备份脚本

0<wbr>3<wbr>*<wbr>*<wbr>0<wbr>/root/MySQLBackup/mysqlFullBackup.sh<wbr>&gt;/dev/null<wbr>2&gt;&amp;1</wbr></wbr></wbr></wbr></wbr></wbr></wbr>

#周一到周六凌晨3:00做增量备份

0<wbr>3<wbr>*<wbr>*<wbr>1-6<wbr>/root/MySQLBackup/mysqlDailyBackup.sh<wbr>&gt;/dev/null<wbr>2&gt;&amp;1</wbr></wbr></wbr></wbr></wbr></wbr></wbr>

<wbr></wbr>

mysqlFullBackup.sh注释：

#!/bin/sh

# Name:mysqlFullBackup.sh

# PS:MySQL DataBase Full Backup.

# Write by:i.Stone

# Last Modify:2007-11-17

#

# Use mysqldump --help get more detail.

#

# 定义变量，请根据具体情况修改

# 定义脚本目录

scriptsDir=`pwd`

# 定义数据库目录

mysqlDir=/usr/local/mysql

# 定义用于备份数据库的用户名和密码

user=root

userPWD=111111

# 定义备份目录

dataBackupDir=/tmp/mysqlbackup

# 定义邮件正文文件

eMailFile=$dataBackupDir/email.txt

# 定义邮件地址

eMail=[e-mail]

# 定义备份日志文件

logFile=$dataBackupDir/mysqlbackup.log

DATE=`date -I`

echo "" > $eMailFile

echo $(date +"%y-%m-%d %H:%M:%S") >> $eMailFile

cd $dataBackupDir

# 定义备份文件名

dumpFile=mysql_$DATE.sql

GZDumpFile=mysql_$DATE.sql.tar.gz

# 使用mysqldump备份数据库，请根据具体情况设置参数

$mysqlDir/bin/mysqldump -u$user -p$userPWD \

--opt --default-character-set=utf8 --extended-insert=false \

--triggers -R --hex-blob --all-databases \

--flush-logs --delete-master-logs \

--delete-master-logs \

-x > $dumpFile

# 压缩备份文件

if [[ $? == 0 ]]; then

<wbr> tar czf $GZDumpFile $dumpFile &gt;&gt; $eMailFile 2&gt;&amp;1</wbr>

<wbr> echo "BackupFileName:$GZDumpFile" &gt;&gt; $eMailFile</wbr>

<wbr> echo "DataBase Backup Success!" &gt;&gt; $eMailFile</wbr>

<wbr> rm -f $dumpFile</wbr>

# Delete daily backup files.

<wbr> cd $dataBackupDir/daily</wbr>

<wbr> rm -f *</wbr>

# Delete old backup files(mtime>2).

<wbr> $scriptsDir/rmBackup.sh</wbr>

# 如果不需要将备份传送到备份服务器或备份服务器为Windows，请将标绿的行注释掉

# Move Backup Files To Backup Server.

#适合Linux（MySQL服务器）到Linux（备份服务器）

<wbr> $scriptsDir/rsyncBackup.sh</wbr>

<wbr> if (( !$? )); then</wbr>

<wbr><wbr><wbr> echo "Move Backup Files To Backup Server Success!" &gt;&gt; $eMailFile</wbr></wbr></wbr>

<wbr><wbr><wbr> else</wbr></wbr></wbr>

<wbr><wbr><wbr> echo "Move Backup Files To Backup Server Fail!" &gt;&gt; $eMailFile</wbr></wbr></wbr>

<wbr> fi</wbr>

else

<wbr> echo "DataBase Backup Fail!" &gt;&gt; $emailFile</wbr>

fi

# 写日志文件

echo "--------------------------------------------------------" >> $logFile

cat $eMailFile >> $logFile

# 发送邮件通知

cat $eMailFile | mail -s "MySQL Backup" $eMail

mysqlDailyBackup.sh注释：<wbr></wbr>

#!/bin/sh
# Name:mysqlDailyBackup.sh
# PS:MySQL DataBase Daily Backup.
# Write by:i.Stone
# Last Modify:2007-11-17
#
# 定义变量，请根据具体情况修改
# 定义数据库目录和数据目录
scriptsDir=`pwd`
mysqlDir=/usr/local/mysql
dataDir=$mysqlDir/data
# 定义用于备份数据库的用户名和密码
user=root
userPWD=111111
# 定义备份目录，每日备份文件备份到$dataBackupDir/daily
dataBackupDir=/tmp/mysqlbackup
dailyBackupDir=$dataBackupDir/daily
# 定义邮件正文文件
eMailFile=$dataBackupDir/email.txt
# 定义邮件地址
eMail=[e-mail]
# 定义日志文件
logFile=$dataBackupDir/mysqlbackup.log
# 得到数据库所在主机的主机名
HOSTNAME=`uname -n`
#
echo "" > $eMailFile
echo $(date +"%y-%m-%d %H:%M:%S") >> $eMailFile
#
# 刷新日志，使数据库使用新的二进制日志文件
$mysqlDir/bin/mysqladmin -u$user -p$userPWD flush-logs
cd $dataDir
# 得到二进制日志列表
fileList=`cat $HOSTNAME-bin.index`
iCounter=0
for file in $fileList
do
<wbr> iCounter=`expr $iCounter + 1`<br> done<br> nextNum=0<br> iFile=0<br> for file in $fileList<br> do<br><wbr> binLogName=`basename $file`<br><wbr> nextNum=`expr $nextNum + 1`<br> # 跳过最后一个二进制日志（数据库当前使用的二进制日志文件）<br><wbr> if [[ $nextNum == $iCounter ]]; then<br><wbr><wbr><wbr> echo "Skip lastest!" &gt; /dev/null<br><wbr> else<br><wbr><wbr><wbr> dest=$dailyBackupDir/$binLogName<br> # 跳过已经备份的二进制日志文件<br><wbr><wbr><wbr> if [[ -e $dest ]]; then<br><wbr><wbr><wbr><wbr><wbr> echo "Skip exist $binLogName!" &gt; /dev/null<br><wbr><wbr><wbr> else<br> # 备份日志文件到备份目录<br><wbr><wbr><wbr><wbr><wbr> cp $binLogName $dailyBackupDir<br><wbr><wbr><wbr><wbr><wbr> if [[ $? == 0 ]]; then<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> iFile=`expr $iFile + 1`<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> echo "$binLogName Backup Success!" &gt;&gt; $eMailFile<br><wbr><wbr><wbr><wbr><wbr> fi<br><wbr><wbr><wbr> fi<br><wbr> fi<br> done<br> if [[ $iFile == 0 ]];then<br><wbr> echo "No Binlog Backup!" &gt;&gt; $eMailFile<br> else<br><wbr> echo "Backup $iFile File(s)." &gt;&gt; $eMailFile<br><wbr> echo "Backup MySQL Binlog OK!" &gt;&gt; $eMailFile</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

# 如果不需要将备份传送到备份服务器或备份服务器为Windows，请将标绿的行注释掉
# Move Backup Files To Backup Server.
#适合Linux（MySQL服务器）到Linux（备份服务器）

<wbr> $scriptsDir/rsyncBackup.sh<br><wbr> if [[ $? == 0 ]]; then<br><wbr><wbr><wbr> echo "Move Backup Files To Backup Server Success!" &gt;&gt; $eMailFile<br><wbr> else<br><wbr><wbr><wbr> echo "Move Backup Files To Backup Server Fail!" &gt;&gt; $eMailFile<br><wbr> fi<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>fi
# 发送邮件通知
cat $eMailFile | mail -s "MySQL Backup" $eMail
# 写日志文件
echo "--------------------------------------------------------" >> $logFile
cat $eMailFile >> $logFile

<wbr></wbr>

rsyncBackup.sh注释：

#!/bin/sh
# Name:rsyncBackup.sh
# PS:Move Backup Files To Backup Server.
# Write by:i.Stone
# Last Modify:2007-11-17
#
# 请根据具体情况修改，注意最后有“/”
# 定义数据库备份目录
dataBackupDir=/tmp/mysqlbackup/
# 定义备份服务器上存放备份数据的目录
backupServerDir=/root/mysqlbackup/
# 定义备份服务器
backupServer=192.168.0.200
#
# 同步备份文件到备份服务器
rsync -a --delete $dataBackupDir -e ssh $backupServer:$backupServerDir > /dev/null 2>&1

rmBackup.sh注释：

#!/bin/sh
# Name:rmBackup.sh
# PS:Delete old Backup.
# Write by:i.Stone
# Last Modify:2007-11-15
#
# 定义备份目录
dataBackupDir=/tmp/mysqlbackup
# 删除mtime>2的日志备份文件
find $dataBackupDir -name "mysql_*.gz" -type f -mtime +2 -exec rm {} \; > /dev/null 2>&1

（5）<wbr><span style="font-family:宋体">、恢复数据库到备份时的状态</span></wbr>

用<wbr>mysqldump<wbr>备份出来的文件是一个可以直接倒入的<wbr>SQL<wbr>脚本</wbr></wbr></wbr></wbr>,直接用<wbr>mysql<wbr>客户端</wbr></wbr>导入就可以了。<wbr></wbr>

/usr/local/mysql/bin/mysql<wbr>-uroot<wbr>-pUserPWD<wbr>db_name<wbr>&lt;<wbr>db_name.sql</wbr></wbr></wbr></wbr></wbr>

对于任何可适用的更新日志，将它们作为<wbr>mysql<wbr>的输入：<wbr></wbr></wbr></wbr>

　　%<wbr>ls<wbr>-t<wbr>-r<wbr>-1<wbr>HOSTNAME-bin*<wbr>|<wbr>xargs<wbr>mysqlbinlog<wbr>|<wbr>mysql<wbr>-uUser<wbr>-pUserPWD<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

ls<wbr><span style="font-family:宋体">命令生成更新日志文件的一个单列列表，根据服务器产生它们的次序排序（注意：如果你修改任何一个文件，你将改变排序次序，这将导致更新日志以错误的次序被运用。）</span></wbr>

本套备份策略只能恢复数据库到最后一次备份时的状态，要想在崩溃时丢失的数据尽量少应该更频繁的进行备份，要想恢复数据到崩溃时的状态请使用主从复制机制(replication)。如果使用本套备份脚本，将日志文件和数据文件放到不同的磁盘上是一个不错的主义，这样不仅可以提高数据写入速度，还能使数据更安全。