I am faced with comparing the structures of multiple databases – but not the databases in their entirety, just the tables they all hold in common. I can export their structure via the interface, but have to click through the list of tables each time to define what I want exported. Is there a way I can access the export queries run by PHPMyAdmin in order to simply copy-paste each time?
Question posted in PhpMyAdmin
The official documentation can be found here.
The official documentation can be found here.
2
Answers
phpMyAdmin 4.5.0+ supports export templates. On the export screen, you can create an export template with the settings you need. They are per server, but we can do something about that.
By default (this can be configured in PMA’s config file), PMA stores these export templates in the database
phpmyadmin
and tablepma__export_templates
.Provided that you have full access to all databases on your servers, including the
phpmyadmin
one, you can do this:phpmyadmin.pma__export_templates
table.phpmyadmin.pma__export_templates
on the next server.You can use
mysqldump --no-data
if you only want to export the metadata.I did this the other day for a developer at my company who was confused that the staging instance of their database was somehow different from the production instance.
I ran:
This dumps just the CREATE TABLE statements. It omits any of the INSERT statements that would contain the data contents of the tables. There’s also some comments and some harmless stuff about character sets. Ignore these.
I edited each file in
vim
to remove the AUTO_INCREMENT options from each table creation. Of course these will be different if the number of rows in the tables is different. Here’s thevim
command:Then diff the files side-by-side:
The output shows the files side-by-side (at least as much as it can display), with a single-character column running down the middle between them. The middle column will have
|
to indicate lines that are different, or<
to indicate lines that exist only in the first file, or>
to indicate lines that exist only the second file.