How to duplicate a schema in mysql database

Question

Target

Duplicate a schema in mysql database, without dumping sql files and execute them.

It would be great if there is a way to accomplish this task in a few commands.

Background

There are ways to dump an database and import it by executing the sqls generated by an dump program.

But what I would like to do is to execute an command like copy schema abc to abc_bk, and done.

Because I have to do it through phpmyadmin and dumping all fields and re-import them is too time consuming.

Answer 1

There is a statement:

CREATE TABLE <name> LIKE <othername>;

But you would have to do this one table at a time. There is no single statement that does this for all the tables in a schema.

You can get a list of tables from INFORMATION_SCHEMA.TABLES:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = <old-schema>;

Then for each table returned by that query, run the statement:

CREATE TABLE <new-schema>.<table-name> LIKE <old-schema>.<table-name>;

You must write some code to perform the loop to do this.

---

Re your comment:

If you want to copy data as well, you can use INSERT ... SELECT.

INSERT INTO <new-schema>.<table-name> 
 SELECT * FROM <old-schema>.<table-name>;

This will take a while, proportional to the number of rows and number of indexes it needs to update in your table (that is, the cost of writing each row is multiplied by the number of indexes).

Note this does lock the rows it reads from your old table while the INSERT...SELECT is running. If you need to continue writing to the old table while this is going, I would recommend using pt-archiver or pt-table-sync to do the copying.