2021.04.08

MySQL replication on GCP ~Cloud SQL から自分で構築した MySQL にレプリケーションする~

次世代システム研究室の Y.I です。　クラウドを利用したアプリケーション開発の知見を得るために、Google Cloud Platform (GCP) のマネージド DB サービス「Cloud SQL for MySQL」、特にレプリケーション機能を調査しました。

皆さんは高負荷が掛かるアプリケーションのアーキテクチャーをどのように設計されるでしょうか？　DB とは別に Redis や Memcahed などのキャッシュミドルウエアを用いる事が多いでしょうか？　それとも Apache Ignite や Hazelcast などのインメモリデータプラットフォームの利用でしょうか。

筆者は DB を多様する設計を行うことがあります。　DBサーバーとは別にAPサーバー上にDBを構築して、レプリケーション機能によりデータを複製、そしてアプリケーションからローカルDBを参照する事で高負荷対策を行う設計です。　今回、 GCP 上でローカルDBへのレプリケーションを実現するためにマネージドサービスである Cloud SQL をプライマリ（旧：マスター）として VM (GCE) 上に構築した MySQL をレプリカ（旧：スレーブ）としてレプリケーションを構築する手順をご紹介します。　

構成

利用技術

Google Cloud Platform (GCP)
Cloud SQL for MySQL
Google Compute Engine (GCE)
MySQL 8
mysqldump

まとめ

結論や気づいた点を先にまとめます。

Cloud SQL から自分で構築した MySQL にレプリケーションできる
サービス無停止で自分で構築した MySQL にレプリケーション可能 (=運用中にレプリカDBを追加可能)
Cloud SQL で binlog を出力するためにバックアップおよびポイントインタイムリカバリを有効にする
Cloud SQL 独自テーブル heartbeat を作成する必要がある
CREATE USER/GRANT SQL は binlog に出力しないのがおすすめ

Cloud SQL へデータ登録し続けながら自分で構築した MySQL にレプリケーションする方法だけを知りたい方は、上記「レプリケーション構築」リンク先をご覧ください。

構築手順（詳細）

それでは実際に構築した際の手順詳細を説明していきます。

Cloud SQL構築

GCP の Web Console で Cloud SQL を構築します。ブラウザ上で簡単に構築できます。

気をつける点は「Connections」と「Backups」の指定です。
Connections は Private IP にチェックを入れ、 Network に Default を選択します。　このあと構築するレプリケーション先の GCE と同じ Network にして、接続できるようにします。
Backups は Enable point-in-time recovoery を有効にします。

– Cloud SQL作成

– MySQL を選択

– ネットワーク設定

– バックアップ設定

構築を開始すると数分で Cloud SQL が起動され利用できるようになります。

GCE 構築

レプリケーション先の Replica DB を構築する GCE を作成します。今回は OS に Ubuntu を指定します。気をつける点は Network Interface に Cloud SQL で指定した Default を選択することです。

構築を開始すると1分かからずに GCE が起動され利用できるようになります。

MySQL インストール

引き続き構築した GCE に MySQL をインストールします。その後、GCE から mysql コマンドで Cloud SQL に接続してみます。 GCE 上の MySQL Server の構築は後ほど行います。

MySQL インストール

sudo apt install mysql-server

MySQL vesion 確認

mysql --version
mysql  Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

Cloud SQL へ接続
GCE 上の mysql コマンドを使って Cloud SQL へ接続します。

mysql -uroot -p -h{cloud sql private ip address}
　※ private ip は Web Console 上に表示されている ip
　※ パスワードは Cloud SQL 構築時に指定したパスワード


Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 654
Server version: 8.0.18-google (Google)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type &#039;help;&#039; or &#039;\h&#039; for help. Type &#039;\c&#039; to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql>

Cloud SQL DBスキーマ等の作成

Cloud SQL に接続できたので、引き続き mysql コマンドで DB user や DB スキーマやテーブルを構築します。

今回、
　DATABASE Name は db01
　Table は t1
　レプリケーションユーザーは rep/reppass01
とします。

– db user 作成

-- CREATE USER/GRANT を一時的に binlog に出力しないようにする
SET sql_log_bin = 0;
CREATE USER 'rep'@'%' IDENTIFIED BY 'reppass01';
GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%';
SET sql_log_bin = 1;

　※ SET sql_log_bin = 0(binlog出力しない)/1(出力する)

– database 作成

CREATE DATABASE db01 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

– table 作成

CREATE TABLE t1 (
id  int  not null auto_increment
,name varchar(255) null
,primary key (id)
);

MySQL 8 構築

次は GCE 上に MySQL 8を構築します。すでにインストールは完了しているのでセットアップを行います。

