In this WordPress tutorial, I will show you how to quickly and efficiently move a larger WordPress MySQL database from one server to another using the best techniques available today. There are some things you cannot do with WordPress database tables that exceed 2mb in size. For example you will not want to try and use PHPMyAdmin with tables larger than 2mb because it will usually time out or cause other issues, but we will show you how to avoid this.
Simple is fast! I always stress that the simple way is often the best and fastest way, so keeping that philosophy in mind, here is how I migrate my larger WordPress databases from one server to the next:
Using PHPMyAdmin to Export:
Most hosting providers and web servers have PHPMyAdmin, so take advantage of the simple export features. You can export large tables without any trouble, you just can’t import them. Below is a screenshot of the PHPMyAdmin export screen which you can find by clicking on the export tab in PHPMyAdmin:
Here is how to export any size WordPress database from PHPMyAdmin:
- Navigate to your PHPMyAdmin. If you are using a Cpanel account, use the link in Cpanel, otherwise, go to www.yourhosthere.com/phpmyadmin.
- From PHPMyAdmin, find the WordPress database you want to move or copy and select it, then click the “Export” tab and export your database as a .sql file. If your database is under 10MB in size, just use the default quick settings you see when first going to the page….all you have to do for small to medium sized databases is click go. However if your database is larger than 10mb and you don’t want to wait several minutes to download the .sql file, you will want to choose the “Custom” method under export methods instead of quick. Unless you have special needs, the only thing you need to change in custom setting is where it says “Compression”. Select Gzip and hit go to download.
Using the command Prompt to Import
Once you are done saving your WordPress site’s .sql files, it is time to import them to your new server. Here is how I did it:
- First create a new database, you can use PHPmyadmin since you probably still have it open. Just click on “Home” then click the “Database” tab and towards the bottom of the page will be a “Create Database” field where you can add an empty database. If you don’t see it there, then you do not have proper permissions set up in your phpmyadmin config file. Either change permissions or use the command line by typing “create database databasename;” at the command prompt. See next steps for how to get to a MYSQL command prompt. For any WordPress site, I like to name the database something with “WordPress” or at least “WPS” in the name of the database, but just be certain that you use a unique name for your database so that you don’t risk overwriting any other database you may have on the same server!
- Using ftp, upload your .sql file to a directory you use for .sql files. If you have not done this before, navigate to your PHPMyAdmin folder and create a new folder and name it whatever you want, but be sure to name it something memorable so you will know what it is for.
- Using your favorite command prompt application, connect to your server. I use Putty. You can download the Putty interface from: http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html. Before going to a MySQL prompt, go to the directory you uploaded your .sql file too. Type “cd /var/www/html/phpmyadmin/uploads” but use your own path of course.
- If your file was compressed, type: “gunzip yourfile.sql.gz”. Exclude quotes as usual.
- Log into MySQL. To get to a MySQL prompt from the command line, type “mysql –u root -p”. That will work if you know your root password. If you don’t, use a MySQL username you know the password for in place of root. Press enter. Enter the password. You know should see a MySQL prompt.
- From your MYSQL prompt, type: “use yourdatabasename;” and Press “Enter”.
- Then type “source yourfilename.sql;”. Next, use PHPMyAdmin to verify your data and you are about done! Also navigate to your WordPress site and be sure everything works.
Troubleshooting the Migration of a WordPress Database
There are some troubleshooting steps you may have to take when migrating a WordPress database from one server to another. If you tested your WordPress site that uses the database after moving it and there are problems with the links and problems viewing pages due to abnormalities in the URLs, then you most likely have to go and manually make changes to the URLs in the database tables to make them work. You have to go to the posts and posts_meta tables and make sure that the old site’s URL are all replaced with the new server’s URL instead.
This tutorial is meant to assist you moving quite large WordPress databases from one server to another. Chances are if you do not have a lot of posts and/or pages on your WordPress site, you may not need to take these extreme actions to migrate your data to a new server. You should consider using a plugin such as BackupBuddy or another backup plugin to assist you before you attempt these methods, however if you do have a large site and no other methods work, this method is a sure fire method to migrate large WordPress data tables from your old server to your new one. Good luck moving your WordPress site!