On October 6, 2007 … I documented How To Convert 20 Domains with Blogs into 3 Domains with Subdomains – in Six Hours. This is where I left off with everything:

STEP FIFTEEN: A Final Thought about Addon Domains

When everything is done and upgraded .. I have two accounts on my server. Naturally, it will be the new subdomain blog that I will be making upgrades, new plugins, theme modifications .. but it is still a duplicate copy of the files in the original domain blog. You have to remember two things now:

1) both accounts are sharing the same MySQL database where all the information is.
2) if you delete the old cPanel account, the MySQL database will automatically be deleted and there is nothing you can do about it!

I’m setting up “Add-on Domains” to each subdomain, and associating the old domain cPanel accounts to the subdomain. This should help me to NOT accidentally delete the entire blog in a blink.

WHAT WAS I THINKING?

The OLD Plan

New blog in Subdomain of new account uses MySQL database associated with old account, attached to the new account as an ADD-ON domain, to prevent deletion.

The NEW Plan

I’m just going to make the database associate with the new account. While I’ll still need the .htaccess file in the old account, eventually .. there should be no risk whatsoever to my existing account (with subdomains blogs) if I decide NOT to renew registration of the old “battling-series” of domains and just delete it once and for good. I honestly don’t foresee people linking to these blogs in a year from now. Maybe they will! But, I’ve got until September 2008 to decide that fate. All blog domain renewals have been extended.

So How Did I do This?

I am going to give you a step by step instruction plan how I will have converted my next blog. Out of 20 blogs or so, I have moved 6 blogs’ databases only (doesn’t sound like much) but I’ve now got the pattern down pat.

* WORKING EXAMPLE AS I DO THIS:

Given:
FORMER BLOG: Battling-Addiction.com (cPanel = addiction account)
NEW BLOG: addiction.BattlingForHealth.com (cPanel = health account with subdomain addiction)
OLD MySQL: blog settings are reading database addiction_addiction
NEW MySQL: Want settings so database is at health_addiction

Step 1 – Create New MySQL database on New Account

In the Health account, cPanel .. I created a MySQL database called addiction and associated it with a username and gave it all the necessary permissions

Step 2 – Backup The Databases – to local computer

Basically, I follow the wordpress codex guide to backing up the databases *NOT* a backup created by the wordpress plugin. Although, if I ever have to restore a backup created by the wordpress plugin, I hope everything is okay. Anyway, this is how to backup your MySQL Database.

* Log into the OLD account’s cPanel account, in this case the “addiction” account – that’s where the MySQL database is now
* Click phpMyAdmin in the database section
* Click the name of the database in the left window (addiction_addiction) or “Databases” in the main window
* Click EXPORT from the main window
* In the STRUCTURE Section … there should be ONLY 3 ticked items:
(i) Add DROP TABLE
(ii) Add AUTO_INCREMENT value
(iii) Enclose table and field names with backquotes
* In the DATA Section .. there should be NO Ticked items (untick ‘Use hexadecimal for BLOB’)
* CLICK so “SAVE AS FILE” Section is selected, but leave the filename alone (__DB__)
* Click “GO” // download and save target on local computer

Step 3 – Restore The Backup Into The New Database

Basically I follow the wordpress codex guide to restoring a database safely, and followed these directions.

* Log into the cPanel of the new account, in this case the “health” account
* Click on phpMyAdmin in the datebase section of the cPanel
* Now, I’ve already got seven databases created (6 transferred subdomains and the main domain) .. I click on the newly created, and empty database “health_addiction”
* Click on IMPORT from the top menu in the main window
* In the first “File To Import” Section, I browse to the backup I just made and select it
* The “Format of Imported File” section should already be ticked “SQL”, with NONE for compatibility mode
* Click GO!

