EDUCBA

Home Data Science Data Science Tutorials MySQL Tutorial Mysql Export Schema

Mysql Export Schema

Mysql Export Schema

Definition of Mysql Export Schema

MySQL Export Schema is a technique featuring exporting the database DDL for database objects like tables, functions, views, triggers, packages, procedures, and packages bodies. Here, the target purpose may be an SQL commander editor, any clipboard or file. In this MySQL Export method in the database, a user can export all database objects, or we can also just make any selection.

Basically, the MySQL Schema is known to be the system schema that includes all tables’ information or data dictionary tables or, say, database object metadata stored in the server and required for query operations performed.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Hence, in MySQL we can export these schemas or databases to an external file in different formats available such as .sql, .csv, .docx, .xml, Microsoft Excel, Google Docs, Open Office, etc. either using phpMyAdmin or command-line program for mysqldump database/table.

Syntax

We can export schema in the form of a table or whole database in a tranquil way by querying the outcome into a CSV file that exists in the MySQL database server.

But before we implement to export data, a user needs to ensure the following things:

  • A user should have write access in the MySQL server process to the target folder, which consists of the target CSV file.
  • The target CSV file should not be present.

Suppose the succeeding query selects all fields from the books to table existing in the database table with WHERE clause:

SELECT * FROM Books WHERE Language = ‘English’;

Now, exporting this query result data into a CSV file, we need to add few clauses to the above query written as follows:

SELECT * FROM Books WHERE Language = ‘English’
INTO OUTFILE ‘C:/tmp/books_info.csv’ FIELDS ENCLOSED BY ‘ ; ’ ESCAPED BY ‘ ” ’
LINES TERMINATED BY ‘ \r\n ’;

When the query is executed in the server, then this statement will create a CSV file named books_info.csv in the specific c:/tmp folder containing the result set. This particular file exported will include lines of table rows in the resulting group. Here, every line is completed by a structure of carriage return along with a line feed character stated by the LINES TERMINATED BY ‘ \r\n ’ clause. Every line comprises values of every table column of the table row in the set of results.

Every value is surrounded by double quotes defined by the FIELDS ENCLOSED BY ‘ ; ’ clause. It helps avoid the value that might include a comma that will be understood as a field divider. When surrounding the values by the quotes, i.e. double quotation marks then, the commas inside the value will not be accepted as the field dividers.

How to export schema in MySQL?

  • MySQL is currently possessed by Oracle and an open-source database. MySQL stores schemas that include everything present in the table structure like fields, views, privileges, triggers, objects, attributes, indexes, operators, functions, along with data that can be accessed using the SQL language.
  • This MySQL Export Schema helps export these schemas from a database or contain a specific table and can be imported to any other database or table having identical schemas as in the previous one.
  • In more, Schemas in MySQL provides a logical structure to store data and values in the form of table rows and columns with query all database objects, functional procedures, data types, constraint, grouped information to add features and implementing new collections using information schemas and relational databases.
  • In simple words, we can say that a MySQL database is a set of schemas, whereas a schema is a set of tables. Thus, schema can be applied to represent a section of the database using a query.

Example

Let us illustrate the MySQL Export Schema process through examples and find out ways to perform the export operation in the phpMyAdmin database server either in your localhost or cpanel’s phpMyAdmin dashboard as follows:

When we need to use the export schema structure to export any database or table from the server using phpMyAdmin then, we will follow the below steps:

1. Click the MySQL database name present on the left menu of phpmyAdmin:

When you access your phpMyAdmin page and login through the username and password as defined by any user who operates it then, you will find a list of databases created within the server on the left menu. You just need to select the particular database which you want to export with schemas and then click on it.

Mysql Export Schema 1

2. Again choose the Export option from the top menu on the right pane.

After clicking on a particular database, you will see the tables present on that database, and on the top menu, different options are presented for the user to perform different tasks. From this top menu, see the Export option and click on it simply.

Choose the Export option 2

3. Choose the XML option from the Format dropdown to export the database or table.

You will see export options by default. SQL format exists, but you need to choose XML format from the dropdown selection.

Choose the XML option 3

4. Select Custom – display all options from the options Export Method.

On the export page, you can view there are two options for the type of export. Those are either a Quick Method with minimal options or a Custom Method, which displays all options possible. So, choose the custom method and move further.

Mysql Export Schema 4.1

Mysql Export Schema 4.2

Mysql Export Schema 4.3

Here, you can also choose the specific tables for which you want to export schemas and remove others by unchecking the table names.

5. From the section of the Data dump options, uncheck Export Contents.

Now, you can see various options in the custom export option like output for size, character and all, Format-specific options which contain all object creation options having schemas to export, and at last, you will find the Data Dump options, which you need to uncheck to avoid the export of contents as we need to export only schemas:

Uncheck Export Contents 5

6. Next, click the Go option.

Finally, setting all just click the Go button to export schemas from the database, and a .xml file will be downloaded as.

Mysql Export Schema 6.1

When you open the file on your browser, you will see only the table schemas of the database exported.

Mysql Export Schema 6.2

Conclusion

MySQL Export Schema feature is obtainable for DB2 for LUW, H2, Derby, Exasol, MariaDB, Informix, SQL, Mimer, SQL Server, Redshift, Snowflake, NuoDB, MySQL, Oracle, SQLite, PostgreSQL, Vertica, and Sybase ASE. MySQL supports exporting database schema with diverse options in an easy and quick manner to either an Oracle Database Cloud storage or a secure FTP.

Recommended Articles

This is a guide to Mysql Export Schema. Here we discuss the different steps to export schema in MySQL along with syntax and example. You may also have a look at the following articles to learn more –

  1. MySQL CURDATE
  2. MySQL Character Set
  3. MySQL ALL
  4. MySQL Interval