Moving a Drupal install from a prefixed database to a non-prefixed one

One of Drupal's features is that it allows database prefixes, so people can host more than one site in a single database, or share the database with other application programs.

As a site grows, people find the need to separate those sites into their own databases, making the prefix unnecessary.

To do that, the following steps are needed:

Backup the existing database

In order to start this process, you have to perform a dump of the database. This is needed for two purposes, one is to have a backup of all your data, and the second is to be able to copy it to the other database, which may be on another host altogether.

This can be done from the PHPMyAdmin web interface or mysqldump from the command line.

Load the database dump

Next, you need to load the dump you created in the new database. Again, use PHPMyAdmin or the mysql command.

Renaming the tables 

You now need to rename the tables so they do not have prefixes. In order to do that, enter the following SQL statements for each table: 

rename table pr_node to node;
rename table pr_node_access to node_access;
... 

 Fixing the sequences table

The tricky part is that for some tables, there is a sequence number used to assign keys to new rows.

In order to see what the sequences are, enter the following SQL command: 

select * from sequences;

The output will be something like:

+--------------------------+-----+
| name                     | id  |
+--------------------------+-----+
| pr_users_uid             |   7 |
| pr_node_nid              | 403 |
| pr_vocabulary_vid        |   1 |
| pr_term_data_tid         |  57 |
| pr_filter_formats_format |   3 |
| pr_menu_mid              | 136 |
| banner                   |   2 |
| pr_files_fid             |   1 |
| pr_comments_cid          |   1 |
+--------------------------+-----+

As you can see most table will have prefixes in them. We need to recreate the data without prefixes.

This can be done in either one of two ways.

The first way is to delete all the data, and insert new rows without prefixes. For example: 

delete from sequences;
insert into sequences values ('users_uid', 7);
insert into sequences values ('node_nid', 403);
insert into sequences values ('vocabulary_vid', 1);
insert into sequences values ('term_data_tid', 57);
insert into sequences values ('filter_formats_format', 3);
insert into sequences values ('menu_mid', 136);
insert into sequences values ('banner', 2);
insert into sequences values ('files_fid', 1);
insert into sequences values ('comments_cid', 1);

The other way can be done via an UPDATE statement for each row. This is left as an exercise to the reader ...

Modifying the settings.php file

Now, all that remains is to modify the settings.php file to remove the prefix, and set the new database parameters. Change the $db_prefix to remove the prefix you had, and set the appropriate parameters in $db_url as needed.