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 后面要记得加双引号("”),否则不会被识别。

本文分享自微信公众号 - JiekeXu之路(JiekuXu_IT)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-04-13

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏网络安全防护

Flipboard 数据库未经授权访问用户账号密码泄露

Flipboard是一款广受欢迎的社交分享型新闻聚合应用,用户数量不低于1.5亿。据了解昨天Flipboard发布了安全通告表示,一些包含了Flipboard用...

12840
来自专栏数据分析1480

BAT大数据分析师:如何规划大数据学习之路?

在美国,大数据工程师平均年薪达17.5万美元,在中国顶尖的互联网公司里,大数据工程师的薪酬比同级别的其他职位高出30%以上。DT时代来得太突然了,国内发展势头很...

22920
来自专栏灯塔大数据

学术大讲堂 |(四)浅谈人工智能在公安大数据的应用探索

大家好,我们来共同分享创新智能应用研究中心的人工智能技术在公安大数据方面的应用探索。作为集团公司的研发引擎,我们的技术研发需要面向市场需求,面向实际业务场景。新...

48970
来自专栏Golang开发

MongoDB(2)——基本操作

修改数据 object id每个文档都有一个属性,为_id,保证每个文档的唯一性,可以自己去设置_id插入文档 如果没有提供,那么MongoDB为每个文档提...

8130
来自专栏idba

MySQL Group Replication 入坑指北

MGR(MySQL Group Replication)是MySQL官方推出的一个全新的高可用与高扩展的解决方案,提供高可用、高扩展、高可靠(强一致性)的MyS...

33760
来自专栏Golang开发

Elasticsearch(二)——Rest API

RESTful 接口 URL 的格式是: http://cluster的地址: 9200/<index>I<type>I [<id>] 其中,index, ...

24020
来自专栏fh-admin

Springboot html vue.js 前后分离 跨域 Activiti6 工作流 集成代码生成器 shiro 权限

博文来源:www.fhadmin.org/webnewsdetail12.html

89830
来自专栏开源优测

MySQL诊断调优常用SQL语

在很多时候,我们需要通过SQL语句来查看MySQL执行SQL的情况,例如查看SQL执行队列,是否存在慢查询等等。

15530
来自专栏Golang开发

iOS持续集成(CI)——OCLint静态代码分析

通过 -e 选项来忽略Cocoapods 来pod文件,通过--来分割 oclint-json-compilation-database 的参数与 oclint...

53710
来自专栏Java架构师进阶

MySQL 巨坑:永远不要在 MySQL 中使用 UTF-8

最近我遇到了一个bug,我试着通过Rails在以“utf8”编码的MariaDB中保存一个UTF-8字符串,然后出现了一个离奇的错误:

12940

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励