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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 86 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.

pv dumps the sqlfile.sql and passes them to mysql (because of the pipe operator). While it is dumping, it shows the progress. The cool thing is that mysql takes the data only as fast as it can progress it, so pv can show the progress of the import. I do not have any proof. But it seems so. I guess there is some buffer used, but at some point I think mysql does not read any more data when it is still busy processing.

share|improve this answer
    
I would guess that mysql might have a buffer, in which some data can be piped in, without being fully "processed" (i.e. if it errors out, pv may have slightly over-reported what actually gets in). But in general, this is how pipes work. It's the same reason you can do sudo hd /dev/sda1 | less and not have your entire system partition in memory. – snapfractalpop Aug 21 '15 at 15:34
    
@snapfractalpop pv won't be overly accurate in many cases because some chunks of SQL will take more time to process than others. A line that constitutes a simple insert will run a lot faster than one that creates on index on a table that already has many rows, for instance. But a a rough idea of progress the output should be helpful unless the read buffer used by mysql is particularly large (for a 7Gb input the buffer would need to be very large to render pv's output not useful at all. – David Spillett Jan 20 at 14:38
    
@DavidSpillett indeed. Your comment mirrors my sentiment. Basically, pv is crude, but effective. What I like most about it is how general it is. Such is the beauty of unix pipes (thank you McIlroy). – snapfractalpop Jan 20 at 15:19

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
    
That worked. Thanks. One last question is, by experience, do you know if the importing time is roughly linear with respect to the .MYD and .MYI file sizes? – qazwsx May 2 '12 at 21:06
    
Table reload is linear. Index rebuilds are linear. Years ago, it was not as I ventured this as a question to MySQL ( lists.mysql.com/mysql/202489 ) and I mentioned it in the DBA StackExchange ( dba.stackexchange.com/a/2697/877 ) – RolandoMySQLDBA May 2 '12 at 21:12

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

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.

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.

share|improve this answer

If you just want to check if it is stalled you can query

show processlist; 

and see what is being executed.

share|improve this answer

You could enable the general log to track what's modified or you could also check the binlogs. With flush logs you can force to create a new binlog. And with mysqlbinlog you can check all modifications. To enable general log:

mysql> SET GLOBAL general_log_file = '/mysql/log/all_queries.log';
mysql> SET GLOBAL general_log = 'ON';
mysql> SET GLOBAL general_log = 'OFF';

You can execute "tail -f /mysql/log/all_queries.log" to see what's been processed. Keep in mind that the log file will grow very fast and other transactions going on will also be seen in the log file.

share|improve this answer
1  
So, based on this, how do you get the progress? – dezso Jan 20 at 11:08
    
It depends on the structure of the SQL file. If for example you have 1 million records and they have an unique identifier going from 1 to 1000000, in the general log, based on the insert instruction you could estimate the progress. – Peter Vandenberghe Jan 20 at 13:38

I'm so surprised no one just posted 'mysql -v' as an option. If it gets stuck, the output will stop.

share|improve this answer
2  
"Monitoring progress" commonly means trying to estimate how far the process has progressed or when would it complete, which mysql -v won't offer. Also, spewing 7 GB of data to the terminal will significantly slow down the restore. – mustaccio Feb 3 at 19:25
    
i see, thanks for the explanation. thats true, the output of 7 GB would not be good to output into the terminal. i guess me using -v was just for a small local test case where my db would just get stuck. – dtc Feb 3 at 20:46

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.