How to Import / Export (Backup / Restore) MySQL Database

It is important to back up your databases so that you can recover your data and be up and running again in case problems occur. MySQL offers a variety of backup strategies from which you can choose the methods that best suit the requirements for your installation.

Export / Backup MySQL database:
The mysqldump client can be used to dump a database or a collection of databases for backup or for transferring the data to another SQL server (not necessarily a MySQL server). The dump contains SQL statements to create the table and/or populate the table.

If you are doing a backup on the server, and your tables all are MyISAM tables, you could consider using the mysqlhotcopy instead since faster backups and faster restores can be accomplished with the latter

Here is the most simple way to export the database to a sql file

# mysqldump -u USER -p DATABASE > FILENAME.sql

USER is the MySQL admin user
DATABASE is the name of the database that need to be exported
FILENAME.sql is the name of the file where your data will be exported

When you issue this command you will be prompted for the MySQL admin password. Enter that password and hit the Enter key. In the directory you issued the command you will now have a file with the FILENAME.sql file you then need to copy to your secure drive.

You can dump all databases by doing:

# mysqldump -u root -p --all-databases > all_dbs.sql

Import/Restore MySQL database:
Below is the simple command through which you can restore / import the already exported MySQL database file (.sql)

# mysql -u USER -p DATABASE < FILENAME.sql

USER is the MySQL admin user
DATABASE is the name of the database where data need to be imported / restore
FILENAME.sql is the dump that was exported.

You will be prompted for the MySQL administrator password.