validate_passwordコンポーネントを消さずにパスワードポリシーを満たさないパスワードを設定する

TL;DR

• CREATE USER .. IDENTIFIED WITH caching_sha2_password AS x'..' の形式で入れればMySQL側は元の文字列を知らないのでパスワードポリシーに引っかかることはない

---

自分で建てたわけではないMySQL を使って遊んでいるところだけれど、 PMM を入れる時にちょっと簡単なパスワードを使いたいと思った。

mysql> CREATE USER pmm@xxx IDENTIFIED BY 'xxx';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

いつものローカルLinuxだったら —initialize-insecure でセットアップしちゃうからすっかり忘れてた。

mysql> SHOW VARIABLES LIKE '%validate%';
+-------------------------------------------------+--------+
| Variable_name                                   | Value  |
+-------------------------------------------------+--------+
| innodb_validate_tablespace_paths                | OFF    |
| lakehouse_validate_table                        |        |
| validate_password.changed_characters_percentage | 0      |
| validate_password.check_user_name               | ON     |
| validate_password.dictionary_file               |        |
| validate_password.length                        | 8      |
| validate_password.mixed_case_count              | 1      |
| validate_password.number_count                  | 1      |
| validate_password.policy                        | MEDIUM |
| validate_password.special_char_count            | 1      |
+-------------------------------------------------+--------+
10 rows in set (0.00 sec)

使いたいパスワードに数字が入ってないのがいけないんだと思うので、無理を通すなら validate_password コンポーネント(MySQL 8.0とそれ以降で validate_password プラグインから validate_password コンポーネントに変わる) をアンインストールしてインストールしなおすのも手か？

mysql> UNINSTALL COMPONENT 'file://component_validate_password';
ERROR 1142 (42000): DELETE command denied to user 'admin'@'xxx' for table 'component'

権限が足りない。であれば設定だけちょろまかして

mysql> SET SESSION validate_password.number_count = 0;
ERROR 1229 (HY000): Variable 'validate_password.number_count' is a GLOBAL variable and should be set with SET GLOBAL

mysql> SET GLOBAL validate_password.number_count = 0;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

これも権限が足りない。
それでもパスワードを強いものにするのは何か負けた気がするので考えてみたら、ハッシュ化されたあとのauthentication_stringを使えば生のパスワードはMySQLにもわからないんだと思い出す。

他のマシンから SELECT authentication_string FROM mysql.user WHERE uesr = 'pmm' で値を引いてそのまま新しい方に使ってみる。これはたぶん転ける。

mysql> CREATE USER pmm@xxx IDENTIFIED  WITH caching_sha2_password AS '$A$005$..';
ERROR 1827 (HY000): The password hash doesn't have the expected format.

caching_sha2_passwordのauthentication_stringはバイナリなので、端末に表示されてない制御文字が含まれている可能性が十分ある。
なので、16進数で取ってきて x'..' の修飾子で突っ込む。

• https://dev.mysql.com/doc/refman/8.4/en/hexadecimal-literals.html

mysql> CREATE USER pmm@xxx IDENTIFIED WITH caching_sha2_password AS x'24412430xxxxx';

Query OK, 0 rows affected (0.00 sec)

思惑通り成功。
………これ誰でもvalidate_passwordすり抜けられるんじゃ（）

​

table_open_cacheに関する実験

TL;DR

• table_open_cache を増やすとメモリ使用量が増えるというのは聞いたことがあるけど実際どれくらい増えるのか調べてみたかった
• 1コネクションで1つずつ SELECT * FROM t で65535個のテーブルを開かせてみた。
• かなりシンプルなテーブルなので複雑なのにするともっと食うかもしれない
• 競合が起こらないと仮定したケースなのでスピードはテーブルキャッシュヒット率に関わらずほぼ一定
• 起動直後が遅いのはデータディクショナリがあったまってないからじゃなかろうか
• 肝心のメモリ使用量は綺麗な結果にはならなかった
  • ↓memory(%)は全部でだいたい15GBなので1%が150MB
  • 雑に、1万増えると270MBくらい？

