Cara menggunakan mysql allow remote connections

Secara default, akses jarak jauh ke server database MySQL dinonaktifkan untuk alasan keamanan. Namun, terkadang Anda perlu memberikan akses jarak jauh ke server database dari rumah atau server web.

Step 1: Login Using SSH (if server is outside your data center)

ssh user@server
### login as the root using su or sudo ##
su
# or use sudo  ##
sudo -i

Step 2: Edit the mysqld.cnf file

Buka

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

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

Arahkan ke baris yang dimulai dengan

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

bind-address            = 127.0.0.1

Secara default, nilai ini disetel ke

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

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

bind-address            = 127.0.0.1

bind-address            = 127.0.0.1

bind-address            = 0.0.0.0

Step 3: Save and Close the file

Jika Anda menggunakan Debian / Ubuntu Linux, ketik perintah berikut untuk me-restart server mysql:

/etc/init.d/mysql restart
or
systemctl restart mysql

Jika Anda menggunakan RHEL / CentOS / Fedora / Scientific Linux, ketik perintah berikut untuk memulai ulang server mysql:

/etc/init.d/mysqld restart
or
systemctl restart mysqld

Jika Anda menggunakan FreeBSD, ketik perintah berikut untuk memulai ulang server mysql:

/usr/local/etc/rc.d/mysql-server restart
or
service mysql-server restart

Step 4: Grant access to remote IP address

Hubungkan ke server mysql:

mysql -u root -p mysql 

Anda dapat membuat akun pengguna baru yang hanya akan terhubung dari host jarak jauh dengan perintah berikut:

CREATE USER 'user'@'remote_server_ip' IDENTIFIED BY 'password';

Kemudian berikan pengguna baru hak istimewa yang sesuai untuk kebutuhan khusus Anda.

GRANT ALL ON *.* TO 'user'@'remote_server_ip' IDENTIFIED BY ‘password’;

Setelah ini, praktik yang baik untuk menjalankan

bind-address            = 127.0.0.1

bind-address            = 127.0.0.1

bind-address            = 127.0.0.1

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

Kemudian Anda dapat keluar dari klien MySQL:

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

Step 5: Open port 3306

Terakhir, dengan asumsi Anda telah mengonfigurasi firewall di server database, Anda juga perlu membuka port

bind-address            = 127.0.0.1

Jika Anda hanya berencana untuk mengakses server database dari satu mesin tertentu

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

Atau, Anda dapat mengizinkan koneksi ke database MySQL Anda dari alamat IP mana pun dengan perintah berikut:

First, confirm that your MySQL server configuration to allow for remote connections. Use your preferred text editor to open the MySQL server configuration file:

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

Scroll down to the bind-address line and ensure that is either commented out or replaced with

sudo systemctl restart mysql

Once you make the necessary changes, save and exit the configuration file. Apply the changes made to the MySQL config file by restarting the MySQL service:

sudo systemctl restart mysql

Next, log into the MySQL server console on the server it was installed:

mysql -u root -p

Enter your mysql user password

Check the hosts that the user you want has access to already. In my case the user is

sudo systemctl restart mysql

SELECT host FROM mysql.user WHERE user = "root";

This gave me this output:

+-----------+
| host      |
+-----------+
| localhost |
+-----------+

Next, I ran the command below to grant the

sudo systemctl restart mysql

sudo systemctl restart mysql

USE my_database;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'my-password';

Note:

sudo systemctl restart mysql

sudo systemctl restart mysql

Afterwhich I checked the hosts that the user now has access to. In my case the user is

sudo systemctl restart mysql

SELECT host FROM mysql.user WHERE user = "root";

This gave me this output:

+-----------+
| host      |
+-----------+
| %         |
| localhost |
+-----------+

Finally, you can try connecting to the MySQL server from another server using the command:

mysql -u username -h mysql-server-ip-address -p

Where u represents user, h represents mysql-server-ip-address and p represents password. So in my case it was:

Bagaimanakah cara melakukan remote user pada server MySQL?

Apa yang dimaksud remote MySQL?