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 'help;' or '\h' for help. Type '\c' 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.comGoogle 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 テーブルなど一部の独自対応すれば特に問題なくレプリケーションすることが可能な事がわかりました。次回は今回構築した構成を中心にチューニングおよびベンチマークをとる予定です。
最後に、次世代システム研究室では、グループ全体のインテグレーションを支援してくれるアーキテクトを募集しています。アプリケーション開発の方、次世代システム研究室にご興味を持って頂ける方がいらっしゃいましたら、ぜひ募集職種一覧からご応募をお願いします。
