In this tutorial I will walk you through the process of creating and restoring a database backup from Terminal in Ubuntu.
Create a Database Backup
We're going to use mysqldump to create a backup of the database, and we're going to compress the backup with gzip.
mysqldump -u user_name -h localhost -p database_name | gzip -9 > backup_name.sql.gz
Replace user_name with your MySQL user name.
Replace database_name with the name of the database you wish to export.
Restore the Database Backup
From the terminal of the server to which you wish to import the database backup, use the following command:
zcat /path/to/database/backup/backup_name.sql.gz | mysql -u user_name -p database_name
Replace user_name with your MySQL user name. You'll be prompted for this user's password.
Replace database_name with the name of the database you wish to import.
Now, if you are feeling extra fancy, I've included additional instructions for deploying a database backup from one remote server to another:
Deploying a Database to Another Server
Exporting and importing your database backups via terminal is also useful if you need to deploy a copy of a database from one server to another. Since you've already done the export on one server, let's focus on the server to which you want to import the database.
Transferring the Database Backup
Do NOT use File Transfer Protocal (FTP). FTP is fun and all, but here we're going to use a more efficient mechanism. Secure Copy has a number of benefits over the lesser methods of transferring files:
- One Less Trip - No need to download from one server just to upload to another.
- Faster Transfers - Servers tend to have good internet connections. Once your computer is out of the equation, the transfer rate is going to be orders of magnitude better.
- It's Secure - Not only does it have secure in the name, but it also uses SSH for authentication and data transfer.
So let's get down to it then. From the server to which you wish to import the database, use the following command:
sudo scp user_name@ip_address:/file/path/to/backup/backup_name.sql.gz ./
Replace user_name with the username you usually use to SSH into the remote server. You'll be prompted for this user's password.
Replace ip_address with the IP Address of the remote server that has the database export on it.
Replace /file/path/to/backup/backup_name.sql.gz with the full file path of the database export on the remote server.
Now that you've got the database backup on the second server, all you have to do is Restore the Database Backup using the instructions from earlier in this tutorial.