If you’ve ever needed to move a website from one folder to another, within the same server and perhaps across sub-domains, then it is very likely that you’ve had to migrate the database as part of the process. Most tutorials on the web will tell you that to migrate a database from a to b, you need to
- Export the source database to an SQL script file
- Create a new database and database user and
- Import the original database into the new database using the SQL script file.
When I first went through this process, I decided to use phpMyAdmin to carry out the export and import. I wanted to keep the SQL script file on the server to speed up transfers etc. but I got stuck at two critical stages.
Firstly at the export database step, I selected “Save on server in the directory /home/dbuser/tmp/” When I hit the “Go” button I was given the following message… “The web server does not have permission to save the file /home/dbuser/tmp/localhost.sql” I attempted to create the folder via an SSH session but the web host wouldn’t allow it at that position in the directory tree. I Google about a bit and read that I needed to modify the config.inc.php file. I gave up looking for this file after about 2.5 seconds as I am far too impatient and just exported the database to my PC’s hard drive. I continued with the process and created the new database and user ready to perform the database import step. I was then of course faced by a similar error message the moment I loaded the import page in phpMyAdmin. This time it read… “The directory you set for upload work cannot be reached”
I was about to restart the search for a method to correct this when out of the corner of my eye, I noticed another tab in phpMyAdmin – it was labelled “Synchronize”. “Synchronize” I thought… that’s exactly what I want to do… sync the new database with the original database. It was a piece of cake to do the sync – here are the steps taken…
- Create your new database as advised by most tutorials on copying sites from one sub-domain to another.
- Follow either a) or b)
a) Create a new database user and give it full access to both the new database and the original database. In hosting accounts using cPanel it’s a simple task of adding the new user to each database with all privileges.
b) Alternatively to save a few mouse clicks, you could just skip the creation of a new user and grant the original database’s user with full privileges to the new database. - Log in to phpMyAdmin – as the new user (if you went with 2a), or the original user (if you opted for 2b).
- Select the Synchronise tab
- Under “Source Database” select “Current Connection” and the original database name from the list.
- Under “Target Database” select “Current Connection” and select the new database name from the list
- Hit “Go” and a page is displayed informing you of the changes due to be actioned once you give the nod
- Click Synchronize Databases and your all done. An exact copy of your original database in a few mouse clicks
You can now make any changes to the websites config files to set any new database name or user parameters that require changing. Let me know if this works for you.
Notes : Above steps are based on phpMyAdmin version 3.4.11.1