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