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

Field Value Range Description
Minute (0-59) 0-59 Specifies the minute at which the task should run.
Example: 0 (start of the hour)
Hour (0-23) 0-23 Specifies the hour of the day at which the task should run.
Example: 0 (midnight)
Day of the Month (1-31) 1-31 Specifies the day of the month on which the task should run.
Example: * (every day)
Month (1-12) 1-12 Specifies the month in which the task should run.
Example: * (every month)
Day of the Week (0-6) 0-6 (0 represents Sunday) Specifies the day of the week on which the task should run.
Example: * (every day)

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:



| 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
    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;

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

SHOW EVENTS FROM database_name;


| 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