Westserve

Importing and Exporting big MySQL databases

This is really one of those situations where I’m recording useful information for my own easy recall at a later date – but hopefully it will be helpful for others elsewhere.

With moving a number of websites to my new VPS one of the challenges has been moving the MySQL databases that most of those site run on.
The fabulous PHPMyAdmin is a wonderful piece of software that allows me to do virtually anything with MySQL dbs, but it’s weaknesses lie in the area of exporting and importing large or very large databases.
Because it’s a php application and you access it via your web browser there are a number of restrictions that come into play. Things like, maximum upload limits in php, memory limits, script execution time limits, etc.

So, I set about learning the basics of doing these things via the command line in secure Telent session using Putty
I should add that I’m not uncomfortable using the command line. After all, I did cut my computing teeth in the late ’70s and early ’80s on systems that only had a command line. And don’t get me reminiscing about the glory days of MS-DOS 3.1 ;-)

Some quick Googling helped and so what follows is courtesy of sites like this which provide the mysql syntax, and this which reminds me of all those UNIX commands to move around the server from the command line.

Exporting a MySQL database via the command line
I confess to having little problem exporting via phpMyAdmin, but large db’s do sometimes not like being zipped or gzipped that way, so here’s the run down:

• login to your web site using Puty or similar.
• navigate to the directory where you want to store your extracted database file. This can be a temporary directory you create just for the purpose. Note that after the MySql dump is created, it shouldn’t be left in a publically accessible directory longer than necessary.
• run the following on the command line: mysqldump -a -u USERNAME -p DATABASE_NAME > FILENAME.sql
• If you want to, check the size of your file (command: ls -al)
• gzip your file to make the download faster using the command:
  gzip FILENAME.sql
• download your file in ftp to your local pc to upload and import into your new database

Importing a MySQL database via the command line

• create a new blank database using phpMyAdmin or your web server control panel. Take careful note of the server host name, db name, username and password.
• upload your *.sql file to your new web site (I create a temporary directory for the purpose)
• Fire up Putty, log in and navigate to the directory with the uploaded sql file.
• If you gzipped the sql file, unzip your database file with this command:
  gzip -d FILENAME.mysql
• run the following on the command line to import:
  mysql -u USERNAME -p DATABASE_NAME < FILENAME.sql
• The system will ask you the password for the database, enter it.
• If all goes well, the command line prompt will sit there for anything from a few seconds to several minutes as the db is processed. When prompt changes, check your database in phpMyAdmin to make sure all the tables imported properly.

You’re done!

After many frustrating session trying to split and upload large db’s in phpMyAdmin, I’m kicking myself I didn’t bite the bullet on this before. a hundred time faster and not really any harder.