今回、AZをまたいだEC2(AWS Amazon Linux2)上にMySQL5.7(.25)をインストールし、Group Replicationを構成する手順を記載します。
MySQL Shellを使って設定するので、MySQL Shell用のインスタンスを一台用意します(※)。
※本来は3台のDB上のどれかに入れてもいいんですが、別途1台用意して外から設定したほうがインスタンス的にスマートなので1台追加します
※8.0.4(RC)では全ノードにMySQL Shellのインストールが必要だったはずなので、もしかするとMySQL5.7のこれよりも前のバージョンだとそういうこともあることだけは認識しといたほうが良いです。
前提
Group Replicationの要件
- ノード数は最低3台以上
- ノード数については奇数であることが推奨される
- ストレージエンジンはInnoDBのみ
- すべてのテーブルにPK必要
- GTIDが有効であること
今回の構成
構成は次の通りとする。
- AZは異なるところにDBインスタンスを配置
- MySQL Shell用にインスタンスを構築。AZはPrimaryとそろえた(違ってもいい)
- IP Addressについては仮のものなので各自の環境で変更してください。
- server_idやAZも同様
- server_idやPortはmy.cnfに書くのでお好きなように。
| 対象 | server_id | IPaddress(Private) | 設定するPort | AZ |
|---|---|---|---|---|
| Primary | 1 | 10.0.0.1 | 33061 | ap-northeast-1a |
| Secondary1 | 2 | 10.0.1.1 | 33062 | ap-northeast-1c |
| Secondary2 | 3 | 10.0.2.1 | 33063 | ap-northeast-1d |
| MySQL Shell | - | 10.0.0.2 | - | ap-northeast-1a |
ハマりそうなポイント
2つあります。
特に前者は気づきやすいですが、後者は意外と知られていない気がするので注意してください。
EC2インスタンスの構築
やっていない人はここから。 1. VPC作成 2. サブネット追加
この辺についてはわからない人は別途記事を読んでゼロから作ってください。
EC2インスタンス作成
1×MySQL Shell用、3×DB用EC2インスタンス分作成
セキュリティグループ
グループレプリケーションのメンバーとなるインスタンスはお互いがTCP接続できないといけない。 3306と設定するポート番号は最低開けなきゃいけないんだけど、22とかもmy.cnfのコピーとかで使いたい人いるだろうし、 その辺の設定についてはお任せ。 (面倒くさい人はすべてのTCPをメンバーのセキュリティグループIDで設定しとけばいいんじゃないかと？) この疎通ができていないとAWS上でのグループレプリケーションは設定で当然ハマるので注意。
MySQL Group Replication構築
mariaDBアンインストール
デフォだとMariaDB入ってるんで、それ使うならそのままでいいけど、今回はMySQL5.7を入れるんで削除。 いちいち入れるかどうか聞かれるの面倒くさいので-yを付けてますが、確認したい人は-yを外してください。
sudo yum list installed | grep mariadb sudo yum remove mariadb-libs -f
MySQL インストール
MySQL5.7を入れる (8.0を入れたい気持ちは グッ と抑える)
sudo yum install http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm -y sudo yum install mysql-community-server -y
キャッシュのクリア(念のため)
sudo yum clean all sudo rm -rf /var/cache/yum
インストール結果の確認
yum repolist enabled | grep "mysql.\*-community.\*"
問題なければこんな表示が出るはず
mysql-connectors-community/x86_64 MySQL Connectors Community 74+21 mysql-tools-community/x86_64 MySQL Tools Community 84 mysql57-community/x86_64 MySQL 5.7 Community Server 327
MySQLバージョン確認
確認コマンド
mysql --version
結果例
mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using EditLine wrapper
確認コマンド
mysqld -V
結果例
mysqld Ver 5.7.25 for Linux on x86_64 (MySQL Community Server (GPL))
MySQL起動
sudo /bin/systemctl start mysqld.service systemctl list-units | grep mysqld.service
rootパスワードの変更
パスワードの確認
cat /var/log/mysqld.log | grep password
確認したパスワードでログイン
mysql -u root -p
rootパスワードの変更
SET GLOBAL validate_password_length=8; SET GLOBAL validate_password_policy=LOW; ALTER USER root@localhost IDENTIFIED BY '<任意のパスワード>';
変更前パラメータ確認
自分が変更する予定のパラメータを確認しておくといいかと思います。 次の例では文字コード、コラーション、オプティマイザルール、バッファプールを変更するので確認しています。
show variables like "chara%"; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) show variables like "optimizer_switch"; +------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on | +------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) show variables like "innodb_buffer_pool_size"; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 134217728 | +-------------------------+-----------+ 1 row in set (0.00 sec)
パラメータ変更
変更パラメータ内容
以下はあくまで参考値なので作ったEC2の環境によって変えること。
今回の設定は
query_cache_size = 0 query_cache_type = 0 default_storage_engine = InnoDB transaction_isolation = REPEATABLE-READ lower_case_table_names = 1 character_set_server = utf8mb4 lc_messages = ja_JP log_timestamps = system explicit_defaults_for_timestamp = 1 innodb_flush_neighbors = 0 innodb_ft_min_token_size = 1 internal_tmp_disk_storage_engine = MyISAM optimizer_switch = mrr_cost_based=off,batched_key_access=on range_optimizer_max_mem_size = 0 sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION read_buffer_size = 2M read_rnd_buffer_size = 16M sort_buffer_size = 2M skip_external_locking bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_repair_threads = 1 myisam_recover_options = BACKUP,FORCE innodb_temp_data_file_path = ibtmp1:128M innodb_flush_log_at_trx_commit = 1 innodb_max_dirty_pages_pct = 90 innodb_flush_method = O_DIRECT performance_schema = 1 innodb_checksum_algorithm = crc32 innodb_large_prefix = 1 innodb_print_all_deadlocks = 1 innodb_buffer_pool_load_at_startup = 0 innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_dump_pct = 100 innodb_open_files = 2048 innodb_strict_mode = 1 # Memory等 innodb_buffer_pool_size = 820M innodb_buffer_pool_instances = 8 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_thread_concurrency = 0 innodb_log_buffer_size = 16M innodb_log_file_size = 2048M innodb_log_files_in_group = 2 log_queries_not_using_indexes = 0 [mysqldump] quick max_allowed_packet = 16M default_character_set = utf8mb4 [mysql] no_auto_rehash default_character_set = utf8mb4 [myisamchk] [mysqlhotcopy] interactive_timeout [client] default-character-set=utf8mb4
コンフィグファイル編集
sudo vim /etc/my.cnf
MySQL 再起動
sudo /bin/systemctl restart mysqld.service systemctl list-units | grep mysqld.service
変更パラメータ確認
show variables like "chara%"; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.01 sec) show variables like "optimizer_switch"; +------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off,block_nested_loop=on,batched_key_access=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on | +------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) show variables like "innodb_buffer_pool_size"; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 805306368| +-------------------------+-----------+ 1 row in set (0.00 sec)
運用ユーザー作成(opeとmanager)
今後、色々データ入れたり、誰かに見せるための運用向けユーザーとして参照用(ope)とリリース用(manager)の二つのユーザーを作成しました。
(この辺は特にグループレプリケーションに関係ないので飛ばしてもらっても構わないし、権限やユーザー名は変えてもらってもいいです)
-- 作業前確認 SELECT host, user FROM mysql.user ; SET GLOBAL validate_password_length=8; SET GLOBAL validate_password_policy=LOW; -- managerユーザー作成 CREATE USER 'manager'@'%' IDENTIFIED BY "xxxxxxx" ; GRANT ALL ON *.* TO 'manager'@'%'; REVOKE GRANT OPTION ON *.* FROM 'manager'@'%'; REVOKE REFERENCES ON *.* FROM 'manager'@'%'; REVOKE EVENT ON *.* FROM 'manager'@'%'; REVOKE TRIGGER ON *.* FROM 'manager'@'%'; REVOKE CREATE ROUTINE ON *.* FROM 'manager'@'%'; REVOKE ALTER ROUTINE ON *.* FROM 'manager'@'%'; REVOKE EXECUTE ON *.* FROM 'manager'@'%'; REVOKE FILE ON *.* FROM 'manager'@'%'; REVOKE CREATE TABLESPACE ON *.* FROM 'manager'@'%'; REVOKE CREATE USER ON *.* FROM 'manager'@'%'; REVOKE REPLICATION SLAVE ON *.* FROM 'manager'@'%'; REVOKE SHUTDOWN ON *.* FROM 'manager'@'%'; REVOKE SUPER ON *.* FROM 'manager'@'%'; -- opeユーザー作成 CREATE USER 'ope'@'%' IDENTIFIED BY "xxxxxxx" ; GRANT SELECT ON *.* TO 'ope'@'%'; GRANT SHOW VIEW ON *.* TO 'ope'@'%'; GRANT PROCESS ON *.* TO 'ope'@'%'; GRANT REPLICATION CLIENT ON *.* TO 'ope'@'%'; GRANT SHOW DATABASES ON *.* TO 'ope'@'%'; FLUSH PRIVILEGES ; -- 作業後確認 SELECT host, user FROM mysql.user ; SET GLOBAL validate_password_length=8; SET GLOBAL validate_password_policy=LOW;
上記については3台それぞれで行ってください。(Group Replication設定完了後なら1台でもOK)
MySQL Shellのインストール
sudo yum install -y mysql-shell
構築時点(2019/03)では8(8.0.15)が入ったが設定上、特に問題は無かった
MySQL Clientのインストール
sudo yum install -y mysql-community-client
クライアントは5.7.25が入った。
Group Replication のセットアップ
リモート接続可能なrootユーザー作成
create user 'root'@'%' identified by 'xxxxxxxxx!'; grant all on \*.\* to 'root'@'%' with grant option; -- localhostのパスワードも変更するなら ALTER USER root@localhost IDENTIFIED BY 'xxxxxxxxx!';
my.cnfの設定追加
事前にIPアドレスは確認しとく
各MySQLサーバ
MySQLサーバの構成は次のとおりとする。
| 対象 | server_id | IPaddress(Private) | 設定するPort | AZ |
|---|---|---|---|---|
| Primary | 1 | 10.0.0.1 | 33061 | ap-northeast-1a |
| Secondary1 | 2 | 10.0.1.1 | 33062 | ap-northeast-1c |
| Secondary2 | 3 | 10.0.2.1 | 33063 | ap-northeast-1d |
my.cnf(Node1、Node2と3は前述の表に沿って差し替える)
# For Replication server_id=1 # server_id=2 #Node2 # server_id=3 #Node3 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON log_bin=binlog binlog_format=ROW relay-log=relaylog # For Group Replication transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=off loose-group_replication_local_address="10.0.0.1:33061" ### Node1 #loose-group_replication_local_address="10.0.1.1:33062" ### Node2 #loose-group_replication_local_address="10.0.2.1:33063" ### Node3 loose-group_replication_group_seeds="10.0.0.1:33061,10.0.1.1:33062,10.0.2.1:33063" loose-group_replication_bootstrap_group=off
MySQL再起動
sudo systemctl restart mysqld systemctl list-units | grep mysqld.service
Group Replication Pluginの有効化、ユーザーーの作成
レプリケーション用ユーザーを作り、REPLICATION SLAVE権限をつける
mysql -uroot -p
INSTALL PLUGIN group_replication SONAME 'group_replication.so'; SET SQL_LOG_BIN=0; UNINSTALL PLUGIN validate_password; CREATE USER <任意のユーザー名>@'%'; GRANT REPLICATION SLAVE ON \*.\* TO <任意のユーザー名>@'%' IDENTIFIED BY '<任意のパスワード>'; SET SQL_LOG_BIN=1; CHANGE MASTER TO MASTER_USER='<任意のユーザー名>', MASTER_PASSWORD='<任意のパスワード>' FOR CHANNEL 'group_replication_recovery';
1行目のINSTALL PLUGIN group_replication～でgroup_replicationパラメータに値が入り、グループレプリケーションが使える準備ができる。
クラスタ構築
1. グループレプリケーションの設定チェック
- mysqlsh
- dba.checkInstanceConfiguration('cluster用ユーザー@PrimaryDBのIPアドレス')
直すべき項目を現在値と必要な値を比較して表示してくれる。
実行例
mysqlsh
dba.checkInstanceConfiguration('root@10.0.0.1')
パスワードの入力を求められるので入力
Please provide the password for 'root@10.0.0.1': **********
入力したパスワードを保存するか聞かれるがそれは各人にお任せ
Save password for 'root@10.0.0.1'? [Y]es/[N]o/Ne[v]er (default No): Y
2. 設定追加
以下をMySQL Shell経由で各DBサーバに対して行う 1. dba.configureInstance('cluster用ユーザー@DBサーバのIPアドレス') 2. Do you want to perform the required configuration changes? [y/n]: と聞かれるので、 y と入力 3. Do you want to restart the instance after configuring it? [y/n]: と聞かれるので、 y と入力
dba.checkInstanceConfiguration('root@10.0.0.1') Do you want to perform the required configuration changes? [y/n]: y Do you want to restart the instance after configuring it? [y/n]: y dba.checkInstanceConfiguration('root@10.0.1.1') Do you want to perform the required configuration changes? [y/n]: y Do you want to restart the instance after configuring it? [y/n]: y dba.checkInstanceConfiguration('root@10.0.2.1') Do you want to perform the required configuration changes? [y/n]: y Do you want to restart the instance after configuring it? [y/n]: y
最初のcheckInstanceConfigurationで次のように設定の問題箇所が表示される
Some configuration options need to be fixed: +--------------------------+---------------+----------------+--------------------------------------------------+ | Variable | Current Value | Required Value | Note | +--------------------------+---------------+----------------+--------------------------------------------------+ | binlog_checksum | CRC32 | NONE | Update the server variable | | enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server | | gtid_mode | OFF | ON | Update read-only variable and restart the server | | server_id | 1 | <unique ID> | Update read-only variable and restart the server | +--------------------------+---------------+----------------+--------------------------------------------------+
ただし、ここであわててmy.cnfいじって再起動する必要はない。 直後にDo you want to perform the required configuration changes?と聞かれているのでyとすることで書き換えてくれるし、 そのさらに後でDo you want to restart the instance after configuring it?と聞いてくるのでyとすることで再起動までしてくれている。
いやあ、楽だわー。
これで準備完了。
3. クラスタ構築
- \c cluster用ユーザー@PrimaryDBサーバのIP
- cluster = dba.createCluster('クラスタ名', {ipWhitelist:'DBサーバのプライベートサブネットワーク,ローカルホスト'});
- Do you want to disable super_read_only and continue? [y/N]: と聞かれるので、 y と入力
- cluster.addInstance('cluster用ユーザー@Secondary1DBサーバのIP', {ipWhitelist:'DBサーバのプライベートサブネットワーク,ローカルホスト'});
- cluster.addInstance('cluster用ユーザー@Secondary2DBサーバのIP', {ipWhitelist:'DBサーバのプライベートサブネットワーク,ローカルホスト'});
確認
MySQL Shellから
mysqlsh
\c root@10.0.0.1 cluster = dba.getCluster("<作成時のクラスタ名>") cluster.status()
こんな風に表示されればOK(クラスタ名をmycluster57とした場合)
{
"clusterName": "mycluster57",
"defaultReplicaSet": {
"name": "default",
"primary": "10.0.1.1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"10.0.1.1:3306": {
"address": "10.0.1.1:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"10.0.0.1:3306": {
"address": "10.0.0.1:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"10.0.2.1:3306": {
"address": "10.0.2.1:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "ip-10-0-0-1.ap-northeast-1.compute.internal:3306"
}
cluster.describe();
{
"clusterName": "mycluster57",
"defaultReplicaSet": {
"name": "default",
"topology": [
{
"address": "10.0.0.1:3306",
"label": "10.0.0.1:3306",
"role": "HA"
},
{
"address": "10.0.2.1:3306",
"label": "10.0.2.1:3306",
"role": "HA"
},
{
"address": "10.0.1.1:3306",
"label": "10.0.1.1:3306",
"role": "HA"
}
],
"topologyMode": "Single-Primary"
}
}
MySQL Clientから
mysql -uroot -p
SELECT * FROM performance_schema.replication_group_members;
3台がONLINEであることを確認
+---------------------------+--------------------------------------+-----------------------------------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-----------------------------------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | acd307e1-2ea4-11e9-9ed2-0accfe5d2d32 | ip-10-0-0-1.ap-northeast-1.compute.internal | 3306 | ONLINE | PRIMARY | 5.7.25 | | group_replication_applier | e5f221eb-2ea1-11e9-bbad-06ebf30711b8 | ip-10-0-1-1.ap-northeast-1.compute.internal | 3306 | ONLINE | SECONDARY | 5.7.25 | | group_replication_applier | e6bd8f9d-2ea1-11e9-98fb-0e2703b1e534 | ip-10-0-2-1.ap-northeast-1.compute.internal | 3306 | ONLINE | SECONDARY | 5.7.25 | +---------------------------+--------------------------------------+-----------------------------------------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec)
参考
https://qiita.com/himatani/items/e1e650992dbc12b8a9b3 https://www.s-style.co.jp/blog/2018/12/2962/ https://mysqlserverteam.com/innodb-cluster-in-opc/ https://qiita.com/huato/items/52871aeeb88d783ff5e1 https://dev.mysql.com/doc/refman/5.7/en/mysql-innodb-cluster-working-with-cluster.html#create-whitelist-servers https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-working-with-cluster.html#create-whitelist-servers