You should receive a ‘success’ message .. like .. Import has been successfully finished, 39842 queries executed. On one of my attempts this past weekend, I received an error message and it did not fully import. What I did was go back into the old database, and DE-SELECT all the Firestats tables (which caused major F/Ups on the conversion to wordpress 2.2.3 that I’ll never use it anyway. After that, the import went smoothly.

Now Comes The Fun Part – Renaming the URL’s

On October 6th, when I moved the old domains into the new subdomain position, I moved all of the actual files that make up wordpress, my themes, my plugins, etc – from the old account to the new account. Over the past week, I have exported the old MySQL databases (which I was accessing from the new account) and imported into a brand new MySQL database associated with the new account.

However – all my images and links in each post created on the old account, still refer to the old account! For instance, if I were to upload pictures on a post .. it would be included in the blog as Battling-Addiction.com/wp-content/uploads/picture.jpg – I want to rename that to be addiction.BattlingForHealth.com/wp-content/uploads/picture.jpg

Likewise, with all my google alerts that I created .. because of all of the spammers and scammers trying to steal my RSS Feeds and replublish them on their own sites with advertising (and most giving me back credit and linking back to the original post) .. what I was doing was embedding a link inside most posts so if someone were doing that .. I would get a google alert notice immediately. For instance, I’d might link the phrase “stop smoking” to Battling-Addiction.com/category/nicotine-addiction .. and now I want to rename it to addiction.BattlingForHealth.com/category/nicotine-addiction

Step 4 – Renaming OLD-(star)(dot)(star) to NEW-(star)(dot)(star)

Basically, I was following the wordpress codex for renaming accounts in your MySQL database.

In a nutshell – there is one table in the database that needs to be fixed, with two fields where the text could be. However, in the possibility that I might have mixed up the old account to include the “WWW” in the URL, I will repeat the procedure twice … so I catch everything … the Battling-Addiction.com/etc stuff and the www.Battling-Addiction.com/etc stuff.

* In the New Database still on screen, in phpMyAdmin program … click on the left window the table called “wp_posts” (or, in my case – “battling_addiction_posts” because I never use the wordpress default “wp_” in my wp-config.php files)
* Click BROWSE from the top menu
* scroll down the page and you should note that there are two fields, at the top of the tables that may have the URL of the old account ..
(i) post_content
(ii) guid
* Scroll back up and on the line where the Browse was, click the “SQL” button from the top header .. you should see a text area box with the title “Run SQL query/queries on database health_addiction:” or something like that .. with text inside … DELETE the text inside that box
* Copy the following 4 commands EXACTLY except you would insert your own URL’s but you get the gist of it. Each command is separate. You copy the first command, then hit GO. Repeat


UPDATE battling_addiction_posts SET guid = REPLACE (
guid,
'http://battling-addiction.com',
'http://addiction.battlingforhealth.com');

– Click GO


UPDATE battling_addiction_posts SET post_content = REPLACE (
post_content,
'http://battling-addiction.com',
'http://addiction.battlingforhealth.com');

– Click GO


UPDATE battling_addiction_posts SET guid = REPLACE (
guid,
'http://www.battling-addiction.com',
'http://addiction.battlingforhealth.com');

– Click GO


UPDATE battling_addiction_posts SET post_content = REPLACE (
post_content,
'http://www.battling-addiction.com',
'http://addiction.battlingforhealth.com');

– Click GO

You should see a success message, like .. Affected rows: 178 (Query took 0.0284 sec)

And That’s How You Rename All The Data In Your MySQL Database!

At this very moment – because was performing my 7th MOVING of the databases .. Everything should work now – except for two final steps

Step 5 – Change the variables in your wp-config.php file

Using my FTP program, basically I copied the existing wp-config.php file over from the server to my local computer and then renamed it wp-config.php.battling-addiction .. so I wouldn’t overwrite it – just in case there was future problems – in the immediate future that is. Then, I copied over the wp-config.php file again .. and edited the following variable ..

// ** MySQL settings ** //
define(‘DB_NAME’, ‘addiction_addiction’); // The name of the database

to:

// ** MySQL settings ** //
define(‘DB_NAME’, ‘health_addiction’); // The name of the database

All other variables stayed the same (if you created it that way .. I modified the username and password actually and changed those too :D)

Step 6 – Check Data and Update Your Permalinks

addiction.BattlingForHealth.com … success! The new blog is working perfectly .. but, just in case, I logged into the dashboard/OPTIONS/PERMALINKS and updated them .. before I did anything else.

At this very moment, I now have 7 moved databases, and 13 more to go. I am in no rush, and plan to do this over the next two weeks .. one blog at a time.