How to Allow MySQL User from Multiple Hosts

By default, MySQL allows you to connect only from local machines and not remote machines. Generally, even when we allow remote access, we allow a MySQL user to access from only one IP address. But sometimes you may need to allow access from multiple or all hosts to your MySQL database. Here are the steps to allow MySQL User from multiple hosts.

How to Allow MySQL User from Multiple Hosts

Here are the steps to allow MySQL User from remote hosts, under different conditions.

1. Allow MySQL User from One Host

Here is the regular command that we use to allow access to test_user from single remote IP address 54.43.32.21. Replace test_user, test_password, 54.43.32.21 with database username, password and remote IP address of your choice. We also use GRANT statement to give all privileges to the remote. You can omit/modify this statement as per your requirement.

mysql> CREATE USER 'test_user'@'54.43.32.21' IDENTIFIED BY 'test_password';
mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'test_user'@'54.43.32.21';
mysql> FLUSH PRIVILEGES;

2. Allow MySQL User from Multiple Hosts

If you want to allow the same MySQL user to access your database from multiple hosts (54.43.32.1-54.43.32.255), here are the commands. In this case, we allow access from range of IP addresses using wildcard character % in our MySQL query.

mysql> CREATE USER 'test_user'@'54.43.32.%' IDENTIFIED BY 'test_password';
mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'test_user'@'54.43.32.%';
mysql> FLUSH PRIVILEGES;

3. Allow MySQL User from All Hosts

If you want the same MySQL User to be able to access your database from all hosts, then run the following command. In this case, we use % wildcard character to specify all hosts after @ in the following queries.

mysql> CREATE USER 'test_user'@'%' IDENTIFIED BY 'test_password';
mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'test_user'@'%';
mysql> FLUSH PRIVILEGES;

In all the above 3 cases, you need to modify the my.cnf file of your MySQL database to allow remote connections. To do that, open my.cnf file in a text editor.

$ sudo vi /etc/mysql/my.cnf

Uncomment the following line by removing # at its beginning. And also set the bind-address to 0.0.0.0 to be able to connect from multiple or all IP addresses.

bind-address       = 0.0.0.0

If you want to allow access from specific IP addresses, you may mention them in place of 0.0.0.0.

Save and close the file. Restart MySQL Server to apply changes.

$ sudo service mysql restart

In this article, we have learnt how to allow connections from multiple hosts in MySQL. But it is important to point out that allowing remote connections from all hosts will make your database less secure and should be avoided if possible. It is always advisable to allow remote connection from one or multiple hosts, but not all hosts.

How to Allow MySQL User from Multiple Hosts