本文基于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后面的密码
你长舒一口气,想想今天的工作量挺大的,于是给作者点了个赞释放一下压力