Brief analysis of mysql scheduled backup tasks

Introduction

In a production environment, in order to avoid data loss, the database is usually backed up regularly. The Linux crontab command can help us to back up the database regularly. First, let's take a brief look at the crontab command. If you know how to do it, please skip to the next content, mysql backup.
The MySQL database in this article is installed in a Docker container, which is used as an example for explanation. If it is not installed in the Docker container, you can also refer to it.

contab scheduled tasks

Use crontab -e to write our scheduled tasks.

0 5 * * 1 [command]

The first five numbers represent minute, hour, day, month, and week respectively, and command behind is your execution command.
If you need to execute a scheduled task at 8:00 p.m. every day, you can write it like this

0 8 * * * [command]

Extensions:

• crontab -l can view your scheduled tasks
• crontab -r deletes all scheduled tasks for the current user

mysql backup

Get started quickly

Here my mysql database is a docker container. If you need to execute a scheduled task at 8 o'clock every night, you can write it like this.
First execute the command crontab -e.

0 8 * * * docker exec mysql_container mysqldump -uroot -proot_password database_name > /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql

mysql_container is the name of your database container
mysqldump is a command to export data from mysql database
-u fill in the root account
-p fill in the root password
database_name The name of the database to be backed up
/var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql backup file, followed by the file name format

If you don't have any requirements and just want to back up, then the above command can help you perform scheduled backups.

Small pitfall: When backing up MySQL, I used commands like docker exec -it mysqldump ... to create a bash script. Because the -i parameter has an interactive meaning, when executing scheduled tasks in crontab , no data was output to sql file. Therefore, do not add the -i parameter when using crontab to regularly back up the docker container.

Crontab Optimization

I don’t recommend writing the commands to be executed directly in crontab -e . If there are too many tasks, the file will become cluttered.
It is recommended to write the database backup command into a bash script. Just call it in crontab , such as: create a /var/backups/mysql/mysqldump.sh file with the following content

docker exec mysql_container mysqldump -uroot -pmypassword database_name > /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql

Then make the file executable by the current user:

chmod 711 /var/backups/mysql/mysqldump.sh

Execute the crontab -e command and modify it as follows:

0 20 * * * /var/backups/mysql/mysqldump.sh

Then this is more standardized.

MySQL backup optimization

Because sql files are relatively large, they are generally compressed, otherwise the disk space will be too large.
Assuming you have done the crontab optimization above, we can change the mysqldump.sh script to the following:

export mysqldump_date=$(date +%Y%m%d_%H%M%S) && \
docker exec mysql_container mysqldump -uroot -pmypassword database_name> /var/backups/mysql/$mysqldump_date.sql && \
gzip /var/backups/mysql/$mysqldump_date.sql
find /var/backups/mysql/ -name "*.sql" -mtime +15 -exec rm -f {} \;

export defines a variable mysqldump_date in the system for use in backup and compression commands
gzip is a compression command. By default, the source file will be deleted after compression and compressed into a .gz file
The command find ... means to search /var/backups/mysql/ directory for all files with a creation time 15 days ago (-mtime +15 ) and a file name suffix of .sql and execute the deletion command -exec rm -f {} \;. The general meaning is: MySQL backup files are only retained within 15 days. Delete all posts older than 15 days.

Data Recovery

If you accidentally execute drop database , stay calm. We first have to create the database where the database was deleted.

>mysql create database database_name;

Then restore the most recent backup. Command to restore the backup:

docker exec -i mysql_container mysql -uroot -proot_password database_name < /var/backups/mysql/20200619_120012.sql

Although the data of the backup file was restored, we did not restore the data after the backup time point.
For example, if you perform a scheduled backup at 8pm, but drop the database at 9pm, the data within the hour from 8pm to 9pm will not be backed up. At this time, binlog log should be used.

binlog

Binlog is an archive log of MySQL that records the logic of data modification, such as adding 1 to the money field of the row with ID = 3.
First log in to mysql and query how many binlog files there are currently:

> mysql show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 729 | No |
| binlog.000002 | 1749 | No |
| binlog.000003 | 1087 | No |
+---------------+-----------+-----------+

