Migrating SQL/MySQL databases from one location to another is not something most of you will do. There are circumstances where migrating a database from the administration side of a server is easier than trying to manually copy data. If you change your web host (or server) after creating databases in the current location or if you wanted to back up your database files manually, then it is essential to know the fundamentals of migration.
Background
SQL/MySQL databases are like MS Excel spreadsheets or MS Access files. The only difference is these databases run on a web (or internal) server as oppose to running on a local computer. Fundamentally they are similar in operation, but differ in how they process and store data. The MS Excel is one of many low level database program. The MS Access is step up from Excel with much more advanced functions. The SQL/MySQL databases are much more advanced and extremely flexible than the previous products.
The flexibility of SQL/MySQL can be summarized…
- They will run most (as far as I know, all) operating systems.
- The deployment and distribution management is very flexible.
- They have the ability to run several databases while communicating between them.
- Easy universal database migration options (no proprietary files).
Migration
For this example, I used phpMyAdmin program installed on a Linux server. This particular example involves in transferring databases of mTouch WP plugin data from one location to another. You can use the exact same steps to migrate SQL/MySQL databases in phpMyAdmin. If you you a different program for database management, it should be similar to phpMyAdmin, but be careful of how you export it.
This is for advanced users ONLY. A mistake can take down your entire website. Use the guide at your own discretion.
1. Make sure your program/plugin/add-on is upto date.
2. Log into your server and then phpMyAdmin.
3. Choose the database location from the left pane of the phpMyAdmin window.
3. Select the database related to your program. It is better if you export and import all databases used by the program. Then go to Export.
4. Choose the export settings appropriately. As long as you will be using the same phpMyAdmin at the new location, it should not matter which format you save export the files.
5. Log into the new server/location and install the save exact software. It is highly recommended using the exact same software version as the one you are migrating from.
6. Long into the phpMyAdmin (new location) and find the location of the databases on the left pane of phpMyAdmin window.
7. Backup the new databases created by the program by exporting them (same as above). Then upload and install the old files using import pane.
8. Validate the install by going into the front end. If you have done everything right, you should be able to now have the database up and running.
It is as simple as that. Backups are very important so never ever skip backups before and during modifications.