分类

Posted in

使用数据库迁移服务迁移 MySQL | Google Cloud 博客_AI阅读总结 — 包阅AI

by baoyueai

包阅导读总结

1. 关键词：Database Migration Service、MySQL、Migration、Cloud Storage、mysqldump

2. 总结：文本介绍了使用数据库迁移服务从本地 MySQL 实例迁移特定数据库的四个步骤，包括使用 mysqldump 进行初始一致快照、将转储文件转移到云存储桶、设置复制过滤标志、配置持续迁移作业，还详细说明了 mysqldump 的相关选项和特权需求。

3. 主要内容：

– 数据库迁移步骤

– 使用 mysqldump 进行初始一致快照

– 需使用特定选项：single-transaction、master-data=1 或 source-data=1（版本> 8.0.26 ）、set-gtid-purged=AUTO

– 所需特权：REPLICATION_SLAVE（若用户设置复制）

– 将手动转储文件转移到 Google Cloud Storage 桶

– 若迁移到新或现有 Cloud SQL for MySQL 实例，设置复制过滤标志

– 配置数据库迁移服务持续迁移作业并从云存储桶进行初始加载

– mysqldump 执行的语句

– 包括 FLUSH LOCAL TABLES 等一系列操作

– 关于版本的特殊说明

– 源 MySQL 实例版本大于 8.0.26 时，使用 source-data=1 替代 master-data=1

思维导图：

文章地址：https://cloud.google.com/blog/products/databases/using-database-migration-service-for-mysql/

文章来源：cloud.google.com

作者：Holly Xu,Somdyuti Paul

发布时间：2024/7/2 0:00

语言：英文

总字数：1600字

预计阅读时间：7分钟

评分：82分

标签：数据库,开发者与实践者

以下为原文内容

本内容来源于用户推荐转载，旨在分享知识与观点，如有侵权请联系删除 联系邮箱 media@ilingban.com

To migrate one or more specific databases from an on-prem MySQL instance, there are four steps involved:

  1. Use mysqldump to take an initial consistent snapshot.

  2. Transfer the manual dump file to Google Cloud Storage bucket.

  3. If migrating to a new or existing Cloud SQL for MySQL instance, set the replicate_do_db replication filter flag.

  4. Configure a Database Migration Service continuous migration job and perform initial load from the Cloud Storage bucket.

Let’s go through each of these steps using the scenario above.

1. Use mysqldump to take a consistent initial snapshot

You’ll start by using mysqldump to take an initial dump at a consistent snapshot and get the binlog coordinates used to set up replication. You’ll need to use the following three options:

  • single-transaction – takes a consistent snapshot in a repeatable read transaction

  • master-data=1 – writes a “CHANGE MASTER TO” statement to the dump file

  • set-gtid-purged=AUTO – writes GTID info based on the gtid_mode on the source instance

The privileges needed for the replication user/user performing mysqldump are:

With those options specified, the statements that will be executed by mysqldump are as below:

  1. FLUSH LOCAL TABLES

  2. FLUSH TABLES WITH READ LOCK

  3. SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

  4. START TRANSACTION

  5. SHOW VARIABLES LIKE ‘gtid_mode’

  6. SELECT @@GLOBAL.GTID_EXECUTED

  7. SHOW MASTER STATUS

  8. UNLOCK TABLES

  9. transaction continues to dump all tables and views

In addition, REPLICATION_SLAVE is also needed if the same user sets up replication from the Database Migration Service.

If your source MySQL instance is version > 8.0.26, use source-data=1 instead of master-data=1. The above mysqldump options take a consistent data dump, capturing the GTID state at the beginning of the backup (for instances that have GTID enabled) and recording the binlog coordinates from which to start replication.

2. Transfer the manual dump file to the Cloud Storage bucket

The below mysqldump command takes a manual dump of the “test’’ database and transfers it to the Cloud Storage ‘mysqldumpsom’ bucket.