mysql - Does mysqldump support a progress bar?

Question

Is there any way to determine, while mysqldump is running, how much of the backup has completed or how much is remaining?

Answer 1

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