Power By 专否社区

Prepend table names on MySQL database export

Go To StackoverFlow.com

1

Is it possible to prepend some text to all tables when exporting a mysql dump.

For example: if the table is named chapters I want to prepend old_ to it so it becomes old_chapters

2012-04-04 17:19
by kylex
Can you not just do a search and replace on the dump file - liquorvicar 2012-04-04 17:22
The dump in question also includes content and is quite large (it stalls most text editors). Also the terms exist in content as well, which makes a simple replace quite tricky - kylex 2012-04-04 17:27
How about copying the relevant table and then ignoring the original table in the dump - liquorvicar 2012-04-04 17:42
Should be a duplicate of this: http://stackoverflow.com/questions/3326581/how-to-import-a-mysql-dump-while-renaming-some-tables-columns-and-not-importin - Churk 2012-04-04 17:53

0

Depending on the complexity of your schema, you may be able to accomplish this with a carefully crafted sed command that finds all of the table references and prepends the table names with "old_".

For example, this command will dump your schema and data and add the old_ prefix to the drop/create table statements as well as the lock/alter table and insert statements:

mysqldump your_schema | sed -e 's/DROP TABLE IF EXISTS `/DROP TABLE IF EXISTS `old_/' -e 's/CREATE TABLE `/CREATE TABLE `old_/' -e 's/INSERT INTO `/INSERT INTO `old_/' -e 's/LOCK TABLES `/LOCK TABLES `old_/' -e 's/ALTER TABLE `/ALTER TABLE `old_/'

However, that command will not add the table prefix for any of the following types of table references:

  • Foreign Key constraints
  • Views
  • Triggers
  • Stored routines
2012-04-04 20:11
by Ike Walker
Ads