resource "google_compute_global_address" "private_ip_address" {
  name          = "cloud-vpn-playground-vpc-private-ip-address-pool"
  purpose       = "VPC_PEERING"
  address_type  = "INTERNAL"
  prefix_length = 16
  network       = module.vpc.network_name

  labels = {
    resource-name     = "cloud-vpn-playground-vpc-private-ip-address-pool"
  }
}

resource "google_service_networking_connection" "private_vpc_connection" {
  network                 = module.vpc.network_name
  service                 = "servicenetworking.googleapis.com"
  reserved_peering_ranges = [google_compute_global_address.private_ip_address.name]

  depends_on = [
    google_compute_global_address.private_ip_address
  ]
}

resource "google_compute_network_peering_routes_config" "peering_routes" {
  peering = google_service_networking_connection.private_vpc_connection.peering
  network = module.vpc.network_name

  import_custom_routes = true
  export_custom_routes = true

  depends_on = [
    google_service_networking_connection.private_vpc_connection
  ]
}

$ gcloud compute addresses describe cloud-vpn-playground-vpc-private-ip-address-pool --global --project=project-a
address: 10.41.0.0
addressType: INTERNAL
creationTimestamp: '2025-06-12T09:58:44.079-07:00'
description: ''
id: '********'
kind: compute#address
labelFingerprint: 0ff3YkQet3o=
labels:
  goog-terraform-provisioned: 'true'
  resource-name: cloud-vpn-playground-vpc-private-ip-address-pool
name: cloud-vpn-playground-vpc-private-ip-address-pool
network: https://www.googleapis.com/compute/v1/projects/project-a/global/networks/cloud-vpn-playground-vpc
networkTier: PREMIUM
prefixLength: 16
purpose: VPC_PEERING
selfLink: https://www.googleapis.com/compute/v1/projects/project-a/global/addresses/cloud-vpn-playground-vpc-private-ip-address-pool
status: RESERVED

$ gcloud compute networks peerings list --network=cloud-vpn-playground-vpc --project=project-a
NAME                              NETWORK                   PEER_PROJECT           PEER_NETWORK       STACK_TYPE  PEER_MTU  IMPORT_CUSTOM_ROUTES  EXPORT_CUSTOM_ROUTES  STATE   STATE_DETAILS
servicenetworking-googleapis-com  cloud-vpn-playground-vpc  ne44e34e2b0993819p-tp  servicenetworking  IPV4_ONLY             True                  True                  ACTIVE  [2025-06-12T10:06:24.307-07:00]: Connected.

$ gcloud services vpc-peerings list --network=cloud-vpn-playground-vpc --project=project-a
---
network: projects/project-a/global/networks/cloud-vpn-playground-vpc
peering: servicenetworking-googleapis-com
reservedPeeringRanges:
- cloud-vpn-playground-vpc-private-ip-address-pool
service: services/servicenetworking.googleapis.com

$ gcloud sql instances describe dms-playground-cloud-sql --project=project-a --format="value(ipAddresses)"
{'ipAddress': '34.80.244.8', 'type': 'PRIMARY'};{'ipAddress': '10.41.0.2', 'type': 'PRIVATE'}

$ gcloud sql instances describe dms-playground-cloud-sql --project=project-b --format="value(ipAddresses)"
{'ipAddress': '34.81.254.91', 'type': 'PRIMARY'};{'ipAddress': '10.235.0.5', 'type': 'PRIVATE'}

### Project A から Project B へのルート
resource "google_compute_route" "vpn_psa_route_01" {
  for_each = google_compute_vpn_tunnel.vpn_tunnel

  project             = "project-a"
  name                = "project-a-to-project-b-vpn-psa-route-${each.key}"
  dest_range          = "10.235.0.0/16"
  network             = "cloud-vpn-playground-vpc"
  next_hop_vpn_tunnel = each.value.self_link
  priority            = 1000

  depends_on = [
    google_compute_vpn_tunnel.vpn_tunnel
  ]
}

$ gcloud compute routes list --project=project-a --filter="destRange:10.235.0.0/16"
NAME                                    NETWORK                   DEST_RANGE     NEXT_HOP                                                                       PRIORITY
project-a-to-project-b-vpn-psa-route-0  cloud-vpn-playground-vpc  10.235.0.0/16  asia-east1/vpnTunnels/project-a-to-project-b-vpn-tunnel-00  1000
project-a-to-project-b-vpn-psa-route-1  cloud-vpn-playground-vpc  10.235.0.0/16  asia-east1/vpnTunnels/project-a-to-project-b-vpn-tunnel-01  1000

