MYSQL备份数据库_MySQL

恢复整个数据库的步骤：

把需恢复的数据库的整个目录的内容拷贝到其它地方，以备用。

使用最近的备份文件重载数据库。如果使用mysqldump生成的备份，则可使用它们作为mysql的输入重载；如果是通过拷贝数据库目录来备份的，则要关闭数据库服务器，再把备份重新拷贝到数据目录，再重启数据库服务器。

通过二进制日志重做事务，恢复到出错点的数据。具体操作是这样的，用mysqlbinlog把日志转换成ASCII格式，再把它作为mysql的输入，并指定--one-database选项，以便mysql只执行你指定的数据库恢复。如：

% ls -t -r -l binlog.[0-9]* | xargs mysqlbinlog | mysql --one-database db_name
但上面命令只适合所有日志文件名具有相同长度的情况。否则需用下面的Perl代码来处理：

#!/usr/bin/perl -w
# sort_num.pl

use strict;

my @files = <>; #read all input file
@files = sort { my $anum = $1 if $a =~//.(/d+)$/; #sort them by numeric extension
my $bnum = $1 if $b =~//.(/d+)$/;
$anum <=> $bnum;
} @files;
print @files; #print them
exit(0);

如下使用该脚本：
% ls -l binlog.[0-9]* | sort_num.pl | xargs mysqlbinlog | mysql --one-database db_name
上面讨论的是需所有日志文件的情况，但多数情况下我们只需备份后生成的几个日志文件，这时，可用以下命令来重做。

% mysqlbinlog binWebjxCom提示：在MySQL中进行数据备份的方法有两种，一种是使用mysqldump程序，一种是使用mysqlhotcopy、cp、tar或cpio等打包程序直接拷贝数据库文件。mysqldump程序备份数据库较慢，但它生成的文本文件便于移植。使用mysqlhotcopy等程序备份速度快，因为它直接对系统文件进行操作，需

定期的备份可使我们数据库崩溃造成的损失大大降低。

在MySQL中进行数据备份的方法有两种，一种是使用mysqldump程序，一种是使用mysqlhotcopy、cp、tar或cpio等打包程序直接拷贝数据库文件。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把数据库直接拷贝到远程服务器

在把数据库直接拷贝到远程主机时，应注意两台机器必须有同样的硬件结构，或者将拷贝的数据表全部是可移植数据表类型。

使用mysqlhotcopy工具，它是一个Perl DBI脚本，可在不关闭服务器的情况下备份数据库，它主要的优点是：

它直接拷贝文件，所以它比mysqldump快。

可自动完成数据锁定工作，备份时不用关闭服务器。

能刷新日志，使备份文件和日志文件的检查点能保持同步。

下面是该工具的使用示例：

% mysqlhotcopy db /bakcup/ #把db数据库备份到backup/db目录里，会自动创建一个db目录
使用BACKUP TABLE语句进行备份，该语句最早出现在MySQL 3.23.25版本中，仅适用于MyISAM数据表。用法如下：

mysql> BACKUP TABLE mytable TO '/backup/db'; #把mytable数据表备份到/backup/db目录下
为了执行该语句，你必须拥有那些表的FILE权限和SELECT权限，备份目录还必须是服务器可写的。该语句执行时，会先把内存中的数据写入磁盘，再把各个数据表的.frm(表结构定义文件)、.MYD(数据)文件从数据目录拷贝到备份目录。它不拷贝.MYI(索引)文件，因为它能用另外两个文件重建。BACKUP TABLE语句备份时，依次锁定数据表，当同时备份多个数据表时，数据表可能会被修改，所以备份0完成时，备份文件中的数据和现时数据表中的数据可能会有差异，为了消除该差异，我们可用只读方式锁定数据表，在备份完成后再解锁。如：

mysql> LOCK TABLES tb1 READ,tb2 READ;
mysql> BACKUP TABLE tb1,tb2 TO 'backup/db';
mysql> UNLOCK TABLES;
使用BACKUP TABLE语句备份的数据表可用RESTORE TABLE重新加载到服务器。

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';
log.1234 | mysql --one-database db_name
% mysqlbinlog binlog.1235 | mysql --one_database db_name
...
如果我们需恢复因执行DROP DATABASE，DROP TABLE或DELETE语句而损坏的数据库，就需在日志文件中删除该语句，否则重做后结果还是一样。所以需把日志转换成ASCII格式并保存起来，再用编辑器打开该文件，删除这些语句后再重做。

如果使用文本变更日志，则不需使用mysqlbinlog程序，因为该日志文件本身就是ASCII格式。

恢复使用BACKUP TABLE命令备份的数据表可用RESTORE TABLE语句：

备份语句：
mysql> BACKUP TABLE table_name1,table_name2 TO '/backup/table_backup';恢复语句：
mysql> RESTORE TABLE table_name1,table_name2 FROM '/backup/table_backup';
恢复有外键的数据表，可用SET FOREIGN_KEY_CHECK = 0;语句先关闭键字检查，导入表后再重启它，赋值为1表示检查有效。

恢复InnoDB表空间，当服务器重启时，InnoDB处理程序会执行自动恢复工作，通过选项文件[mysqld]段中的set-variable=innodb_force_recovery=level设置自动恢复的级别，推荐典型的启动值为4。如果需从备份文件恢复，则和上面介绍的方法一样。BDB数据表的恢复也一样，启动服务器时它会进行自动恢复。