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 all databases using mysqlcheck:
To repair a single database using mysqlcheck:
First of all, we need to stop MySQL as ‘myisamchk’ can only be used when MySQL is stopped:
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.
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.
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.errMySQL service status
To see if MySQL service is running, run the following command:
Code:
ps aux | grep mysql
Code:
/scripts/restsrtsrv_mysql
Code:
tail -f /var/lib/mysql/HOSTNAME.errGlobal 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
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_mysqlAvailable 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 -hIf 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_mysqlRepair 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’
To repair all databases using mysqlcheck:
Code:
mysqlcheck -A --auto-repair
Code:
mysqlcheck --auto-repair DATABASE_NAME- Repairing MySQL databases using ‘myisamchk’
First of all, we need to stop MySQL as ‘myisamchk’ can only be used when MySQL is stopped:
Code:
/scripts/restartsrv_mysql --stop
Code:
cd /var/lib/mysql/DATABASE_NAME
myisamchk -o *.MYI
Code:
/scripts/restartsrv_mysqlPermissions 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.
Code:
chmod 1777 /tmp
chmod 751 /var/lib/mysql
chown root:root /tmp
chown -R mysql:mysql /var/lib/mysqlTo check if there is immutable attribute applied to MySQL directory run ‘lsattr’ command as follows:
Code:
lsattr /var/lib- 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
Code:
chattr -i /var/lib/mysqlCheck 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
Code:
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 2587 mysql 40u IPv6 11636747 0t0 TCP *:mysql (LISTEN)Finding slow queries
To find slow queries in MySQL, add the following line to /etc/my.cnf under [mysqld]:
Code:
slow_query_log = 1Verify the hosts file
Make sure /etc/hosts contains an entry for localhost:
Code:
cat /etc/hosts
Code:
127.0.0.1 localhost
Code:
127.0.0.1 localhost.localdomain localhost
Code:
127.0.0.1 localhost localhost.localdomainDump 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:
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
Last edited by a moderator:
