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

3 Answers 3

up vote 38 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 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 at 14:53

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:

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

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

Your Answer

 
discard

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.