本站所有文章均为原创,如您有所获益,恳请帮忙点击站内广告支持一下,您的帮助是我持续更新的动力!
  • 首页
  • MySQL
  • MySQL备份教程(1)逻辑备份工具mysqldump、mysqlpump使用教程

MySQL备份教程(1)逻辑备份工具mysqldump、mysqlpump使用教程

发布:TangLu2021-4-3 9:37分类: MySQL 标签: mysql 备份

一、什么是逻辑备份

逻辑备份是将数据的每次变化记录在了备份文件中,查看备份文件可以看出备份的过程其实是把SQL语句给导出来了,然后再导入备份文件到数据库中进行还原。逻辑备份的优点是可跨平台迁移,但是效率较低,通常适合对一些数据量不大的业务进行备份,当数据量达到上百G就不再推荐使用逻辑备份。

MySQL官方提供了2个逻辑备份工具,分别是mysqldump和mysqlpump,两者命令格式基本一致,区别在于mysqldump是单线程备份,mysqlpump是多线程备份(5.7开始支持)。这2个工具均不支持增量备份,在实际工作中也建议采用全备+binlog的形式。两个工具适用于所有存储引擎,但是备份MyISAM表会有FTWRL情况(FLUSH TABLE WITH READ LOCK)。另外还有第三方逻辑备份工具mydumper,它是基于mysqldump优化后的多线程备份工具,由于是基于行进行并发工作,所以可以针对单表并行备份,效率更快。


二、mysqldump使用教程

-A、--all-databases备份所有的库,如果数据太多的话不建议这样备份

-B、--databases:指定需要备份的库,多个库用逗号分隔。如果没有指定该选项,备份的模式是备份某库中的某张或多张表。使用该选项备份多个库时会在备份文件中创建CREATE DATABASE和USE DB_NAME的语句,这样导入数据就不用手动创建库了,但是备份单个库的时候是没有USE DB_NAME的,恢复数据时需要自行指定。

--master-data={0|1|2}:建议必加选项。将备份时binlog的position信息记录到备份文件中(可以在备份完成后通过grep -i change过滤看到)。0为不记录;1为记录binlog position信息并直接执行change master语句;2为记录相关信息但是进行注释,可以通过该信息自行决定是否启用,建议为2。

--single-transaction:建议必加选项。对于Innodb引擎的表在备份时会先START TRANSACTION开启一个事务,备份基于这个事务快照进行,保证了数据一致性的同时让数据能正常更新。该选项对非InnoDB引擎无效。

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

--where:指定过滤条件进行备份,如只备份某一年的数据

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

--ignore-table:忽略指定的表,该选项值的格式为dbname.tablename

--add-drop-database:在备份文件中为每个数据库增加一个DROP DATABASE IF EXISTS语句

--triggers:备份包含了触发器,该选项默认是启动的

--routines:备份包含存储过程和函数

--max_allowed_packet=64M:备份时服务端往客户端传输数据时包的大小限制

--dump-slave:传统复制架构下在从库进行备份时需要加上该选项,记录主库的binlog位置,否则将是从库的位置

--no-create-db:不生成create database语句

--no-create-info:不生成create table语句

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

--default-parallelism:mysqlpump专用,指定备份并行线程数,默认是2

--compress-output:mysqlpump专用,指定压缩方式,如zlib。压缩后的备份文件需要先解压才能还原。

--exclude-databasesmysqlpump专用,备份时排除指定的数据库

--exclude-tablesmysqlpump专用看,备份时排除指定的表

--users:mysqlpump专用,备份时可以附带备份数据库中的用户信息。可以使用该选项来备份数据库中的所有用户,只需要在备份的时候用exclude排除掉所有库即可


三、mysqldump备份实例(最好不要在主库执行备份操作)

· 备份指定的一个或多个数据库

mysqldump -u root -p --single-transaction --triggers  --routines  --master-data=2 -B DATABASE_NAME > /data/dbbak/DATABASE_NAME.sql


· 备份某个库中的某张表

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


· 备份所有库

mysqldump -u root -p --single-transaction --master-data=2 --triggers --routines --all-databases > /data/dbbak/ALL.sql


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

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


· 备份某个库时忽略某张表

 mysqldump -u root -p -B database_name --ignore-table=databasename.tablename > /data/dbbak/database.sql


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

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


· 带过滤条件的备份

/usr/local/mysql/bin/mysqldump -udba -p -S /tmp/mysql3307.sock  --single-transaction --ignore-table=wp_ark.ark_counselorlevel --where="create_time>2020-01-01" wp_ark  > /data/dbbak/wp_ark2020.sql


· 备份的同时进行压缩

mysqldump -udba -p -S /tmp/mysql3307.sock  --single-transaction linuxe_database |gzip -c > linuxe.backup.tgz


· 压缩备份到异地服务器

mysqldump -udba -p -S /tmp/mysql3307.sock  --single-transaction linuxe_database |gzip -c | ssh root@192.168.1.100 'cat > /data/backup/linuxe_database.gz'


四、恢复MySQL备份数据示例

· 登录MySQL客户端后使用source命令恢复数据

mysql > source /bak/mydata.sql


· 使用mysql命令恢复数据,适合数据比较大的备份文件:

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

· 恢复增量数据

由于在备份之后数据还会发生变化,所以还要结合备份文件中记录的二进制位置信息来手动恢复数据,所以备份要养成加上-F和--master-data的习惯。比如全量备份文件中记录了位置信息为872,那么恢复了全量备份后,只需要从其他二进制日志中查找872之后的SQL语句用于恢复即可。另外在恢复前临时关闭二进制日志再进行导入,避免产生不必要的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


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

已有 0/5763 人参与

发表评论:

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

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