mysql開啟遠端通路權限_MySQL 開啟遠端通路權限

1. 登入mysql資料庫:

mysql -u root -p

mysql>use mysql;

因為 mysql 資料庫中存儲使用者資訊的 user 表。

2. 檢視mysql 資料庫的 user 表中使用者的相關資訊：

select host, user from user;

或者 mysql 8：

select host, user, authentication_string, plugin from user;

3.　授權主機權限并設定遠端通路的權限:

CREATE USER 'root'@'%' IDENTIFIED BY '[email protected]';

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '[email protected]';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

flush privileges;

GRANT ALL ON *.* TO 'root'@'%';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

如果是固定ip就這麼寫：

grant all privileges on *.* to 'root'@'192.168.0.49' identified by 'password' with grant option;

GRANT ALL ON 表示所有權限，% 表示通配所有 host，可以通路遠端。

MySQL 8修改密碼：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密碼';

mysql>FLUSH PRIVILEGES;

注意：MySQL8中需要修改 /etc/my.cnf ：

default-authentication-plugin=mysql_native_password