MySQL is a popular open source Relational DataBase Management System (RDBMS) based on Structured Query Language. MySQL is the database of choice for the most popular open source project like WordPress, Joomla, phpBB, osCommerce, and other software built on the LAMP (Linux, Apache, MySQL, PHP) stack. When managing an application on the LAMP stack, sooner or later, you would want to backup the database or restore from a backup.

How to Export a MySQL Database to a SQL Dump File

The easiest way to do this is to use the mysqldump command from the shell:

mysqldump -u USERNAME -p DATABASE > FILENAME.sql

After you execute the command, you will be prompted for your password and the entire contents of the DATABASE will be dumped to FILENAME.sql.The dump contains the create and insert commands for all the tables and you don’t need to worry about anything at this point.
It is advisable to create regular backups of your database in case of a failure. You can always restore from this dump and back online in no time.

How to Import a MySQL Database From a SQL Dump File

You can restore your database from a SQL dump file by passing the file using the mysql command from the shell:

mysql -u USERNAME -p DATABASE > FILENAME.sql

After you execute the command, you will be asked for your password and the database will be restored from the SQL dump file.

Advanced Options for Exporting and Importing

Backup Database Table Structure
If you want to backup the structure of the database tables without the data, you can use the –no-data switch:

mysqldump -u USERNAME -p --no-data DATABASE > FILENAME.sql

Backup Data Without Inserts
If you only want to backup the data without the database structure, you can use the –no-create-info switch:

mysqldump -u USERNAME -p --no-create-info DATABASE > FILENAME.sql

Backup Several Databases into One File
If you want to backup multiple databases into one file, you can use the –databases option to specify the multiple databases:

mysqldump -u USERNAME -p --databases DATABASE1 DATABASE2... > FILENAME.sql

Backup All Databases into One File
If you want to backup all databases on a server into one file, you can use the –all-databases option:

mysqldump -u USERNAME -p --all-databases > FILENAME.sql

Backup a Database Without Locking Tables
If you get an 1044 “Access Denied when using LOCK TABLES”, you can use the –lock-tables=false option to backup the database without locking the table:

mysqldump -u USERNAME -p --lock-tables=false DATABASE > FILENAME.sql