MySQL备份教程(1)逻辑备份之mysqldump

TangLu 7307 2021-04-03

一、逻辑备份原理介绍

逻辑备份是将数据的每次变化以SQL的形式记录在了文件中,在进行还原的时候就是重新执行这些SQL的过程。逻辑备份的优点是可跨平台迁移,但是效率较低,通常适合对一些数据量不大的业务进行备份,当数据量达到百G以上基本就不建议逻辑备份了。常用的逻辑备份工具有官方推出的mysqldump、mysqlpump和第三方工具mydumper。其中mysqlpump是mysqldump的升级版,从MySQL 5.7开始支持使用,两者命令格式基本一致,区别在于mysqldump是单线程备份单线程恢复,mysqlpump是多线程备份单线程恢复mydumper则属于第三方逻辑备份工具,它是基于mysqldump优化后的多线程备份工具,支持备份单表,在进行还原时也支持并发工作,所以效率更快。

所有逻辑备份工具在备份过程中都会有一个FLUSH TABLES + FLUSH TABLE WITH READ LOCK的操作,然后开启一个事务生成快照读,后续备份基于这个快照进行(这些操作可以开启general log进行查看),这样可以保证数据一致性,并且让后续的增删改查操作能正常进行。逻辑备份通常不支持直接增量备份,所以在实际工作中建议采用全备+binlog的形式进行备份。首先建立定期的全量备份计划任务,如果全量备份数据量比较大,通常建议一周备份1-2次即可,增量数据则可以通过备份binlog来实现。这样最坏的情况就是恢复3天或7天前的一个全备,然后再恢复增量备份。为了备份数据的安全还要对备份文件进行备份,至少保证备份文件存放于2个不同的机房才是安全的。备份文件的保存期限取决于公司具体要求,为了保证备份有效性可以定期进行备份抽样检查


二、mysqldump | mysqlpump常用选项

--master-data={0|1|2}建议必加选项。备份时是否记录binlog position信息(备份时会先FTWRL获取快照读,接着SHOW MASTER STATUS获取binlog信息,最后UNLOCK开始真正的备份)。该选项有三个值,0为不记录binlog;1为记录binlog position信息并直接执行change master语句;2为记录相关信息但是进行注释,可以通过该信息自行决定是否启用,建议为2

--single-transaction:建议必加选项。对于Innodb引擎的表在备份时会先START TRANSACTION开启一个事务,期间会短暂的FTWRL(毫秒级别)。后续备份基于这个事务快照进行,保证了数据一致性的同时让数据能正常更新。如果没有加该选项,备份将按照单表一张一张进行,每个表的备份时间是不一样的

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

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

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

--triggers:备份时包含触发器,该选项默认开启,--skip-triggers为关闭

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

--set-gtid-purged=OFF:该选项默认为ON,如果数据库有开启GTID,则备份的时候会带上GTID信息。但是这样的备份文件在进行数据恢复的时候需要注意,如果目标数据库没有开启GTID模式可以直接导入,如果目标数据库开启了GTID模式且已经有产生一条或以上GTID,则必须需要执行RESET MASTER语句清空了GTID信息才可以恢复数据

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

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

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

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

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

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

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

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

–skip-extended-insert:备份文件中的INSERT语句一次只插入一行,而不会进行合并

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

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

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

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

--exclude-tables:mysqlpump专用看,备份时排除指定的表

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


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

· 备份所有数据库

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


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

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


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

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到需要恢复的数据库上


版权声明
本站所有文章均为原创,转载请注明出处!小站维护不易,如果对您有所帮助,希望能点击一下站内广告,谢谢!
上一篇:【Redis运维】Redis 3.x配置文件示例与说明
下一篇:MySQL备份教程(2)物理备份之xtrabackup/innobackupex
相关文章

 发表评论

暂时没有评论,来抢沙发吧~

微信二维码