MySQL 备份恢复（一）

数据是很重要的，没有备份，删库就只能跑路了，当然这只是玩笑话了。但当数据损坏或者误操作删除数据时，备份就显得尤为重要了，备份可以恢复误删除的数据，备份可以作为我们最后的“救命稻草”。MySQL 也是可以按照服务运行状态分为冷备和热备（即停机和非停机），热备份又可以分为逻辑备份和裸设备备份。按照备份后的内容量又可以分为全量备份和增量备份。

冷备：顾名思义，就是数据库处于停机未运行的状态下进行的备份。这种备份的好处是保证数据库的完整性，备份的过程也很简单恢复起来也很迅速，但是需要停机，这对于业务比较频繁的系统而言将是致命的，当用户正在下单时你数据库要停机备份，这肯定是不现实的，故能停机备份的系统都是业务比较少，使用不是很频繁的系统，即非核心系统。

冷备的备份恢复很简单，先将数据库服务停止，然后备份数据目录，恢复时替换原目录，重启服务。

停服务：  ./bin/mysqladmin –uroot–proot shutdown 
备份数据目录： cp –r/opt/mysql    newdirectory
恢复：cp –rnewdirectory /opt/mysql #将备份的数据替换原目录重启数据库即可

热备：与冷备刚好相反，数据库服务在运行状态下进行数据备份，这种情况不用停机不影响现有业务，热备又分为逻辑备份和裸文件备份。常用的备份工具有mysqldump、mydumper、XtraBackup以及MySQL5.7以后出现的mysqlpump 多线程备份，但由于mysqlpump使用的较少且不安全，不在此次讲述范围之内。

mysqldump

mysqldump 是系统自带的工具，也是一个最基础的备份软件，mysqldump 可以保证数据一致性且不影响业务的运行，所产生的备份，最终是要结合 binlog 进行恢复。备份的过程是先从 buffer 中找到需要备份的数据进行备份，如果 buffer 中没有，则去磁盘的数据文件中查找并调回到 buffer 里面在备份，最后形成一个可编辑的以 .sql 结尾的备份文件。

整个备份过程可通过打开通用日志来查看，使用set global general_log=on; 打开通用日志。其中，generallog 的存放路径可通过以下命令查看：

root@db 12:12:  [(none)] showvariables like '%general_log_file%';

观察通用日志也可明白个大概，等有机会在说吧，这里引用脚本之家的一张图片来看看备份的流程。

备份的基本流程如下：

1.调用 FTWRL(flush tables with read lock)，全局禁止读写

2.开启快照读，获取此时的快照(仅对 innodb 表起作用)

3.备份非 innodb 表数据(*.frm,*.myi,*.myd等)

4.非 innodb 表备份完毕后，释放 FTWRL锁

5.逐一备份 innodb 表数据

6.备份完成。

基本上备份就是这么一个流程，下面我们来一起看看 mysqldump 备份工作，可以使用 mysqldump –help 获取更多的信息，全是英文而且比较多，可以慢慢研究，这里仅仅介绍几个比较重要的参数。

--single-transaction

用于保证 InnoDB 备份数据时的一致性，配合可重复读 RR（repetable read）隔离级别使用，当发生事务时，读取一个事务的快照，直到备份结束时，都不会读取到事务开始之后提交的任何数据。

--all-database(-A)

导出全部数据库。

--all-tablespaces(-Y)

导出全部表空间。

--master-data

该参数有 1 和 2 两个值，如果值等于 1，就会在备份出来的文件中添加一个 CHANGE MASTER 的语句（搭建主从复制架构）；如果值等于 2，就会在备份出来的文件中添加一个 CHANGE MASTER 的语句，并在语句前面添加注释符号（后期配置搭建主从架构）。

--dump-slave

该参数用于在从库端备份数据，在线搭建新的从库时使用。该参数也有 1 和 2 两个值。值为 1 时，也是在备份文件中添加一个 CHANGE MASTER 的语句；值为 2 时，则会在 CHANGE MASTER 命令前增加注释信息。

--no-create-info(-t)

备份过程中，只备份表数据，并不备份表结构。

--no-data(-d)

备份过程中，只备份表结构，并不备份表数据。

--complete-insert(-c)

使用完整的 insert 语句会包含表的列信息，可提高插入效率。

--databases(-B)

