hironomiu's Blog

こそっと書いていきます。twitter_id：hironomiu

mysql バージョン5.6で使えるoptimizer_trace

MySQL5.6で実装されたoptimizer_traceを触ってみた

設定できるパラメータ

enabled 初期値：off
end_marker 初期値：off
one_line 初期値：off

設定

mysql> show variables like 'opttimizer_trace';
Empty set (0.00 sec)

mysql> show variables like 'optimizer_trace';
 +-----------------+-----------------------------------------+
 | Variable_name   | Value                                   |
 +-----------------+-----------------------------------------+
 | optimizer_trace | enabled=off,end_marker=off,one_line=off |
 +-----------------+-----------------------------------------+
1 row in set (0.00 sec)

mysql> set optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'optimizer_trace';
 +-----------------+----------------------------------------+
 | Variable_name   | Value                                  |
 +-----------------+----------------------------------------+
 | optimizer_trace | enabled=on,end_marker=off,one_line=off |
 +-----------------+----------------------------------------+
1 row in set (0.00 sec)

SQL実行、information_schema.optimizer_trace参照

mysql> select * from hoge;
Empty set (0.00 sec)

mysql> select * from information_schema.OPTIMIZER_TRACE;
 +--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
| QUERY              | TRACE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | MISSING_BYTES_BEYOND_MAX_MEM_SIZE | INSUFFICIENT_PRIVILEGES |
 +--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
| select * from hoge | {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `hogedb`.`hoge`.`id` AS `id` from `hogedb`.`hoge`"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "database": "hogedb",
                "table": "hoge",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "database": "hogedb",
                "table": "hoge",
                "table_scan": {
                  "rows": 1,
                  "cost": 1
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "database": "hogedb",
                "table": "hoge",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "rows": 1,
                      "cost": 1,
                      "chosen": true
                    }
                  ]
                },
                "cost_for_plan": 1.2,
                "rows_for_plan": 1,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "database": "hogedb",
                  "table": "hoge",
                  "attached": null
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "database": "hogedb",
                "table": "hoge",
                "access_type": "table_scan"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
} |                                 0 |                       0 |
 +--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
1 row in set (0.00 sec)

推奨ではないけどこういう設定方法も出来る

mysql> show variables like 'optimizer_trace';
 +-----------------+-----------------------------------------+
 | Variable_name   | Value                                   |
 +-----------------+-----------------------------------------+
 | optimizer_trace | enabled=off,end_marker=off,one_line=off |
 +-----------------+-----------------------------------------+
1 row in set (0.00 sec)

mysql> set optimizer_trace=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'optimizer_trace';
 +-----------------+----------------------------------------+
 | Variable_name   | Value                                  |
 +-----------------+----------------------------------------+
 | optimizer_trace | enabled=on,end_marker=off,one_line=off |
 +-----------------+----------------------------------------+
1 row in set (0.00 sec)

epel周りの話

備忘録として
epelを落とそうとしてもなぜか失敗する。

wget http://download.fedora.redhat.com/pub/epel/5/x86_64/epel-release-5-4.noarch.rpm
--2012-03-25 01:12:34--  http://download.fedora.redhat.com/pub/epel/5/x86_64/epel-release-5-4.noarch.rpm
download.fedora.redhat.com をDNSに問いあわせています... 失敗しました: 名前またはサービスが不明です.
wget: ホストアドレス `download.fedora.redhat.com' を解決できませんでした。

https://fedoraproject.org/wiki/FWN/Issue127

https://fedoraproject.org/wiki/Infrastructure/Mirroring

どうも変わったらしい。

wget http://dl.fedoraproject.org/pub/epel/5/x86_64/epel-release-5-4.noarch.rpm
--2012-03-25 01:14:14--  http://dl.fedoraproject.org/pub/epel/5/x86_64/epel-release-5-4.noarch.rpm
dl.fedoraproject.org をDNSに問いあわせています... 209.132.181.23, 209.132.181.24, 209.132.181.25, ...
dl.fedoraproject.org|209.132.181.23|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 12232 (12K) [application/x-rpm]
`epel-release-5-4.noarch.rpm' に保存中

100%[========================================================================================================================================================================================>] 12,232      42.9K/s 時間 0.3s    

