MySQLのReplication設定

2023年1月22日2023年2月9日

ソース（マスター）サーバへのRepliction設定追加

mysql-server.cnfにレプリケーション用の設定を追加

[mysqld]
（省略）

### Start GTID replication settings
bind-address=【マスターサーバのIPアドレス】
log_bin=/var/log/mysql/mysql-bin.log

server-id=【マスター/レプリカサーバ全体でユニークになる番号】

gtid_mode=ON
enforce-gtid-consistency=ON
### End GTID replication settings

MySQLの再起動

sudo systemctl restart mysqld.service

レプリケーション用MySQLユーザの作成

mysqlコンソールでユーザを作成して権限を付与する。

mysql> CREATE USER '【レプリケーションユーザ名】'@'【レプリカサーバIP】' IDENTIFIED WITH mysql_native_password BY '【任意のパスワード】';
mysql> GRANT REPLICATION SLAVE ON *.* TO '【レプリケーションユーザ名】'@'【レプリカサーバIP】';
mysql> FLUSH PRIVILEGES;

ソースサーバへのRepliction設定追加

mysql-server.cnfにレプリケーション用の設定を追加

[mysqld]
（省略）

### Start GTID replication settings
log_bin=/var/log/mysql/mysql-bin.log
relay-log=/var/log/mysql/mysql-relay-bin.log

server-id=【マスター/レプリカサーバ全体でユニークになる番号】

gtid_mode=ON
enforce-gtid-consistency=ON
log-replica-updates=ON
skip-replica-start=ON
### End GTID replication settings

MySQLの再起動

sudo systemctl restart mysqld.service

データベースを読み取り専用に設定

mysqlコンソールで下記コマンドを実行し、データベースを読み取り専用にする。

mysql> SET @@GLOBAL.read_only = ON;

レプリケーションの開始

ソースサーバで行う作業

mysqlコンソールで下記コマンドを実行し、データベースを読み取り専用にする。

mysql> SET @@GLOBAL.read_only = ON;

データベースのダンプを取得する。

mysqldump -u root -p 【データベース名】 --set-gtid-purged=OFF > 【ダンプファイル名】

このダンプファイルはレプリカサーバにリストアするため、環境に応じた適当な方法でレプリカサーバからアクセスできる場所に保存する。

レプリカサーバで行う作業

mysqlコンソールに接続して下記コマンドを実行し、データベースを読み取り専用にする。

mysql> SET @@GLOBAL.read_only = ON;

データベースのダンプをリストアする。

mysql -u root -p 【データベース名】 < 【ダンプファイル名】

引き続き下記コマンドを実行し、レプリケーション元サーバの設定を行う。

mysql> CHANGE REPLICATION SOURCE TO
    -> SOURCE_HOST='【マスターサーバのIPアドレス】',
    -> SOURCE_USER='【レプリケーション用ユーザ名】',
    -> SOURCE_PASSWORD='【レプリケーション用ユーザのパスワード】',
    -> SOURCE_AUTO_POSITION=1;

下記コマンドを実行し、レプリケーションを開始する。

mysql> START REPLICA;

レプリケーション状態の確認

ソースサーバでの確認

mysqlコンソールで下記コマンドを実行する。

show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 704
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 3d7a6e44-58f0-11ed-8a29-020304cb002e:1-2
1 row in set (0.00 sec)

レプリカサーバでの確認

mysqlコンソールで下記コマンドを実行する。

mysql> show replica status\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
              Source_Log_File: mysql-bin.000002
          Read_Source_Log_Pos: 704
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
           Retrieved_Gtid_Set: 3d7a6e44-58f0-11ed-8a29-020304cb002e:1-2
※必要箇所のみの抜粋

Replica_IO_Stateが”Waiting for source to send event”、Replica_IO_RunnginとReplica_SQL_RunningがYes、ソースサーバとレプリカサーバの値が整合していればOK。

MySQL/MariaDB