细聊MySQL的备份与恢复

备份对于数据库来说是相当重要的工作。如果数据库在使用过程中出现了问题，比如系统崩溃、硬件故障或错误的删除了数据。这时，如果我们进行了数据备份，就能比较方便的使数据库恢复工作，并使我们的数据损失到最

        备份对于数据库来说是相当重要的工作。如果数据库在使用过程中出现了问题，比如系统崩溃、硬件故障或错误的删除了数据。这时，如果我们进行了数据备份，就能比较方便的使数据库恢复工作，并使我们的数据损失到最小。下面，我从备份类型、备份方法及一些常用的例子来和大家详细探讨下数据库的备份与恢复。

一、备份类别

1、物理备份与逻辑备份

物理备份用人话来形容就是复制数据库的数据文件。如果我们需要备份名为test的数据库，则我们可以将数据目录下的test目录复制到备份设备中。如果我们需要备份test库下名为user的表，则可以根据不同的存储引擎选取不同的数据文件来复制。如user是myisam，则可以复制user.frm，user.MYD，user.MYI文件。如user是innodb，则可以复制user.frm，user.ibd。其中.frm是包含数据结构的文件。.MYD是myisam引擎中包含具体数据的文件。.MYI是myisam引擎中包含索引内容的文件。.ibd是innodb引擎中包含具体数据的文件。

逻辑备份是将数据库的结构对象及数据对象（所有的事件、触发器、存储过程等）转化为SQL语句后再保存到备份文件中。所以逻辑备份与具体的操作平台无关。

                物理备份的特征：

                    1、它是直接复制二进制数据文件的，所以我们在恢复数据时只能原样恢复，不能自定义修改数据文件再恢复。

                    2、物理备份比逻辑备份速度更快，因为没有转化SQL这一过程。

                    3、物理备份不支持细粒度的备份，如果你只是想备份某个表中的某一范围的行，可能会难以办到。

                    4、物理备份可能会丢失最新写入到数据库的数据。因为在物理备份时，可能会有新增的数据还驻留在内存中，没有写入硬盘。

                    5、物理备份对平台有一定的要求，如果两个操作系统的文件系统不一样，有可能会不能恢复数据。所以要求尽可能在相同的操作系统及文件系统上实现数据的备份与恢复。

                    6、最好在服务器停止运行时执行物理备份，否则可能需要正确的加锁策略来确保数据一致。

                逻辑备份的特征：

                    1、通过将需备份的数据转换成SQL来完成备份，所以在恢复数据时，我们可以通过修改SQL的方式来自定义恢复的内容，有更大的灵活性。

                    2、因为特征1的原因，所以在速度方面会比物理备份慢，因此不太适合大量数据的备份。

                    3、备份力度方面比物理备份更细，可以到行级。

                    4、不依赖具体的操作系统平台。

                    5、备份时无需停止服务器。

            2、在线备份与离线备份

                在线备份是通过远程客户端连到服务器进行备份，如在客户端使用导出工具导出SQL备份文件。在备份时应该在SQL上加合适的锁防止数据不一致的情况。比如写数据时应加写锁以防止导出数据集时发生数据不一致。

离线备份是在服务器停运的状态下进行备份，因为此时不会有数据操作，所以它在实现上更简单。

            3、完全备份与增量备份

                完全备份就是备份到目前为止的所有数据。增量备份只备份自上一次完全备份或增量备份到至今，期间有所变化的数据。实现完全备份的方法有很多，如使用逻辑备份或者物理备份都可以实现完全备份。完全备份在恢复时是最方便的，但在备份时通常需要耗费很长的时间，所以如果数据库的数据量非常大，每天都执行完全备份是不现实的。应采用完全备份与增量备份相结合的策略。如在每个星期天执行一次完全备份，礼拜一到礼拜五实行增量备份。增量备份的特性与完全备份刚好相反，在备份时会非常的快捷方便，但在还原时会比较繁琐。而且要实现增量备份，我们必须在服务器运行时开启二进制日志。这样我们在恢复时才能根据二进制日志来进行实时恢复。

