MySQL数据库的备份和还原
- 一、备份的方法
- 在数据库表丢失或损坏的情况下，备份你的数据库是很重要的。如果发生系统崩溃，你肯定想能够将你的表尽可能丢失最少的数据恢复到崩溃发生时的状态。
备份策略一：直接拷贝数据库文件（不推荐）
备份策略二：使用mysqlhotcopy备份数据库（完全备份，适合小型数据库备份）
备份策略三：使用mysqldump备份数据库（完全+增量备份，适合中型数据库备份）
备份策略四：使用主从复制机制(replication)（实现数据库实时备份）
1.1 直接拷贝数据库文件
直接拷贝数据文件最为直接、快速、方便但缺点是基本上不能实现增量备份。为了保证数据的一致性，需要在备份文件前，执行以下语句：
FLUSH TABLES WITH READ LOCK;
也就是把内存中的数据都刷新到磁盘中，同时锁定数据表，以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单，直接拷贝回原来的数据库目录下即可。
1.2 使用mysqlhotcopy备份数据库
mysqlhotcopy 是一个PERL程序，最初由Tim Bunce编写。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件（包括数据表定义文件、数据文件、索引文件）所在的机器上，并且mysqlhotcopy只能用于备份MyISAM表。
本备份策略适合于小型数据库的备份，数据量不大，可以采用mysqlhotcopy程序每天进行一次完全备份.
(1)它直接拷贝文件，所以它比mysqldump快。
(2)可自动完成数据锁定工作，备份时不用关闭服务器。
备份策略布置：
（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执行备份脚本。
(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)。
mysqlbackup.sh注释：
- 1 #!/bin/sh
- 2
- 3 # Name:mysqlbackup.sh
- 4
- 5 # PS:MySQL DataBase Backup,Use mysqlhotcopy script.
- 6
- 7 # Write by:i.Stone
- 8
- 9 # Last Modify:2007-11-15
- 10
- 11 #
- 12
- 13 # 定义变量，请根据具体情况修改
- 14
- 15 # 定义脚本所在目录
- 16
- 17 scriptsDir=`pwd`
- 18
- 19 # 数据库的数据目录
- 20
- 21 dataDir=/usr/local/mysql/data/
- 22
- 23 # 数据备份目录
- 24
- 25 tmpBackupDir=/tmp/tmpbackup/
- 26
- 27 backupDir=/tmp/mysqlbackup/
- 28
- 29 # 用来备份数据库的用户名和密码
- 30
- 31 mysqlUser=root
- 32
- 33 mysqlPWD=111111
- 34
- 35 # 定义eMail地址
- 36
- 37 eMail=alter@somode.com
- 38
- 39
- 40 # 如果临时备份目录存在，清空它，如果不存在则创建它
- 41
- 42 if [[ -e $tmpBackupDir ]]; then
- 43
- 44 rm -rf $tmpBackupDir/*
- 45
- 46 else
- 47
- 48 mkdir $tmpBackupDir
- 49
- 50 fi
- 51
- 52 # 如果备份目录不存在则创建它
- 53
- 54 if [[ ! -e $backupDir ]];then
- 55
- 56 mkdir $backupDir
- 57
- 58 fi
- 59
- 60
- 61 # 清空MySQLBackup.log
- 62
- 63 if [[ -s MySQLBackup.log ]]; then
- 64
- 65 cat /dev/null >MySQLBackup.log
- 66
- 67 fi
- 68
- 69
- 70 # 得到数据库备份列表，在此可以过滤不想备份的数据库
- 71
- 72 for databases in `find $dataDir -type d | \
- 73
- 74 sed -e "s/\/usr\/local\/mysql\/data\///" | \
- 75
- 76 sed -e "s/test//"`; do
- 77
- 78
- 79 if [[ $databases == "" ]]; then
- 80
- 81 continue
- 82
- 83 else
- 84
- 85 # 备份数据库
- 86
- 87 /usr/local/mysql/bin/mysqlhotcopy --user=$mysqlUser --password=$mysqlPWD -q "$databases" $tmpBackupDir
- 88
- 89 dateTime=`date "+%Y.%m.%d %H:%M:%S"`
- 90
- 91 echo "$dateTime Database:$databases backup success!" >>MySQLBackup.log
- 92
- 93 fi
- 94
- 95 done
- 96
- 97
- 98 # 压缩备份文件
- 99
- 100 date=`date -I`
- 101
- 102 cd $tmpBackupDir
- 103
- 104 tar czf $backupDir/mysql-$date.tar.gz ./
- 105
- 106
- 107 # 发送邮件通知
- 108
- 109 if [[ -s MySQLBackup.log ]]; then
- 110
- 111 cat MySQLBackup.log | mail -s "MySQL Backup" $eMail
- 112
- 113 fi
- 114
- 115
- 116 # 使用smbclientmv.sh脚本上传数据库备份到备份服务器
- 117
- 118 # $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 "put $BackupDir/mysql-$date.tar.gz \
- mysql-$date.tar.gz"
- # Delete temp files
- rm -f $BackupDir/mysql-$date.tar.gz
1.3 使用mysqlhotcopy备份数据库
mysqldump程序备份数据库较慢，但它生成的文本文件便于移植。使用mysqlhotcopy等程序备份速度快，因为它直接对系统文件进行操作，需人为协调数据库数据的备份前后一致性。
使用mysqldump备份数据库其实就是把数据库转储成一系列CREATE TABLE和INSERT语句，通过这些语句我们就可重新生成数据库。使用mysqldump的方法如下：
% mysqldump –opt testdb | gzip > /data/backup/testdb.bak
#–opt选项会对转储过程进行优化，生成的备份文件会小一点，后的管道操作会进行数据压缩
% mysqldump –opt testdb mytable1,mytable2 | gzip > /data/backup/testdb_mytable.bak
#可在数据库后接数据表名，只导出指定的数据表，多个数据表可用逗号分隔。
–opt选项还可激活–add-drop-table选项，它将会在备份文件的每条CREATE TABLE前加上一条DROP TABLE IF EXISTS语句。这可方便进行数据表的更新，而不会发生“数据表已存在”的错误。
用mysqldump命令还可直接把数据库转移到另外一台服务器上，不用生成备份文件。重复执行可定期更新远程数据库。
% mysqladmin -h remote_host create testdb
% mysqldump –opt testdb | mysql -h remote_host testdb
另外还可通过ssh远程调用服务器上的程序，如：
% ssh remote_host mysqladmin create testdb
% mysqldump –opt testdb | ssh remote_host mysql testdb
通过直接拷贝系统文件的方式备份数据库，在备份时，要确保没有人对数据库进行修改操作。要做到这点，最好关闭服务器。如果不能关闭的，要以只读方试锁定有关数据表。下面是一些示例：
% cp -r db /backup/db #备份db数据库到/backup/db目录
% cp table_name.* /backup/db #只备份table_name数据表
% scp -r db remotehot:/usr/local/mysql/data #用scp把数据库直接拷贝到远程服务器
在把数据库直接拷贝到远程主机时，应注意两台机器必须有同样的硬件结构，或者将拷贝的数据表全部是可移植数据表类型。
InnoDB和BDB数据库也可用mysqldump和直接拷贝法进行备份。使用直接拷贝法时应注意需把组成InnoDB和BDB数据库的所有文件都拷贝下来，如InnoDB的.frm文件、日志文件和表空间配置文件；BDB的数据文件、日志文件等。
使用镜像机制进行备份，我们可用SLAVE STOP语句挂起从服务器的镜像，在从服务器上通过直接拷贝法或其它工具制作备份。备份完成，用SLAVE START重新启动镜像，从服务器重新与主服务器同步，接收备份时主服务器所做的修改。
在MySQL中没有为数据库重命名的命令，但我们可用mysqldump转储数据库，再创建一个新的空数据库，把转储文件加载到该新数据库，这样就完成数据库重命名的工作。如：
% mysqldump old_db >db.sql #转储db数据库数据
% mysqladmin create new_db #新建一个空的数据库
% mysql new_db < db.sql #把db数据库的数据加载到新的数据库中
% mysqladmin drop old_db #删除旧的数据库
一个更简单的重命名数据库的方法是直接修改数据库目录名，但该方法不适用于InnoDB和BDB数据库。注意，在更名后，需在权限表中更新相关数据表信息，需执行以下语句：
mysql> UPDATE db SET db=\’new_db\’ WHERE db=\’old_db\’;
mysql> UPDATE tables_priv SET db=\’new_db\’ WHERE db=\’old_db\’;
mysql> UPDATE columns_priv SET db=\’new_db\’ WHERE db=\’old_db\’;
mysql> UPDATE host SET db=\’new_db\’ WHERE db=\’old_db\’;