By default, the MySQL server only accepts requests from localhost, i.e., only from the computer it's installed on. In most cases this is enough, especially since this behavior provides better protection of the server against attacks. However, sometimes you need to provide access to the databases for users from the network. For example, for a service running on another machine.
In general, we need to perform 2 actions:
a) Allow the MySQL server to listen on network interfaces (not just on 127.0.0.1)
b) Create permissions for network users.
If the first point is more or less clear, point (b) needs some explanation. The thing is that normally users are created in the form 'username'@'localhost', which lets them connect with certain privileges to certain databases, but only from localhost. These users can't connect from the network - MySQL tells them that the password or username is incorrect.
The trick is that MySQL keeps a list of users split by the machines they're allowed to connect from. So user User1 might connect from localhost with password ABCD, from address 192.168.0.1 they might be registered with password BBBCCC, and from address 192.168.1.1 - with password BCDE and only for certain databases. That is, we can specify which computers a user is allowed to connect from, what password they'll have in each case, and which databases they have privileges on. In fact, if you look inside the mysql database, you'll see that this isn't one user but 3 different users: user1@localhost, user1@192.168.0.1, and user1@192.168.1.1. That is, from MySQL's point of view these are different users, with correspondingly different privileges and passwords.
So, to get access from the network, we need to create additional users in MySQL specifying something other than localhost. You can either specify the exact IP addresses these users are allowed to connect from, or the special '%' character, which means "any computer except localhost".
Here's an example of usernames:
- username@'localhost' : can only connect from localhost
- username@'192.168.0.1' : can only connect from 192.168.0.1
- username@'%' : can connect from any computer except localhost
- username@'192.168.0.%' : can connect from any computer whose IP address starts with 192.168.0.
As you can see, for the localhost user you always need to specify it separately.
If you need to specify several IP addresses for a single user... you'll unfortunately have to add these IP addresses separately, either separated by commas, but after each comma there needs to be another IDENTIFIED BY construct.
So, let's give the MySQL server access from the network.
1) First, we need to check that the server is listening for requests on more than just localhost.
For Debian:
Open the file /etc/mysql/my.cnf and look for the bind-address line. We need it to be uncommented and to contain the value 0.0.0.0 :
# cat /etc/mysql/my.cnf | grep bind-address
bind-address = 0.0.0.0
If you changed the line, you need to restart the MySQL server:
# /etc/init.d/mysql restart
For FreeBSD:
By default, on FreeBSD the MySQL server listens on all available interfaces and nothing extra needs to be changed.
2) Now we need to add users who have access from the network. Without this, the server will listen on the network, but still won't let anyone in.
# mysql -u root -p
Password:
// MySQL welcome message
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user1'@'192.168.0.%' IDENTIFIED BY 'password1';
mysql> GRANT ALL PRIVILEGES ON mydatabase.* TO 'user2'@'%' IDENTIFIED BY 'password2';
In this code, user1 is granted full access to all databases on the server, but only from computers on the 192.168.0.* network.
The second user is allowed to log in from any computer at all (except localhost, of course), but they only have full access to the mydatabase database.
You can add other necessary users with the necessary privileges the same way.
3) Verify network access.
$ mysql -u username -p -h 192.168.0.100
Instead of 192.168.0.100, specify the IP address of the machine with the MySQL server, and instead of username - the user's name.
If the server doesn't let you in, first check whether there's a firewall in the way and whether the MySQL port, 3306, is open on that firewall.
Well, that's all there is to it.
Comments