Share
Embed
あなたの知らない データベースのロギングの世界 / logging queries
builderscon tokyo 2018 の発表資料です。
https://builderscon.io/tokyo/2018/session/87e13506-2f80-4fae-af9c-2421c7dbb460
※発表後に分かったこと、教えていただいたことにより、発表時の資料から若干の変更を加えています
Okumura Takahiro
September 11, 2018
Transcript
69 slides
-
Πϯγσϯτͷ࣌ܥྻ ࣌ ༰ 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 ใެ։ ӨڹൣғͷѲʹ͕͔͔࣌ؒΓɺใެ։ͷΕʹܨ͕Δ -
Ұ൪खܰͳํ๏ɻ%#ɺϢʔβɺϗετɺΫΤϦ ϩά͕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 -
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; -
curl -L 'https://bintray.com/mcafee/mysql-audit- plugin/download_file?file_path=audit-plugin- mysql-5.7-1.1.6-784-linux-x86_64.zip' -o audit- plugin-mysql.zip unzip audit-plugin-mysql.zip
cp audit-plugin-mysql-5.7-1.1.6-784/lib/ libaudit_plugin.so /usr/lib/mysql/plugin/ NZTRMBVEJU४උ -
$ 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Φϑηοτͷܭࢉ -
--- 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ͷઃఆ -
{ "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 Θ͔Γ͍͢Α͏ܗͯ͠·͢ -
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 -
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 -
1SPYZ42-Λհͨ͠ߏ Consul watch ProxySQL Cluster Client Client Client ProxySQL ProxySQL
ProxySQL DB db-proxy.service.foo.consul (via Consul DNS Interface) ཧڷ -
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 ⭕ ❌ -
1SPYZ42-ඪ४Ͱ௨৴ͷଟॏԽʢෳΫϥΠΞϯτ͔Β ͷଓΛ·ͱΊΔʣػೳΛఏڙ͍ͯ͠Δ 1SPYZ42-.VMUJQMFYJOH ProxySQL Application A DB Client Client Client
Client multiplexing client backend proxy -
-"45@*/4&35@*%ؔͷΑ͏ͳʮηογϣϯதͷ࠷ۙͷ */4&35݁ՌʯΛݟΔΑ͏ͳͷͱ૬ੑ͕ѱ͍ 1SPYZ42-.VMUJQMFYJOH ProxySQL Application A DB Client Client Client
Client multiplexing client backend proxy -
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@*% -
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@*% -
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@*% -
ੜ͞Εͨ*%ɺଓ͝ͱʹαʔόʔʹอ࣋͞Ε·͢ɻ ͭ·ΓɺؔʹΑͬͯࢦఆ͞ΕͨΫϥΠΞϯτʹฦ͞Εͨ ɺͦͷΫϥΠΞϯτʹΑͬͯ"650@*/$3&.&/5Χϥ ϜʹӨڹΛ༩͑Δ࠷ۙͷεςʔτϝϯτ༻ʹ࠷ॳʹੜ͞Ε ͨ"650@*/$3&.&/5Ͱ͢ɻ͜ͷɺ΄͔ͷΫϥΠ Ξϯτ͕ಠࣗͷ"650@*/$3&.&/5Λੜͨ͠߹Ͱ ӨڹΛड͚ΔՄೳੑ͋Γ·ͤΜɻ͜ͷಈ࡞ʹΑͬͯɺ֤ ΫϥΠΞϯτ΄͔ͷΫϥΠΞϯτͷΞΫςΟϏςΟʔΛؾ ʹ͢Δ͜ͱͳ͘ɺϩοΫτϥϯβΫγϣϯΛ࣮ߦ͠ͳ͍Ͱ ಠࣗͷ*%ΛऔಘͰ͖·͢ɻ
-"45@*/4&35@*% IUUQTEFWNZTRMDPNEPDSFGNBOKBJOGPSNBUJPOGVODUJPOTIUNMGVODUJPO@MBTUJOTFSUJE -
ੜ͞Εͨ*%ɺଓ͝ͱʹαʔόʔʹอ࣋͞Ε·͢ɻ ͭ·ΓɺؔʹΑͬͯࢦఆ͞ΕͨΫϥΠΞϯτʹฦ͞Εͨ ɺͦͷΫϥΠΞϯτʹΑͬͯ"650@*/$3&.&/5Χϥ ϜʹӨڹΛ༩͑Δ࠷ۙͷεςʔτϝϯτ༻ʹ࠷ॳʹੜ͞Ε ͨ"650@*/$3&.&/5Ͱ͢ɻ͜ͷɺ΄͔ͷΫϥΠ Ξϯτ͕ಠࣗͷ"650@*/$3&.&/5Λੜͨ͠߹Ͱ ӨڹΛड͚ΔՄೳੑ͋Γ·ͤΜɻ͜ͷಈ࡞ʹΑͬͯɺ֤ ΫϥΠΞϯτ΄͔ͷΫϥΠΞϯτͷΞΫςΟϏςΟʔΛؾ ʹ͢Δ͜ͱͳ͘ɺϩοΫτϥϯβΫγϣϯΛ࣮ߦ͠ͳ͍Ͱ ಠࣗͷ*%ΛऔಘͰ͖·͢ɻ
-"45@*/4&35@*% IUUQTEFWNZTRMDPNEPDSFGNBOKBJOGPSNBUJPOGVODUJPOTIUNMGVODUJPO@MBTUJOTFSUJE -
1SPYZ42-.VMUJQMFYJOH ProxySQL Client Client Client INSERT INSERT INSERT SELECT LAST_INSERT_ID()
ProxySQL DB INSERT INSERT INSERT SELECT LAST_INSERT_ID() -
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ग़ -
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 -
mysqlslap \ --host=proxysql_url \ --user=user \ --password=******** \ --delimiter=';' \
--create=create.sql \ --query=query.sql \ --concurrency=100 \ --iteration=1 \ --no-drop NZTRMTMBQͷίϚϯυྫ