So it turns out that not as many folks who care about MySQL and Ruby on Rails want to hear about drinking sparks and snowboarding. Wtf people? Anyhoo, now I have two blogs for your enjoyment.

• Snow Giraffe Tech – this tech talk blog
• The Snow Giraffe Blogs my personal funtime blog about the snow gnar, the sparks, people I like, and general awesome

If you care to know the intimate details of how I duplicated and split and retained all my old relevant posts with Wordpress and Dreambutt (ok, yes I have been referring to Dreamhost by a better name for way too long), read on about my SQL poweress. (Also, I have always wanted to blog about blogging.)

How to split or duplicate your wordpress blog

First, I hate IT. I am not an IT expert. I can move files around and write queries and begrudgingly edit php files. This is dangerous probably, but hey it worked for me.

The examples assume the following data for the new and old blogs. It assumes that your current directory is

/blah/blah/www

Original blog

url: host.com/blog  
directory on server: /blah/blah/www/blog
database: wordpress_blog
database host: blog.myhost.com

New blog

url: host.com/blog
directory on server: /blah/blah/www/new_blog
database: wordpress_new_blog
database host: blog.myhost.com

Upgrade your existing blog

Upgrade your blog to which ever you want to use for the new one.

Install Wordpress in a new directory

I am using dreambutt, so I used the one-click installer (Goodies -> One Click Installer) to install a new blog to my directory. If you don’t have such luxuries, than you can probably figure out how to install wordpress.

Some guidelines:

• Use a new directory
• Use a new database (okay you can reuse the old but be careful)
• Host can be shared

Dump and replace your old database

• Dump your old database (wordpress_blog).
• Update references to host.com/blog to host.com/new_blog
• Update references to server directories: /blah/blah/www/blog to /blah/blah/www/new_blog
• Import into your new blog database (wordpress_new_blog)

mysqldump -uusername -ppassword -hblog.hyhost.com wordpress_blog > wordpress.sql
sed 's/host\.com\/blog/host\.com\/new_blog/g' wordpress.sql > wordpress_new1.sql
sed 's/www\/blog\/wp/www\/new_blog\/wp/g' wordpress_new1.sql > wordpress_new2.sql
mysql -uusername -ppassword -hblog.myhost.com wordpress_new_blog < wordpress_new2.sql

If you don’t feel comfortable updating the sql before importing it. Skip the SED tasks. However, make sure that you update the columns with the following option_names in the options table:

+--------------------------------------+
| option_name                          |
+--------------------------------------+
| siteurl                              |
| home                                 |
| recently_edited                      |
| upload_path                          |
| dashboard_widget_options             |
+--------------------------------------+

Backup and copy the new blog

Backup the new blog then recursively copy the old to the new directory.

mv /blah/blah/www/new_blog /blah/blah/www/new_blog.new
cp -r /blah/blah/www/blog /blah/blah/www/new_blog

Change the configuration

Change new_blog/wp-config.php to point to the new database. You might want to open it up and change other details as well.

// ** MySQL settings ** //
define('DB_NAME', 'wordpress_new_blog');     // The name of the database

I ran this real quick:

sed 's/wordpress_blog/wordpress_new_blog/'  new_blog/wp-config.php > new_wp-config.php
cp new_wp-config.php new_blog/wp-config.php

Login to the new blog

You should be able to login on host.com/new_blog with the same credentials you used on the original blog. The styles, posts, etc should be exactly the same. You probably want to:

• Delete (in bulk) and posts and media that you no longer want.
• Update the blog name, settings, etc particular to your new blog.

Backup and copy the new blog

Cleanup your mess.

• Remove temporary files we created

rm -r wordpress*.sql
rm new_wp-config.php

• Remove backup directoried (if desired)
• Remove old media files either directly on the server or log into the new blog
• Repeat for the original blog if necessary

Thats it!

If you have any troubles, examine the new_blog/wp-config.php file.

Connect to the new database and make sure references to the old blog are removed from the options table.

If at any point you mess up, drop the database, revert to the original (temp.orig) and start again.

Filed under:
Coding and Computers, MySQL by blythe