How to backup and restore MySQL database from SSH

This post is the solution to backup your MySQL database through SSH,First of all connect to your Linux server through command line after that use the mysqldump utility to create your database backup.

 

mysqldump -u root -p yourdb > yourdb.sql

To backup in zip form:

mysqldump -u root -p yourdb | gzip > yourdb.sql.gz

To backup multiple databases:

mysqldump -u root -p --databases yourdb1 yourdb2 yourdb3 > yourdb1-yourdb2-yourdb3.sql

To backup all databases use the following command:

mysqldump -u root -p --all-databases > all-db-backup.sql

To backup all databases in zipped form:

mysqldump -u root -p --all-databases | gzip > databasebackup.sql.gz

To backup database structure only:

mysqldump -u root -p --no-data yourdb > yourdb.sql

To backup database data only:

mysqldump -u root -p --no-create-info yourdb > yourdb.sql

To backup MySQL database in XML format:

mysqldump -u root -p --xml yourdb > yourdb.xml

To restore MySQL backup,we use following command:

mysql -u root -p yourdb < yourdb.sql