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.
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
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.
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.
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…
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 (…)
To make stuff work both locally and across the network, we had to:
- Get CREATE statements for every table and view
- Alter the needed information on those (especially on CREATE VIEW commands)
- Run drops and foreign key checks
- Run each of those create commands
As it turns out, playing around with
SHOW CREATEstatements took the same amount of time
CREATE TABLE LIKEtakes, if not less.
- run mysqldump over ssh into that machine
- Alter dump as needed, especially on view commands
- Add drops and foreign key checks
- 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).