Remote Clients Cannot Connect

Many MySQL packages disable TCP/IP networking or bind MySQL to 127.0.0.1 (Loopback IP Address) by default as a security measure. Before we delve into the configuration aspect, I'd like to explain what the two directives we are going to change actually do:

• The first, skip-networking is fairly simple. It just tells MySQL to run without any of the TCP/IP networking options.
• The second, bind-address requires a little bit of background information. A given server usually has at least two networking interfaces (although this is not required) and can easily have more. The two most common are a Loopback network device and a physical Network Interface Card that allows you to communicate with the network. MySQL is commonly bound to the loopback interface by default because it makes it impossible to connect to the TCP port on the server from a remote host (bind-address must refer to a local IP address, or you will receive a fatal error and MySQL will not start). This of course is not desirable if you want to use the TCP port from a remote host, so you must remove this bind-address directive (Currently MySQL only supports one bind-address, but binds to 0.0.0.0 (every IP) if the bind-address directive is left out ).

Finding the Defaults File

To enable MySQL to listen to remote connections, you should edit your defaults file.

Common Locations for Defaults Files:
 * /etc/my.cnf                              (*nix/BSD)
 * $MYSQL_HOME/my.cnf                       (*nix/BSD) *Most Notably /etc/mysql/my.cnf
 * SYSCONFDIR/my.cnf                        (*nix/BSD)
 * WINDIR\my.ini, WINDIR\my.cnf             (Windows)
 * C:\my.ini, C:\my.cnf                     (Windows)
 * INSTALLDIR\my.ini, INSTALLDIR\my.cnf     (Windows)
(For more information about using option files, see this page)

Editing the Defaults File

Once you have located the defaults file, use a text editor to open the file and try to find lines like this under the [mysqld] section:

[mysqld]
   ...
   skip-networking
   ...
   bind-address = <some ip-address>
   ...

(The lines may not be in this order)

If you are able to locate these lines, make sure they are both commented out (prefaced with hash (#) characters), so that they look like this:

[mysqld]
   ...
   #skip-networking
   ...
   #bind-address = <some ip-address>
   ...

(Again, the order of these lines don't matter)

Save the file and restart the MySQL daemon or service.

Granting User Connections from Remote Hosts

Now that your MySQL Server installation is setup to accept connections from remote hosts, we have to add a user that is allowed to connect from something other than 'localhost' (Users in MySQL are defined as 'user'@'host', so 'chadmaynard'@'localhost' and 'chadmaynard'@'1.1.1.1' (or 'chadmaynard'@'server.domain.local') are different users that can have completely different permissions and/or passwords.

To create a new user:

• log into the MySQL command line client (or your favorite graphical client if you wish)

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.27-community-nt
  
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
mysql>

• if you are interested in viewing any existing remote users, issue the SQL statement SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';

mysql> SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';
+------------+---------------+
| User       | Host          |
+------------+---------------+
| just4fun   | 1.1.1.1       |
| root       | 192.168.100.% |
| repl       | slave         |
+------------+---------------+
3 rows in set (0.02 sec)
 
mysql>

(If you have a fresh install, it is normal for no rows to be returned)

• Now you have some decisions to make. At the heart of every grant statement you have these things:
  • list of allowed privileges
  • what database/tables these privileges apply to
  • username
  • host this user can connect from
  • and optionally a password

In #mysql it is common for people to want to create a "root" user that can connect from anywhere. To improve upon this idea, I will create a root user that can connect from anywhere on my LAN (192.168.100.0/24). This is an improvement because opening a MySQL server up to the internet and granting access to all hosts is bad practice.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.100.%' IDENTIFIED BY 'my-new-password' WITH GRANT OPTION;
Query OK, 0 rows affected (0.11 sec)
  
mysql>

(% is a wildcard)

At this point we have accomplished our goal and we have a user 'root' that can connect from anywhere on the 192.168.100.0/24 LAN.

For more information about how to use GRANT, please see the manual page.

Caveats

• if your system is running a software firewall (or behind a hardware firewall / NAT) you must allow connections destined to TCP port that MySQL runs on (by default and almost always 3306).
• to undo this change, simply uncomment the bind-address line in your defaults file.