У меня есть производственная среда, где примерно раз в неделю я обнаруживаю, что базу данных необходимо восстановить и перезапустить. База данных MariaDB содержит некоторые таблицы InnoDB и некоторые MyISAM (смесь в основном по причинам устаревания, а не по каким-то причинам).
У нас случаются сбои таблиц примерно раз в неделю.
Сегодня в журнале ничего нет до 13:44, когда он начинается с очевидного перезапуска, за которым следует ряд таблиц, которые сообщаются как поврежденные:
180725 13:44:20 mysqld_safe Number of processes running now: 0
180725 13:44:20 mysqld_safe mysqld restarted
180725 13:44:21 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
180725 13:44:21 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 11977 ...
180725 13:44:21 InnoDB: The InnoDB memory heap is disabled
180725 13:44:21 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180725 13:44:21 InnoDB: Compressed tables use zlib 1.2.7
180725 13:44:21 InnoDB: Using Linux native AIO
180725 13:44:21 InnoDB: Initializing buffer pool, size = 128.0M
180725 13:44:21 InnoDB: Completed initialization of buffer pool
180725 13:44:21 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 15064581, file name ./mysql-bin.000014
180725 13:44:22 InnoDB: Waiting for the background threads to start
180725 13:44:23 Percona XtraDB (http://www.percona.com) 5.5.52-MariaDB-38.3 started; log sequence number 355063376
180725 13:44:23 [Note] Plugin 'FEEDBACK' is disabled.
180725 13:44:23 [Note] Recovering after a crash using mysql-bin
180725 13:44:23 [Note] Starting crash recovery...
180725 13:44:23 [Note] Crash recovery finished.
180725 13:44:23 [Note] Server socket created on IP: '0.0.0.0'.
180725 13:44:23 [Warning] 'proxies_priv' entry '@ root@prod3' ignored in --skip-name-resolve mode.
180725 13:44:23 [Note] Event Scheduler: Loaded 0 events
180725 13:44:23 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.56-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
180725 13:44:33 [ERROR] mysqld: Table './prod/products' is marked as crashed and should be repaired
180725 13:44:33 [Warning] Checking table: './prod/products'
180725 13:44:33 [ERROR] mysqld: Table './prod/product_images' is marked as crashed and should be repaired
180725 13:44:33 [Warning] Checking table: './prod/product_images'
180725 13:44:33 [ERROR] mysqld: Table './prod/products_linked_groups' is marked as crashed and should be repaired
180725 13:44:33 [Warning] Checking table: './prod/products_linked_groups'
180725 13:44:34 [ERROR] mysqld: Table './prod/pricer_items' is marked as crashed and should be repaired
180725 13:44:34 [Warning] Checking table: './prod/pricer_items'
180725 13:45:03 [ERROR] mysqld: Table './prod/email_queue' is marked as crashed and should be repaired
180725 13:45:03 [Warning] Checking table: './prod/email_queue'
180725 13:45:20 [ERROR] mysqld: Table './prod/stock' is marked as crashed and should be repaired
180725 13:45:20 [Warning] Checking table: './prod/stock'
180725 13:45:56 [ERROR] mysqld: Table './prod/wrpm_users' is marked as crashed and should be repaired
180725 13:45:56 [Warning] Checking table: './prod/wrpm_users'
180725 13:45:56 [ERROR] mysqld: Table './prod/users_files' is marked as crashed and should be repaired
180725 13:45:56 [Warning] Checking table: './prod/users_files'
180725 13:50:50 [ERROR] mysqld: Table './prod/faqs' is marked as crashed and should be repaired
180725 13:50:50 [Warning] Checking table: './prod/faqs'
180725 13:51:20 [ERROR] mysqld: Table './prod/orders' is marked as crashed and should be repaired
180725 13:51:20 [Warning] Checking table: './prod/orders'
180725 13:51:20 [ERROR] mysqld: Table './prod/orders_products' is marked as crashed and should be repaired
180725 13:51:20 [Warning] Checking table: './prod/orders_products'
180725 13:56:41 [Note] /usr/libexec/mysqld: Normal shutdown
Я знаю как это восстановить и использовали все следующие команды:
systemctl status mariadb
mysqlcheck --auto-repair -A -u root -p
myisamchk -o /var/lib/mysql/prod/*.MYI (used with caution!)
Мне бы хотелось помочь, так это как начать диагностировать причину проблемы. Может, в конфигурации что-то явно не так?
Конфигурационный файл /etc/my.cnf.d/server.cnf содержит:
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
# Skip reverse DNS lookup of clients
skip_name_resolve = 1
# key_buffer_size = 256M
# max_allowed_packet = 1M
# table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
wait_timeout=30
max_allowed_packet = 16M
max_connections = 100 #default is 151
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
# binary logging format - mixed recommended
binlog_format=mixed
# required unique id between 1 and 2^32 - 1
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
Ваш сервер по какой-то причине дает сбой. Эти сообщения появляются именно во время восстановления. Посмотрите дальше в журнале mariadb, чтобы увидеть, были ли сообщения до сбоя. Если нет сообщения о сбое / безопасном завершении работы, возможно, mariadb был убит из-за состояния OOM.