そろばんのしょ(第3版第2刷)

雑魚い見習いDBAの日々学んだことや、どーでもいいことを。基本は自分の備忘録。

Oracle11gでのLIMIT,OFFSETするクエリをMySQL8のwindow関数で利用してみる

諸事情でWindow関数を触る機会があって、ちょっと前にyokuさんに教えてもらったWindow関数ネタを思い出したので1つ。

Oracle11gにはMySQLでおなじみ?のLIMIT,OFFSETがない。

そこで(いろいろやり方はあるけど)window関数を使ってあらわしたりする。

例えば id(number), value(varchar2) のテーブルならこんな感じ。

SELECT * FROM 
  (
     SELECT row_number() over order by id asc) as row_number,
                   value 
     FROM t1
  ) a
WHERE a.row_number between 500 and 600;

MySQL8からwindow関数が追加され、row_number()とかもつかえるのでこのクエリをそのままMySQLで使えるか検討してみる。 環境はMySQL8.0.13。データは前回のブログで使った100万行くらい入ってるシンプルなテーブルのやつ

mysql  [d1]> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `value` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql  [d1]> SELECT COUNT(1) FROM t1;
+----------+
| COUNT(1) |
+----------+
|  1011000 |
+----------+
1 row in set (0.15 sec)
mysql  [d1]> SELECT * FROM
    ->   (
    ->      SELECT row_number() over (order by id) as row_no,
    ->                    value
    ->      FROM t1
    ->      )  a
    -> WHERE a.row_no between 500 and 600;
+--------+------------+
| row_no | value      |
+--------+------------+
|    500 | 1zwujbeNGW |
|    501 | qz8df1RHcc |
|    502 | XKH92CeXGa |
|    503 | zWc7JQeOH0 |
|    504 | AkwB5KClKD |
|    505 | rP1ZUAOc80 |
~~

|    595 | oDdB7ZR1jD |
|    596 | kqUuhKOELK |
|    597 | o4mAu0y2KG |
|    598 | 5e0EbD9OXW |
|    599 | yUyRUrOy9d |
|    600 | 5J4wVaIKOJ |
+--------+------------+
101 rows in set (0.91 sec)

