mysqldump 逻辑备份工具

原文链接：https://www.cpweb.top/1018

一、备份的基本概念

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

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

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

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

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

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

 

二、命令介绍

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

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

  示例：基本备份

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

# -B  单库或多库备份
[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

 

三、重要参数

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、特殊对象备份参数

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

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

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

 

4、–max-allowed-packet

  即发送到服务器或从服务器接收的最大数据包长度。如果接收到数据包长度大于了数据库定义的值，备份时就会报错，我们可以在备份时指定它的大小（以字节为单位）。
  此参数最大限制为1GB，这个值必须是1024的倍数，如果不是1024倍数的值，mysgl就会自动四舍五入为最接近的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;    // 退出数据库重连查看即可

 

四、完整备份命令

  以下就是我们使用mysqldump命令备份时需要执行的完整命令，上述参数强烈建议都加上。

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

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

 

五、实例演练

1、场景

  每天晚上23：00进行数据库的全备和binlog备份。每天中午12:00，进行binlog备份。
  故障：周二上午10点数据库损坏了，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;

 
参考文章：https://www.jianshu.com/p/6fbdcb7695cb