SSH — Backing up your database

Overview

This article explains how to backup your site using SSH. This means you would log into your server using a terminal, then run a command to download the database to the server. For other backup options, view the following article instead:

Finding your database credentials

To backup your database, you'll need to know your database credentials such as username, password, hostname, and database name. View the following article for instructions on how to locate these credentials:

Where to run the command?

You must log into your web server via SSH to run the following command. Visit the following article for full instructions on how to do log into your server in a terminal:

Running the command

At this point you should have your database credentials and are logged into your server in a terminal. Make sure you're in your user's home directory:

[server]$ cd ~

Once there, run the following command. Make sure to change the username, password, yourMySQLHostname, and dbname to the correct credentials. 

[server]$ mysqldump --opt --user=username -p --host=yourMySQLHostname --no-tablespaces dbname > nameofyourbackup.sql
  • You will then be prompted for your database user's password
  • You can use any name for "nameofyourbackup.sql" – just make sure it ends with .sql

Please note the following when you run this command:

  • You only need to run the command once.
  • The command does not provide any output.
  • To an inexperienced user, it may appear that the server is not responding — Do not cancel the command action! If there is no response from the server, 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 the following FTP article for instructions on how to log into your server and download the .sql file:

Backing up ALL databases under a single username

The following script backs up all databases under a single username. In the past it was possible to use the --all-databases flag, but this no longer functions due to a MySQL upgrade. Make sure you obtain the hostname, username, and password before proceeding.

  1. Create a file on your Mac, Linux computer, or on your DreamHost server. Name it something like db-export.sh.
  2. Add the following code to it. Make sure to update the host, username, and password fields.
    #!/bin/sh
    
    host="yourMySQLHostname"
    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
  3. Run the file.
    [server]$ sh db-export.sh

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

Backing up your database in a cron job

If you want to back up your database in a cron job, you'll need to specify the password. For example, you could add this line to a Shell script to back up your database.

Make sure you add single quotes before and after your password to ensure the command reads it correctly.

[server]$ mysqldump --opt --user=username --password='password' --host=yourMySQLHostname --no-tablespaces dbname > nameofyourbackup.sql

See also

Did this article answer your questions?

Article last updated PST.

Still not finding what you're looking for?