mysql备份工具位置_mysql备份工具之mysqldump详解

分手后的思念是犯贱 2023-01-12 02:00 191阅读 0赞

mysqldump简介

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

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

mysqldump的优点：

可以直接使用文本处理工具处理对应的备份数据，因为备份数据已经被mysqldump转换为了对应的insert语句，所以，我们可以借助文件系统中的文本处理工具对备份数据进行直接处理。

mysqldump的缺点：

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

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

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

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

初识mysqldump

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

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

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

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

mysqldump -uroot -h192.168.1.180 zsythink -p

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

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

![4aadcd346926eb796c65b63532cfcd92.png][]

首先，输出的信息中包含一些注释，这些注释信息中包括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中都有什么数据

mysql> select \* from a;

\+------+

| id |

\+------+

| 1 |

| 2 |

\+------+

2 rows in set (0.00 sec)

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

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

mysqldump -uroot -h192.168.1.180 zsythink -p>zsythinkdb.sql

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

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

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

![959a1f71e00990e6d2ff9a2e03208bc8.png][]

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

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

mysqldump -uroot -h192.168.1.180 zsythink -p

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

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

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

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

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

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

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

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

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

mysqldump -uroot -h192.168.1.180 -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(位置)，如果你对上述描述不是特别理解，请参考如下连接。

\--master-data选项有3个可用值，0、 1 、2，这三个值分别表示不同的含义

此值为0：表示在使用mysqldump进行备份时，不记录对应二进制日志文件位置，将此值显式的设置为0与不使用此选项的效果相同。

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

此值为2：表示在使用mysqldump进行备份时，记录对应二进制日值文件的位置，如果将此选项的值设置为2，则会在备份文件中生成对应的"CHANGE MASTER TO"语句，此语句中标明了备份开始时二进制日志的前缀名以及其所处的position(位置)，但是"CHANGE MASTER TO"语句将会被注释，与此值为1时不同，此选项值为1时，"CHANGE MASTER TO"语句不会被注释，此选项值为2时，"CHANGE MASTER TO"语句会被注释，所以，如果只是单纯的为了记录备份时的二进制日志文件位置，那么将此选项值设置为2即可。

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

mysqldump -uroot -h192.168.1.180 --master-data=1 --databases zsythink -p

![85e7e385fca8b624e276072cc8a78a09.png][]

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

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

![b9298839d6c0858a7bd5040f0d6d3be1.png][]

选项：--flush-logs

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

mysqldump -uroot -h192.168.1.180 --master-data=2 --databases zsythink --flush-logs -p

![79011abf4abbfb8694a7fb86d9da26ea.png][]

其他常用选项

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

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

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

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

[4aadcd346926eb796c65b63532cfcd92.png]: /images/20221119/31fbdb2912fc462da6d5eeb1eafb7a5a.png
[959a1f71e00990e6d2ff9a2e03208bc8.png]: /images/20221119/610ca836d5df4fe2bc188ed31d4e1a92.png
[85e7e385fca8b624e276072cc8a78a09.png]: /images/20221119/8a8d802c6d864715ac799a333286e834.png
[b9298839d6c0858a7bd5040f0d6d3be1.png]: /images/20221119/d19a1600735d4fa4b67c254a35e1daa0.png
[79011abf4abbfb8694a7fb86d9da26ea.png]: /images/20221119/f554c131f4db4fd1886418cf4e6a6bfa.png