SSH — Backing up your database

Overview

This article explains how to back up your database using SSH.

Backing up a single database

Back up by running a command

The following command backs up a single database under your username.

1. Log in to your server via SSH.
2. Navigate into your user's home directory.

   [server]$ cd ~

3. 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, and database to your credentials. 
   • You can use any name for nameofyourbackup.sql – just make sure it ends with .sql.
   • --no-tablespaces is used to exclude statements in the output.

   [server]$ mysqldump --opt --user=username -p --host=hostname --no-tablespaces database > nameofyourbackup.sql

4. 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.

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.

Backing up ALL databases

The following script backs up all databases under a single username.

1. Log in to your server via SSH and navigate into your user's home directory.
2. Create a file and name it something like db-export.sh.
3. Add the following code to it.

   Make sure to change the hostname, username, and password to 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

4. Run the file:

   [server]$ sh db-export.sh

All databases under this username are then downloaded to your current directory.

See also

• Finding your database login credentials
• phpMyAdmin — How to backup / export a database or table
• Database backup overview