Linux Pipe Viewer – MySQL

How long does it take to backup/restore a MySQL database?

This is a common question I used to ask myself at least once a month. It would always be crunch time, and a database table needed to be copied to another server. This is an easy task when the table only has 100 rows… but of course the one in question is 12GB on disk. How do we measure this? How long will this take?

Again I use pipe viewer to answer this. Lets go over a basic example working with a full database including all the tables. The raw InnoDB and MyISAM data files consume 1.3GB. We will assume the most basic mysqldump command ignoring triggers and routines. The task at hand is to perform a backup, and to reload from this backup all while giving good estimates on the time required.

First, we need the initial backup size. This means we have to perform a full backup without knowing how long it will take.

$ time mysqldump -h dbserver database > db.sql

real    0m21.370s
user    0m13.107s
sys     0m5.603s

[user@server ~]$ ls -lh db.sql
-rw-rw-r-- 1 user user 882M Jan 17 14:35 db.sql

Already we can see this backup took 21 seconds and uses 882MB. The problem is that we did not know anything while the backup was taking place. Now that we know the backup size we can use this value in pipe viewer.

Here is the example again using pipe viewer to estimate the backup size…

[user@server ~]$ mysqldump -h dbserver database | pv -s 882M > db.sql
 881MB 0:00:19 [45.3MB/s] [=========================> ] 99%

Notice this does not reach 100%. Lets fix this by using the actual byte size..

[user@server ~]$ ls -l db.sql
-rw-rw-r-- 1 user user 924259742 Jan 17 15:31 db.sql
[user@server ~]$ mysqldump -h dbserver database | pv -s 924259742 > db.sql
 881MB 0:00:17 [  49MB/s] [=================================>] 100%

Score! If we know the end size of a backup, we can estimate how long it will take. In this case it took 17 seconds.

Now lets restore from this backup. This time I will show you an example of the progress bar early in the process showing a percentage complete and an estimated time to completion of 4 minutes and 12 seconds to go.

[user@server ~]$ pv db.sql | mysql -h dbserver database2
 136MB 0:00:46 [2.85MB/s] [===>                        ] 15% ETA 0:04:12
 881MB 0:06:12 [2.37MB/s] [===========================>] 100%

Notice that we do not need to put in the size of the backup to get an accurate estimate. Here pipe viewer works like the cat utility to send the contents to stdout and into the mysql command to execute the statements. We always knew that reloading a MySQL database is slower due to calculating indexes and other I/O nonsense. Now we can start a copy or restore and know the progress and an estimated time to completion. Finally!

All rights reserved