How to Move MySQL to a New Server

by

The need to shift one’s database to a different server pops up too often. Moving larger databases can be a little difficult, and might involve several technicalities. For example, if you have a MySQL database with size larger than 2 MB, PHPMyAdmin isn’t the right way to manipulate it. To avoid any such difficulties you should follow the simplest steps.

Here we shall discuss how to quickly transfer larger WordPress MySQL databases to a new server. There are two broad steps in the process.

Export Using PHPMyAdmin

PHPMyAdmin is ubiquitous in web servers and hosts, one could use the simplistic export function in PHPMyAdmin. The catch with PHPMyAdmin is that you can export as large tables as you want, but you can’t import. One can find the export options in the ‘Export’ tab in PHPMyAdmin. Here are detailed steps

  1. Log into your cpanel and click on the PHPMyAdmin link or go to www.yourhost.com/phpmyadmin
  2. From the PHPMyAdmin portal, locate the WordPress DB, and click ‘Export’ tab after selecting the database.
  3. Export your database as .sql file.
  4. If your database is less than 10 MB– Use the default export settings. This can be accomplished by simply clicking on ‘Go’ after you have reached the export page.
  5. If your database is greater than 10 MB then select ‘custom’ method instead of ‘Quick’. In this page, just make sure to click ‘Compressed’ when downloading the file. Select ‘Gzip’ in the file formats column.

Importing database using command prompt

After the database has been imported, the next step would be to import the WordPress .sql file.

  1. Create a new database– You can use PHPMyAdmin to create a database. Navigate to the ’Home’, find the ‘Database’ tab and choose the ‘Create database’ field to have an empty database.
  2. In the event that you don’t find a ‘Create database’ option, it would mean that you don’t have enough permission. Change your config file settings to set proper rights.
  3. Give a name and password for your new database, say ‘wp’ is the name of the new database.
  4. Using FTP upload the .sql file to the new server. Remember to place this file in a folder location which you can remember.
  5. Connect with the server using a shell program, for example Putty.
  6. Navigate to the directory where you have uploaded the .sql file.
  7. If you had a large database and had downloaded in compressed format, then use gunzip db_name.sql.gz to unzip the files.
  8. Next, log into MySQL prompt from command line using mysql –u root –p
  9. If you do not know the root password, use a username for which you have the password, in place of ‘root’.
  10. Now that you are logged in, type use WP [or whatever name that you have given to the new database]
  11. Then type source db_name.sql and you are done.

The last few steps would be to verify the integrity of the tables by using PHPMyAdmin. There may sometimes be abnormalities regarding the URLs in use. These might need manual changes, which shall involve changes in the posts and posts_meta tables. You might have to replace the old server’s URL with the new ones.

Following the above steps, you would successfully be able to move even large databases to a new server. That being said, no method is absolutely safe, and hence it is recommended to backup your database before starting the whole migration process. Hopefully, the migration process would be smooth for you.