日々の覚書

いつから入ったか忘れたけど、5.6の途中くらいだった気がする。

mysql80 8> CREATE USER yoku0825 IDENTIFIED BY 'abc';
Query OK, 0 rows affected (0.01 sec)

$ mysqlbinlog bin.000002
..
CREATE USER 'yoku0825'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E'
/*!*/;
..

こんな感じで、平文でパスワードを指定するようなクエリーを実行させると、バイナリログでは互換性のあるハッシュ形式に書き換えて記録してくれるやつ。

mysql80 8> CREATE USER yoku0824 IDENTIFIED BY 'c';

(gdb) bt
+bt
#0  Rewriter_create_user::append_user_auth_info (this=0x7f89a00c8cc0, user=0x7f89a000c1e0, comma=false, str=0x7f89f7334c00) at /home/yoku0825/mysql-8.0.25/sql/sql_rewrite.cc:774
#1  0x0000000000dd7fbf in Rewriter_user::rewrite_users (this=this@entry=0x7f89a00c8cc0, lex=lex@entry=0x7f89a00cf940, str=str@entry=0x7f89f7334c00)
    at /home/yoku0825/mysql-8.0.25/sql/sql_rewrite.cc:714
#2  0x0000000000dd824a in Rewriter_user::rewrite (this=this@entry=0x7f89a00c8cc0, rlb=
      @0x7f89f7334c00: {m_ptr = 0x7f89a000d750 "CREATE USER ", m_length = 12, m_charset = 0x38294c0 <my_charset_bin>, m_alloced_length = 16, m_is_alloced = true})
    at /home/yoku0825/mysql-8.0.25/sql/sql_rewrite.cc:419
#3  0x0000000000dd8338 in Rewriter_create_user::rewrite (this=0x7f89a00c8cc0, rlb=
      @0x7f89f7334c00: {m_ptr = 0x7f89a000d750 "CREATE USER ", m_length = 12, m_charset = 0x38294c0 <my_charset_bin>, m_alloced_length = 16, m_is_alloced = true})
    at /home/yoku0825/mysql-8.0.25/sql/sql_rewrite.cc:758
#4  0x0000000000dd8bbd in (anonymous namespace)::rewrite_query(THD*, Consumer_type, Rewrite_params*, String&) () at /home/yoku0825/mysql-8.0.25/sql/sql_rewrite.cc:297
#5  0x0000000000dd8e3f in mysql_rewrite_query(THD*, Consumer_type, Rewrite_params*) () at /home/yoku0825/mysql-8.0.25/sql/sql_rewrite.cc:344
#6  0x0000000000d8bc7a in dispatch_sql_command (thd=0x7f89a000fdf0, parser_state=<optimized out>) at /home/yoku0825/mysql-8.0.25/sql/sql_parse.cc:4920
#7  0x0000000000d8ce6a in dispatch_command(THD*, COM_DATA const*, enum_server_command) () at /home/yoku0825/mysql-8.0.25/sql/sql_parse.cc:1841
#8  0x0000000000d8ed04 in do_command (thd=0x7f89a000fdf0) at /home/yoku0825/mysql-8.0.25/sql/sql_parse.cc:1320
#9  0x0000000000eb3b88 in handle_connection (arg=arg@entry=0x78589f0) at /home/yoku0825/mysql-8.0.25/sql/conn_handler/connection_handler_per_thread.cc:301
#10 0x000000000234101e in pfs_spawn_thread (arg=0x79cd080) at /home/yoku0825/mysql-8.0.25/storage/perfschema/pfs.cc:2898
#11 0x00007f8a08141ea5 in start_thread (arg=0x7f89f7336700) at pthread_create.c:307
#12 0x00007f8a065249fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

(gdb) p *user
+p *user
$4 = {
  user = {
    str = 0x7f89a000c1c0 "yoku0824",
    length = 8
  },
  host = {
    str = 0x25c3a9c "%",
    length = 1
  },
  plugin = {
    str = 0x25be139 "",
    length = 0
  },
  auth = {
    str = 0x7f89a000c268 "c",
    length = 1
  },
  current_auth = {
    str = 0x0,
    length = 0
  },
  uses_identified_by_clause = true,
  uses_identified_with_clause = false,
  uses_authentication_string_clause = false,
  uses_replace_clause = false,
  retain_current_password = false,
  discard_old_password = false,
  has_password_generator = false,
  alter_status = {
    update_password_expired_fields = false,
    update_password_expired_column = false,
    use_default_password_lifetime = true,
    expire_after_days = 0,
    update_account_locked_column = false,
    account_locked = false,
    password_history_length = 0,
    use_default_password_history = true,
    update_password_history = false,
    password_reuse_interval = 0,
    use_default_password_reuse_interval = false,
    update_password_reuse_interval = false,
    failed_login_attempts = 0,
    update_failed_login_attempts = false,
    password_lock_time = 0,
    update_password_lock_time = false,
    update_password_require_current = UNCHANGED
  }
}

割と汎用的な mysql_rewrite_query という名前の関数の割に

