A (not so) small rant on techniques to copy MySQL database structures

And here I am, facing a simple task of duplicating the original company’s database into a test db, so we can, uh, run tests.

Just a matter of mysqldump’ing it, you would guess.
Some rare folks would even point to CREATE TABLE x2 LIKE x1. Very clever.

But MySQL is not here for you. He’s quite a respectable but buggy guy, in the end. I’m writing this small article, to sum up all the resources I found along this adventure, in the hope this could help other adventurers out there.

TL;DR: play around with mysqldump and SSH, or use string sorcery with SHOW CREATE TABLE.

CREATE TABLE LIKE doesn’t copy views

This issue is quite simple: you can’t create views with a CREATE TABLE command, obviously.
And no, there’s no CREATE VIEW x2 LIKE x1, because a view’s usage is similar to a table’s only in some cases. You have to resort to string sorcery and the output of SHOW CREATE VIEW.

Bonus: should I mention there’s no way to separate tables from views on commands such as SHOW TABLES?

CREATE TABLE LIKE copies everything from the structure, except constraints

The manual tells you that you can copy over your table using that command, including column attributes and indexes. But there’s no clear statement saying your constraints will go forgotten.
It also tells you foreign keys will be dumped correctly by mysqldump, even telling you about the ordering issue that utility has. But why tell you the negative, unsolved issues such as that those constraints are not included in the CREATE TABLE LIKE copy operation?

So in those two cases, you got to resort to string sorcery. The usual features MySQL built for that doesn’t work.

mysqldump might take forever to run across a network

In some cases we would need to copy the structure from a machine to another, so the best way would simply to give mysqldump the host, user, and password, and ask him for a complete dump that database schema, correct?

Oh well, if we ssh’d into the server and ran mysqldump it would take, say, 3 secs to dump everything. Downloading that dump over SSH would take the same amount of time.
If done using host/user/password it could take almost 3 minutes (!!!), over the exact same VPN network.

Thus we had to stuff into our application some SSH credentials. Just for that single operation. DB credentials are not enough for MySQL’s dump protocol.
Go wonder.

mysqldump might ignore your foreign key order

Again, as stated in the manual, mysqldump will disable foreign key checks on your dump so table order can be ignored during importing.
However, that depends on your command line options. If you get naïve and try to speed up things --compact‘ing your dump, MySQL will take your disable command out; you’ll have to add it by hand — what’s not hard, as you’ll be renaming the original database in the dump anyway…

Views on mysqldump or SHOW CREATE VIEW include information related to the original user

One more thing you get to replace() on your dump: the view’s part will include the algorithm and definer options — the last one, pointing to the user that created it. Unless that user is yourself, you got to replace that or MySQL will ask you for more permissions than you have.

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`192.168.0.1` SQL SECURITY DEFINER VIEW `mydb`.`v_cool_view` AS (…)

The final solution

To make stuff work both locally and across the network, we had to:

Verify if we had a local copy of the original db. If the answer is yes…

1. Get CREATE statements for every table and view
2. Alter the needed information on those (especially on CREATE VIEW commands)
3. Run drops and foreign key checks
4. Run each of those create commands

As it turns out, playing around with SHOW CREATE statements took the same amount of time CREATE TABLE LIKE takes, if not less.

If we had to connect to an external DB…

1. run mysqldump over ssh into that machine
2. Alter dump as needed, especially on view commands
3. Add drops and foreign key checks
4. Write that into a temporary file, and run it over mysql client As we had to alter the dump before running it, a simple pipe wouldn’t do; altering it with cat and sed would complicate things way too much, and there was no way to run a bunch of queries at once through our ORM, nor a reliable way to separate each query in the dump file (many were comment-fenced).

Good luck on your MySQL adventures, sir. Good luck.