Renaming MySQL tables and databases

Renaming MySQL tables and databases Organic Design procedure

Duplicating a database

This example duplicates a database called "DB_A" to "DB_B", to rename you'd also drop the original database after successfully duplicating it.

First log in to MySQL and create a new empty database for the target name

<mysql>CREATE DATABASE DB_B;</mysql>

Then quit from MySQL and export the source database to a temporary file, and import it into the new database. I prefer to do this as two separate commands so that we can use interactive passwords and avoid them being written to history.

<bash>mysqldump -u DBUSER -p DB_A > tmp mysql -u DBUSER -p DB_B < tmp rm tmp</bash>

Renaming tables

Renaming one or two tables is simple, just log in to SQL and use the following syntax:

<mysql>RENAME TABLE tbl_from_1 TO tbl_to_1, tbl_from_2 TO tbl_to_2;</mysql>

But usually we need to rename forty or fifty tables because we're usually changing the table-prefix of all the tables in an application like MediaWiki or Wordpress. To do this we can output the list of tables we want to change with SHOW TABLES, and then use a text editor (one that has regular expressions like Geany) to reformat the list into a RENAME TABLE command that we'll paste back into the MySQL command line. First log in to MySQL, select the database containing the tables and list them as follows (in this example we're going to change a prefix from foo_ to bar_.

<mysql>USE DB_A SHOW TABLES LIKE foo_%;</mysql>

Then select and copy the table list and paste into your text editor, you should have something like the following (I've omitted most tables to save space).

| foo_archive | | foo_category | | foo_categorylinks | | foo_change_tag | | foo_external_user | | foo_externallinks | | foo_filearchive | | foo_hitcounter | | foo_image | | foo_imagelinks | | foo_interwiki | | foo_ipblocks | | foo_iwlinks | | foo_job | | foo_l10n_cache | | foo_langlinks | | foo_log_search | | foo_logging | | foo_module_deps |

Then you can use the following regular expression to convert the list into the appropriate format.

The search expression is:

^[ |]+(foo_(\w+))[ |]+$

and the replacement expression is:

\1 TO bar_\2

Now you should have the following text in the editor:

foo_archive TO bar_archive foo_category TO bar_category foo_categorylinks TO bar_categorylinks foo_change_tag TO bar_change_tag foo_external_user TO bar_external_user foo_externallinks TO bar_externallinks foo_filearchive TO bar_filearchive foo_hitcounter TO bar_hitcounter foo_image TO bar_image foo_imagelinks TO bar_imagelinks foo_interwiki TO bar_interwiki foo_ipblocks TO bar_ipblocks foo_iwlinks TO bar_iwlinks foo_job TO bar_job foo_l10n_cache TO bar_l10n_cache foo_langlinks TO bar_langlinks foo_log_search TO bar_log_search foo_logging TO bar_logging foo_module_deps TO bar_module_deps

Then it's a simple matter to replace the newline characters (\n) with commas (,) and prepend the whole thing with RENAME TABLE. Make sure you have a semicolon (;) and no comma on the end of the list.

<mysql>RENAME TABLE foo_archive TO bar_archive,foo_category TO bar_category,foo_categorylinks TO bar_categorylinks, foo_change_tag TO bar_change_tag,foo_external_user TO bar_external_user,foo_externallinks TO bar_externallinks, foo_filearchive TO bar_filearchive,foo_hitcounter TO bar_hitcounter,foo_image TO bar_image,foo_imagelinks TO bar_imagelinks, foo_interwiki TO bar_interwiki,foo_ipblocks TO bar_ipblocks,foo_iwlinks TO bar_iwlinks,foo_job TO bar_job, foo_l10n_cache TO bar_l10n_cache,foo_langlinks TO bar_langlinks,foo_log_search TO bar_log_search,foo_logging TO bar_logging, foo_module_deps TO bar_module_deps;</mysql>

You can then paste this whole command into the MySQL console to do the renaming in one go.