sudo mysql_secure_installation
　 ※ root 権限で実行しましょう

一般ユーザー権限で実行すると以下のようにアクセス拒否されてしまいます

usr0102198@vm01:~$ mysql_secure_installation 
Securing the MySQL server deployment.
Enter password for user root: 
Error: Access denied for user 'root'@'localhost'

OS root権限で mysql コマンドを実行すると構築したMySQLへ接続できます
従来のパスワード認証で MySQL に接続するには ALTER USER します

sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'ここに新しいパスワード';
Query OK, 0 rows affected (0.01 sec)

exit

/etc/mysql/conf.d/mysql.cnf を編集します

[mysqld]

# 各MySQLサーバを識別するためのIDの設定
server-id=101

# GTIDの有効化(enforce_gtid_consistency=ONが必要)
gtid_mode=ON
# GTIDと併用できないSQL文を禁止する
enforce_gtid_consistency=ON

# バイナリログの有効化
log_bin=mysql-bin

# バイナリログをコミットと同時にディスクに書込む
sync_binlog=1

log_slave_updates

character-set-server=utf8mb4

slow_query_log=ON
long_query_time=1.0

relay_log_info_repository=TABLE
relay_log_recovery=ON
relay_log_purge=ON

innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=1
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_io_capacity = 2000

[mysql]

MySQL を再起動して my.cnf を反映します

usr0102198@vm01:~$ sudo systemctl restart mysql

Cloud SQL 独自テーブル heartbeat 作成

GCE 上の mysql へ接続

usr0102198@vm01:~$ mysql -uroot -p

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

heartbeat テーブル作成

mysql> CREATE TABLE `mysql`.`heartbeat` (
    ->   `id` int(10) unsigned NOT NULL,
    ->   `master_time` datetime(6) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.06 sec)

1レコード登録する

mysql> insert into mysql.heartbeat values (1, '2021-03-18 00:00:00.819465');
Query OK, 1 row affected (0.01 sec)

 ※ 日時は登録できればどの値でも良いです。レプリケーションを開始すると都度更新されます

以上で MySQL の構築が完了しました。

レプリケーション構築

いよいよレプリケーションを構築します。サービス運用中にレプリカDBを構築する際にはデータが登録され続けている状態でレプリケーション構築する必要があります。今回は10秒に1回レコードを登録し続けている状態で mysqldump で Cloud SQL の DB をエクスポートして、GCE上の MySQL にインポートして、レプリケーションを開始します。登録され続けるレコードが正しくレプリケーションされるか確認してします。

サービス無停止状態をエミュレートするために下記 shell script で Cloud SQL にレコード登録し続けます。その間にエクスポートしてレプリケーションを構築します。

for i in `seq 1 10000`; do
  echo ${i};
  mysql -uroot -p{ここにパスワード}} -h{ip address} db01 -e "INSERT INTO db01.t1 values (null, 'name${i}');";
  sleep 10;
done

Cloud SQL の対象DBをエクスポートする

mysqldump --databases db01 -h10.XXXXXXXX -u root -p \
--hex-blob --single-transaction --set-gtid-purged=OFF --master-data=2 --flush-logs \
--complete-insert --default-character-set=binary > db01_dump.sql

　--single-transaction: トランザクション開始して実行時点の情報をエクスポートする
　--hex-blob/--set-gtid-purged=OFF: Cloud SQLからエクスポートする際に必要なパラメーター
　--master-data=2: プライマリDBのbinlog file/positionを出力
　--flush-logs: プライマリDBのbinlog fileを新しいfileへ切り替え
　--complete-insert: INSERT文のカラム名を出力する
　--default-character-set=binary: 文字コードを変更せずにエクスポート（文字化け対策におすすめ）

Cloud SQL からエクスポートした export file を構築した MySQL にインポートします

usr0102198@vm01:~$ mysql -uroot -p < db01_dump.sql

export file から CHANGE MASTER 箇所を探す

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=195;

GCE 上の MySQL に接続

usr0102198@vm01:~$ mysql -uroot -p

インポートして t1テーブルのレコードがエクスポートした時点の id 5 であることを確認

mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | name1 |
|  2 | name2 |
|  3 | name3 |
|  4 | name4 |
|  5 | name5 |
+----+-------+
5 rows in set (0.00 sec)
mysql>

レプリケーション設定

mysql> CHANGE MASTER TO 
    ->  MASTER_HOST = '10.XXXXXXXX',
    ->  MASTER_USER = 'rep', 
    ->  MASTER_PASSWORD = 'reppass01', 
    ->  MASTER_LOG_FILE= 'mysql-bin.000002',    ※ export file の MASTER_LOG_FILE の値
    ->  MASTER_LOG_POS=195      ※ export file の MASTER_LOG_POS の値
    ->  ;
