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