Defining a BIND address in MYSQL, Hello my dear readers, in this article, we will be talking about how we define a BIND address in Mysql. In the previous article, we told you about "MySQL installation and MYSQL on Ubuntu" installation. Let's assume we have completed the installation. So how do we manage?
This is where the BIND address comes into play. If the BIND address is not defined, you can only connect to our Mysql server via localhost, that is, through the server itself.
What is BIND Address Definition in MYSQL?
The MySQL server listened to the inbound connection requests on one or all established network interfaces prior to the adoption of WL#11652 in MySQL 8.0.13.
How to Bind a MySQL Server to an IP Address? Though it might be advantageous to set aside more than one network interface for a particular use, allowing for the specification of bind-address for multiple network interfaces, in the event that the underlying platform supports multiple network interfaces. Using this functionality to divide network traffic on a MySQL server for particular objectives like:
- Monitoring of Replication Backup ( Users )
- Users' flow of business
- Keep in mind that this also gives you the option *not* to use a MySQL interface.
What is BIND?
Bind-address configuration on MySQL shows which networks our MySQL server will listen to. In short, we can specify the allowed remote connections. ( Allow remote connections ) By default, MySQL is configured to accept local connections on localhost.
BIND Address Definition in MYSQL?
The MySQL server can only be accessed by programs that are running on the same host by default because it accepts just connections from localhost.
However, there are times when a remote site must be used to access the MySQL server. For instance, if you are using a multi-server deployment and the application is running on a separate computer than the database server, you might need to connect to the distant MySQL server from your local system. The MySQL server may be accessed through an SSH tunnel, or it can be set up to allow connections from far away locations.
The procedure for enabling remote connections to a MySQL server is described in this article. For MariaDB, the same steps apply.
Configuring MySQL Server
Setting the MySQL server to listen on a particular IP address or all IP addresses on the system is the first step.
The best choice is to configure the MySQL server to exclusively listen on the private IP if the server and clients can interact via a private network.
Otherwise, configure the MySQL server to listen on all available IP addresses if you wish to connect to the server over a public network. To achieve this, you must add to or modify the value of the bind-address option in the MySQL configuration file. Both a single IP address and IP ranges can be configured. The MySQL Server will accept connections from any host IPv4 interface if the address is 0.0.0.0.If you have IPv6 configured on your system, then instead of 0.0.0.0, use ::.
Depending on the distribution, the MySQL configuration file's location varies. The file is placed at /etc/mysql/mysql.conf.d/mysqld.cnf in Ubuntu and Debian, but it is at /etc/my.cnf in Red Hat-based distributions like CentOS.
Writeing this code:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Find a line with the word "bind-address" in it, and change its value to the IP address where a MySQL server should listen. The value is normally set to 127.0.0.1 ( which only listens on localhost ).
By altering the value to 0.0.0.0 in this example, we'll tell the MySQL server to listen on all IPv4 interfaces.
bind-address = 0.0.0.0 # skip-networking
If there is a line that contains the word "skip-networking," remove it or comment it out by beginning the line with a #.
The bind-address directive may not be available in MySQL versions 8.0 and above. Add it here beneath the [mysqld] section.
For modifications to take effect after completion, restart the MySQL service. Services can only be restarted by root or users with sudo access.
To restart the MySQL service on Debian or Ubuntu, type:
$ sudo systemctl restart mysql
To restart the service on RedHat-based distributions like CentOS, do the following:
$ sudo systemctl restart mysqld
Granting Access to a User from a Remote Machine
The next step is to provide the remote user access to the database.
Type: to connect to the MySQL server as the root user.
$ sudo mysql
Run the command below and input the password when prompted if you are logging in as root with the outdated, native MySQL authentication plugin:
$ mysql -uroot -p
Now we wills ay to you again, From inside the MySQL shell, use the GRANT statement to grant access to the remote user.
$ msql > GRANT ALL ON database_name.* TO user_name@'ip_address' IDENTIFIED BY 'user_password';
Where:
- database_name is the name of the database that the user will connect to.
- user_name is the name of the MySQL user.
- user_password is the user password.
For instance, to allow access to a database dbname for the user foo from a client machine with the IP 10.8.0.5, you would execute:
$ - GRANT ALL ON dbname.* TO foo@'10.8.0.5' IDENTIFIED BY 'my_passwd';
Configuring Firewall
The final step is to set up your firewall to accept traffic from distant workstations on port 3306 ( the default port for MySQL ).
Iptables
The command shown below will enable access to the MySQL port from any IP address on the Internet if you are using iptables as your firewall. This is quite unsafe.
sudo iptables -A INPUT -p tcp --destination-port 3306 -j ACCEPT
Allow access from a specific IP address:
sudo iptables -A INPUT -s 10.8.0.5 -p tcp --destination-port 3306 -j ACCEPT
UFW
Now my friends also we sill say you again again all: UFW is the default firewall tool in Ubuntu. To allow access from any IP address on the Internet ( very insecure ), run:
sudo ufw allow 3306/tcp
Allow access from a specific IP address:
sudo ufw allow from 10.8.0.5 to any port 3306
Conclusion
MySQL, the most widely used open source database server, only accepts connections from localhost by default.
To allow remote connections to the MySQL server, you must perform the following actions:
Set the MySQL server to listen on all interfaces or a single interface. / Grant access to the remote user. / Opening firewall port for MySQL. / Please feel free to ask questions in the comments section below.
Now friends, we think this article is really useful for you and you can support us by sharing our article. All code lovers here! If you are confused, please contact us and we will support you as soon as possible. We are here to support you when you cannot do it in every article. Just look at our one meeting, stay healthy!
