On my localhost, I had installed Apache and MySQL and PHP. I installed Wordpress, which really consisted of dropping the files in a /var/www/html subdirectory. After diddling with configuration files, this installation worked fine for months.
Sometime in February 2006, a full-time dedicated web server became available due to the generosity of a friend. I wanted to port the blog from my localhost over to the ISP servers. To be clear, I did not want to port Wordpress itself — all the php scripts. Instead, I wanted one of the MySQL databases to be ported over to the new ISP server.
Wordpress at both locations was version 1.5.2, so I didn't need to worry about version incompatibility. As I went through the process, I discovered the MySQL versions were different and this caused a slight unexpected error which was straightforward to accommodate.
Here are is an overview of the process I used:
- Dump localhost database in SQL text format using phpMyAdmin
- Create and dump virgin WordPress installation from ISP using phpMyAdmin
- Create a copy of localhost dump under a new name, and start modifying it.
- Make changes to wp_options table
- Append usernames
- Fix trailing syntax for each table create command
- Drop all tables from virgin ISP installation
- Import modified SQL text file
1. Dump localhost databaseSeveral Blog or Wiki PHP scripts provide various backup and restore functions. For whatever reason, I decided to go one layer deeper and actually grab a snapshot of the database. If you're familiar with MySQL command line usage, you may be able to do this more directly with a script or something. I prefered to use the phpMySQL suite, which is a set of PHP scripts that know how to interact with MySQL.
One of the phpMySQL capabilities is to write a text file that includes all the SQL commands necessary to re-create the database. A matching capability is to import a text file and execute the embedded SQL commands.
Together, these capabilities of phpMySQL provide a neat and clean way of backing up the Blog database in a file that is easy to handle, compresses well for archiving, and can be modified directly if the need arrises. ClassicTextFileAdvantages.
2. Create and dump virgin WordPress installation on ISPEither before or after the prior step, go to the new ISP server and create a WordPress installation. In my situation, they have an application called "Fantastico" that accepts a few simple pieces of information and creates the entire directory structure and configuration files.
This step is required so you don't have to guess at all the configuration options for Wordpress. All you have to do is mimic what the default installation does.
Once the virgin installation is created, use an ISP version of phpMySQL to dump a copy of the virgin MySQL database. Save this somewhere such as you desktop where you can print it, scroll through it, clip from it, etc.
3. Modify localhost database dump
3.1 wp_options table
3.2 append usernames
3.3 Fix table create commands
4. Drop all tables from virgin ISP installationOnce you have a SQL dump of the virgin ISP installation, and you have the localhost WordPress dump file modified, you have one more step to do before importing it into the ISP server. You have to get rid of the existing ISP WordPress database so when the text file tries to create new tables using the SQL language, you won't get "table already exists" errors.
Use phpMySQL on the ISP to drop all tables from the database.