Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'Database Discussions' started by hamidkh, Jan 22, 2018.

  1. hamidkh

    hamidkh Registered

    Joined:
    Apr 30, 2007
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    151
    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
     
    #1 hamidkh, Jan 22, 2018
    Last edited by a moderator: Jan 22, 2018
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    41,396
    Likes Received:
    1,606
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    hamidkh likes this.
  3. hamidkh

    hamidkh Registered

    Joined:
    Apr 30, 2007
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    151
    I am trying to add it to the original post but apparently, I cannot edit my post for some reason. It throws an error.
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    41,396
    Likes Received:
    1,606
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

    I've edited the post on your behalf, as you have not posted enough to have editing permissions.

    Thank you.
     
    hamidkh likes this.
Loading...

Share This Page