Dedicated Servers – Moving Your MySql Databases to a Dedicated Server

Dedicated Servers – Moving Your MySql Databases to a Dedicated Server




This is one of the largest and often unanticipated problems faced by webmasters making the switch to dedicated hosting, already when upgrading with the same web hosting company we will focus on the use of phpMyAdmin.

Before making the switch to dedicated hosting you should clearly back up all your website files and your databases. The former can be done via FTP but your databases should be ‘dumped’ using phpMyAdmin this can be achieved by logging on to phpMyAdmin selecting your database and clicking export, you will then be taken to the export page and should select ‘save as file’ ‘compression none’ and then go. Your database will then be dumped to the download file on your hard excursion.

Once your server is setup you should logon navigate to your database section of your control panel and create a new database, if you are using c-panel you will be allowed to name your database (the easiest option is to name it the same as your dumped database) if not then you will create a new blank database with a different name and username, this is not a problem but is what causes people a lot of trouble.

When you have set up your new database on your dedicated server you should navigate to your MySql database dump on your hard excursion (database.sql), open it as a text file and you will be presented with something which begins like this:

— phpMyAdmin SQL Dump — version 2.6.4-pl3 — — great number: dbxxx.hostname.co.uk — Generation Time: May 15, 2009 at 07:57 PM — Server version: 5.0.67 — PHP Version: 4.3.10 — — Database: `dbxxxxxxxx` —

CREATE DATABASE `dbxxxxxxxx` DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci;

USE dbxxxxxxxx;

This is instructing phpMyAdmin to create the database and tables, you should replace dbxxxxxxxx with your new database name and since we already have a database we can erase the following section:

CREATE DATABASE `dbxxxxxxxx` DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci;

USE dbxxxxxxxx;

Failure to do this will consequence in an error message informing you that the database already exists when you try to import the file.

You should be left with a file which looks like this:

— phpMyAdmin SQL Dump — version 2.6.4-pl3 — — great number: dbxxx.hostname.co.uk — Generation Time: May 15, 2009 at 07:57 PM — Server version: 5.0.67 — PHP Version: 4.3.10 — — Database: `dbxxxxxxxx` —

Save the sql file, open up phpMyAdmin select your new database open up the sql window, select import files then choose file, navigate to the updated database.sql file and click go. After a short pause your new database will be populated with the tables from old database.

Your new database is ready to go, but your website does not know it has been renamed. You now need to amend the connection data in your site to mirror your new database details.

The following details need to be changed to mirror your new database:

great number – Usually this will become localhost when moving to a dedicated server

User- Database- Password-

Finally upload your files via FTP to your new server and once your DNS settings have been updated you are good to go.




leave your comment

Top