Create MySQL Dump and Restore It

To Dump

A Single Database:

$ mysqldump -u root -p database_name > dumpfile.sql
 - OR -
$ mysqldump -u root -p --databases database_name > dumpfile.sql

All Databases:

$ mysqldump -u root -p -A > dumpfile.sql
 - OR -
$ mysqldump -u root -p --all-databases > dumpfile.sql

Selected Databases:

$ mysqldump -u root -p -D database_1 database_2 database_3 > dumpfile.sql
 - OR -
$ mysqldump -u root -p --databases database_1 database_2 database_3 > dumpfile.sql

A Table in a Database:

$ mysqldump -u root -p --databases database_name --tables table_name > dumpfile.sql

Zip it at the same time:

$ mysqldump -u root -p --opt database_name | gzip > dumpfile.sql.gz

See here for explanations of "--opt" option.

Create and zip remotely, pipe it and unzip
(create .my.cnf in user's home directory for password)

$ ssh user@host "mysqldump database_name | gzip -9" | gzip -d > dumpfile.sql

To Restore

$ mysql -u root -p database_name < dumpfile.sql

# For compressed dump
$ gunzip < dumpfile.sql.gz | mysql -u root -p database_name