phpmyadmin Question posted in
The official documentation can be found here.

Is there a way of viewing the export query run by PHPMyAdmin on a database?

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?

Tags:

2

Answers


  1. 0

    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 table pma__export_templates.

    Provided that you have full access to all databases on your servers, including the phpmyadmin one, you can do this:

    • Create an export template with the settings you want on one of the servers.
    • Export the export template itself from the phpmyadmin.pma__export_templates table.
    • Import the template to phpmyadmin.pma__export_templates on the next server.
    • Go to the export screen there and load the template.
    Login or Signup to reply.
  2. 0

    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:

    mysqldump --no-data --skip-add-drop-table --skip-add-locks -h $staging_host > staging-dump.sql
mysqldump --no-data --skip-add-drop-table --skip-add-locks -h $prod_host > prod-dump.sql

    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 the vim command:

    :%g/AUTO_INCREMENT=[0-9]* /s///

    Then diff the files side-by-side:

    diff -y staging-dump.sql prod-dump.sql > diff

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search