View the binlog currently being written

mysql> show master status\G;

Generate a new binlog file. All subsequent operations of MySQL will be written into the new binlog file. Generally, this command will be executed first when restoring data.

mysql> flush logs

View binlog log

mysql> show binlog events in 'binlog.000003';

Tips: When initializing the MySQL container, add the parameter --binlog-rows-query-log-events=ON. Or modify the /etc/mysql/my.cnf file in the container, add the parameter binlog_rows_query_log_events=ON, and then restart the mysql container. This will add the original SQL to the binlog file.

Recover Data

Take back the passage from the example above.

The scheduled backup is performed at 8 pm, but the database is dropped at 9 pm. Therefore, the data within the hour from 8 pm to 9 pm will not be backed up. .

First, after entering the mysql container, switch to the /var/lib/mysql directory and check the creation date of the binlog file

cd /var/lib/mysql
ls -l
...
-rw-r----- 1 mysql mysql 729 Jun 19 15:54 binlog.000001
-rw-r----- 1 mysql mysql 1749 Jun 19 18:45 binlog.000002
-rw-r----- 1 mysql mysql 1087 Jun 19 20:58 binlog.000003
...

From the file date, we can see that the time is 2020-06-21, and the last update time of binlog.000002 file is 18:45, so the backup at 8 pm must contain the data of binlog.000002;
The last update date of binlog.000003 is 20:58, so the data we need to restore = the full backup at 8 pm + the data before 20:00 of binlog.000003 - the time when the drop database command was executed.

Restore command format:

mysqlbinlog [options] file | mysql -uroot -proot_password database_name

Common parameters of mysqlbinlog:

--start-datetime start time, format 2020-06-19 18:00:00
--stop-datetime end time, same format as above
--start-positon starting position (need to view binlog file)
--stop-position End position, same as above
...

Before restoring the backup data and binlog data, it is recommended to log in to MySQL and execute flush logs to generate a new binlog log, so that you can focus on the binlog file that needs to be restored.
First we need to check the binlog log to see where drop database operation was performed:

mysql> show binlog events in 'binlog.000003';
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000003 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.20, Binlog ver: 4 |
| binlog.000003 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000003 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000003 | 235 | Query | 1 | 318 | BEGIN |
| binlog.000003 | 318 | Rows_query | 1 | 479 | # INSERT INTO `product_category` SET `name` = 'Bedding' , `create_time` = 1592707634 , `update_time` = 1592707634 , `lock_version` = 0 |
| binlog.000003 | 479 | Table_map | 1 | 559 | table_id: 139 (hotel_server.product_category) |
| binlog.000003 | 559 | Write_rows | 1 | 629 | table_id: 139 flags: STMT_END_F |
| binlog.000003 | 629 | Xid | 1 | 660 | COMMIT /* xid=2021 */ |
| binlog.000004 | 660 | Anonymous_Gtid | 1 | 739 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000004 | 739 | Query | 1 | 822 | drop database hotel_server /* xid=26 */ |
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------

According to the above log, we can see that the drop database operation was performed at End_log_pos= 822 , so the range of binlog recovery is 2020-06-19 20:00:00 - 660 . Why 660? Because the commit of the previous transaction of drop database is at position 660, the command is as follows:

mysqlbinlog --start-datetime=2020-06-19 20:00:00 --stop-position=660 /var/lib/mysql/binlog.000003 | mysql -uroot -proot_password datbase_name

If your range includes position 822, then the drop database command will be executed for you. If you don’t believe it, try it?
After executing the above command, your data will be restored to the state before drop database ! Happy or not, excited or not!

Summarize

Because scheduled backup of MySQL is a necessary task in the production environment. It is very commonly used. So I couldn’t wait to write a blog. Of course, I am also very grateful for the help of my colleagues. This article has been written for three days, because I am constantly trying and erroring and constantly updating the article. Avoid writing down incorrect knowledge points. If this helps you, please follow me! Thanks.

The above is a brief analysis of the details of the MySQL scheduled backup task. For more information about the MySQL scheduled backup task, please pay attention to other related articles on 123WORDPRESS.COM!