> ## Content Index > Fetch the complete content index at: https://blog.alphavps.com/llms.txt > Use this file to discover other available public pages before exploring further. # How to backup and restore a MySQL database - URL: https://blog.alphavps.com/how-to-backup-and-restore-a-mysql-database/ - Published: 2024-01-25T08:35:00.000Z - Updated: 2026-04-20T08:06:01.000Z - Author: David Filipov - Tags: Tutorials, Databases, Backups & Storage 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: ``` 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 ```