I have about 12 tables in MySQL innoDB, one of them has 11 million records in it.
I used this command to back things up:
mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz
And this command to import things on the new server:
USE [DBNAME];
SOURCE [/path_to_file/DBNAME].sql;
Here is the pain I am afflicted with (the time is going up with each query!):
What can I do to speed things up? Is something wrong with my mysqldump command?
Best Answer
For your dump command, you could use the following
Extended inserts are better.
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction The Single Transaction option is better for dumping InnoDB tables.
In your my.cnf file, make the following changes temporarily
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_doublewrite
and also
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
Restart MySQL and then remove these options after your import and restart MySQL again.
Memory Tables
If you're entire database could fit into memory, you could change the ENGINE=InnoDB to ENGINE=MEMORY in your dump sql file for every table or just the tables that are taking the longest to import, import the dump file back into your database and then change the engine back to innodb.
ALTER TABLE name_of_table ENGINE=InnoDB;
mysqlimport command
http://linux.die.net/man/1/mysqlimport
I have used mysqlimport to import data backed up using the tab format option in mysqldump. It has proven faster than importing via the mysql command.
Dump the database using mysqldump
mysqldump --tab=/some_directory/ -u [USERNAME] -p [DBNAME] [TABLENAME]
Import the data into your database.
mysqlimport -u [USERNAME] -p [DBNAME] /some_directory/tablename.sql