お、ちゃんと出力された。SQL的には(当たり前だけど)問題ないっぽい。 これでOracleのテーブルをMySQLに移行するときのSQLの書き換える量がへりますね。(めでたしめでたし。

ただ、0.91 secはやっぱ遅い気がする。

mysql  [d1]> SELECT id, value FROM t1 ORDER BY id LIMIT 101 OFFSET 499;
+-----+------------+
| id  | value      |
+-----+------------+
| 500 | 1zwujbeNGW |
| 501 | qz8df1RHcc |
| 502 | XKH92CeXGa |
| 503 | zWc7JQeOH0 |
| 504 | AkwB5KClKD |
| 505 | rP1ZUAOc80 |
| 506 | WJSjD0NWJb |
| 507 | 3j6lvvGYvH |
~~
| 598 | 5e0EbD9OXW |
| 599 | yUyRUrOy9d |
| 600 | 5J4wVaIKOJ |
+-----+------------+
101 rows in set (0.00 sec)

。。。ですよね〜。

ということでOFFSETを深くしてprofilingをとってみる。

mysql  [d1]> SELECT * FROM
    ->     (
    ->       SELECT row_number() over (order by id) as row_no,
    ->                    value
    ->       FROM t1
    ->     )  a
    ->  WHERE a.row_no between 1000000 and 1000010;
+---------+------------+
| row_no  | value      |
+---------+------------+
| 1000000 | 5UGdDjX6sI |
| 1000001 | 1BmxMSJMNO |
| 1000002 | fRA4G96KoZ |
| 1000003 | 0PEhFBERn3 |
| 1000004 | x9N5k3eez5 |
| 1000005 | KeIyt1RHX5 |
| 1000006 | BaDGNWrvPj |
| 1000007 | hnpbYAQtKW |
| 1000008 | XI1a2RAy0G |
| 1000009 | ZmnI8XobRb |
| 1000010 | N6XXZnw3mo |
+---------+------------+
11 rows in set (1.01 sec)

mysql  [d1]> show profile;
+----------------------------+----------+
| Status                     | Duration |
+----------------------------+----------+
| starting                   | 0.000550 |
| checking permissions       | 0.000024 |
| Opening tables             | 0.000390 |
| init                       | 0.000034 |
| System lock                | 0.000066 |
| optimizing                 | 0.000010 |
| optimizing                 | 0.000007 |
| statistics                 | 0.000054 |
| preparing                  | 0.000033 |
| Creating tmp table         | 0.000058 |
| statistics                 | 0.000023 |
| preparing                  | 0.000015 |
| executing                  | 0.000032 |
| Sending data               | 0.000015 |
| executing                  | 0.000004 |
| Sending data               | 0.000008 |
| Creating sort index        | 0.973423 |
| end                        | 0.000040 |
| query end                  | 0.000014 |
| waiting for handler commit | 0.000030 |
| query end                  | 0.000017 |
| removing tmp table         | 0.028293 |
| query end                  | 0.004465 |
| removing tmp table         | 0.000023 |
| query end                  | 0.000015 |
| closing tables             | 0.000041 |
| freeing items              | 0.000094 |
| cleaning up                | 0.000094 |
+----------------------------+----------+
28 rows in set, 1 warning (0.00 sec)

Warning (Code 1287): 'SHOW PROFILE' is deprecated and will be removed in a future release. Please use Performance Schema instead
mysql  [d1]> SELECT id, value FROM t1 ORDER BY id LIMIT 11 OFFSET 999999;
+---------+------------+
| id      | value      |
+---------+------------+
| 1000000 | 5UGdDjX6sI |
| 1000001 | 1BmxMSJMNO |
| 1000002 | fRA4G96KoZ |
| 1000003 | 0PEhFBERn3 |
| 1000004 | x9N5k3eez5 |
| 1000005 | KeIyt1RHX5 |
| 1000006 | BaDGNWrvPj |
| 1000007 | hnpbYAQtKW |
| 1000008 | XI1a2RAy0G |
| 1000009 | ZmnI8XobRb |
+---------+------------+
10 rows in set (0.22 sec)

mysql [d1]>  SELECT id, value FROM t1 ORDER BY id LIMIT 11 OFFSET 999999;
+---------+------------+
| id      | value      |
+---------+------------+
| 1000000 | 5UGdDjX6sI |
| 1000001 | 1BmxMSJMNO |
| 1000002 | fRA4G96KoZ |
| 1000003 | 0PEhFBERn3 |
| 1000004 | x9N5k3eez5 |
| 1000005 | KeIyt1RHX5 |
| 1000006 | BaDGNWrvPj |
| 1000007 | hnpbYAQtKW |
| 1000008 | XI1a2RAy0G |
| 1000009 | ZmnI8XobRb |
| 1000010 | N6XXZnw3mo |
+---------+------------+
11 rows in set (0.19 sec)

mysql  [d1]> show profile;
+----------------------------+----------+
| Status                     | Duration |
+----------------------------+----------+
| starting                   | 0.000265 |
| checking permissions       | 0.000019 |
| Opening tables             | 0.000135 |
| init                       | 0.000022 |
| System lock                | 0.000065 |
| optimizing                 | 0.000011 |
| statistics                 | 0.000046 |
| preparing                  | 0.000031 |
| Sorting result             | 0.000009 |
| executing                  | 0.000008 |
| Sending data               | 0.189200 |
| end                        | 0.000031 |
| query end                  | 0.000009 |
| waiting for handler commit | 0.000016 |
| query end                  | 0.000014 |
| closing tables             | 0.000020 |
| freeing items              | 0.000038 |
| cleaning up                | 0.000048 |
+----------------------------+----------+
18 rows in set, 1 warning (0.00 sec)

Warning (Code 1287): 'SHOW PROFILE' is deprecated and will be removed in a future release. Please use Performance Schema instead

window関数の利用するするほうはCreating tmp tableしてCreating sort indexしてるがこのsort indexが重いらしい。 件数がすくないとたいしたことないかもしれないけど無難にlimitに書き換えてあげたほうがよいかもしれない。

(書いてから気づいたけどbetweenじゃなくて普通にid > ? and id <= ?ってすればよかった)

MTSを使ってクエリをロールバックする。

この記事はMySQL Casual Advent Calendar 2018の16日目です。

元ネタはlefredさんのこちらのスライド FOSDEM MySQL & Friends Devroom, February 2018 MySQL Point-in-Time Recovery like a Rockstar

ざっくりいうと、 マルチスレッドスレーブ(以下MTS)を使ってbinlogを読み込ませてPITRを早くするというものです。 検証環境はCentOS7、MySQL8.0.13。

例えばこんな感じなテーブル。

 fuki1 [d1]> SELECT * FROM t1 limit 10;
+----+------------+
| id | value      |
+----+------------+
|  1 | C8vAt5qSyl |
|  2 | 15mtFfSGuz |
|  3 | 96ZFZ0451W |
|  4 | ZHM6qMQRXI |
|  5 | xMkqCVFgOT |
|  6 | FWBCUry1FW |
|  7 | Uoa5Y1sOPK |
|  8 | DoCkXtvZSj |
|  9 | JdiF94D1t2 |
| 10 | dwqV6ykt54 |
+----+------------+
10 rows in set (0.01 sec)
 
fuki1 [d1]> SELECT COUNT(1) FROM t1;
+----------+
| COUNT(1) |
+----------+
|  1010000 |
+----------+
1 row in set (0.15 sec)

このテーブルにこんな更新を全件にかけてしまったとします。

fuki1 [d1]> UPDATE t1 SET value = 'lhfukamachi';
Query OK, 1010000 rows affected (14.48 sec)
Rows matched: 1010000  Changed: 1010000  Warnings: 0

fuki1 [d1]> SELECT * FROM t1 limit 10;
+----+-------------+
| id | value       |
+----+-------------+
|  1 | lhfukamachi |
|  2 | lhfukamachi |
|  3 | lhfukamachi |
|  4 | lhfukamachi |
|  5 | lhfukamachi |
|  6 | lhfukamachi |
|  7 | lhfukamachi |
|  8 | lhfukamachi |
|  9 | lhfukamachi |
| 10 | lhfukamachi |
+----+-------------+
10 rows in set (0.03 sec)

UPDATE t1 SET value = 'lhfukamachi'によってvalueカラムが全て'lhfukamachi'に書き換わってしまいました。 その後もデータが来る想定でこのテーブルに1000件ほどデータを追加します(ついでに他のテーブルにも更新します。

fuki1 [d1]> select count(1) FROM t1;
+----------+
| count(1) |
+----------+
|  1011000 |
+----------+
1 row in set (0.13 sec)

通常PITRをする時は、クエリが実行される以前のフルバックアップ + バイナリログを用いてmysqlbinlogコマンドを使って特定のクエリのポジションを飛ばしてデータを再構築するやり方が一般的かと思います。 MTS(というか、レプリケーション)を使ったやり方の場合はbinlogをあたかもrelaylogとして読ませてやるやり方らしいです。

(下記記事の「レプリケーション機能を使用してリカバリする」を参考) 第46回 特定テーブルをロールバックする方法:MySQL道普請便り|gihyo.jp … 技術評論社

とりあえず現状のgtid_executedを確認してみます。

fuki1 [(none)]> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 11025939
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 7d2772a5-8987-11e8-a6a6-0201858272ab:1-128919
1 row in set (0.03 sec)  

次に、問題のクエリをbinlogから探し出してGTIDを調べます。

$ /usr/local/mysql8012/bin/mysqlbinlog -vv mysql-bin.000002 | grep -A10 -B20 'lhfukamachi' 
(省略)
SET @@SESSION.GTID_NEXT= '7d2772a5-8987-11e8-a6a6-0201858272ab:123919'/*!*/;
# at 9448700
#181216 13:28:00 server id 19355  end_log_pos 9448789 CRC32 0xd73ed147  Query   thread_id=30269 exec_time=15    error_code=0
SET TIMESTAMP=1544934480/*!*/;
BEGIN
/*!*/;
# at 9448789
#181216 13:28:00 server id 19355  end_log_pos 9448908 CRC32 0x71786abb  Query   thread_id=30269 exec_time=15    error_code=0
SET TIMESTAMP=1544934480/*!*/;
UPDATE t1 SET value = 'lhfukamachi'
/*!*/;
# at 9448908
#181216 13:28:00 server id 19355  end_log_pos 9448939 CRC32 0x4314fe86  Xid = 91082
COMMIT/*!*/;
# at 9448939
#181216 13:28:54 server id 19355  end_log_pos 9449014 CRC32 0x3e0275c5  GTID    last_committed=30003    sequence_number=30004   rbr_only=no     original_committed_timestamp=1544934534697601 immediate_commit_timestamp=1544934534697601      transaction_length=317
# original_commit_timestamp=1544934534697601 (2018-12-16 13:28:54.697601 JST)
# immediate_commit_timestamp=1544934534697601 (2018-12-16 13:28:54.697601 JST)
/*!80001 SET @@session.original_commit_timestamp=1544934534697601*//*!*/;
SET @@SESSION.GTID_NEXT= '7d2772a5-8987-11e8-a6a6-0201858272ab:123920'/*!*/;

問題のクエリのGTIDはこれっぽい。

7d2772a5-8987-11e8-a6a6-0201858272ab:123919

ここで一旦MySQLを止めて、binlogを退避後、過去のコールドバックアップをもってきます。

$ MYSQL_PWD='********' /usr/local/mysql8013/bin/mysqladmin --defaults-file=/data/mydatabase/my.cnf -uroot shutdown
$ cp -p  /data/mydatabase/mysql-bin.index /data/tmp/relaylog_backup/mysql-relay.index
$ cp -p  /data/mydatabase/mysql-bin.000001 /data/tmp/relaylog_backup/mysql-relay.000001
$ cp -p  /data/mydatabase/mysql-bin.000002 /data/tmp/relaylog_backup/mysql-relay.000002
$ vim mysql-relay.index
~
`mysql-bin.*` を`mysql-relay*`に書きなおしましょう。
~

// 過去のコールドバックアップの解凍
# zstd -dc -p2 mydatabase.20182214.tar.zst | tar xv
// 現在のdatadirを名前を変えて退避
# mv /data/mydatabase  /data/_mydatabase
// datadirに過去のコールドバックアップを置き直す。
# mv mydatabase /data/mydatabase

続いて過去のbinlogをrelaylogとして設置します

$ cp -p /data/tmp/relaylog_backup/mysql-relay.* /data/mydatabase/

あとはMySQLを起動後、RESET MASTERしてから除外するクエリのGTIDをそえて、GTID_PURGEDを設定します。

fuki1 [(none)]> SELECT @@gtid_executed;
+----------------------------------------------+
| @@gtid_executed                              |
+----------------------------------------------+
| 7d2772a5-8987-11e8-a6a6-0201858272ab:1-93916 |
+----------------------------------------------+
1 row in set (0.00 sec)

fuki1 [(none)]> RESET MASTER;
Query OK, 0 rows affected (0.06 sec)

fuki1 [d1]> SET GLOBAL GTID_PURGED='7d2772a5-8987-11e8-a6a6-0201858272ab:1-93916,7d2772a5-8987-11e8-a6a6-0201858272ab:123919'; 
Query OK, 0 rows affected (0.08 sec)

fuki1 [mysql]> SET GLOBAL server_id = 1;
Query OK, 0 rows affected (0.00 sec)

fuki1 [mysql]> SET GLOBAL SLAVE_PARALLEL_TYPE = 'LOGICAL_CLOCK';
Query OK, 0 rows affected (0.00 sec)

fuki1 [mysql]> SET GLOBAL SLAVE_PARALLEL_WORKERS = 3;
Query OK, 0 rows affected (0.00 sec)

CHANGE MASTER構文とSTART SLAVE sql_threadを実施してreloylog(もともとbinlogだったやつ)の読み込み開始

fuki1 [(none)]> CHANGE MASTER TO master_host='dummy',
    -> relay_log_file = 'mysql-relay.000001',
    -> relay_log_pos= 4;
Query OK, 0 rows affected (0.04 sec)

fuki1 [(none)]> START SLAVE sql_thread;
Query OK, 0 rows affected (0.10 sec)

おぉ。innotopのトランザクションをみてると確かに3 threadで実行されてる!!

History  Versions  Undo  Dirty Buf  Used Bufs  Txns  MaxTxnTime  LStrcts
     51                      2.67%      3.78%     8       00:00

ID  User        Host  Txn Status  Time   Undo  Query Text
12  system use        ACTIVE (PR  00:00     2
13  system use        ACTIVE (PR  00:00     2
14  system use        ACTIVE (PR  00:00     2

完了後データを確認してみるとたしかにgtid_purgedで設定したクエリはとばされて読み込みが完了してます。

fuki1 [d1]> SELECT * FROM t1 limit 10;
+----+------------+
| id | value      |
+----+------------+
|  1 | C8vAt5qSyl |
|  2 | 15mtFfSGuz |
|  3 | 96ZFZ0451W |
|  4 | ZHM6qMQRXI |
|  5 | xMkqCVFgOT |
|  6 | FWBCUry1FW |
|  7 | Uoa5Y1sOPK |
|  8 | DoCkXtvZSj |
|  9 | JdiF94D1t2 |
| 10 | dwqV6ykt54 |
+----+------------+
10 rows in set (0.01 sec)

fuki1 [d1]> SELECT @@gtid_executed;
+-----------------------------------------------+
| @@gtid_executed                               |
+-----------------------------------------------+
| 7d2772a5-8987-11e8-a6a6-0201858272ab:1-128919 |
+-----------------------------------------------+
1 row in set (0.00 sec)

fuki1 [d1]> select count(1) FROM t1;
+----------+
| count(1) |
+----------+
|  1011000 |
+----------+
1 row in set (0.13 sec)

こんな感じでMTSを利用してPITRができるようです。すごい! 本来は速度検証してどれくらい早くなるかまでやりたかったけど更新データ量が少なくてそこまで至らなかった感じです。。。:bow: 多分自分が本番で実際することはないですが、こういうやり方もあるよーって感じで勉強になりました。

以上です。

mysqlのバージョンが古くてdump-slaveオプションが使えない時の対処法

これは MySQL Casual Advent Calendar 2017 の Day14 の記事です。

少し前にMySQL4.0を5.0にするという作業をしたときにmysqldumpでdump-slaveオプションが使えなかったときの話です。 世間ではMySQL8.0で賑わって?おりますが、このご時世4.0というバージョンを使っていたアプリケーションがありまして、とりあえず5.0にしようかという話になりました。 そこで5.0にするために事前に5.0のレプリケーションをmysqldumpを使って作ることにしました。

そのデータベースはアプリケーションで運用しているMasterのデータベースが1台と、バックアップ取得用としてレプリケーションを組んだデータベースが1台ありました。 mysqldumpには5.5からdump-slaveというオプションがありまして、このオプションを利用することでslaveのデータベースから、Masterのpositionを取得して"CHANGE MASTER TO"の中に埋め込むことができます。 しかし、5.5未満のバージョンにはこのプションがありません。

そこでFLUSH TABLES WITH READ LOCKをslave側に使ってmysqldumpを行うという技を利用して対処しました。

用意するもの:ターミナル2ウィンドウ(以下trm1,trm2)

  1. trm1でFLUSH TABLES WITH READ LOCKをかけてテーブルロックをかける
trm1> FLUSH TABLES WITH READ LOCK;
  1. trm1でshow slave statusを実行し、dump取得直前のExec_master_log_posを確認
trm1 > show slave status\G
*************************** 1. row ***************************
          Master_Host: 192.168.33.101
          Master_User: replication
          Master_Port: 3306
        Connect_retry: 60
      Master_Log_File: mysql-bin.8747
  Read_Master_Log_Pos: 1018652977
       Relay_Log_File: relay-bin.2679
        Relay_Log_Pos: 363016692
Relay_Master_Log_File: mysql-bin.8747
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_do_db:
  Replicate_ignore_db:
           Last_errno: 0
           Last_error:
         Skip_counter: 0
  Exec_master_log_pos: 1018652977
      Relay_log_space: 363016692
1 row in set (0.00 sec)
  1. trm2でdumpを開始
$ /usr/local/mysql/bin/mysqldump --socket=/tmp/mysql.sock -uroot -p'****' > /tmp/dump.sql
  1. dump完了後trm1でshow slave statusでexec master log posが変更してないことを確認
trm1 > show slave status\G
*************************** 1. row ***************************
          Master_Host: 192.168.33.101
          Master_User: replication
          Master_Port: 3306
        Connect_retry: 60
      Master_Log_File: mysql-bin.8747
  Read_Master_Log_Pos: 1018652977
       Relay_Log_File: relay-bin.2679
        Relay_Log_Pos: 363016692
Relay_Master_Log_File: mysql-bin.8747
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_do_db:
  Replicate_ignore_db:
           Last_errno: 0
           Last_error:
         Skip_counter: 0
  Exec_master_log_pos: 1018652977
      Relay_log_space: 363016692
1 row in set (0.00 sec)
  1. unlock tables;でロックを解除
trm1> unlock tables;
  1. show slave statusレプリケーションが再開していることを再確認する
trm1 > show slave status\G
*************************** 1. row ***************************
          Master_Host: 192.168.33.101
          Master_User: replication
          Master_Port: 3306
        Connect_retry: 60
      Master_Log_File: mysql-bin.8747
  Read_Master_Log_Pos: 1028503328
       Relay_Log_File: relay-bin.2679
        Relay_Log_Pos: 372867043
Relay_Master_Log_File: mysql-bin.8747
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_do_db:
  Replicate_ignore_db:
           Last_errno: 0
           Last_error:
         Skip_counter: 0
  Exec_master_log_pos: 1028503328
      Relay_log_space: 372867043
1 row in set (0.00 sec)

無事Masterのpositionを把握してdumpを取得することができました。あとは新Slaveにdumpを適用して"CHANGE MASTER TO"を打てば完了です。 長年MySQLを触っている方にとっては常套手段らしいですね。とはいえ、dumpslaveが使えないと知った時はどうやってやるのか検討もつかなかったので無事作成できて何よりです。

tpcc-mysqlをインストールしてみた

tpcc-mysqlのインストール備忘録。 個人環境にクエリをかけながら検証をしたかったので元隣の隣の人に相談した所、tpcc-mysqlというベンチマークツールを教えてもらったのでインストールしてみた。 mysqlyumでいれてないためところどころ詰まったので一緒にメモっておく

GitHub - Percona-Lab/tpcc-mysql

$ git clone https://github.com/Percona-Lab/tpcc-mysql.git
$ cd src
$ vim Makefile

###
mysql_configのパスをフルパスに書き直した。
LIBS=       `/usr/local/mysql5718/bin/mysql_config --libs_r` -lrt

INC=        -I. `/usr/local/mysql5718/bin/mysql_config --include`
###

$ cd src ; make
##  databaseの作成。例に習って`tpcc1000`とする
MYSQL_PWD="mysql_pass" /usr/local/mysql5718/bin/mysqladmin --defaults-file=<my.cnfのパス> -uroot create tpcc1000

##  CREATE TABLE文の流し込み。
$ mysql -u root -p tpcc1000 < /data/tpcc-mysql.git/create_table.sql

## データ挿入!とおもったらコケた。。。
$ ./tpcc_load -h127.0.0.1 -d tpcc1000 -u root -p "mysql_pass" -w 1000
./tpcc_load: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory

## シンボリックリンク貼り直し
$ ln -s /usr/local/mysql5718/lib/libmysqlclient.so /usr/lib64/libmysqlclient.so.20

$ ./tpcc_load -hlocalhost -d tpcc1000 -u root -p "mysql_pass" -w 1000
*************************************
*** TPCC-mysql Data Loader        ***
*************************************
option h with value 'localhost'
option d with value 'tpcc1000'
option u with value 'root'
option p with value 'mysql_pass'
option w with value '1000'
<Parameters>
     [server]: localhost
     [port]: 3306
     [DBname]: tpcc1000
       [user]: root
       [pass]: mysql_pass
  [warehouse]: 1000

2002, HY000, Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

## `/tmp/mysql.sock`を見に行ってしまうらしい。シンボリックリンクで凌ぐ
$ ln -s <my.cnfに設定しているパス>/mysql.sock /tmp/mysql.sock

## 通った。
$ ./tpcc_load -hlocalhost -d tpcc1000 -u root -p "mysql_pass" -w 1000
*************************************
*** TPCC-mysql Data Loader        ***
*************************************
option h with value 'localhost'
option d with value 'tpcc1000'
option u with value 'root'
option p with value 'mysql_pass'
option w with value '1000'
<Parameters>
     [server]: localhost
     [port]: 3306
     [DBname]: tpcc1000
       [user]: root
       [pass]: mysql_pass
  [warehouse]: 1000
TPCC Data Load Started...
Loading Item
.................................................. 5000
.................................................. 10000
.................................................. 15000
.................................................. 20000
.................................................. 25000
.................................................. 30000
.................................................. 35000
.................................................. 40000

...

実行。

./tpcc_start -h 127.0.0.1 -P 3306 -d tpcc1000 -u root -p mysql_pass -w 1000 -c 32 -r 10 -l 1000

実行中にInnoTopを見たところ下記みたいな感じ。ちゃんと32接続来てる。

When   Load  Cxns  QPS     Slow  Se/In/Up/De%  QCacheHit  KCacheHit  BpsIn    BpsOut
Now    0.00    34   3.14k     0  59/15/20/ 1       0.00%    100.00%  129.85k  942.15k
Total  0.00   151  10.14      0  59/15/20/ 1       0.00%     77.27%  424.62     3.14k

Cmd      ID      State               User   Host       DB      Time   Query
When   Load  Cxns  QPS     Slow  Se/In/Up/De%  QCacheHit  KCacheHit  BpsIn    BpsOut
Now    0.00    34   3.14k     0  59/15/20/ 1       0.00%    100.00%  129.85k  942.15k
Total  0.00   151  10.14      0  59/15/20/ 1       0.00%     77.27%  424.62     3.14k

Cmd      ID      State               User   Host       DB      Time   Query
Execute     270  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     271  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     272  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     273  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     276  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     277  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     278  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     279  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     280  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     281  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     284  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     285  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     286  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y1
Execute     289  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     291  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     293  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     294  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     295  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     296  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     297  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     298  Creating sort inde  root   127.0.0.1  tpcc1000   00:01  SELECT c_id FROM custo
Execute     300  updating            root   127.0.0.1  tpcc1000   00:01  UPDATE warehouse SET w_y
Execute     269  updating            root   127.0.0.1  tpcc1000   00:00  UPDATE warehouse SET w_y
Execute     274  Sending to client   root   127.0.0.1  tpcc1000   00:00  SELECT s_quantity, s_data
Execute     275  updating            root   127.0.0.1  tpcc1000   00:00  UPDATE warehouse SET w_y
Execute     282  updating            root   127.0.0.1  tpcc1000   00:00  UPDATE warehouse SET w_y
Execute     283  Creating sort inde  root   127.0.0.1  tpcc1000   00:00  SELECT c_id FROM custo
Execute     287  updating            root   127.0.0.1  tpcc1000   00:00  UPDATE district SET d_ytd = 
Execute     288  updating            root   127.0.0.1  tpcc1000   00:00  UPDATE order_line SET ol_d
Execute     290  closing tables      root   127.0.0.1  tpcc1000   00:00  UPDATE stock SET s_quant
Execute     292  closing tables      root   127.0.0.1  tpcc1000   00:00  SELECT count(*) FROM stoc
Execute     299  updating            root   127.0.0.1  tpcc1000   00:00  UPDATE warehouse SET w_y

ただ、途中でサーバーの容量がいっぱいいっぱいになってしまったので最後まで実行完了できなかった。

MySQLでユーザーを作ったときのハッシュ値から同じ`PASSWORD`でユーザーを生成する

MySQL5.7の話。本日隣の人から教えてもらった事。

ユーザーを作るとき等でAPサーバーのIPが追加や変更されるときにハッシュ値から同じパスワードを設定することができる。

例えば、

fuki [(none)]> create user 'fuki'@'127.0.0.1' identified by 'Abc123';
Query OK, 0 rows affected (0.07 sec)

fuki [(none)]> SELECT user,host,authentication_string FROM mysql.user WHERE user='fuki' ORDER BY 1,2;
+------+-----------+-------------------------------------------+
| user | host      | authentication_string                     |
+------+-----------+-------------------------------------------+
| fuki | 127.0.0.1  | *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
+------+-----------+-------------------------------------------+
1 rows in set (0.00 sec)

上記のようなfukiを作るとパスワードはauthentication_stringにあるようなハッシュ値になる。 このハッシュ値を利用してユーザーを再作成するときはIDENTIFIED BYのあとにPASSWORDをつける。

mysql [(none)]> create user 'fuki'@'localhost' identified by password '*7A8BBCB18A250055A6BB98ECFA33A8174D219504';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 1287): 'IDENTIFIED BY PASSWORD' is deprecated and will be removed in a future release. Please use IDENTIFIED WITH <plugin> AS <hash> instead
mysql [(none)]> SELECT user,host,authentication_string FROM mysql.user WHERE user='fuki' ORDER BY 1,2;
+------+-----------+-------------------------------------------+
| user | host      | authentication_string                     |
+------+-----------+-------------------------------------------+
| fuki | 127.0.0.1 | *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
| fuki | localhost | *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

同じハッシュ値になっている。

しかし、Warningにもあるようにいずれなくなる機能のようである。 まあ、普通に作成しろってことですよね。

MySQLのbinlogを見る方法

MySQLで生成されるbinログはlessでみてもバイナリログなので自分では解読できない なのでmysqlbinlogコマンドで見る。 下記は自分の環境5.7.18で試した結果

$ /usr/local/mysql5718/bin/mysqlbinlog mysql-bin.000004
SET @@SESSION.GTID_NEXT= '627024f4-2645-11e7-b811-001a4a5718c0:12'/*!*/;
# at 1538
#170530  0:50:09 server id 33083  end_log_pos 1680 CRC32 0x4ec4a13b     Query   thread_id=18    exec_time=0 error_code=0
use `fuki`/*!*/;
SET TIMESTAMP=1496073009/*!*/;
create table fuki.user_master (
user_id int, user_name varchar(256))
/*!*/;
# at 1680
#170530  0:50:47 server id 33083  end_log_pos 1745 CRC32 0x04caa6e8     GTID    last_committed=6    sequence_number=7
SET @@SESSION.GTID_NEXT= '627024f4-2645-11e7-b811-001a4a5718c0:13'/*!*/;
# at 1745
#170530  0:50:44 server id 33083  end_log_pos 1824 CRC32 0xd6d6771f     Query   thread_id=18    exec_time=0 error_code=0
SET TIMESTAMP=1496073044/*!*/;
BEGIN
/*!*/;
# at 1824
#170530  0:50:44 server id 33083  end_log_pos 1939 CRC32 0xf4184acd     Query   thread_id=18    exec_time=0 error_code=0
SET TIMESTAMP=1496073044/*!*/;
insert into user_master values (1,'fuki')
/*!*/;
# at 1939
#170530  0:50:47 server id 33083  end_log_pos 1970 CRC32 0x37fe8db8     Xid = 1775
COMMIT/*!*/

よく使いそうなオプションとしては--start-datetime=<datetime>'`--stop-datetime=<datetime> で時間を区切るとかかな。 とりあえず困ったら--helpでhelpを見る。