VirtualBox / Vagrant / php7.1 / mysql5.6 でレプリケーション実装

master/slaveの環境構築

/master
- /sync
- Vagrantfile
/slave
- /sync
- Vagrantfile

# centos7
vagrant init centos/7
vagrant up
vagrant ssh

# ip check
# ip a | grep 192

# apache
sudo yum -y install httpd
sudo systemctl start httpd.service
sudo systemctl enable httpd.service

# PHP 7.1 install https://www.tecmint.com/install-php-7-in-centos-7/
sudo yum install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
sudo yum install -y http://rpms.remirepo.net/enterprise/remi-release-7.rpm
sudo yum install -y yum-utils
sudo yum-config-manager --enable remi-php71  # Install PHP 7.1
sudo yum install -y php php-mysql php-mbstring php-simplexml php-intl
# php -v

# other package
sudo yum install -y git zip unzip vim

# mysql5.6 install https://weblabo.oscasierra.net/installing-mysql56-centos7-yum/
sudo yum remove -y mariadb-libs
sudo yum localinstall -y http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm
sudo yum -y install yum-utils
sudo yum repolist all | grep mysql
sudo yum-config-manager --disable mysql57-community
sudo yum-config-manager --enable mysql56-community
sudo yum -y install mysql-community-server
# mysqld --version

sudo systemctl enable mysqld
sudo systemctl start mysqld
# sudo systemctl stop mysqld

# https://blog.codecamp.jp/root-password
mysql -u root
mysql> update mysql.user set password=password('root') where user = 'root';
mysql> flush privileges;
mysql> quit
sudo systemctl restart mysqld.service


# firewall disable
sudo systemctl stop firewalld
sudo systemctl disable firewalld
# sudo systemctl status firewalld

# SELINUX disable
sudo vim /etc/selinux/config
SELINUX=disabled

exit
vagrant halt

# synced_folder用
vagrant plugin install vagrant-vbgust

Vagrantfile / master

mkdir master/sync

config.vm.box = "centos/7"
config.vm.network "private_network", ip: "192.168.33.10"
config.vm.synced_folder "./sync", "/home/vagrant/sync"

Vagrantfile / slave

mkdir slave/sync

config.vm.box = "centos/7"
config.vm.network "private_network", ip: "192.168.33.20"
config.vm.synced_folder "./sync", "/home/vagrant/sync"

レプリケーションとはなにか

masterサーバのレプリカとしてslaveを作成する。
masterサーバの更新処理(insert, delete, updateなど)を実行したとき、
自動的にslaveに反映される。 (リアルタイム更新のレプリカ)
逆にslaveで更新処理を実行してもmasterには反映されない。
ので、slave側ではread_onlyを使いslaveの更新処理に対してはエラーを出すように
すると良い。

なぜレプリケーションをするのか

更新処理(inser, delete, updateなど)をmasterで実行し、
参照(select)をslaveで実行することで、masterの負荷を減らす。

レプリケーションの設定

参考 https://qiita.com/ksugawara61/items/fdd5ae9b78931540887f

master 設定

sudo vim /etc/my.cnf

# log-bin バイナリロギングの有効
# server-id 一意サーバIDを作成
[mysqld]
log-bin=mysql-bin
server-id=1

mysql -u root -p
mysql> create database example;

mysql> create user 'repl'@'192.168.33.20' identified by 'slave';
mysql> grant replication slave on *.* to 'repl'@'192.168.33.20';
# mysql> select user, host from mysql.user;
# mysql> show grants for 'repl'@'192.168.33.20';

mysql> flush tables with read lock;

mysql> show master status; # file名とpositionを記録しておく。(slaveで記述するので)
# Empty setだった場合、file='', position=4となる

mysql> quit

mysqldump --single-transaction -u root -p exmaple > example.dump

mysqlにログイン

mysql> unlock tables;

slave設定

# server-id 一意サーバIDを作成
# read_only 更新処理に対してエラーを出す 
sudo vim /etc/my.cnf
[mysqld]
server-id=2
read_only

mysqlにログイン
mysql> create database example;
mysql> use example;
mysql> source ~/example.dump;
mysql> change master to
      -> master_host='192.168.33.10',
      -> master_user='repl',
      -> master_password='slave',
      -> master_log_file='xxxxxxxxxxx',
      -> master_log_pos=xxx;
mysql> start slave;
mysql> show slave status\G # エラーがなければ成功

テスト

masterに接続
mysql> use example;
mysql> create table hoge (id INT not null);
mysql> show tables;

slaveに接続
mysql> use example;
mysql> show tables;

hogeテーブルがslaveにもあったら成功

master and slave have equal MySQL server UUIDs https://blog.apar.jp/linux/3163/

read_only用のSUPER権限を持たないユーザをslaveに作成

slaveに接続
mysqlログイン
mysql> create user 'slave'@'192.168.33.10' identified by 'slave';
mysql> grant select on *.*  to 'slave'@'192.168.33.10';
# msyql> show grants for 'slave'@'192.168.33.10';

テスト (master側に生成)

test.php

<?php

class PdoTest {
    private $connect = null;
    public function __construct(array $param) {
        $this->connect = new \PDO($param['dsn'], $param['user'], $param['password']);
        $this->connect->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    }

    public function execute(string $sql, array $param = []) {
        $stmt = $this->connect->prepare($sql);
        $stmt->execute($param);
        return $stmt;
    }

    public function select(): void {
        $sql = 'SELECT * FROM hoge';
        var_dump($this->execute($sql)->fetchAll(\PDO::FETCH_ASSOC));
    }

    public function insert(int $id): void {
        $sql = 'INSERT INTO hoge (id) VALUES (:id)';
        $this->execute($sql, [':id' => $id]);
    }

    public function close(): void {
        unset($this->connect);
    }
}

function testMaster() {
    $param = [
        'dsn' => 'mysql:dbname=example;host=localhost',
        'user' => 'root',
        'password' => 'root',
    ];
    $master = new \PdoTest($param);
    $master->select();
    $master->insert(10);
    $master->close();
}

function testSlave() {
    $param = [
        'dsn' => 'mysql:dbname=example;host=192.168.33.20',
        'user' => 'slave',
        'password' => 'slave',
    ];
    $slave = new \PdoTest($param);
    $slave->select();
    $slave->insert(20);
    $slave->close();
}

try {
    testMaster();
    testSlave();
} catch(\PDOException $e) {
    echo $e->getMessage();
} catch(\Exception $e) {
    echo $e->getMessage();
}

php test.php

SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --read-only option so it cannot execute this statement
が出ればread_only成功

http://blog.asial.co.jp/1213を参考にライブラリを作る。