Back Up and Restore a MySQL Database

You can backup your MySQL data by using the mysqldump command. This command connects to the MySQL server and creates an SQL dump file

$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.dump]

[uname] Your database username
[pass] The password for your database
[dbname] The name of your database
[backupfile.dump] The filename for your database backup
[--opt] The mysqldump option

For example, to backup a database name 'mydb'
$ mysqldump -u root -p Tutorials > mydb_backup.dump

With mysqldump command you can specify  tables of your database you want to backup

mysqldump -u root -p mydb mytable  > mytable_backup.dump

If you want to back up all the databases in the server at one time you should use the --all-databases option.

$ mysqldump -u root -p --all-databases > alldb_backup.dump

The mysqldump command has also some other useful options:

--add-drop-table: Tells MySQL to add a DROP TABLE statement before each CREATE TABLE in the dump.

--no-data: Dumps only the database structure, not the contents.

--add-locks: Adds the LOCK TABLES and UNLOCK TABLES statements you can see in the dump file.


Back up your MySQL Database with Compress

$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.dump.gz]

Restoring your MySQL Database

Create an appropriately named database on the target machine
Load the file using the mysql command:

$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.dump]

how you can restore your mydb.dump file to the mydb database.

$ mysql -u root -p mydb < mydb.dump