Enable remote access to the MySQL database




We experience the following error when logging in to the database remotely from our computer








The reason is that MySQL does not allow remote access by default. If we want to implement remote access, we have to modify the MySQL configuration file on the remote server

mysqld.cnf

.


in

Remote server

Terminal input command:

[Shell]

Plain text view
Copy the code

?
1
sudo
vim
/etc/mysql/mysql
.conf.d
/mysqld
.cnf


the

Bind-address = 127.0.0.1 MySQL > restart MySQL

[Shell]

Plain text view
Copy the code

?
1
sudo
service mysql restart










We go back to our computer and log in again. The following error occurs again:








The root user can only log in locally when installing MySQL.


After logging in to mysql, enter the following commands:


[SQL]

Plain text view
Copy the code

?
1
2
use mysql
select
host ,
user
,authentication_string
from
user
;







Host indicates the host, user indicates the user, and authentication_string indicates the encrypted password.


You can see that the host corresponding to the root user is localhost, indicating that the root user can only log in locally.


For remote access, we need to change localhost to %. We don’t normally change root Settings, so we create a new user for remote access.


Create syntax:
Grant on database to ‘username ‘@’ identified by’ password ‘;


[SQL]

Plain text view
Copy the code

?
1
grant
all
privileges
on
*. *
to
'myroot'
@
The '%'
identified
by
'myroot'
;






We can use the newly created user login to achieve remote access.




For more free technical information: annalin1203