解决1130 - Host 'xxx' is not allowed to connect to this MariaDB server这一系列问题[MySQL]

本文基于CentOS7，相信你已经知道CnetOS7中mysql被mariadb替换了

回顾一下安装过程：为了保险，你关闭了selinux和防火墙。你在CentOS7上yum安装了mariadb-server，正在执行初始化

mysql_secure_installation

结果报错了

错误1：

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

你惊奇的发现，只是你太粗心，mysql没有启动，于是你快速敲下

systemctl start mariadb

好，问题解决了，你接着输入mysql_secure_installation，来到这个界面

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]

错误2：

你选了Y，Disallow root login remotely? 这句是在问是否关闭远程连接，Y就是关闭

其实后面我发现这个选Y或者n都没关系。。。

你选了n，很从容的完成了后续操作，准备远程连接

错误3：

就是标题中的1130 - Host 'xxx' is not allowed to connect to this MariaDB server

你困惑了，但通过本地登录查看了一下

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 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)

恩，你很放心，进行远程连接，正确输入了数据库的账号密码，确认了ip和端口，点击连接，结果

错误4：

你又一次困惑，但是你突然发现说你在用密码，于是，你恍然大悟，原来这样授权是不需要输入密码的，于是你去掉密码，登录成功了

但是你一想，不要密码可能不保险，不是你的风格，于是你增加了密码重新授权

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)

这下你放心了，但是

注意：

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

你长舒一口气，想想今天的工作量挺大的，于是给作者点了个赞释放一下压力