SSH — Backing up your database

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:

• Database backup overview

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:

• Finding your database login credentials

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:

• SSH overview

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

You can now use the following FTP article for instructions on how to log into your server and download the .sql file:

• FTP overview

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.

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.

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