[WordPress on VPS] How To Backup and Restore MySQL Database

The database is the backbone to all WordPress websites, which is why knowing how to back up and restore MySQL database is a crucial step to getting prepared when needed.

Here is how you can do it and do it all from the shell command line without the popular phpMyAdmin.

Backup MySQL Database

mysqldump is the command line we will be using to backup MySQL database data. Basically, it reads the data from the database and writes them to a dump file with all the SQL commands needed to re-create the database from scratch.

To backup a single database, use

$> mysqldump -u root -p db-name > db-backup.sql
Enter password:

Type in MySQL’s root password and it’s done.

You can also backup multiple databases by use the –databases switch.

$> mysqldump -u root -p --databases db1,db2,db3 > db-backups.sql

Or, simply use the –all-databases to backup all databases you have loaded in your MySQL.

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

If your database is very big, it’s a good idea backing up the database with compression. Just pipe the output to the gzip command line and you will get the compressed backup copy in gzip format.

$> mysqldump -u root -p db-name | gzip -9 db-backup.sql.gz

Restore MySQL Database

Since the backup file is full of SQL commands, we can restore databases by simply telling mysql command to run the commands in the backup file and put the data into the proper database.

If the backup file is only for a single database, use

$> mysql -u root -p db-name < db-backup.sql

If the backup file contains multiple databases, here is how to use it to restore to a specific database.

$> mysql -u root - p --one-database db-name < db-all-backup.sql