https://github.com/mysql/mysql-server/blob/mysql-8.0.25/sql/sql_rewrite.cc#L303-L348

そこから呼び出しているやっぱり汎用的な名前の rewrite_query 関数は、アカウント系DDLの操作に特化したリライトしかしてない。

https://github.com/mysql/mysql-server/blob/mysql-8.0.25/sql/sql_rewrite.cc#L216-L300

ジェネラルログも書き換えてた。

2021-06-17T14:47:18.332134+09:00            8 Query     CREATE USER 'yoku0824'@'%' IDENTIFIED BY <secret>

ところで、 CREATE USER を1回さばく間に Rewriter_create_user::append_user_auth_info を3回通ったんだけど、最初の dispatch_sql_command から呼ばれる時点で書き換えはは完了しているっぽい。

(gdb) b Rewriter_create_user::append_user_auth_info
+b Rewriter_create_user::append_user_auth_info
Breakpoint 1 at 0xdd7ad0: file /home/yoku0825/mysql-8.0.25/sql/sql_rewrite.cc, line 774.

(gdb) p *user
+p *user
$4 = {
  user = {
    str = 0x7f89a000c1c0 "yoku0824",
    length = 8
  },
  host = {
    str = 0x25c3a9c "%",
    length = 1
  },
  plugin = {
    str = 0x25be139 "",
    length = 0
  },
  auth = {
    str = 0x7f89a000c268 "c",
    length = 1
  },
  current_auth = {
    str = 0x0,
    length = 0
  },
  uses_identified_by_clause = true,
  uses_identified_with_clause = false,
  uses_authentication_string_clause = false,
  uses_replace_clause = false,
  retain_current_password = false,
  discard_old_password = false,
  has_password_generator = false,
  alter_status = {
    update_password_expired_fields = false,
    update_password_expired_column = false,
    use_default_password_lifetime = true,
    expire_after_days = 0,
    update_account_locked_column = false,
    account_locked = false,
    password_history_length = 0,
    use_default_password_history = true,
    update_password_history = false,
    password_reuse_interval = 0,
    use_default_password_reuse_interval = false,
    update_password_reuse_interval = false,
    failed_login_attempts = 0,
    update_failed_login_attempts = false,
    password_lock_time = 0,
    update_password_lock_time = false,
    update_password_require_current = UNCHANGED
  }
}

$ strings ib_logfile0  | grep yoku0824

1回目はまだ1回もリライトしてないので生パスワードのまま。

(gdb) c
+c
Continuing.

Breakpoint 1, Rewriter_create_user::append_user_auth_info (this=0x7f89a00c8cc0, user=0x7f89a000c1e0, comma=false, str=0x7f89f7334c00)
    at /home/yoku0825/mysql-8.0.25/sql/sql_rewrite.cc:774
774       append_auth_id(m_thd, user, comma, str);

(gdb) p *user
+p *user
$6 = {
  user = {
    str = 0x7f89a000c1c0 "yoku0824",
    length = 8
  },
  host = {
    str = 0x25c3a9c "%",
    length = 1
  },
  plugin = {
    str = 0x25d023d "mysql_native_password",
    length = 21
  },
  auth = {
    str = 0x7f89a01e6690 "*FDD369C6B7C3C64C7C07EDE4DC5C01BF8970B24D",
    length = 41
  },
  current_auth = {
    str = 0x0,
    length = 0
  },
  uses_identified_by_clause = true,
  uses_identified_with_clause = false,
  uses_authentication_string_clause = false,
  uses_replace_clause = false,
  retain_current_password = false,
  discard_old_password = false,
  has_password_generator = false,
  alter_status = {
    update_password_expired_fields = false,
    update_password_expired_column = false,
    use_default_password_lifetime = true,
    expire_after_days = 0,
    update_account_locked_column = false,
    account_locked = false,
    password_history_length = 0,
    use_default_password_history = true,
    update_password_history = false,
    password_reuse_interval = 0,
    use_default_password_reuse_interval = true,
    update_password_reuse_interval = false,
    failed_login_attempts = 0,
    update_failed_login_attempts = false,
    password_lock_time = 0,
    update_password_lock_time = false,
    update_password_require_current = UNCHANGED
  }
}

$ strings ib_logfile0  | grep yoku0824

2回目の時点では既にLEX_USER自体がハッシュを使ったものに書き換えられている。

(gdb) c
+c
Continuing.

Breakpoint 1, Rewriter_create_user::append_user_auth_info (this=0x7f89a0086610, user=0x7f89a000c1e0, comma=false, str=0x7f89f732ff80)
    at /home/yoku0825/mysql-8.0.25/sql/sql_rewrite.cc:774
774       append_auth_id(m_thd, user, comma, str);

$ strings ib_logfile0  | grep yoku0824
                                                                           yoku0824
yoku0824

3回目に差し掛かった時点で ib_logfile0 の中に書き込まれている。

Lossless-semisyncではない環境なので、1回目がgeneral_log/SHOW PROCESSLIST用の書き換え、2回目が ib_logfile0 への書き込み、3回目がbinlogへの書き込みとかなのかな。