Help us understand the problem. What is going on with this article?

[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.000003154

# 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)

以上です!終わり。

hikaru_
アウトプット、メモ用です。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした