Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Short version: are there settings for my.cnf that I could adjust to improve the speed of a database restore (backup created with mysqldump, restore being done with the mysql client)? I have plenty of RAM (20+ GB) available to me, as well as 8 CPUs.

Long Version:

I have nightly backups of my production database (created with mysqldump) that I would like to load into development server.

When I tried to restore yesterday, I got a syntax error when trying to run the backup file using the MySQL client program. I would poke into the file, but it is too big to open in vi, and since extended-inserts was turned on, grep is pretty much useless. (I have a sneaking suspicion that I blew out a buffer someplace, but that may not be true.)

I turned off extended-inserts and made another dump, but I killed the restore after 9 hours.
top is telling me that the mysql server is just dawdling along.

This is nearly an out-of-the-box configuration at the moment. The only changes that I have made so far are:

key_buffer = 1G    
innodb_buffer_pool_size = 10G
innodb_log_buffer_size = 10M
innodb_flush_method=O_DIRECT

I'm still a beginner DBA for MySQL, so those changes are based on "gosh, that looks like it might help."

Server version: 5.5.29-0ubuntu0.12.10.1 (Ubuntu). I'm using (I hope) all InnoDB tables.

EDIT: there is a configuration tool at the Percona website that seemed to give me reasonable settings for memory. After setting that up, I tried loading the database several times (and even ended up re-initializing the server). I got a variety of results -- sometimes the load completed successfully, sometimes the server crashed, sometimes I got syntax errors. I have learned that the backup without extended-inserts is a non-starter: it just takes too long to load.

Thanks to those who responded.

share|improve this question
1  
What error you got while restoring the DB? –  Abhishek Anand Amralkar Apr 9 '13 at 12:52
    
You should first create database, then select it in Phpmyadmin and click on Import? Did you done that? –  Luka Apr 9 '13 at 13:23
    
@AbhishekAmralkar : I'm getting a syntax error. I don't have it in front of me. But having a syntax error in a file generated by mysqldump struck me as strange. –  Chris Curvey Apr 9 '13 at 19:55
    
@Luka : I have created the database and am restoring it, but I'm using command line tools (mysqldump and mysql) not phpmyadmin. –  Chris Curvey Apr 9 '13 at 19:56

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.