MySQL Dump/Import progress on Windows

MySQLmysqldump

I'm going to dump a considerable big datatabase (7gb) from a MySQL 5.1 instance running on Windows into a .sql script and import it into a MySQL 5.5 instance, also on Windows (another machine). I'd like to be able to monitor the process of both operations, since I believe they are going to take some time to execute.

Apparentely, this feature has not yet made into the target MySQL versions (http://stackoverflow.com/questions/8806435/how-to-implement-a-mysql-dump-restore-progress-bar). I also checked the solution suggested here, but it seems a lot of work (and also inserting more variables to this sensitive operation) to do something that should be simple.

By the time, ins't there any simple solution to monitor, somehow, the progress of these operations?

Best Answer

Restoration will be

Per database (alphabetical order)
Per table within each database (alphabetical order)

If you want to see the progress, you can run the following every few minutes:

SHOW DATABASES;

To be sure that restore is not blocked, use SHOW PROCESSLIST. It will show you the restore transactions (create table, insert into...).

You can also followed your disk space usage but this is'nt a really good metric...

Max.

Related Solutions

Mysql – Can MySQL 5.1 read a MySQL 5.5 MyISAM file

What could go wrong? If the way MyISAM changed the format of how something is stored in their files, it will break on 5.1. This will render your stats database on the master corrupt and unusable.

Even if the initial load went fine, it doesn't mean something won't come along later with the result listed above...a corrupt database. It's a risk, and if it can be avoided, I personally would do so.

And in your case, it can be avoided by dumping the data using mysqldump quickly and then load into the 5.1 instance.

Mysql – Repair with keycache in thesql runs EVERY time. Can’t even log why it’s happening

Your my.conf says the following: key_buffer_size=32M That's really small. You could afford to set it higher: Run this query please:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will give you a recommended size of key_buffe_size. Please you common sense when setting this value.

Here is something else: I don't see a bulk_insert_buffer_size. The default is 8MB. Try setting it to 512MB:

[mysqld]
bulk_insert_buffer_size=512M

I just noticed your myisam_sort_buffer_size is only 300M. Set it to 4G (max value)

[mysqld]
myisam_sort_buffer_size=4G

Best Answer

Related Solutions

Mysql – Can MySQL 5.1 read a MySQL 5.5 MyISAM file

Mysql – Repair with keycache in thesql runs EVERY time. Can’t even log why it’s happening

Related Question