MySQL Backup and Recovery with Command-Line

the mysqldump client is a backup program which can be used to dump a database or a collection of databases for backup or transfer to another sql server. the dump typically contains sql statements to create the table, populate it, or both.

backup an entire database

mysqldump -u username -p db_name > backup.sql

-p, --password
-u, --user=username

backup a table

mysqldump db_name table > backup.sql

dump several databases with one command

mysqldump --databse db_name1 db_name2 .. > databases.sql

dump all databases

mysqldump --all-databases > all_databases.sql



you need to create the databases manually if your target mysql server has no database with same name before recovery

mysql> create database db_name;

restore

mysql -u username -p < backup.sql