mysql

转载自：mysql/mariadb知识点总结（28）：mysql备份工具之mysqldump | 朱双印博客

如果你只是想找一些mysqldump语句进行参考，那么，直接查看本文章下方的"mysqldump备份操作小结"部分即可。

如果你想学习怎样使用mysqldump，以及一些常用选项的含义，请按部就班的阅读这篇文章。

mysqldump简介

原理：

mysqldump是mysql自带的逻辑备份工具。

它的备份原理是，通过协议连接到mysql数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert语句，当我们需要还原这些数据时，只要执行这些insert语句，即可将对应的数据还原。

优点：

1. 文本格式：可以直接使用文本处理工具(eg:sed)处理对应的备份数据，因为备份数据已经被mysqldump转换为了对应的insert语句，所以，我们可以借助文件系统中的文本处理工具对备份数据进行直接处理；
2. 压缩比高（约：10:1）：针对数据量超大（PB级别的数据），使用mysqldump逻辑备份，可以利用文本的高压缩比，节省物理存储成本；
3. 不需下载其他软件 

缺点：

当数据为浮点类型时，会出现精度丢失

mysqldump的备份过程属于逻辑备份，备份速度、恢复速度与物理备份工具相比较慢，而且mysqldump备份的过程是串行化的，不会并行的进行备份，如果想要并行备份，可以使用mydumper，但是此处我们不考虑这些，只考虑mysqldump，当数据量较大时，一般不会使用mysqldump进行备份，因为效率较低。

注意：

mysqldump对innodb存储引擎支持热备，innodb支持事务，我们可以基于事务通过mysqldump对数据库进行热备。

mysqldump对myisam存储引擎只支持温备，通过mysqldump对使用myisam存储引擎的表进行备份时，最多只能实现温备，因为在备份时会对备份的表请求锁，当备份完成后，锁会被释放。

mysqldump示例：

示例过程：

mysqldump是一个客户端工具，所以当mysqldump连接到数据库时，也会读取mysql数据库的配置文件，加载跟客户端相关的配置。

我们先通过一些最简单的实验，来了解一下mysqldump，但是这些操作过于简陋，不足以满足我们的备份需求，备份的数据也有可能出现问题，所以不要使用简单示例中的语句进行备份，我们只是通过它们去了解mysqldump命令罢了，等我们掌握了它，再总结一些实用的备份命令，先来做个小示例。

假设，我们现在想要通过mysqldump备份zsythink数据库，那么，我们可以通过如下mysqldump命令

（注意，如下命令只会显示mysqldump的备份过程，并没有将数据备份，我们慢慢聊）

我们说过mysqldump是一个客户端命令，所以，就像使用mysql命令连接数据库一样，我们需要指定连接的用户名，需要连接的数据库服务IP，以及使用-p选项提示我们输入密码，这些用法都与我们的mysql命令一致，上图中，我们指定要备份zsythink数据库，此时，提示我们输入root用户的密码。

输入正确的密码以后，会看到类似如下模样的输出

首先，输出的信息中包含一些注释，这些注释信息中包括mysqldump的版本，mysql的版本，以及主机IP，数据库信息等信息。

除了这些信息，还会输出一些看似注释的可执行意见，比如如下信息。

/*!40101 SET NAMES utf8 */;

那么上述信息有什么用呢？我们来了解一下，因为，mysqldump备份出的信息为可执行的sql，所以，当我们使用这些sql进行数据还原的时候，则必须执行它们，而上述信息表示当mysql版本大于等于4.01.01的时候，才会执行 SET NAMES utf8这条语句，否则这条语句则不会被执行，如果使用mysqldump备份出的sql语句在其他关系型数据库上执行，那么这些信息将被当做注释处理，这是mysql为了使这些sql语句的兼容性更强而使用的一种手段，我们不用过分在意他们。

我们要关注的是上图中红色标注的信息。

首先，mysqldump将表a的表结构转换成了对应的sql语句，为了确保还原表a时能够正常的执行创建表的语句，所以，mysqldump自动生成了DROP表a的语句，然后又生成了CREATE表a的语句。完成上述步骤后，mysql开始将表a中的数据转换成对应的sql语句，那么我们看看表a中都有什么数据