table_open_cache	count	memory(%)	time(s)
4000	0	1.9	N/A
4000	1	6.6	155
4000	2	7.2	105
4000	3	7.2	107
8000	0	3.1	N/A
8000	1	6.6	139
8000	2	7.4	104
8000	3	7.4	105
12000	0	3.2	N/A
12000	1	6.8	149
12000	2	9.0	109
12000	3	9.5	109
24000	0	3.1	N/A
24000	1	7.2	146
24000	2	9.3	109
24000	3	11.3	110
65535	0	3.1	N/A
65535	1	7.3	146
65535	2	9.1	104
65535	3	9.7	105
100000	0	3.1	N/A
100000	1	7.9	146
100000	2	10.2	104
100000	3	11.1	104

---

準備。
d2スキーマに65535個のテーブルを作る。

$ perl -MDBI -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/usr/mysql/8.0.41/data/mysql.sock", "root", ""); for (my $n= 1; $n <= 65535; $n++) { my $sql= sprintf("CREATE TABLE IF NOT EXISTS d2.%05d (num int)", $n); $conn->do($sql); }'

一度シャットダウンして起動しなおす。 innodb_buffer_pool_load_at_startup はOFF。

$ ps auxwwww | grep mysqld

yoku0825 1738789 31.0  1.9 1017124 305532 pts/0  Sl   00:49   0:03 /usr/mysql/8.0.41/bin/mysqld --basedir=/usr/mysql/8.0.41 --datadir=/usr/mysql/8.0.41/data --plugin-dir=/usr/mysql/8.0.41/lib/plugin --server-id=1080 --default-authentication-plugin=mysql_native_password --mysqlx=off --early-plugin-load=keyring_file.so --loose-keyring-file-data=/usr/mysql/8.0.41/keyring --admin-address=127.0.0.1 --admin-port=63080 --create-admin-listener-thread=ON --loose-innodb-print-ddl-logs --innodb-validate-tablespace-paths=OFF --log-error=error.log --pid-file=/usr/mysql/8.0.41/data/mysql.pid --socket=/usr/mysql/8.0.41/data/mysql.sock --port=64080

$ mysql80 -sse "SELECT @@table_open_cache"
4000

テスト用のクエリは65535個の各テーブルをSELECT *する（何も入ってないので空っぽ）だけ。

$ date ; time perl -MDBI -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/usr/mysql/8.0.41/data/mysql.sock", "root", ""); for (my $n= 1; $n <= 65535; $n++) { my $sql= sprintf("SELECT * FROM d2.%05d", $n); $conn->do($sql); }' ; date
Fri Feb 14 00:50:48 GMT 2025

real    2m35.310s
user    0m0.989s
sys     0m1.514s
Fri Feb 14 00:53:23 GMT 2025

$ ps auxwwww | grep mysqld 

yoku0825 1738789 14.9  6.6 2896216 1069348 pts/0 Sl   00:49   0:58 /usr/mysql/8.0.41/bin/mysqld --basedir=/usr/mysql/8.0.41 --datadir=/usr/mysql/8.0.41/data --plugin-dir=/usr/mysql/8.0.41/lib/plugin --server-id=1080 --default-authentication-plugin=mysql_native_password --mysqlx=off --early-plugin-load=keyring_file.so --loose-keyring-file-data=/usr/mysql/8.0.41/keyring --admin-address=127.0.0.1 --admin-port=63080 --create-admin-listener-thread=ON --loose-innodb-print-ddl-logs --innodb-validate-tablespace-paths=OFF --log-error=error.log --pid-file=/usr/mysql/8.0.41/data/mysql.pid --socket=/usr/mysql/8.0.41/data/mysql.sock --port=64080

1回目の時点では 422 table/sec でオープン完了、メモリの利用率は4.7%増(15.3GBくらいメモリがあるので700MBくらいの増)

テーブルオープンにはデータディクショナリもアクセスするからInnoDBバッファプールもあるだろうしテーブルキャッシュが効かなさそうな数だけど2回目は速くなるはず。

$ date ; time perl -MDBI -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/usr/mysql/8.0.41/data/mysql.sock", "root", ""); for (my $n= 1; $n <= 65535; $n++) { my $sql= sprintf("SELECT * FROM d2.%05d", $n); $conn->do($sql); }' ; date
Fri Feb 14 00:59:08 GMT 2025

real    1m45.284s
user    0m0.906s
sys     0m1.410s
Fri Feb 14 01:00:53 GMT 2025

