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
MySQL service status To see if MySQL service is running, run the following command:
If you need to restart MySQL service, use the following command:
It might be helpful to keep your eyes on the error log while restarting the service using the following command:
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:
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:
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:
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:
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.
To repair a single database using mysqlcheck:
Then, navigate to damaged database"s location and run the utility:
After repairing your desired databases, start 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.
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:
The immutable attribute is shown as "i" in the output. Following you can find some examples of the "lsattr" command output.
To remove the immutable attribute from the MySQL directory, run the following command:
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:
The following output is confirming that MySQL is listening on the default port (note the bold ones only):
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]:
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:
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:
OR
OR
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.
# tail -n 100 /var/lib/mysql/HOSTNAME.err
MySQL service status To see if MySQL service is running, run the following command:
ps aux | grep mysql
If you need to restart MySQL service, use the following command:
/scripts/restsrtsrv_mysql
It might be helpful to keep your eyes on the error log while restarting the service using the following command:
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:
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):
nano /etc/my.cnf
- Comment out everything but the following lines:
[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:
[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:
/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:
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:
/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 -A --auto-repair
To repair a single database using mysqlcheck:
mysqlcheck --auto-repair DATABASE_NAME
- Repairing MySQL databases using "myisamchk"
/scripts/restartsrv_mysql --stop
Then, navigate to damaged database"s location and run the utility:
cd /var/lib/mysql/DATABASE_NAME
myisamchk -o *.MYI
After repairing your desired databases, start MySQL:
/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.
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:
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):
--------------- /var/lib/mysql
----------I--e- /var/lib/mysql
- Output showing immutable attribute (Bad! should be fixed):
----i--------e- /var/lib/mysql
----i---------- /var/lib/mysql
To remove the immutable attribute from the MySQL directory, run the following command:
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:
lsof -i :3306
The following output is confirming that MySQL is listening on the default port (note the bold ones only):
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]:
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:
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:
127.0.0.1 localhost
OR
127.0.0.1 localhost.localdomain localhost
OR
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):
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:
- 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
-
Hello, Thank you for sharing. Note that for InnoDB recovery, we provide a guide at: Thanks. 0 -
I am trying to add it to the original post but apparently, I cannot edit my post for some reason. It throws an error. 0 -
I am trying to add it to the original post but apparently, I cannot edit my post for some reason. It throws an error.
Hello, I've edited the post on your behalf, as you have not posted enough to have editing permissions. Thank you.0
Please sign in to leave a comment.
Comments
3 comments