MySQL授权远程登录

解决1130 - Host 'xxx' is not allowed to connect to this MariaDB server

本地登录查看

[root@localhost ~]# mysql -u root -p
Enter password:

MariaDB [(none)]> show grants for root;
ERROR 1141 (42000): There is no such grant defined for user 'root' on host '%'
MariaDB [(none)]> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)

授权到所有网络

# 将登录的主机设置为全部都能登录
MariaDB [(none)]> grant all privileges on *.* to 'root'@'%' with grant option;
Query OK, 0 rows affected (0.15 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.13 sec)

#随后，直接更新服务。或者直接重启mariadb server服务

或者，如下这样授权是不需要输入密码的

MariaDB [(none)]> grant all on *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> show grants for root;
+-------------------------------------------+
| Grants for root@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)
 
MariaDB [(none)]> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | %         |
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)

或者，不要密码可能不保险，增加密码重新授权

这里设置的远程连接密码可以不和本地密码一致，远程连接的时候用identified by后面的密码

MariaDB [(none)]> grant all on *.* to root@'%' identified by "123456";
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> show grants for root;
+--------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)