### Project B から Project A へのルート
resource "google_compute_route" "vpn_psa_route_01" {
  for_each = google_compute_vpn_tunnel.vpn_tunnel

  project             = "project-b"
  name                = "project-b-to-project-a-vpn-psa-route-${each.key}"
  dest_range          = "10.41.0.0/16"
  network             = "cloud-vpn-playground-vpc"
  next_hop_vpn_tunnel = each.value.self_link
  priority            = 1000

  depends_on = [
    google_compute_vpn_tunnel.vpn_tunnel
  ]
}

$ gcloud compute routes list --project=project-b --filter="destRange:10.41.0.0/16"
NAME                                    NETWORK                   DEST_RANGE    NEXT_HOP                                                                       PRIORITY
project-b-to-project-a-vpn-psa-route-0  cloud-vpn-playground-vpc  10.41.0.0/16  asia-east1/vpnTunnels/project-b-to-project-a-vpn-tunnel-00  1000
project-b-to-project-a-vpn-psa-route-1  cloud-vpn-playground-vpc  10.41.0.0/16  asia-east1/vpnTunnels/project-b-to-project-a-vpn-tunnel-01  1000

### Project A の GCE（10.0.0.100）から Project B の Cloud SQL インスタンス（10.235.0.5:3306）への接続確認
$ telnet 10.235.0.5 3306
Trying 10.235.0.5...
Connected to 10.235.0.5.

### Project B の GCE（10.0.1.100）から Project A の Cloud SQL インスタンス（10.41.0.2:3306）への接続確認
$ telnet 10.41.0.2 3306
Trying 10.41.0.2...
Connected to 10.41.0.2.

-- ユーザ作成
CREATE USER 'replicator'@'%' IDENTIFIED BY 'YourStrongPassword';

-- 権限付与
GRANT REPLICATION SLAVE, EXECUTE, SELECT, SHOW VIEW, REPLICATION CLIENT, RELOAD, TRIGGER ON *.* TO 'replicator'@'%';

-- 保存
FLUSH PRIVILEGES;

-- ユーザ確認
SELECT User, Host FROM mysql.user WHERE User = 'replicator';
+------------+------+
| User       | Host |
+------------+------+
| replicator | %    |
+------------+------+
1 row in set (0.00 sec)

-- 権限確認
SHOW GRANTS FOR 'replicator'@'%';
+---------------------------------------------------------------------------------------------------------------------+
| Grants for replicator@%                                                                                             |
+---------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, EXECUTE, SELECT, SHOW VIEW, REPLICATION CLIENT, RELOAD, TRIGGER ON *.* TO `replicator`@`%` |
+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- サンプルデータベースの追加
CREATE DATABASE IF NOT EXISTS replication_test_db;
USE replication_test_db;

