How to add remote MySQL user access.

Users created in MySQL should be for localhost access. In order for a user to be connected from a remote computer, the IP address must be mentioned in its user record. Removing a user access is a matter of deleting that user from the user record.

How to add remote user access

Example, user with login "developer" wants to access MySQL database at server 10.1.1.100 from a laptop at the IP address 10.1.2.23.

The network and database administrator received approval to allow any user to access remotely from the IP address 10.1.2.1 to 10.1.2.224 to the existing database name "tutorial". Here is how its done.

Step 1: Login as server administrator and ensure MySQL can accept connections from remote servers.

MySQL community , edit the file /etc/my.cnf

MariaDB community , edit the file /etc/my.cnf.d/server.cnf

Add the following line, save.

bind-address=0.0.0.0

Restart the MySQL server.

Ensure the server firewall allows access to MySQL port, where default is port 3306.

Example for Centos;

sudo firewall-cmd --new-zone=public --permanent

sudo firewall-cmd --reload

sudo firewall-cmd --permanent --zone=public --add-source=133.155.44.103

sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp

sudo firewall-cmd --reload

sudo firewall-cmd --list-all-zones

sudo firewall-cmd --get-services

Step 2: Login to MySQL database as administrator. Add login for remote user and list users.

mysql -u root -p

> CREATE USER 'developer'@'10.2.%' IDENTIFIED BY 'password';

> SELECT user,host FROM mysql.user;

Step 3: Assign login to access database

> GRANT ALL PRIVILEGES ON 'tutorial'.* to 'developer'@'10.2.%';

> FLUSH PRIVILEGES;

> SHOW GRANTS FOR 'developer'@'10.2.%';

Step 4: Monitor connection;

> SELECT user,host, command FROM information_schema.processlist;

How to connect remotely

On the remote server, run the following client command 

mysql -u developer -p -h 10.1.1.100

The MySQL client, it should have the same configuration for SSL as the server to avoid SSL issues.

How to remove remote user access

Login as database administrator and delete login of user and its host as recorded in database;

> DELETE FROM mysql.user WHERE User='developer' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

> FLUSH PRIVILEGES;