Recover or Dump a Mysql databse using a commandline

Backing up MySQL database

mysqldump – a quick way to dump small databases using sql files

Make a backup of One database

mysqldump  --default-character-set=utf8  -u db_user -p -h db_host db_name > dump_file.sql

Make a backup of All databases

mysqldump  --default-character-set=utf8  -u db_user -p -h db_host -all-databases > dump_file.sql

Include routines,triggers and events

mysqldump  --default-character-set=utf8  --single-transaction --set-gtid-purged=OFF  
--add-drop-database --triggers --routines --events 
-u db_user -p -h db_host db_name > dump_file.sql

Gzip (compress) the backup

mysqldump -u db_user -p db_name | gzip -9 > backup.sql.gz

Recover:

mysql  --default-character-set=utf8  -u db_user -p -h db_host db_name &lt; dump_file.sql

Recover only one database from the dump file:

mysql -u db_user -p --one-database db_name &lt; dump_file.sql

Recover from a gzipped file:

zcat dump_file.sql.gz | mysql -u db_user -p

Dump everything:

Move one database to a separate file (extract a database from dump containing more than one database:

sed -n '/^-- Database: `DATABASE_NAME`/,/^-- Database: `/p' DUMP_NAME.sql > FILE.sql