Transforming a default table names installation into a prefix table names installation

This operation may be useful in many cases, expecially if you need to move your site from an host providing a dedicated drupal installation database to a shared database with all data in one database.

1) Log off your Drupal site, back up the database, the risk breaking your data is definitely here.

2) Export the database to text file. You can use i.e. phpMyAdmin Export function with the following options: "SQL" (as format to use). Be sure to check Structure (and "Add AUTO_INCREMENT value" in it) and Data (and "Use exadecimal for binary fields"). Don't use compression settings, you want a plain file.

3) Decide the prefix for the tables. I.e. all tables name changed from "xxxxx" to "dpl_xxxxx".

4) Open the text dump created, search and replace every

CREATE TABLE '

with

CREATE TABLE 'dpl_

and

INSERT INTO '

with

INSERT INTO 'dpl_

where dpl_ is any SQL valid prefix you want to use.

5) Save the dump

6) Drop the current database (if you are reinstalling on same machine, else skip)

7) Create the database in the destination machine. I give "utf8_unicode_ci" as default collation. From now on everything is done on the destination computer.

8) Import the saved dump there.

9) Open a SQL prompt / window / whatever (i.e. "SQL" option in phpMyAdmin).

10) At the SQL prompt enter:

update dpl_sequences set name = concat('dpl_', name)

Of course if you use something different than 'dpl_', you have to apply such prefix both in front of "sequences" and in the concat operator. Example, if you used "my_stuff_" it'd become:

update my_stuff_sequences set name = concat('my_stuff_', name)

Beware, the prefix must be the same in the whole process or everything will break. I'd really suggest keeping case consistency too.

11) Open the settings.php, usually it'll reside in the /sites/default folder. If you have a multi-site installation you have to find the relevant php settings file in the affected domain folder.

12) If you changed database name / server, search for:

$db_url = 'server://path/database_name';

and alter it as per the instructions reported above that line.

13) Search for:

$db_prefix = '';

(should be right next of the above $db_url statement) and put the new prefix inside the ' '.

Example:

$db_prefix = 'dpl_';

14) Launch the home page of the destination installation. If everything shows up, you are at a good point. Else you have to double check what step did you miss.

15) Log in as administrator and create a quick page and a story. If you see green and encouraging "page / story added" you are done. If you get "Warning: duplicate key / ID" whatever, you skipped something about prefixes and you have to drop the database and redo from point 6.