一、备份的基本概念

在学习 mysqldump 命令之前，我们有必要了解下数据库备份的基本知识。
总体来说，从数据备份模式的角度来说，可分为物理备份和逻辑备份：

• 逻辑备份：基于文件级的备份，一般是指使用软件技术从数据库中导出数据并写入到一个输出文件中。
• 物理备份：基于数据块级别的备份，一般是指直接复制包含数据的文件夹和文件。

数据备份的类型分为：冷备份、温备份、热备份：

• 冷备份：也称为离线备份，是指在关闭数据库情况下（或者说阻止一切对数据库数据操作行为的情况）进行的数据库完整备份。
• 温备份：不停止对数据库的访问，也不阻止对数据库数据的读取，但是阻止数据修改的情况下进行的备份。
• 热备份：不停止对数据库的访问，也不阻止对数据库数据的读取和写入的情况下进行的备份。

数据备份的方式，可分为全量备份、增量备份、差异备份：

全量备份：对全部数据都进行备份。比较浪费磁盘空间备份时间长，但是恢复数据速度方便快捷。
增量备份：备份上次备份之后发生变化的数据备份。相对全备，节省了磁盘空间且备份数据快，但是恢复数据速度慢。即如果恢复数据，那就得先恢复最近一次的全备，然后按顺序一个一个的去恢复增备，或者将数据按顺序合并成完整数据再恢复。
差异备份：在上次全量备份到进行差异备份的这段时间内，对那些发生变化的数据的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。差异备份在避免了另外两种备份策略缺陷的同时，又具备了它们各自的优点。首先，它具有了增量备份需要时间短、节省磁盘空间的优势；其次，它又具有了全量备份恢复恢复数据速度方便快捷的特点。

二、命令介绍

mysqldump 命令是 mysql 自带且最常用的逻辑备份工具，它的备份是从数据库中将SQL语句导出写入到一个文本文件中。
用法：

    mysqldump [OPTIONS] database [tables]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]

示例：

# -A（--all-databases），全库备份 
[root@db01 ~]# mysqldump -uroot -p -A > /backup/full.sql

# -B（--databases），单库或多库备份
[root@db01 ~]# mysqldump -uroot -p -B test1 > /backup/db1.sql
[root@db01 ~]# mysqldump -uroot -p -B test1 test2 > /backup/db1.sql

# 单表或多表备份 
[root@db01 ~]# mysqldump -uroot -p test1 stu1 > /backup/tab1.sql
[root@db01 ~]# mysqldump -uroot -p test1 stu1 stu2 > /backup/tab2.sql

常用选项说明：

选项	描述
-h	主机
-P	端口
-u	用户
-p	密码
-A, -‌-all-databases	转储所有数据库中的所有表
-B, -‌-databases	转储几个数据库时，通常 mysqldump 将命令行上的第一个名称参数视为数据库名称，将后面的名称视为表名称。使用此选项，它将所有名称参数视为数据库名称。 CREATE DATABASE 和 USE 语句包含在每个新数据库之前的输出中。

三、重要参数

1、-‌-master-data