二、备份方法

       物理备份方法通常是用专门的MySQL备份工具或操作系统的相关指令进行的，这里强调一点，就是在进行物理复制的时候，在执行复制之前应给需要备份的表加读锁，并且需要将驻留在内存的索引信息写入I/O，具体命令为FLUSH TABLES WITH READ LOCK。下面我主要介绍下MySQL普通版本下的备份方法。

使用mysqldump实现数据库的逻辑备份。

            mysqldump命令在mysql安装目录的bin目录下。主要作用是导出SQL文件。具体语法格式如下：

mysqldump [options] db_name [tbl_name ...] 
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

常用的可选参数如下：

参数	描述	适用	废弃
--add-drop-database	在每个 CREATE DATABASE 声明前加上DROP DATABASE 声明
--add-drop-table	在每个 CREATE TABLE 声明前加上DROP TABLE 声明
--add-drop-trigger	在每个 CREATE TRIGGER 声明前加上DROP TRIGGER 声明
--add-locks	在备份时所表
--all-databases	备份所有数据库里的所有表
--allow-keywords	将表名作为列名的前缀
--apply-slave-statements	将STOP SLAVE放在CHANGE MASTER声明前，并将START SLAVE放在结尾
--comments	增加注释到备份文件
--compact	生成更加紧凑的输出
--compatible=name[,name,...]	生成更加兼容的格式
--complete-insert	使用完整的 INSERT 声明
--create-options	在CREATE TABLE声明里包含所有的MySQL指定选项
--databases	备份指定的数据库
--delete-master-logs	在执行dump操作后删除master节点的binlog
--disable-keys	对于每一个表的INSERT声明前加上/*!40000 ALTER TABLE tbl_name DISABLE KEYS */; 和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */，这样可以更快的载入dump文件，但仅仅对具有非唯一索引的MyISAM表有效。
--dump-date	包含dump时间
--extended-insert	使用批量插入语法
--flush-logs	在开始导出前刷新MYSQL server日志文件
--flush-privileges	在导出后刷新权限
--hex-blob	使用16进制导出二进制列
--ignore-error=error[,error]...	跳过指定的错误	5.7.1
--ignore-table=db_name.tbl_name	不导出指定的表
--insert-ignore	写INSERT IGNORE 声明，而不是 INSERT 声明
--lock-all-tables	锁所有的数据库表
--lock-tables	导出前所数据库内的表
--login-path=name	同mysql命令
--max_allowed_packet=value	同mysql命令
--net_buffer_length=value	同mysql命令
--no-autocommit	添加 SET autocommit = 0 和COMMIT 声明在每个 INSERT 声明前
--no-create-db	不创建 CREATE DATABASE 声明
--no-data	不导出表的数据
--no-defaults	同mysql命令
--no-set-names	不设置字符集
--no-tablespaces	不写任何 CREATE LOGFILE GROUP 或 CREATE TABLESPACE 声明
--opt	--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset的缩写
--order-by-primary	按逐渐或唯一索引排序导出数据行
--quote-names	添加引用标识
--replace	用 REPLACE 声明代替 INSERT 声明
--result-file=file	输出文件到指定位置
--secure-auth	同mysql命令	5.7.4	5.7.5
--set-charset	设置字符集
--set-gtid-purged=value	增加 SET @@GLOBAL.GTID_PURGED
--shared-memory-base-name=name	同mysql命令
--single-transaction	在导出前写 BEGIN SQL 声明
--skip-add-drop-table	add-drop-table 反向操作
--skip-add-locks	add-locks 反向操作
--skip-comments	comments 反向操作
--skip-compact	compact 反向操作
--skip-disable-keys	disable-keys 反向操作
--skip-extended-insert	extended-insert 反向操作
--skip-opt	关闭 --opt 选项
--skip-quick	quick 反向操作
--skip-quote-names	quote-names 反向操作
--skip-set-charset	不设置字符集
--skip-triggers	不导出触发器
--skip-tz-utc	关闭 tz-utc 选项
--tab=path	生成一个用tab隔开的数据文件。
--tables	覆盖 --databases 选项
--triggers	导出触发器
--tz-utc	针对列中的TIMESTAMP字段，增加SET TIME_ZONE='+00:00'到导出文件中
--where='where_condition'	导出符合where条件的行
--xml	生成XML输出

