Installing MySQL server and using it from the same host is a very common practice by Server Administrators. However, it's highly recommended that we should separate the MySQL server and client, the MySQL server should be accessed from a remote host. This method helps the system become more secure and increases performance.
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform related MySQL queries.
In this context, we shall look into how to configure the MySQL server to allow remote connection from its client.
Steps to Connect to a MySQL database remotely
In order to perform these steps, ensure that you log in as the root MySQL user and do the following.
1. Configure the MySQL server
To begin, we will configure the MySQL server to listen on a pre-defined IP or all IP addresses on your server.
If the MySQL server is in the same network as the clients, you should configure the listen port on the private IP.
On another hand, if you need to connect to the MySQL server through a public network, let's configure the server.
You should Open the configuration file by running the below command:
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnfThen, Go to the line that begins with bind-address.
By default, the value of bind-address is 127.0.0.1.
If you want to set the MySQL server to listen on all of the IP addresses, configure the value of bind-address = 0.0.0.0
To make the change effect, let’s restart the MySQL service:
$ sudo systemctl restart mysql2. Grant access to a user from remote hosts
Here, you will see how to allow access to a database to remote users.
To do this, start by Logging in to the MySQL server with the command:
$ sudo mysqlThen run the below query:
mysql> GRANT ALL ON DATABASE-NAME.* TO username@IP-ADDRESS IDENTIFIED BY 'USER-PASSWORD';For example:
mysql> GRANT ALL ON linuxapt.* TO user@10.0.0.5 IDENTIFIED BY 'mysecuredpass';3. Configure Firewall
The MySQL server listens on port 3306. You have to open this port to allow traffic from the remote hosts. You can use one of these below methods:
When using iptables, run the below command:
$ sudo iptables -A INPUT -p tcp --destination-port 3306 -j ACCEPTWhen using ufw, open port 3306 by simply running the below command:
$ sudo ufw allow 3306/tcp[Need assistance in fixing MySQL database issues? We can help you. ]
Conclusion
This article covers how to allow remote connections to a MySQL server. Hosting databases and web servers on a separate database server can improve security, hardware performance, and enable you to scale resources quickly.
One of the most frequently observed database bottlenecks encountered by large projects is high MySQL traffic. Sure, we could talk about moving to a bigger cloud instance size, faster cores, and better storage, but that’s too obvious and costly. Instead, we would like to focus on several tips that can reduce traffic with a bit of configuration.
Methods to reduce Internet traffic when connecting to the remote host:
- Disable and/or optimize the high-load features of your application.
- Use ProxySQL to enable connection pooling. This will prevent your application from overloading MySQL with a multitude of concurrent connections. Another nice feature of ProxySQL helps cache query results for a certain period of time.
- Monitor your databases for performance bottlenecks.
- Identify and optimize queries that cause high load. If you use dbForge Studio for MySQL, you get a specialized tool—Query Profiler—which helps you deal with this task easily.
- Add missing indexes and eliminate redundant and unused ones.
- Conduct a thorough check of your background operations. Some of them can be postponed, some may work just as effectively with limited resources (e.g. limited concurrencies for batch jobs), and some may be perfectly running on replicas.
This article covers how to allow remote connections to a MySQL server. Hosting databases and web servers on a separate database server can improve security, hardware performance, and enable you to scale resources quickly.
One of the most frequently observed database bottlenecks encountered by large projects is high MySQL traffic. Sure, we could talk about moving to a bigger cloud instance size, faster cores, and better storage, but that’s too obvious and costly. Instead, we would like to focus on several tips that can reduce traffic with a bit of configuration.
Methods to reduce Internet traffic when connecting to the remote host:
- Disable and/or optimize the high-load features of your application.
- Use ProxySQL to enable connection pooling. This will prevent your application from overloading MySQL with a multitude of concurrent connections. Another nice feature of ProxySQL helps cache query results for a certain period of time.
- Monitor your databases for performance bottlenecks.
- Identify and optimize queries that cause high load. If you use dbForge Studio for MySQL, you get a specialized tool—Query Profiler—which helps you deal with this task easily.
- Add missing indexes and eliminate redundant and unused ones.
- Conduct a thorough check of your background operations. Some of them can be postponed, some may work just as effectively with limited resources (e.g. limited concurrencies for batch jobs), and some may be perfectly running on replicas.