当我们要进行增量备份时，需要从 binlog 日志文件中截取上次备份到现在的日志信息，那么如何去确定开始的 position 号呢？这就要用到 mysqldump 命令的 --master-data[=#] 参数。
在备份时，加上它可以将我们的 binlog 日志文件的文件名和 position 号附加到输出文件中。如果等于1，对附加信息不加注释；等于2，则会加上注释，同时加上它，还会自动全局锁表。

[root@db01 ~]# mysqldump -uroot -p -A --master-data=1 > /backup/1.sql
[root@db01 ~]# vim /backup/1.sql
--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1171;

[root@db01 ~]# mysqldump -uroot -p -A --master-data=2 > /backup/2.sql
[root@db01 ~]# vim /backup/2.sql 
--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1171;

通过上例我们可以清楚看到等于1和等于2的区别，一般来说实际上等于1还是很少用，一般都是用等于2。后面的 "MASTER_LOG_POS=1171" 即上次备份最后一个事件结束的 position号，此时我们就可以拿到这个编号去做增量备份了。

2、-‌-single-transaction

通过上述对 --master-data 参数的介绍，我想大家应该都大致明白了它的作用，不知道大家有没有注意到前面提到的加上它在备份时会全局锁表，在生产环境中肯定是锁表的时间越少越好，那么是否有什么参数可以减少锁表的时间呢？
这时候我们就要用到了 --single-transaction 参数，即快照备份（只针对 InnoDB 表），它可以帮助我们在备份时减少锁表的时间。我们来看看它是如何去实现减少锁表的时间的：

mysqldump -uroot -p -A --master-data=2 --single-transaction > /backup/full.sql

以上条命令为例，以下是它备份的大致流程：
 1、备份元数据，像建表建库这些语句等，此时会加上 FTWRL（全局锁），即mysqldump命令帮助手册提到"global read lock"。
 2、对于备份MyISAM表数据，是锁表备份。
 3、对于备份InnoDB表数据，先调整隔离级别，解锁表，开启独立事务，生成快照, 通过快照去备份。

注：
 FTWRL（全局锁），即"Flush tables with read lock"，让整个库处于只读状态，所有更新操作都会被阻塞。
 它的典型使用场景是，做全库逻辑备份时。如果在做数据的时候不加锁，就可能出现备份的数据和原数据不一致的情况。

3、-‌-max-allowed-packet

客户端和服务器通信缓冲区的最大大小，即发送到服务器或从服务器接收的最大数据包长度。如果接收到数据包长度大于了数据库定义的值，备份时就会报错，我们可以在备份时指定它的大小（以字节为单位）。
默认为 24 MB，最大为 1 GB，这个值必须是 1024 的倍数，如果不是 1024 倍数的值，mysql 就会自动四舍五入为最接近的 1024 的倍数。有时候大的 insert 或者 update 会受到此参数的限制，导致失败。

mysqldump -uroot -p -A -R -E --master-data=2 --single-transaction --triggers --max_allowed_packet=64M > /backup/full.sql

# 查看默认值： 
mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
|              4194304 |
+----------------------+

# 设置默认值：
mysql> set global max_allowed_packet=64*1024*1024;    # 退出数据库重连查看即可

4、特殊对象备份参数

mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers > /backup/full.sql

选项说明：
-R          备份存储过程和函数。
-E          备份事件，此处事件可以理解mysql中计划任务。
--triggers  备份触发器。

注：如果备份时不加上述参数，那么它们代表的东西的不会备份下来。

四、完整备份命令

以下就是我们使用mysqldump命令备份时需要执行的完整命令：

mysqldump -uroot -p -A -RE --triggers --master-data=2 --single-transaction > /backup/full.sql

# 也可以压缩备份
mysqldump -uroot -p -A -RE --triggers --master-data=2 --single-transaction | gzip > /backup/full_$(date +%F).sql.gz

五、实例演练

1、场景

每天晚上23：00进行数据库的全备和 binlog 备份。每天中午12:00，进行 binlog 备份。
故障：周二上午10点数据库损坏了，binlog 和全备都是好的。

恢复思路：
 • 首先拿全备恢复到上一天晚上23：00状态。
 • 再从全备和binlog日志中找出开始和结束position号。
 • 用拿到的position号从binlog日志中去截取从上次全备到数据库数据损坏之前的数据，最后将数据拿到数据库中恢复。

2、模拟环境

（1）模拟原始数据
mysql> create database educ;
mysql> use educ;
mysql> create table stu(id int);
mysql> insert into stu values(1),(2),(3);

（2）模拟周一晚上23:00全备
[root@db01 backup]# mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --max_allowed_packet=64M | gzip > /backup/full_$(date +%F).sql.gz

（3）模拟周二白天数据变化
mysql> create table stu2(id int);
mysql> insert into stu2 values(1),(2),(3);

（4）模拟周二10点的数据故障
mysql> drop database educ;

3、恢复数据

（1）检查备份和日志，获取position号
[root@db01 backup]# gunzip full_2020-11-05.sql.gz 
[root@db01 backup]# vim full_2020-11-05.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1757;

（2）截取binlog
mysql> show binlog events in 'mysql-bin.000002';
+------------------+------+----------------+-----------+-------------+--------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                 |
+------------------+------+----------------+-----------+-------------+--------------------------------------+
......省略
| mysql-bin.000002 | 2248 | Query          |         1 |        2340 | drop database educ                   |
+------------------+------+----------------+-----------+-------------+--------------------------------------+

[root@db01 binlog]# mysqlbinlog --start-position=1757 --stop-position=2248 mysql-bin.000002 > /tmp/binlog.sql

注：开启了GTID，需要添加"--skip-gtids"，同时也可以使用GTID方式截取。

（3）恢复数据 
mysql> set sql_log_bin=0;
mysql> source /data/backup/mdp/full_2020-07-10.sql
mysql> use educ;
mysql> show tables;
+----------------+
| Tables_in_educ |
+----------------+
| stu            |
+----------------+
mysql> source /tmp/bin.sql
mysql> show tables;
+----------------+
| Tables_in_educ |
+----------------+
| stu            |
| stu2           |
+----------------+
mysql> select * from stu2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
mysql> set sql_log_bin=1;

六、其它参数

-‌-add-drop-table：在每个 CREATE TABLE 语句之前写一个 DROP TABLE 语句。要禁止请使用 --skip-add-drop-table。
-‌-add-locks：用 LOCK TABLES 和 UNLOCK TABLES 语句包围每个表转储。这会在重新加载转储文件时加快插入速度。要禁止请使用 --skip-add-locks。
-‌-create-options：在 CREATE TABLE 语句中包含所有 MySQL 特定的表选项。
-‌-disable-keys（-K）：对于每个表，用 " /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; " 和 " /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; " 包围 INSERT 语句。这使得加载转储文件更快，因为索引是在插入所有行之后创建的。此选项仅对 MyISAM 表的非唯一索引有效。要禁止请使用 --skip-disable-keys。
-‌-extended-insert（-e）：使用包含多个 VALUES 列表的多行语法编写 INSERT 语句。这会导致转储文件更小，并在重新加载文件时加快插入速度。使用 --skip-extended-insert 则相反，每条数据对应一个 INSERT 语句。
-‌-lock-tables（-l）：对于每个转储的数据库，在转储之前锁定所有要转储的表。在 MyISAM 表的情况下，表被 READ LOCAL 锁定以允许并发插入。对于诸如 InnoDB 之类的事务表，--single-transaction 是比 --lock-tables 更好的选择，因为它根本不需要锁定表。
-‌-quick（-q）：此选项对于转储大型表很有用。它强制 mysqldump 一次从服务器检索表的行，而不是检索整个行集并在写出之前将其缓冲在内存中。
-‌-set-charset：将 SET NAMES default_character_set 写入输出。默认情况下启用此选项。要禁止 SET NAMES 语句，请使用 --skip-set-charset。
-‌-comments（-i）：在转储文件中写入附加信息，例如程序版本、服务器版本和主机。默认情况下启用此选项。要禁止显示此附加信息，请使用 --skip-comments。
-‌-opt：此选项默认启用，是 --add-drop-table、--add-locks、--create-options、--disable-keys、--extended-insert、--lock-tables、--quick、--set-charset 组合的简写。它提供了快速转储操作并生成可以快速重新加载到 MySQL 服务器的转储文件。因为该选项默认是启用的，所以你只需要指定它的反面， --skip-opt 来关闭几个默认设置。
-‌-compact：产生更紧凑的输出。这个选项相当于激活了 --skip-add-drop-table、--skip-add-locks、--skip-comments、--skip-disable-keys、--skip-set-charset。

参考文章：

https://www.jianshu.com/p/6fbdcb7695cb

一、备份的基本概念

二、命令介绍

三、重要参数

1、-‌-master-data

2、-‌-single-transaction

3、-‌-max-allowed-packet

4、特殊对象备份参数

四、完整备份命令

五、实例演练

1、场景

2、模拟环境

3、恢复数据

六、其它参数

你可能也喜欢

mysql多实例配置

Atlas 实现读写分离

mysql用户和权限管理

发表评论 取消回复

发表评论取消回复