Scripting

How to Allow Remote Connections to MySQL

Bamdeb Ghosh
MySQL is a famous free-to-use relational database administration system due to its reliability, scalability, and user-friendliness. MySQL is set up by default to allow connections exclusively from the localhost, limiting the access from the database server to the local machine. There are times, however, when remote connections to the MySQL server are required, particularly when numerous users need to access the database from various places. Enabling remote connections can help the teams cooperate more effectively by allowing for continuous communication.

Users can connect to the database server from multiple locations by setting MySQL to allow the remote connections. This promotes productivity and cooperation. This adaptability enables the enterprises and organizations to take use of MySQL’s capabilities in dispersed contexts. This allows collaborative data management and improves the overall operational efficiency.

Installing MySQL in Linux

To install MySQL on our Linux system, refresh or update the package index first.

$ sudo apt update

Setup the MySQL server software. We can select either the standard MySQL server or the community edition which is also known as MySQL Server CE.

$ sudo apt install mysql-server

The user has to set a password for the MySQL during the installation.

The MySQL service should begin soon after the installation is complete. We can check the status if it is active or not by typing the following command:

$ sudo systemctl status mysql

Somehow, if it is not started, we can start it by typing the following command:

$ sudo systemctl start mysql

Changing the Bind-Address IP

After completing the whole installation process, run this command:

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Output:

[mysqld]

#

# * Basic Settings

#

user = mysql

# pid-file = /var/run/mysqld/mysqld.pid

# socket = /var/run/mysqld/mysqld.sock

# port = 4405

# datadir = /var/lib/mysql

** many more outputs **

# tmpdir = /tmp

#

bind-address = 127.0.0.1

mysqlx-bind-address = 127.0.0.1

#

The server only seeks for local connections if this value is set to 127.0.0.1. The previous directive needs to be changed to contain an external IP address. To help with fixing the problems, we may set this command to a global IP address such as *,::, or 0.0.0.0:

Output:

[mysqld]

#

# * Basic Settings

#

user = mysql

# pid-file = /var/run/mysqld/mysqld.pid

# socket = /var/run/mysqld/mysqld.sock

# port = 4405

# datadir = /var/lib/mysql

** …….. many more outputs ………**

. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir

# tmpdir = /tmp

bind-address = 0.0.0.0

mysqlx-bind-address = 127.0.0.1

#

After that, just do the following steps:

  • Save the Changes: To save the modifications that we made, press the following key combination: “CTRL + X”. This command tells Nano that we want to exit the editor.
  • Confirm the Saved Changes: After pressing “CTRL + X”, Nano will ask, “Save modified buffer?” and there will be two options: “Y” or “N”. We need to type “Y”.
  • Specify the File Name: After confirming the saved changes, Nano presents the option to specify the file name if we made changes to the original file. If we want to keep the same file name, simply press “ENTER” on our keyboard.
  • Exit Nano: Once we saved the changes, Nano will exit and we’ll return to the command line interface or terminal.

Conclusion

Remote MySQL connections are required in a variety of scenarios involving dispersed applications, remote development, and collaborative database management. By following the processes that are outlined in this extensive guide, we may securely modify MySQL server’s settings and enable the remote access which allows us to fully utilize MySQL’s capabilities while ensuring the security of our database.

Similar Posts