How to migrate a large MySQL database
From JumbaWiki
Ths tutorial explains the issues with migrating a large MySQL database. There are two methods discussed:
Contents |
Migrate by SSH access
Exporting/Dumping your existing database to a file
The biggest issue with using PHPMyAdmin to export a large database is that you hit the dreaded PHP timeout and don't get a complete export of your data. Typically, this timeout is set to 30 seconds.
There are a couple of ways to do the export that will result in a complete file. The first way I am going to cover is via SSH
If you have SSH access to your old server, you can run the following command:
mysqldump -u DBUSER -p DBNAME > DBNAME.sql
Replace fields as appropriate. This will dump your database instance to a file named DBNAME.sql in whatever directory you run the command from. If it doesn't work, try changing to a directory that you know you have write access to.
You can also gzip the file by running the following command (especially useful if your database is large.)
tar czvf DB.tar.gz DBNAME.sql
Then download either the SQL file or the tar file via FTP, or use wget to transfer it directly to your new host (providing you have SSH access at your new host, of course!)
Importing/Restoring your database
So, you've dumped your database into a file and need to import it into your new Jumba hosting account. First of all, FTP the file created in the first step to your Jumba webspace. (Obviously if you used wget to do this in the previous step, this step can be eliminated.)
If you turned your SQL file into a tarball, extract it using
tar -zxvf DB.tar.gz
Now, you need to create your new database. The easiest (and recommended) way of doing this is to create it via cpanel.
Once that is done, you can issue the following command in order to import the SQL file into your newly-created database.
mysql -uDBUSER -pDBPASSWORD DBNAME < DBNAME.sql
Congratulations, you've now successfully migrated your database to Jumba!
Migrate with PHP
What if I don't have SSH access?
If you don't have SSH access, there are some great PHP scripts that will do the job. They get around the PHP timeout limit by using Javascript to remember the last part of the export and then restart the script from the appropriate point. There are several PHP scripts available to do the database migration for you if you do not have SSH access or are not comfortable using the command line.
One of the best is MySQLDumper.
Have a look at the web page for instructions and to download the script. This script works extremely well with large databases (It has been used to import over 200MB of database exports).
