Import/export tables or databases to .sql in MySQL (examples)

This tutorial will cover basic import and export terminal procedures in MySQL. Each of the procedure is explained through an example. All of the export commands are also available remotely, all you have to do is to change the host to your remote address.

Export whole database

Export database by following the command below. No table names are required, as you want to export all of them.

mysqldump -u your_username -p your_database_name > output.sql

Export table/s

In case of a single table, specify its name after your database name.

mysqldump -u your_username -p your_database_name name_of_the_export_table > output_table.sql

If you want to export multiple tables, specify their names one after another, separated by a space.

mysqldump -u your_username -p your_database_name your_table1 your_table2 your_table3 > output_tables.sql

Ignore some tables while exporting

To leave out one or more tables while dumping the database, use –ignore-table parameter.

mysqldump -u your_username -p db_name --ignore-table=db_name.table1 --ignore-table=db_name.table2 > output.sql

Export only schema (CREATE instructions)

Add –no-data to export only schema.

mysqldump --no-data -u your_username -p your_database_name > output.sql

Or, with tables:

mysqldump --no-data -u your_username -p your_database_name table1 table2 table3 > output.sql

Export only data

Add –no-create-info to export only data:

mysqldump --no-create-info -u your_username -p your_database_name > output.sql

Or, with tables:

mysqldump --no-create-info -u your_username -p your_database_name table1 table2 table3 > output.sql

Single transaction vs Lock tables

If you add –single-transaction parameter, your command will run without locking tables, in one transaction. What this means is that it creates a snapshot of the tables and dumps it. Although the tables might change during the process, you only get the data from the snapshot. Use this option when working with InnoDB engine.

mysqldump --no-create-info --single-transaction -u your_username -p your_database_name > output.sql

If you add –lock-tables parameter, the command will lock all tables related to your action and will not unlock them until the operation is over. Use this with MyISAM engine or a mix of MyISAM and InnoDB.

mysqldump --no-create-info --lock-tables -u your_username -p your_database_name > output.sql

Export stored procedures, functions and triggers

Add –routines and –triggers to your command if you want to export stored procedures, stored functions and triggers as well.

mysqldump --routines --triggers -u your_username -p your_database_name > output.sql

Import database

Change your command to mysql and add sign < to import data.

mysql -u your_username -p your_database_name < data_to_import.sql

Import multiple .sql files

Use command cat *.sql before your mysql command to filter all the .sql files to process.

cat *.sql | mysql -u your_username -p your_database_name

 Import only specific tables from .sql file

Simply choose which tables would you like to import:

mysql -u your_username -p your_database_name --tables table1 table2 table3 < data_to_import.sql

Check out the Pipe Viewer tool

Pipe Viewer is a tool which shows a progress bar while importing tables/databases. It is especially helpful when importing huge files. If you’d like to try it out, proceed to my tutorial on this link.

Show MySQL import progress using Pipe Viewer

This is a short tutorial on how to show MySQL import progress using an app called Pipe Viewer. It’s very easy to install and use, but there is a few points which require explanation, so I will describe them here. I will also show examples on how to use this tool.