How do I backup MySQL databases?

For VPS Users:

Even though we backup VPS files, it is not recommended to rely on file backups for MySQL backups and you are responsible for making your own backups. Restoring a database from file backups can result in corruption.

We recommend to use mysqldump to create a MySQL dump file daily which can be used to restore the entire server or independent databases or tables. This tool can handle from small to really big databases. Also, you can set up a cron job to do backups every certain amount of time.

It is possible to backup your tables or databases using phpMyAdmin’s interface, but only if the database is small, since it may time out, or be much slower.

To backup your whole database using mysqldump:

  1. Login into your server via SSH
  2. Enter mysqldump -u [databaseuser] -p [databasename] > /path/to/output/file.sql
  3. Press enter
  4. Enter databaseuser‘s password

You will be able to download the output file via SCP, or copying / moving the file to your web root, like (for example, /var/www/vhosts/yourdomain/httpdocs/file.sql) then going into your browser downloading it (for example, http://yourdomain.com/file.sql).

Remember: The user databaseuser must have privileges on the database to successfully complete the backup. databaseuser is not your Linux / root user.

For Shared Hosting Users:

You will also need to specify the host when running the mysqldump command (otherwise you get an error stating 2002: Can’t connect throughout socket)

For sg111 users the command would be:

For sg112 user the command would be:

Recomended Reading

Noupe: 10 Ways to Automatically & Manually Backup MySQL Database