If you have a fresh install of a mysql server running on Debian (e.g., after running apt-get install mysql-server) you may find that any mysql client connections from other computers to your Debian server fail (one popular example is Kodi / XBMC, etc.). You may also find that mysql connections made from Debian itself (such as a local WordPress database install) are working fine.
One quick test is to use a mysql client on another linux machine to try to connect to the Debian mysql server. From the console of the other linux machine, use the following command (replace the x.x.x.x with the IP address of the Debian server). In this example we are trying to connect with the root user.
mysql -h x.x.x.x -u root
You may then get something like:
ERROR 2003: Can’t connect to MySQL server on ‘x.x.x.x’ (111 “Connection refused”)
By default, mysql server on Debian will bind itself to the loopback address (127.0.0.1). This setting can cause all remote connections to the server to be blocked with Error 111, even though all local connections will work fine (such as a WordPress install noted earlier). As a comparison, you can test local connections by issuing the above command directly from the Debian console, which should yield an error such as:
ERROR 1045: Access denied for user ‘root’@’localhost’ (using password: NO)
This basically confirms local connections are hitting the Debian mysql server. To enable remote connections for Debian’s mysql server, modify the mysql configuration file using these steps:
sudo nano /etc/mysql/my.cnf
- Find the line that says
bind-address 127.0.0.1 - and comment it out by inserting a hash in front of it
#bind-address 127.0.0.1 - Save and close the my.cnf file.
- Then restart the mysql server:
sudo /etc/init.d/mysql restart
Then try connecting to the Debian mysql server from another computer.