-- サンプルテーブルの追加
CREATE TABLE replication_test_tables (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  message VARCHAR(255),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- サンプルデータの追加
INSERT INTO replication_test_tables (message, created_at)
VALUES
  ('Test message 1', NOW()),
  ('Test message 2', NOW()),
  ('Test message 3', NOW());

-- 確認
SELECT * FROM replication_test_tables;
+----+----------------+---------------------+
| id | message        | created_at          |
+----+----------------+---------------------+
|  1 | Test message 1 | 2025-06-12 17:58:38 |
|  2 | Test message 2 | 2025-06-12 17:58:38 |
|  3 | Test message 3 | 2025-06-12 17:58:38 |
+----+----------------+---------------------+
3 rows in set (0.00 sec)

-- プライマリの過去全ての Binlog を確認
SHOW BINARY LOGS
+-----------------+-----------+-----------+
| Log_name        | File_size | Encrypted |
+-----------------+-----------+-----------+
| mysql-bin.00004 |       244 | No        |
| mysql-bin.00005 |       244 | No        |
| mysql-bin.00006 |      1324 | No        |
+-----------------+-----------+-----------+
3 rows in set (0.01 sec)

-- プライマリの現在の Binglog を取得
SHOW MASTER STATUS
+-----------------+----------+--------------+------------------+-------------------------------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+-----------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.00006 |     1324 |              |                  | ad31e7ff-47b0-11f0-bd54-42010a290003:1-24 |
+-----------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

resource "google_sql_database_instance" "default" {
  project             = "project-b"
  count               = 1
  name                = "dms-playground-cloud-sql"
  region              = "asia-east1"
  database_version    = "MYSQL_8_0_31" ## ソースの Cloud SQL のバージョンに合わせる
  deletion_protection = true
}

$ terraform import 'google_sql_database_instance.default[0]' project-a/dms-playground-cloud-sql

resource "google_sql_user" "default" {
  count    = 1
  name     = [MYSQL_USER_NAME]
  instance = google_sql_database_instance.default[count.index].name
  host     = "%"
  password = [MYSQL_USER_PASS]
}

resource "google_database_migration_service_connection_profile" "src_profile_01" {
  project               = "project-b" ## ターゲットプロジェクトに作成
  location              = "asia-east1"
  connection_profile_id = "dms-playground-cloud-sql-src-profile"
  display_name          = "dms-playground-cloud-sql-src-profile"

  mysql {
    host     = "10.41.0.2"
    port     = 3306
    username = "replicator"
    password = "YourStrongPassword"
  }

  labels = {
    resource-name = "dms-playground-cloud-sql-src-profile"
  }
}

$ gcloud database-migration connection-profiles describe dms-playground-cloud-sql-src-profile --region=asia-east1 --project=project-b
createTime: '2025-06-12T05:59:02.860232149Z'
displayName: dms-playground-cloud-sql-src-profile
labels:
  goog-terraform-provisioned: 'true'
  resource-name: dms-playground-cloud-sql-src-profile
mysql:
  host: 10.41.0.2
  passwordSet: true
  port: 3306
  username: replicator
name: projects/project-b/locations/asia-east1/connectionProfiles/dms-playground-cloud-sql-src-profile
satisfiesPzi: true
satisfiesPzs: true
state: READY
updateTime: '2025-06-13T00:21:10.014319Z'

resource "google_database_migration_service_connection_profile" "dst_profile_01" {
  project               = "project-b" ## ターゲットプロジェクトに作成
  location              = "asia-east1"
  connection_profile_id = "dms-playground-cloud-sql-dst-profile"
  display_name          = "dms-playground-cloud-sql-dst-profile"

  mysql {
    cloud_sql_id = "dms-playground-cloud-sql"
  }

  labels = {
    resource-name = "dms-playground-cloud-sql-dst-profile"
  }
}

$ gcloud database-migration connection-profiles describe dms-playground-cloud-sql-dst-profile --region=asia-east1 --project=project-b
createTime: '2025-06-18T15:03:06.264373371Z'
displayName: dms-playground-cloud-sql-dst-profile
labels:
  goog-terraform-provisioned: 'true'
  resource-name: dms-playground-cloud-sql-dst-profile
mysql:
  cloudSqlId: dms-playground-cloud-sql
name: projects/project-b/locations/asia-east1/connectionProfiles/dms-playground-cloud-sql-dst-profile
provider: CLOUDSQL
satisfiesPzi: false
satisfiesPzs: false
state: READY
updateTime: '2025-06-18T15:03:06.347052Z'

resource "google_database_migration_service_migration_job" "dms_job_01" {
  project          = "project-b" ## ターゲットプロジェクトに作成
  location         = "asia-east1"
  migration_job_id = "dms-playground-cloud-sql-dms-job"
  display_name     = "dms-playground-cloud-sql-dms-job"

  vpc_peering_connectivity {
    vpc = "cloud-vpn-playground-vpc"
  }

  source      = google_database_migration_service_connection_profile.src_profile_01.name ## Source Connection Profile を指定
  destination = google_database_migration_service_connection_profile.dst_profile_01.name ## Destination Connection Profile を指定

  type        = "CONTINUOUS"

  labels = {
    resource-name = "dms-playground-cloud-sql-dms-job"
  }

  depends_on = [
    google_database_migration_service_connection_profile.src_profile_01,
    google_database_migration_service_connection_profile.dst_profile_01
  ]
}

$ gcloud database-migration migration-jobs describe dms-playground-cloud-sql-dms-job --region=asia-east1 --project=project-b
createTime: '2025-06-18T15:03:19.591264032Z'
destination: projects/project-b/locations/asia-east1/connectionProfiles/dms-playground-cloud-sql-dst-profile
destinationDatabase:
  engine: MYSQL
  provider: CLOUDSQL
displayName: dms-playground-cloud-sql-dms-job
duration: 0.182634s
labels:
  goog-terraform-provisioned: 'true'
  resource-name: dms-playground-cloud-sql-dms-job
name: projects/project-b/locations/asia-east1/migrationJobs/dms-playground-cloud-sql-dms-job
objectsConfig: {}
satisfiesPzi: false
satisfiesPzs: false
source: projects/project-b/locations/asia-east1/connectionProfiles/dms-playground-cloud-sql-src-profile
sourceDatabase:
  engine: MYSQL
state: NOT_STARTED
type: CONTINUOUS
updateTime: '2025-06-18T15:03:19.717494Z'
vpcPeeringConnectivity:
  vpc: cloud-vpn-playground-vpc

### Connection Profile 一覧
$ gcloud database-migration connection-profiles list --region=asia-east1 --project=project-b
CONNECTION_PROFILE_ID                 DISPLAY_NAME                          REGION      STATE  PROVIDER  ENGINE  HOSTNAME/IP  CREATED
dms-playground-cloud-sql-src-profile  dms-playground-cloud-sql-src-profile  asia-east1  READY            MYSQL   10.41.0.2    2025-06-12T05:59:02
dms-playground-cloud-sql-dst-profile  dms-playground-cloud-sql-dst-profile  asia-east1  READY  CLOUDSQL  MYSQL                2025-06-18T15:03:06

### Migration Job 一覧
$ gcloud database-migration migration-jobs list --region=asia-east1 --project=project-b
MIGRATION_JOB_ID                  DISPLAY_NAME                      REGION      STATUS   SOURCE                                DESTINATION
dms-playground-cloud-sql-dms-job  dms-playground-cloud-sql-dms-job  asia-east1  STOPPED  dms-playground-cloud-sql-src-profile  dms-playground-cloud-sql-dst-profile

resource "google_project_iam_member" "default" {
  project = "project-b"
  role    = "roles/cloudsql.client"
  member  = "serviceAccount:service-[PROJECT_NUMBER]@gcp-sa-datamigration.iam.gserviceaccount.com"
}

$ gcloud database-migration migration-jobs demote-destination dms-playground-cloud-sql-dms-job --region=asia-east1 --project=project-b
done: false
metadata:
  '@type': type.googleapis.com/google.cloud.clouddms.v1.OperationMetadata
  apiVersion: v1
  createTime: '2025-06-18T15:11:18.562654495Z'
  requestedCancellation: false
  target: projects/project-b/locations/asia-east1/migrationJobs/dms-playground-cloud-sql-dms-job
  verb: demoteDestination
name: projects/project-b/locations/asia-east1/operations/operation-1750259477086-637da0a658097-40301964-57d61c54

-- 確認
SELECT * FROM replication_test_tables;
+----+----------------+---------------------+
| id | message        | created_at          |
+----+----------------+---------------------+
|  1 | Test message 1 | 2025-06-12 17:58:38 |
|  2 | Test message 2 | 2025-06-12 17:58:38 |
|  3 | Test message 3 | 2025-06-12 17:58:38 |
+----+----------------+---------------------+
3 rows in set (0.00 sec)

-- プライマリの過去全ての Binlog を確認
SHOW BINARY LOGS
+-----------------+-----------+-----------+
| Log_name        | File_size | Encrypted |
+-----------------+-----------+-----------+
| mysql-bin.00001 |       180 | No        |
| mysql-bin.00002 |      1040 | No        |
+-----------------+-----------+-----------+
3 rows in set (0.01 sec)

-- プライマリの現在の Binglog を取得
SHOW MASTER STATUS
+-----------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                   |
+-----------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| mysql-bin.00002 |     1040 |              |                  | 70e60be1-4c51-11f0-8483-42010aeb0007:1-4, ad31e7ff-47b0-11f0-bd54-42010a290003:1-25 |
+-----------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- ソースで実行
INSERT INTO replication_test_tables (message, created_at)
VALUES
  ('Test message 4', NOW()),
  ('Test message 5', NOW()),
  ('Test message 6', NOW());

-- ターゲットで実行
SELECT * FROM replication_test_tables;
+----+----------------+---------------------+
| id | message        | created_at          |
+----+----------------+---------------------+
|  1 | Test message 1 | 2025-06-12 17:58:38 |
|  2 | Test message 2 | 2025-06-12 17:58:38 |
|  3 | Test message 3 | 2025-06-12 17:58:38 |
|  4 | Test message 4 | 2025-06-18 15:49:05 | -- ソースで追加されたレコード
|  5 | Test message 5 | 2025-06-18 15:49:05 | -- ソースで追加されたレコード
|  6 | Test message 6 | 2025-06-18 15:49:05 | -- ソースで追加されたレコード
+----+----------------+---------------------+
6 rows in set (0.00 sec)

-- 実際にはレコード数の比較でレプリケーション状況を把握
SELECT COUNT(*) FROM replication_test_tables;

-- レコード数の概算値を取得
SELECT table_rows FROM information_schema.tables WHERE table_schema = '[DATABASE_NAME]' AND table_name = 'replication_test_tables';

$ gcloud sql instances patch [INSTANCE_NAME] --project=[PROJECT_ID] --database-flags=read_only=on

CREATE TABLE hoge (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100)
);

