Does mysqldump support a progress bar?

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
...

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.

Tags:

Mysql

Recent Posts