hypermkt blog

my.cnfのread_onlyについて調べた

November 25, 2015

概要

某所でMySQLのスレーブサーバーを構築することになり、友人から read_only権限について教えてもらったので改めて自分で調べなおしてみた。

read_onlyとは?

  • MySQLのスレーブサーバーでは my.cnfで read_only設定をするとSUPER権限を持つアカウント以外の更新クエリーは実行できなくなる

疑問

  • rootアカウントが書き込み権限を持つアカウントを発行しようとするとどうなるのか 予測1. 作成できない 予測2. SUPER権限有りで作成できる

検証環境

  • Vagrant
  • CentOS release 6.5 (Final)
  • Server version: 5.5.46 MySQL Community Server (GPL)

テスト環境を用意した

初期状態

mysql> select * from user\G

  • ** 1. row *** Host: localhost User: root Password: Selectpriv: Y Insertpriv: Y Updatepriv: Y Deletepriv: Y Createpriv: Y Droppriv: Y Reloadpriv: Y Shutdownpriv: Y Processpriv: Y Filepriv: Y Grantpriv: Y Referencespriv: Y Indexpriv: Y Alterpriv: Y Showdbpriv: Y Superpriv: Y Createtmptablepriv: Y Locktablespriv: Y Executepriv: Y Replslavepriv: Y Replclientpriv: Y Createviewpriv: Y Showviewpriv: Y Createroutinepriv: Y Alterroutinepriv: Y Createuserpriv: Y Eventpriv: Y Triggerpriv: Y Createtablespacepriv: Y ssltype: sslcipher: x509issuer: x509subject: maxquestions: 0 maxupdates: 0 maxconnections: 0 maxuserconnections: 0 plugin: authentication_string: 1 row in set (0.00 sec)

mysql> select * from db\G

  • ** 1. row *** Host: localhost Db: root User: root Selectpriv: Y Insertpriv: Y Updatepriv: Y Deletepriv: Y Createpriv: Y Droppriv: Y Grantpriv: N Referencespriv: Y Indexpriv: Y Alterpriv: Y Createtmptablepriv: Y Locktablespriv: Y Createviewpriv: Y Showviewpriv: Y Createroutinepriv: Y Alterroutinepriv: Y Executepriv: Y Eventpriv: Y Triggerpriv: Y 1 row in set (0.00 sec)

mysql>

grant all privilegesのアカウントを作成する

この時点では test_userアカウントは hogeデータベースに対して全権限があるとしてアカウントは作成された。ここは普通なんだね。

GRANT ALL PRIVILEGES ON hoge.* TO testuser@localhost; SET PASSWORD FOR ‘testuser’@‘localhost’ = PASSWORD(‘fugafuga’); flush privileges;

mysql> GRANT ALL PRIVILEGES ON hoge.* TO test_user@localhost; Query OK, 0 rows affected (0.00 sec)

mysql> SET PASSWORD FOR ‘test_user’@‘localhost’ = PASSWORD(‘fugafuga’); Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

mysql> select * from user\G

  • ** 1. row *** Host: localhost User: root Password: Selectpriv: Y Insertpriv: Y Updatepriv: Y Deletepriv: Y Createpriv: Y Droppriv: Y Reloadpriv: Y Shutdownpriv: Y Processpriv: Y Filepriv: Y Grantpriv: Y Referencespriv: Y Indexpriv: Y Alterpriv: Y Showdbpriv: Y Superpriv: Y Createtmptablepriv: Y Locktablespriv: Y Executepriv: Y Replslavepriv: Y Replclientpriv: Y Createviewpriv: Y Showviewpriv: Y Createroutinepriv: Y Alterroutinepriv: Y Createuserpriv: Y Eventpriv: Y Triggerpriv: Y Createtablespacepriv: Y ssltype: sslcipher: x509issuer: x509subject: maxquestions: 0 maxupdates: 0 maxconnections: 0 maxuserconnections: 0 plugin: authentication_string:
  • ** 2. row *** Host: localhost User: testuser Password: *574991E975571281C22C99705978B14740B142B7 Selectpriv: N Insertpriv: N Updatepriv: N Deletepriv: N Createpriv: N Droppriv: N Reloadpriv: N Shutdownpriv: N Processpriv: N Filepriv: N Grantpriv: N Referencespriv: N Indexpriv: N Alterpriv: N Showdbpriv: N Superpriv: N Createtmptablepriv: N Locktablespriv: N Executepriv: N Replslavepriv: N Replclientpriv: N Createviewpriv: N Showviewpriv: N Createroutinepriv: N Alterroutinepriv: N Createuserpriv: N Eventpriv: N Triggerpriv: N Createtablespacepriv: N ssltype: sslcipher: x509issuer: x509subject: maxquestions: 0 maxupdates: 0 maxconnections: 0 maxuserconnections: 0 plugin: authenticationstring: NULL 2 rows in set (0.00 sec)

mysql> select * from db\G

  • ** 1. row *** Host: localhost Db: hoge User: testuser Selectpriv: Y Insertpriv: Y Updatepriv: Y Deletepriv: Y Createpriv: Y Droppriv: Y Grantpriv: N Referencespriv: Y Indexpriv: Y Alterpriv: Y Createtmptablepriv: Y Locktablespriv: Y Createviewpriv: Y Showviewpriv: Y Createroutinepriv: Y Alterroutinepriv: Y Executepriv: Y Eventpriv: Y Trigger_priv: Y
  • ** 2. row *** Host: localhost Db: root User: root Selectpriv: Y Insertpriv: Y Updatepriv: Y Deletepriv: Y Createpriv: Y Droppriv: Y Grantpriv: N Referencespriv: Y Indexpriv: Y Alterpriv: Y Createtmptablepriv: Y Locktablespriv: Y Createviewpriv: Y Showviewpriv: Y Createroutinepriv: Y Alterroutinepriv: Y Executepriv: Y Eventpriv: Y Triggerpriv: Y 2 rows in set (0.00 sec)

hogeデータベースにusersテーブルを作った

CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB;

test_userでinsertを実行すると

insertが拒否された。なるほど、 read_onlyが有効になっているから更新系クエリーが実行できないということなんだね。

[vagrant@db ~]$ mysql -utest_user -p -Dhoge Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.5.46 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> insert into users (id, name) values (1, ‘hoge’); ERROR 1290 (HY000): The MySQL server is running with the —read-only option so it cannot execute this statement mysql>

my.cnfから

read_onlyを外したらinsert出来た

設定が外れた瞬間普通に実行できる。

[vagrant@db ~]$ mysql -utest_user -p -Dhoge Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.46 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> insert into users (id, name) values (1, ‘hoge’); Query OK, 1 row affected (0.01 sec)

mysql>

結論

自分の予想は全て外れたw

  • rootアカウントで grant allのアカウント作成を実行すると、一般権限で grant allなアカウントが作成される。
  • 但し read_only設定につき更新系クエリーが実行できない。設定がなければ更新系クエリーが実行できる。

参考


都内で働くWebアプリケーションエンジニア。主にサーバーサイド。最近はRuby/Railsでコードを書くのが楽しい。