Open1
You can try to restart the database with innodb_force_recovery=6
Matsukura Yuki背景
こちらの記事を見て開発環境では、ALTER INSTANCE DISABLE INNODB REDO_LOG; を発行してredoを無効化していました。
しばらく快適に使っていましたが、DBがクラッシュしてしまいました。
対処
起動しようとしても起動しません。
% docker compose run db bash
bash-4.4# /entrypoint.sh
[Entrypoint] MySQL Docker Image 8.0.32-1.2.11-server
bash-4.4# /entrypoint.sh mysqld
[Entrypoint] MySQL Docker Image 8.0.32-1.2.11-server
[Entrypoint] Starting MySQL 8.0.32-1.2.11-server
2023-12-28T08:14:12.943884Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2023-12-28T08:14:12.946234Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32) starting as process 40
2023-12-28T08:14:12.948070Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive
2023-12-28T08:14:12.954983Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-28T08:14:13.187496Z 1 [ERROR] [MY-013598] [InnoDB] Server was killed when InnoDB redo logging was disabled. Data files could be corrupt. You can try to restart the database with innodb_force_recovery=6
2023-12-28T08:14:13.187562Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2023-12-28T08:14:13.600510Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2023-12-28T08:14:13.600673Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-12-28T08:14:13.600700Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-12-28T08:14:13.601267Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.32) MySQL Community Server - GPL.
innodb_force_recovery=6 を [mysqld] のセクションに追加した上で起動してみます。
bash-4.4# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
bash-4.4# echo 'innodb_force_recovery = 6' >> /etc/my.cnf
bash-4.4# /entrypoint.sh mysqld
[Entrypoint] MySQL Docker Image 8.0.32-1.2.11-server
[Entrypoint] Starting MySQL 8.0.32-1.2.11-server
2023-12-28T08:15:00.739761Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2023-12-28T08:15:00.741395Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32) starting as process 85
2023-12-28T08:15:00.743063Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive
2023-12-28T08:15:00.749617Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-28T08:15:00.943273Z 1 [ERROR] [MY-013598] [InnoDB] Server was killed when InnoDB redo logging was disabled. Data files could be corrupt. You can try to restart the database with innodb_force_recovery=6
2023-12-28T08:15:00.943338Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2023-12-28T08:15:01.413446Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2023-12-28T08:15:01.413674Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-12-28T08:15:01.413713Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-12-28T08:15:01.414429Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.32) MySQL Community Server - GPL.
bash-4.4# echo 'innodb_purge_threads=0' >> /etc/my.cnf
Operating system error number 22 in a file operation. というエラーが出て起動しなくなった。。。
bash-4.4# /entrypoint.sh mysqld
[Entrypoint] MySQL Docker Image 8.0.32-1.2.11-server
[Entrypoint] Starting MySQL 8.0.32-1.2.11-server
2023-12-28T08:44:02.259836Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2023-12-28T08:44:02.261366Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32) starting as process 84
2023-12-28T08:44:02.262980Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive
2023-12-28T08:44:02.268208Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-28T08:44:02.271285Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 22 in a file operation.
2023-12-28T08:44:02.271308Z 1 [ERROR] [MY-012596] [InnoDB] Error number 22 means 'Invalid argument'
2023-12-28T08:44:02.271316Z 1 [ERROR] [MY-012646] [InnoDB] File /var/lib/mysql/mysql.sock: 'file_status_posix_stat' returned OS error 122.
2023-12-28T08:44:02.271405Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 22 in a file operation.
2023-12-28T08:44:02.271423Z 1 [ERROR] [MY-012596] [InnoDB] Error number 22 means 'Invalid argument'
2023-12-28T08:44:02.271428Z 1 [ERROR] [MY-012646] [InnoDB] File /var/lib/mysql/mysql.sock: 'file_status_posix_stat' returned OS error 122.
2023-12-28T08:44:02.414266Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-12-28T08:44:02.516439Z 1 [Warning] [MY-011018] [InnoDB] Skip updating information_schema metadata in InnoDB read-only mode.
2023-12-28T08:44:02.516480Z 1 [Warning] [MY-010005] [Server] Skip re-populating collations and character sets tables in InnoDB read-only mode.
2023-12-28T08:44:02.523757Z 2 [Warning] [MY-011018] [Server] Skip updating information_schema metadata in InnoDB read-only mode.
2023-12-28T08:44:02.524453Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode.
2023-12-28T08:44:02.524469Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode.
2023-12-28T08:44:02.524473Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode.
2023-12-28T08:44:02.524919Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2023-12-28T08:44:02.535512Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2023-12-28T08:44:02.547165Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-12-28T08:44:02.547364Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-12-28T08:44:02.604444Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-12-28T08:44:02.604563Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
DBを作り直しです。