TL;DR
- 障害時の動作を確認するためにファイルシステムをフルにしたり
xfs_freezeをかけたいことはままある - が、MySQLの動作パーティションが分かれていない場合、本当にやると調査用のシェルまで動けなくなってしまう
- ループバックデバイスを使ってdatadirだけ起こすのが楽
↑だけで全部言い尽くしてしまった。
取り敢えずdatadirに割り当てる好きなサイズのファイルを切り出す。
[yoku0825@yoku0825-conoha work]$ dd if=/dev/zero of=./datadir bs=1M count=300
300+0 records in
300+0 records out
314572800 bytes (315 MB) copied, 0.26401 s, 1.2 GB/s
[yoku0825@yoku0825-conoha work]$ ll -h
total 301M
-rw-r--r-- 1 yoku0825 yoku0825 300M Sep 2 13:20 datadir
ファイルシステムを作ってから好きなマウントディレクトリに割り当てる。
[yoku0825@yoku0825-conoha work]$ mkfs -t xfs ./datadir
meta-data=./datadir isize=512 agcount=4, agsize=19200 blks
= sectsz=512 attr=2, projid32bit=1
= crc=1 finobt=0, sparse=0
data = bsize=4096 blocks=76800, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0 ftype=1
log =internal log bsize=4096 blocks=855, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
[yoku0825@yoku0825-conoha work]$ sudo mount ./datadir /mnt
[yoku0825@yoku0825-conoha work]$ df -h /mnt
Filesystem Size Used Avail Use% Mounted on
/dev/loop1 297M 16M 282M 6% /mnt
既存のdatadirの中身を /mnt に移してシンボリックリンクにするなり、 --datadir=/mnt で --initialize するなり、 ラジバンダリ
俺は yoku0825 というOSアカウントで mysqld を起動するので、オーナーを変えてしまっている。
[yoku0825@yoku0825-conoha work]$ sudo chown -R yoku0825. /mnt
[yoku0825@yoku0825-conoha work]$ /usr/mysql/8.4.2/bin/mysqld --no-defaults --datadir=/mnt --initialize-insecure
2024-09-02T04:21:51.223351Z 0 [System] [MY-015017] [Server] MySQL Server Initialization - start.
2024-09-02T04:21:51.223497Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
2024-09-02T04:21:51.223507Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)
2024-09-02T04:21:51.225861Z 0 [System] [MY-013169] [Server] /usr/mysql/8.4.2/bin/mysqld (mysqld 8.4.2) initializing of server in progress as process 22201
2024-09-02T04:21:51.235592Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-09-02T04:21:51.565478Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-09-02T04:21:52.610123Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2024-09-02T04:21:55.333522Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end.
[yoku0825@yoku0825-conoha work]$ /usr/mysql/8.4.2/bin/mysqld --no-defaults --datadir=/mnt --daemonize
mysqld will log errors to /mnt/yoku0825-conoha.err
mysqld is running as pid 22300
テキトーにbinlogを吐きそうな何かをしてやると
[yoku0825@yoku0825-conoha work]$ mysqlslap -S /tmp/mysql.sock -uroot --auto-generate-sql --auto-generate-sql-execute-number=10000000 --auto-generate-sql-load-type=w -c 10
[yoku0825@yoku0825-conoha work]$ df -h /mnt
Filesystem Size Used Avail Use% Mounted on
/dev/loop1 297M 297M 20K 100% /mnt
[yoku0825@yoku0825-conoha work]$ tail /mnt/yoku0825-conoha.err
2024-09-02T04:22:12.892895Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
2024-09-02T04:22:12.892905Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)
2024-09-02T04:22:13.150060Z 0 [System] [MY-010116] [Server] /usr/mysql/8.4.2/bin/mysqld (mysqld 8.4.2) starting as process 22298
2024-09-02T04:22:13.177164Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-09-02T04:22:13.436646Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-09-02T04:22:13.796358Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-09-02T04:22:13.796400Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-09-02T04:22:13.821037Z 0 [System] [MY-010931] [Server] /usr/mysql/8.4.2/bin/mysqld: ready for connections. Version: '8.4.2' socket: '/tmp/mysql.sock' port: 3306 Source distribution.
2024-09-02T04:22:13.821757Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2024-09-02T04:26:29.994586Z 27 [ERROR] [MY-000035] [Server] Disk is full writing './binlog.000001' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs. <--- これ
[yoku0825@yoku0825-conoha work]$ mysql -S /tmp/mysql.sock -uroot
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+-----------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+-----------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 341 | Waiting on empty queue | NULL |
| 19 | root | localhost | mysqlslap | Sleep | 115 | | NULL |
| 20 | root | localhost | mysqlslap | Query | 85 | waiting for handler commit | INSERT INTO t1 VALUES (1665728802,'8qB5MAR1caTvNFXB00HKShGa1uToGCMP2ZMHjpABFg6fnTioTz8pZGNQAkEJwckr9 |
| 21 | root | localhost | mysqlslap | Query | 85 | waiting for handler commit | INSERT INTO t1 VALUES (866596855,'naQuzhMt1IrZIJMkbLAKBNNKKK2sCknzI5uHeGAgQuDd5SLgpN0smODyc7qorTo1Qa |
| 22 | root | localhost | mysqlslap | Query | 85 | waiting for handler commit | INSERT INTO t1 VALUES (100669,'qnMdipW5KkXdTjGCh2PNzLoeR0527frpQDQ8uw67Ydk1K06uuNHtkxYBxT5w8plb2Bbpz |
| 23 | root | localhost | mysqlslap | Query | 85 | waiting for handler commit | INSERT INTO t1 VALUES (1137990260,'pvYr3YntZ2DoGrwWfL91bW9Epw8iO6vDuR4xrkqOe3Dum1PPEQQpwRvmO3Kg2Ftob |
| 24 | root | localhost | mysqlslap | Query | 85 | waiting for handler commit | INSERT INTO t1 VALUES (2010720737,'iLEkea9eOuLJweKJjQ3IJlW1dXTSs6dbJjJpHTRZFgkBJ4XuNQ4iCRchy51r3WQEE |
| 25 | root | localhost | mysqlslap | Query | 85 | waiting for handler commit | INSERT INTO t1 VALUES (866596855,'naQuzhMt1IrZIJMkbLAKBNNKKK2sCknzI5uHeGAgQuDd5SLgpN0smODyc7qorTo1Qa |
| 26 | root | localhost | mysqlslap | Query | 85 | waiting for handler commit | INSERT INTO t1 VALUES (100669,'qnMdipW5KkXdTjGCh2PNzLoeR0527frpQDQ8uw67Ydk1K06uuNHtkxYBxT5w8plb2Bbpz |
| 27 | root | localhost | mysqlslap | Query | 85 | waiting for handler commit | INSERT INTO t1 VALUES (1759592334,'3lkoxjtvgLu5xKHSTTtJuGE5F5QqmCcppCTmvFZScRZQgim93gSxwb24gKmIPEzEQ |
| 28 | root | localhost | mysqlslap | Query | 85 | waiting for handler commit | INSERT INTO t1 VALUES (866596855,'naQuzhMt1IrZIJMkbLAKBNNKKK2sCknzI5uHeGAgQuDd5SLgpN0smODyc7qorTo1Qa |
| 29 | root | localhost | mysqlslap | Query | 85 | waiting for handler commit | INSERT INTO t1 VALUES (1759592334,'3lkoxjtvgLu5xKHSTTtJuGE5F5QqmCcppCTmvFZScRZQgim93gSxwb24gKmIPEzEQ |
| 30 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST |
+----+-----------------+-----------+-----------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+
13 rows in set, 1 warning (0.01 sec)
とまあファイルシステムが埋まるとbinlogが吐けなくて waiting for handler commit で突き刺さることがわかります。
mysql コマンドラインクライアントでの新規接続には成功するけど SHOW BINARY LOGS が刺さったり (binlogを参照するためのmutexが競合する）、 performance_schema.processlist は読み取れる (MySQL 8.4の SHOW PROCESSLIST はこれを読む）けど information_schema.processlist は読めない (テンポラリーテーブルを作るので失敗した）とか
mysql> SELECT * FROM information_schema.processlist;
ERROR 1114 (HY000): The table '/tmp/#sql571c_22_0' is full
mysql> SELECT * FROM performance_schema.processlist;
..
14 rows in set (0.04 sec)
SHUTDOWN ステートメントは刺さるかなと思ったら刺さらずにちゃんと停止したり、 binlog_error_action はファイルシステムフルではトリガーされないのだなあと思ったり、学びはあると思います
