After installing my eighth Drupal 5 site this afternoon, I came to the sudden realization that I now have older sites with all the configuration and modules exactly as I need them. Why can't I reuse one of those sites' config options and settings for a brand new install? It would save hours of downloading all those modules again, installing them, and configuring all the minuscule settings till they're just right.

Well, it turns out that I can, in fact, do just that. Let me show you how!

[update on 2009/01/05] There's now a module that handles this exact procedure. http://drupal.org/project/delete_all

Moving The Site

Step One: Prepare The Old Site

1. Turn off the cache. Get your old site ready for the migration by disabling the site-wide cache at Administer > Site Configuration > Performance
2. Use the Empty Cache button in the Devel module to empty all your cache tables.
3. Run cron.php to clean up the logs and other laundry items.

Step Two: Make a Copy of The Old Site

Using MySQLadministrator, make a backup copy of the originating site's tables. I'll show you what settings to use in my video tutorial on drupaldojo.com/lesson/22 if you're interested. PHPmyAdmin or the command line would accomplish the same thing if you'd rather go that route. You want to end up with a text file who's extension is .sql and saved as UTF-8.

Once you've got the old site's database saved onto your computer, open it up with your favorite text editor and run Find & Replace on the name of the old database, changing it to the name of the new database. Here's what my old_drupal_database.sql file looked like:


-- MySQL Administrator dump 1.4
-- ------------------------------------------------------
-- Server version 5.0.30-Debian_3

CREATE TABLE `myOldSiteDatabaseName`.`drupal_access` (
`aid` int(11) NOT NULL auto_increment,
`mask` varchar(255) NOT NULL default '',
`type` varchar(255) NOT NULL default '',
`status` tinyint(4) NOT NULL default '',
PRIMARY KEY (`aid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


Which I then changed into:


CREATE DATABASE IF NOT EXISTS myNEWsiteDatabaseName;
USE myNEWsiteDatabaseName;

CREATE TABLE `myNEWsiteDatabaseName`.`drupal_access` (
`aid` int(11) NOT NULL auto_increment,
`mask` varchar(255) NOT NULL default '',
`type` varchar(255) NOT NULL default '',
`status` tinyint(4) NOT NULL default '',
PRIMARY KEY (`aid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


Performing a simple Find & Replace for myOldSiteDatabaseName and changing all instances of that to myNEWsiteDatabaseName will be sufficient to create a new database for your new site. There's no need to do it line-by-line, just click the Replace All button. You'll notice that I use "_drupal" table prefixes in this database, as I do for all my sites. Don't even worry about those prefixes, there's no way for the find/replace to mess anything up. My server automatically creates an identical MySQL user for each new website account that I create. It also creates a database whose name is identical to that account's username. Your mileage may vary.

Save the edited file into the new website's directory somewhere, in preparation for the next steps.

Step Three: Upload an Identical Version of Drupal Codebase

You cannot change Drupal versions while executing this re-use procedure, so don't even attempt it. This tutorial is already a shortcut. DO NOT layer shortcuts in web development. That being said, upload it to the same relative directory of your new site as the old site resided in. You can't change from having Drupal installed in /drupal/ on your old site, to / on your new site. Well, you could, but you'd have to know a lot more about the tables in these databases than I do, and if that's the case, you can stop reading this tutorial right... about... here.

Step Four: Install Your Copied Database

Using MySQLadministrator again, upload the edited copy of your .sql file to the MySQL server. True, there are many ways to go about this, but since I'm working on a remote VPS server for purposes of this tutorial, and I prefer GUI apps, you'll just have to put up with me. You may extrapolate a faster or better method than what I show you here, but that just means you understood what I was trying to say. :=)

A word of encouragement here: You won't overwrite any existing database tables belonging to another app with this method. Remember, we turned off the 'DROP table if EXISTS' feature in MySQLadministrator. We also have table prefixes for everything drupal_ related, so it's not a problem to upload this file into a database that already has some PHPlist, Gallery2, or what-have-you in it. Drupal tables play nicely with the other kids.

Configure Your New Site

Step Five: Change The settings.php File

In your new site, Change the username/password to match your new site's database login settings. Also change the $base_url and any variable override you might have set. Add a variable override to change the theme to Garland. You'll want this, especially if you were using a custom theme before. Don't worry, you can change back later once you know you can login to your new site.

$conf = array(
'theme_default' => 'garland',
);


Step Six: Reset The Variables

Now it starts to get tricky. You'll need PHPmyAdmin or a command line, because MySQLadministrator is not yet full-fledged enough to make edits to fields in a table. Maybe someday an aspiring coder will write these commands into a Drupal module and save us all the trouble, but here goes!

Open the database, and move to the drupal_variable table. I've pasted the PHPmyAdmin SELECT commands for you, so you shouldn't have any trouble finding these rows. You're looking to change the following variables:

• site_name
  SELECT *
FROM `drupal_variable`
WHERE `name` LIKE CONVERT( _utf8 'site_name'
USING utf8 )
• site_slogan You could change this in the admin interface of the site, but since you're already here...
  SELECT *
FROM `drupal_variable`
WHERE `name` LIKE CONVERT( _utf8 'site_slogan'
USING utf8 )
• site_mission Same thing with this variable. It could also be changed in the admin section of the site.
  SELECT *
FROM `drupal_variable`
WHERE `name` LIKE CONVERT( _utf8 'site_mission'
USING utf8 )
• site_mail I list this one because you're likely to forget it.
  SELECT *
FROM `drupal_variable`
WHERE `name` LIKE CONVERT( _utf8 'site_mail'
USING utf8 )
• theme_default Remember that theme names are always lowercase. I recommend using garland, at least for now.
  SELECT *
FROM `drupal_variable`
WHERE `name` LIKE CONVERT( _utf8 'theme_default'
USING utf8 )

Once you've selected, changed, and saved these sitewide variables, it's time to nuke the old site's content. Be careful though. Hands off that big red button!

Step Six: Nuke The Old Content

All of the content for a site resides in two places. The drupal_node table and the drupal_node_revisions table. (Remember that I'm using table prefixes. Your site might just be node and node_revisions). I use the Truncate command for this procedure because it empties a table of all rows, but leaves the structure of the Drupal table intact so I can refill it with new content later.

1. Delete all content in the drupal_node table:
   
TRUNCATE TABLE `drupal_node`;

2. Delete all content in the drupal_node_revisions table:
   
TRUNCATE TABLE `drupal_node_revisions`;


UPDATE on 07.26.2007: An array that lists all the tables that need to be emptied would be a good thing. Something like:

/* Make an array out of all the table names that need emptying */
$tables-to-empty = array(
'node',
'node_revisions',
'files',
'file_revisions',
'comments',
'node_comment_statistics',
'flood',
'search_dataset',
'sessions',
'url_aliases'
'DELETE FROM {users} WHERE uid > 2' => 'users',
);


07.25.2007 -- Steps that still need more explanation

I'll cover the changes to /files dir and the /temp dir when I can. There's a way to set up your site so you don't have to worry about re-creating these two directories each time you do this Recyclicon procedure.

Change your contact form information. If you forget this step, everyone will laugh at you behind your back. Or possibly even in front of your back.