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.

Follow these steps to troubleshoot MySQL:

First, check if the MySQL server is running and listening for connections on the configured port:
```
$ sudo service mysql status
$ sudo netstat -pltn
```
Check MySQL logs for any error messages at /var/log/mysql.log and mysql.err.

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

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
```
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’;
```

Check the user has access to specified resources:

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

Check your firewall is not blocking connections to MySQL.
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.

Use a repair table statement to recover the crashed MyISAM table:

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

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

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