MariaDB bind-address

Definition of MariaDB bind-address

When we install MariaDB database server, it only accepts the connection of its localhost. If users need to connect with different remote client computers from a remote relocation, we are not able to connect remote users so that reason we can use the bind-address concept to connect databases from the different remote client from a different remote locations. When a configuration is correct then we are able to establish a connection between them. If the database is connected to the internet we are able to access it from anywhere around the world where the internet facility is available. MariaDB server runs on a default IP address that is 127.0.0.1.

Syntax:

grant all privileges on *.* to ‘user name’@’IP address.%’ identified by ‘new password of user’ with grant option;

Explanation:

In the above syntax, we use the grant all privileges command to list out all allowed privileges, after that we use *.* This symbol to define which table or database has these privileges followed by user name with their IP address or we can say host address to connection purpose and password field is an optional part of this syntax. One more important thing is that the % symbol is used for a wildcard entry.

How bind-address works in MariaDB?

Let’s see how bind-address works in MariaDB as follows.

Basically, MariaDB packages bind with by default IP address that is 127.0.0.1. In old packages, they used skip-networking, and nowadays we use bind addresses. So let’s see one by one.

skip networking: This is a very simple method to configure and in this method, MariaDB runs without any TCP/IP protocols.

bind address: In this method basically we required two networking interfaces that are Loopback network device and a physical NIC the NIC means Network Interface Card which allows us to communicate with the network. Actually, this is not required because we have a different option for that purpose. The default IP address of MariaDB is suitable to connect them in a network that is bind-address refers to the local IP address or we will receive a fatal error and MariaDB will not start. So if we need to connect them in the network we need to remove the bind-address directive (The MariaDB supports one bind-address if the bind-address directive is left out).

If the bind-address runs with 127.0.0.1 (localhost) address so at that time no one is able to connect to the MariaDB server from a different host or different remote location.

Now let’s see different steps to bind the address as follows.

The first step we need to find out the default files. We need to enable MariaDB to listen to remote location connections so need to edit the default file that means we need to edit my.cnf file as per our requirement.

The second step is editing the default file. After getting the configuration file, the next step is to open that file in the edit tool and try to find the below lines.

[mysqld] ……
…..
skip - networking
……
bind – address = < specified ip – address>
….

There is no need to have the same order that is mentioned in above, sometimes it may be different.

After finding above mention lines then make sure both are commented by using the # symbol as follows.

mysqld] ……
…..
# skip - networking
……
# bind – address = < specified ip – address>
….

The same thing again here there is no need to have the same order.

Otherwise, we can have another alternative path that we can add some lines at the end of my.cnf configuration file and the lines that we need to add as follows.

[mysqld] skip - networking = 0
skip – bind-address

After adding this line save file and restart the MariaDB service.

The third step we need to grant user connection from remote hosts. Now installed MariaDB servers accept connections from remote hosts, as per our requirement we can add a new user that we need to allow to connect from different remote locations that means other than the localhost.

Examples

Now let’s see the example of MariaDB bind address as follows.

Now first see the existing remote user from MariaDB server as follows.

SELECT host, user FROM mysql.user WHERE Host <> 'localhost';

Explanation:

In the above example, we use the select and where clause, In this example we use the select clause with two attributes such as host and user as shown in the above statement, after that we access the MySQL.user table or we can also call a system table that contains all user details. Finally, we add conditions by using the where clause as shown above statement. The final output of the show databases queries we illustrate by using the following snapshot.

Suppose we need to create a new user at that time we can use the following syntax as follows.

create user specified user name;

Explanation:

In the above syntax we use to create user statement to create a new user on the MariaDB server, here specified user name means actual user name that we need to create.

Now see how we can add the user to get the remote access from different remote locations as follows.

Example

GRANT ALL PRIVILEGES ON *.* TO 'king'@'198.162.10.1.%'
IDENTIFIED BY 'sample123' WITH GRANT OPTION;

Explanation

In the above example, we use the grant all privileges command to a specified table or database as shown in the above statement by using the *.* symbol. Here the king is the username that we need to connect them from different remote locations with their IP address or we can say the host is used to connecting this user mentioned in an above statement after that we assign password and password field is an optional part of this syntax and most important thing is that % symbol is used for a wildcard. The final output of the show databases queries we illustrate by using the following snapshot.

Conclusion

We hope from this article you have understood about the MariaDB bind address. From this article, we have learned the basic syntax of MariaDB bind-address and we also see different examples of MariaDB bind address. From this article, we learned how and when we use MariaDB bind addresses.

Recommended Articles

This is a guide to the MariaDB bind-address. Here we discuss the Definition, How bind-address works in MariaDB?, Advantages and disadvantages. You may also have a look at the following articles to learn more –

1. MariaDB MaxScale
2. MariaDB Foreign Key
3. MariaDB Commands
4. MariaDB UPDATE