2012-03-25 01:14:15 (42.9 KB/s) - `epel-release-5-4.noarch.rpm' へ保存完了 [12232/12232]

mysql レプリケーション周り

mysql レプリケーション周り

とある人にcreate temporary tableでテーブルを作成した場合、slaveにレプリケーションってされるの？と素朴な質問をされ「？」って思ったのでちょっと調べてみた。途中からはtemporary tableとかあんま関係なくなってきてるけど気にしない。

master側で以下のDDL、DMLを実行した

slave側のignore-do-db設定でtest2を許可している状態からaaa、bbb（temporary table）を作成しデータをINSERTした。（autocommit=on）

mysql> use test2;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table aaa(id int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into aaa values (10);
Query OK, 1 row affected (0.01 sec)

mysql> create temporary table bbb (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bbb values (50);
Query OK, 1 row affected (0.01 sec)

mysql> insert into aaa values (100);
Query OK, 1 row affected (0.00 sec)

mysql> select * from aaa;
 +------+
 | id   |
 +------+
 |   10 |
 |  100 |
 +------+
2 rows in set (0.00 sec)

mysql> select * from bbb;
 +------+
 | id   |
 +------+
 |   50 |
 +------+
1 row in set (0.00 sec)

上のオペレーションが終わったところでslaveを確認すると

まぁ当然bbb（temporary table）は存在してない。

mysql> show tables;
 +-----------------+
 | Tables_in_test2 |
 +-----------------+
 | aaa             |
 +-----------------+
1 row in set (0.00 sec)

mysql> select * from aaa;
 +------+
 | id   |
 +------+
 |   10 |
 |  100 |
 +------+
2 rows in set (0.00 sec)

slaveのrelayログを確認

mysqlbinlogで整形して中身を見ると、bbbのSQLは伝播していた（49行目、59行目）。master側で出力されたbinaryログをioスレッドはちゃんとピックアップするけど、込み入った設定をしない場合、単純にsqlスレッドが実行しないことでレプリケーションされていな模様。

      1 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
      2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
      3 DELIMITER /*!*/;
      4 # at 4
      5 #120316  6:58:38 server id 5112  end_log_pos 114        Start: binlog v 4, server v 5.6.4-m7-log created 120316  6:58:38
      6 BINLOG '
      7 DmZiTw/4EwAAbgAAAHIAAABAAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
      8 AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAAF85430=
      9 '/*!*/;
     10 # at 114
     11 #700101  9:00:00 server id 1  end_log_pos 0     Rotate to mysql-bin.000014  pos: 4
     12 # at 157
     13 #120316  6:58:24 server id 1  end_log_pos 114   Start: binlog v 4, server v 5.6.4-m7-log created 120316  6:58:24 at startup
     14 ROLLBACK/*!*/;
     15 BINLOG '
     16 AGZiTw8BAAAAbgAAAHIAAAAAAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
     17 AAAAAAAAAAAAAAAAAAAAZmJPEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAAO/C2kU=
     18 '/*!*/;
     19 # at 267
     20 #120316  6:59:26 server id 1  end_log_pos 210   Query   thread_id=2     exec_time=0     error_code=0
     21 use test2/*!*/;
     22 SET TIMESTAMP=1331848766/*!*/;
     23 SET @@session.pseudo_thread_id=2/*!*/;
     24 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
     25 SET @@session.sql_mode=0/*!*/;
     26 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
     27 /*!\C utf8 *//*!*/;
     28 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
     29 SET @@session.lc_time_names=0/*!*/;
     30 SET @@session.collation_database=DEFAULT/*!*/;
     31 create table aaa(id int)
     32 /*!*/;
     33 # at 363
     34 #120316  6:59:56 server id 1  end_log_pos 287   Query   thread_id=2     exec_time=0     error_code=0
     35 SET TIMESTAMP=1331848796/*!*/;
     36 BEGIN
     37 /*!*/;
     38 # at 440
     39 #120316  6:59:56 server id 1  end_log_pos 386   Query   thread_id=2     exec_time=0     error_code=0
     40 SET TIMESTAMP=1331848796/*!*/;
     41 insert into aaa values (10)
     42 /*!*/;
     43 # at 539
     44 #120316  6:59:56 server id 1  end_log_pos 413   Xid = 20
     45 COMMIT/*!*/;
     46 # at 566
     47 #120316  7:00:18 server id 1  end_log_pos 520   Query   thread_id=2     exec_time=0     error_code=0
     48 SET TIMESTAMP=1331848818/*!*/;
     49 create temporary table bbb (id int)
     50 /*!*/;
     51 # at 673
     52 #120316  7:00:32 server id 1  end_log_pos 597   Query   thread_id=2     exec_time=0     error_code=0
     53 SET TIMESTAMP=1331848832/*!*/;
     54 BEGIN
     55 /*!*/;
     56 # at 750
     57 #120316  7:00:32 server id 1  end_log_pos 696   Query   thread_id=2     exec_time=0     error_code=0
     58 SET TIMESTAMP=1331848832/*!*/;
     59 insert into bbb values (50)
     60 /*!*/;
     61 # at 849
     62 #120316  7:00:32 server id 1  end_log_pos 774   Query   thread_id=2     exec_time=0     error_code=0
     63 SET TIMESTAMP=1331848832/*!*/;
     64 COMMIT
     65 /*!*/;
     66 # at 927
     67 #120316  7:00:43 server id 1  end_log_pos 851   Query   thread_id=2     exec_time=0     error_code=0
     68 SET TIMESTAMP=1331848843/*!*/;
     69 BEGIN
     70 /*!*/;
     71 # at 1004
     72 #120316  7:00:43 server id 1  end_log_pos 951   Query   thread_id=2     exec_time=0     error_code=0
     73 SET TIMESTAMP=1331848843/*!*/;
     74 insert into aaa values (100)
     75 /*!*/;
     76 # at 1104
     77 #120316  7:00:43 server id 1  end_log_pos 978   Xid = 25
     78 COMMIT/*!*/;
     79 DELIMITER ;
     80 # End of log file
     81 ROLLBACK /* added by mysqlbinlog */;
     82 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

binlog-ignore

そうは言っても不要なDDLやDMLでネットワーク転送が発生するのって何か嫌だなぁって思って調べて見ると、DB単位しか無理だけどbinlog-ignoreでmaster側でbinlogそのものを吐かない方法がある。

mysql> create table ccc (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into ccc values (111);
Query OK, 1 row affected (0.00 sec)

mysql> select * from ccc;
 +------+
 | id   |
 +------+
 |  111 |
 +------+
1 row in set (0.00 sec)

binlogにも上のDDL、DMLは出力されていないし、当然slaveのrelayログにも無いし、test2上にテーブルもデータもない。

replicate-ignore-table

slave側でreplicate-ignore-tableでtest2.cccを設定し再起動して下記SQLをmaster側で実施する。

mysql> create table ddd (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into ddd values(100);
Query OK, 1 row affected (0.00 sec)

mysql> create table ccc (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into ccc values ( 200);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ddd values (500);
Query OK, 1 row affected (0.00 sec)

mysql> show tables;
 +-----------------+
 | Tables_in_test2 |
 +-----------------+
 | aaa             |
 | ccc             |
 | ddd             |
 +-----------------+
3 rows in set (0.00 sec)

mysql> select * from ddd;
 +------+
 | id   |
 +------+
 |  100 |
 |  500 |
 +------+
2 rows in set (0.00 sec)

mysql> select * from ccc;
 +------+
 | id   |
 +------+
 |  200 |
 +------+
1 row in set (0.00 sec)

上を実施したところでslaveも確認

mysql> show tables;
 +-----------------+
 | Tables_in_test2 |
 +-----------------+
 | aaa             |
 | ddd             |
 +-----------------+
2 rows in set (0.00 sec)

mysql> select * from ddd;
 +------+
 | id   |
 +------+
 |  100 |
 |  500 |
 +------+
2 rows in set (0.00 sec)

ちゃんとcccがignoreされている。肝心のslaveのrelayログの中身はというと？

      1 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
      2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
      3 DELIMITER /*!*/;
      4 # at 4
      5 #120316  7:32:14 server id 5112  end_log_pos 114        Start: binlog v 4, server v 5.6.4-m7-log created 120316  7:32:14
      6 BINLOG '
      7 7m1iTw/4EwAAbgAAAHIAAABAAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
      8 AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAACed1Yg=
      9 '/*!*/;
     10 # at 114
     11 #700101  9:00:00 server id 1  end_log_pos 0     Rotate to mysql-bin.000016  pos: 4
     12 # at 157
     13 #120316  7:31:31 server id 1  end_log_pos 114   Start: binlog v 4, server v 5.6.4-m7-log created 120316  7:31:31 at startup
     14 ROLLBACK/*!*/;
     15 BINLOG '
     16 w21iTw8BAAAAbgAAAHIAAAAAAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
     17 AAAAAAAAAAAAAAAAAADDbWJPEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAAMqY3wM=
     18 '/*!*/;
     19 # at 267
     20 #120316  7:32:51 server id 1  end_log_pos 211   Query   thread_id=2     exec_time=0     error_code=0
     21 use test2/*!*/;
     22 SET TIMESTAMP=1331850771/*!*/;
     23 SET @@session.pseudo_thread_id=2/*!*/;
     24 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
     25 SET @@session.sql_mode=0/*!*/;
     26 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
     27 /*!\C utf8 *//*!*/;
     28 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
     29 SET @@session.lc_time_names=0/*!*/;
     30 SET @@session.collation_database=DEFAULT/*!*/;
     31 create table ddd (id int)
     32 /*!*/;
     33 # at 364
     34 #120316  7:33:08 server id 1  end_log_pos 288   Query   thread_id=2     exec_time=0     error_code=0
     35 SET TIMESTAMP=1331850788/*!*/;
     36 BEGIN
     37 /*!*/;
     38 # at 441
     39 #120316  7:33:08 server id 1  end_log_pos 387   Query   thread_id=2     exec_time=0     error_code=0
     40 SET TIMESTAMP=1331850788/*!*/;
     41 insert into ddd values(100)
     42 /*!*/;
     43 # at 540
     44 #120316  7:33:08 server id 1  end_log_pos 414   Xid = 17
     45 COMMIT/*!*/;
     46 # at 567
     47 #120316  7:33:15 server id 1  end_log_pos 511   Query   thread_id=2     exec_time=0     error_code=0
     48 SET TIMESTAMP=1331850795/*!*/;
     49 create table ccc (id int)
     50 /*!*/;
     51 # at 664
     52 #120316  7:33:27 server id 1  end_log_pos 588   Query   thread_id=2     exec_time=0     error_code=0
     53 SET TIMESTAMP=1331850807/*!*/;
     54 BEGIN
     55 /*!*/;
     56 # at 741
     57 #120316  7:33:27 server id 1  end_log_pos 689   Query   thread_id=2     exec_time=0     error_code=0
     58 SET TIMESTAMP=1331850807/*!*/;
     59 insert into ccc values ( 200)
     60 /*!*/;
     61 # at 842
     62 #120316  7:33:27 server id 1  end_log_pos 716   Xid = 19
     63 COMMIT/*!*/;
     64 # at 869
     65 #120316  7:33:35 server id 1  end_log_pos 793   Query   thread_id=2     exec_time=0     error_code=0
     66 SET TIMESTAMP=1331850815/*!*/;
     67 BEGIN
     68 /*!*/;
     69 # at 946
     70 #120316  7:33:35 server id 1  end_log_pos 893   Query   thread_id=2     exec_time=0     error_code=0
     71 SET TIMESTAMP=1331850815/*!*/;
     72 insert into ddd values (500)
     73 /*!*/;
     74 # at 1046
     75 #120316  7:33:35 server id 1  end_log_pos 920   Xid = 20
     76 COMMIT/*!*/;
     77 DELIMITER ;
     78 # End of log file
     79 ROLLBACK /* added by mysqlbinlog */;
     80 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

cccのSQLはやはり伝播してしまうらしい。（49行目、59行目）、binaryログの中身をある条件（オブジェクト、SQL、権限）だけ転送しないって処理は考えると重いので妥当と言えば妥当な気がしてきた。

mysql viewを使ってみる

mysql viewを使ってみる

今回は特定ユーザにviewのみアクセスさせtableにはアクセスできない状態にしたかったが、いざやってみると結構嵌ったので備忘録として記載しとく。
特に記載が無い場合はrootでDDLは実行しています。

  • 検証用のDBを作成
mysql> create database hogedb;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | hogedb             |
 | mysql              |
 | performance_schema |
 +--------------------+
4 rows in set (0.00 sec)
  • ユーザの作成

tableに対してもownerとしてtablehogeユーザをviewだけ参照できるユーザとしてviewhogeユーザを作成。
（viewhogeには権限は未設定。）

create database hogedb;
GRANT USAGE ON *.* TO 'tablehoge'@'localhost' IDENTIFIED BY 'tablehoge';
GRANT ALL PRIVILEGES ON `hogedb`.* TO 'tablehoge'@'localhost';
GRANT USAGE ON *.* TO 'viewhoge'@'localhost' IDENTIFIED BY 'viewhoge';

mysql> create database hogedb;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT USAGE ON *.* TO 'tablehoge'@'localhost' IDENTIFIED BY 'tablehoge';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `hogedb`.* TO 'tablehoge'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT USAGE ON *.* TO 'viewhoge'@'localhost' IDENTIFIED BY 'viewhoge';
Query OK, 0 rows affected (0.00 sec)
  • tableの作成

hogedbに作成。

create table hogetb(
 id int NOT NULL AUTO_INCREMENT,
 col1 int,
 col2 int,
 PRIMARY KEY (`ID`)
) engine = innodb
;

mysql> use hogedb
Database changed
mysql> create table hogetb(
    ->  id int NOT NULL AUTO_INCREMENT,
    ->  col1 int,
    ->  col2 int,
    ->  PRIMARY KEY (`ID`)
    -> ) engine = innodb
    -> ;
Query OK, 0 rows affected (0.01 sec)
  • viewの作成

DEFINERには`tablehoge`@`localhost`を指定する。

CREATE ALGORITHM=UNDEFINED DEFINER=`tablehoge`@`localhost` SQL SECURITY DEFINER VIEW hogeview AS select id ,col2 from hogetb;

mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`tablehoge`@`localhost` SQL SECURITY DEFINER VIEW hogeview AS select id ,col2 from hogetb;
Query OK, 0 rows affected (0.00 sec)
  • viewhogeにも参照権限を付与する。
GRANT SELECT ON `hogedb`.`hogeview` TO 'viewhoge'@'localhost';

mysql> GRANT SELECT ON `hogedb`.`hogeview` TO 'viewhoge'@'localhost';
Query OK, 0 rows affected (0.00 sec)
  • tablehogeでの確認

tablehogeでログインしtable、viewの両方アクセスできることを確認する。

[root@localhost ~]# mysql -u tablehoge -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 76
Server version: 5.6.4-m7-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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>
mysql>
mysql> use hogedb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
 +------------------+
 | Tables_in_hogedb |
 +------------------+
 | hogetb           |
 | hogeview         |
 +------------------+
2 rows in set (0.00 sec)

mysql> select * from hogetb;
Empty set (0.00 sec)

mysql> select * from hogeview;
Empty set (0.00 sec)
  • viewhogeでの確認

hogeviewだけ見えれば今回のミッション達成。

[root@localhost ~]# mysql -u viewhoge -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 78
Server version: 5.6.4-m7-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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> use hogedb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
 +------------------+
 | Tables_in_hogedb |
 +------------------+
 | hogeview         |
 +------------------+
1 row in set (0.01 sec)

mysql> select * from hogeview;
Empty set (0.00 sec)

無事viewだけ見せれた。（DEFINERとSQL SECURITY周りはちょっと納得してないけど。）
レプリケーション時はviewhogeはmaster,slaveの両方に存在し参照したいviewについては同じ権限があればOKなはず。

mysql log_errorメッセージでちょっと気になったこと

mysql5.6を評価していてエラーログを見ていたときに、あれっと思ったことが。

120210 9:16:59 [Note] /usr/sbin/mysqld: Shutdown complete

120210 09:16:59 mysqld_safe mysqld from pid file /var/lib/mysql/localhost.localdomain.pid ended
120210 09:16:59 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
120210 9:16:59 [Note] Plugin 'FEDERATED' is disabled.
120210 9:16:59 InnoDB: The InnoDB memory heap is disabled
120210 9:16:59 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120210 9:16:59 InnoDB: Compressed tables use zlib 1.2.3
120210 9:16:59 InnoDB: Using Linux native AIO
120210 9:16:59 InnoDB: CPU does not support crc32 instructions
120210 9:16:59 InnoDB: Initializing buffer pool, size = 512.0M
120210 9:16:59 InnoDB: Completed initialization of buffer pool
120210 9:17:00 InnoDB: highest supported file format is Barracuda.
120210 9:17:00 InnoDB: 128 rollback segment(s) are active.
120210 9:17:00 InnoDB: Waiting for the background threads to start
120210 9:17:01 InnoDB: 1.2.4 started; log sequence number 1678378
120210 9:17:01 [Note] Event Scheduler: Loaded 0 events
120210 9:17:01 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.4-m7-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

このmysqld_safeの停止、起動のメッセージ部分だけ09:16:59と0パディングされている。

120210 09:16:59 mysqld_safe mysqld from pid file /var/lib/mysql/localhost.localdomain.pid ended
120210 09:16:59 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

5.5の環境を覗いてgrepしてみたらやっぱり0パディングされていた。

どうでも良いんだろうけど凄く気になる。

mysql error logの肥大化

とあるサービスのエンジニアから開発機のDisk使用量のアラートが上がってると問い合わせがきたので

開発機のDiskの状況を調べてたらこんな状況だった。

[xxxxxxxxx]# df

Filesystem 1K-ブロック 使用 使用可 使用% マウント位置
/dev/sda2 5820012 1139920 4379680 21% /
/dev/sda5 125199736 108222064 10515128 92% /var
/dev/sda1 101086 18745 77122 20% /boot
tmpfs 2024808 0 2024808 0% /dev/shm

/varが確かに92％を超えていた。
どこがそんなに食ってるんだろうと調べると、とあるmysqlインスタンスが75Gも使っていたのが判明。
確認するとerror logがすごいことに。

  • rw-rw---- 1 mysql root 59928108333 12月 22 13:58 xxxxxxxx.err

中身をtailで見ると

InnoDB: but the tablespace does not exist or is just being dropped.
111222 13:59:52 InnoDB: Error: trying to access tablespace 11113 page no. 3,
InnoDB: but the tablespace does not exist or is just being dropped.
111222 13:59:52 InnoDB: Error: trying to access tablespace 11097 page no. 3,
InnoDB: but the tablespace does not exist or is just being dropped.
111222 13:59:52 InnoDB: Error: trying to access tablespace 11113 page no. 3,
InnoDB: but the tablespace does not exist or is just being dropped.
111222 13:59:52 InnoDB: Error: trying to access tablespace 11097 page no. 3,
InnoDB: but the tablespace does not exist or is just being dropped.

ひたすらerrorを吐き続けていた。
通常のrestartが出来ないのでkill -9で該当プロセスをkill

[xxxxxxxxx]# /etc/init.d/mysql_xxxxx restart
Shutting down MySQL...............................................................................................................................................

★ここで別ターミナルでkill

SUCCESS!
Starting MySQL..... SUCCESS!

改めてstopし調査用に退避するのも何なのでエイっとerror logは削除
起動しerror logの中身を見ると

[xxxxxxxxx]# cat mysql51-dev1.ycc.ecnavi.info.err
111222 14:06:17 mysqld_safe A mysqld process already exists

ちょっと気持ち悪いのでもう一回restartを。

111222 14:28:34 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 246733814, file name ./mysql51-dev1-bin.000055
111222 14:28:34 InnoDB Plugin 1.0.6 started; log sequence number 56840114350
111222 14:28:34 [Note] Recovering after a crash using mysql51-dev1-bin
111222 14:28:34 [Note] Starting crash recovery...
111222 14:28:34 [Note] Crash recovery finished.
111222 14:28:34 [Note] Event Scheduler: Loaded 0 events
111222 14:28:34 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.42-community-log' socket: '/var/lib/mysql_au_shopping/mysql.sock' port: 3313 MySQL Community Server (GPL)


ちゃんとクラッシュリカバリもしてくれて無事起動しerror logの肥大化も収まってくれた。

このバグの可能性が高い感じ

http://bugs.mysql.com/bug.php?id=38901

AWS　RDS　init_connectを設定した際の不具合

AWSのRDSでinit_connectにtimezone設定をしているサービスでの出来事

AWS側のメンテナンスでRDSの再起動が掛かった後、下のように接続が拒否された。

[root@XXXXX ~]# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 665

Server version: 5.5.12-log

 

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

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

 

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

 

mysql> show databases;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    666

Current database: *** NONE ***

 

ERROR 2013 (HY000): Lost connection to MySQL server during query

 

サイトエンジニアの調査で、これが原因だったらしい。

https://forums.aws.amazon.com/thread.jspa?messageID=239869