Some MySQL installation gotchas!

I always use PostgreSQL for every project in my company, both in development and production. Recently, I have to use MySQL (version 5.1.36) in my new project because the client uses MySQL for deployment. The first step I have to do is to setup MySQL on my laptop for the project development, I’m using ArchLinux so the steps are straight-forward:

*Install MySQL with the pacman:
```
pacman -S mysql
```
Start it up:
```
/etc/rc.d/mysqld start
```

Set the root password:

/usr/bin/mysqladmin -u root password 's3cr3t'

Execute the recommended script to secure the new MySQL installation. Basically, the script will remove the test database, the anonymous users and only allow connection from localhost: /usr/bin/mysql_secure_installation

Here come the gotchas!

I open up MySQL Administrator and connect to the new ‘shiny’ server with the user ‘root’, everything is fine. I create a new user, then a new catalog and grant all the neccessary permissions for the new user to use the new catalog. I fire up Tomcat which contains my web application (Spring + Hibernate), the application creates a connection pool (c3p0) at startup to improve performance, but it failed with the error message Connection refused”. Hmm…strange! Googling for a while, I was suggested to put this into my /etc/hosts.allow the following line:

mysqld: 127.0.0.1:ALLOW

Restart my web application, now the error message is different Communications link failure …. Open up the configuration of MySQL (/etc/my.cnf), I found out that the skip-networking is on! Here is the documentation about this entry:

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!

Comment that entry (by adding the # before it) and restart the mysql server. My web application now can startup properly!

Gotchas solved.

PS: I’m totally new to MySQL, this is my way I made my application work with MySQL, if you have a better setup, I’m glad to know :)