[MySQL] 1つのサーバ内でMySQLを複数起動してレプリケーション設定

はじめに

1台構成のステージング環境でもレプリケーション組む機会があったのでメモ。

バージョン

CentOS version

# cat /etc/redhat-release
CentOS Linux release 7.1.1503 (Core)

MySQL version

# mysqladmin --version
mysqladmin  Ver 8.42 Distrib 5.7.19, for Linux on x86_64

MySQL 5.7.13以下は方法が違うので注意。

2つ目のMySQLを立ち上げる

[STEP1] ディレクトリ作成

まずはslave用のディレクトリ作成して、権限変更。

# mkdir /var/lib/mysqld/slave
# chown -R mysql:mysql mysqld

[STEP2] my.cnf設定

my.cnfの設定追加

[mysqld]
server-id=1
log-bin=mysql_bin

[mysqld@slave]
server-id=2
datadir=/var/lib/mysqld/slave
socket=/var/lib/mysqld/slave/mysql.sock
log-error=/var/log/mysqld-slave.log
port=3307

mysqld再起動

# systemctl restart mysqld

my.cnfで設定したmysqld@slaveを起動&確認

# systemctl start mysqld@slave
# systemctl status mysqld@slave

[STEP3] パスワード変更

mysqld@slaveのパスワード確認

# cat /var/log/mysqld-slave.log | grep "temporary password" 

mysqld@slaveにログインしてパスワード変更

# mysql -h 127.0.0.1 -p -P 3307

mysql> SET password for root@localhost=password('パスワード');
mysql> FLUSH PRIVILEGES;

レプリケーション設定

[STEP1] slaveにデータを流す

mysqld@slaveにデータベース作成

mysql> create database スキーマ名;

masterからダンプをとる

# mysqldump --single-transaction --master-data=2 -u root -p スキーマ名 > hoge.sql

mysqld@slaveに流す

# mysql -h 127.0.0.1 -p -P 3307 スキーマ名 < hoge.sql

dumpのPosition確認してメモっておく。今回はmysql_bin.000003と154

# head -50 hoge.sql | grep 'CHANGE MASTER'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000003', MASTER_LOG_POS=154;

[STEP2] masterにレプリケーション用の接続ユーザ作成

レプリケーション用のユーザを作成する。

mysql> GRANT REPLICATION SLAVE ON *.* TO 'ユーザ名'@'localhost' IDENTIFIED BY 'パスワード';
mysql> FLUSH PRIVILEGES;

[STEP3] slave設定

slaveをrootでも更新不可にする。

[mysqld@slave]
server-id=2
datadir=/var/lib/mysqld/slave
socket=/var/lib/mysqld/slave/mysql.sock
log-error=/var/log/mysqld-slave.log
port=3307

+ super_read_only=1

slave再起動

# systemctl restart mysqld@slave

mysqld@slaveに接続して設定

# mysql -h 127.0.0.1 -p -P 3307

masterの情報と、前にメモったpositionを入力

mysql> CHANGE MASTER TO
    -> MASTER_HOST='localhost',
    -> MASTER_PORT=3306,
    -> MASTER_USER='ユーザ名',
    -> MASTER_PASSWORD='パスワード',
    -> MASTER_LOG_FILE='mysql_bin.000003',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 1 warning (0.02 sec)

slave起動！！

mysql> start slave;

最後に確認！

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: ユーザ名
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000006
          Read_Master_Log_Pos: 597
               Relay_Log_File: localhost-relay-bin.000005
                Relay_Log_Pos: 810
        Relay_Master_Log_File: mysql_bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                             .
                             .
                             .
1 row in set (0.00 sec)

以上です！終わり。