How to troubleshoot MySQL connectivity issues

So you have setup a wordpress or drupal site and you need to access MySQL to be able to work on your database.  However, you can’t seem to connect to MySQL as you expect.  Each time you try to connect using MySQL workbench or other means, the connection simply times out.  Most likely you are hitting a MySQL permissions issue that is preventing you from connecting a MySQL client to your server.  Let’s take a look at how to troubleshoot MySQL connectivity issues.

The first thing that needs to be done, is that we need to tell the server that you are allowing privileges on the table(s) you are wanting to work with.  Use the following command to enable privileges on your database for your user.  We are using root in the example below:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '%PASSWORD%';

In the command above you will replace the root user with your user of choice if not root, as well as replace the %PASSWORD% variable with your actual password.  This tells the server to allow privileges on all resources when the above user connects with the specified password.

Another issue I have seen with MySQL is the my.cnf file causing issues.  In Ubuntu this is found in /etc/mysql.  There is a line in there that tells the MySQL configuration what the bind-address is.  By default in your config file this is probably set to 127.0.0.1.  Simply comment this line out by adding a #  in front.

Cloud connectivity

Also in working with Amazon EC2 recently, there are a few tricks that need to happen to successfully connect your MySQL workbench to your EC2 instance.  You won’t be able to connect to your MySQL server via a standard plain old TCP/IP connection.  You will need to choose to connect via TCP/IP over SSH and also use your key that was generated when you launched your EC2 instance.  Take a look at the screenshot below of how the instance needs to be configured:

A few points to note:

• Your SSH hostname will be either your public DNS name in your EC2 console or the public IP address
• Your username will be the special usernames that Amazon sets up in the instance images.  For AMI it is ec2-user and for Ubuntu it is ubuntu
• SSH Key File is the path to your key that you generated for your instance
• MySQL hostname needs to match what is in your my.cnf bind address section, or simply comment this out in that file as we mentioned above and use the internal IP address

Final Thoughts

Usually in troubleshooting MySQL connectivity issues, the issue will be in one of the areas we have mentioned above.  Also, however, never rule out firewalls and other traffic restricting devices as these always come into play.  There are so many different possibilities here though that we simply cannot cover those.  Just make sure the common port, 3306, or whichever port you have configured is able to pass traffic.