$ !ps
ps auxwwww | grep mysqld ; date
yoku0825 1738789 11.1  7.2 3027288 1161812 pts/0 Sl   00:49   1:42 /usr/mysql/8.0.41/bin/mysqld --basedir=/usr/mysql/8.0.41 --datadir=/usr/mysql/8.0.41/data --plugin-dir=/usr/mysql/8.0.41/lib/plugin --server-id=1080 --default-authentication-plugin=mysql_native_password --mysqlx=off --early-plugin-load=keyring_file.so --loose-keyring-file-data=/usr/mysql/8.0.41/keyring --admin-address=127.0.0.1 --admin-port=63080 --create-admin-listener-thread=ON --loose-innodb-print-ddl-logs --innodb-validate-tablespace-paths=OFF --log-error=error.log --pid-file=/usr/mysql/8.0.41/data/mysql.pid --socket=/usr/mysql/8.0.41/data/mysql.sock --port=64080
yoku0825 1739879  0.0  0.0   9292  1092 pts/0    S+   01:04   0:00 grep --color=auto mysqld
Fri Feb 14 01:04:25 GMT 2025

3回目。

$ date ; time perl -MDBI -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/usr/mysql/8.0.41/data/mysql.sock", "root", ""); for (my $n= 1; $n <= 65535; $n++) { my $sql= sprintf("SELECT * FROM d2.%05d", $n); $conn->do($sql); }' ; date
Fri Feb 14 01:04:47 GMT 2025

real    1m47.792s
user    0m0.776s
sys     0m1.589s
Fri Feb 14 01:06:35 GMT 2025

$ !ps
ps auxwwww | grep mysqld ; date
yoku0825 1738789 13.2  7.2 3027288 1162340 pts/0 Sl   00:49   2:26 /usr/mysql/8.0.41/bin/mysqld --basedir=/usr/mysql/8.0.41 --datadir=/usr/mysql/8.0.41/data --plugin-dir=/usr/mysql/8.0.41/lib/plugin --server-id=1080 --default-authentication-plugin=mysql_native_password --mysqlx=off --early-plugin-load=keyring_file.so --loose-keyring-file-data=/usr/mysql/8.0.41/keyring --admin-address=127.0.0.1 --admin-port=63080 --create-admin-listener-thread=ON --loose-innodb-print-ddl-logs --innodb-validate-tablespace-paths=OFF --log-error=error.log --pid-file=/usr/mysql/8.0.41/data/mysql.pid --socket=/usr/mysql/8.0.41/data/mysql.sock --port=64080
yoku0825 1739938  0.0  0.0   9292  1152 pts/0    S+   01:07   0:00 grep --color=auto mysqld
Fri Feb 14 01:07:31 GMT 2025

2回目3回目は 606 table / secくらい。メモリは0.6%増で90MBくらい。

……というのをパラメータを変えながら延々試していた。

1つだけハマったのが、 table_open_cache=100000 と table_open_cache=200000 の時にだけ何故かメモリ使用量が逆転する状況になって、なんでかと頭を捻ってたら

$ grep table_open_cache /usr/mysql/8.0.41/data/error.log
2025-02-14T03:07:36.786684-00:00 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 200000)

MySQLが自動調整していました。

​

gh-ostの-postpone-cut-over-flag-fileみたいなことをpt-oscでもやりたい

この記事は MySQL Advent Calendar 2024 の24日目の記事です。

昨日は updraftさん の 今日は、MySQL 8.0.35で非推奨になった「SHOW PROCESSLIST」の代わりのパフォーマンススキーマを見てみるの日。 - 今日はなにの日。 でした。

---

最近、ALTER TABLEのメタデータロックの競合がちょっと話題に上がっていました（ので便乗して今年書いたスライドを載せておきます）

それでちょっと思い出したんですが、 gh-ost や pt-online-schema-change (以下、pt-osc) とメタデータロックを比較するとこんな感じになります。

type	開始時排他MDL	終了時排他MDL
ネイティブALTER TABLE(ALGORITHM=INPLACE)	取る	取る
ネイティブALTER TABLE(ALGORITHM=INSTANT)	取る	取らない
gh-ost	取らない	取る
pt-osc	3回取る	取る

