MySQL is used by many organizations and websites around the world. Often database administrators need to set up multi-user MySQL environments where different users are able to access data from different locations using different devices. In such cases, they need to create remote users to MySQL database. In this article, we will learn how to enable remote access in MySQL.
What is Remote Access?
Remote Access means when a user accesses database from a different IP address other than that of the database server. In simple terms, they are accessing database from a different machine other than the server. This machine may be a workstation, laptop or a mobile device. For example, a user accessing an office database from home desktop is a remote user. In today’s distributed working environment, with remote work on the rise, it has become essential to allow remote access to databases. Such access is commonly required in business intelligence and reporting systems.
Enable Remote Access in MySQL
There are three parts to enabling remote access in MySQL – Configure MySQL server to accept remote connections, create remote MySQL user and open firewall port 3306.
1. Configure MySQL Server
By default, MySQL Server listens to only localhost (127.0.0.1) for security reasons. We need to modify this setting so that it listens to requests from other IP addresses also. For this, open MySQL configuration file in a text editor. This file is located at /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.conf
sudo vi /etc/mysql/my.cnf
Next, locate the following line.
bind-address=127.0.0.1
In the above line, replace 127.0.0.1 with the specific remote IP address that you want your MySQL server to listen to. Or you can replace it with 0.0.0.0 so that it listens to queries from all IP addresses.
bind-address=0.0.0.0
Save and close the file. Restart MySQL server to apply changes.
sudo service mysql restart
OR
sudo systemctl restart mysql
2. Create Remote User
Next, we create a specific remote user. We have assumed that you are still logged into your MySQL console.
Run the following query to create a new user that will send requests from IP 12.23.34.45. Replace test_user and test_pass with username and password of your choice.
CREATE USER 'test_user'@'12.23.34.45' IDENTIFIED BY 'test_pass';
Run the following query to grant several permissions such as SELECT, INSERT, UPDATE, etc. to this new user.
GRANT SELECT, INSERT, UPDATE, DELETE on my_database.* to 'test_user'@'12.23.34.45';
Run the following command to apply changes.
FLUSH PRIVILEGES;
We have covered a very generic case of a remote user from a specific IP address who has multiple privileges on your database. Here are some common use cases required by organizations. You can implement them by modifying the above commands.
Often database administrators need to allow read only access for a remote user from a single IP address (e.g. 45.56.67.78). In such cases, run the following queries to create a remote user
CREATE USER 'test_user'@'45.56.67.78' IDENTIFIED BY 'test_pass';
GRANT SELECT, SHOW VIEW on my_database.* to 'test_user'@'45.56.67.78';
FLUSH PRIVILEGES;
Sometimes, you may need to allow a remote user to access your database from all IP addresses. In such cases, replace the IP address in above queries with %. Please note, do this only if you absolutely need it because it will open your system to all IPs.
CREATE USER 'test_user'@'%' IDENTIFIED BY 'test_pass';
GRANT SELECT, SHOW VIEW on my_database.* to 'test_user'@'%';
FLUSH PRIVILEGES;
In the above examples, we have given remote access to all tables of our database. If you want to give read only access to one table, then specify in your GRANT query as shown. Replace table_name with the name of your table.
CREATE USER 'test_user'@'45.56.67.78' IDENTIFIED BY 'test_pass';
GRANT SELECT, SHOW VIEW on my_database.table_name to 'test_user'@'45.56.67.78';
FLUSH PRIVILEGES;
If you want to allow access to multiple tables and databases, then run a separate GRANT query for each table and database as shown.
CREATE USER 'test_user'@'45.56.67.78' IDENTIFIED BY 'test_pass';
GRANT SELECT, SHOW VIEW on my_database1.table1_name to 'test_user'@'45.56.67.78';
GRANT SELECT, SHOW VIEW on my_database2.table2_name to 'test_user'@'45.56.67.78';
FLUSH PRIVILEGES;
3. Open Firewall Port
By default, port 3306 is blocked on all systems for remote connections, thanks to the operating system firewall on the server. This is true even if you have enabled MySQL server to accept remote connections. So you will need to modify firewall rules to open port 3306. You can do this using UFW firewall or iptables service as shown.
Using UFW
Here is the command to open port 3306 using UFW service.
sudo ufw allow from 12.23.34.45 to any port 3306
Using iptables
Here is the command to accept remote connections using iptables service.
sudo iptables -A INPUT -p tcp -s 12.23.34.45 --dport 3306 -j ACCEPT
If your server is running on cloud service providers such as AWS, Azure, Cloudflare, etc. then opening port using Linux firewall service is not enough. You need to also open the port on the firewall management panel of cloud service provider by adding inbound traffic rules for port 3306, from specific IP addresses.
4. Test Remote Access
Log in to your MySQL server from MySQL client located at 12.23.34.45. Replace server_ip with the IP address of your MySQL server. You will be prompted for password. Enter password to proceed.
mysql -u test_user -h server_ip -p
On successful login, you will see mysql> prompt.
mysql>
Now you can start running queries from remote client.
Key Points
You have to be very careful while allowing remote access to database. Here are some things to keep in mind while doing so.
- Allow remote access from specific IP address or a group of IP addresses. Do not allow remote users to access your data from all IP addresses, by mentioning % in your CREATE USER statement.
- If your users do not need remote access any more, revoke it. Similarly, if your users leave a project team or the company itself, then revoke their access. Database administrators to regularly check if remote access is needed by their users.
- Some users have a habit of sharing username and passwords. If this happens in case of remote users, it is more dangerous since it can easily fall into the hands of unknown people. So it is advisable to periodically change remote username and password.
Conclusion
In this article, we have learnt how to enable remote access in MySQL. We have learnt what is remote access and why it is needed. We have learnt how to configure the MySQL server to accept remote connections, how to create a remote user and also how to test remote access. You can customize these steps as per your requirement.
Also read:
How to Create Read Only MySQL User
How to Increase Max Connections in MySQL
How to Unpivot Table in MySQL
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.