2

After searching SO, I found answers to the following:

  1. How to copy an entire MySQL schema using mysqldump
  2. How to copy an entire MySQL schema using PHP
  3. How to copy an entire MySQL schema using the enterprise edition of MySQL
  4. How to copy an entire Microsoft SQL Server schema using the menus.
  5. I also found a few hints about copying a MySQL schema using SQL commands.

My question: If I use the following SQL commands to copy a MySQL schema, what parts of the old schema would not be copied? Indexes? Constraints? Views? Anything else?

CREATE SCHEMA new_schema DEFAULT CHARACTER SET utf8;

CREATE TABLE new_schema.table1 LIKE old_schema.table1;
CREATE TABLE new_schema.table2 LIKE old_schema.table2;
CREATE TABLE new_schema.table3 LIKE old_schema.table3;
...;

INSERT INTO new_schema.table1 SELECT * FROM old_schema.table1;
INSERT INTO new_schema.table2 SELECT * FROM old_schema.table2;
INSERT INTO new_schema.table3 SELECT * FROM old_schema.table3;
...;
Improve this question
4
  • take a dump of old database and then import them in other mysql server. Commented Jun 11, 2014 at 17:46
  • 1
    Why are you reinventing the wheel? Mysqldump is really what you want for this. Did you find it lacking in some way? Commented Jun 11, 2014 at 17:52
  • @dpw, a common reason is that he doesn't have privilege to run mysqldump or any other child process in his hosted environment, and he wants a "pure PHP" way to do something equivalent without needing to fork a child process. Commented Jun 11, 2014 at 17:53
  • @BillKarwin, that's a legit reason I guess, but if that's the case then I wonder how the answer to point #2 "How to copy an entire MySQL schema using PHP" was insufficient. Commented Jun 11, 2014 at 18:03

2 Answers 2

Reset to default
8

The CREATE TABLE ... LIKE will take care of indexes and constraints.

You should take care to SET FOREIGN_KEY_CHECKS=0 while you run this, because if table1 has a foreign key to table2, then creating table1 will fail. Likewise inserting data into the tables in the wrong order will fail.

Your script does not cover:

  • Views
  • Triggers
  • Stored procedures
  • Stored functions
  • Events

There are no CREATE... LIKE... statements for these other objects. You'll have to use SHOW CREATE... and then run it against in the context of the new schema. See the various SHOW CREATE... statements here: http://dev.mysql.com/doc/refman/5.6/en/show.html

I also caution that the way you INSERT INTO... SELECT FROM... will work, but can fill up your rollback segment if the table is very large. Tools like pt-archiver try to copy tables in batches, ascending along the primary key, to avoid this problem.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

I think routines can't be copied directly with sql commands (as far as I know there's not such anything like create procedure myProc like old.myProc).

I would recommend you use mysqldump, since it takes care of copying everything, including the data (if you don't want to copy the data, you can use the -d switch to prevent creating the insert statements).

If you want to create a "template" (a database that is exactly like another database, but without the data), you can use the following:

mysqldump [connectionParameters] -d -R -v yourOldDatabase > databaseTemplate.sql

The options explained:

  • [connectionParameters]: host, user and password
  • -d: Don't copy data
  • -R: Include routines in the dump
  • -v: Output what mysqldump is doing to the console

You can open this "light" sql script to check how the objects were created.

Hope this helps

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.