-- ソースで実行
INSERT INTO replication_test_tables (message, created_at)
VALUES
  ('Test message 7', NOW()),
  ('Test message 8', NOW()),
  ('Test message 9', NOW());

SELECT * FROM replication_test_tables;
+----+----------------+---------------------+
| id | message        | created_at          |
+----+----------------+---------------------+
|  1 | Test message 1 | 2025-06-12 17:58:38 |
|  2 | Test message 2 | 2025-06-12 17:58:38 |
|  3 | Test message 3 | 2025-06-12 17:58:38 |
|  4 | Test message 4 | 2025-06-18 15:49:05 |
|  5 | Test message 5 | 2025-06-18 15:49:05 |
|  6 | Test message 6 | 2025-06-18 15:49:05 |
|  7 | Test message 7 | 2025-06-18 15:58:18 | -- ソースで追加したレコード
|  8 | Test message 8 | 2025-06-18 15:58:18 | -- ソースで追加したレコード
|  9 | Test message 9 | 2025-06-18 15:58:18 | -- ソースで追加したレコード
+----+----------------+---------------------+
6 rows in set (0.00 sec)

-- ターゲットで実行
SELECT * FROM replication_test_tables;
+----+----------------+---------------------+
| id | message        | created_at          |
+----+----------------+---------------------+
|  1 | Test message 1 | 2025-06-12 17:58:38 |
|  2 | Test message 2 | 2025-06-12 17:58:38 |
|  3 | Test message 3 | 2025-06-12 17:58:38 |
|  4 | Test message 4 | 2025-06-18 15:49:05 |
|  5 | Test message 5 | 2025-06-18 15:49:05 |
|  6 | Test message 6 | 2025-06-18 15:49:05 |
+----+----------------+---------------------+
6 rows in set (0.00 sec)

