1. Quote
MySQL is a widely used relational database management system. For any enterprise or individual, data security and continuity are crucial. Backup and recovery are one of the important means to protect data security and achieve business continuity. This article will introduce the importance of MySQL backup and recovery, the selection of backup strategies, the use of backup tools, and the steps and precautions in the recovery process.
2. Importance of data backup
1. Data loss risk
The MySQL database may face a variety of risks, such as hardware failure, human misoperation, natural disasters, malicious attacks, etc. These factors can lead to data loss. Without backup, once the data is lost, it will be very difficult to restore the data, and it may even be impossible to completely restore it.
2. Business continuity
For many organizations and businesses, continuous availability of databases is critical. If a database fails or data is lost, business operations can be severely impacted, resulting in downtime, data inconsistencies, and reduced customer satisfaction. By backing up data regularly, you can quickly restore your database and reduce the risk of business interruption, thereby maintaining business continuity.
3. Data consistency guarantee
Backups are not just about protecting data from loss but also ensuring data consistency during data recovery. Backup can capture the state of the database at a certain point in time during the backup, so that data consistency can be ensured during recovery and avoid data corruption or incompleteness.
4. Disaster recovery
Unpredictable events such as natural disasters, man-made accidents or malicious attacks may cause complete or partial damage to the database. In this case, backup is the key to restoring the database. With backup, you can quickly restore your database and reduce the impact of disasters on your business.
5. Data analysis and decision support
Backing up data is important for data analysis, reporting, and decision support. Backup data can be used to generate historical data reports, trend analysis, business insights, etc. These data analysis and decision support efforts are critical to an organization’s strategic planning and business growth.
3. percona-xtrabackup physical backup
1. Install xtrabackup
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.27/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.27-1. el7.x86_64.rpm yum -y install percona-xtrabackup-24-2.4.27-1.el7.x86_64.rp
2. Full backup and recovery process
Create a folder for file backup
mkdir -pv /xtrabackup/test
backup database
innobackupex --user=root --password='database password' /xtrabackup/test
After backing up the database, we need to view the files after the backup
cd /xtrabackup/test/2023-11-12_18-19-25 ls
After the above operations are completed, we can proceed with data recovery
Before restoring we first stop the database
systemctl stop mysqld
After the database stops, we need to clean up the environment
rm -rf /var/lib/mysql/* rm -rf /var/log/mysqld.log rm -rf /var/log/mysql-slow/slow.log
After the environment cleanup is completed, we can merge the log verification and restore the data.
innobackupex --apply-log /xtrabackup/test/2023-11-12_18-19-25
After merging logs to verify recovery, we can officially proceed with data recovery.
innobackupex --copy-back /xtrabackup/test/2023-11-12_18-19-25
After data recovery, we also need to authorize the directory
chown mysql.mysql /var/lib/mysql -R
After completing the above operations, we can start the database service and initialize the database at the same time
systemctl start mysqld
After starting the database, we can log in to the database to view it.
3. Incremental backup and recovery process
Principle: Each time you back up the new data generated from the last backup to now
Prepare test libraries and tables
mysql> create database testdb; Query OK, 1 row affected (0.00 sec) mysql> use testdb; Database changed mysql> create table test(id int); Query OK, 0 rows affected (0.00 sec) mysql> insert into test set id=1; Query OK, 1 row affected (0.00 sec)
Full backup first: Monday
innobackupex --user=root --password="database password" /xtrabackup/test
Then perform incremental backup: Tuesday and Wednesday
Before doing incremental backup, I first need to add data to the table in the database
mysql> insert into testdb.test values(2);
After adding data, perform incremental backup (Tuesday)
innobackupex --user='root' --password='P@ssword1' --incremental incremental backup file storage directory --incremental-basedir=full backup file storage directory
Add Wednesday’s data to the table in the database
mysql> insert into testdb.test values(3);
After adding data, perform incremental backup (Wednesday)
innobackupex --user='root' --password='P@ssword1' --incremental incremental backup file storage directory --incremental-basedir=first incremental backup file storage directory
After the above operations are completed, we can proceed with data recovery
Like a full backup, we also need to stop the database service before we restore it.
systemctl stop mysqld
Clean up the environment as well
rm -rf /var/lib/mysql/*
Verify data, merge data
Verify the full backup of the database first
innobackupex --apply-log --redo-only full backup data path
Roll back the backup content in order (Monday, Tuesday, Wednesday)
innobackupex --apply-log --redo-only full backup path --incremental-dir=incremental backup data order, Monday, Tuesday, Wednesday
Data recovery
innobackupex --copy-back full backup path
Authorization data storage path
chown -R mysql.mysql /var/lib/mysql
Start database
systemctl start mysqld
4. Differential backup and recovery process
Insert data into the database first
mysql> create database testdb; mysql> use testdb; mysql> create table test2(id int); mysql> insert into test values(1); mysql> select * from test;
Take a full backup (Monday)
innobackupex --user='root' --password='P@ssword1' /xtrabackup/demo/
Add data (Tuesday)
mysql> insert into testdb.test2 values(2);
After adding data, perform a differential backup (Tuesday)
innobackupex --user=root --password="password" --incremental /xtrabackup --incremental-basedir=/xtrabackup/full backup directory
Add Wednesday’s data
mysql> insert into testdb.test2 values(3);
Perform differential backup as above (Wednesday)
innobackupex --user=root --password="password" --incremental /xtrabackup --incremental-basedir=/xtrabackup/full backup directory
After the above operations are completed, our backup is complete and we can restore the data.
Before restoring, we first stop the database and then clean up the environment
systemctl stop mysqld rm -rf /var/lib/mysql/*
Next we can perform data verification and data merging
Verify full backup data first
innobackupex --apply-log --redo-only /xtrabackup/full backup directory (Monday)
Re-verify differential backup data
innobackupex --apply-log --redo-only /xtrabackup/full backup directory (Monday) --incremental-dir=/xtrabacku/a differential backup
Data recovery
innobackupex --copy-back /xtrabackup/full backup directory
Since all the data has been merged into the full backup, the path filled in is the path of the full backup.
4. Conclusion
MySQL backup and recovery are important measures to protect data security and achieve business continuity. By choosing an appropriate backup strategy, using appropriate backup tools, and following correct recovery steps and considerations, you can ensure data security, consistency, and availability. I hope this article will help you understand and implement MySQL backup and recovery, and can play a role in protecting data and ensuring business continuity.
If you have other questions about MySQL backup and recovery or need more in-depth study, it is recommended that you continue to explore MySQL’s official documentation and related resources to obtain more guidance and practical experience. I wish you success in using MySQL backup and recovery!