While setting up a development environment on my laptop, Nathan and I came across a bit of a problem: How do you transfer large databases in a way that a) works for databases with a mix of MyISAM and InnoDB tables, b) doesn’t take an eternity, and c) won’t wipe out any existing data on your server? After scouring forums and blog posts, I mixed a number of methods and came up with my own super awesome method! It’s pretty easy, and is much faster and more reliable than a straight MySQL dump:

1. Copy database files from the old server to the new one. On the both servers, find your MySQL data directories. In WAMP, that directory is located at ‘WAMP\bin\mysql\mysql[version]\data\’. In MAMP, that directory is located at ‘MAMP/db/mysql/’ In LAMP, that directory is located at ‘LAMP/var/lib/mysql/’ On your old server, you’ll see folders for every database on the server. Copy the folders for the databases you would like to transfer and paste them to the data folder on your new server. Compressing the files before transfer may save some time too. Technically, the only files we care about here are the MyISAM files, as the InnoDB files won’t be properly transferred this way. But, instead of wasting time individually selecting each MyISAM file, it’s easier just to copy them all. If you don’t have any InnoDB tables already on your new server, you’re in luck! Just transfer all database files from your old server, along with the files that start with ‘ibdata’, and you’re good to go. The ‘ibdata’ files store all the data for InnoDB tables on that server, while the .frm files in the database folders provide the framework. If you already have InnoDB tables on your server, overwriting the ‘ibdata’ files will delete all the data in those tables. If you want to transfer all the users and permissions from your old server, and don’t have any databases, users, or permissions to overwrite on your new server, copy over the ‘mysql’ database folder as well. It contains all the data concerning users and permissions. DO NOT do this unless you have nothing currently on your new server that you’d like to keep.
2. Next, to transfer over the InnoDB tables, we’ll do a MySQL dump. In a terminal window, enter the following command:mysqldump -u [username] -p –databases [dbnames] –ignore-table=[db.ignoretable] | gzip > [exportpath].sql.gz This exports the databases you want to a compressed file, selectively ignoring any tables you don’t want. The reason we want to ignore some tables is because mysqldump is not only slow, but it also tends to stall on large MyISAM files. Since we already transferred over the MyISAM files, you can ignore them. If you’re unsure which tables are MyISAM, either check them out in your phpMyAdmin or look for the files ending in .myd or .myi in the database folders. [username] is your MySQL username. [dbnames] is the names of the databases you’d like to export, separated by spaces. You only need to export the databases that have at least one InnoDB table. [db.ignoretable] is the name of a table you have already imported and would like to exclude from this dump, preceded by the name of the database it is from and a period. For every table you are excluding, you will need another ignore-table rule. You don’t need to ignore all the MyISAM tables, but if they have a large file size, it’s probably a good idea. [exportpath] is the path you’d like to export the compressed file to. Here’s an example: mysqldump -u root -p –databases cats dogs pachyderms –ignore-table=dogs.terriers –ignore-table=pachyderms.pigs | gzip > ~/Desktop/animals.sql.gz Once you run this, it’ll prompt you for your password and spend a little time thinking.
3. Now, we’ll import the dump to the new server. Open a MySQL query window for the new server. You can do that by clicking on the SQL tab in phpMyAdmin, or by entering the following in a terminal:mysql -u [username] -p Now run this query: source [importpath]; [importpath] is the path to the dump file we created last step. Ensure to decompress the file before running this query. You can decompress it by either opening it with a decompressing program or running gunzip [filepath] on it.
4. Finally, restart your servers. Assuming everything was done correctly, all your databases should be working properly!

So that is a little bit of work, but it saves a lot of time when working with large databases. For smaller transfers, a MySQL dump should suffice, and this can be done easily enough from phpMyAdmin with their Import/Export feature. I hope this helps satisfy your database transferring needs! Please comment if you’ve got any questions or suggestions!