表a中只有2条数据，而mysqldump就是将a表中的所有数据转换成了一条insert语句，当我们执行这条insert语句时，就相当于还原了a表数据，当然，上述示例中的insert语句只所以非常少，非常短，是因为我们的示例数据太少了，如果是真正的正在使用的数据库，那么这条insert语句将会非常长，而且，数据库中不只有一张表，因为这是示例，所以我们只截取了一部分进行演示。

你一定发现了，当我们执行刚才的mysqldump语句时，所有信息都输出到了屏幕上，并没有保存到任何文件中，所以我们无法通过这些信息对数据进行还原，聪明如你一定想到了，我们只要将这些信息重定向到指定的文件即可，比如，使用用下语句。

我们将备份信息重定向到了zsythinkdb.sql文件中，下次执行这个sql脚本，即可还原zsythink数据库中的所有的表。

但是，细心如你一定发现了，此sql脚本中并不包含任何创建数据库的语句，只有创建表的语句，也就是说，在还原时，必须先确保对应的数据库已经存在，那么，我们能不能再备份时就生成创建库的语句呢？必须的额，使用--databases选项指定数据库，即可在备份时生成创建数据库的语句，示例如下。

那么，我们能不能只备份数据库中的某张表呢？能不能同时备份指定的多张表呢？能不能一次备份多个数据库呢？必须能啊，只要我们灵活的运用一些选项即可，总结如下。

选项小结：

单库所有表（含数据）：

使用如下语句备份zsythink数据库中的所有表，但是不会生成创建zsythink数据库的语句，只是备份其中的表（包括创建表的语句和数据）。

mysqldump -uroot -h192.168.1.146 zsythink -p

单库指定表（含数据）：

使用如下语句备份zsythink数据库中指定的表，下例中只会备份zsythink数据库中的t1、t2、t3表，其他表不会备份，也不会生成创建zsythink数据库的语句（但是对应的表的创建语句会生成，表数据会备份）。

mysqldump -uroot -h192.168.1.146 zsythink t1 t2 t3 -p

单库所有表(含建库语句&含数据) -databases：

使用如下语句备份zsythink整个数据库，包括其中的所有表，并且会生成创建zsythink数据库的语句。

mysqldump -uroot -h192.168.1.146 --databases zsythink -p

多库全表(含建库语句&含数据) -databases:

使用如下语句备份指定的多个数据库，所有被指定的数据库中的表都会被备份，对应的创建库的语句也会生成，下例表示同时备份zsythink库与test库。

mysqldump -uroot -h192.168.1.146 --databases zsythink test -p

全库（含建库语句&含数据）--all-databases：

使用如下语句备份当前数据库服务中的所有库。

mysqldump -uroot -h192.168.1.146 --all-databases -p

只要表结构（不含建库语句&数据）-d：

其实，在某些时候，我们只是想要将数据库的表结构备份出来，不想备份数据，以便创建一个完全相同的干净数据库，那么我们可以使用如下语句备份数据库的所有表结构，如下命令表示备份zsythink数据库中的所有表的表结构，不包含表数据，不包含创建库的语句，只有创建表的语句，如下命令的"-d"选项可使用"--no-data"代替，他们效果完全相同。

 

mysqldump -uroot -hlocalhost -d zsythink -p

 

如下命令表示备份zsythink数据库中test表的表结构，不包含表数据，不包含创建库的语句，只有创建test表的语句。

mysqldump -uroot -hlocalhost -d zsythink test -p

其他常用选项：

选项 --master-data：

应用场景：

之前说过，在实际进行数据恢复时，往往需要进行时间点还原，通常的做法是先通过最近一次的备份，将数据恢复到备份时的样子，然后再通过二进制日志，将备份之后的更改操作重放一遍，这样就将数据恢复成了最近的模样，比如，备份开始时，二进制日志对应的position为123，那么我们在进行时间点还原时，则需要先通过"备份文件"将数据恢复为备份时的样子，然后再通过二进制日志文件，将position 123之后的所有操作重放一遍。但是，在还原时，我们怎么知道备份时position的值呢？没错，我们必须在备份时就做好标记，在恢复时才知道应该从哪里重放。否则在恢复时，我们则无法获取到重放二进制日志的起始点位置。

 

