CentOS 5.4 で SSL 暗号化付き MySQL レプリケーションを設定する方法
Version 1.0
Author: Falko Timme
フォロー: Twitter
重要: 本手順はバックアップポリシーではありません。DELETE 等の操作もレプリケートされるため注意してください。
概要と目的
このドキュメントは以下を達成することを目的としています:
- MySQL5 をインストールし、SSL 接続を有効化する方法
- マスターで SSL 用の CA/サーバー/クライアント証明書を作成する方法
- マスターとスレーブ間で暗号化されたレプリケーションを実装する方法
用語（1 行定義）:
- マスター: データの変更を行う主要な MySQL サーバー。
- スレーブ: マスターの変更を受け取り同期する MySQL サーバー。
- binlog: マスターが書き出す更新ログ。スレーブはこれを読み取り再現する。
前提条件
- 両方のサーバーは CentOS 5.4 であること（他ディストリでも小修正で適用可）。
- マスターに exampledb（テーブルとデータ）が既に存在していること。スレーブには存在しない。
- ルート権限で操作できること（sudo でも可）。
- ネットワークでマスターとスレーブが相互に疎通できること（ポート 3306 等）。
重要な固定値（ファクトボックス）:
- 証明書の鍵長: RSA 2048
- 証明書有効期間: 1000 日（チュートリアルでの例）
- バイナリログ保持: expire_logs_days = 10
- 最大バイナリログサイズ: max_binlog_size = 100M
1 MySQL 5 のインストールと SSL サポートの有効化
まず MySQL5 がインストールされていない場合は両方のサーバーでインストールします。
サーバー server1/server2:
yum install mysql mysql-devel mysql-server起動時に自動開始するよう設定し、MySQL を起動します。
chkconfig --levels 235 mysqld on
/etc/init.d/mysqld startroot パスワードを設定します（各サーバーで実行）。
server1:
mysqladmin -u root password yourrootsqlpassword
mysqladmin -h server1.example.com -u root password yourrootsqlpasswordserver2:
mysqladmin -u root password yourrootsqlpassword
mysqladmin -h server2.example.com -u root password yourrootsqlpasswordパスワード未設定だと誰でも MySQL に接続できるので必ず設定してください。
次に両方の MySQL が SSL をサポートしているか確認します。MySQL にログインして次を実行します:
mysql -u root -pMySQL シェルで:
show variables like '%ssl%';出力が次のように have_openssl / have_ssl が DISABLED なら、コンパイル時に SSL サポートはあるが無効になっています。
mysql> show variables like ‘%ssl%’; +—————+———-+ | Variable_name | Value | +—————+———-+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | +—————+———-+ 7 rows in set (0.01 sec)
MySQL シェルを一旦抜けて /etc/my.cnf を編集します。
quit; vi /etc/my.cnf
[mysqld] セクションに ssl 行を追加します（下は例）。
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql
old_passwords=1 ssl
[mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
MySQL を再起動して SSL が有効になったことを確認します。
/etc/init.d/mysqld restart mysql -u root -p show variables like ‘%ssl%’;
有効化されていれば次のように YES が出ます:
mysql> show variables like '%ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
+---------------+-------+
7 rows in set (0.00 sec)
quit; でシェルを抜けます。
注意: 古いパッケージやカスタムビルドではパスやオプションが異なることがあります。ディストリビューションのドキュメントを参照してください。
2 マスター側の設定（証明書作成と my.cnf の更新）
マスターでまずバイナリログ用ディレクトリを作成して所有権を設定します。
server1:
mkdir /var/log/mysql
chown mysql:mysql /var/log/mysqlSSL 用の CA、サーバー、クライアント証明書を作成します。ここでは /etc/mysql/newcerts に作成します。
mkdir -p /etc/mysql/newcerts && cd /etc/mysql/newcertsCA 証明書を作成:
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pemサーバー証明書を作成:
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pemクライアント証明書を作成:
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem作成後、ls -l で次のようにファイルが揃っていることを確認します（出力例はローカル日時などで異なります）。
[root@server1 newcerts]# ls -l
total 32
-rw-r--r-- 1 root root 1375 Feb 8 17:37 ca-cert.pem
-rw-r--r-- 1 root root 1679 Feb 8 17:37 ca-key.pem
-rw-r--r-- 1 root root 1119 Feb 8 17:37 client-cert.pem
-rw-r--r-- 1 root root 1675 Feb 8 17:37 client-key.pem
-rw-r--r-- 1 root root 968 Feb 8 17:37 client-req.pem
-rw-r--r-- 1 root root 1119 Feb 8 17:37 server-cert.pem
-rw-r--r-- 1 root root 1679 Feb 8 17:37 server-key.pem
-rw-r--r-- 1 root root 968 Feb 8 17:37 server-req.pem
[root@server1 newcerts]#スレーブ側（server2）に転送するファイルは ca-cert.pem、client-cert.pem、client-key.pem です。まず server2 に /etc/mysql/newcerts を作成します。
server2:
mkdir -p /etc/mysql/newcertsserver1 から scp で転送します（root の SSH アクセスが有効であることを前提とします）。
server1:
scp /etc/mysql/newcerts/ca-cert.pem [email protected]:/etc/mysql/newcerts
scp /etc/mysql/newcerts/client-cert.pem [email protected]:/etc/mysql/newcerts
scp /etc/mysql/newcerts/client-key.pem [email protected]:/etc/mysql/newcerts次にマスターの /etc/my.cnf を編集して SSL 証明書のパスを追加します。
vi /etc/my.cnf[mysqld] セクションへ次を追加します（完全な例を示します）。
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
ssl
ssl-ca=/etc/mysql/newcerts/ca-cert.pem
ssl-cert=/etc/mysql/newcerts/server-cert.pem
ssl-key=/etc/mysql/newcerts/server-key.pem
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pidMySQL を再起動します。
/etc/init.d/mysqld restartレプリケーション用ユーザー作成
server1 上で、スレーブが接続するためのレプリケーション専用ユーザーを作成します。SSL 接続のみ許可したい場合は REQUIRE SSL を付けます。
mysql -u root -pMySQL シェルで:
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password' REQUIRE SSL;
FLUSH PRIVILEGES;
quit;注: REQUIRE SSL を外すと暗号化されていない接続も許可されます。既存ユーザーを SSL 接続限定に変更するには:
GRANT USAGE ON *.* TO 'slave_user'@'%' REQUIRE SSL;マスターの my.cnf にレプリケーション設定を追加
レプリケーションに必要な設定（サーバー ID、バイナリログなど）を /etc/my.cnf の [mysqld] に追加します。ここでは exampledb を複製対象とします。
[mysqld]
...
ssl
ssl-ca=/etc/mysql/newcerts/ca-cert.pem
ssl-cert=/etc/mysql/newcerts/server-cert.pem
ssl-key=/etc/mysql/newcerts/server-key.pem
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = exampledb設定を反映するために再起動します。
/etc/init.d/mysqld restartデータベースのロックとスナップショット作成
例として exampledb をスレーブに複製するため、マスターでデータをロックして現在の master status（ファイルとポジション）を取得し、その状態でダンプを取りスレーブへ転送します。
server1:
mysql -u root -pMySQL シェル:
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;SHOW MASTER STATUS の出力例（必ずファイル名と Position を記録してください）:
mysql> SHOW MASTER STATUS; +——————+———-+————–+——————+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +——————+———-+————–+——————+ | mysql-bin.000001 | 3096416 | exampledb | | +——————+———-+————–+——————+ 1 row in set (0.00 sec)
MySQL シェルを開いたまま別端末でデータダンプを作成しスレーブへ転送します（ロックを保持したまま行う）。
server1（別シェル）:
cd /tmp mysqldump -u root -pyourrootsqlpassword –opt exampledb > snapshot.sql scp snapshot.sql [email protected]:/tmp
転送が終わったら最初のシェルでロックを解除して終了します。
server1:
UNLOCK TABLES; quit;
---
## 3 スレーブ側の設定（インポートと接続設定）
スレーブ側での作業フロー（概要）:
1. MySQL をインストールし SSL を有効化（第1節参照）
2. マスターから受け取った ca-cert.pem, client-cert.pem, client-key.pem を /etc/mysql/newcerts に配置
3. スレーブの my.cnf を編集して server-id を設定
4. マスターのスナップショットをインポート
5. CHANGE MASTER TO ... で暗号化オプションを含めてマスター接続を設定し START SLAVE
6. SHOW SLAVE STATUS で同期状況を確認
### スレーブ my.cnf の例
server2 の /etc/my.cnf に次を追加または修正します（server-id はマスターと重複しない値にすること）。
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql old_passwords=1 ssl ssl-ca=/etc/mysql/newcerts/ca-cert.pem ssl-cert=/etc/mysql/newcerts/client-cert.pem ssl-key=/etc/mysql/newcerts/client-key.pem server-id = 2
[mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
MySQL を再起動します。
/etc/init.d/mysqld restart
### スナップショットのインポート
server2 で受け取った snapshot.sql をインポートします。
mysql -u root -p CREATE DATABASE IF NOT EXISTS exampledb; quit; mysql -u root -p exampledb < /tmp/snapshot.sql
### マスター接続情報の設定（SSL 指定）
インポート後、スレーブをマスターに接続させます。先ほどマスターで記録した File と Position を使用してください。SSL を使うために MASTER_SSL=1 等のオプションを指定します。
server2:
mysql -u root -p CHANGE MASTER TO MASTER_HOST=’server1.example.com’, MASTER_USER=’slave_user’, MASTER_PASSWORD=’slave_password’, MASTER_LOG_FILE=’mysql-bin.000001’, MASTER_LOG_POS=3096416, MASTER_SSL=1, MASTER_SSL_CA=’/etc/mysql/newcerts/ca-cert.pem’, MASTER_SSL_CERT=’/etc/mysql/newcerts/client-cert.pem’, MASTER_SSL_KEY=’/etc/mysql/newcerts/client-key.pem’;
START SLAVE; SHOW SLAVE STATUS\G quit;
SHOW SLAVE STATUS の主要フィールド（確認ポイント）:
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Last_IO_Error / Last_SQL_Error: 空であること
- Seconds_Behind_Master: 数値（遅延が小さいこと）
SSL 接続であることを確認するには、MySQL の接続情報やネットワークトレースで TLS が確立されているか確認します。MySQL 5 系では CHANGE MASTER TO で MASTER_SSL=1 を使います。
---
## トラブルシューティングとよくある失敗例
1. Slave_IO_Running が No のまま
- ネットワーク疎通（ポート 3306）やユーザーの権限、パスワードを確認。
- マスターの bind-address 設定やファイアウォールを確認。
2. SSL エラー（証明書不一致や権限）
- ca-cert.pem が両方に正しくコピーされているか。
- client-key.pem のパーミッションが厳密であるか（例: 600）。
3. SHOW MASTER STATUS でのポジションの不一致
- スナップショット取得時に FLUSH TABLES WITH READ LOCK を実行しているか確認。
4. レプリケーションの永続的遅延
- 大きなトランザクションや I/O 負荷が原因の可能性がある。長時間実行されるクエリを確認。
デバッグのヒント:
- マスターで `tail -f /var/log/mysqld.log` を見て接続ログを確認。
- スレーブで `SHOW PROCESSLIST;` や `SHOW SLAVE STATUS\G` を頻繁にチェック。
---
## セキュリティとプライバシーの注意点
- SSL 証明書とキーは慎重に保管し、client-key.pem や server-key.pem のパーミッションを 600 に設定してください。
- REQUIRE SSL を使ってレプリケーションユーザーを暗号化接続のみに制限することを推奨します。
- 証明書の有効期限を把握し、期限切れ前に更新計画を立ててください。
- 個人情報（PII）が含まれるデータベースをレプリケーションする際は、適用される法令（GDPR 等）に応じたアクセス管理とログ管理を実施してください。
---
## 受け入れ基準
この構成が正しく動作していると判断するためのチェックリスト（受け入れ基準）:
- マスターで `SHOW MASTER STATUS;` に表示された File と Position を、スレーブの `CHANGE MASTER TO` で使用している。
- スレーブで `SHOW SLAVE STATUS\G` を実行し、Slave_IO_Running と Slave_SQL_Running がともに Yes である。
- `Seconds_Behind_Master` が 0（または実用上許容される小さな値）である。
- レプリケーションユーザーが REQUIRE SSL で作成されている（暗号化接続のみ許可する場合）。
- マスター／スレーブ間の通信が TLS で暗号化されていることを確認済み。
---
## 運用に役立つチェックリスト（役割別）
管理者（マスター担当）:
- [ ] 証明書の作成と安全な保管
- [ ] my.cnf に SSL 設定と binlog 設定を追加
- [ ] レプリケーション用ユーザー作成（REQUIRE SSL が望ましい）
- [ ] スナップショット取得時に FLUSH TABLES WITH READ LOCK を実行
運用担当（スレーブ担当）:
- [ ] ca-cert.pem, client-cert.pem, client-key.pem を正しく配置
- [ ] server-id を一意に設定
- [ ] スナップショットのインポートを実施
- [ ] CHANGE MASTER TO で SSL オプションを指定し START SLAVE を実行
- [ ] 定期的に SHOW SLAVE STATUS を確認
---
## テストケース / 受け入れ試験
1. 新しいレコードをマスターで作成し、スレーブに即時反映されることを確認する。
2. マスターで大きなトランザクションを実行し、スレーブが正常に追従することを確認する。
3. マスターの証明書を一時的に無効化した場合、スレーブの接続が失敗すること（REQUIRE SSL を使用している場合）。
4. スレーブ再起動後に自動的にレプリケーションが再開すること。
---
## 保守と運用のヒント
- バイナリログはディスクを消費するため expire_logs_days と max_binlog_size を適切に設定してください。
- 定期的に `SHOW MASTER STATUS` と `SHOW SLAVE STATUS\G` を監視し、アラートを設定してください。
- 証明書のローテーションを計画し、スムーズに差し替えられる手順を文書化しておきます。
- 大規模なデータ移行やスキーマ変更時はレプリケーションを一時停止してから作業することを検討してください。
---
## ミニ手順（まとめ）
1. 両サーバーに MySQL をインストールして起動。
2. /etc/my.cnf に ssl を追加し MySQL を再起動。
3. マスターで /etc/mysql/newcerts に CA/サーバ/クライアント証明書を作成。
4. ca-cert.pem, client-cert.pem, client-key.pem をスレーブに転送。
5. マスターの my.cnf に ssl-ca/cert/key とバイナリログ設定を追加。
6. レプリケーション用ユーザーを作成（REQUIRE SSL 推奨）。
7. マスターで FLUSH TABLES WITH READ LOCK と SHOW MASTER STATUS を実行しスナップショットを取得。
8. スレーブにスナップショットをインポート。
9. スレーブで my.cnf を設定（server-id, SSL 設定）し CHANGE MASTER TO により接続設定、START SLAVE。
10. SHOW SLAVE STATUS\G で状態を確認。
---
## 用語集（1 行）
- SSL/TLS: 通信を暗号化するプロトコル。MySQL では証明書で TLS を確立。
- CA: 証明書を署名する認証局（ここでは自己署名 CA を作成）。
- binlog: マスターのトランザクションを記録するバイナリログ。
---
## まとめ
- CentOS 5.4 環境で MySQL レプリケーションを SSL で暗号化するには、MySQL の SSL 有効化、証明書の作成と配置、my.cnf の SSL 設定、レプリケーションユーザーの作成、マスターのスナップショット取得、スレーブのインポートと CHANGE MASTER TO が必要です。
- セキュリティ面では鍵の保護、REQUIRE SSL の使用、証明書有効期限の管理が重要です。
- 運用面では定期的なステータス確認とログ監視、バイナリログの管理が必須です。
補足: 本手順は一般的な設定例を示したものであり、環境によりパスや設定を調整してください。問題が発生したらログと SHOW SLAVE STATUS を確認して原因を特定してください。
