Upgrade to Pro — share decks privately, control downloads, hide ads and more …

あなたの知らない
データベースのロギングの世界 / logging queries

あなたの知らない
データベースのロギングの世界 / logging queries

builderscon tokyo 2018 の発表資料です。
https://builderscon.io/tokyo/2018/session/87e13506-2f80-4fae-af9c-2421c7dbb460

※発表後に分かったこと、教えていただいたことにより、発表時の資料から若干の変更を加えています

Okumura Takahiro

September 11, 2018
Tweet

More Decks by Okumura Takahiro

Other Decks in Technology

Transcript

  1. Πϯγσϯτͷ࣌ܥྻ ೔࣌ ಺༰ 2018/01/07 (೔) 20:04 ݕ஌ 2018/01/07 (೔) 22:28

    Ұ࣍ରԠ׬ྃ 2018/01/08 (݄) 01:33 ӨڹൣғҰ෦ൃ֮ 2018/01/10 (ਫ) 13:34 Өڹൣғ௥Ճൃ֮ 2018/01/10 (ਫ) 15:35 ୈࡾऀػؔʹΑΔௐࠪ։࢝ 2018/01/25 (໦) 11:58 ୈࡾऀػؔʹΑΔௐࠪ׬ྃ 2018/01/26 (ۚ) 07:40 ৘ใެ։ Өڹൣғͷ೺Ѳʹ͕͔͔࣌ؒΓɺ৘ใެ։ͷ஗Εʹܨ͕Δ
  2. Ұ൪खܰͳํ๏ɻ%#ɺϢʔβɺϗετɺΫΤϦ ϩά͕547ܗࣜͰه࿥͞Ε͍ͯΔɻ HFOFSBM@MPH 2018-09-07T16:39:50.949045Z 8 Connect root@localhost on using Socket

    2018-09-07T16:39:50.949471Z 8 Query select @@version_comment limit 1 2018-09-07T16:40:13.059736Z 8 Query SELECT * FROM test.t 2018-09-07T16:40:13.762332Z 8 Quit
  3. 1PTUHSF42-Ͱ͸MPH@TUBUFNFOUBMMͱ MPH@MJOF@QSFpYbUVIEJF`ͷΑ ͏ʹ͢Ε͹ه࿥Մೳͱͷ͜ͱ ͪΐͬͱԣಓ1PTUHSF42- 2018-09-10 01:14:05 UTC  postgres  172.20.0.1  testdb 

    idle  00000LOG: statement: SHOW server_version 2018-09-10 01:14:08 UTC  postgres  172.20.0.1  testdb  idle  00000LOG: statement: SELECT * FROM information_schema.tables;
  4. $ bash ./audit-plugin-mysql-5.7-1.1.6-784/utils/ offset-extract.sh /usr/sbin/mysqld //offsets for: /usr/sbin/mysqld (5.7.23) {"5.7.23","f5943a4e0d10ed18befd36bb32aed8bc",

    6992, 7040, 4000, 4520, 72, 2704, 96, 0, 32, 104, 136, 7128, 4392, 2800, 2808, 2812, 536, 0, 0, 6360, 6384, 6368}, NZTRMBVEJUΦϑηοτͷܭࢉ
  5. --- my.cnf +++ /etc/my.cnf @@ -33,3 +33,7 @@ innodb_log_buffer_size=32M innodb_log_file_size=128M

    innodb_autoextend_increment=64 + +plugin-load=AUDIT=libaudit_plugin.so +audit_offsets=6992, 7040, 4000, 4520, 72, 2704, 96, 0, 32, 104, 136, 7128, 4392, 2800, 2808, 2812, 536, 0, 0, 6360, 6384, 6368 +audit_json_file=on NZTRMBVEJUNZDOGͷઃఆ
  6. { "msg-type": "activity", "date": "1501591974035", "thread-id": "1", "query-id": "8508", "user":

    "", "priv_user": "", "ip": "", "host": "", "cmd": "create_db", "query": "CREATE DATABASE IF NOT EXISTS infra" } NZTRMBVEJUϩά΁ͷग़ྗ WBSMJCNZTRMNZTRMBVEJUKTPO Θ͔Γ΍͍͢Α͏੔ܗͯ͠·͢
  7. 20170807 13:56:47,example.com,root,localhost, 151722,0,CONNECT,,,0 20170807 13:56:47,example.com,root,localhost, 151722,73837,QUERY,,'select @@version_comment limit 1',0 20170807

    13:56:47,example.com,root,localhost, 151722,73838,QUERY,,'select @@hostname',0 20170807 13:56:47,example.com,root,localhost, 151722,0,DISCONNECT,,,0 .BSJB%#"VEJU1MVHJOϩά WBSMJCNZTRMTFSWFS@BVEJUMPH
  8. 1SPYZ42-Λհͨ͠ߏ੒ ͱʹ͔͘΍ΔΜͩύλʔϯ server A server B server C ProxySQL ProxySQL

    ProxySQL DB app app app 0.0.0.0:3306 0.0.0.0:3306 0.0.0.0:3306
  9. 1SPYZ42-Λհͨ͠ߏ੒ Consul watch ProxySQL Cluster Client Client Client ProxySQL ProxySQL

    ProxySQL DB db-proxy.service.foo.consul (via Consul DNS Interface) ཧ૝ڷ
  10. w 1SPYZ42-͸όοΫΤϯυͷ%#ʹͭͳ͙ΞΧ΢ϯτ৘ใ Λࣗ෼Ͱอ͍࣋ͯ͠Δ w %#ͷNZTRMVTFSͱ1SPYZ42-ͷNBJONZTRM@VTFST ͕ζϨΔͱ໘౗ Ϣʔβ؅ཧ໰୊ ProxySQL (main.mysql_users) Client

    DB Connect as hello (using *668425423DB5…) *DB5193AF9213… con builders *123456788909… hello *668425423DB5… password username Client Client Connect as builders (using *12345678909…) Connect as con (using *12345678909…) ⭕ ⭕ ❌ *DB5193AF9213… con bbuilders *8425423DB519… hello *668425423DB5… password username ⭕ ❌
  11. mysql> SHOW CREATE TABLE t\G ******************** 1. row ******************** Table:

    t Create Table: CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci -"45@*/4&35@*%
  12. mysql> INSERT INTO t VALUES (NULL, 'hfm'); mysql> SELECT *

    FROM t; +----+------+ | id | name | +----+------+ | 1 | hfm | +----+------+ mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ -"45@*/4&35@*%
  13. mysql> INSERT INTO t (name) VALUES ('builders'), ('con'), ('yeah!!'); mysql>

    SELECT * FROM t; +----+----------+ | id | name | +----+----------+ | 1 | hfm | | 2 | builders | | 3 | con | | 4 | yeah!! | +----+----------+ mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 2 | +------------------+ -"45@*/4&35@*%
  14. 1SPYZ42-.VMUJQMFYJOH ProxySQL Client Client Client INSERT INSERT INSERT SELECT LAST_INSERT_ID()

    ProxySQL DB INSERT INSERT INSERT SELECT LAST_INSERT_ID() ͜ͷ*/4&35 Ͳͷ$MJFOUग़਎
  15. CREATE TABLE a ( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

    name VARCHAR(10), updated BOOLEAN DEFAULT false, PRIMARY KEY (id) ); NZTRMTMBQ INSERT INTO mysqlslap.a (name) VALUES ('hello'); SELECT * FROM mysqlslap.a; ... SELECT * FROM mysqlslap.a; UPDATE a SET updated = true WHERE id = (SELECT LAST_INSERT_ID()); create.sql query.sql
  16. mysqlslap \ --host=proxysql_url \ --user=user \ --password=******** \ --delimiter=';' \

    --create=create.sql \ --query=query.sql \ --concurrency=100 \ --iteration=1 \ --no-drop NZTRMTMBQͷίϚϯυྫ