$ gcloud sql instances describe [INSTANCE_NAME] --project=[PROJECT_ID] --format="value(settings.backupConfiguration.binaryLogEnabled)"
[何も表示されない場合は有効になっていない]

### ソース Cloud SQL インスタンスの承認済みネットワークに追加
$ gcloud sql instances patch [INSTANCE_NAME] --authorized-networks=[REPLICATION_OUTGOING_IP_ADDRESS] --project=project-a

### データベースに接続する
$ gcloud sql connect [INSTANCE_NAME] --project=[PROJECT_ID] --user=[USER_NAME]
$ mysql -u [USER_NAME] -p -h [INSTANCE_EXTERNAL_IP] -P 3306 ## 承認済みネットワークで許可されている場合は mysql コマンドでも接続できる

### general_log の設定を確認する
> SHOW VARIABLES LIKE 'general_log%';
+------------------+-------------------------+
| Variable_name    | Value                   |
+------------------+-------------------------+
| general_log      | OFF                     |
| general_log_file | /mysql/logs/general.log |
+------------------+-------------------------+
2 rows in set (0.00 sec)

### general_log を有効化する（有効化されていない場合）
$ gcloud sql instances patch [INSTANCE_ID] --database-flags=general_log=on --project=[GCP_PROJECT_ID]

### 全ての接続 / クエリのログを取得する
> SELECT event_time, user_host, command_type FROM mysql.general_log ORDER BY event_time DESC LIMIT 100;

### 特定ユーザに限定して接続 / クエリのログを取得する
> SELECT event_time, user_host, command_type FROM mysql.general_log WHERE user_host = 'replicator@[REPLICATION_OUTGOING_IP_ADDRESS]' ORDER BY event_time DESC LIMIT 100;

### 現在の接続状況を確認する
> SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE FROM information_schema.processlist;