Overview
Back up your database over SSH with the mysqldump command — export a single database, schedule that backup as a cron job, or run a shell script to export all databases under your username.
Before proceeding, make sure you've completed the following.
Find your database credentials: To back up your database, you'll need your database credentials, such as username, password, hostname, and database name. See this article for instructions on how to locate this information.
Configure SSH: Make sure you have Created a Shell user and are able to log into your server via SSH. This is required to run the commands in this article.
How do I back up a single database?
How do I back up by running a command?
The following command backs up a single database under your username.
Log in to your server via SSH.
Navigate into your user's home directory.
[server]$ cd ~
Run the following command to back up your database into the current directory.
Note the following adjustments to make:
Make sure to change the
username,hostname, anddatabaseto your credentials.You can use any name for
nameofyourbackup.sql– just make sure it ends with.sql.--no-tablespacesis used to exclude statements in the output.
[server]$ mysqldump --opt --user=username -p --host=hostname --no-tablespaces database > nameofyourbackup.sql
When prompted, enter your database user's password.
Please note the following when you run this command:
The command does not provide any output, so make sure you do not cancel it.
If the server does not respond, then you can assume that the command is continuing with the backup.
For large databases, a backup can take several minutes to complete.
You can now use this FTP article for instructions on how to log into your server and download the database file.
How do I back up via a cron job?
If you want to back up your database using a cron job, you'll need to specify the password. For example:
[server]$ mysqldump --opt --user=username --password='password' --host=hostname --no-tablespaces database > nameofyourbackup.sql
Make sure you add single quotes before and after your password to ensure the command reads it correctly.
How do I back up all databases?
The following script backs up all databases under a single username.
Log in to your server via SSH and navigate into your user's home directory.
Create a file and name it something like db-export.sh.
Add the following code to it.
Make sure to change the
hostname,username, andpasswordto your credentials.#!/bin/sh host="hostname" username="username" password="password" for DB in $(mysql --host="$host" --user="$username" -p"$password" -e"show databases" --skip-column-names -s | grep -v information_schema); do mysqldump --opt --host="$host" --user="$username" -p"$password" --no-tablespaces $DB > ./$DB.sql; done
Run the file:
[server]$ sh db-export.sh
All databases under this username are then downloaded to your current directory.