以下是一个简单的备份例子，如下所示：

/usr/local/mysql/bin/mysql -uroot -p123456 \
-h127.0.0.1 -e"flush tables with read lock” \
#将驻留在内存中的数据写入表中并给所有表添加读锁

/usr/local/mysql/bin/mysqldump -uroot -p123456 \
-h127.0.0.1  --databases test --add-drop-database \
--add-drop-table --extended-insert --create-options > \
/usr/local/mysql/backup/backup.sql
#备份test库到backup.sql文件。加了—databases后，会在备份文件里
生成创建数据库CREATE DATABASE test;的声明。否则，如果直接使用
mysqldump test > backup.sql命令则不会生成create database语句。
具体参数的运用可以参考上表，如果数据较大，我们可以使用—opt选项进行备份。

/usr/local/mysql/bin/mysql -uroot -p123456 \
-h127.0.0.1 -e"unlock tables”
#解除读锁

三、数据恢复方法

1、mysqldump恢复法

使用mysqldump备份的数据，通常在一个.sql文件中。要恢复数据，只用利用mysql客户端执行备份文件即可。如：

/usr/local/mysql/bin/mysql -uroot -p123456 \
-h127.0.0.1 < /usr/local/mysql/backup/backup.sql

即可

2、二进制日志恢复法

使用此方法的前提是你的服务器是以—log-bin参数运行的，这样服务器才会生成二进制日志。使用二进制日志恢复法需使用mysqlbinlog命令，此命令也在mysql安装目录的bin目录下。恢复命令如下：

/usr/local/mysql/bin/mysqlbinlog xxx-bin.000001 \
| /usr/local/mysql/bin/mysql -uroot -p123456 -h127.0.0.1

诸如此类xxx-bin.000001的格式均为二进制日志文件。xxx-bin.000001里是以二进制存储的对数据库的所有操作，所以此命令执行后会将历史上对数据库的操作进行重做，以此达到恢复数据的目的。值得注意的是，如果你有多个二进制日志文件，如有xxx-bin.000001和xxx-bin.000002这两个文件，如果按以下方式进行恢复将是不安全的：

/usr/local/mysql/bin/mysqlbinlog xxx-bin.000001 \
| /usr/local/mysql/bin/mysql -uroot -p123456 -h127.0.0.1
/usr/local/mysql/bin/mysqlbinlog xxx-bin.000002 \
| /usr/local/mysql/bin/mysql -uroot -p123456 -h127.0.0.1

这样利用两次客户端连线去恢复数据是不安全的，如果在xxx-bin.000001内有一个生成临时表的声明，而在xxx-bin.000002内需要使用这个临时表，则这样操作将出现问题，执行mysqlbinlog xxx-bin.000002时将会找不到这个临时表。所以正确的做法应该是：

/usr/local/mysql/bin/mysqlbinlog xxx-bin.000001 xxx-bin.000002 \
| /usr/local/mysql/bin/mysql -uroot -p123456 -h127.0.0.1

mysqlbinlog恢复数据的方法很灵活，可以通过时间点与位置点进行恢复如：

/usr/local/mysql/bin/mysqlbinlog —start-datetime=‘2014-01-20 8:00:00’\
—stop-datetime=‘2014-01-22 19:00:00' xxx-bin.000001\
| /usr/local/mysql/bin/mysql -uroot -p123456 -h127.0.0.1
#表示重做xxx-bin.000001文件里从2014-01-20 8:00:00到
2014-01-22 19:00:00这个时间范围内的操作。

/usr/local/mysql/bin/mysqlbinlog --start-position=368315\
xxx-bin.000001 | /usr/local/mysql/bin/mysql\
-uroot -p123456 -h127.0.0.1
#表示重做xxx-bin.000001文件内从位置368315开始直到文件末尾的操作。

        关于备份与恢复，一般采用完全备份和增量备份结合的方法。这样可以在保证备份效率的基础上达到实时恢复要求。比如让服务器以—log-bin参数运行，然后每个礼拜天进行一次mysqldump。这样，比如2014年12月7号23点59分完成的备份，2014年12月9日7点需要进行恢复。则首先运行mysql