备份多个数据库。参数后面所有名字参量都被看作数据库名。例如：

mysqldump  -uroot –proot–databases db1 db2

--default-character-set

字符集，MySQL目前默认的字符集为 UTF8，要与备份出来的表的字符集保持一致。例如：

mysqldump  -uroot -proot--all-databases --default-character-set=utf8

--quick(-q)

相当于加 sql_no_query,不缓冲查询，直接导出到标准输出。默认为打开状态，使用 --skip-quick 取消该选项

--where( -w)

只转储给定的 WHERE 条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。例如：

mysqldump  -uroot -p --host=localhost --all-databases --where=” user=’root’”

下面看看备份恢复过程，首先备份整个数据库，命令如下：

[root@JiekeXu tmp]# mysqldump--single-transaction -uroot -proot -A >/tmp/all_20190413.sql

备份文件说明：这里简单说明一下备份内容，文件开头首先表明了备份文件使用的 mysqldump 工具的版本号，然后是备份账号的名称和主机信息，以及备份的数据库的名称，最后是 MySQL 服务器的版本号。接下来的部分是一些 SET 语句，这些语句将一些系统变量值付给用户定义变量，以确保被恢复的数据库的系统变量和原来备份时的变量相同，例如：

/*!40101SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

注意：备份文件开始的一些语句以数字开头，这些数字代表了 MySQL 版本号，该数字告诉我们，这些语句只有在指定的 MySQL 版本或者比该版本高的情况下才能执行。例如 40101，表明这些语句只有在 MySQL 版本号为 4.01.01 或者更高条件下才可以被执行。

备份文件的最后几行 MySQL 使用 SET 语句恢复服务器系统变量原来的值。

备份文件中的"--"字符是注释语句；以"/*!"开头、"*/"结尾的语句是可执行的 MySQL 注释，这些注释可以被 MySQL 执行，但在其他数据库管理系统中将被做为注释忽略，提高了数据库的可以移植性。

恢复全库

恢复全库时可利用 mysql 这个客户端工具来进行，命令如下：

mysql –uroot –proot </tmp/all_20190413.sql

testdb 单库备份: 

mysqldump --single-transaction -uroot -proottestdb >/tmp/testdb_20190413.sql

单库恢复：

mysql –uroot –proot testdb < /tmp/testdb_20190413.sql

注意：单库恢复时需要先创建数据库 create database testdb,然后在做单库恢复。

单库压缩备份：

对于业务发生比较大的公司，数据库的数据肯定会比较大，可能就会使用压缩备份，节省备份时间与磁盘空间的使用。恢复时，先解压缩，然后和前面的恢复一样。

mysqldump -uroot -proot -B testdb |gzip >/tmp/testdb_20190413.sql.gz

备份单表：

mysqldump --single-transaction -uroot -proottestdb t  > /tmp/testdb_t_20190413.sql

单表恢复：

mysql –uroot –proot testdb </tmp/testdb_t_20190413.sql

注意：单表恢复时，在导入符号前不需要写表名，只需要写库名即可。

备份 testdb 库下表 t的表结构信息：

mysqldump --single-transaction -uroot -proottestdb t -d  > /tmp/t.sql

备份 testdb库下表 t 的数据：

mysqldump --single-transaction -uroot -proottestdb t -t  > /tmp/t_data.sql

备份 testdb库下表 t中 id<=2的记录：

mysqldump --single-transaction -uroot -proottestdb t –where=”id<=2”  >/tmp/t_id.sql

注意：这里的 where 后面要记得加双引号（"”）,否则不会被识别。

那么使用 mysqldump 备份恢复就介绍到这里，还有很多场景也许没有涉及到，但限于篇幅等有机会在说吧，mydumper、XtraBackup等备份工具等下次在介绍，保持关注就可以了！

参考资料

http://tencentdba.com/blog/mysqldump-backup-principle/

https://www.jb51.net/article/83652.htm

http://www.cnblogs.com/chenmh/p/5300370.html

张甦 著 《MySQL王者晋级之路》

- End -

推荐阅读：

资源分享：

5T技术资源大放送！包括但不限于：Linux，Python，Oracle，MySQL，Java，前端，大数据，人工智能等，具体获取方式可关注本公众号或者添加我微信获取~~~

长按添加微信，可加入资源技术交流群

获取更多付费资源

长按识别二维码即可关注！

点我，在看