SOLVED mysql appears to be down after moving database

[MjrGaelic]

Centos 7.8 hyper-v
cPanel 88.0.12
MariaDB 10.3.23

I moved my mysql out of the root directory and put it in home as I was running out of space in root.

I used a method outlined by cPanelTristan linked here: How to move MySQL data directory

Uncheck Service Monitor in WHM > Service Manager and save.

/etc/init.d/mysql stop
mkdir /home/var_mysql
mv /var/lib/mysql /home/var_mysql
chown -R mysql:mysql /home/var_mysql/mysql
ln -s /home/var_mysql/mysql /var/lib/mysql
/etc/init.d/mysql start

Check the Service Monitor in WHM > Service Manager and save.

So, that all worked seemingly, and service mysql start gets the server running and my sites can connect.

Then the error emails started flooding in:

Service Name    mysql
Service Status    failed ⛔
Notification    The service “mysql” appears to be down.
Service Check Method    The system’s command to check or to restart this service failed.
Number of Restart Attempts    7
Service Check Raw Output    (XID 4hhqh8) The “mysql” service is down.

The subprocess “/usr/local/cpanel/scripts/restartsrv_mysql” reported error number 255 when it ended.
Startup Log    No startup log
Log Messages    2020-07-13 11:15:01 0 [Note] /usr/sbin/mysqld: ready for connections.
2020-07-13 10:45:34 0 [Note] /usr/sbin/mysqld: Shutdown complete
2020-07-13 8:36:43 0 [Note] /usr/sbin/mysqld: ready for connections.
2020-07-13 8:34:56 0 [Note] /usr/sbin/mysqld: Shutdown complete
2020-07-13 8:33:53 0 [Note] /usr/sbin/mysqld: ready for connections.
2020-07-13 8:32:52 0 [Note] /usr/sbin/mysqld: Shutdown complete
Memory Information    
Used    1.86 GB
Available    29.38 GB
Installed    31.24 GB

Checking the /var/lib/mysql/server.err I don't actually see any time stamped items that correspond to the failure notices.. The closest looking was this:

2020-07-13 11:15:00 0 [Note] InnoDB: Using Linux native AIO
2020-07-13 11:15:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-07-13 11:15:00 0 [Note] InnoDB: Uses event mutexes
2020-07-13 11:15:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2020-07-13 11:15:00 0 [Note] InnoDB: Number of pools: 1
2020-07-13 11:15:00 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-07-13 11:15:00 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-07-13 11:15:00 0 [Note] InnoDB: Completed initialization of buffer pool
2020-07-13 11:15:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-07-13 11:15:01 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-07-13 11:15:01 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-07-13 11:15:01 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-07-13 11:15:01 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-07-13 11:15:01 0 [Note] InnoDB: Waiting for purge to start
2020-07-13 11:15:01 0 [Note] InnoDB: 10.3.23 started; log sequence number 5991258020; transaction id 11961221
2020-07-13 11:15:01 0 [Note] InnoDB: Loading buffer pool(s) from /home/var_mysql/mysql/ib_buffer_pool
2020-07-13 11:15:01 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-07-13 11:15:01 0 [Note] Server socket created on IP: '::'.
2020-07-13 11:15:01 0 [Note] Reading of all Master_info entries succeeded
2020-07-13 11:15:01 0 [Note] Added new Master_info '' to hash table
2020-07-13 11:15:01 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.3.23-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
2020-07-13 11:15:01 9 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
2020-07-13 11:15:03 0 [Note] InnoDB: Buffer pool(s) load completed at 200713 11:15:03

When I try to restart mysql using the WHM GUI I get the following:

Waiting for “mysql” to start ………Unrecognized configuration options may have caused the MySQL startup errors. The system has attempted to auto-update your MySQL configuration file for your MySQL version. This should resolve any errors that stem from an outdated MySQL configuration file. …Unrecognized configuration options may have caused the MySQL startup errors. The system has attempted to auto-update your MySQL configuration file for your MySQL version. This should resolve any errors that stem from an outdated MySQL configuration file. …failed.

