数据是很重要的,没有备份,删库就只能跑路了,当然这只是玩笑话了。但当数据损坏或者误操作删除数据时,备份就显得尤为重要了,备份可以恢复误删除的数据,备份可以作为我们最后的“救命稻草”。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 -
推荐阅读:
Windows环境下Oracle11gR2的安装与卸载
关系型数据库MySQL之InnoDB体系结构
关系型数据库MySQL表索引和视图详解
Linux 运维必备的 40 道面试精华题
关系型数据库MySQL体系结构详解
资源分享:
5T技术资源大放送!包括但不限于:Linux,Python,Oracle,MySQL,Java,前端,大数据,人工智能等,具体获取方式可关注本公众号或者添加我微信获取~~~
长按添加微信,可加入资源技术交流群
获取更多付费资源
长按识别二维码即可关注!