Difference between revisions of "Renaming MySQL tables and databases"

From Organic Design wiki
m (→‎Renaming tables)
(Change source-code blocks to standard format)
 
(5 intermediate revisions by one other user not shown)
Line 1: Line 1:
 
{{procedure}}
 
{{procedure}}
 +
 +
{{h|Note that you should back up your databases before doing these procedures as anything typed incorrectly could result in lost or corrupted data!}}
  
 
== Duplicating a database ==
 
== Duplicating a database ==
Line 5: Line 7:
  
 
First log in to MySQL and create a new empty database for the target name
 
First log in to MySQL and create a new empty database for the target name
−
{{code|<sql>CREATE DATABASE DB_B;</sql>}}
+
<source lang="mysql">
 +
CREATE DATABASE DB_B;
 +
</source>
  
  
 
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.
 
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.
−
{{code|<bash>mysqldump -u DBUSER -p DB_A > tmp
+
<source lang="bash">
 +
mysqldump -u DBUSER -p DB_A > tmp
 
mysql -u DBUSER -p DB_B < tmp
 
mysql -u DBUSER -p DB_B < tmp
−
rm tmp</bash>}}
+
rm tmp
 +
</source>
  
 
== Renaming tables ==
 
== Renaming tables ==
 
Renaming one or two tables is simple, just log in to SQL and use the following syntax:
 
Renaming one or two tables is simple, just log in to SQL and use the following syntax:
−
{{code|<sql>RENAME TABLE tbl_from_1 TO tbl_to_1, tbl_from_2 TO tbl_to_2;</sql>}}
+
<source lang="mysql">
 +
RENAME TABLE tbl_from_1 TO tbl_to_1, tbl_from_2 TO tbl_to_2;
 +
</source>
  
  
 
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_''.
 
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_''.
−
{{code|<mysql>USE DB_A
+
<source lang="mysql">
−
SHOW TABLES LIKE foo_%;</mysql>}}
+
USE DB_A
 +
SHOW TABLES LIKE foo_%;
 +
</source>
  
  
−
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).
+
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). Note that you only want the lines that have a table name pasted into the editor, not the table headings or separator/closing bars.
−
{{code|<pre>| foo_archive            |
+
<source>
 +
| foo_archive            |
 
| foo_category          |
 
| foo_category          |
 
| foo_categorylinks      |
 
| foo_categorylinks      |
Line 42: Line 53:
 
| foo_log_search        |
 
| foo_log_search        |
 
| foo_logging            |
 
| foo_logging            |
−
| foo_module_deps        |</pre>}}
+
| foo_module_deps        |
 +
</source>
  
  
−
Then you can use the following regular expression to convert the list into the appropriate format.
+
Then you can use the following regular expression (i.e. make sure "use regular expressions" is ticked) to convert the list into the appropriate format.
  
 
The search expression is:
 
The search expression is:
−
{{code|<pre>^[ |]+(foo_(\w+))[ |]+$</pre>}}
+
<source>
 +
^[ |]+(foo_(\w+))[ |]+$
 +
</source>
  
  
 
and the replacement expression is:
 
and the replacement expression is:
−
{{code|<pre>\1 TO bar_\2</pre>}}
+
<source>
 +
\1 TO bar_\2
 +
</source>
 +
*Depending on your editor, you may need to use ''$1'' and ''$2'' or ''\\1'', ''\\2'' etc, check your manual, this is written for [[Geany]] users.
  
  
 
Now you should have the following text in the editor:
 
Now you should have the following text in the editor:
−
{{code|<pre>foo_archive TO bar_archive
+
<source>
 +
foo_archive TO bar_archive
 
foo_category TO bar_category
 
foo_category TO bar_category
 
foo_categorylinks TO bar_categorylinks
 
foo_categorylinks TO bar_categorylinks
Line 74: Line 92:
 
foo_log_search TO bar_log_search
 
foo_log_search TO bar_log_search
 
foo_logging TO bar_logging
 
foo_logging TO bar_logging
−
foo_module_deps TO bar_module_deps</pre>}}
+
foo_module_deps TO bar_module_deps
 +
</source>
 +
 
 +
 
 +
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.
 +
<source lang="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;
 +
</source>
 +
 
 +
 
 +
You can then paste this whole command into the MySQL console to do the renaming in one go.

Latest revision as of 18:11, 22 May 2015

Procedure.svg Renaming MySQL tables and databases
Organic Design procedure

Note that you should back up your databases before doing these procedures as anything typed incorrectly could result in lost or corrupted data!

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 

CREATE DATABASE DB_B;


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. 

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

Renaming tables

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

RENAME TABLE tbl_from_1 TO tbl_to_1, tbl_from_2 TO tbl_to_2;


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_. 

USE DB_A
SHOW TABLES LIKE foo_%;


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). Note that you only want the lines that have a table name pasted into the editor, not the table headings or separator/closing bars. 

| 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 (i.e. make sure "use regular expressions" is ticked) to convert the list into the appropriate format.

The search expression is: 

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


and the replacement expression is: 

\1 TO bar_\2
  • Depending on your editor, you may need to use $1 and $2 or \\1, \\2 etc, check your manual, this is written for Geany users.


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. 

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;


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

Retrieved from "https://organicdesign.nz/wiki/index.php?title=Renaming_MySQL_tables_and_databases&oldid=119900"