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:
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
See also
InnoDB troubleshooting at https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html