How to backup and restore a MySQL database

Backing up and restoring a MySQL database is a crucial task to ensure the safety and integrity of your data.

Backup MySQL database

Assuming that your database is already created run the following command to back up your database:

sudo mysqldump -u your_username -p your_database_name > backup.sql

This command creates a backup file that can then be imported into an already existing database the restore the old one.

Automatic backups

It is possible to automate the backup process so that it is performed on a certain date and time without your input. There are multiple ways this can be achieved. We will go over the most universally applicable ones.

Using cronjob

Open the cron config file with the following command:

crontab -e

And enter the following line:

0 0 * * * mysqldump -u your_username -p'your_password' your_database_name > /path/to/backup/directory/backup.sql

The symbols(0 0 * * *) at the beginning of the file indicate when the cronjob will run. You can take a look at the table below so you can set exactly when you want your cronjob to run.

Cron Job Time Syntax

So, the cron job time syntax 0 0 * * * translates to "At 12:00 AM (midnight) every day of every month, regardless of the day of the week.

Using MySQL Event Scheduler

If you have MySQL 5.1.6 or later, you can use the built-in Event Scheduler to automate backups.

First, you need to ensure that the MySQL Event Scheduler is enabled

mysql -u root -p
SET GLOBAL event_scheduler = ON;

To check the status of the MySQL scheduler use:

SHOW VARIABLES WHERE VARIABLE_NAME = 'event_scheduler';

Output:

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+

Alternatively, you can edit the config file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following line under the Mysqld area:

event_scheduler = on

And restart the service:

systemctl restart mysql

Then you can use the following syntax the schedule a backup event;

CREATE EVENT daily_backup
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    SET @backup_file = CONCAT('/path/to/backup/directory/backup_', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '.sql');
    SET @sql = CONCAT('mysqldump -u your_username -p''your_password'' your_database_name > ', @backup_file);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;

You can use the following command to check the scheduled events of a database:

SHOW EVENTS FROM database_name;

Output:

+------------------+----------------------+-----------+----------------------+----------------------+----------------------+----------------+----------------+--------------------+------+---------+------------+----------------------+
| Db               | Name                 | Definer   | Time zone            | Type                 | Execute at           | Interval value | Interval field | Starts             | Ends | Status  | Originator | character_set_client |
+------------------+----------------------+-----------+----------------------+----------------------+----------------------+----------------+----------------+--------------------+------+---------+------------+----------------------+
| your_database_name | daily_backup         | root@localhost | SYSTEM               | RECURRING            | 2038-01-19 03:14:07 | 1              | DAY            | 2023-08-30 03:14:07 | NULL | ENABLED |         10 | utf8                 |
+------------------+----------------------+-----------+----------------------+----------------------+----------------------+----------------+----------------+--------------------+------+---------+------------+----------------------+

Restore database from backup

To restore a database from a backup you will first need to create a new empty database and import the backup file into it.

mysql -u root -p
CREATE DATABASE newdatabase;

You have to use the following command to import it:

mysql -u your_username -p your_database_name < backup.sql