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 -

推荐阅读:

模拟真实环境下超简单超详细的MySQL 5.7安装

Windows环境下Oracle11gR2的安装与卸载

关系型数据库MySQL之InnoDB体系结构

关系型数据库MySQL表索引和视图详解

Linux 运维必备的 40 道面试精华题

关系型数据库MySQL体系结构详解

资源分享:

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

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

获取更多付费资源

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

点我,在看

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值