Akses mysql dengan ip public

However, in some situations, it is necessary to access the MySQL server from a remote location. For example, you may need to connect to the remote MySQL server from your local system or a multi-server deployment where the application is running on a different machine from the database server. One option would be to access the MySQL server through SSH Tunnel , and another is to configure the MySQL server to accept remote connections.

This article goes through the steps necessary to allow remote connections to a MySQL server. The same instructions apply for MariaDB.

Configuring MySQL Server

The first step is to set the MySQL server to listen on a specific IP address or all IP addresses on the machine.

If the MySQL server and clients can communicate over a private network, the best option is to set the MySQL server to listen only on the private IP.

Otherwise, if you want to connect to the server over a public network, set the MySQL server to listen on all IP addresses on the machine. To do so, you need to edit the MySQL configuration file and add or change the value of the

bind-address           = 0.0.0.0
# skip-networking

bind-address           = 0.0.0.0
# skip-networking

bind-address           = 0.0.0.0
# skip-networking

sudo systemctl restart mysql

The location of the MySQL configuration file differs depending on the distribution. In Ubuntu and Debian the file is located at

sudo systemctl restart mysql

sudo systemctl restart mysql

Open the file with your text editor :

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

Search for a line that begins with

bind-address           = 0.0.0.0
# skip-networking

By default, the value is set to

sudo systemctl restart mysql

In this example, we’ll set the MySQL server to listen on all IPv4 interfaces by changing the value to

bind-address           = 0.0.0.0
# skip-networking

mysqld.cnf

bind-address           = 0.0.0.0
# skip-networking

If there is a line containing

sudo systemctl restart mysql

sudo systemctl restart mysql

In MySQL 8.0 and higher, the

bind-address           = 0.0.0.0
# skip-networking

sudo systemctl restart mysql

Once done, restart the MySQL service for changes to take effect. Only root or users with sudo privileges can restart services.

To restart the MySQL service on Debian or Ubuntu, type:

sudo systemctl restart mysql

On RedHat based distributions like CentOS to restart the service run:

sudo systemctl restart mysqld

Granting Access to a User from a Remote Machine

The next step is to allow access to the database to the remote user.

Log in to the MySQL server as the root user by typing:

sudo mysql

If you are using the old, native MySQL authentication plugin to log in as root, run the command below and enter the password when prompted:

mysql -uroot -p

From inside the MySQL shell, use the

sudo systemctl restart mysqld

GRANT ALL ON database_name.* TO user_name@'ip_address' IDENTIFIED BY 'user_password';

Where:

• sudo systemctl restart mysqld

  1 is the name of the database that the user will connect to.

• sudo systemctl restart mysqld

  2 is the name of the MySQL user.

• sudo systemctl restart mysqld

  3 is the IP address from which the user will connect. Use

  sudo systemctl restart mysqld

  4 to allow the user to connect from any IP address.

• sudo systemctl restart mysqld

  5 is the user password.

For example, to grant access to a database

sudo systemctl restart mysqld

sudo systemctl restart mysqld

sudo systemctl restart mysqld

sudo systemctl restart mysqld

GRANT ALL ON dbname.* TO foo@'10.8.0.5' IDENTIFIED BY 'my_passwd';

Configuring Firewall

The last step is to configure your firewall to allow traffic on port

sudo mysql

Iptables

If you are using iptables as your firewall, the command below will allow access from any IP address on the Internet to the MySQL port. This is very insecure.

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

UFW is the default firewall tool in Ubuntu. To allow access from any IP address on the Internet (very insecure), run:

bind-address           = 0.0.0.0
# skip-networking

Allow access from a specific IP address:

bind-address           = 0.0.0.0
# skip-networking

FirewallD

FirewallD is the default firewall management tool in CentOS. To allow access from any IP address on the Internet (very insecure) type:

bind-address           = 0.0.0.0
# skip-networking

To allow access from a specific IP address on a specific port, you can either create a new FirewallD zone or use a rich rule. Well create a new zone named

sudo mysql

bind-address           = 0.0.0.0
# skip-networking

Verifying the Changes

To verify that the remote user can connect to the MySQL server, run the following command:

bind-address           = 0.0.0.0
# skip-networking

Where

sudo systemctl restart mysqld

sudo mysql

If everything is setup up correctly, you will be able to login to the remote MySQL server.

If you get an error like below, then either port 3306 is not open , or the MySQL server is not listening on the IP address .

bind-address           = 0.0.0.0
# skip-networking

The error below is indicating that the user you are trying to log in with doesn’t have permission to access the remote MySQL server.

bind-address           = 0.0.0.0
# skip-networking

Conclusion

MySQL, the most popular open-source database server by default, listens for incoming connections only on localhost.

MySQL masuk ke dalam jenis apa?

Bagaimana cara masuk ke MySQL?

Bagaimanakah cara melakukan remote user pada server MySQL?

Apakah MySQL itu server?