我有一个生产环境，大约每周一次，我发现数据库需要修复和重新启动。MariaDB 数据库包含一些 InnoDB 和一些 MyISAM 表（混合使用主要是出于遗留原因，而不是考虑因素）。
- 服务器是具有 1GB RAM、40GB 磁盘空间和 1 个 CPU 的虚拟机
- 内存使用率通常为 50%，CPU 约为 20%
- 操作系统CentOS Linux 版本 7.5
- MariaDB 版本是5.5.56-MariaDB
- PHP 使用 CodeIgniter 3 框架通过数据库会话来访问数据库。
我们大约每周都会遇到一次桌面崩溃。
今天的日志中没有任何内容，直到 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
答案1
您的服务器因某种原因崩溃了。这些消息是在恢复期间出现的。进一步查看 mariadb 日志，看看崩溃前是否有消息。如果没有消息表明崩溃/安全关闭，则可能是 mariadb 因 OOM 条件而被终止。