MySQL备份教程（1）逻辑备份工具mysqldump使用教程

一、MySQL中的逻辑备份

逻辑备份是指通过导出数据库中的逻辑信息（如表结构、视图、索引、存储过程、数据内容等）并保存为可读格式的过程。它将数据库数据以SQL语句或其他标准格式（如CSV、JSON）输出到备份文件中，备份文件可以直接读取或编辑。由于备份文件是SQL语句，可以灵活的跨平台、跨版本迁移，可以用于云或容器这种不能访问底层数据文件的环境。逻辑备份文件在进行还原的时候就是重新执行这些SQL的过程，对于资源消耗较大，耗时较长，并且不支持增量备份，所以不太适用于大型的数据库。

常用的逻辑备份工具有MySQL官方的mysqldump、mysqlpump（mysqldump的升级版，MySQL 5.7 开始支持，命令格式基本一致，区别在于mysqldump是单线程备份单线程恢复，mysqlpump是多线程备份单线程恢复）。另还有第三方逻辑备份工具 mydumper，它也是基于mysqldump优化后的多线程备份工具，支持备份单表，在进行还原时也支持并发工作，效率更快。

所有逻辑备份工具在备份过程中都会有一个FLUSH TABLES + FLUSH TABLE WITH READ LOCK的操作，然后开启一个事务生成快照读，后续的备份是基于这个快照进行，这样可以保证数据一致性，并且让后续的增删改查操作能正常进行。由于是基于快照进行备份的，所以备份的数据是备份那一刻的样子，后面的增量数据需要靠其他手段进行恢复。

在实际工作中建议采用全备+binlog的形式进行备份。首先建立定期的全量备份计划任务，如果全量备份数据量比较大，通常建议一周备份1-2次即可，增量数据则可以通过备份binlog来实现。这样最坏的情况就是恢复3天或7天前的一个全备，然后再恢复增量备份。为了备份数据的安全还要对备份文件进行备份，至少保证备份文件存放于2个不同的机房才是安全的。备份文件的保存期限取决于公司具体要求，为了保证备份有效性可以定期进行备份抽样检查。

二、逻辑备份工具使用说明

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

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

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

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

--set-gtid-purged：备份时是否记录GTID信息（备份文件中会产生set global gtid_purged语句），该选项默认为ON表示记录。在使用这样的备份文件进行数据恢复时，如果目标数据库也开启了GTID模式并且产生过属于目标节点自身的GTID，那么目标节点必须先执行一次RESET MASTER语句清空掉这些信息，否则数据库会避免数据混乱而停止恢复

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

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

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

--where：指定过滤条件进行备份，如只备份某一年的数据或者某个匹配条件的字段，比如id=5的数据，--where='id=5'

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

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

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

--dump-slave：如果是基于Position的传统复制，在从库进行备份时通过该选项来记录主库的Binlog位置，否则将是从库的位置

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

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

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

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

--replace：将INSERT语句替换为REPLACE语句，避免导入数据时主键冲突

-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用户名 -p密码 -S /tmp/mysql3306.sock -B 数据库名 --single-transaction --ignore-table=数据库名.表名 --ignore-table=数据库名.表名 --ignore-table=数据库名.表名 > /data/backup/archery_182.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逻辑备份数据恢复

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

mysql > source /bak/mydata.sql

· 方法2：使用mysql命令恢复数据

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