Implementation of mysql backup strategy (full backup + incremental backup)

Recently, the project needs to back up the database data. By consulting various materials, a database backup strategy was designed. After one week of debugging and operation, it is now in a stable operation state. Now I would like to share my ideas and thank gredn.

Design scenario

1) Incremental backup is performed at 3:00 a.m. from Monday to Saturday, copying mysql-bin.00000* to the specified directory;
2) For full backup, use mysqldump to export the entire database. It is executed every Sunday at 3 am and will delete mysq-bin.00000* left over from last week. The backup operation of mysql will then be retained in the bak.log file.

Technical Points

Mysqldump, mysqlbinlog, crontab

Server Information

Host: centos7; Database: mysql5.7

Preparation

Enable binlog logging (1) Create a new directory and execute:

#mkdir /home/mysql
#cd /home/mysql
#mkdir mysql-bin. #Incremental log file directory

(2) Modify the user/group to which it belongs: (If not modified, MySQL cannot be restarted)

#chown -R mysql.mysql mysql-bin 

(3) Modify the MySQL configuration file and execute:

#vim /etc/my.cnf 

Among them, server-id represents the id of a single node. Since there is only one node here, the id can be randomly assigned to a number. Here the id is set to 1. If there are multiple nodes in the cluster, the ids cannot be the same (for versions below 5.7, you do not need to specify server-id);
log_bin specifies the storage path of the binlog log file. The log file starts with mysql-bin.

(4) Restart MySQL and execute:

#systemctl restart mysqld.service

(5) View the log file:

#cd /home/mysql/mysql-bin 

(6) Enter the database and check the startup effect:

#show variables like '%log_bin%'; 

Write a full backup script (Mysql-FullyBak.sh)

Enter the /home/mysql directory and create a new directory: mkdir backup
Enter the backup directory and create a new daily directory: mkdir backup
Switch to the /home/mysql directory and execute:

#vim Mysql-FullyBak.sh

Parameter Description:
--lock-tables
Lock the currently exported data table instead of locking all tables in the database at once. This option is only applicable to MyISAM tables when the MySQL database engine is used. If it is an Innodb table, you can use the –single-transaction option.
--flush-logs
End the current log and generate a new log file.
--delete-master-logs
Clear old logs to free up space. However, if the server is configured as a mirrored replication master, it is dangerous to delete the MySQL binary log with --delete-master-logs, because the slave server may not have fully processed the contents of the binary log. In this case, it is safer to use PURGE MASTER LOGS.
–quick
This option is useful when exporting large tables. It forces MySQLdump to query the server to get the records directly for output instead of getting all the records and caching them in memory.
--single-transaction
This option submits a BEGIN SQL statement before exporting data. BEGIN will not block any application and can ensure the consistency of the database during export. It only applies to transactional tables, such as InnoDB and BDB. This option is mutually exclusive with the --lock-tables option, since lock-tables will cause any pending transactions to be implicitly committed. To export large tables, use it with the --quick option.
–events
Export events –master-data=2
The parameter --master-data=[0|1|2]
0: Do not record
1: Recorded as a CHANGE MASTER statement
2: The CHANGE MASTER statement recorded as a comment –master-data=2 option will record the name of the new log file after the full backup in the output SQL.
For reference during future recovery, for example, the output backup SQL file contains:
CHANGE MASTER TO MASTER_LOG_FILE='MySQL-bin.000002', MASTER_LOG_POS=106;

Writing incremental backup scripts

Switch to the /home/mysql directory and execute:

#vim Mysql-DailyBak.sh 

Set up crontab for scheduled tasks

(1) Install crontab (already installed by default in centos7):

#yum install crontabs

Service Operation Instructions:

#/bin/systemctl start crond.service //Start service#/bin/systemctl stop crond.service //Shutdown service#/bin/systemctl restart crond.service //Restart service#/bin/systemctl reload crond.service //Reload

Configuration:

#/bin/systemctl status crond.service //Service status

Add automatic startup:

#chkconfig –level 35 crond on

(2) Enter in the command line:

#crontab -e 

Add the corresponding task, save and exit with wq

#Execute the full backup script at 3:00 a.m. every Sunday 0 3 * * 0 /bin/bash -x /home/mysql/Mysql-FullyBak.sh >/dev/null 2>&1
#Make incremental backups from Monday to Saturday at 3:00 a.m. 0 3 * * 1-6 /bin/bash -x /home/mysql/Mysql-DailyBak.sh >/dev/null 2>&1

Note: By default, crontab will notify the user via email after executing a task. To avoid sending messages every time, add /dev/null 2>&1

(3) View scheduled tasks: #crontab -l

Parameters and description:
crontab -u //Set a user's cron service. Generally, the root user needs this parameter when executing this command;
crontab -l //List the details of a user's cron service;
crontab -r //Delete all users' cron services;
crontab -e //Edit a user's cron service;
For example: root can view its own cron settings: crontab -u root -l
For example: root deletes the cron settings of user fred: crontab -u fred -r
Replenish:
(1) You can directly edit the /etc/crontab file, that is, vi /etc/crontab, and add corresponding tasks (for the crontab file of the entire system);
(2) The records of crontab executing scheduled tasks will be written to the file /var/log/cron, and the records are distinguished by account number.

Recovery Operation

The recovery process will also write log files. If the amount of data is large, it is recommended to turn off the binlog function first.
1. Scenario: Suppose at 9 o'clock in the morning, the database is attacked and the entire database is dropped!
2. Recovery ideas:
Use the CHANGE MASTER statement recorded in the full SQL file, the binlog file and its position information to find the incremental part in the binlog file.
Use the mysqlbinlog command to export the above binlog file to a sql file and remove the drop statement.
The complete data can be restored by exporting the SQL file through the full backup file and the incremental binlog file.
3. Recovery steps:

(1) First, decompress the latest full backup file, enter the backup file directory, and execute:

#tar -zxvf XXX.sql.tgz

(2) To view the newly added binlog files after full backup, execute:

#grep CHANGE XXX.sql 

As can be seen from the figure, this is the position of the binlog file at the time of full preparation, that is, line 154 of mysql-bin.000003. Therefore, the data in the binlog files before this file are already included in this full preparation sql file.

(3) Restore the information after line 154 of the mysql-bin.000003 file

Enter the mysql-bin.000003 directory and execute (sysecokit is the database name);

#mysqlbinlog --start-position=154 --database=sysecokit mysql-bin.000003 | mysql -uroot -p -v sysecokit

(4) Export other binlog files (except mysql-bin.000003) to sql files and execute (-d specifies the database):

#mysqlbinlog -d sysecokit mysql-bin.00000X >00Xbin.sql 

(5) Edit the latest 00Xbin.sql with vim and delete the drop statement
(6) To restore the full backup data, execute:

#mysql -uroot -p < XXX.sql

For example: #mysql -uroot -p < 20180716.sql
(7) To restore incremental data, execute (syseco is the database name):

#mysql -uroot -p syseco<00Xbin.sql

For example: #mysql -uroot -p syseco<004bin.sql
Now that everything is done, let's look at the files generated after running for a week:

This is the end of this article about the implementation of MySQL backup strategy (full backup + incremental backup). For more relevant MySQL backup strategy content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!