EC2２台をそれぞれマスターとスレーブとしてMySQLをインストールして設定してみます。
OSはAmazonLinux2を使います。
サーバーの準備
マスターとして使用するサーバーをreplication-master、スレーブとして使用するサーバーをreplication-slaveとして~/.ssh/configの設定もしておきます。
セキュリティーグループ
sshで手元から接続できるようにするのと、サーバー同士がTCP3306番ポートで通信できるように例えば下記の様に設定します。
マスターサーバーの設定
MySQLのインストール
MySQL5.7をインストールします。
sudo yum update
sudo yum remove mariadb-libs
sudo yum install http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
sudo yum install -y mysql-community-server
MySQLの設定
/etc/my.cnfファイルの[mysqld]の項目に以下の２行を追記します。バイナリログを使用する設定とserver-idを設定します。server-idはユニークなものを設定します。
log-bin
server-id=101
MySQLの起動
sudo systemctl start mysqld
MySQLに接続
自動生成されたパスワードがlogに載っているので調べます。
sudo cat /var/log/mysqld.log |grep "password is generated"
ここで表示されるパスワードを使ってrootでログインします。
mysql -uroot -p
パスワードの変更
パスワードの変更をしないとエラーが出るのでabcABC=0に変更します。
mysql> SET PASSWORD FOR root@localhost=password('abcABC=0');
DBとテーブルの作成
ログイン後、replication_testというデータベースとusersというテーブルの作成をします。
mysql> CREATE DATABASE replication_test;
mysql> USE replication_test;
mysql> CREATE TABLE users(id int not null primary key AUTO_INCREMENT, name varchar(30));
初期データの投入
mysql> INSERT INTO users(name) value('habu');
ちゃんと入っているか確認します。
mysql> SELECT * FROM users;
+----+------+
| id | name |
+----+------+
| 1 | habu |
+----+------+
ユーザーの設定
replicateユーザーを作成します。また、スレーブのプライベートDNSからの接続を許可するようにしておきます。
mysql> CREATE USER 'replicate'@'ip-172-31-27-89.ap-northeast-1.compute.internal' IDENTIFIED BY 'abcABC=0';
作成したユーザーにレプリケーションを許可する権限を付与します。
GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'ip-172-31-27-89.ap-northeast-1.compute.internal';
バイナリログの情報確認
スレーブで使用するのでFileとPositionをメモしておきます。
mysql> SHOW MASTER STATUS;
+----------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| ip-172-31-26-17-bin.000002 | 586 | | | |
+----------------------------+----------+--------------+------------------+-------------------+
マスターDBのdump作成
MySQLから出てdumpを作成します。
mysqldump --single-transaction -uroot -p replication_test > replication_test.dump
スレーブサーバーの設定
途中までマスターサーバーと同じですが順番に設定してきます。
MySQLのインストール
MySQL5.7をインストールします。
sudo yum update
sudo yum remove mariadb-libs
sudo yum install http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
sudo yum install -y mysql-community-server
MySQLの設定
/etc/my.cnfファイルの[mysqld]の項目に以下の行を追記します。ユニークになるようにスレーブには102を設定しました。
server-id=102
MySQLの起動
sudo systemctl start mysqld
MySQLに接続
自動生成されたパスワードがlogに載っているので調べます。
sudo cat /var/log/mysqld.log |grep "password is generated"
ここで表示されるパスワードを使ってrootでログインします。
mysql -uroot -p
パスワードの変更
パスワードの変更をしないとエラーが出るのでabcABC=0に変更します。
mysql> SET PASSWORD FOR root@localhost=password('abcABC=0');
DBの作成
ログイン後、replication_testというデータベースを作成します。
mysql> CREATE DATABASE replication_test;
データベースのインポート
mysql> USE replication_test;
mysql> SOURCE ~/replication_test.dump;
スレーブ設定
下記のように設定します。
| 項目 | 設定値 |
|---|---|
| master_host | マスターサーバーのプライベートIP |
| master_user | レプリケーション用に作成したユーザー名 |
| master_password | レプリケーション用に作成したユーザーのパスワード |
| master_log_file | マスターサーバー作成時にメモしておいたバイナリデータのFile情報 |
| master_log_pos | マスターサーバー作成時にメモしておいたバイナリデータのPosition情報 |
mysql> CHANGE MASTER TO master_host='172.31.26.17', master_user='replicate', master_password='abcABC=0', master_log_file='ip-172-31-26-17-bin.000002', master_log_pos=586;
mysql> CHANGE REPLICATION FILTER replicate_do_db = (replication_test);
設定をしたらレプリケーションを開始します。
mysql> START SLAVE;
設定の確認
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.31.26.17
Master_User: replicate
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: ip-172-31-26-17-bin.000002
Read_Master_Log_Pos: 586
Relay_Log_File: ip-172-31-27-89-relay-bin.000002
Relay_Log_Pos: 330
Relay_Master_Log_File: ip-172-31-26-17-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 586
Relay_Log_Space: 547
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 101
Master_UUID: 4e916f3a-a81b-11ea-bc01-068aeb366e0a
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Slave_IO_Stateの値がWaiting for master to send eventとなっていれば問題なく動いているはずですが、ちゃんと動いているか実際にデータをいれて確認してみます。
もし、Slave_IO_Stateの値がConnecting to masterまま止まってしまった場合はセキュリティグループやユーザーの設定を見直してみると良いかもしれません。スレーブのサーバーからmysqlコマンドでマスターサーバーへ接続できるのであればスレーブ側の接続設定が間違っている可能性が高いかもしれません（今回そこでつまっていました）
マスターサーバーのmysqlに入ったあとINSERTしてみます。
mysql> USE replication_test;
mysql> INSERT INTO users(name), VALUE('fujii');
確認すると
mysql> SELECT * FROM users;
+----+----------+
| id | name |
+----+----------+
| 1 | habu |
| 2 | fujii |
+----+----------+
と入っています。これがスレーブでも適用されていればokです。
これを確認する為スレーブサーバーに入って確認します。
mysql> USE replication_test;
mysql> SELECT * FROM users;
+----+----------+
| id | name |
+----+----------+
| 1 | habu |
| 2 | fujii |
+----+----------+
マスターのデータが問題なく反映されているので無事レプリケーション設定ができました🎉