pt-oscの仕組み的にトリガーを使うので、 CREATE TRIGGER のぶんだけ (INSERT Trigger, Update Trigger, Delete Triggerがそれぞれ同時に作れないので計3回） 排他MDLが必要になります。

で、ネイティブALTER TABLEにせよgh-ost, pt-oscにせよ

Native ALTER TABLEのMDLのハマりどころは「ALTERのfinalizeの時に排他MDLが必要」かつ「finalizeのタイミングが読みにくい」の合わせ技なので、pt-oscとgh-ostでfinalize（pt-oscとgh-ostではRENAME TABLEの排他MDL）を遅延させられるテクニックがたまに有効

— yoku0825 (@yoku0825) December 11, 2024

があって、開始時のMDLは「まあCtrl+Cですぐ中断できるし lock_wait_timeout でAbortさせても良い」んですが終了時のMDLは「終わる時間が完全には読みにくい」とか「終わる時間が深夜になると無理」とか「ここでタイムアウトしちゃうと作業が最初からやり直し」とかがあります。

gh-ostは（おそらく作ってる人たちがそもそもこの悩みを抱えていて） -postpone-cut-over-flag-file というのがあって、この「終了処理で排他MDLを取らないといけないステップをファイルの存在で遅延させる」仕組みがあります。

たとえば -postpone-cut-over-flag-file /tmp/flag とかやっておくと、 /tmp/flag がある間は終了処理に入らない(gh-ostを起動した時点でファイルは勝手に作られる)ので、ピークタイムを避けてゆっくり余裕がありそうな時間に rm /tmp/flag で排他MDLの処理を実行させることができます。

対して、pt-oscにはデフォルトでこの機能はない ( —pause-file だとカットオーバー以外の INSERT IGNORE INTO .. の部分も止まってしまう) ので、自分で --plugin で使える Perlスクリプト を書いてフックしてやる必要があります。

pt-oscのドキュメントにも書いてあるんですがこの形の方が人に説明しやすかったので前に書いたものがこちらです。

https://github.com/yoku0825/pt-osc-plugin/blob/main/pt-osc-plugin.pl

init(pt-osc起動時に呼ばれるフック)でフラグファイルを作ってあげて

sub init
{
  my ($self, %args)= @_;
  _logger(DEBUG, "start init");
  open(my $flag_file, ">", "/tmp/flag");
  close($flag_file);
  _logger(DEBUG, "finish init");
}

before_swap_tables(排他MDLが必要になる処理の直前)でフラグファイルをチェックしてあげれば

sub before_swap_tables
{
  my ($self, %args)= @_;
  _logger(DEBUG, "start before_swap_tables");

  while (-e "/tmp/flag")
  {
    _logger(INFO, "flag file does not exist, sleepling..");
    sleep 5;
  }

  _logger(DEBUG, "finish before_swap_tables");
}

↓pt-oscの終了（するときの排他MDL）のタイミングを調整できます。

$ PLUGIN_LOG_LEVEL=9 pt-online-schema-change --user=root --socket=/usr/mysql/8.0.40/data/mysql.sock --alter "Engine = InnoDB" D=d1
,t=t1 --execute --plugin=/tmp/pt-osc.pl

..
start after_copy_rows at pt_online_schema_change::main
finish after_copy_rows at pt_online_schema_change::main
start before_swap_tables at pt_online_schema_change::main
flag file does not exist, sleepling.. at pt_online_schema_change::main
flag file does not exist, sleepling.. at pt_online_schema_change::main
flag file does not exist, sleepling.. at pt_online_schema_change::main

「pt-oscは終了時の排他MDLが制御できなくて…」という時に試してみてください。

明日は zoosm3さん です！

​

Blackholeストレージエンジンとバイナリログと 2024

この記事は MySQL Advent Calendar 2024 の20日目の記事です。
昨日は asahideさん の Google Cloudでも使えるHeatWave MySQL でした。

---

TL;DR

• binlog_format=ROW で log_replica_updates=ON (MySQL 8.0とそれ以降のデフォルトのまま)
• ソースが BLACKHOLEストレージエンジン の場合は INSERT はバイナリログに載るが UPDATE/DELETE はバイナリログに載らない
  • これは知ってた
• ソースがInnoDBでレプリカがBLACKHOLEの場合、ソースで成立した INSERT だけでなく UPDATE/DELETE もレプリカのバイナリログに吐く
  • これうまく使えばbinlog_serverになるんじゃ？

---

まだ MySQL::Sandbox を使っている。

$ make_replication_sandbox --how_many_slaves=1 8.0.40
$ cd sandboxes/rsandbox_8_0_40/
$ ./m

CREATE TABLE d1.innodb (num int) Engine = InnoDB;
CREATE TABLE d1.myisam (num int) Engine = MyISAM;
CREATE TABLE d1.blackhole (num int) Engine = BLACKHOLE;

FLUSH BINARY LOGS;  -- 見やすくするため

INSERT INTO d1.innodb VALUES(1);
INSERT INTO d1.myisam VALUES(1);
INSERT INTO d1.blackhole VALUES(1);

UPDATE d1.innodb SET num = 2 WHERE num = 1;
UPDATE d1.myisam SET num = 2 WHERE num = 1;
UPDATE d1.blackhole SET num = 2 WHERE num = 1;

master [localhost] {msandbox} ((none)) > SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                            |
+---------+------+--------------------------------------------------------------------------------------------------------------------+
| Warning | 1870 | Row events are not logged for UPDATE statements that modify BLACKHOLE tables in row format. Table(s): 'blackhole.' |
+---------+------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

DELETE FROM d1.innodb;
DELETE FROM d1.myisam;
DELETE FROM d1.blackhole;

master [localhost] {msandbox} ((none)) > SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                            |
+---------+------+--------------------------------------------------------------------------------------------------------------------+
| Warning | 1870 | Row events are not logged for DELETE statements that modify BLACKHOLE tables in row format. Table(s): 'blackhole.' |
+---------+------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

$ mysqlbinlog -vv master/data/mysql-bin.000002  | grep '^###'
### INSERT INTO `d1`.`innodb`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `d1`.`myisam`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `d1`.`blackhole`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `d1`.`innodb`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `d1`.`myisam`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `d1`.`innodb`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `d1`.`myisam`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */

SHOW WARNINGS でも出てくる通り、BLACKHOLEストレージエンジンへの UPDATE/DELETE は無視されてバイナリログに載らない。
ソースのバイナリログに載らないから当然レプリカのバイナリログにも載らない。

$ mysqlbinlog -vv node1/data/mysql-bin.000001 | grep '^###'
### INSERT INTO `d1`.`innodb`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `d1`.`myisam`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `d1`.`blackhole`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `d1`.`innodb`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `d1`.`myisam`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `d1`.`innodb`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `d1`.`myisam`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */

仮にソースでBLACKHOLE, レプリカでInnoDBなんてことを今日日(昔は binlog_format=STATEMENT や MIXED なこともあったので UPDATE/DELETE もソースで空振りしてもバイナリログに落ちてレプリカに伝わる)やろうとすると、レプリカ側ではINSERTはされ続けるけどUPDATEやDELETEは常に伝わらないので無限に肥大化することになる。

$ ./m
CREATE TABLE d1.innodb_to_blackhole (num int) Engine = InnoDB;
FLUSH BINARY LOGS;

$ ./s
ALTER TABLE d1.innodb_to_blackhole Engine = BLACKHOLE;
FLUSH BINARY LOGS;

ところで、ソースでInnoDBにしてからレプリカをBLACKHOLEにする(昔のバッドノウハウの更に逆パターン)をすると(本当にやるなら SET SESSION sql_log_bin=OFF 推奨)

$ ./m

INSERT INTO d1.innodb_to_blackhole VALUES (1);
UPDATE d1.innodb_to_blackhole SET num = 2 WHERE num = 1;
DELETE FROM d1.innodb_to_blackhole;

$ mysqlbinlog -vv master/data/mysql-bin.000003 | grep '^###'
### INSERT INTO `d1`.`innodb_to_blackhole`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `d1`.`innodb_to_blackhole`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `d1`.`innodb_to_blackhole`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */

当然ソース(InnoDB)のバイナリログにも載るし

$ mysqlbinlog -vv node1/data/mysql-bin.000002 | grep '^###'
### INSERT INTO `d1`.`innodb_to_blackhole`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `d1`.`innodb_to_blackhole`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `d1`.`innodb_to_blackhole`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */

( д ) ﾟ ﾟ レプリカのバイナリログに載ってるー！

これを上手く使えれば、

• mysqldだけどそんなにメモリもDiskも積まないマシンを作って
• でもmysqldだからsemisyncでACKを返せて
• リアルタイムでバイナリログだけを保管できて
• 何なら CHANGE REPLICATION SOURCE TO で source_host に指定してバイナリログだけ引っ張ってこられる ( gtid_mode=ON がいいと思う)
  ようなbinlogサーバーが作れると思いませんか！！

たぶん試します。

明日は discus_hamburgさん です！

​