SSH — Backing up your database

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.

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.

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.

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.

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