Query OK, 0 rows affected, 8 warnings (0.07 sec)

レプリケーション設定確認

mysql> show replica status\G
*************************** 1. row ***************************
             Replica_IO_State: 
                  Source_Host: 10.XXXXXXXX
                  Source_User: rep
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000002
          Read_Source_Log_Pos: 195
               Relay_Log_File: vm01-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Source_Log_File: mysql-bin.000002
           Replica_IO_Running: No
          Replica_SQL_Running: No

レプリケーション開始

mysql> start replica;
Query OK, 0 rows affected (0.00 sec)

t1 テーブルレコードが id 5 から id 44 まで増えていてレプリケーションされている事を確認

mysql> select * from db01.t1;
+----+--------+
| id | name   |
+----+--------+
|  1 | name1  |
| ....省略.... |
| 44 | name44 |
+----+--------+
44 rows in set (0.00 sec)

今回ハマった事

Cloud SQL で binlog を出力するためにバックアップおよびポイントインタイムリカバリを有効にする

-> ポイントインタイムリカバリを有効にしないと binlog が出力されないため、レプリケーションの構築ができません。筆者は最初試した際に可能な限り低予算で構築するためにバックアップをしない設定で確認したところ、binlogが出てなくてレプリケーションができない状態になりました。。

Cloud SQL 独自テーブル heartbeat を作成する必要がある

->Cloud SQL 独自のテーブルで定期的にレコードが更新されています。レプリカDBの mysql スキーマにも作成してレコードを登録しておかないとレプリケーションエラーとなります。

CREATE USER/GRANT SQL は binlog に出力しないのがおすすめ

->エクスポートファイルを取り込んだ際に、CREATE USER でレプリケーションエラーとなりました。skipして回避しましたが、プライマリDBでbinlogに出力しない設定にしてから CREATE USER するのが間違いがなくおすすめです。

参考リンク

MySQL: https://dev.mysql.com
Google Cloud Platform: https://cloud.google.com/
Cloud SQL: https://cloud.google.com/sql
Apache Ignite: https://ignite.apache.org/
Hazelcast: https://hazelcast.com/

7.まとめ

今回は Cloud SQL から自分で構築した MySQL にレプリケーションを行いました。

Cloud SQL のレコードが id 5 / name5 の状態で mysqldump してエクスポートしましたが、GCE 上の MySQL でレプリケーションを開始したら id 44/name44 レコードまでレプリケーションされ、データが登録し続けている状態でも正しくレプリカDBを作成することが出来ました。

マネージドサービスである Cloud SQL から外部 MySQL にどのようにレプリケーションすれば良いのか疑問でしたが、 heartbeat テーブルなど一部の独自対応すれば特に問題なくレプリケーションすることが可能な事がわかりました。次回は今回構築した構成を中心にチューニングおよびベンチマークをとる予定です。

最後に、次世代システム研究室では、グループ全体のインテグレーションを支援してくれるアーキテクトを募集しています。アプリケーション開発の方、次世代システム研究室にご興味を持って頂ける方がいらっしゃいましたら、ぜひ募集職種一覧からご応募をお願いします。

グループ研究開発本部の最新情報をTwitterで配信中です。ぜひフォローください。

Follow @GMO_RD

関連記事

ちょっとこわいGoogle Cloud Pub/Sub によるFaaS アーキテクチャ

実際のワークロードに近いSQLの負荷テストをmysqlslapのような感覚で行う方法を考える

Hadoop (Hive, Ambari など) が使う DB を Percona XtraDB Cluster & ProxySQL で冗長化してみた

MariaDB Galera Clusterのパラメータを変化させたときの書き込み性能について

最新の記事

採用情報

インターンシップ

インタビュー

MySQL replication on GCP ~Cloud SQL から 自分で構築した MySQL にレプリケーションする~

利用技術

まとめ

構築手順（概要）

構築手順（詳細）

Cloud SQL構築

GCE 構築

MySQL インストール

Cloud SQL DBスキーマ等の作成

MySQL 8 構築

レプリケーション構築

今回ハマった事

参考リンク

7.まとめ

関連記事

ちょっとこわいGoogle Cloud Pub/Sub によるFaaS アーキテクチャ

実際のワークロードに近いSQLの負荷テストをmysqlslapのような感覚で行う方法を考える

Hadoop (Hive, Ambari など) が使う DB を Percona XtraDB Cluster & ProxySQL で冗長化してみた

MariaDB Galera Clusterのパラメータを変化させたときの書き込み性能について

タグ

最新の記事

採用情報

インターンシップ

インタビュー

MySQL replication on GCP ~Cloud SQL から自分で構築した MySQL にレプリケーションする~