数据库故障恢复

2025-06-19 21:25:17
丁国栋
原创 6
摘要：记录一次mysql数据库提示升级的问题。

提示如下

❯ docker logs mysql
mysql 06:09:50.39 
mysql 06:09:50.39 Welcome to the Bitnami mysql container
mysql 06:09:50.40 Subscribe to project updates by watching https://github.com/bitnami/bitnami-docker-mysql
mysql 06:09:50.40 Submit issues and feature requests at https://github.com/bitnami/bitnami-docker-mysql/issues
mysql 06:09:50.40 
mysql 06:09:50.40 INFO  ==> ** Starting MySQL setup **
mysql 06:09:50.42 INFO  ==> Validating settings in MYSQL_*/MARIADB_* env vars
mysql 06:09:50.43 INFO  ==> Initializing mysql database
mysql 06:09:50.43 DEBUG ==> Ensuring expected directories/files exist
mysql 06:09:50.44 INFO  ==> Updating 'my.cnf' with custom configuration
mysql 06:09:50.48 INFO  ==> Setting user option
mysql 06:09:50.49 DEBUG ==> Setting user to 'root' in mysql configuration file /opt/bitnami/mysql/conf/my.cnf
mysql 06:09:50.64 INFO  ==> Setting slow_query_log option
mysql 06:09:50.64 DEBUG ==> Setting slow_query_log to '0' in mysql configuration file /opt/bitnami/mysql/conf/my.cnf
mysql 06:09:50.68 INFO  ==> Setting long_query_time option
mysql 06:09:50.68 DEBUG ==> Setting long_query_time to '10.0' in mysql configuration file /opt/bitnami/mysql/conf/my.cnf
mysql 06:09:50.72 INFO  ==> Using persisted data
mysql 06:09:50.76 INFO  ==> Running mysql_upgrade
mysql 06:09:50.77 INFO  ==> Starting mysql in background
2025-06-19T06:09:51.000224Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2025-06-19T06:09:51.000289Z 0 [Note] /opt/bitnami/mysql/bin/mysqld (mysqld 5.7.37) starting as process 71 ...
2025-06-19T06:09:51.004746Z 0 [Note] InnoDB: PUNCH HOLE support available
2025-06-19T06:09:51.004762Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2025-06-19T06:09:51.004765Z 0 [Note] InnoDB: Uses event mutexes
2025-06-19T06:09:51.004767Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2025-06-19T06:09:51.004770Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2025-06-19T06:09:51.004772Z 0 [Note] InnoDB: Using Linux native AIO
2025-06-19T06:09:51.005003Z 0 [Note] InnoDB: Number of pools: 1
2025-06-19T06:09:51.005111Z 0 [Note] InnoDB: Using CPU crc32 instructions
2025-06-19T06:09:51.022852Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2025-06-19T06:09:51.030979Z 0 [Note] InnoDB: Completed initialization of buffer pool
2025-06-19T06:09:51.033442Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2025-06-19T06:09:51.046200Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2025-06-19T06:09:51.110373Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2025-06-19T06:09:51.110432Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2025-06-19T06:09:51.238338Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2025-06-19T06:09:51.239906Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2025-06-19T06:09:51.239927Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2025-06-19T06:09:51.240878Z 0 [Note] InnoDB: 5.7.37 started; log sequence number 800163718
2025-06-19T06:09:51.241253Z 0 [Note] InnoDB: Loading buffer pool(s) from /bitnami/mysql/data/ib_buffer_pool
2025-06-19T06:09:51.241500Z 0 [Note] Plugin 'FEDERATED' is disabled.
2025-06-19T06:09:51.246093Z 0 [Note] InnoDB: Buffer pool(s) load completed at 250619  6:09:51
2025-06-19T06:09:51.254344Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2025-06-19T06:09:51.254372Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2025-06-19T06:09:51.254379Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2025-06-19T06:09:51.254383Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2025-06-19T06:09:51.255416Z 0 [Warning] CA certificate ca.pem is self signed.
2025-06-19T06:09:51.255465Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2025-06-19T06:09:51.255666Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2025-06-19T06:09:51.255696Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2025-06-19T06:09:51.255730Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2025-06-19T06:09:51.303256Z 0 [Note] Event Scheduler: Loaded 0 events
2025-06-19T06:09:51.303622Z 0 [Note] /opt/bitnami/mysql/bin/mysqld: ready for connections.
Version: '5.7.37'  socket: '/opt/bitnami/mysql/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking server version.
Running queries to upgrade MySQL server.
❯ docker logs mysql
mysql 06:09:50.39 
mysql 06:09:50.39 Welcome to the Bitnami mysql container
mysql 06:09:50.40 Subscribe to project updates by watching https://github.com/bitnami/bitnami-docker-mysql
mysql 06:09:50.40 Submit issues and feature requests at https://github.com/bitnami/bitnami-docker-mysql/issues
mysql 06:09:50.40 
mysql 06:09:50.40 INFO  ==> ** Starting MySQL setup **
mysql 06:09:50.42 INFO  ==> Validating settings in MYSQL_*/MARIADB_* env vars
mysql 06:09:50.43 INFO  ==> Initializing mysql database
mysql 06:09:50.43 DEBUG ==> Ensuring expected directories/files exist
mysql 06:09:50.44 INFO  ==> Updating 'my.cnf' with custom configuration
mysql 06:09:50.48 INFO  ==> Setting user option
mysql 06:09:50.49 DEBUG ==> Setting user to 'root' in mysql configuration file /opt/bitnami/mysql/conf/my.cnf
mysql 06:09:50.64 INFO  ==> Setting slow_query_log option
mysql 06:09:50.64 DEBUG ==> Setting slow_query_log to '0' in mysql configuration file /opt/bitnami/mysql/conf/my.cnf
mysql 06:09:50.68 INFO  ==> Setting long_query_time option
mysql 06:09:50.68 DEBUG ==> Setting long_query_time to '10.0' in mysql configuration file /opt/bitnami/mysql/conf/my.cnf
mysql 06:09:50.72 INFO  ==> Using persisted data
mysql 06:09:50.76 INFO  ==> Running mysql_upgrade
mysql 06:09:50.77 INFO  ==> Starting mysql in background
2025-06-19T06:09:51.000224Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2025-06-19T06:09:51.000289Z 0 [Note] /opt/bitnami/mysql/bin/mysqld (mysqld 5.7.37) starting as process 71 ...
2025-06-19T06:09:51.004746Z 0 [Note] InnoDB: PUNCH HOLE support available
2025-06-19T06:09:51.004762Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2025-06-19T06:09:51.004765Z 0 [Note] InnoDB: Uses event mutexes
2025-06-19T06:09:51.004767Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2025-06-19T06:09:51.004770Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2025-06-19T06:09:51.004772Z 0 [Note] InnoDB: Using Linux native AIO
2025-06-19T06:09:51.005003Z 0 [Note] InnoDB: Number of pools: 1
2025-06-19T06:09:51.005111Z 0 [Note] InnoDB: Using CPU crc32 instructions
2025-06-19T06:09:51.022852Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2025-06-19T06:09:51.030979Z 0 [Note] InnoDB: Completed initialization of buffer pool
2025-06-19T06:09:51.033442Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2025-06-19T06:09:51.046200Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2025-06-19T06:09:51.110373Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2025-06-19T06:09:51.110432Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2025-06-19T06:09:51.238338Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2025-06-19T06:09:51.239906Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2025-06-19T06:09:51.239927Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2025-06-19T06:09:51.240878Z 0 [Note] InnoDB: 5.7.37 started; log sequence number 800163718
2025-06-19T06:09:51.241253Z 0 [Note] InnoDB: Loading buffer pool(s) from /bitnami/mysql/data/ib_buffer_pool
2025-06-19T06:09:51.241500Z 0 [Note] Plugin 'FEDERATED' is disabled.
2025-06-19T06:09:51.246093Z 0 [Note] InnoDB: Buffer pool(s) load completed at 250619  6:09:51
2025-06-19T06:09:51.254344Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2025-06-19T06:09:51.254372Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2025-06-19T06:09:51.254379Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2025-06-19T06:09:51.254383Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2025-06-19T06:09:51.255416Z 0 [Warning] CA certificate ca.pem is self signed.
2025-06-19T06:09:51.255465Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2025-06-19T06:09:51.255666Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2025-06-19T06:09:51.255696Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2025-06-19T06:09:51.255730Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2025-06-19T06:09:51.303256Z 0 [Note] Event Scheduler: Loaded 0 events
2025-06-19T06:09:51.303622Z 0 [Note] /opt/bitnami/mysql/bin/mysqld: ready for connections.
Version: '5.7.37'  socket: '/opt/bitnami/mysql/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking server version.
Running queries to upgrade MySQL server.
mysql_upgrade: [ERROR] 1194: Table 'proc' is marked as crashed and should be repaired
mysql_upgrade: [ERROR] 1194: Table 'proc' is marked as crashed and should be repaired
mysql_upgrade: [ERROR] 1194: Table 'proc' is marked as crashed and should be repaired
mysql_upgrade: [ERROR] 1194: Table 'proc' is marked as crashed and should be repaired
mysql_upgrade: [ERROR] 1194: Table 'proc' is marked as crashed and should be repaired
mysql_upgrade: [ERROR] 1194: Table 'proc' is marked as crashed and should be repaired
mysql_upgrade: [ERROR] 1194: Table 'proc' is marked as crashed and should be repaired
mysql_upgrade: [ERROR] 1194: Table 'proc' is marked as crashed and should be repaired
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
The sys schema is already up to date (version 1.5.2).
Error occurred: Query against INFORMATION_SCHEMA.ROUTINES failed when checking the sys schema.
mysql 06:10:06.54 INFO  ==> Stopping mysql
2025-06-19T06:10:06.551491Z 0 [Note] Giving 0 client threads a chance to die gracefully
2025-06-19T06:10:06.551509Z 0 [Note] Shutting down slave threads
2025-06-19T06:10:06.551518Z 0 [Note] Forcefully disconnecting 0 remaining clients
2025-06-19T06:10:06.551522Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2025-06-19T06:10:06.551609Z 0 [Note] Binlog end
2025-06-19T06:10:06.574157Z 0 [Note] Shutting down plugin 'ngram'
2025-06-19T06:10:06.574179Z 0 [Note] Shutting down plugin 'partition'
2025-06-19T06:10:06.574184Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2025-06-19T06:10:06.574189Z 0 [Note] Shutting down plugin 'ARCHIVE'
2025-06-19T06:10:06.574192Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2025-06-19T06:10:06.574219Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2025-06-19T06:10:06.574223Z 0 [Note] Shutting down plugin 'MyISAM'
2025-06-19T06:10:06.574235Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2025-06-19T06:10:06.574239Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2025-06-19T06:10:06.574242Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2025-06-19T06:10:06.574247Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2025-06-19T06:10:06.574250Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2025-06-19T06:10:06.574253Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2025-06-19T06:10:06.574257Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2025-06-19T06:10:06.574260Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2025-06-19T06:10:06.574264Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2025-06-19T06:10:06.574267Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2025-06-19T06:10:06.574270Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2025-06-19T06:10:06.574274Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2025-06-19T06:10:06.574277Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2025-06-19T06:10:06.574281Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2025-06-19T06:10:06.574284Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2025-06-19T06:10:06.574288Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2025-06-19T06:10:06.574291Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2025-06-19T06:10:06.574295Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2025-06-19T06:10:06.574299Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2025-06-19T06:10:06.574302Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2025-06-19T06:10:06.574305Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2025-06-19T06:10:06.574309Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2025-06-19T06:10:06.574312Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2025-06-19T06:10:06.574316Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2025-06-19T06:10:06.574320Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2025-06-19T06:10:06.574324Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2025-06-19T06:10:06.574328Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2025-06-19T06:10:06.574332Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2025-06-19T06:10:06.574336Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2025-06-19T06:10:06.574339Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2025-06-19T06:10:06.574343Z 0 [Note] Shutting down plugin 'InnoDB'
2025-06-19T06:10:06.574892Z 0 [Note] InnoDB: FTS optimize thread exiting.
2025-06-19T06:10:06.575071Z 0 [Note] InnoDB: Starting shutdown...
2025-06-19T06:10:06.675347Z 0 [Note] InnoDB: Dumping buffer pool(s) to /bitnami/mysql/data/ib_buffer_pool
2025-06-19T06:10:06.675740Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 250619  6:10:06
2025-06-19T06:10:08.831910Z 0 [Note] InnoDB: Shutdown completed; log sequence number 800251120
2025-06-19T06:10:08.837291Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2025-06-19T06:10:08.837318Z 0 [Note] Shutting down plugin 'MEMORY'
2025-06-19T06:10:08.837326Z 0 [Note] Shutting down plugin 'CSV'
2025-06-19T06:10:08.837332Z 0 [Note] Shutting down plugin 'sha256_password'
2025-06-19T06:10:08.837338Z 0 [Note] Shutting down plugin 'mysql_native_password'
2025-06-19T06:10:08.837559Z 0 [Note] Shutting down plugin 'binlog'
2025-06-19T06:10:08.841610Z 0 [Note] /opt/bitnami/mysql/bin/mysqld: Shutdown complete
mysql 06:10:09.56 DEBUG ==> Waiting for mysql to unlock db files
❯
1. 为什么提示需要 upgrade？
  • 版本检查： MySQL 的服务器启动时会检查系统表的版本，与当前运行的 MySQL 版本（5.7.37）是否兼容。如果系统表版本较旧或格式不匹配（例如旧版本表结构或字段缺失），就需要运行 mysql_upgrade。
  • 表损坏： 错误日志中的信息指向 proc 表被标记为损坏（"Table 'proc' is marked as crashed"）。proc 表是 mysql 数据库中的系统表之一，用于存储存储过程相关信息。这种损坏可能是由于意外的服务器关闭、磁盘错误或其他原因导致。
  • 升级期间问题： 在执行 mysql_upgrade 时，它尝试检查和更新系统表，但由于 proc 表损坏，导致升级失败。
  • 配置问题：包管理器配置了自动更新或者人为迁移因素，导致数据库中的版本和服务端二进制版本不一样。
2. 问题分析
mysql_upgrade 的作用是确保所有系统表的结构与当前 MySQL 版本保持一致。它会执行必要的检查和修复工作，包括：
  • 更新系统表结构。
  • 修复损坏的表。
  • 检查并更新表数据。
日志中出现的 ERROR 1194 表示 proc 表处于损坏状态，而升级操作无法继续执行。

表损坏的原因可能包括：
  • 磁盘空间不足导致数据写入失败。
  • 文件系统错误。
  • MySQL 服务意外崩溃或强制终止。
  • 之前的数据迁移或导入操作异常。

3. 解决方法

  • 如果mysql server可以自行恢复的不需要特殊处理；
  • 通过备份恢复；
--

发表评论
博客分类