lingudcloud Details: Alibaba Cloud RDS MySQL Database Backup and Cross-regional Disaster Recovery
Who hasn't experienced several thrilling moments in Internet projects?
"mistakenly deleting the production database", "the optical cable in the computer room was cut off" and "blackmail virus encryption", these accidents sound like jokes, as long as they happen once, a company can be shut down directly.
Many people bought Aliyun RDS MySQL and thought that everything would be fine if they ordered "automatic backup. But have you ever thought about what you should do if the city where the entire cloud computer room is located encounters an extreme natural disaster, or if your Aliyun account is stolen or hackers delete the cloud backup together?
Today, I don't want to talk about the concept of virtual, but I want to go directly to the hard core dry goods. Take you
Build a truly life-saving RDS MySQL backup and cross-regional disaster recovery system from scratch
. The whole actual combat exercise, not a word of nonsense.
Core Disaster Recovery Logic: What is our goal?
Before we start, let's take a look at the "three lines of defense" architecture we want to implement:
Plaintext
production database]
│
├── defense line 1 ──> [local automatic backup] (preventing accidental deletion and daily failure)
│
├── defense line 2 ─> [cross-region backup synchronization] (disaster of city-level computer room)
│
ND-Line of Defense 3-> [Different Places Pull Up and Restore] (Actual Combat Exercise to Ensure Backup Is Really Available)
Step 1: Configure local automated backup (Line 1: Daily Life)
Alibaba Cloud RDS enables backup by default, but the default configuration is often "not long enough" or "not dense enough". We need to optimize it manually.
1. Adjust backup strategy
Log on to the Alibaba Cloud console and enter the ApsaraDB for RDS ".
Click "Instance List" in the left menu to enter your MySQL instance.
In the navigation pane on the left, find Backup and Restoration, and click Backup Policy Settings on the right ".
2. The key parameters are adjusted as follows:
Data backup cycle: It is strongly recommended to check every day (by default, backup may be every few days). Don't risk saving that storage fee.
Data backup retention days: At least 30 days is recommended in the production environment. If the industry has compliance requirements (e. g. financial, medical), it needs to be adjusted higher.
Log Backup (Binlog): Must be enabled! This is the key to your "accurate to second recovery. The Binlog retention time is recommended to be at least 7 days.
Step 2: Turn on cross-regional backup synchronization (Line 2: Prevent city-level disasters)
If your production database is in the "Beijing" computer room, then your local backup also exists in Beijing. In the event of a rare failure of the cloud infrastructure in the entire Beijing region, your backup will not be available at all.
At this time, we need to automatically synchronize the backup to another city (such as "Shenzhen" or "Shanghai") thousands of kilometers away.
1. Enable cross-region backup
On the Backup and Restoration page, switch to the Cross-Region Backup Settings tab.
Click "Repair"
Change settings "and change the status to" on ".
destination region: choose a city that is physically far away from your production room. For example, production in Hangzhou, Beijing or Shenzhen.
2. Backup type selection
Select Cross-region data backup and Cross-region log backup ".
Note: After the local backup is completed, cross-region synchronization will be asynchronously transmitted by the Alibaba Cloud background, which will not occupy the intranet bandwidth and performance of your production database.
Step 3: Actual Combat Exercise-Pull up and Restore Data in Different Places (Key)
No matter how good the backup is, if you haven't practiced recovery, it's just a bunch of "Schrodinger binaries". You have no idea if you can use it at a critical moment.
Now simulate the scene:
Beijing production library completely paralyzed, we need to use cross-regional backup to pull up a new database in Shenzhen computer room.
Scenario A: Restore to a brand new "clone instance" (safest and most recommended)
Use this method when you need complete, untouched data, or when you need to restore data to a specific point in time a few days ago.
Log on to the Alibaba Cloud RDS console and switch to the destination city of your cross-region backup (for example, Shenzhen).
In the menu on the left, go to Backup and Restore, and click Cross-Region Backup.
Find the backup file you want to restore, or click "restore any point in time".
Key Selection: Restore Historical Data To: Select New Instance ". Time point selection: you can accurately check to the past year, month, day, minute and second. This is the power of Binlog log backup.
Click Next and the system will let you purchase a temporary "clone database".
After payment is enabled, Aliyun will automatically download the remote backup and replay the Binlog log. In about 10 to 30 minutes (depending on the amount of data), a new database containing your historical data will appear alive in the Shenzhen computer room.
Scenario B: Download the backup file and manually restore it to a local or self-built server
If the company does not want to spend more money to open a new instance on the cloud, or you need to download the data to the server of the local office network for data analysis, you can download it manually.
1. Download the file
In the cross-region backup list, find the corresponding data backup and click on the right
“Download”
(If the prompt does not generate a download address, click "Generate Instance Backup Download Address" first, wait a few minutes before downloading).
💡The physical backup file provided by Alibaba Cloud RDS is usually .tar.gz or packaged through Percona XtraBackup tools.
2. Self-built Linux server recovery actual combat (take NRE / XtraBackup as an example)
Suppose you downloaded a physical backup compression package. After decompression, we need to use Percona tools to restore it.
The first step is to install the XtraBackup tool (the version must strictly correspond to your MySQL version,
MySQL 8.0 must be 8.0 in XtraBackup).
The second step is to execute the unzip and prepare (Prepare) command:
Bash
#1. Create a clean data directory
mkdir -p /var/lib/mysql_recovery
#2. Prepare the downloaded decompressed file (assuming it has been decompressed) data
xtrabackup --prepare --target-dir=/var/lib/mysql_recovery
The role of this step is to roll back those transactions that have not yet been committed at the time of backup, and write the committed transactions to disk to ensure data consistency.
The third step is to modify the permissions and modify the MySQL configuration file:
Bash
chown -R mysql:mysql /var/lib/mysql_recovery
In your self-built MySQL
my.cnf
In,
datadir
Point to this
/var/lib/mysql_recovery
Directory, and then restart the MySQL service, the data is perfectly resurrected.
Production environment to avoid the history of blood and tears
Don't put the data and backup under the same Aliyun account! If you encounter the legendary "employee deletion and running away" or the "enterprise boss account theft" that occurs frequently in the north and south of the great river, the first thing a hacker does when he enters is to completely release all your RDS and backups. The ultimate security solution: under another completely independent Aliyun account controlled by senior management, open a low-profile lightweight server or OSS storage, and pull a database backup across accounts through automated scripts every day.
Note that the "local storage fee" and "cross-region transmission fee" of Aliyun's free backup quota is limited (usually as large as your database capacity). The excess will be charged a high storage fee. Cross-regional synchronization will also charge a small amount of network transmission fees. Save money tips: For log backup (Binlog), if the business write operation is extremely frequent, the log volume will be extremely terrible. You can appropriately shorten the local log retention time to 3-5 days, lengthen the off-site retention time, and balance cost and security.
Regular drills, even once a quarter, many technical teams write backup scripts and put them on the shelf. As a result, an accident occurred one year later. When downloading the backup, it was found that the backup files were all bad because the versions were incompatible or a parameter was missing from the script that year. The real disaster tolerance is to write "recovery drill" into the KPI of the technical team.
Take precautions and check your Aliyun RDS backup strategy tonight!
