さくらのVPS-MariaDBでレプリケーション設定をする

さくらのVPSでMariaDBのレプリケーション設定をしていきたいと思います。前提として下記の条件が整っているとします。

DBサーバー1(Master)：192.168.0.2
DBサーバー2(Slave)：192.168.0.3
CentOS7
MariaDB:mysql Ver 15.1 Distrib 5.5.47-MariaDB

DB2台のOS、MariaDBのバージョンは二つとも同じとします。

マスター側の設定

レプリケーションのユーザー設定

レプリケーションユーザー：repl
レプリケーションは192.168.0.0/24のネットワークからの許可
レプリケーションユーザーのパスワードはpasswordとします

`1`	`MariaDB [(none)]> GRANT REPLICATION SLAVE ON . TO` `'repl'@'192.168.0.0/255.255.255.0'` `IDENTIFIED BY` `'password';`

my.cnfに設定を追記

my.confに設定を追記します。ここで間違ってはいけないのは[mysqld]の下に追記します。[mysqld_safe]に追記すると失敗しますので気をつけてください。

01[mysqld]
02datadir=/var/lib/mysql
03socket=/var/lib/mysql/mysql.sock
04# Disabling symbolic-links is recommended to prevent assorted security risks
05symbolic-links=0
06# Settings user and group are ignored when systemd is used.
07# If you need to run mysqld under a different user or group,
08# customize your systemd unit file for mariadb according to the
09# instructions in http://fedoraproject.org/wiki/Systemd
10 
11#ここに追記
12#add
13log_bin=/var/log/mariadb/binlog
14server-id=1
15expire_logs_days=5
16 
17 
18[mysqld_safe]
19・・・

このようにします。

log-bin でレプリケーションに必要なバイナリログを有効にします
server-id はレプリケーションを構成するサーバー内で被らないように割り振ります
expire_logs_days はバイナリログの保存日数です。レプリケーションに間が空いた時に復旧できるようにする期間を指定します

マスターのダンプ

マスター側のデータベースをダンプするため設定をします。

rootユーザーでDBにアクセス

01mysql -u root -p
02Enter password: 
03Welcome to the MariaDB monitor.  Commands end with ; or \g.
04Your MariaDB connection id is 2862
05Server version: 5.5.47-MariaDB-log MariaDB Server
06 
07Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
08 
09Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
10 
11MariaDB [(none)]>

読み書きロックを行います

`1`	`MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;`

`2`	`Query OK, 0 rows affected (0.00 sec)`

バイナリログのFileとPositionを確認します

1SHOW MASTER STATUS;
2+---------------+----------+--------------+------------------+
3| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
4+---------------+----------+--------------+------------------+
5| binlog.000001 | 33424169 |              |                  |
6+---------------+----------+--------------+------------------+

mysqldump でマスターのDBをファイルに書き出します

`1`	`mysqldump -u root -p --all-databases --lock-all-tables > /tmp/dbdump.db`

`2`	`Enter password:`

読み書きロックを解除します

01mysql -u root -p
02Enter password: 
03Welcome to the MariaDB monitor.  Commands end with ; or \g.
04Your MariaDB connection id is 2862
05Server version: 5.5.47-MariaDB-log MariaDB Server
06 
07Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
08 
09Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
10 
11MariaDB [(none)]> UNLOCK TABLES;

SCPでデータを送る

ダンプしたファイルをスレーブ側へ送ってあげます。

`1`	`[root@] # scp /tmp/dbdump.db root@192.168.0.3:/tmp/`

`2`	`password:パスワードを入力`

MariaDBの再起動

`1`	`[root@] # systemctl restart mariadb`

再起動と転送できたらこれでマスター側の構築を終了します。次にスレーブ側の設定をします

スレーブ側の設定

スレーブ側は新しいターミナルを立ち上げても現在のマスターからsshでログインしてもどちらでも構いません

1ssh root@192.168.0.3
2 
3root@192.168.20.3's password:
4password:

mysql_install_db を実行

`1`	`[root@] # mysql_install_db --user=mysql`

my.cnfに設定を追加

my.cnfに設定を追加します。今回設定するのはサーバーIDとデータベースを読み込み専用にするためのものです。

01[mysqld]
02datadir=/var/lib/mysql
03socket=/var/lib/mysql/mysql.sock
04# Disabling symbolic-links is recommended to prevent assorted security risks
05symbolic-links=0
06# Settings user and group are ignored when systemd is used.
07# If you need to run mysqld under a different user or group,
08# customize your systemd unit file for mariadb according to the
09# instructions in http://fedoraproject.org/wiki/Systemd
10#add
11server-id=2 #追加
12read_only　#追加
13 
14[mysqld_safe]
15・・・

これも設定の記述する位置に注意が必要です。mysqld_safeに記述しないように注意が必要です。

server-idはMySQLのホストを区別するための番号です。複数のスレーブを設置する場合、被らないように別々の数字を割り当ててください
read_onlyはデーターベースを読み込み専用にするためのオプションです。スレーブに誤って更新クエリを行ってしまうとレプリケーションが止まってしまうため、それを防止する目的で設定します

MariaDBの再起動

スレーブ側のMariaDBが起動していた場合は再起動をしてください。もし起動していない場合は起動してください。

1#再起動コマンド
2[root@] # systemctl restart mariadb
3 
4#起動コマンド
5[root@] # systemctl start mariadb

となります。

マスターで書き出したデータベースの復元

`1`	`mysql --user=root > /tmp/dbdump.db`

rootのパスワードの変更

`1`	`mysqladmin -u root password` `'password'`

passwordの所に新しいパスワードをいれてください。

スレーブDBの設定

01mysql -u root -p
02Enter password: 
03Welcome to the MariaDB monitor.  Commands end with ; or \g.
04Your MariaDB connection id is 2862
05Server version: 5.5.47-MariaDB-log MariaDB Server
06 
07Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
08 
09Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
10 
11MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.2', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=33424169;
12 
13Query OK, 0 rows affected (0.25 sec)

スレーブをスタートします

`1`	`MariaDB [(none)]> START SLAVE;`

`2`	`Query OK, 0 rows affected, 1 warning (0.01 sec)`

状態の確認

レプリケーションが無事にできているか確認します。

マスター側

01mysql -u root -p
02Enter password: 
03Welcome to the MariaDB monitor.  Commands end with ; or \g.
04Your MariaDB connection id is 2862
05Server version: 5.5.47-MariaDB-log MariaDB Server
06 
07Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
08 
09Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
10MariaDB [(none)]> SHOW MASTER STATUS;
11+---------------+----------+--------------+------------------+
12| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
13+---------------+----------+--------------+------------------+
14| binlog.000001 | 55101492 |              |                  |
15+---------------+----------+--------------+------------------+

スレーブ側

01mysql -u root -p
02Enter password: 
03Welcome to the MariaDB monitor.  Commands end with ; or \g.
04Your MariaDB connection id is 2862
05Server version: 5.5.47-MariaDB-log MariaDB Server
06 
07Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
08 
09Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
10MariaDB [(none)]> SHOW SLAVE STATUS\G
11*************************** 1. row ***************************
12               Slave_IO_State: Waiting for master to send event
13                  Master_Host: 192.168.0.2
14                  Master_User: repl
15                  Master_Port: 3306
16                Connect_Retry: 60
17              Master_Log_File: binlog.000001
18          Read_Master_Log_Pos: 55101492
19               Relay_Log_File: mariadb-relay-bin.000002
20                Relay_Log_Pos: 55071500
21        Relay_Master_Log_File: binlog.000001
22             Slave_IO_Running: Yes
23            Slave_SQL_Running: Yes
24              Replicate_Do_DB: 
25          Replicate_Ignore_DB: 
26           Replicate_Do_Table: 
27       Replicate_Ignore_Table: 
28      Replicate_Wild_Do_Table: 
29  Replicate_Wild_Ignore_Table: 
30                   Last_Errno: 0
31                   Last_Error: 
32                 Skip_Counter: 0
33          Exec_Master_Log_Pos: 55101492
34              Relay_Log_Space: 55071796
35              Until_Condition: None
36               Until_Log_File: 
37                Until_Log_Pos: 0
38           Master_SSL_Allowed: No
39           Master_SSL_CA_File: 
40           Master_SSL_CA_Path: 
41              Master_SSL_Cert: 
42            Master_SSL_Cipher: 
43               Master_SSL_Key: 
44        Seconds_Behind_Master: 0
45Master_SSL_Verify_Server_Cert: No
46                Last_IO_Errno: 0
47                Last_IO_Error: 
48               Last_SQL_Errno: 0
49               Last_SQL_Error: 
50  Replicate_Ignore_Server_Ids: 
51             Master_Server_Id: 1
521 row in set (0.00 sec)

Slave_IO_RunningとSlave_SQL_RunningがYesになっていること
Master_Log_File と Read_Master_Log_Pos がマスターで確認したFile、Positionと一致していること

一致していればレプリケーション成功です。

参考サイト

http://qiita.com/suzutsuki0220/items/e5be03ea8f44ad2f6533

個人支援・寄付について

サイトラボでは個人支援・寄付を受けております。ご協力いただける方はお願いいたします。当サイトではビットコインで受け付けております。

ビットコイン：3LHnADwZwUbic2L45EnVJEykiG6KfbqrwS

HyperDBを使うと真っ白な画面になる owncloudをインストールして設定する