极
极核实验室
MySQL Remote Connection: % Privilege

MySQL Remote Connection: % Privilege

AI summary
文章主要介绍了如何解决远程电脑管理数据库连接失败的问题。首先需要开启MySQL的3306端口,通过编辑iptables防火墙配置文件并重启防火墙实现。接着使用mysql命令连接到MySQL,进入mysql数据库表,查看用户表并更新root用户的Host属性为%,或指定IP地址,最后刷新权限。完成这些步骤后即可成功远程连接数据库。

Repost license

Request authorization before reposting and retain the authorization code, original URL, and go/affiliate links.

Original URL:https://www.geekcorelab.com/post/permission-for-mysql-remote-connection

The commercial link is an authorized go short link that must stay on the reposted page. It records the source before sending readers to the original article.

We often encounter this problem when managing databases from a remote computer
Connection failed

So this article will help you solve this problem

The test server uses CentOS7.5-MySQL5.5
You need to open the MySQL port first; otherwise, it will not connect even after configuration
If you are using an iptables firewall, you can open 3306 with the following operations
vi /etc/sysconfig/iptables #编辑防火墙配置文件
Add a line at the very bottom
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
:wq! #保存退出
Then you need to restart the firewall
systemctl restart iptables.service #重启防火墙使配置生效
systemctl enable iptables.service #设置防火墙开机启动

The operation is very simple, just 5 steps, as follows:

First, connect to MySQL

mysql -uroot -p or /usr/local/mysql/bin/mysql -u root -p

Then, when Enter password: appears, enter your MySQL root password

If the following image appears, it means MySQL connected successfully

Connect to MySQL
Enter the mysql table

use mysql;

Enter the mysql table
View the user table

SELECT `Host`,`User` FROM user;

View users
Update the permissions of the root user

UPDATE user SET `Host` = '%' WHERE `User` = 'root' LIMIT 1;

% can be changed to a specified IP
Update root

Force refresh permissions

flush privileges;

Update permissions
After completing the above steps, you can connect to the database remotely

Success

Comments

Discussion

Be kind and keep the discussion friendly.

Complete your profile details to improve the chance of approval.

Your email will not be shown publicly.

Loading...

Markdown supported.

Discussion

0 comments

Internal link graph

Loading internal links…

View linked articles