Backing Up MySQL via SSH

Don’t even ask whether you can back up and restore MySQL databases via SSH on a Linux server — of course you can! As long as you know the MySQL root password, it’s possible. Note that your SSH root password and your MySQL root password are typically different. Here are some basic example commands for backing up and restoring MySQL databases. (NOTE: When you run the commands, a password prompt will appear — enter your MySQL root password.)

Backing Up a Database

To back up a single database:

mysqldump -u root -p database_name > database_name.sql

To back up multiple databases:

mysqldump -u root -p --databases database1 database2 > databases.sql

To back up all databases:

mysqldump -u root -p --all-databases > all_databases.sql

To back up a specific table within a database:

mysqldump -u root -p database_name table_name > table_name.sql

To back up and compress the dump at the same time:

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

To back up only the data (no table structure):

mysqldump -u root -p --no-create-info database_name > data_only.sql

To back up only the table structure (no data):

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

How to Restore from Backup

To restore a database from backup:

mysql -u root -p database_name < database_name.sql

To restore a single database from a full backup:

mysql -u root -p --one-database database_name < all_databases.sql