Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am importing a 7 GB foobar.sql to restore a table in a local database.

$ mysql -h localhost -u root 'my_data' < foobar.sql

$ mysql --version
/usr/local/mysql/bin/mysql  Ver 14.12 Distrib 5.0.96, for apple-darwin9.8.0 (i386) using readline 5.1

How can I monitor its progress?

share|improve this question
you can also use -v (verbose) to see each query on terminal –  astro Jan 12 at 5:12

4 Answers 4

up vote 51 down vote accepted

If you're just importing from a dump file from the CLI on *nix, e.g.

mysql -uxxx -pxxx dbname < /sqlfile.sql

then first install pipe viewer on your OS then try something like this:

pv sqlfile.sql | mysql -uxxx -pxxxx dbname

which will show a progress bar as the program runs.

It's very useful and you can also use it to get an estimate for mysqldump progress.

share|improve this answer
Didnt work for me. pv gives no output. –  Jaseem Jun 7 '13 at 19:50
My dump already finished and it is still at 27% without finishing and keep reporting with a increasing rate even if the backup already is done. Obs.: I am using a 4Gb sql file. –  developer3466402 May 12 '14 at 12:30
This is mindblowing. Thank you. Time to ask stack overflow on how this works at a program level :D –  Tek Jul 25 '14 at 14:53
Thank you by taking attention to a less common but useful command. It works. :) For more information about pv take a look at: cyberciti.biz/open-source/command-line-hacks/… –  Onur Yılmaz Feb 1 at 15:58
@Rob, Since the mysql command itself has no output, how does pv work? –  Pacerier Mar 18 at 7:49

When you execute a mysqldump of a single database, all tables are dumped in alphabetical order.

Naturally, the reload of the mysqldump into a database would also be in alphabetical order.

You could just do a SHOW PROCESSLIST; and find out the DB Connection running the mysqldump. When the dump is reloaded, the DB Connection will vanish.

If you want to know what tables are in the dumpfile, run this against foobar.sql

cat foobar.sql | grep "^CREATE TABLE" | awk '{print $3}'

UPDATE 2012-05-02 13:53 EDT

Sorry for not noticing that there is only one table.

If the table is MyISAM, the only way to monitor is from the OS point of view. The reason? The table is write-locked throughout the reload. What do you look for? The size of the .MYD and .MYI files. Of course, you need to compare that with what the table size was before on the other DB server you imported from.

If the table is InnoDB and you have innodb_file_per_table enabled, the only way to monitor is from the OS point of view. The reason? The table is write-locked throughout the reload. What do you look for? The size of the .ibd file. Of course, you need to compare that with what the table size was before on the other DB server you imported from.

If the table is InnoDB and you have innodb_file_per_table disabled, not even the OS point of view can help.

UPDATE 2012-05-02 13:56 EDT

I addressed something like this last year : How do I get % progress for "type db.sql | mysql"

UPDATE 2012-05-02 14:09 EDT

Since a standard mysqldump write-locks the table like this:

INSERT INTO `a` VALUES (123),(451),(199),(0),(23);
/*!40000 ALTER TABLE `a` ENABLE KEYS */;

then, there is no way to get a progress from with mysql until the table lock is released.

If you can get LOCK TABLES and UNLOCK TABLES commented out of the dumpfile...

  • if the table is MyISAM, SELECT COUNT(*) would work
  • if the table is InnoDB, SELECT COUNT(*) would probably slow down/halt the load until count is done
share|improve this answer
Hi, thanks for input. But the command I posted didn't run any mysqldump. So I wonder why you think there is a connection running mysqldump? There is only one ^CREATE TABLE in foobar.sql with millions of rows. Is it possible to monitor how many has been processed? –  qazwsx May 2 '12 at 17:22
What if the script has multiple INSERT INTO statements? Can you use a SELECT COUNT(*) FROM TableName ? –  ypercube May 2 '12 at 17:56
@ypercube When mysqldumps are created, they include a LOCK WRITE. Thus, the table is write-locked throughout the reload. Not even the information_schema can help in that instance. This applies equally to InnoDB tables : dba.stackexchange.com/a/1307/877 –  RolandoMySQLDBA May 2 '12 at 17:59
@ypercube I addressed your comment in my answer –  RolandoMySQLDBA May 2 '12 at 18:09
In the database subfolder in datadir. For your case, that would be /var/lib/mysql/my_data. If that is not the correct location, the run SHOW VARIABLES LIKE 'datadir'; then go to the folder my_data under that. –  RolandoMySQLDBA May 2 '12 at 20:46

If you've already started the import, you can execute this command in another window to see the current size of your databases. This can be helpful if you know the total size of the .sql file you're importing.

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" 
FROM information_schema.TABLES GROUP BY table_schema;  

Credit to: http://forums.mysql.com/read.php?108,201578,201578

share|improve this answer

As a solution for someone who can't get pv to work or for whom pv tells lies. You can monitor the size of ibdata1 file in /var/lib/mysql which contains the data. This will end up the same size (or thereabouts) of the filesize in your source server.

If there are many tables you can also watch them appear one by one in /var/lib/mysql/< database name>.

share|improve this answer
Is this behavior documented? –  Pacerier Mar 18 at 7:52
Well only in this post. I happened to use this fact recently when a long term database had built up a log file of around 20G over a period of three or four years. I noticed the transfer was taking ages and used this technique to monitor progress. –  nerak99 Mar 19 at 11:48
Which means this is likely to break in the future. –  Pacerier Mar 19 at 13:26
@Pacerier. I think you are looking at my comment the wrong way round. I think that it is highly unlikely that the day will dawn when a database does not involve a file somewhere or other. Meanwhile, you can monitor the file to see how a transfer is progressing. The method I suggested has been something you could do in one form or another since the first sql database was written. I never intended to suggest that it was any kind of "official" technique that a manual jockey could fall back on. It assumes a general level of proficiency with computers in general and unix in particular. –  nerak99 Mar 22 at 10:02

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.