其实，备份时二进制日志文件的position非常重要，对于主从复制结构来说，我们同样需要用到它，如果，你的数据库已经运行了一段时间，里面已经存在了一些数据，而此时，你想要将数据库架构从单机架构改为主从架构，你想把当前的主机作为主服务器，然后再新加入一台服务器作为从服务器，那么，一般的做法是将主库中的数据先备份一份出来，然后导入到从库中，但是，当你从主库中备份数据时，往往是热备的，也就是说，备份完成后，有一些操作只在主库中完成了，而备份中却不存在这些数据，所以，我们使用备份在从库中将数据还原以后，还要告诉"从库"，将备份之后的操作从"主库"那边同步过来，但是，我们怎么知道从哪里开始同步呢？没错，我们必须在备份的时候就记住它的position，以便告诉从库，从哪里开始同步。其实，这与我们手动重放二进制日志时的场景并没有什么不同，它们的最终目的都是要确定通过备份还原数据以后，要从哪个位置开始执行之后的"重放"或"同步"操作。

 

那么说了这么多，我们怎样才能在备份时，记住binlog对应的position呢？如果你的数据库开启了二进制日志，那么，在使用mysqldump进行备份时，需要使用--master-data选项，标记备份开始时binlog所对应的position（位置），如果你对上述描述不是特别理解，请参考如下连接。

mysql/mariadb知识点总结（25）：二进制日志

参数值的含义：

1. 此值为0：表示在使用mysqldump进行备份时，不记录对应二进制日志文件位置，将此值显式的设置为0与不使用此选项的效果相同。
2. 此值为1：表示在使用mysqldump进行备份时，记录对应二进制日志文件位置，此值为默认值，也就是说，使用--master-data与使用--master-data=1的效果相同，如果将此选项的值设置为1，则会在备份文件中生成对应的"CHANGE MASTER TO"语句，此语句中标明了备份开始时二进制日志的前缀名以及其所处的position(位置)，生成此语句的目的是，在主从复制结构中的"从服务器"中通过备份sql还原数据以后，告诉"从库"，从"主库"的二进制日志文件中的哪个位置开始"同步"。如果我们没有使用主从复制结构，同时又想要在备份时记录二进制日志文件的position，则可以将此选项的值设置为2。
3. 此值为2：表示在使用mysqldump进行备份时，记录对应二进制日值文件的位置，如果将此选项的值设置为2，则会在备份文件中生成对应的"CHANGE MASTER TO"语句，此语句中标明了备份开始时二进制日志的前缀名以及其所处的position(位置)，但是"CHANGE MASTER TO"语句将会被注释，与此值为1时不同，此选项值为1时，"CHANGE MASTER TO"语句不会被注释，此选项值为2时，"CHANGE MASTER TO"语句会被注释，所以，如果只是单纯的为了记录备份时的二进制日志文件位置，那么将此选项值设置为2即可。

不同参数值的演示：

那么，我们来动手做个实验，看看加上--master-data选项后，备份文件中到底是什么样子的。

从上图可以看出，如果将--master-data的值指定为1，则会在备份中生成CHANGE MASTER TO语句，而此语句中包含了备份开始时，binlog对应的文件以及position。

但是此语句并未被注释，所以，如果只是单纯的想要获取对应二进制日志文件的位置，则可以将--master-data的值设置为2，如下图所示。

选项：--flush-logs

我们再来想另外一个问题，如果，我们将二进制日志的大小设置为600兆，那么，每当二进制日志的大小满600兆，对应的二进制日志文件就会发生滚动，生成一个新的二进制文件，并将原来的600兆保存，假设，我们使用mysqldump对数据库进行备份的那一刻，对应binlog的大小为300兆，也就是说，备份操作开始时，二进制日志文件的position的位置则会处于文件居中的位置，那么，当我们想要找到对应position进行重放时，此位置之前的操作记录对于我们来说都是"无用"的，可是比较尴尬的是，我们还必须找到此位置，这样就会产生一些"多余的工作量"，那么我们能不能直接避免这种情况的发生呢？必须的，劳动人民的智慧是无穷的，聪明如你一定想到了，使用一个选项就能搞定，没错，就是一个选项，它就是--flush-logs选项，当我们使用mysqldump进行备份时，如果使用了此选项，备份开始时就会滚动一次二进制日志，无论二进制日志对应的文件大小是否达到600兆，都会滚动，这样，我们就能在对应的二进制日志文件的开头部分开始重放日志了，是不是很高效？有没有很方便？

