MySQL | Backup and restoration using mysqldump

The mysqldump client is a utility that dumps one or more MySQL database for backup or transfer to another SQL server. It can be used to dump one table at a time as well. All that is required is connectivity to the database server and access privileges to the database.

Backup a single table.

mysqldump -u username -p db_name table_name > table_name_dump.sql

Backup multiple tables.

mysqldump -u username -p db_name table1_name table2_name > dump.sql

Restore the single table back to the database.

mysql -u username -p db_name < /path/to/table_name_dump.sql

Backup an entire database. The command can be altered to backup multiple databases just like tables.

mysqldump -u username -p db_name > db_backup.sql

Backup all the databases in the server.

mysqldump -u username -p --all-databases > all_db_backup.sql

Restore the database.

mysqldump -u username -p < db_backup.sql