MySQL DB restore HORRIBLY SLOOOOW!

[Willie Trombone]

I'm sure someone here has some suggestions for me but I'm totally disillusioned with MySQL restore.
I have a .sql file that was created with mysqldump - it's around 75Mb in size. It was created on a Linux system and I'm trying to restore it on a Windows system.

To cut a long story short, I create the DB using the correct character set, then at the command prompt run:

mysql -u root -p dbname < path to .SQL file

And there it sits... I can see the files appear in the data directory but four hours into the process it's not finished and the total data restored is pathetic... I hate to say that there are things I really like about MS SQL but restoring a DB is fast becoming one. It's never taken longer than a minute to restore a very big DB file on MS SQL for me. I'm convinced there must be another way on MySQL.

It seems I'm not the only one having this issue but I'm yet to find a solution:
http://forums.mysql.com/read.php?34,37918,37918#msg-37918

Hey All

I am pretty new to MySQL, I have been working with it on and off for the last few months. I really like the DB, it seems there are a lot of options but I am running into one particular problem that is very frustrating.

My boss did a backup of the DB by using the MySQL Gui Admin tool, the Database was pretty full of data when he did it, hence the backup file for 1 db was 70MB and another was 1.4GB. Initially we tried restoring the DB using the same Admin GUI that we backed it up on but it would lock up almost immediatley, so I upgraded the Admin tool to 1.0.22a I believe. This seemed to fix the lockup part but the restore was going really, really slow. This is all on a Linux server with 2 Hyperthreading 2.4Ghz Xeon processors and 2GB of RAM. So I finally stopped the restore, after 1 hour on the 70MB DB it had only restored like 1 MB of Data.

My next step was to install 1.1 Gui Admin on my windows machine and try the restore from there. This seemed to go slightly faster but not by much so I finally cancelled that. I went into the DB setting and increased the Memory values for the DB and then tried once again to do the restore and once again it is crawling along. I finally just started the restore yesterday for both the DB's, it is running from the server consoles Gui Admin, it has run all night and is only 5% and 10% into the the Restore.

Is there something I can do to speed this up? It seems to get slower and slower the more it restores and at the current speed it looks like it will take a week or more to restore.

Lastly, I know we should have made a file copy of the DB but I am just the janitor trying to clean this up

FYI, each on the DB's run from a different instance of the Mysqld server, so there are 2 servers running simultaniously on the linux server. The Server itself is Suse Linux Enterprise server 9.1 SP2. When the restore is running the memory is preyy much pegged along with the CPU 1 and 2.

Thanks

 

[rurapente]

antivirus and friends off? not scanning the DB files, database or tables?

Shouldnt take that long. could be the GUI tool maybe try importing it via the mysql command line tool?

 

[Willie Trombone]

I found a solution - I've added the following parameters to the beginning of the .SQL

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

And setting key_buffer_size to around a gig, it completed within minutes. I’ll potter around to find which combinations give the biggest boost.
Seems by default that restoring from a mysqldump takes forever - it's not just me, lots of folks having the same issues.

 

[Nod]

If you do a restore, it is normally a good idea to disable any triggers and unique key checks, etc. All of the features that normally would ensure a persistent database, will also slow down a restore.

Remember to make another full backup after the restore and confirming that it is working.