选线：--max_allowed_packet：

MySQL根据配置文件，会限制server端或client端接受的数据包的大小。

1. server端：如果写入大数据时，因为默认的配置太小，插入和更新操作会因为 max_allowed_packet 参数限制，而导致失败；
2. client端 ：mysqldump 时，传输的数据包大于 max_allowed_packet 参数的限制时，备份操作就会失败。

所以，遇到报错信息  1153 – Got a packet bigger than ‘max_allowed_packet’ bytes 时，可以：

mysqldump -uroot -h192.168.1.146 --flush-logs --master-data=2 --single-transaction --routines --triggers --events --max_allowed_packet=64M --all-databases -p > dbbackup.sql

注意：mysql客户端 max_allowed_packet一般128M都能满足，该参数最大值1G。

其他常用选项

我们说过，备份的最终目的是"还原"，而只备份表中的数据，往往是不够的，因为在数据库中，可能还存在一些存储过程和存储函数、存在一些触发器、事件表，这些"东西"也需要备份，以免最终的备份"不全"，在恢复的时候就"懵逼"了，那么，我们来看一些其他的常用的选项，总结如下：

--routines选项：表示备份时，存储过程和存储函数也会被备份。

--triggers选项：表示备份时，触发器会被备份。

--events选项：表示备份时，事件表会被备份。

Mysqldump在不同存储引擎下的使用：

我们还需要考虑一个问题，如果我们要备份数据，我们一定要保证备份出的数据的可用性，如果备份后的数据在恢复以后，无法正常使用，那么备份出的数据也就失去了备份的意义，数据可用的前提就是数据的正确性、完整性、一致性。

而不同的存储引擎，满足一致性时所使用的方法不同，如果你还不明白数据一致性的相关概念，请参考如下文章

mysql/mariadb知识点总结（27）：一致性读，快照读

 

那么，我们就来看看使用mysqldump怎样对不同类型的存储引擎进行备份，以满足备份数据的一致性。

表类型为innodb

我们说过，mysqldump支持对innodb存储引擎进行热备，热备的概念我们已经提到过，所谓热备，就是在备份的时候能够对数据库进行读写，但是，如果在备份时对数据库进行读写，怎样保证数据的一致性呢？没错，之前的文章已经详细解释过，如果表类型为innodb，基于"可重读"事务，即可保证热备出的数据的一致性。

所谓"基于事务"完成备份，就是说所有备份的操作都是在一个"独立的事务"中完成的，而且这个事务的隔离级别为"可重读"，其他读写操作是在这个备份事务之外进行的，所以，利用"可重读"事务的"隔离性"，即可保证读写操作并不会对备份操作造成影响。

选项 --single-transaction 登场：

我们先看一下mysql中当前默认的隔离级别

可以看到，当前默认的隔离级别为"读提交"，并不是"可重读"，那我们需要手动的将当前默认的隔离级别设置为"可重读"吗？不需要，因为mysqldump会自动将备份操作放入一个"可重读"的事务中，即使默认隔离级别不是"可重读"。

