Из за чего могут возникать ошибки в базе данных?

Question

[Chaly95]

Столкнулся с такой проблеммой
CMS сайта Wordpress, расположен на VPS
Ночью пропала большая часть информации с сайта. Я скачал логи ошибок базы данных.
И при просмотре обнаружил такую ошибку
1. Сервер базы данных перезагружался
2. После перезагруки такая ошибка
[ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed и такие ошибки продолжались до того момента пока я не восстановил резервную копию

Часть файла логов:

Момент перезагрузки базы

191204 22:42:20 [Warning] Checking table: './newpipl/dhoz_options'
191204 22:42:20 [ERROR] mysqld: Table './piplkr/dhoz_posts' is marked as crashed and should be repaired
191204 22:42:20 [Warning] Checking table: './piplkr/dhoz_posts'
191204 22:42:20 [ERROR] mysqld: Table './piplkr/dhoz_postmeta' is marked as crashed and should be repaired
191204 22:42:20 [Warning] Checking table: './piplkr/dhoz_postmeta'
191204 22:42:20 [ERROR] mysqld: Table './newpipl/dhoz_woocommerce_sessions' is marked as crashed and should be repaired
191204 22:42:20 [Warning] Checking table: './newpipl/dhoz_woocommerce_sessions'
191204 22:42:20 [ERROR] mysqld: Table './piplkr/dhoz_options' is marked as crashed and should be repaired
191204 22:42:20 [Warning] Checking table: './piplkr/dhoz_options'
191204 22:42:21 [ERROR] mysqld: Table './piplkr/dhoz_term_relationships' is marked as crashed and should be repaired
191204 22:42:21 [Warning] Checking table: './piplkr/dhoz_term_relationships'
191204 22:42:21 [ERROR] mysqld: Table './newpipl/dhoz_term_taxonomy' is marked as crashed and should be repaired
191204 22:42:21 [ERROR] mysqld: Table './newpipl/dhoz_posts' is marked as crashed and should be repaired
191204 22:42:21 [Warning] Checking table: './newpipl/dhoz_posts'
191204 22:42:21 [Warning] Checking table: './newpipl/dhoz_term_taxonomy'
191204 22:42:21 [ERROR] mysqld: Table './newpipl/dhoz_term_relationships' is marked as crashed and should be repaired
191204 22:42:21 [Warning] Checking table: './newpipl/dhoz_term_relationships'
191204 22:42:21 [ERROR] mysqld: Table './newpipl/dhoz_wc_product_meta_lookup' is marked as crashed and should be repaired
191204 22:42:21 [Warning] Checking table: './newpipl/dhoz_wc_product_meta_lookup'
191204 22:42:21 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and should be repaired
191204 22:42:21 [Warning] Checking table: './newpipl/dhoz_postmeta'
191204 22:42:26 [ERROR] mysqld: Table './newpipl/dhoz_usermeta' is marked as crashed and should be repaired
191204 22:42:26 [Warning] Checking table: './newpipl/dhoz_usermeta'
191204 22:42:26 [ERROR] mysqld: Table './community/wp_options' is marked as crashed and should be repaired
191204 22:42:26 [Warning] Checking table: './community/wp_options'
191204 22:42:26 [ERROR] Got an error from unknown thread, /builddir/build/BUILD/mariadb-5.5.60/storage/myisam/ha_myisam.cc:936
191204 22:42:26 [Warning] Recovering table: './newpipl/dhoz_postmeta'
191204 22:42:27 [ERROR] mysqld: Table './community/wp_postmeta' is marked as crashed and should be repaired
191204 22:42:27 [Warning] Checking table: './community/wp_postmeta'
191204 22:42:27 [Note] Retrying repair of: './newpipl/dhoz_postmeta' including modifying data file
191204 22:42:29 [ERROR] mysqld: Table './newpipl/dhoz_users' is marked as crashed and should be repaired
191204 22:42:29 [Warning] Checking table: './newpipl/dhoz_users'
191204 22:42:47 mysqld_safe Number of processes running now: 0
191204 22:42:47 mysqld_safe mysqld restarted
191204 22:42:47 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 7987 ...
191204 22:42:47 InnoDB: The InnoDB memory heap is disabled
191204 22:42:47 InnoDB: Mutexes and rw_locks use GCC atomic builtins
191204 22:42:47 InnoDB: Compressed tables use zlib 1.2.7
191204 22:42:47 InnoDB: Using Linux native AIO
191204 22:42:47 InnoDB: Initializing buffer pool, size = 256.0M
InnoDB: mmap(275513344 bytes) failed; errno 12
191204 22:42:47 InnoDB: Completed initialization of buffer pool
191204 22:42:47 InnoDB: Fatal error: cannot allocate memory for the buffer pool
191204 22:42:47 [ERROR] Plugin 'InnoDB' init function returned error.
191204 22:42:47 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
191204 22:42:47 [Note] Plugin 'FEEDBACK' is disabled.
191204 22:42:47 [ERROR] Unknown/unsupported storage engine: InnoDB
191204 22:42:47 [ERROR] Aborting

191204 22:42:47 [Note] /usr/libexec/mysqld: Shutdown complete

После перезагруки такая ошибка, после этой ошибки пропала часть данных

191204 22:42:47 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
191204 22:45:01 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
191204 22:45:01 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 8423 ...
191204 22:45:01 InnoDB: The InnoDB memory heap is disabled
191204 22:45:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins
191204 22:45:01 InnoDB: Compressed tables use zlib 1.2.7
191204 22:45:01 InnoDB: Using Linux native AIO
191204 22:45:01 InnoDB: Initializing buffer pool, size = 256.0M
191204 22:45:01 InnoDB: Completed initialization of buffer pool
191204 22:45:01 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...
191204 22:45:01 InnoDB: Waiting for the background threads to start
191204 22:45:02 Percona XtraDB (http://www.percona.com) 5.5.59-MariaDB-38.11 started; log sequence number 444653173
191204 22:45:02 [Note] Plugin 'FEEDBACK' is disabled.
191204 22:45:02 [Note] Server socket created on IP: '127.0.0.1'.
191204 22:45:02 [Note] Event Scheduler: Loaded 0 events
191204 22:45:02 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.60-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
191204 22:45:03 [ERROR] mysqld: Table './newpipl/dhoz_options' is marked as crashed and should be repaired
191204 22:45:03 [Warning] Checking table: './newpipl/dhoz_options'
191204 22:45:05 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:05 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:06 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:07 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:08 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed
191204 22:45:08 [ERROR] mysqld: Table './newpipl/dhoz_postmeta' is marked as crashed and last (automatic?) repair failed

Прошу помощи у более опытных товарищей)

Answer 1

[Melkij]

myisam же. Так же известный как бинарная куча нетранзакционного мусора. Это для него нормально и вполне ожидаемо.

Answer 2

[Vitaly Karasik]

Посмотрите на системные логи - возможно поймем почему перезагрузился сервер.
Плюс покажите логи базы перед перезагрузкой.
В принципе любая база может упасть с потерей данных, так что бэкапы важны.
Как уменьшить вероятность потери - обновить MySQL на современную версию (5.5.60 очень старая), плюс можно сконвертировать таблицы в InnoDB.

Comments

[Chaly95]

Спасибо)

