Seamless database migration: Use GCP database migration service to achieve smooth MySQL cloud migration
In the architecture evolution of Internet companies, the most thrilling and treading on thin ice task is definitely "database migration.
Traditional database migration is like changing the tires of a racing car on the highway. In order to ensure data consistency, the old practice is usually to suspend the shutdown announcement at 3 a.m., cut off all front-end traffic, and then use
MySQL dump
Export a large SQL file with tens of GB, and then slowly transfer and import it to the cloud. The result is often because the network jitter is interrupted or the import speed is too slow, resulting in the dawn is not finished, and finally can only be forced to roll back under the high pressure of the whole staff, not only let the research and development team lose all hair, but also seriously affect the company's core business.
In the modern ecology of Google Cloud(GCP, Google Cloud), there is a dimension reduction strike artifact designed to break this deadlock, called
Database Migration Service (DMS)
.
Its core logic is very pure:
Fully managed, zero downtime, seamless real-time synchronization
. By combining MySQL's native Binlog replication technology, DMS can continuously move the data of the old database to the cloud like running water in the background while opening the online business as usual to pick up customers. After the data on both sides are completely aligned, you only need to pick a calm afternoon and take a few seconds to gently change the connection string at the front end to complete the "smooth cloud" at the large factory level ".
Today we don't pull complicated cryptographic formulas and reject any nonsense. Cut directly from the hard core actual combat and take you from the local self-built MySQL to Google Cloud.
Cloud SQL for MySQL
The seamless migration walkthrough.
Phase 1: Deep disassembly, "two-stage physical world model" of DMS live migration"
Before you go to the console and click the mouse, you must build the physical operation model of the underlying DMS in your mind, otherwise you will definitely be confused by the complex network and permission configuration.
The full incremental live migration of DMS essentially builds a highly available data transmission pipeline at the bottom of the Google intranet. The entire life cycle is divided into two core stages:
Phase 1: Inventory Dump Initialization (Full Dump): When you start a migration task, DMS packs and clones all the table structures and data in the current inventory of the source database without affecting the normal reading and writing of your source database, and quickly delivers them to the Cloud SQL target database in the cloud.
Phase 2: Incremental water catch-up (CDC/Binlog replication): DMS will seamlessly switch to continuous data capture (CDC) mode when the stock is moved. It will be transformed into a standard MySQL Slave (from the library), staring at the binlog (binary log) of the source library. As long as the online user places a new order and changes a password, DMS will send this in milliseconds.
The incremental modification is "caught" and replayed in the cloud target library.
Core Architecture Conclusion: In these two phases, your old database stays open for the entire time, without downtime for even a second. The new cloud library is like a shadow of the old library, following suit until the data time difference (Replication Lag) on both sides is zero.
The second stage: the eve of actual combat-pulling up the "pass" in the self-built source database"
In order for DMS to move bricks legally, we must first go back to our hometown (the local self-built MySQL source library) to do some underlying configuration.
1. Open Binlog log (inject real-time copy soul)
Open your source library's
my.cnf
or
my.ini
configuration file, make sure that the following lines are not commented out and that the parameters are correct:
Ini, TOML
[mysqld]
log-bin=mysql-bin
binlog_format = ROW# must be in ROW format. DMS relies on this to accurately identify changes in each row of data.
server-id = 100001# Specify a unique cluster ID for the source database
expire_logs_days = 7 # Binlog is kept for at least 7 days to prevent DMS from being automatically deleted before it can be read.
After the change, remember to restart your self-built MySQL to make the configuration take effect.
2. Create a DMS dedicated "connector" account
Connect to the source database, and type the following SQL command of the factory specification to create a security account with exclusive replication permissions for GCP DMS:
SQL
-Create an account called gcp_dms and allow it to connect from anywhere (or specify GCP intranet segment)
CREATE USER 'gcp_dms'@'%' IDENTIFIED BY 'YourHardPassword123! ';
-- Grant basic data read and cluster replication permissions (the principle of minimum permissions for large factories)
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'gcp_dms'@'% ';
-- Refresh permissions, let the secret code take effect instantly
FLUSH PRIVILEGES;
The third stage: actual combat exercise-hand-in-hand configuration of DMS migration pipeline
The environment is ready, we log in
GCP Console
, search and enter
Database Migration (database migration)
Page.
Step 1: Create a Migration Job (Migration Job)
Click Create Migration Job at the top ":
Job name: mysql-to-cloudsql-prod.
Source Database Engine: Select MySQL.
Target database engine: Select Cloud SQL for MySQL.
Migration Type: Do not hesitate to select Persistent (Continuous) ". Note: Only Continuous is selected for seamless and smooth migration with incremental synchronization. If One-time (one-time) is selected, the data will stop abruptly after being moved, and zero downtime switching cannot be achieved.
Step 2: Define the Source Connection Profile
Here, we will tell Google the parameters matched in the second stage in our hometown:
Connection profile name: source-local-mysql.
Host name/IP and port: fill in the public network IP or dedicated intranet IP of your local MySQL, and the port is 3306 by default.
Username and Password: Fill in the gcp_dms and password exactly.
Step 3: Define the cloud target library (Destination)
Where are you going to move the data? DMS provides an extremely invincible function here--
One click in the background to help you create a new Cloud SQL target library directly.
.
Enter the cloud DB instance ID (if cloudsql-mysql-prod) you want to create a new one.
Select the version of MySQL (it is recommended to be consistent with the source database, such as 8.0).
Select a region (e. g. asia-east1 Taiwan).
Set the strong password of the cloud root account and select the machine specification (for example, 2 cores and 4GB of memory, check "High Availability and High Defense" in the production environment to enable cross-zone dual-active disaster tolerance).
Step 4: Connectivity Method-Cut off hacker snooping
This is the easiest place to step on the pit and get stuck. How does Google Cloud's DMS cross the firewall to connect to your local machine? DMS provides three standard connectivity methods:
Advanced Recommendation: Reverse SSH Tunnel (Reverse SSH Tunnel):DMS will generate a very lightweight intermediate VM virtual machine in the background and give you a series of SSH commands. You only need to run this string of commands on the local server to pull up a fully encrypted, firewall-penetrating secret one-way tunnel between the local and GCP intranets. You don't need to open 3306 ports to the world on the company firewall at all, and the safety factor is directly full.
Alternative: IP Allow List: Deadlock the set of official public network IP addresses prompted by GCP DMS into the whitelist of the outermost hardware firewall in your local computer room.
After the connectivity test is passed, click at the bottom
"Create and start a job (Create &
amp; Start Job)"
.
The fourth stage: the moment of witnessing miracles-online real-time catch-up and ultimate cut-off
After clicking Start, the huge elastic distributed computing power of DMS will be instantly awakened. Back on the migration job list, you'll stare at the status bar to see its magical lifecycle evolve:
The state changes from Creating to Starting and then enters the Full dump in progress (which means it is desperately carrying the old data you have accumulated in the past few years).
After the inventory is moved, the status will change to green CDC in progress (continuous replication).
At this point, take a look at the console
"Replication Latency (Replication Lag)"
Indicator. At first, the delay may be a few minutes due to new changes during stock handling; but soon, with Google's powerful intranet bandwidth firepower on,
Replication Lag will be completely zeroed (0 seconds)
.
This means that at this moment, every piece of data in your local old library and the data in Google Cloud SQL's new library have reached
Absolute, pixel-level, full real-time synchronization
.
The five-step method of "golden cutting flow" operation of large factory level standard:
When the delay stabilizes at 0 seconds, we can pick a business trough (for example, when users have the least access at 4 pm) and make the final cut-off switch. The whole process takes only 10 seconds:
Suspend temporary read-only at the front end: execute SET GLOBAL read_only = 1 in the background of your Web application or in MySQL source library;, force the old library to "read-only state". This action is used to absolutely ensure that no new order can be secretly written into the old library within a few seconds of switching, thus causing the data on both sides to be disconnected.
Wait for the final check: stare at the DMS console and wait for 5 seconds to completely replay the last bit of Binlog tail being transmitted in the cloud to ensure that the data on both sides are not bad in the first phase.
Click the "Upgrade (Promote)" big button: In the GCP DMS console, decisively click "Upgrade (Promote)" at the top ". Bottom Insider: This action will instantly cut off the slave copy relationship between the new library and the old library in the cloud. The Cloud SQL target library will be separated from the slave library identity in an instant and evolve into a completely independent, readable and writable top-level master library (Master).
Replace the connection string: change the database connection IP in all your back-end Web applications and microservice configurations from the original old self-built library IP to the new Cloud SQL public/intranet IP with one-click modification.
Restart the application and revive it completely: the front-end App is reconnected to the new cloud library and is read-only. New orders began to fall wildly on Google's cloud with extremely high performance.
Perfect end of work!
After the whole battle, the client only experienced a very slight and short "unable to place an order/report an error" prompt between steps 1 and 4. The website as a whole never posted a small black house announcement, with zero data loss and a complete victory in Shangyun.
The fifth stage: the history of blood and tears under the transnational database architecture.
This fully managed migration solution is extremely elegant to use. But to survive in a truly enterprise-class, highly concurrent production environment, as a chief data architect, you must guard against the following two hidden pits that arise from differences in underlying technical details:
1. The fatal "time zone and character set (Collation) paranormal confusion"
After migrating, many teams found that the old code that had been running online for half a year was inexplicably 8 hours less in all China time and local time after connecting to Cloud SQL in the cloud. Or some user comments containing uncommon words and Emoji expressions have directly turned into garbled codes or reported errors.
Reason disassembly: The local self-built MySQL default time zone usually follows the host (for example, it is set to the system time zone CST), while GCP Cloud SQL defaults to global compliance, and its underlying main time zone is forced to be locked to UTC. The default character set may also be slightly different from your old library.
Large factory standard pit avoidance operation: before clicking DMS to start migration, go to Cloud SQL's "configuration changes", find the database flag (Database Flags), explicitly add default_time_zone = '08: 00 '(or fit your business's main time zone), and forcibly align the character set to utf8mb4. The first iron rule to prevent online code from reporting errors is to block the underlying parameters at the source.
2. After DMS migration, the "deduction hourglass idle trap"
Many novices happily went off work to eat hot pot after upgrading (Promote) the database and running their business to the cloud. As a result, when I looked through the bill at the end of the month, I found that there was an inexplicable and expensive DMS resource occupancy fee.
Hard core stop loss suggestion: when you click "Promote", DMS, a migration job, has not been completely physically destroyed. it still occupies temporary computing resources and network interface in the cloud background, and the hourglass is still running wildly.
Correct posture: After confirming that the new cloud library runs smoothly for 24 hours and that no rollback is required, you must immediately return to the DMS console, select the completed migration job, and click Delete without brains ". Don't worry, deleting the job will never have any impact on the Cloud SQL database that you have evolved successfully. Only in this way can the deduction be completely stopped.
Summary
Using GCP Database Migration Service for seamless database migration, the core industrial essence lies in 16 words:
Configure Locks,
The tunnel is opened, Binlog is chasing, and the stream is upgraded.
.
You have completely bid farewell to the manual export and handling of the past three o'clock in the morning. All network communication, large-scale data packaging, real-time status synchronization to Google's top fully managed security brain to clone. The core database assets are soldered to the cloud's high-security foundation smoothly and smoothly, which is the most elegant and authentic architect clearance posture in the modern cloud-native era.