Setting backups

In this recipe, we will learn how to back up the MySQL database.

Getting ready

You will need administrative access to the MySQL database.

How to do it…

Follow these steps to set up the backups:

1. Backing up the MySQL database is the same as exporting data from the server. Use the mysqldump tool to back up the MySQL database as follows:

   $ mysqldump -h localhost -u admin -p mydb > mydb_backup.sql
   

2. You will be prompted for the admin account password. After providing the password, the backup process will take time depending on the size of the database.

3. To back up all databases, add the --all-databases flag to the preceding command:

   $ mysqldump --all-databases -u admin -p alldb_backup.sql
   

4. Next, we can restore the backup created with the mysqldump tool with the following command:

   $ mysqladmin -u admin -p create mydb
   $ mysql -h localhost -u admin -p mydb < mydb_backup.sql
   

5. To restore all databases, skip the database creation part:

   $ mysql -h localhost -u admin -p < alldb_backup.sql...