24/7/365 Support

Troubleshooting MySQL in Ubuntu

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

Help Category:

What Our Clients Say