How-to: Troubleshoot MySQL most common issues on a cPanel server

I am putting together some MySQL troubleshooting tips that may help you troubleshoot your MySQL most common issues on your cPanel server. Whether MySQL cannot be started, starts with error or is not running as expected. These tips are valid on cPanel servers. Please make backups before performing any changes and use this guide at your own direction and risk.

In this guide, HOSTNAME is the fully qualified hostname of your server. You can see your server's hostname using 'hostname -f' command. DATABASE_NAME is the name of your desired database.


Check MySQL error logs

Checking MySQL logs can shed some light on the problem and might eventually help to have it solved.

MySQL error log file is located at /var/lib/mysql/HOSTNAME.err

Code:

# tail -n 100 /var/lib/mysql/HOSTNAME.err

MySQL service status

To see if MySQL service is running, run the following command:

Code:

ps aux | grep mysql

If you need to restart MySQL service, use the following command:

Code:

/scripts/restsrtsrv_mysql

It might be helpful to keep your eyes on the error log while restarting the service using the following command:

Code:

tail -f /var/lib/mysql/HOSTNAME.err

Global options file entries

MySQL service starts by reading entries in the global options file (aka global configuration file).

It is common that MySQL does not work properly or even does not start because of the entries in the options file. Restoring option file to its default values might be a good idea in some cases.

Follow these steps to restore it to default values:

• Start by taking a backup of current option file:

Code:

cp /etc/my.cnf /backup/my.cnf.bak

• Then, edit the options file with your favorite editor (I use 'nano' here, but you may use 'vi' or any other editors):

Code:

nano /etc/my.cnf

• Comment out everything but the following lines:

Code:

[mysqld]
default-storage-engine=MyISAM
innodb_file_per_table=1
performance-schema=0
local-infile=0

To comment out each entry, simply put a hash sign ‘#’ at the beginning of that line. So, after commenting out non-default entries, your option file may look like this:

Code:

[mysqld]
default-storage-engine=MyISAM
innodb_file_per_table=1
performance-schema=0
local-infile=0
#max_allowed_packet=1073741824
#open_files_limit=50000
#max_user_connections=9
#tmp_table_size=128M
#max_heap_table_size=128M
#innodb_buffer_pool_size=12G
#innodb_log_file_size=3G
#innodb_buffer_pool_instances=12
#max_connections=160
#wait_timeout=28000
#interactive_timeout=28000
#table_open_cache=3000
#key_buffer_size=3G

• Restart MySQL to see if your issue has resolved by removing extra entries:

Code:

/scripts/restartsrv_mysql

Available disk space

MySQL can be stopped, and in some cases, tables can get corrupted if your server is running out of available disk space. We will check available disk space on the partition where MySQL is residing and make sure there is enough space available using the following command:

Code:

df -h

MySQL resides in /var/lib/mysql so if you have a separate partition for /var, you should make sure this partition has available space. Otherwise, make sure there is enough space on the / partition.

If you are running out of disk space, begin by removing some unused data to make at least a few gigabytes available and then restart MySQL service using the following command:

Code:

/scripts/restsrtsrv_mysql

To be on the safe side, especially if you are running a shared server with many databases, where checking every single database is almost impossible, I recommend repairing your databases. To repair your databases, please refer to the following section.


Repair MySQL databases

MySQL tables can be corrupted for different reasons, like running out of disk space, file-system issues, or a server crash. In most situations, you can simply repair them using free utilities.

• Repairing MySQL databases using ‘mysqlcheck’

‘mysqlcheck’ is a great utility used to repair MySQL databases while MySQL is running. So, there is no downtime and it is a safe method to repair databases.

To repair all databases using mysqlcheck:

Code:

mysqlcheck -A --auto-repair

To repair a single database using mysqlcheck:

Code:

mysqlcheck --auto-repair DATABASE_NAME

• Repairing MySQL databases using ‘myisamchk’

While ‘mysqlcheck’ is good enough for most situations, there is another utility called ‘myisamchk’ to repair MySQL databases (MyISAM tables).

First of all, we need to stop MySQL as ‘myisamchk’ can only be used when MySQL is stopped:

Code:

/scripts/restartsrv_mysql --stop

Then, navigate to damaged database’s location and run the utility:

