Back Up or Export a Database from SSH

What do we need to know?

A database is where your website keeps its data. An export is a copy saved to a file. SSH is a way to type commands on your server from your own computer. It uses a text window called a terminal.

The command we use is mysqldump. It reads a database and writes all its content to a .sql file. The file is plain text with the instructions needed to rebuild the data.

Before you start

• You need SSH access. Your welcome email or hosting panel tells you if you have it. Ask Hostvento support if you are not sure.
• Know the database name, the user name and the password.

Steps

1. Connect to your server with SSH.
2. Run this command. Change the three words to your own details.

mysqldump -u username -p databasename > backup.sql

This asks for your password, then saves the database into backup.sql in the current folder.

3. Type the database password and press Enter. You will not see the letters as you type. This is normal.
4. Check that the file exists.

ls -lh backup.sql

This lists the file and shows its size. A size of zero means something went wrong.

Make a smaller file

Compress the file with gzip. This makes it much smaller.

mysqldump -u username -p databasename | gzip > backup.sql.gz

Put a copy in a better place

Do not leave your only backup on the same server. Download it to your computer. Use File Manager in your control panel or an SFTP program.

Restore it later

Warning: Restoring replaces data in the database. Take a new backup first.

mysql -u username -p databasename < backup.sql

This reads the file back into the database.

Quick recap

• mysqldump saves a database to a .sql file.
• Use > to write to the file and < to read it back.
• Add gzip to make the file smaller.
• Download the backup and keep it off the server.