本站所有文章均为原创,如对您有帮助,恳请帮忙点击任何一处广告
  • 首页
  • MySQL
  • MySQL备份教程(一)mysqldump/mysqlpump逻辑备份工具的使用

MySQL备份教程(一)mysqldump/mysqlpump逻辑备份工具的使用

发布:TangLu2018-9-10 15:06分类: MySQL 标签: mysql 备份

数据是所有公司的生命线,做好数据备份工作的重要性不言而喻。而在容灾备份中运维人员需要关注六个点:1、数据一致性和完整性;2、备份期间对生产系统的影响;3、异构系统的数据抽取;4、数据传输带宽问题;5、备份数据验证;6、数据恢复的效率。这些问题都做好思考以后就可以开始备份工作了,下面是在MySQL中进行逻辑备份的完成介绍与流程:

一、数据备份的类型与方式

1、根据备份期间是否能正常提供服务可以将备份分为热备份、冷备份、温备份三个类型:备份过程中读写操作均不受影响的叫做热备份;只能进行读操作,无法进行写操作的叫温备份;需要停止服务进行的备份则为冷备份

2、根据备份方式又分为了物理备份与逻辑备份直接复制数据文件的方式称为物理备份,这种备份方式速度快,但为了保持数据一致性通常要停止服务再进行备份,以免内存中的数据丢失。常见的物理备份方式可以是tar命令打包数据目录、cp命令复制数据目录、lvm快照备份或者最出名的备份工具xtrabackup;逻辑备份则是将执行过的SQL导出到一个文本中,可跨平台迁移,但是效率低,如mysqldump命令


二、MySQL的逻辑备份工具介绍

官方推出了2个逻辑备份工作,分别是mysqldump和mysqlpump。前者是是单线程备份,mysqlpump是多线程备份,需要5.7以后才可以使用。两者命令格式基本一致,适用于所有存储引擎,支持完全备份和增量备份。它们执行后会都生成一个备份文件,查看该文件可以看到备份的过程其实是把SQL语句给导出来了,推荐数据小于30G时用此操作。另外还有第三方逻辑备份工具mydumper,基于mysqldump优化,也是多线程工作。

下面介绍官方备份工具mysqldump和mysqlpump的常用选项:

-A | --all-databases备份所有的库

-B | --databases备份多个指定的库。如果没有指定该选项,备份的模式是备份某库中的某张或多张表。该选项还会在备份时增加创建数据库和切换数据库的操作,这样在导入数据的时候就不用再手动创建库了

--single-transaction:导出过程中不允许DDL操作,可以得到一致性的导出结果,只支持innodb。

--master-data={0|1|2}是否将二进制日志位置信息记录到备份文件中(大概22行),这个信息可以用来做增量备份或者主从。三个值的区别:0为不记录1为记录binlog日志的路径和pos信息,做主从同步时从库能自己找到合适的位置,不用手动指定position和binlog(但是仅限于source导入备份)2为记录相关信息但是进行注释,通过二进制日志恢复数据时可以查看该信息。

-d | no-data只备份表结构不包含数据

--ignore-table:忽略指定的某个库或者某个表

--add-drop-database:如果在执行恢复操作时,目标数据库上有同名数据库则先删除

--log-error=/tmp/mysqldump.log:导出时生成日志

-x | --lock-all-tables:备份前锁定所有库的所有表使其只读,避免备份过程中产生了新的写请求,通常用于MyISAM引擎(已经几乎被淘汰),如果是对单个数据库锁定的话则是--lock-tables

-F | --flush-logs:备份前会刷新并生成新的binlog文件,这样备份后的binlog都记录在新的文件中,方便以后使用备份文件+二进制日志进行数据恢复

--default-character-set=utf8:导出时设置默认字符集

--default-parallelismmysqlpump):指定并行线程数,默认是2

--exclude-databasesmysqlpump:备份时排除指定的数据库

--exclude-tablesmysqlpump:备份时排除指定的表


三、恢复MySQL备份数据示例

方法1、登录MySQL客户端后使用source命令恢复数据

mysql > source /bak/mydata.sql


方法2、使用mysql命令恢复数据,适合数据比较大的备份文件

mysql -u root -p test < test.sql  #备份文件时如果没有加-B选项,那么恢复之前需要先use到需要恢复的数据库上

恢复增量数据:由于在备份之后数据还会发生变化,所以还要结合备份文件中记录的二进制位置信息来手动恢复数据,比如全量备份文件中记录了位置信息为872,那么恢复了全量备份后,只需要从其他二进制日志中查找872之后的SQL语句用于恢复即可。另外建议在恢复前建议登录MySQL客户端临时关闭二进制日志再进行导入,避免产生不必要的IO操作

mysqlbinlog mysql-bin.00005 --start-position=251 > bak.sql  #从备份文件中先得知位置信息,然后进行导出
mysqlbinlog mysql-bin.00006  > bak2.sql  #另外一个文件是全部都要导入的,不再需要位置信息
mysql > SET SQL_LOG_BIN=0;
mysql > source bak.sql
mysql > source bak2.sql


四、mysqldump常见使用场景与常用命令示例(最好不要在主库执行备份操作)

1、备份指定的一个数据库

mysqldump -u root -p DATABASE_NAME > /data/dbbak/DATABASE_NAME.sql


2、备份某个库中的某张表

mysqldump -u root -p DATABASE_NAME TABLE_NAME > /data/dbbak/TABLE_NAME.sql


3、备份多个指定的数据库

mysqldump -u root -p -B DATABASE_NAME1 DATABASE_NAME2 > /data/dbbak/DATABASES_NAME.sql


4、备份所有库

mysqldump -u root -p --single-transaction --all-databases > /data/dbbak/ALL.sql


5、使用全备文件恢复单个数据库

mysql -uroot -p123456 -o linuxe < all.sql  #-o选项用于指定单个数据库,其他全部跳过。恢复前需要先创建该数据库
#cat all.sql | grep "CREATE DATABASE" | grep linuxe  #找出创建linuxe数据库的语法


6、只备份某张表的结构,不包含数据

mysqldump -u root -p --no-data DATABASE_NAME TABLE_NAME > /data/dbbak/TABLE_NAME.sql


温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,谢谢合作!
et_highlighter51
版权所有:《Linux运维技术学习站点
文章标题:《MySQL备份教程(一)mysqldump/mysqlpump逻辑备份工具的使用
除非注明,文章均为 《Linux运维技术学习站点》 原创
转载请注明本文短网址:http://www.linuxe.cn/post-131.html  [生成短网址]

已有 0/3605 人参与

发表评论:

欢迎分享Linux运维技术学习站点

欢迎使用手机扫描访问本站,还可以关注微信哦~