Does mysqldump support a progress bar?

Is there any wa开发者_开发技巧y to determine, while mysqldump is running, how much of the backup has completed or how much is remaining?

Install and use pv (it is available as a yum package for CentOS)

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.

Assuming the expect size of the resulting dumpfile.sql file is 100m (100 megabytes), the use of pv would be as follows:

mysqldump <parameters> | pv --progress --size 100m > dumpfile.sql

The console output will look like:

[===> ] 20%

Look at the man page man pv for more options. You can display the transfer rate, or how much time has elapsed, or how many bytes have transferred, and more.

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:

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:

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

Yes, a patch was committed on 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.

So check your version, update if needed and enjoy.

A complete version of the Russell E Glaue answer. Get rounded db size as pv accepts integer only and calculate data length without indexes, per @mtoloo comment:

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

After MySQL 5.7+, you can use mysqlpump. Although it doesn't show a progress bar, it still shows some progress like this:

Dump progress: 1/1 tables, 0/191 rows
Dump progress: 16/17 tables, 19959/116836 rows
Dump progress: 18/19 tables, 22959/117032 rows
Dump progress: 19/21 tables, 24459/118851 rows
Dump progress: 19/22 tables, 26959/118852 rows
Dump progress: 21/23 tables, 28545/119020 rows
Dump progress: 22/23 tables, 30045/119020 rows
...

Just created Bash script MySQL / MariaDB Dump Helper inspired by Russell E Glaue and pocheptsov.

There is a sample of output:

❯ ./bin/db/dump xxx
[2020-08-19 09:54:59+02:00] [INFO] Dumping database 'master' (≈5.4GiB) into ./bin/db/master.sql...
5.40GiB 0:07:56 [11.6MiB/s] [===========================================================================>] 100%
[2020-08-19 10:02:56+02:00] [INFO] Done.
[2020-08-19 10:02:56+02:00] [INFO] Dumping database 'second_db' (≈2.2GiB) into ./bin/db/second_db.sql...
 905MiB 0:01:38 [1.34MiB/s] [==============================>                                              ] 41% ETA 0:02:17

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)

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.

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.

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.