Troubleshooting MySQL

In this recipe, we will look at some common problems with MySQL and learn how to solve them.

Getting ready

You will need access to a root account or an account with sudo privileges.

You will need administrative privileges on the MySQL server.

How to do it…

Follow these steps to troubleshoot MySQL:

1. First, check if the MySQL server is running and listening for connections on the configured port:

   $ sudo service mysql status
   $ sudo netstat -pltn
   

2. Check MySQL logs for any error messages at /var/log/mysql.log and mysql.err.
3. You can try to start the server in interactive mode with the verbose flag set:

   $ which mysqld
   /usr/sbin/mysqld
   $ sudo /usr/sbin/mysqld --user=mysql --verbose
   

4. If you are accessing MySQL from a remote system, make sure that the server is set to listen on a public port. Check for bind-address in my.cnf:

   bind-address = 10.0.247.168
   

5. For any access denied errors, check if you have a user account in place and if it is allowed to log in from a specific IP address:

   mysql> select user, host, password from mysql.user where user = ‘username’;
   

6. Check the user has access to specified resources:

   mysql > grant all privileges on databasename.* to ‘username’@’%’;
   

7. Check your firewall is not blocking connections to MySQL.
8. If you get an error saying mysql server has gone away, then increase wait_timeout in the configuration file. Alternatively, you can re-initiate a connection on the client side after a specific timeout.
9. Use a repair table statement to recover the crashed MyISAM table:

   $ mysql -u root -p
   mysql> repair table databasename.tablename;
   

10. Alternatively, you can use the mysqlcheck command to repair tables:

    $ mysqlcheck -u root -p --auto-repair \
    --check --optimize databasename
    

See also

• InnoDB troubleshooting at https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html