MySQL 备份和恢复策略
在数据库表丢失或损坏的情况下，备份你的数据库是很重要的。如果发生系统崩溃，你肯定想能够将你的表尽可能丢失最少的数据恢复到崩溃发生时的状态。本文主要对MyISAM表做备份恢复。
备份策略一：直接拷贝数据库文件（不推荐）
备份策略二：使用mysqlhotcopy备份数据库（完全备份，适合小型数据库备份） 备份策略三：使用mysqldump备份数据库（完全+增量备份，适合中型数据库备份） 备份策略四：使用主从复制机制(replication)（实现数据库实时备份）
脚本下载地址：点击下载脚本
备份策略一、直接拷贝数据库文件
直接拷贝数据文件最为直接、快速、方便，但缺点是基本上不能实现增量备份。为了保证数据的一致性，需要在备份文件前，执行以下 SQL 语句： FLUSH TABLES WITH READ LOCK;
也就是把内存中的数据都刷新到磁盘中，同时锁定数据表，以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单，直接拷贝回原来的数据库目录下即可。
备份策略二、使用mysqlhotcopy备份数据库
mysqlhotcopy 是一个 PERL 程序，最初由Tim Bunce编写。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件（包括数据表定义文件、数据文件、索引文件）所在的机器上，并且mysqlhotcopy 只能用于备份 MyISAM表。
本备份策略适合于小型数据库的备份，数据量不大，可以采用mysqlhotcopy程序每天进行一次完全备份。 备份策略布置：
（1）、安装DBD-mysql perl模块，支持mysqlhotcopy脚本连接到MySQL数据库。 shell> tar -xzvf DBD-mysql-4.005.tar.gz shell> cd DBD-mysql-4.005 shell> unset LANG
shell> perl Makefile.PL -mysql_config=/usr/local/mysql/bin/mysql_config -testuser=root -testpassword=UserPWD shell> make shell> make test shell> make install
（2）、设置crontab任务，每天执行备份脚本 shell> crontab -e
0 3 * * * /root/MySQLBackup/mysqlbackup.sh >/dev/null 2>&1 每天凌晨3:00执行备份脚本。
mysqlbackup.sh注释： #!/bin/sh
# Name:mysqlbackup.sh
# PS:MySQLDataBaseBackup,Usemysqlhotcopy 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=alter@somode.com
# 如果临时备份目录存在，清空它，如果不存在则创建它 if [[ -e $tmpBackupDir ]]; then rm -rf $tmpBackupDir/* else
mkdir $tmpBackupDir fi
# 如果备份目录不存在则创建它 if [[ ! -e $backupDir ]];then mkdir $backupDir fi
# 清空MySQLBackup.log
if [[ -s MySQLBackup.log ]]; then cat /dev/null >MySQLBackup.log fi
# 得到数据库备份列表，在此可以过滤不想备份的数据库 for databases in `find $dataDir -type d | \\ sed -e \/\\/usr\\/local\\/mysql\\/data\\///\ sed -e \
if [[ $databases == \ continue else # 备份数据库
/usr/local/mysql/bin/mysqlhotcopy --user=$mysqlUser --password=$mysqlPWD -q \
dateTime=`date \
echo \ fi done
# 压缩备份文件 date=`date -I` cd $tmpBackupDir
tarczf $backupDir/mysql-$date.tar.gz ./
# 发送邮件通知
if [[ -s MySQLBackup.log ]]; then
cat MySQLBackup.log | mail -s \fi
# 使用smbclientmv.sh脚本上传数据库备份到备份服务器 # $scriptsDir/smbclientmv.sh
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 \mysql-$date.tar.gz\
# Delete temp files
rm -f $BackupDir/mysql-$date.tar.gz
(3)、恢复数据库到备份时的状态
mysqlhotcopy 备份出来的是整个数据库目录，使用时可以直接拷贝到 mysqld 指定的 datadir (在这里是 /usr/local/mysql/data/)目录下即可，同时要注意权限的问题，如下例：
shell>cp -rfdb_name /usr/local/mysql/data/
shell>chown -R mysql:mysql /usr/local/mysql/data/ (将 db_name 目录的属主改成 mysqld 运行用户)
本套备份策略只能恢复数据库到最后一次备份时的状态，要想在崩溃时丢失的数据尽量少应该更频繁的进行备份，要想恢复数据到崩溃时的状态请使用主从复制机制(replication)。 备份策略三、使用mysqldump备份数据库
mysqldump 是采用SQL级别的备份机制，它将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。mysqldump 比直接拷贝要慢些。关于mysqldump的更详细解释见最后的附录。
对于中等级别业务量的系统来说，备份策略可以这么定：第一次完全备份，每天一次增量备份，每周再做一次完全备份，如此一直重复。而对于重要的且繁忙的系统来说，则可能需要
每天一次全量备份，每小时一次增量备份，甚至更频繁。为了不影响线上业务，实现在线备份，并且能增量备份，最好的办法就是采用主从复制机制(replication)，在 slave 机器上做备份。 备份策略布置： （1）、创建备份目录
Shell> mkdir /tmp/mysqlbackup Shell> mkdir /tmp/mysqlbackup/daily （2）、启用二进制日志
采用 binlog 的方法相对来说更灵活，省心省力，而且还可以支持增量备份。
启用 binlog 时必须要重启 mysqld。首先，关闭 mysqld，打开 /etc/my.cnf，加入以下几行： [mysqld] log-bin
然后启动 mysqld 就可以了。运行过程中会产生 HOSTNAME-bin.000001 以及 HOSTNAME-bin.index，前面的文件是 mysqld 记录所有对数据的更新操作，后面的文件则是所有 binlog 的索引，都不能轻易删除。关于 binlog 的更详细信息请查看手册。 （3）、配置SSH密钥登录，用于将MySQL备份传送到备份服务器（如果备份服务器为Windows，请跳过此部）。
1）、在MySQL所在服务器（192.168.0.20）生成SSH密钥 [root@lab ~]# ssh-keygen -t rsa Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): //直接回车
Enter passphrase (empty for no passphrase): //直接回车，不使用密码 Enter same passphrase again: //直接回车，不使用密码 Your identification has been saved in /root/.ssh/id_rsa. Your public key has been saved in /root/.ssh/id_rsa.pub. The key fingerprint is:
c2:96:9f:2d:5a:8e:08:42:43:35:2f:85:5e:72:f8:1c root@lab
2）、在备份服务器（192.168.0.200）上创建目录，修改权限，并传送公钥。 [root@lab ~]# ssh 192.168.0.200 \
The authenticity of host '192.168.0.200 (192.168.0.200)' can't be established. RSA key fingerprint is 37:57:55:c1:32:f1:dd:bb:1b:8a:13:6f:89:fb:b8:9d. Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.200' (RSA) to the list of known hosts. root@192.168.0.200's password: //输入备份服务器的root密码 [root@lab ~]# scp .ssh/id_rsa.pub 192.168.0.200:.ssh/authorized_keys2