MySQL mysqldump 是否支持进度条？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4852933/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does mysqldump support a progress bar?
提问by Sina
Is there any way to determine, while mysqldumpis running, how much of the backup has completed or how much is remaining?
有什么方法可以确定在mysqldump运行时完成了多少备份或剩余多少？
采纳答案by xelco52
Yes, a patch was committedon March 27th, 2010:
This new patch has an extra parameter --show-progress-size which by default is set to 10,000. So when --verbose is used, every 10,000 lines you will get a regular status output of the number of rows for a particular table dumped.
这个新补丁有一个额外的参数 --show-progress-size，默认设置为 10,000。因此，当使用 --verbose 时，每 10,000 行，您将获得转储的特定表的行数的常规状态输出。
So check your version, update if needed and enjoy.
因此，请检查您的版本，根据需要进行更新并享受。
回答by Russell E Glaue
Install and use pv(it is available as a yum package for CentOS)
安装使用pv（CentOS 有 yum 包）
http://www.ivarch.com/programs/pv.shtml
http://www.ivarch.com/programs/pv.shtml
PV ("Pipe Viewer") is a tool for monitoring the progress of data through a pipeline. It can be inserted into any normal pipeline between two processes to give a visual indication of how quickly data is passing through, how long it has taken, how near to completion it is, and an estimate of how long it will be until completion.
PV（“管道查看器”）是一种通过管道监控数据进度的工具。它可以插入到两个进程之间的任何正常管道中，以直观地指示数据通过的速度、花费的时间、距离完成的程度以及完成之前的估计时间。
Assuming the expect size of the resulting dumpfile.sql file is 100m (100 megabytes), the use of pvwould be as follows:
假设生成的 dumpfile.sql 文件的预期大小为 100m（100 兆字节），使用pv如下：
mysqldump <parameters> | pv --progress --size 100m > dumpfile.sql
mysqldump <parameters> | pv --progress --size 100m > dumpfile.sql
The console output will look like:
控制台输出将如下所示：
[===> ] 20%
[===> ] 20%
Look at the man page man pvfor more options. You can display the transfer rate, or how much time has elapsed, or how many bytes have transferred, and more.
查看手册页man pv以获取更多选项。您可以显示传输速率，或者已经过去了多少时间，或者已经传输了多少字节等等。
If you do not know the size of your dump file, there is a way to obtain a size of the MySQL database from the table_schema - it will not be the size of your dump file, but it may be close enough for your needs:
如果您不知道转储文件的大小，有一种方法可以从 table_schema 获取 MySQL 数据库的大小 - 它不会是转储文件的大小，但它可能足以满足您的需要：
SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
Update
更新
In my experience, when dumping the entire MySQL server, the actual uncompressed size of the mysql dump (using the mysqldump --hex-blob option) is roughly between 75% to 85% of the live size of MySQL data obtained from information_schema. So for a general solution, I might try the following:
根据我的经验，在转储整个 MySQL 服务器时，mysql 转储的实际未压缩大小（使用 mysqldump --hex-blob 选项）大约在从 information_schema 获得的 MySQL 数据的实时大小的 75% 到 85% 之间。因此，对于一般解决方案，我可能会尝试以下操作：
SIZE_BYTES=$(mysql --skip-column-names <parameters> <<< 'SELECT ROUND(SUM(data_length) * 0.8) AS "size_bytes" FROM information_schema.TABLES;')
SIZE_BYTES=$(mysql --skip-column-names <parameters> <<< 'SELECT ROUND(SUM(data_length) * 0.8) AS "size_bytes" FROM information_schema.TABLES;')
mysqldump <parameters> --hex-blob | pv --progress --size $SIZE_BYTES > dumpfile.sql
mysqldump <parameters> --hex-blob | pv --progress --size $SIZE_BYTES > dumpfile.sql
回答by pocheptsov
A complete version of the Russell E Glaueanswer. Get rounded db size as pv accepts integer only and calculate data length without indexes, per @mtoloocomment:
Russell E Glaue答案的完整版本。获取四舍五入的数据库大小，因为 pv 仅接受整数并计算没有索引的数据长度，根据@mtoloo注释：
db_size=$(mysql -h"$DB_HOST" \
-u"$DB_USERNAME" \
-p"$DB_PASSWORD" \
--silent \
--skip-column-names \
-e "SELECT ROUND(SUM(data_length) / 1024 / 1024, 0) \
FROM information_schema.TABLES \
WHERE table_schema='$DB_NAME';")
Create a backup into timestamped filename:
将备份创建为带时间戳的文件名：
mysqldump -h"$DB_HOST" \
-u"$DB_USERNAME" \
-p"$DB_PASSWORD" \
--single-transaction \
--order-by-primary \
--compress \
$DB_NAME | pv --progress --size "$db_size"m > "$(date +%Y%m%d)"_backup.sql
回答by Luis H Cabrejo
I was looking for some similar tool (PV) but Im not dumping DBs. While merging two large databases, with some extra calculations and formulas, the process was not listed on TOP nor HTOP utilities, but only on the header as a io% (Only shows in the list at startup process then it disapears). Shows high usage all the time, but in this case is on IO side and its not listed in the bodylist on the utilities as other processes do display. I had to use IOSTAT instead to see writing progress of output database but couldnt figure if was actually doing the writing on the file (only displays xfer rates). I found out the old way, using Filezilla FTP, comparing source databases sizes, and since Im doing a merge, the output file had to showed up while the files were merged. I was able to watch the increment when refreshing filezilla directory contents until process ended succesfully, the size of the sum of both DBs merged as expected. (You can actually do a refresh per minute and calculate manually the time of your hardware io xfer and processing speed)
我正在寻找一些类似的工具（PV），但我没有倾倒数据库。在合并两个大型数据库时，使用一些额外的计算和公式，该过程未列在 TOP 或 HTOP 实用程序中，而仅作为 io% 列在标题上（仅在启动过程中显示在列表中，然后消失）。一直显示高使用率，但在这种情况下是在 IO 端，它没有像其他进程那样显示在实用程序的 bodylist 中。我不得不使用 IOSTAT 来查看输出数据库的写入进度，但无法确定是否确实在对文件进行写入（仅显示 xfer 速率）。我找到了旧方法，使用 Filezilla FTP，比较源数据库大小，并且由于我进行了合并，因此在合并文件时必须显示输出文件。我能够在刷新 filezilla 目录内容时看到增量，直到进程成功结束，两个数据库的总和的大小按预期合并。（您实际上可以每分钟刷新一次并手动计算硬件 io xfer 的时间和处理速度）
I went to the MySQL directory (Where the actual database is stored as files, in my case ../mysql/database/tablename.MYD ... (files in MYSQL are saved with a corresponding .FRM file that contains the table format data, and an .MYI file, which serves as the database index) and just refresh the page to be able to see actual size of output merged file and indeed, worked out for me.
我转到了 MySQL 目录（实际数据库存储为文件的位置，在我的情况下为 ../mysql/database/tablename.MYD ...（MYSQL 中的文件与包含表格式数据的相应 .FRM 文件一起保存） ，和一个 .MYI 文件，它用作数据库索引），只需刷新页面即可看到输出合并文件的实际大小，实际上，这对我有用。
By the way, TOP and HTOP only showed MYSQLD doing some backgroud process but the workhorse was transfered to de IO side for output. Both of my merging DBs were of about 20 million rows about 5 gigs each, on my CPU dual Core was taking hours to merge, and no progress at all were showed anywhere (Even phpmyadmin timed out but the process continued). Tried to use PV using PID numbers but since Im not doing dumping there is no transfer to pipe. Anyway just write this out for someone that is looking for effective and easy way to check progress of creation of output file. It should work also for dumps and restores. Be patience, once it starts, it will finish, thats for sure, unless error on SQL sintax (Happened to me before, no rows were merged at all on previous trials, it took its sweet time but nothing happened at the end, and without tools, impossible knowing whats going on, its a waist of time), I suggest you can try with some small sample rows before commit to the time consuming real operation, to validate your SQL sintax.
顺便说一下，TOP 和 HTOP 只显示 MYSQLD 做了一些后台处理，但主力被转移到 de IO 端进行输出。我的两个合并数据库都有大约 2000 万行，每个大约 5 演出，在我的 CPU 双核上合并需要几个小时，并且在任何地方都没有显示任何进展（即使 phpmyadmin 超时，但过程仍在继续）。尝试使用 PID 数字使用 PV，但由于我没有进行转储，因此无法传输到管道。无论如何，只需为正在寻找有效且简单的方法来检查输出文件创建进度的人写出来。它也应该适用于转储和恢复。要有耐心，一旦开始，它就会完成，那是肯定的，除非 SQL sintax 出错（以前发生在我身上，在之前的试验中根本没有合并任何行，它度过了美好的时光，但最后什么也没发生，而且没有工具，
Not completely answering your question on progress bar on a c++ program, but you can take this to grab file size of the MYD file created and calculate a progress bar using source file size divided by xfer rate to calculate remaining time. Best Regards.
不能完全回答您关于 C++ 程序进度条的问题，但您可以借此获取创建的 MYD 文件的文件大小，并使用源文件大小除以 xfer 率来计算进度条以计算剩余时间。此致。
