Today I was trying to make a copy of my blog on another server for testing purposes. As I’ve already written about in my article on WordPress blog migrations, the biggest hassle is transferring the files and databases from one location to another. I’ve already written about how you can easily compress your blog directory on the origin server and SSH it to the destination quickly and easily without having to rely upon messy FTP uploads and downloads.
But if you want to do the same with your MySQL database, you may run into a couple of problems, the biggest of which is phpMyAdmin – a tool that is really not built for handling large files. I found out today that phpMyAdmin has a few really tight restrictions on database imports and exports which can be made worse if your server is locked down and you don’t have access to the PHP configuration files like php.ini.
Exporting your MySQL Database
When performing an export function with PHP admin, you may not have the option to store the resulting SQL file on the server itself. This depends on your permissions and what kind of setup you have. In which case, you have no option but to download an SQL dump onto your local PC and upload it via FTP.
If this is the case, it’s far better to perform the operation via SSH instead. Assuming you have commandline access to your server, making an SQL backup of an existing database that is stored on the server is as easy as typing in the command:
mysqldump -p -u oldusername olddatabase > dbname.sql
Where you simply replace “oldusername” and “olddatabase” with your username and database name respectively. Executing this command will prompt you for your password and then store the result in an SQL file called dbname.sql. Once you have this in a publicly accessible location, you can use the wget SSH command on your destination server to transfer it over quickly and painlessly as described in the second link in this article.
Importing the MySQL Database
The real problem is encountered when you try and import your SQL dump into an existing database. When you log into phpMyAdmin and click the “Import” tab, you might see something like this:
If you have your file on a local PC, you can forget about uploading it via the phpMyAdmin interface if it is larger than 2 MB. My database was a full 30 MB! This setting can be changed in the php.ini file, but if you have a shared hosting plan, you may not have this option.
To make matters worse, it may not be possible to FTP the file to the server location mentioned in the import interface as shown above if you don’t have the appropriate permissions. And you may not be allowed to change the location either. In all of this, phpMyAdmin proves to be extremely uncooperative when it comes to importing large databases. So what you do?
SSH to the Rescue
Fortunately for us, our problems are solved if you have SSH access. To start off with, just get the SQL dump onto your server in any location – it doesn’t have to be the one specified in the “Import” tab. As long as you can reach it with SSH, it’s fine. FTP might be slow, but at least it’ll get the job done.
Once you have the file on your destination server, create an SSH connection and navigate to that directory. Then type in this command:
mysql -p -u newusername newdatabase < dbname.sql
First make sure that the destination “newdatabase” is empty. Executing this line will prompt you for your password and then seamlessly import your SQL dump into the target database no matter how large it is without any fuss.
Today I learned that while phpMyAdmin is great for quick database access, it falls woefully short when it comes to import and export functionality with large files. Sure you can configure it to change its limits, but you may not always have the permissions to do so. In such situations, SSH is the far easier option and can save you hours of your time.