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を参考にライブラリを作る。