I’ve always found sites built on MySQL a little scary. With HMTL and typical flat files, you can just always open your data in a text editor, make changes, save it and move on. The problem with this approach is that you will eventually hit a wall where maintaining the data isn’t very practical. Take my cooking site for instance www.sinfulvegetarian.com. If that site didn’t have a backend database, I would have to maintain over 40 HTML pages. Even if I did use server-side includes (which I do) the site would become unwieldy very quickly. As it sits, I’ve only coded about seven pages for that website. Two of those pages are the server-side included headers and footers. This is so I can maintain the Google Analytics code on only one footer instead of updating five footers if Google decides to change that code again. So while I still have a bit of the fear of the unknown when it comes to mysql, I find it very useful. This brings me to my most recent hitching point with MySQL.
I recently decided to consolidate all of my websites on one virtual server via add-on domains. This allows me to pay less than $10 per month and host my seven plus websites in one place. This saves me a ton of money over what I was doing and is totally acceptable because all of my sites are relatively low in load and traffic. The main reason I put it off as long as I did was because several of my sites are driven by mysql backends. I was afraid of corrupting the database or not being able to move it or just flat out afraid of the unknown but finally I put my mind to it and figured it out. I hope this article helps someone out there complete this VERY simple task that is not very widely/well documented to my knowledge. I am going to assume rudimentary knowledge of how to FTP, edit text files and how to access your hosting company’s control panel. Most companies that I’ve used seem to use similar control panels but if not, you can always ask your hosting company for tech support on this topic.
First off, you’ll need to pull your entire site down from with FTP to your local computer. If you have a Mac, I suggest Cyberduck. If you are using a PC, Coreftp provided me years of excellent service. After you’ve saved your website, now you need to snag the database. You’ll need to log into your existing hosting company’s cpanel and choose the phpMyAdmin option. Some hosts have a direct way to access this tool as well. If in doubt, ask tech support. When phpMyAdmin pops up, choose the export tab. Now there is a place to select which database to export. Highlight the one that corresponds to your site (or one of them if there is more than one, I’m only giving instructions to do this one at a time). Now most importantly, ignore all the options and leave them default except for where it says “save to file” at the bottom and asks for a name. Give it a memorable name and hit go. Save the file to your local computer.
Now on the new web server, you need to log into your control panel and find what might be called the “MySQL Databases” icon. Whatever the case, you are trying to get to the place where you create new MySQL databases and users. Now create a new username and password. This is something you’ll need to write down and use in a few minutes but you won’t need to remember it so choose something secure. Then you need to create a database. Use something description and write that down also. THIS IS IMPORTANT!!! Look on the page and you will probably notice that username and database name you chose have something appended to them. Usually this is the username that you needed to log into the control panel and FTP. THAT is what you need. So if you named your database acmewidgets, your full database name is probably something like johnsmith_acmewidgets. So far you should have:
username: johnsmith_widgetsuser
password: Ty23$%!!!
database: johnsmith_acmewidgets
One more thing before you leave that page. You need to associate the username with the database and assign privileges. I will let you be the judge of which privileges you need to provide but remember if something doesn’t work, you can always come back here later and tweak the settings.
Now, open up the database.sql file that you saved on your local computer with a text editor. You will see something like this at the top:
— phpMyAdmin SQL Dump
— version 3.2.4
— http://www.phpmyadmin.net
—
— Host: localhost
— Generation Time: Apr 20, 2010 at 01:43 AM
— Server version: 5.1.30
— PHP Version: 5.2.4
…
—
— Database: `john_widgets`
—
CREATE DATABASE `john_widgets` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `john_widgets`;
You will need to change the three occurrences of your old database name to the one you just created on your new web server. One is just a comment but it’s still good practice to change it. Save and close the file. Next, in phpMyAdmin on the new webserver, go to the import tab. Now browse and find you hand-modified database file where it says “location of text file” and then hit go. If all is right in the universe, you should get get a nice message that says that the import was successful. If not, it might be time to bug tech support.
Next, you need to go to your website on your local computer. You’ll need to find the file(or ALL the files) that contain references to your database. On wordpress, this is easy. They are all housed in wp-config.php. WordPress has done an incredible job of making it simple for you. These lines are the only ones that should concern you when transferring a site unless you had an exotic configuration of some sort:
// ** MySQL settings – You can get this info from your web host ** //
/** The name of the database for WordPress */
define(‘DB_NAME’, ‘john_oldwidgetsdb’);
/** MySQL database username */
define(‘DB_USER’, ‘john_oldusername’);
/** MySQL database password */
define(‘DB_PASSWORD’, ‘password’);
Change the three items in question. Upload your website via FTP and cross your fingers. If all went well, you should be good to go. Remember to redirect your host file or whatever you may need to do to make sure you are looking at the new site and not your old one.
If you are transferring something besides a WordPress site, you may have a LOT more hunting to do to find those database references. One of my sites is rather poorly coded (I didn’t code it) and there are three files for the main site with database info hard coded into them and SEVEN more for the administration console for that site.
Hopefully this article has explained the process a bit even if you chicken out and just beg tech support to transfer your site for you. MOST web hosting companies are glad to move your site over for free. At least that has been my experience.
