Managing Users and Privileges - Learning MySQL - page 312

Including the --host=localhost actually has no effect, since localhost is the default
anyway. Now, let's try specifying the IP address for localhost ; this is always 127.0.0.1:
$ mysql --user=hugh --host=127.0.0.1 --password= the_password
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 47 to server version: 5.0.22-standard-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
The result is another successful connection, since localhost and 127.0.0.1 are the same
system, and MySQL matches the request for host 127.0.0.1 against the privileges for
localhost .
Now, let's try connecting to the MySQL server on ruttle from ruttle by using its IP
address:
$ mysql --user=hugh --host=192.168.1.2 --password= the_password
ERROR 1130 (): #HY000Host '192.168.1.2' is not allowed to connect to this
MySQL server
This time, the connection isn't successful. If you replace 192.168.1.2 with ruttle.invy
home.com , you'll see the same problem. Let's explore why we can't connect.
At the beginning of this section, we allowed access to the user 'hugh'@'localhost' .
That's exactly what the MySQL server is enforcing: we can only connect from the
localhost , and not from anywhere else, including from the actual IP address or domain
of the localhost machine. If you want to allow access from 192.168.1.2 (and its equiv-
alent domain name ruttle.invyhome.com ), you need to grant those privileges by creating
a new user with the username hugh and the host 192.168.1.2. Note that each username
and host pair is treated as a separate user and has its own password.
Log in to the monitor as the root user, and type:
mysql> GRANT ALL ON *.* TO 'hugh'@'192.168.1.2' IDENTIFIED BY ' the_password ';
Query OK, 0 rows affected (0.00 sec)
Now, quit the monitor and try connecting as the user hugh :
$ mysql --user=hugh --host=192.168.1.2 --password= the_password
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 50 to server version: 5.0.22-standard-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
You'll also find you can now connect using ruttle.invyhome.com in place of
192.168.1.2, as long as you've got a correctly configured domain nameserver (DNS)
setup. If you have trouble connecting to the MySQL server, refer to the checklist in
“Client Programs Can't Connect to the Server” in Chapter 2.