Cpanel::Exception::Services::StartError Service Status
undefined status from Cpanel::ServiceManager::Services::Mysql

Service Error
(XID 2qtg68) The “mysql” service failed to start.

Startup Log
Jul 13 11:03:01 ws.sd50.bc.ca mysqld[3046]: 2020-07-13 11:03:01 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Jul 13 11:03:01 ws.sd50.bc.ca mysqld[3046]: 2020-07-13 11:03:01 0 [Note] Plugin 'FEEDBACK' is disabled.
Jul 13 11:03:01 ws.sd50.bc.ca mysqld[3046]: 2020-07-13 11:03:01 0 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
Jul 13 11:03:01 ws.sd50.bc.ca mysqld[3046]: 2020-07-13 11:03:01 0 [ERROR] Unknown/unsupported storage engine: InnoDB
Jul 13 11:03:01 ws.sd50.bc.ca mysqld[3046]: 2020-07-13 11:03:01 0 [ERROR] Aborting
Jul 13 11:03:01 ws.sd50.bc.ca mysqld[3046]: Warning: Memory not freed: 520
Jul 13 11:03:01 ws.sd50.bc.ca systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Jul 13 11:03:01 ws.sd50.bc.ca systemd[1]: Failed to start MariaDB 10.3.23 database server.
Jul 13 11:03:01 ws.sd50.bc.ca systemd[1]: Unit mariadb.service entered failed state.
Jul 13 11:03:01 ws.sd50.bc.ca systemd[1]: mariadb.service failed.
mysql has failed. Contact your system administrator if the service does not automagically recover.

If I just service mysql start however, that works and the database fires up.

What did I break and how do I fix it so that the monitor sees the process running correctly and that the process can be started properly and automagically?

Thank you for your time.

Cheers,

Ryan

 

[cPanelLauren]

IT looks as though something is not recognizing that the datadir was moved. What's present in /etc/my.cnf

 

[MjrGaelic]

Thanks for getting back to me.

Here is my current cnf..

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

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

[mysqld]
log-error=/var/lib/mysql/ws2.sd50.bc.ca.err
performance-schema=0
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=40000
local-infile=0

 

[cPanelLauren]

Thanks! Now what's the output of the following?

mysql -u root -p -e "SELECT @@datadir;"

as well as:

stat /var/lib/mysql

 

[MjrGaelic]

Thanks again =)

Enter password:
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
[[email protected] etc]#

and..

[[email protected] etc]# stat /var/lib/mysql
  File: ‘/var/lib/mysql’ -> ‘/home/var_mysql/mysql’
  Size: 21              Blocks: 0          IO Block: 4096   symbolic link
Device: fd00h/64768d    Inode: 1027232     Links: 1
Access: (0777/lrwxrwxrwx)  Uid: (    0/    root)   Gid: (    0/    root)
Context: system_u:object_r:var_lib_t:s0
Access: 2020-07-14 08:37:31.094000000 -0700
Modify: 2020-07-13 08:36:37.884000000 -0700
Change: 2020-07-13 08:36:37.884000000 -0700
 Birth: -
[[email protected] etc]#

 

[cPanelLauren]

This is all as expected. Can you please open a ticket using the link in my signature? Once open please reply with the Ticket ID here so that we can update this thread with the resolution once the ticket is resolved.


Thanks!

 

[cPSamuelM]

Hello,

Thank you for submitting a ticket. I wanted to follow up here in the forum in case anybody following this thread is curious about the resolution.

After moving the MariaDB data directory to /home, it was necessary to disable the ProtectHome option. The following steps are required:

1) Use systemctl edit mariadb to create an override file with the following contents:

[Service]
ProtectHome=false

2) Manually edit /etc/systemd/system/mariadb.service.d/protecthome.conf to ensure it has the same contents:

[Service]
ProtectHome=false

3) Reload the daemon: systemctl daemon-reload

4) Start the service: /scripts/restartsrv_mysql --start

I hope you find this information helpful. Thank you again for contacting cPanel!