Exporting MySQL schema structure using popular MySQL clients

Exporting schema structure using your favorite MySQL client

The importance of databases indexes

Creating the right indexes for your database, is the foundation for optimal database and SQL query performance. If you’re currently investing all of your efforts in query optimization, you should know that there are more aspects to think about.

EverSQL Query Optimizer‘s algorithm takes many factors into consideration to calculate the best indexes for your query. Among those factors are the query structure, table sizes, existing indexes and their cardinality, column types and sizes, the connections between the tables and columns in the query and many other factors.

Therefore, when optimizing with EverSQL, we don’t only ask for the query to optimize, but also require the schema structure, to provide you with the best query optimization and indexing recommendations.

Export schema structure using MySQLDump

mysqldump is a database backup command line utility created by Oracle. The utility is provided as part of the MySQL Server package.

The following command can be used to export the schema structure using mysqldump:

mysqldump --xml --no-data -h localhost -u root -pPassword schema_name > path/to/dump/file

Please make sure to change the following parameters to adjust the command to your environment:

  • “localhost” – should be replaced with your MySQL server name.
  • “root” – should be replaced with a MySQL user with permissions to dump the schema structure.
  • “Password” – the MySQL user’s password.
  • “schema_name” – The name of your schema.
  • “path/to/dump/file” – The path to export the schema structure to.

Once you executed the command and exported the schema structure, you’re all ready to upload it to EverSQL Query Optimizer.

Export schema structure using phpMyAdmin

phpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the Web.

Please follow these steps to export the schema structure using phpMyAdmin:

  1. On the left menu, click your database name.
  2. On the right pane, choose Export from the top menu.
  3. From the Format dropdown, choose XML.
  4. From the Export Method options, choose Custom – display all options
  5. Uncheck Export Contents from the Data dump options section.
  6. Click Go.

You’re now ready to upload your schema structure to EverSQL Query Optimizer.

Export schema structure using Sequel Pro

Sequel Pro is a Mac database management application for working with MySQL databases.

Please follow these steps to export the schema structure using Sequel Pro:

  1. From the File menu, choose Export
  2. At the top menu, choose SQL as the export method
  3. Uncheck the option: Content
  4. Uncheck the option: DROP TABLE syntax
  5. Uncheck the option: Output Blog as Hex
  6. Click Export

You’re now ready to upload your schema structure to EverSQL Query Optimizer.

Export schema structure using MySQL Workbench

MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. MySQL Workbench is available on Windows, Linux and Mac OS X.

Please follow these steps to export the schema structure using MySQL Workbench:

  1. From the Server menu, choose Data Export
  2. On the left side, choose the database to export.
  3. Choose “Dump structure only” as the dump method.
  4. Uncheck the options: Dump Stored Procedures and Functions, Dump Events, Dump Triggers
  5. Under Export Options, choose Export to Self-Contained File
  6. Click Start Export

You’re now ready to upload your schema structure to EverSQL Query Optimizer.

Export schema structure using HeidiSQL

HeidiSQL is a free and open-source administration tool for MySQL and its forks, as well as Microsoft SQL Server and PostgreSQL.

Please follow these steps to export the schema structure using HeidiSQL:

  1. From the Tools menu, choose Export Database as SQL
  2. On the left side, choose the database to export.
  3. Uncheck Create and Drop next to Databases
  4. Uncheck Drop next to Tables
  5. Check Create next to Tables
  6. Choose No Data to extract only the schema structure.
  7. Choose Single SQL file as the output method.
  8. Click Export

You’re now ready to upload your schema structure to EverSQL Query Optimizer.

Export schema structure using SQLYog

SQLyog is a GUI tool for the RDBMS MySQL. It is developed by Webyog.

Please follow these steps to export the schema structure using SQLYog:

  1. From the Tools menu, choose Backup Database as SQL dump
  2. At the top pane, choose Export as SQL: structure only
  3. On the left side, choose the database to export.
  4. On the left side, uncheck all Object types except Tables.
  5. Uncheck all options on the right side pane.
  6. Click Export

You’re now ready to upload your schema structure to EverSQL Query Optimizer.

Export schema structure using DbForge Studio Express

A free mysql client that provides basic functionality for database development. It includes database Object Editor, security configuration tools, advanced Data Editor, and Export/Import from CSV and ODBC

Please follow these steps to export the schema structure using DbForge Studio Express:

  1. From the Database menu, choose Backup and Restore => Backup Database
  2. Click Next to navigate to the Backup Content screen.
  3. Choose the structure option and uncheck the Data option.
  4. Uncheck all Object types except Tables.
  5. Click Backup

You’re now ready to upload your schema structure to EverSQL Query Optimizer.