Alibaba Cloud Database Data Backup and Export Tutorial!!
As the "heart" of application development, the database is the core asset of the entire business. Running business in the cloud, the most afraid thing is not that there is a Bug in the code, but that the database is blackmailed by hackers and employees are not careful.
DROP DATABASE
Mistreat, or because of program logic vulnerabilities to wash the data clean.
On Alibaba Cloud, the most commonly used database service is
RDS MySQL
. Many people think that if the cloud database comes with high availability, it will not lose data. This is a typical misunderstanding-high availability prevents hardware failures and cannot prevent human misoperation. Only hand grip
Backup
and
Export file
, the heart is really bottom.
Today's tutorial is directly loaded with hard goods, taking you to use the safest and most intuitive method to handle the automatic backup settings of Aliyun RDS, manual backup, and how to safely export data to a local computer.
Core principle: backup and export are not the same thing
Before you start, take 10 seconds to sort out two confusing concepts that determine whether you can save your life at a critical moment:
Data backup (physical/logical backup): This is automatically run by Alibaba Cloud background. The backup file is stored in Alibaba Cloud's internal secure storage. You cannot double-click to open the backup file. Its function is "disaster recovery", such as allowing the database to return to the state of 3 pm yesterday with one click.
Data export (generating. SQL files): This is to turn the table structure and data in the database into SQL scripts that humans can understand and download to your own computer or development environment. Its role is "migration, local debugging or partial archiving".
The first stage: configure automatic backup (worry-free "regret medicine")
Alibaba Cloud RDS enables automatic backup by default, but the official default retention time is usually very short (for example, 7 days). If it is a critical production environment, it must be reconfigured for business compliance.
1. Adjust the automatic backup strategy
Log on to the Alibaba Cloud console, search for RDS at the top, and enter the ApsaraDB RDS console.
In the left-side navigation pane, click Instance List, select the region where your database is located (for example, China East 1), and click your instance ID to go to the management page.
In the left menu bar, find and click Backup Restore.
On the right side of the page, click the Backup Settings tab.
2. Recommended configuration of key parameters:
After clicking "Edit", you will see the following core options, which are recommended:
Physical backup frequency: It is recommended to select all days of the week (backup every day). If the amount of data is very large, in order to save storage fees, at least Monday, Wednesday, Friday and Sunday should be checked.
Backup time: select a business low peak period, such as 02:00 - 03:00 a.m. Although physical backups have little impact on performance, low-peak operations are the most prudent.
Backup retention days: 7 days by default. It is strongly recommended to change to 30 days or 180 days (depending on industry compliance requirements.
fixed). In many cases, employees delete data by mistake, and it is often discovered after two weeks. If they only keep it for 7 days, it will really evaporate completely.
Log backup (local log/binlog): must be enabled! Only when log backup is enabled can Alibaba Cloud support point-in-time recovery (PITR) ". This means that you can accurately restore the database to any minute in the past 14 days.
Phase II: Temporary manual backup (required before major changes are made)
When you are ready to release a new version of the code, you want to execute it on a large table.
ALTER TABLE
, or to modify user data in batches, you must manually make a full backup immediately.
The backup and recovery page of the RDS instance is still displayed.
Click the Manual Backup button in the upper right corner of the page.
Backup Type: Select a physical backup (the fastest and most stable backup).
Backup policy: Select snapshot backup or regular backup (default).
Give the backup a comment name, such as 20260602_temporary backup before release.
Click OK. At this time, you can go to the data backup list and see a "running" task. when the progress is 100, you will get your death-free gold medal.
The third stage: data export to local (become. SQL file)
If you need to pull online data locally for development and debugging, or if you need to provide a copy of offline data to your partner, you need to use the export function. The most recommended and secure export method for Alibaba Cloud is through
DMS (Data Management Service)
.
1. Enter the DMS export console
In the upper-right corner of the RDS instance details page, click Log on to the database. This will bring you into Aliyun's DMS system in a secret-free way.
After logging in successfully, in the database instance tree on the left, find the database you want to export and right-click it.
In the pop-up menu, select Export-> Database Export (or select Data Schema-> Export from the top navigation bar).
2. Configure the export task
After entering the export ticket page, fill in the following guidelines:
Job Name: a name, such as local test data export.
Export Type: Select the data (if only the table structure, select "Structure"; usually select "Structure and Data").
Database/Table: Select the database you want to export. If you don't need the whole library, just check some specific tables after expansion.
Export Format: Select SQL without a doubt.
Mandatory character set: It is recommended to keep the default, or explicitly select the UTF8mb4 (to prevent emoji symbols from being garbled).
3. Submit application and download
Click Submit Application at the bottom.
Enterprise-level attention: If your Aliyun account is configured with a security approval process, you need an administrator or click "Approve" on the DingTalk ". If it is a personal account, the system will automatically pass in seconds.
After the approval is passed, DMS will run the task frantically in the background to help you.
Packing.
When the task completes, the status changes to Success ". In the job details, there will be a button to download the export file. Click on it to download a. zip package (which is a standard. SQL file after decompression) to your local computer.
The fourth stage: a large number of export "invisible pit" and the solution
If your database is very large (for example, more than 5GB or a single table has tens of millions of data), directly using the above DMS export may encounter two problems:
DMS limits export size
, or
Export causes online database CPU to soar
.
For large amounts of data, Jianghu veterans generally use these two schemes:
Scheme A: Download the official physical backup file and use the tool to restore it locally.
Alibaba Cloud allows you to directly download the physical backup (xb format/tar format) that is automatically typed in the background.
In the Backup Recovery-> Data Backup list, find a backup.
Click Download on the right (if it is a free version, you may need to convert to a downloadable link first).
Download it to a local Linux server and use open source tools to extract and restore Percona XtraBackup. This method is the most time-saving, suitable for dozens of g or even hundreds of g of data, do not need to go slow SQL export.
Scenario B: Use the command line's own
MySQL dump
(Recommended for programmers)
If your local computer can connect to Aliyun database through the external network (public network IP), you can directly use the command line to guide the local terminal, which is extremely efficient.
Bash
mysqldump -h your RDS extranet connection address-u your database user name-p -- default-character-set = utf8mb4 -- single-transaction -- skip-lock-tables database name> /path/to/local_backup. SQL
💡Explanation of command line parameters:-single-transaction: must be added! Its function is to turn on snapshot reading and will not lock your table during export. If it is not added, online business may be directly stuck and error reported during export. -- skip-lock-tables: Prevent table lock failures from causing interruptions when there is no administrator permission.
Summary of Pitfall-Avoidance Mnemonics
Database operation and maintenance, steady word head. Finally, I'll give you four self-defense tips to implement your habits:
Automatic backup change days: The first thing to do for newly purchased RDS is to change the backup retention days from 7 days to 14 days or 30 days.
Manual before a big fight: Before entering the production database to type UPDATE or DELETE, go to the console and click "Manual Backup", which is not less than two minutes.
DMS export is the most secure: when exporting data in teams, try
Go to DMS work order. It has operation log marks, and can automatically desensitize sensitive fields (such as user mobile phone number, ID card) to prevent data leakage.
Verify backups regularly: Don't wait for something to happen before you find out the backup file is bad. Every six months, try to pull the backup file to a test instance to restore it to ensure "exercise success".
