Distributed environment is hardly a new notion in the world of software. For one, you no longer need to host MySQL servers and databases on the same local machine. Now you can have a dedicated server that can deliver the required level of security and performance. With the help of root/sudo user privileges, you can enable new remote connections and set up secure and effective teamwork with your databases using a remote MySQL server.
This article will make a good start, explaining how to enable and set up remote connections to MySQL servers and databases, manage user privileges, and, most interestingly, how to get everything done most easily using dbForge Studio, a specialized IDE for MySQL databases.
Contents
1. How to enable and set up remote MySQL server connection
2. How to grant and set up user access from the remote host
3. How to connect to a remote database
4. How to connect to the remote host using dbForge Studio for MySQL
5. How to reduce Internet traffic when connecting to the remote host
How to enable and set up remote MySQL server connection
Our first task is to enable and configure remote connections to the MySQL server in order to allow users to connect to the remote host. You will need your Windows or Linux machine, which has the required MySQL server installed on it, and your own root access to set up remote connection from. Ready? Let’s get started.
If you want to enable remote access for a root user, run the following SQL command on the server:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;Now you need to change the bind-address IP (the bind variable) in the MySQL config file. If you have a Linux machine, launch your CLI and run the following command:
sudo nano /etc/mysql/mysql.conf.d/my.cnf
If you have a Windows machine, the file name will be my.ini, and it will be located in the MySQL installation directory, e.g. C:\Program Files\MySQL\MySQL Server 8.0\.
Please note that the exact location of the config file may vary depending on your MySQL version, so you may need to adjust it accordingly.
Once you open the config file, look up the bind-address line. The default IP is 127.0.0.1, which enables access from localhost only (bind 127.0.0.1). You need to set a new IP to match the address of the computer that will be able to gain remote access to MySQL server. Please note that setting it to 0.0.0.0 will enable access from all hosts. When it’s done, save and exit the config file.
Now you need to restart the MySQL service in order to apply your changes. This is how you can do it on Linux:
sudo systemctl restart mysql
And if you work on Windows, you can launch the command prompt, proceed to your MySQL directory, and run the following command (similarly, alter it to match your MySQL version):
net stop MySQL80 net start MySQL80
If it does not work, you can restart MySQL by right-clicking and selecting Restart via the Service tab of your Task Manager.
How to grant and set up user access from the remote host
You can enable remote access to a remote user by reconfiguring that account to connect from the remote server instead of localhost. Open your MySQL client as a root/sudo user:
sudo mysql
If you have enabled password authentication for root access, run the following command to access the MySQL shell:
mysql -u root -p
You can allow remote connection for a locally connected user using the RENAME USER command (having specified the user name and the remote server IP beforehand):
mysql> RENAME USER 'user_name'@'localhost' TO 'user_name'@'remote_server_ip';
There is an alternate way: you can create a new user to connect right from the remote host:
mysql> CREATE USER 'user name'@'remote_server_ip' IDENTIFIED BY 'password';
Finally, you need to grant privileges to the remote user. Since it is generally not recommended to grant ALL privileges, it is better to specify the required ones, for instance:
mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'user_name'@'remote_server_ip' WITH GRANT OPTION;
Afterwards, you can run the FLUSH PRIVILEGES command, which will free up the memory cached by the server following the CREATE USER and GRANT statements:
mysql> FLUSH PRIVILEGES;
As a bonus, we would like to recommend an article where you can learn how to check MySQL user permissions.
How to connect to a remote database
Now comes the time to connect to a MySQL database remotely and test your remote connection online. We suggest 3 tools that can help you do it: the MySQL CLI client, MySQL Workbench, and dbForge Studio for MySQL. You will find the detailed walkthrough for each tool in our detailed guide to connecting to MySQL Server.
How to connect to the remote host using dbForge Studio for MySQL
Among the 3 ways mentioned in the previous paragraph, we would like to single out dbForge Studio for MySQL by Devart. It is the most comprehensive MySQL toolset that money can buy, offering far superior capabilities than those of similar tools like MySQL Workbench.
dbForge Studio delivers a smart wizard that guides the user through the setup of a remote host and database connection as well as connection to the local server. See how easily it’s done—check our step-by-step guide to connecting via dbForge Studio for MySQL.
You can also consult the product documentation to learn all about managing database connections.
How to reduce Internet traffic when connecting to the remote host
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.
- 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.
That’s it! If you want to make your work with the remote MySQL host and databases far less time-consuming, try the 30-day free trial of dbForge Studio for MySQL.
