mysqldump工具如何显示备份进度？

公众号：DBA随笔。后台回复"技术群"可添加微信技术群

mysqldump工具如何显示备份进度？

    mysqldump工具是mysqldump官方自带的一个备份数据库的工具，它导出的数据是标准的SQL语句，当然，它还支持导出CSV文件(--tab选项)以及XML文件(这个用的很少)。

    在导出CSV文件的时候，还有一个限制，就是mysqldump工具必须和mysql server在同一个机器上，而且对文件位置有要求，总之，使用起来不太方便。

常用参数

    mysqldump备份的时候，常用的参数这里我简单列举一下：

--user=xx : 用户

--password=xx：密码

--host=xx：mysql地址

--port=xx：mysql端口

--databases：备份的数据库

--all-databases：备份所有数据库

--tables：备份表

--no-create-db：导出文件不包含创建db的语句

--no-create-info：导出文件不包含建表语句

--no-data：导出文件不包含数据

--routines：导出文件包含存储过程和函数

--triggers：导出文件包含触发器

--events：导出文件包含事件

--single-transactions：开启事务去获取一致性备份

--source-data：获取主库的binlog文件名和偏移量

--where：备份的数据可以满足一定的where条件

--set-gtid-purged：是否设置set gtid_purged语句

   以上这些参数，能够满足备份中大约99%的需求，如果常用这个工具的话，想必都不会陌生，这里不再赘述，有兴趣可以看官方文档。

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

   正常备份的时候，mysqldump只会输出一个我们最终想要的文件以及一些告警信息，不会输出别的东西，如下：

mysqldump -uroot -proot -h127.0.0.1 -P4306 --databases test > tmp.sql

输出内容如下：

mysqldump: [Warning] Using a password on the command line interface can be insecure.

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database.If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

    但是为了展示进度，我们可以通过下面2个方法来进行：

1、使用--verbose参数

   同样的命令，加了--verbose命令，就能看出来一些更详细的信息：

mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test  --verbose  > tmp.sql

命令输出如下：

mysqldump: [Warning] Using a password on the command line interface can be insecure.

-- Connecting to 127.0.0.1...

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

-- Retrieving table structure for table t...

-- Sending SELECT query...

-- Retrieving rows...

-- Retrieving table structure for table t1...

-- Sending SELECT query...

-- Retrieving rows...

-- Disconnecting from 127.0.0.1...

上面加粗部分，就是导出时候的进度。

    如果我们想知道目前导出到哪个表了，只需要提前在information_schema库中查看一下所有表，然后在日志中留意关键日志：Retrieving table structure for table xxx...即可知道目前导出到哪个表了，还可以根据总的表数量来计算一个导出的进度值。

2、使用pv工具

   pv全名为Pipe Viewer，利用它我们可以查看到命令执行的进度。

如果你是centos系统，可以通过yum install pv -y来安装pv工具；

如果你是Ubuntu系统，可以通过apt-get install pv来安装pv工具

    我们先看下使用pv和不使用pv的区别：

mysqldump -uroot -proot -h127.0.0.1 -P4306 --databases test --set-gtid-purged=off |pv  > tmp.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

55.6KiB 0:00:00 [ 981KiB/s] [   <=>             ]

   可以看到，使用pv工具的时候，mysqldump的结果这里多了一个进度，进度左边是导出的速度以及总的导出容量，右边是一个进度条。这样的结果，其实已经能够反映一部分问题了。

    更准确的进度

   为了更加精准的了解导出的进度，可以提前计算一下要导出的库表的总大小，计算的SQL如下：

SELECT ROUND(SUM(data_length) 1024 1024, 0) FROM information_schema.TABLES WHERE table_schema='$DB_NAME';

   注意，这里的容量是一个估算的值，不是很准确。而且我这里将数据大小的单位换算成MB，如果你的数据量很小，可以不用换算，或者数据量很大，可以换算成GB

    然后利用pv的--size参数，告诉pv，此次mysqldump大概要导出多少数据，pv工具会利用这个参考值来给出具体的进度，如下：

mysqldump -uroot -proot -h127.0.0.1 -P4306 --set-gtid-purged=off --databases test |pv --size `mysql -uroot -proot -h127.0.0.1 -P4306 -Nse "SELECT ROUND(SUM(data_length)) FROM information_schema.TABLES WHERE table_schema='test'"` > tmp.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

55.6KiB 0:00:00 [1.52MiB/s] [=========================>            57%] 

     这里可以看到，最终的进度只有57%，如果数据量很小，统计信息的误差可能很大，只能看到一个大概的备份进度，如果你的数据量很大，这里给出的执行进度，大概率是一个比较准确的值，最终的进度也会趋近于100%

    最后，给出一条SQL执行mysqldump，并查看导出进度的语句：

mysqldump -uroot -proot -h127.0.0.1 -P4306 --set-gtid-purged=off --databases test | pv --size `mysql -uroot -proot -h127.0.0.1 -P4306 -Nse "SELECT ROUND(SUM(data_length)) FROM information_schema.TABLES WHERE table_schema='test'"` > tmp.sql