Code:

cd /var/lib/mysql/DATABASE_NAME
myisamchk -o *.MYI

After repairing your desired databases, start MySQL:

Code:

/scripts/restartsrv_mysql

Permissions and attributes

Invalid permissions on MySQL directories can result in various errors and MySQL service may not be able to start due to that. So, we will overview MySQL-related directories permission and make sure everything looks good. You can use ‘ls -l’ command to see permission/ownership of files and directories.

• /tmp permission should be 1777 (drwxrwxrwt) with root:root ownership; otherwise, InnoDB may throw errors.
  
  
• /var/lib/mysql permission should be 751 (drwxr-x--x) with mysql:mysql ownership.

You can use ‘chmod’ and ‘chown’ commands following to fix permissions (use where necessary):

Code:

chmod 1777 /tmp
chmod 751 /var/lib/mysql
chown root:root /tmp
chown -R mysql:mysql /var/lib/mysql

MySQL can also be affected by immutable attribute. The immutable attribute is used to prevent a file or directory from being modified and placing that attribute on MySQL data directory prevents it from working properly (as MySQL needs to write into that directory). This attribute is usually placed on that directory by a systems administrator intentionally to prevent MySQL from running on the server.

To check if there is immutable attribute applied to MySQL directory run ‘lsattr’ command as follows:

Code:

lsattr /var/lib

The immutable attribute is shown as ‘i’ in the output. Following you can find some examples of the ‘lsattr’ command output.

• Examples showing no immutable attribute (Good):

Code:

--------------- /var/lib/mysql
----------I--e- /var/lib/mysql

• Output showing immutable attribute (Bad! should be fixed):

Code:

----i--------e- /var/lib/mysql
----i---------- /var/lib/mysql

To remove the immutable attribute from the MySQL directory, run the following command:

Code:

chattr -i /var/lib/mysql

Check if mysql is listening on its default port

In most cases, MySQL should be listening on its default port (3306). To verify whether it is listening on this port, simply run the following command:

Code:

lsof -i :3306

The following output is confirming that MySQL is listening on the default port (note the bold ones only):

Code:

COMMAND   PID  USER   FD   TYPE    DEVICE SIZE/OFF NODE NAME
mysqld  2587 mysql   40u  IPv6 11636747      0t0  TCP *:mysql (LISTEN)

You may need to make corrections to /etc/my.cnf if MySQL is not listening on its default port. To do so, you need to comment out the 'port=XXXX' line.


Finding slow queries

To find slow queries in MySQL, add the following line to /etc/my.cnf under [mysqld]:

Code:

slow_query_log = 1

Slow queries will then be logged in /var/lib/mysql/HOSTNAME-slow.log


Verify the hosts file

Make sure /etc/hosts contains an entry for localhost:

Code:

cat /etc/hosts

We expect to see an entry for localhost in the hosts file. Any of the following entries are good and if you see one there, you don't need to make any changes:

Code:

127.0.0.1    localhost

OR

Code:

127.0.0.1    localhost.localdomain localhost

OR

Code:

127.0.0.1    localhost localhost.localdomain

Dump databases

It is advised to create a dump of your databases whenever you are troubleshooting and applying different fixes as there are always risks of data-loss and damages to your databases.

• To dump all databases into a single file run the following command (you may change the destination to your desired backup path):

Code:

mysqldump -AER > /root/mysql_dump.sql

• If InnoDB is corrupted, you may not be able to create a dump of those databases. In this case, we will need to use repair InnoDB using the guide below:

InnoDB Corruption Repair Guide


Default MySQL paths on a cPanel server (for reference)

HOSTNAME is your servers fully qualified hostname. Use 'hostname -f' to see the hostname of your server.

• MySQL data directory: /var/lib/mysql
• MySQL PID file: /var/lib/mysql/HOSTNAME.pid
• MySQL sock file: /tmp/mysql.sock
• MySQL error log: /var/lib/mysql/HOSTNAME.err
• MySQL slow-query log: /var/lib/mysql/HOSTNAME-slow.log
• MySQL general query log: /var/lib/mysql/HOSTNAME.log

 

I am trying to add it to the original post but apparently, I cannot edit my post for some reason. It throws an error.