2017-12-14

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）

trm1でFLUSH TABLES WITH READ LOCKをかけてテーブルロックをかける

trm1> FLUSH TABLES WITH READ LOCK;

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)

trm2でdumpを開始

$ /usr/local/mysql/bin/mysqldump --socket=/tmp/mysql.sock -uroot -p'****' > /tmp/dump.sql

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)

unlock tables;でロックを解除

trm1> unlock tables;

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が使えないと知った時はどうやってやるのか検討もつかなかったので無事作成できて何よりです。

2017-08-28

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

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

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

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

2017-07-24

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にもあるようにいずれなくなる機能のようである。まあ、普通に作成しろってことですよね。

2017-05-30

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を見る。

そろばんのしょ（第3版第2刷）

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

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

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

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

MySQLのbinlogを見る方法