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.

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…

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.

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!