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.sqlUSER 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.sqlImport/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.sqlUSER 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.