为了方便理解备份过程，我们将查询日志开启，并且将查询日志存放在general_log表中(如果你不理解下图中变量的含义，请参考http://www.zsythink.net/archives/1246)。

好了，万事俱备只欠东风，我们现在就开始备份。

在备份innodb存储引擎的表时，如果想让备份操作基于"独立的事务"进行，则需要使用 --single-transaction选项，示例如下。

使用--single-stransaction选项备份后，查看general_log日志，使用如下语句查看general_log;

mysql> select  event_time,thread_id,command_type,argument from mysql.general_log;

从日志中可以看到如下信息，mysqldump自动将备份会话中的事务隔离级别设置为了"可重读"，并且开启了一个事务，而且，开始事务时，使用了"WITH CONSISTENT SNAPSHOT"，表示事务开始的那一刻，同时创建了快照，以保证备份事务中的"一致性读"。

从上图可以看出，在备份innodb存储引擎的表时，如果使用了--single-transaction选项，mysqldump会自动开启一个"可重读"的事务，基于这个独立的"事务"，备份出一个满足一致性的数据备份。

 小结：

所以，如果你的数据库中的所有的表都是使用innodb存储引擎，而且你要对数据库进行热备，那么，则一定要添加上--single-transaction选项，如果开启了二进制日志，也不要忘了设置--master-data选项。

表类型为myisam：

如果你数据库中的表使用了myisam存储引擎，那么，在备份时最多只能达到温备的程度，因为myisam存储引擎不支持事务，即使我们使用--single-transaction选项对myisam表进行备份，也不会有任何作用，我们只能通过锁表的方式进行，即在备份开始到备份结束期间，备份的表会被加上读锁，以保证数据的一致性，但是如果你的mysql环境是主从环境，则可以在从服务器上进行备份操作，此处不考虑那么多，我们只要记住，对myisam表进行备份时，需要加锁以保证数据一致性即可。

选项 --lock-all-tables ：

对所有数据备份时，可以使用--lock-all-tables选项，对所有库的所有表加读锁，--lock-all-tables对应的短选项为-x，而且，此选项与--single-transaction选项不能同时存在，示例如下：

mysqldump -uroot -h192.168.1.146 --lock-all-tables --all-databases -p > dbbackup.sql

对指定的数据库进行备份时，可以使用--lock-tables选项，对指定库的所有表加锁，--lock-tables对应的短选项为-l，注意，此选项与--single-transaction选项同时存在时，此选项将失效，示例如下

 mysqldump -uroot -h192.168.1.146 --lock-tables --databases zsythink -p > zsythink.sql 

 

当然，我们也可以使用上述语句对innodb存储引擎的表进行备份，不过这样就不是热备了，而是温备。

mysqldump备份操作总结：

常用备份语句：

存储引擎为innodb的数据表：

1. 如果未开启二进制日志，备份指定的zsythink数据库，可以使用如下语句：

   mysqldump -uroot -h192.168.1.146 --single-transaction --routines --triggers --events --max_allowed_packet=64M --databases zsythink -p > zsythink.sql

2. 在开启二进制日志的情况下，备份指定的zsythink数据库，通常使用如下语句：

   mysqldump -uroot -h192.168.1.146 --flush-logs --master-data=2 --single-transaction --routines --triggers --events --max_allowed_packet=64M --databases zsythink -p > zsythink.sql

3. 在开启二进制日志的情况下，备份所有数据库，通常使用如下语句

   mysqldump -uroot -h192.168.1.146 --flush-logs --master-data=2 --single-transaction --routines --triggers --events --max_allowed_packet=64M --all-databases -p > dbbackup.sql

存储引擎为myisam的数据表：

1. 如果未开启二进制日志，备份指定的zsythink数据库，可以使用如下语句：

   mysqldump -uroot -h192.168.1.146 --routines --triggers --events --max_allowed_packet=64M --lock-tables --databases zsythink -p > zsythink.sql

2. 在开启二进制日志的情况下，备份指定的zsythink数据库，通常使用如下语句：

   mysqldump -uroot -h192.168.1.146 --flush-logs --master-data=2 --routines --triggers --events --max_allowed_packet=64M --lock-tables --databases zsythink -p > zsythink.sql

3. 在开启二进制日志的情况下，备份所有数据库，通常使用如下语句：

   mysqldump -uroot -h192.168.1.146 --flush-logs --master-data=2 --routines --triggers --events --max_allowed_packet=64M --lock-all-tables --all-databases -p > dbbackup.sql

 使用场景：

1. 数据量较小时（eg：百G级别时），可以考虑使用 mysqldump 逻辑备份：
   1. 备份100G数据，大约耗时 0.5~1H
   2. 恢复100G数据，大约耗时1~2H

扩展：

从全备文件 截取单库单表数据：

1、获得表结构
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full.sql>createtable.sql

2、获得INSERT INTO 语句，用于数据的恢复

# grep -i 'INSERT INTO `city`'  full.sqll >data.sql &

3.获取单库的备份

# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql