read_only replica にする練習
元記事
この記事の続き
現状確認 in slave-db
mysql --defaults-extra-file=mariadb_root.cnf SHOW VARIABLES LIKE '%read_only%'; SHOW GLOBAL VARIABLES LIKE '%read_only%';
log
MariaDB [(none)]> SHOW VARIABLES LIKE '%read_only%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_read_only | OFF | | read_only | OFF | | tx_read_only | OFF | +------------------+-------+ 3 rows in set (0.002 sec) MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%read_only%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_read_only | OFF | | read_only | OFF | | tx_read_only | OFF | +------------------+-------+ 3 rows in set (0.001 sec) MariaDB [(none)]>
同じなのかな？
server.cnf を編集する
sudo cp -aiv \ /etc/my.cnf.d/server.cnf \ /etc/my.cnf.d/server.cnf.$(date +%F_%H-%M-%S) sudo vim /etc/my.cnf.d/server.cnf [mysqld] セクションに read_only を追記
diff
sudo diff -uprN \ /etc/my.cnf.d/server.cnf.2021-08-12_11-12-03 \ /etc/my.cnf.d/server.cnf
log
[ope@slave-db0 ~]$ sudo diff -uprN \ > /etc/my.cnf.d/server.cnf.2021-08-12_11-12-03 \ > /etc/my.cnf.d/server.cnf --- /etc/my.cnf.d/server.cnf.2021-08-12_11-12-03 2021-08-11 11:22:17.658896135 +0900 +++ /etc/my.cnf.d/server.cnf 2021-08-12 11:18:50.477378529 +0900 @@ -19,6 +19,7 @@ server_id=103 # auto_increment_increment=2 # 2 からカウントを始める # auto_increment_offset=2 +read_only # # * Galera-related settings [ope@slave-db0 ~]$
slave-db0 の mariadb を restart
sudo systemctl restart mariadb sudo systemctl status mariadb
slave-db0 のステータス確認
mysql --defaults-extra-file=mariadb_root.cnf SHOW VARIABLES LIKE '%read_only%'; SHOW GLOBAL VARIABLES LIKE '%read_only%';
log
MariaDB [(none)]> SHOW VARIABLES LIKE '%read_only%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_read_only | OFF | | read_only | ON | | tx_read_only | OFF | +------------------+-------+ 3 rows in set (0.002 sec) MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%read_only%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_read_only | OFF | | read_only | ON | | tx_read_only | OFF | +------------------+-------+ 3 rows in set (0.001 sec) MariaDB [(none)]>
これで read_only が ON になった
一応レプリケーションステータスも確認
SHOW ALL REPLICAS STATUS \G
log
MariaDB [(none)]> SHOW ALL REPLICAS STATUS \G *************************** 1. row *************************** Connection_name: Con1 Slave_SQL_State: Slave has read all relay log; waiting for more updates Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.102 Master_User: replication_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: radius0-bin.000001 Read_Master_Log_Pos: 619 Relay_Log_File: slave-db0-relay-bin-con1.000002 Relay_Log_Pos: 674 Relay_Master_Log_File: radius0-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ... *************************** 2. row *************************** Connection_name: Con2 Slave_SQL_State: Slave has read all relay log; waiting for more updates Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.103 Master_User: replication_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: radius1-bin.000001 Read_Master_Log_Pos: 1402059 Relay_Log_File: slave-db0-relay-bin-con2.000002 Relay_Log_Pos: 674 Relay_Master_Log_File: radius1-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ...
OK
read_only が効果を発揮するかテスト
これを確認すると、 read_only を有効にした場合、 SUPER privilege と READ ONLY ADMIN privilege なら書き込みが出来るとのこと。あと、プライマリから更新するレプリカサーバは更新される
まず、権限の確認
SELECT user, Super_priv FROM mysql.user ;
log
MariaDB [(none)]> SELECT user, Super_priv FROM mysql.user ; +------------------+------------+ | User | Super_priv | +------------------+------------+ | mariadb.sys | N | | root | Y | | mysql | Y | | radius | N | | replication_user | N | +------------------+------------+ 5 rows in set (0.002 sec) MariaDB [(none)]>
radius user が Super_priv をもっていないことが分かった
radius user で insert テスト in slave-db0
mysql --defaults-extra-file=mariadb_radius.cnf radius SELECT * FROM radcheck; INSERT INTO radcheck (username, attribute, op, value) VALUES ('foo3', 'Cleartext-Password', ':=', 'foo3pass') ; SELECT * FROM radcheck;
log
MariaDB [radius]> SELECT * FROM radcheck; +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | | 2 | foo2 | Cleartext-Password | := | foo2pass | +----+----------+--------------------+----+----------+ 2 rows in set (0.001 sec) MariaDB [radius]> INSERT INTO radcheck -> (username, attribute, op, value) -> VALUES -> ('foo3', 'Cleartext-Password', ':=', 'foo3pass') -> ; ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement MariaDB [radius]> MariaDB [radius]> MariaDB [radius]> SELECT * FROM radcheck; +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | | 2 | foo2 | Cleartext-Password | := | foo2pass | +----+----------+--------------------+----+----------+ 2 rows in set (0.001 sec) MariaDB [radius]>
きちんと、 read-only オプションがあるのでこのステートメントは実行できないとエラーまででてくれて偉い
radius0 で INSERT して slave-db0 に反映されるかテスト
radius0
mysql --defaults-extra-file=mariadb_radius.cnf radius SHOW VARIABLES LIKE 'hostname'; SELECT * FROM radcheck;
log
MariaDB [radius]> SHOW VARIABLES LIKE '%hostname%'; +---------------+---------+ | Variable_name | Value | +---------------+---------+ | hostname | radius0 | +---------------+---------+ 1 row in set (0.003 sec) MariaDB [radius]> SELECT * FROM radcheck; +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | | 2 | foo2 | Cleartext-Password | := | foo2pass | +----+----------+--------------------+----+----------+ 2 rows in set (0.001 sec) MariaDB [radius]>
slave-db0
SHOW VARIABLES LIKE 'hostname'; SELECT * FROM radcheck;
log
MariaDB [radius]> SHOW VARIABLES LIKE 'hostname'; +---------------+------------------+ | Variable_name | Value | +---------------+------------------+ | hostname | slave-db0.tk.net | +---------------+------------------+ 1 row in set (0.002 sec) MariaDB [radius]> SELECT * FROM radcheck; +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | | 2 | foo2 | Cleartext-Password | := | foo2pass | +----+----------+--------------------+----+----------+ 2 rows in set (0.001 sec) MariaDB [radius]>
更新
radius0
SHOW VARIABLES LIKE 'hostname'; INSERT INTO radcheck (username, attribute, op, value) VALUES ('foo3', 'Cleartext-Password', ':=', 'foo3pass') ; SELECT * FROM radcheck;
radius0 log
MariaDB [radius]> SHOW VARIABLES LIKE 'hostname'; +---------------+---------+ | Variable_name | Value | +---------------+---------+ | hostname | radius0 | +---------------+---------+ 1 row in set (0.002 sec) MariaDB [radius]> INSERT INTO radcheck -> (username, attribute, op, value) -> VALUES -> ('foo3', 'Cleartext-Password', ':=', 'foo3pass') -> ; Query OK, 1 row affected (0.014 sec) MariaDB [radius]> SELECT * FROM radcheck; +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | | 2 | foo2 | Cleartext-Password | := | foo2pass | | 3 | foo3 | Cleartext-Password | := | foo3pass | +----+----------+--------------------+----+----------+ 3 rows in set (0.001 sec) MariaDB [radius]>
slave-db0
MariaDB [radius]> SHOW VARIABLES LIKE 'hostname'; +---------------+------------------+ | Variable_name | Value | +---------------+------------------+ | hostname | slave-db0.tk.net | +---------------+------------------+ 1 row in set (0.002 sec) MariaDB [radius]> SELECT * FROM radcheck; +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | | 2 | foo2 | Cleartext-Password | := | foo2pass | | 3 | foo3 | Cleartext-Password | := | foo3pass | +----+----------+--------------------+----+----------+ 3 rows in set (0.001 sec) MariaDB [radius]>
問題なく更新された
まとめ
/etc/my.cnf.d/server.cnf の [mysqld] セクションに read_only を追記するとそのデータベースは read_only となる
read_only のときに書き込みが出来るのは
- SUPER 権限
- READ ONLY ADMIN 権限 (ただ
mysql.userテーブルに見当たらない) - PRIMARY から REPLICA に向けての更新
だけである
SUPER 権限の確認は SELECT user, super_priv FROM mysql.user; で可能
次回
ちゃんとフェイルオーバーについてやりたい
