Backing up and restoring a MySQL database via SSH
1/ This tutorial will show you how the steps for backing up a MySQL database via SSH
Sometimes a MySQL database is simply too large to backup on a web based interface. In the event of this occurring, it is fairly easy to backup a database via SSH; all it takes is one command. This tutorial will show you how.
First of all, login to SSH either as root or elevate yourself to root status.
mysqldump -u user -p password -h host dbname > outputfile
The terms highlighted in bold need to be filled out by you. A brief overview of what they are:
- user: The user that has access to the database you want to backup
- password: The password of that user
- host: Your database host; usually setting this to localhost works fine
- dbname: The name of the database you want to backup
- outputfile: The name of the file you want to backup the database to.
Here is an example:
mysqldump -u john -p johnpass -h localhost john_db > /home/john/backup.sql
This will backup the database john_db to /home/john/backup.sql. Keep in mind for very large files, this will take some time, so be patient. Once it is finished, you will be returned to the command line.
2/ This tutorial will show you how to restore a MySQL database via SSH
Sometimes you will have a database backup that is too big to restore over a web interface. The best way to restore large SQL backups is through SSH, and all it takes is one command. This tutorial will show you how.
First of all, connect to your server via SSH and login as root/elevate yourself to root status. Here is the command we are going to use:
mysql -u user -p password -h host database_name < backupfile.sql
You need to change the bold sections. A brief explanation of each:
- user: The user who has access to the database you are importing data into
- password: Password of the user
- host: The database host. This is usually localhost
- database_name: Name of the database you want to import data into
- backupfile.sql – Name/location of the backup file you want to import.
Here is a proper example:
mysql -u john -p johnspass -h localhost johns_db < /home/john/backups/backup.sql
In this example, we are importing the file backup.sql into the database johns_db, using the user john, and the database host is localhost.
This could take a while depending on the size of the database. Once you are returned to the command line, the import has been completed. It is a good idea to check in phpMyAdmin or a similar tool to make sure that the database imported correctly.