第84讲：基于各种场景使用mysqldump逻辑备份数据库

1.mysqldump备份工具的语法格式

mysqldump的使用语法：

• 备份指定数据库

  • mysqldump 选项 数据库

• 备份指定数据库下的某张表

  • mysqldump 选项 数据库 表

• 备份多个数据库

  • mysqldump 选项 --database/-B 数据库1 数据库2

• 备份所有数据库

  • mysqldump 选项 --all-databases/ -A

mysqldump连接数据库的选项：

• -u,--user：指定登陆数据库的用户名。
• -p,--password：指定登录数据库的密码
• -h,--host：指定要登陆的MySQL服务器地址。
• -P,--port：指定MySQL数据库的端口号。
• -S：指定当前数据库实例的sock文件。

mysqldump输出选项：

• --add-drop-database：在每个数据库创建语句前加上 drop database 语句。

• --add-drop-table：在每个表创建语句前加上 drop table 语句 , 默认开启 ; 不开启 (–skip-add-drop-table) 。

• -n, --no-create-db：不包含数据库的创建语句。

• -t, --no-create-info：不包含数据表的创建语句。

• -d --no-data：不包含数据。

• -T, --tab=name：自动生成两个文件：一个.sql文件，创建表结构的语句；一 个.txt文件，数据文件 。

2.使用mysqldump进行全库备份

使用mysqldump对本机上的所有MySQL数据库全部都进行备份。

[root@mysql ~]# mysqldump -uroot -p123456 -A -S /tmp/mysql.sock > /data/backup/all_db.sql

mysqldump工具备份的数据库，有创建数据库的语句、创建表的语句、插入数据的语句，都是我们能看懂的SQL。

3.备份单个库或者多个库的数据

1）备份单个库

[root@mysql ~]# mysqldump -uroot -p123456 -B db_1 > /data/backup/db_1.sql

2）备份多个库

[root@mysql ~]# mysqldump -uroot -p123456 -B db_1 db_2 > /data/backup/db_1_db_2.sql

4.备份某个库下的单表或者多表的数据

需求：备份db_1数据库下的bmxxb或者ryxxb表

[root@mysql ~]# mysqldump -uroot -p123456 -B db_1 --tables bmxxb ryxxb > /data/backup/table_bak.sql

或者 

[root@mysql ~]# mysqldump -uroot -p123456 db_1 bmxxb ryxxb > /data/backup/table_bak.sql

注意此种方法备份的表，在还原时，需要数据库已经存在，如果数据库不存在则表不能还原，而且还要进入到这个数据库中进行还原。

5.mysqldump备份数据库时必加的一些参数

5.1.基本参数

-R：在备份数据时，同时备份库中的所有存储过程和函数，如果没有存储过程和函数时，会自动忽略。

-E：在备份数据时，同时备份EVENT事件，如果没有则会自动忽略。

--triggers：在备份数据时，同时备份库中包含的触发器，如果没有则自动忽略。

如果不加这三个参数，当库中头存储过程和函数以及触发器时，我们没有备份，通过备份还原数据时，会把库覆盖掉，里面的存储过程和函数以及触发器都会丢失。

5.2.核心参数

在mysqldump备份数据时，有两个非常重要的核心参数，这两个核心参数是mysqldump备份时必加的参数。

首先我们来思考一个备份还原的场景：

我们的备份时在每天晚上0点进行全库备份，第二天上午10点，数据库被误删除了，此时有什么好的恢复方法吗？

首先想到的就是利用前天晚上的全库备份去还原被删除的数据库，对于0点到当前时间的数据时没有备份的，此时就需要Binlog日志中截取了，但是截取Binlog就非常棘手了，不管是基于时间的标识位号截取还是基于GTID号截取，终点都特别好找，但是起点非常的难找，我们做不到哪一天才是0点备份之后第一个产生的数据。最终恢复好的数据可能有缺失的现象。

基于这种场景，其实我们也有解决的办法，例如在备份之前刷新一个Binlog，每天备份的时候都会产生一个新的Binlog，相对来说就能解决问题了。

mysqldump也为我们提供了解决办法，那么就是在备份的时候，在SQL文件里追加上备份之前最后一条数据时在哪一个Binlog日志中以及该数据对应的事件标识位号。当我们需要截取备份时间到当前时间的Binlog时，直接在备份好的SQL文件里找到这个标识位，作为截取Binlog的开始标识位即可。

下面我们就一起来看一看解决上述问题的核心参数：

–master-data核心参数参数

--master-data=2：该参数会以注释的行驶，保存备份开始时间点的Binlog状态信息，记录备份开始时间点的数据位于哪一个Binlog日志中，以及备份开始时间点的数据对应的Position标识位号，该参数一共有3个值：

• --master-data=0：默认值
• --master-data=1：以change master to的命令形式写入到备份文件中，可以在主从复制时使用。
• --master-data=2：以注释的行驶记录，备份时间使用的Binlog日志名以及Position号都会记录下来。

--master-data参数的作用：记录备份时的Binlog状态，并且会自动锁表，但是当存储引擎是InnoDB时，配置--single-transaction参数也可以减少锁表的时间。

在备份时指定--master-data参数观察效果

[root@mysql ~]# mysqldump -uroot -p123456 -B db_1 --master-data=2 > /data/backup/db_1.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=364;

当备份时指定了--master-data参数后，会在备份文件的顶部增加上当时Binlog日志的状态信息。

–single-transaction核心参数

--single-transaction：开启快照备份的功能，当使用此参数时，会开启一个快照备份的功能，此时要备份的表就不会产生锁表的现象，在备份之前，针对要备份的表打一个快照，备份快照中的数据，相当于热备份。

--single-transaction可以配合--master-data参数一起使用。

--master-data默认情况下会对所有表进行温备份，也就是会对所有的表都设置一个表锁，当配合--single-transaction这个参数一起使用时，如果存储引擎是InnoDB，则不会再对表设置表锁，而是对InnoDB的表进行快照备份，对非InnoDB引擎的表设置一个表锁。

6.mysqldump备份数据库时的一些其他参数

下面的这些参数在备份时可加可不加。

-F：在备份数据库时，刷新Binlog日志，产生新的Binlog日志。这个参数有个很恶心的地方，就在于当前MySQL实例中有多少个数据库，就会刷新多个Binlog，并不是按照我们想的那样，只产生一个Binlog，日积月累我们看的的Binlog日志将会很多很多，一般不用。

--set-gtid-purged：该参数是关于备份时事务的GTID号的，如果该参数值为OFF，那么备份数据时，就不会记录数据所对应事务的GTID号，将来还原数据时会产生新的GTID号，如果该参数值为ON则会保留数据所对应事务的GTID号，默认值为AUTO，自动判断。

如果是日常备份，可以指定--set-gtid-purged=OFF，不记录数据原来的GTID号，当在构建MySQL主从复制环境时要将该值设置成``–set-gtid-purged=ON或者–set-gtid-purged=AUTO`，确保数据的一致性。

如下图所示，左侧是--set-gtid-purged=OFF，备份文件中就没有GTID号相关的内容了，右侧是--set-gtid-purged=ON，会记录数据原来的GTID号。

--max-allowed-packet：指mysql服务器端和客户端在一次传送数据包的过程当中最大允许的数据包大小，默认值是4M，如果我们的备份的数据量很大，超过了每次传输数据包的最大大小，那么就会报错，可以通过该参数在备份时指定允许传输的最大数据包大小。