[Chaly95]

перезагрузку сервера вызвала ошибка Fatal error: cannot allocate memory for the buffer pool.

[Vitaly Karasik]

Значит, недостаточно RAM для mysql.
Сколько памяти на сервере и что в my.cnf ?

[Chaly95]

Vitaly Karasik, Я поднял до 256 мб было 128 мб. Но все равно таже ошибка на сервере 8 гиг

[Vitaly Karasik]

Chaly95, нет-нет, наоборот - MySQL требует больше памяти чем доступно на сервере.
покажите my.cnf - наверно, есть еще настройки которые требуют память.

[Chaly95]

Vitaly Karasik,

[mysqld]
innodb_buffer_pool_size = 556M
collation-server = utf8_general_ci
character-set-server = utf8
local-infile=0
innodb_file_per_table = 1
pid-file = /var/run/mysqld/mysqld.pid
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
bind-address = 127.0.0.1
query-cache-limit = 60M
query-cache-size = 768M
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[Vitaly Karasik]

странно...
а на этом сервере только база бежит или вебсервер тоже?

[Chaly95]

Vitaly Karasik, И вебсервер тоже((

[Vitaly Karasik]

Я так и догадался. Значит вебсервер сожрал память.
Я вижу два пути улучшить ситуацию, я голосую за первый:
- передвинуть базу на отдельный сервер. Например, за те же деньги взять два сервера по 4ГБ.
Тогда даже если вебсервер будет перегружен и упадет, база будет в порядке
- тьюнить версервер чтобы не жрал слишком много памяти

[Chaly95]

Vitaly Karasik, Спасибо!)