How to Backup a MySQL Database

A database backup is a copy of the data stored in a database which can be used to restore the database to a previous state in case of data loss, corruption, or other issues.

These backup files are often generated daily using automated scripts executed by task schedulers such as Cron, and stored in the cloud on services such as Amazon S3 or Google Archival, that provide both durability and security.

In this article, you'll learn how to create a dump (i.e. backup) of your MySQL database using the mysqldump utility.

Backuping data with mysqldump

The mysqldump utility is a command-line tool used to create logical backups of MySQL databases by generating SQL statements into a text file, which can then be used to recreate database objects and insert data.

The general syntax of the mysqldump command is as follows:

$ mysqldump -u [user] -p [options] > [destination]

Where:

• user is the username used to connect to the database server.
• options is a list of flags and arguments used to filter the command's output.
• destination is the path to the destination file the backup will be saved to.

Note that when running this command, you will be prompted to type in the password associated with the specified user account.

Creating a full database backup

A full backup is a dump that includes all the necessary instructions (i.e. SQL statements) for recreating an entire database including all its tables, indexes, and other objects.

To create a full database backup, you can use the following command:

$ mysqldump -u [user] -p [database] > [destination]

Where:

• database is the name of the database you want to create a backup of.

For example:

$ mysqldump -u admin -p elearning > dump.sql

Alternatively, you can generate a dump of all the databases contained in the MySQL server at once using the --all-databases flag:

$ mysqldump -u admin -p --all-databases > dump.sql

Creating a full table backup

To create a full backup of a single database table, you can use the same command and specify the table name right after the database name as follows:

$ mysqldump -u [user] -p [database] [table] > [destination]

For example:

$ mysqldump -u admin -p elearning users > dump.sql

Creating a structure backup

In some cases, such as when creating a testing/staging environment, you may want to only backup the structure of a database or its tables, without the data they contain.

To achieve this, you can use the --no-data flag:

$ mysqldump -u [user] -p --no-data [database] [table] > [destination]

Troubleshooting

Here is a list of the most common errors you might encounter, and that you will need to resolve in order to generate a dump of your database.

Access denied

This error means that the user you are connected with doesn't have sufficient permissions to access the database(s) or table(s) you’re trying to back up. To solve this error, you can try to run the mysqldump command with the --no-tablespaces flag.

Out of memory

This error means that the machine doesn't have enough available RAM to perform the database dump. To solve this error, you can try to run the mysqldump command with the --quick flag.

Permission denied

This error means that you don't have sufficient permission to write the backup file in the specified directory. To solve this, either change the destination path to a directory your have read and write permissions on, or run the mysqldump command with sudo to gain elevated privileges.