Mysql之数据库备份与恢复

一、数据库备份与恢复

在数据库的实际使用过程中，可能会因下列各种因素导致数据部分或全部丢失：
（1）计算机硬件故障
（2）计算机软件故障
（3）病毒
（4）人为误操作
（5）自然灾害
（6）盗窃
所以数据库系统提供了备份和恢复策略来保证数据库中数据的可靠性和完整性。
数据库备份是指通过导出数据或者拷贝表文件的方式来制作数据库的副本。

二、mysql数据库备份与恢复的方法

mysql数据库中的备份和恢复组件，为存储在mysql数据库中的关键数据提供了重要的保护手段。这里介绍四种常用的备份和恢复方法

1、使用sql语句备份与恢复表数据

该方法只能导出或导入数据的内容，而不包括表的结构。若表的结构文件损坏，则必须先设法恢复表结构。这里我已经创建了一个名叫mysql_test的数据库，并在这个数据库里创建了一个叫customers的表，大家可以根据自己创建的数据库和表测试下面sql语句。
导出mysql_test数据库中customers表的数据到指定文件目录下进行备份：

select * from mysql_test.customersinto outfile 'd:/backup/backupfile.txt'--指定导出的数据文件路径fields terminated by ','--表示两个字段之间用“，”分隔，['tɜ:mɪneɪtɪd]结束的意思optionally enclosed by '"'--表示文件中的字符值放在双引号之间lines terminated by '?';--表示一个数据行以“？”作为结束标志

下面就是导出的备份文件，其中出现的 \N 表示空值

然后，我们先创建一个customers表的拷贝customers_copy，这是一个空表

create table mysql_test.customers_copylike mysql_test.customers;

将备份数据导入空表，按照这样就可以完成了数据的恢复了

load data infile 'd:/backup/backupfile.txt'into table mysql_test.customers_copyfields terminated by ','    optionally enclosed by '"'lines terminated by '?';

这里出现乱码是因为编码方式的问题，在命令行输入下列语句即可：

set names gbk;

关于mysql中文乱码问题详解，请移步另一篇博客文章：http://blog.csdn.net/paranoidyang/article/details/57128346

2、使用mysql客户端实用程序备份和恢复数据

（1）打开mysql客户端实用程序的方法

打开cmd的命令行窗口，进入mysql安装目录下的bin子目录，如我的是：D:\Program Files\wampserver\wamp\bin\mysql\mysql5.5.20\bin，则在命令行输入下面命令:

cd D:\Program Files\wampserver\wamp\bin\mysql\mysql5.5.20\bin

如上图所示即为mysql客户端实用程序运行界面，由此可在该界面光标闪烁处输入所需的mysql客户端实用程序命令。

（2）使用mysqldump程序备份数据

该方法不仅可以导出或导入数据的内容，还包括表的结构。在上图所示的光标闪烁处输入所需的mysql客户端实用程序命令，下面是一些实例。
备份数据库mysql_test中的表customers到d:\backup\file.sql：

mysqldump -h localhost -u root -p123456 mysql_test customers>d:\backup\file.sql  /*-h后面是主机名，如果是本地服务器，-h可以省略-u后面是用户名-p后面是密码，p与密码之间不能有空格这些命令都是为了连接mysql服务器的*/

然后在d盘的backup下就可以看到一个叫file.sql的备份文件：

同理，也可以备份数据库mysql_test和数据库mysql到d:\backup\data.sql：

mysqldump -u root -p123456 --databases mysql_test mysql>d:\backup\data.sql

备份mysql服务器上所有数据库：

mysqldump -u root -p123456 --all-databases>d:\backup\alldata.sql

将上面的命令都执行一遍后，在d盘的backup目录下就会有下面这些文件 ：

有时候 要恢复的数据量很大，备份文件中众多的sql语句会使恢复的效率降低，所以可以在mysqldump命令中使用“–tab=”选项来分开数据和创建表的sql语句。这样一来，每个表会对应两个文件，一个是.txt格式的存储数据内容的文件，另一个是.sql格式的包含表结构创建的文件。
如：将数据库mysql_test中所有表的表结构和数据分别备份到d盘的backup目录下。

mysqldump -u root -p123456 --tab=d:\backup\mysql_test

（3）使用mysql命令恢复数据

假设数据库mysql_test 遭遇损坏，试用该数据库的备份文件mysql_test.sql将其恢复：

mysql -u -p123456 mysql_test<mysql_test.sql

如果只是表结构损坏了，也可以使用mysql命令对其单独做恢复处理，不过表中的数据将被清空。如下面的customer表结构损坏了，对其进行恢复：

mysql -u root -p123456 customers<d:\backup\customers.sql    --customers.sql是存储customers表结构的备份文件，位置放在d盘的backup目录下

（4）使用mysqlimport程序恢复数据

如果只是为了恢复数据表中的数据，还可以使用mysqlimport客户端实用程序来完成。如下面使用备份数据文件customers.txt恢复数据库mysql_test中表customers的数据：

mysqlimport -u root -p123456 --low-priority --replace mysql_test d:\backup\customers.txt     

3、使用mysql图形界面工具备份和恢复数据

用图形界面工具进行备份和恢复相对于前面两种，会简单些。这里以phpMyAdmin为例，介绍一下相关操作。
备份数据库
用web方式登录phpMyAdmin：http://localhost:8080/phpmyadmin/（这里的端口号为8080是我自定义的，如果你没修改过端口号，则默认为80，此时直接写localhost即可）
出现下图所示界面，导出选项就是用来备份的，下面有个格式选择下拉框可以选择备份文件的格式。只要选定左边要备份的数据库和表，然后点执行按钮即可完成数据库的备份，是不是超级简单哈。

恢复数据库
恢复数据库也是很简单，导入选项，然后选取欲导入的备份文件，点击执行即可。

4、直接复制

这个就不用多说什么了。

三、二进制日志文件的使用

二进制日志包含了数据备份后进行的所有更新（不包含没有修改任何数据的语句），实时记录了数据库中修改、插入和删除的sql语句，能够在数据恢复时最大可能地更新数据库。
开启日志文件
mysql服务器若启用二进制日志，系统性能会有所降低，而且会浪费一定的存储空间，所以默认是不开启的，需要手动启用。
（1）打开mysql安装目录下的my.ini文件
（2）找到[mysqld]这个标签，在该标签下，添加以下一行语句：

log-bin=D:/Program Files/wampserver/wamp/bin/mysql/mysql5.5.20/bin/bin_log/*log-bin说明要开启二进制日志文件，=号后面的是二进制日志路径，其中bin_log是二进制日志文件名*/

如下图所示：

保存修改，重启mysql服务器。
此时，在D:/Program Files/wampserver/wamp/bin/mysql/mysql5.5.20/bin下就可以看到两个这样的文件
bin-log.数字编号：二进制日志文件，以二进制形式存储，用于保存数据库更新信息，每次启动服务器或刷新日志时，都会重新生成一个二进制日志文件，其扩展名中的数字编号依次递增。
bin-log.index：服务器自动创建的二进制日志索引文件，包含所有使用的二进制日志文件的文件名。
如下图所示：

使用mysqlbinlog实用工具处理日志
开启二进制日志后，系统自动生成的二进制日志文件可以通过实用工具mysqlbinlog来处理。
（1）查看二进制日志文件

mysqlbinlog bin_log.0000001>d:\backup\bin_log000001.txt/*由于二进制日志数据可能会非常庞大，而无法在屏幕上延伸，此时可以采用重定向的方法将二进制数据保存到bin_log000001.txt这个文本文件中，以便查看*/

（2）使用二进制日志恢复数据
这里要注意的是，二进制日志记录的是更新数据的语句，不包含没有修改任何数据的语句。

mysqlbinlog bin_log.000001 | mysql -u root -p123456

由于二进制日志文件会占用很大的硬盘资源，所以需要及时清除没用的二进制日志文件。可以使用下面sql语句清除所有的日志文件：

reset master;