Назад | Перейти на главную страницу

mysql 8 создает только один файл журнала вместо 2 (ib_logfile0)

У меня проблема с одним из моих серверов 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```