我的一台 mysql 服务器遇到了与 innodb 日志文件有关的问题。尽管它配置为正常情况下有 2 个日志文件,但当我启动服务器时,它只会创建其中一个。这会导致一个大问题,因为如果服务因任何原因重新启动,它将无法启动,因为它找不到日志文件中所需的内容。重命名旧的 ib_logfile0 将允许它再次启动。
此时我担心任何类型的数据损坏。服务器当前版本为:
/usr/sbin/mysqld Ver 8.0.19 for Linux on x86_64 (MySQL Community Server - GPL)
我已将以下从正在运行的实例中转储的配置(已精简)附加到附件中。谢谢!
abort-slave-event-count 0
activate-all-roles-on-login FALSE
archive ON
autocommit TRUE
automatic-sp-privileges TRUE
avoid-temporal-upgrade FALSE
back-log 151
basedir /usr/
big-tables FALSE
binlog-cache-size 32768
binlog-checksum CRC32
binlog-direct-non-transactional-updates FALSE
binlog-encryption FALSE
binlog-error-action ABORT_SERVER
binlog-expire-logs-seconds 259200
binlog-format ROW
binlog-group-commit-sync-delay 0
binlog-group-commit-sync-no-delay-count 0
binlog-gtid-simple-recovery TRUE
binlog-max-flush-queue-time 0
binlog-order-commits TRUE
binlog-rotate-encryption-master-key-at-startup FALSE
binlog-row-event-max-size 8192
binlog-row-image FULL
binlog-row-metadata MINIMAL
binlog-row-value-options
binlog-rows-query-log-events FALSE
binlog-stmt-cache-size 32768
binlog-transaction-dependency-history-size 25000
binlog-transaction-dependency-tracking COMMIT_ORDER
bulk-insert-buffer-size 8388608
collation-server utf8mb4_0900_ai_ci
completion-type NO_CHAIN
concurrent-insert AUTO
connect-timeout 10
console FALSE
create-admin-listener-thread FALSE
cte-max-recursion-depth 1000
daemonize FALSE
datadir /var/lib/mysql/
default-storage-engine InnoDB
default-table-encryption FALSE
default-tmp-storage-engine InnoDB
delay-key-write ON
delayed-insert-limit 100
delayed-insert-timeout 300
delayed-queue-size 1000
div-precision-increment 4
end-markers-in-json FALSE
enforce-gtid-consistency FALSE
eq-range-index-dive-limit 200
event-scheduler ON
expire-logs-days 0
explicit-defaults-for-timestamp TRUE
external-locking FALSE
flush FALSE
flush-time 0
gdb FALSE
general-log FALSE
gtid-executed-compression-period 1000
gtid-mode OFF
help TRUE
histogram-generation-max-mem-size 20000000
host-cache-size 279
information-schema-stats-expiry 86400
init-connect
init-file (No default value)
initialize FALSE
initialize-insecure FALSE
innodb-adaptive-flushing TRUE
innodb-adaptive-flushing-lwm 10
innodb-adaptive-hash-index TRUE
innodb-adaptive-hash-index-parts 8
innodb-adaptive-max-sleep-delay 150000
innodb-api-bk-commit-interval 5
innodb-api-disable-rowlock FALSE
innodb-api-enable-binlog FALSE
innodb-api-enable-mdl FALSE
innodb-api-trx-level 0
innodb-autoextend-increment 64
innodb-autoinc-lock-mode 2
innodb-buffer-pool-chunk-size 134217728
innodb-buffer-pool-dump-at-shutdown TRUE
innodb-buffer-pool-dump-now FALSE
innodb-buffer-pool-dump-pct 25
innodb-buffer-pool-filename ib_buffer_pool
innodb-buffer-pool-in-core-file TRUE
innodb-buffer-pool-instances 0
innodb-buffer-pool-load-abort FALSE
innodb-buffer-pool-load-at-startup TRUE
innodb-buffer-pool-load-now FALSE
innodb-buffer-pool-size 134217728
innodb-change-buffer-max-size 25
innodb-change-buffering all
innodb-checksum-algorithm crc32
innodb-cmp-per-index-enabled FALSE
innodb-commit-concurrency 0
innodb-compression-failure-threshold-pct 5
innodb-compression-level 6
innodb-compression-pad-pct-max 50
innodb-concurrency-tickets 5000
innodb-data-file-path ibdata1:12M:autoextend
innodb-data-home-dir (No default value)
innodb-deadlock-detect TRUE
innodb-dedicated-server TRUE
innodb-default-row-format dynamic
innodb-directories (No default value)
innodb-disable-sort-file-cache FALSE
innodb-doublewrite TRUE
innodb-fast-shutdown 1
innodb-file-per-table TRUE
innodb-fill-factor 100
innodb-flush-log-at-timeout 1
innodb-flush-log-at-trx-commit 2
innodb-flush-method fsync
innodb-flush-neighbors 0
innodb-flush-sync TRUE
innodb-flushing-avg-loops 30
innodb-force-load-corrupted FALSE
innodb-force-recovery 0
innodb-fsync-threshold 0
innodb-ft-aux-table (No default value)
innodb-ft-cache-size 8000000
innodb-ft-enable-diag-print FALSE
innodb-ft-enable-stopword TRUE
innodb-ft-max-token-size 84
innodb-ft-min-token-size 3
innodb-ft-num-word-optimize 2000
innodb-ft-result-cache-limit 2000000000
innodb-ft-server-stopword-table (No default value)
innodb-ft-sort-pll-degree 2
innodb-ft-total-cache-size 640000000
innodb-ft-user-stopword-table (No default value)
innodb-idle-flush-pct 100
innodb-io-capacity 200
innodb-io-capacity-max 18446744073709551615
innodb-lock-wait-timeout 50
innodb-log-buffer-size 16777216
innodb-log-checksums TRUE
innodb-log-compressed-pages TRUE
innodb-log-file-size 67108864
innodb-log-files-in-group 2
innodb-log-group-home-dir (No default value)
innodb-log-spin-cpu-abs-lwm 80
innodb-log-spin-cpu-pct-hwm 50
innodb-log-wait-for-flush-spin-hwm 400
innodb-log-write-ahead-size 8192
innodb-lru-scan-depth 1024
innodb-max-dirty-pages-pct 90
innodb-max-dirty-pages-pct-lwm 10
innodb-max-purge-lag 0
innodb-max-purge-lag-delay 0
innodb-max-undo-log-size 1073741824
innodb-monitor-disable (No default value)
innodb-monitor-enable (No default value)
innodb-monitor-reset (No default value)
innodb-monitor-reset-all (No default value)
innodb-numa-interleave FALSE
innodb-old-blocks-pct 37
innodb-old-blocks-time 1000
innodb-online-alter-log-max-size 134217728
innodb-open-files 0
innodb-optimize-fulltext-only FALSE
innodb-page-cleaners 4
innodb-page-size 16384
innodb-parallel-read-threads 4
innodb-print-all-deadlocks FALSE
innodb-print-ddl-logs FALSE
innodb-purge-batch-size 300
innodb-purge-rseg-truncate-frequency 128
innodb-purge-threads 4
innodb-random-read-ahead FALSE
innodb-read-ahead-threshold 56
innodb-read-io-threads 4
innodb-read-only FALSE
innodb-redo-log-archive-dirs (No default value)
innodb-redo-log-encrypt FALSE
innodb-replication-delay 0
innodb-rollback-on-timeout FALSE
innodb-rollback-segments 128
innodb-sort-buffer-size 1048576
innodb-spin-wait-delay 6
innodb-spin-wait-pause-multiplier 50
innodb-stats-auto-recalc TRUE
innodb-stats-include-delete-marked FALSE
innodb-stats-method nulls_equal
innodb-stats-on-metadata FALSE
innodb-stats-persistent TRUE
innodb-stats-persistent-sample-pages 20
innodb-stats-transient-sample-pages 8
innodb-status-file FALSE
innodb-status-output FALSE
innodb-status-output-locks FALSE
innodb-strict-mode TRUE
innodb-sync-array-size 1
innodb-sync-spin-loops 30
innodb-table-locks TRUE
innodb-temp-data-file-path ibtmp1:12M:autoextend
innodb-temp-tablespaces-dir (No default value)
innodb-thread-concurrency 0
innodb-thread-sleep-delay 10000
innodb-tmpdir (No default value)
innodb-undo-directory (No default value)
innodb-undo-log-encrypt FALSE
innodb-undo-log-truncate TRUE
innodb-undo-tablespaces 2
innodb-use-native-aio TRUE
innodb-write-io-threads 4
internal-tmp-mem-storage-engine TempTable
join-buffer-size 262144
keep-files-on-create FALSE
key-buffer-size 8388608
key-cache-age-threshold 300
key-cache-block-size 1024
key-cache-division-limit 100
language /usr/share/mysql-8.0/
large-pages FALSE
lc-messages en_US
lc-messages-dir /usr/share/mysql-8.0/
lc-time-names en_US
local-infile FALSE
lock-wait-timeout 31536000
log-bin binlog
log-bin-index binlog.index
log-bin-trust-function-creators FALSE
log-bin-use-v1-row-events FALSE
log-error stderr
log-error-services log_filter_internal; log_sink_internal
log-error-suppression-list
log-error-verbosity 1
log-isam myisam.log
log-output FILE
log-queries-not-using-indexes FALSE
log-raw FALSE
log-short-format FALSE
log-slave-updates TRUE
log-slow-admin-statements FALSE
log-slow-extra FALSE
log-slow-slave-statements FALSE
log-statements-unsafe-for-binlog TRUE
log-tc tc.log
log-tc-size 24576
log-throttle-queries-not-using-indexes 0
log-timestamps UTC
long-query-time 10
low-priority-updates FALSE
lower-case-table-names 0
mandatory-roles
master-info-file master.info
master-info-repository TABLE
master-retry-count 86400
master-verify-checksum FALSE
max-allowed-packet 67108864
max-binlog-cache-size 18446744073709547520
max-binlog-dump-events 0
max-binlog-size 1073741824
max-binlog-stmt-cache-size 18446744073709547520
max-connect-errors 100
max-connections 151
max-delayed-threads 20
max-digest-length 1024
max-error-count 1024
max-execution-time 0
max-heap-table-size 16777216
max-join-size 18446744073709551615
max-length-for-sort-data 4096
max-points-in-geometry 65536
max-prepared-stmt-count 16382
max-relay-log-size 0
max-seeks-for-key 18446744073709551615
max-sort-length 1024
max-write-lock-count 18446744073709551615
memlock FALSE
min-examined-row-limit 0
myisam-block-size 1024
myisam-data-pointer-size 6
myisam-max-sort-file-size 9223372036853727232
myisam-mmap-size 18446744073709551615
myisam-recover-options OFF
myisam-repair-threads 1
myisam-sort-buffer-size 8388608
myisam-stats-method nulls_unequal
myisam-use-mmap FALSE
mysql-native-password-proxy-users FALSE
mysqlx ON
mysqlx-cache-cleaner ON
mysqlx-compression-algorithms DEFLATE_STREAM,LZ4_MESSAGE,ZSTD_STREAM
mysqlx-connect-timeout 30
mysqlx-document-id-unique-prefix 0
mysqlx-enable-hello-notice TRUE
mysqlx-idle-worker-thread-timeout 60
mysqlx-interactive-timeout 28800
mysqlx-max-allowed-packet 67108864
mysqlx-max-connections 100
mysqlx-min-worker-threads 2
mysqlx-port 33060
mysqlx-port-open-timeout 0
mysqlx-read-timeout 30
mysqlx-socket (No default value)
mysqlx-wait-timeout 28800
mysqlx-write-timeout 60
net-buffer-length 16384
net-read-timeout 30
net-retry-count 10
net-write-timeout 60
new FALSE
ngram ON
ngram-token-size 2
old FALSE
old-alter-table FALSE
old-style-user-limits FALSE
open-files-limit 8161
pid-file /var/run/mysqld/mysqld.pid
plugin-dir /usr/lib/mysql/plugin/
preload-buffer-size 32768
profiling-history-size 15
query-alloc-block-size 8192
query-prealloc-size 8192
range-alloc-block-size 4096
range-optimizer-max-mem-size 8388608
read-buffer-size 131072
read-only FALSE
read-rnd-buffer-size 262144
regexp-stack-limit 8000000
regexp-time-limit 32
require-secure-transport FALSE
rpl-read-size 8192
rpl-stop-slave-timeout 31536000
safe-user-create FALSE
schema-definition-cache 256
secondary-engine-cost-threshold 100000
secure-file-priv /var/lib/mysql-files
server-id 1
server-id-bits 32
session-track-transaction-info OFF
show-create-table-verbosity FALSE
show-old-temporals FALSE
show-slave-auth-info FALSE
skip-grant-tables FALSE
skip-name-resolve FALSE
skip-networking FALSE
skip-show-database FALSE
skip-slave-start FALSE
slow-launch-time 2
socket /var/run/mysqld/mysqld.sock
sort-buffer-size 262144
sporadic-binlog-dump-fail FALSE
sql-mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
sql-require-primary-key FALSE
stored-program-cache 256
stored-program-definition-cache 256
super-large-pages FALSE
super-read-only FALSE
symbolic-links FALSE
sync-binlog 1
sync-master-info 10000
sync-relay-log 10000
sync-relay-log-info 10000
sysdate-is-now FALSE
table-definition-cache 2000
table-encryption-privilege-check FALSE
table-open-cache 4000
table-open-cache-instances 16
tablespace-definition-cache 256
tc-heuristic-recover OFF
temptable-max-ram 1073741824
temptable-use-mmap TRUE
thread-cache-size 9
thread-handling one-thread-per-connection
thread-stack 286720
tmp-table-size 16777216
tmpdir /tmp
transaction-alloc-block-size 8192
transaction-isolation REPEATABLE-READ
transaction-prealloc-size 4096
updatable-views-with-limit YES
upgrade AUTO
validate-config FALSE
windowing-use-high-precision TRUE
错误日志:
2020-03-04T12:22:44.645032Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19) starting as process 1642
2020-03-04T12:22:44.688308Z 0 [Warning] [MY-012360] [InnoDB] Option innodb_dedicated_server is ignored for innodb_log_file_size because innodb_log_file_size=67108864 is specified explicitly.
2020-03-04T12:22:45.087351Z 1 [ERROR] [MY-012961] [InnoDB] Only one log file found
2020-03-04T12:22:45.087663Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error not found.
2020-03-04T12:22:45.578090Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2020-03-04T12:22:45.578608Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-03-04T12:22:45.579056Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-03-04T12:22:45.584321Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19) MySQL Community Server - GPL.
2020-03-04T12:23:42.214245Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19) starting as process 1851
2020-03-04T12:23:42.234222Z 0 [Warning] [MY-012360] [InnoDB] Option innodb_dedicated_server is ignored for innodb_log_file_size because innodb_log_file_size=67108864 is specified explicitly.
2020-03-04T12:23:42.458195Z 1 [ERROR] [MY-012961] [InnoDB] Only one log file found
2020-03-04T12:23:42.458462Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error not found.
2020-03-04T12:23:42.952511Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2020-03-04T12:23:42.953093Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-03-04T12:23:42.953603Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-03-04T12:23:42.955514Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19) MySQL Community Server - GPL.
2020-03-04T12:25:47.602032Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19) starting as process 1978
2020-03-04T12:25:47.623263Z 0 [Warning] [MY-012360] [InnoDB] Option innodb_dedicated_server is ignored for innodb_log_file_size because innodb_log_file_size=67108864 is specified explicitly.
2020-03-04T12:25:54.492113Z 4 [System] [MY-013381] [Server] Server upgrade from '80018' to '80019' started.
2020-03-04T12:26:04.788132Z 4 [System] [MY-013381] [Server] Server upgrade from '80018' to '80019' completed.
2020-03-04T12:26:24.417982Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-03-04T12:26:24.474922Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.19' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-03-04T12:26:24.566369Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060```