MariaDB crashes after DC incident

Hello all.
Context: this VPS is hosting 2-3 websites. Has been running smoothly, is being kept updated, a doozy.
48h ago, a DC node incident occurs. Hosting company is advised. Says they are on it. Replies an hour later that "all is fixed".

From that point on, I was unable to access CWP as it would through a bunch of errors after login screen. Had to reset the mysql root password, remove and reinstall MariaDB, did a bunch of swinging in the dark (this really isn't my cup of tea).

At this point it seems that MariaDB will start, then crash, then restart and so on. Associated issues is, I have WP sites working, others throwing "Database connection error" whether mysql is up or not. I can access CWP, although it will randomly crash (likely because mysql has crashed and is restarting). I'm at a loss here, probably at the point where anything I do will compound the problem instead of fixing it. Any insight is appreciated.

Here is the CWP config :
 

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   899  100   899    0     0  15133      0 --:--:-- --:--:-- --:--:-- 15237
===============================================
Kernel:
Linux glenlyon.unbrandedprojects.com 3.10.0-1160.88.1.el7.x86_64 #1 SMP Tue Mar 7 15:41:52 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux

CPU info:
model name      : QEMU Virtual CPU version 2.5+

Release:
CentOS Linux release 7.9.2009 (Core)

Arch:
x86_64

Virtualization Type:
kvm

Memory:
              total        used        free      shared  buff/cache   available
Mem:           3692         433        2247          11        1011        3028
Swap:          3999           0        3999

MySQL info:
mysql  Ver 15.1 Distrib 10.4.28-MariaDB, for Linux (x86_64) using readline 5.1

Disk Info:
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs        1.8G     0  1.8G   0% /dev
tmpfs           1.9G     0  1.9G   0% /dev/shm
tmpfs           1.9G  8.5M  1.8G   1% /run
tmpfs           1.9G     0  1.9G   0% /sys/fs/cgroup
/dev/vda1       1.5T   58G  1.4T   5% /
tmpfs           370M     0  370M   0% /run/user/0


#
# /etc/fstab
# Created by anaconda on Sat Sep 25 08:13:35 2021
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(, mount( and/or blkid( for more info
#
/dev/vda1 / ext4 rw,relatime,quota,usrquota,grpquota,data=ordered 0 0
/dev/vda2        swap       swap       defaults        0 0

Apache PHP info:
PHP 8.1.13 (cli) (built: Dec 24 2022 08:54:25) (NTS)
Copyright (c) The PHP Group
Zend Engine v4.1.13, Copyright (c) Zend Technologies

Apache start script check:
grep: /etc/init.d/httpd: No such file or directory

CWP Admin check:
/usr/local/cwpsrv/htdocs/admin

CWP version:
0.9.8.1152

You need MariaDB stable in order to use the server -- CWP depends on it, the Postfix mail transport needs it, Roundcube webmail needs it, and WP and other dynamic sites depend on it.

Did the host add or remove something in /etc/my.cnf ?
Did you run mysql_secure_installation ?

Here is a lightly tuned my.cnf for general purpose CMS use (WordPress, Drupal, Joomla)

Code: [Select]

[mysqld]
performance-schema=0
max_connections=100
max_user_connections=75
query_cache_size=32M
skip-external-locking
key_buffer_size=32M
table_open_cache=128
sort_buffer_size=512K
net_buffer_length=8K
read_buffer_size=256K
read_rnd_buffer_size=512K
myisam_sort_buffer_size=16M
tmp_table_size=32M
max_heap_table_size=32M
thread_cache_size=4
open_files_limit=10000
local-infile=0
max_allowed_packet=500M

# InnoDB
innodb_file_per_table=1
innodb_locks_unsafe_for_binlog=1
innodb_fast_shutdown = 1
# Recovery mode options
#innodb_force_recovery = 5
#innodb_purge_threads=0

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

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

You need MariaDB stable in order to use the server -- CWP depends on it, the Postfix mail transport needs it, Roundcube webmail needs it, and WP and other dynamic sites depend on it.

Did the host add or remove something in /etc/my.cnf ?
Did you run mysql_secure_installation ?

I did not run secure_installation. I come accross it now that you mention it. I will look into that as well as using the config you supplied in the other answer. Will report back. Appreciate the help.

Here is a lightly tuned my.cnf for general purpose CMS use (WordPress, Drupal, Joomla)

So, I did mysql_secure_installation, which went without a hitch. The .cnf content you provided don't seem to work well on my installation as MariaDB stops and won't restart.

I'm juste slightly further up s**t creek I'm afraid. Still, I can interact with CWP, then radomly it will throw a bunch of errors, recover and we do the whole loop again.

I'm at a loss as to what happened and how I could fix it...

So, this ends up being one of the scary ones: where you don't know how the problem got started and aren't sure what it was you did that solved it...

I ended up re-installing by the book using https://mariadb.com/resources/blog/installing-mariadb-10-on-centos-7-rhel-7/

It came right up and worked, fixed the instability and messed up WP sites.

This is the default example configuration of /etc/my.cnf provided by MySQL. This config file was generated by MySQL running on CentOS 7. I am running the same CentOS version as you are, but I am running MariaDB 10.6 (long term support release).

Code: [Select]

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid