当mysql部署完成之后,mysql将会读取/etc/hosts中对于服务器内网的host的定义,而阿里云服务器将会自动的将内网的ip地址添加到这个里面。如同下面:
[root@iZ23jhimygwZ ~]# cat /etc/hosts
127.0.0.1 localhost
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
xxx.xxx.xxx.xxx iz23jdwmygwz
[mysql]> select user,password,host from user;
+------+-------------------------------------------+--------------+
| user | password | host |
+------+-------------------------------------------+--------------+
| root | *45C964FD6EC6A75DA5F19BD625CD6A9D1B91B3FF | localhost |
| root | | iz23jdwmygwz |
| root | *45C964FD6EC6A75DA5F19BD625CD6A9D1B91B3FF | 127.0.0.1 |
| root | | ::1 |
| | | localhost |
| | | iz23jdwmygwz |
| | | iz23jdwmygwz |
+------+-------------------------------------------+--------------+
默认的这些账号是由mysql_install_db命令来创建的。纯粹为了方便来操作。也能匿名登录本机。在考虑到安全性的问题的时候,他们还编写了一个mysql_secure_installation工具,中间编写了一些用于清理的函数。
这个里面也就顺带将自己内网连接的方式的密码设置为空了。而且当你以他的内网地址登录的时候是无需要密码的。如果这个时候我们添加了一个test账户来访问这个数据库,并且将密码设置成test。(注意:GRANT命令其实是不需要执行任何FLUSH PRIVILEGES; 很多网上的人都添加上去,也不去做实验。)
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY 'test' WITH GRANT OPTION;
+------+-------------------------------------------+--------------+
| user | password | host |
+------+-------------------------------------------+--------------+
| root | *45C964FD6EC6A75DA5F19BD625CD6A9D1B91B3FF | localhost |
| root | | iz23jdwmygwz |
| root | *45C964FD6EC6A75DA5F19BD625CD6A9D1B91B3FF | 127.0.0.1 |
| root | | ::1 |
| | | localhost |
| | | iz23jdwmygwz |
| test | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | % |
+------+-------------------------------------------+--------------+
如果当我们在本地,使用他的内网地址来访问,就也将会被
| | | localhost |
| | | iz23jdwmygwz |
代替掉。
mysql -utest -ptest
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
而无需任何的密码:
mysql -utest
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2667
Server version: 5.5.47-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
如果想解决掉这个问题,我们需要将服务器中的这些授权都删除掉:
MariaDB [mysql]> delete from user where user='' and host='iz23jdwmygwz';
Query OK, 1 row affected (0.07 sec)
MariaDB [mysql]> delete from user where user='' and host='localhost';
Query OK, 1 row affected (0.00 sec)
MariaDB [mysql]> select user,password,host from user;
+------+-------------------------------------------+--------------+
| user | password | host |
+------+-------------------------------------------+--------------+
| root | *45C964FD6EC6A75DA5F19BD625CD6A9D1B91B3FF | localhost |
| root | | iz23jdwmygwz |
| root | *45C964FD6EC6A75DA5F19BD625CD6A9D1B91B3FF | 127.0.0.1 |
| root | | ::1 |
| test | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | % |
+------+-------------------------------------------+--------------+
5 rows in set (0.00 sec)
最后就能通过-utest -ptest和设置成自己的iz23jdwmygwz对应的hosts地址也能访问。
[root@iz23jdwmygwz ~]# mysql -utest -ptest -h127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2672
Server version: 5.5.47-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> quit
Bye
[root@iz23jdwmygwz ~]# mysql -utest -ptest -hiz23jdwmygwz
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2673
Server version: 5.5.47-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
其实在自带的工具mysql_secure_installation里面也有类似的东西
echo "By default, a MariaDB installation has an anonymous user, allowing anyone"
echo "to log into MariaDB without having to have a user account created for"
echo "them. This is intended only for testing, and to make the installation"
echo "go a bit smoother. You should remove them before moving into a"
echo "production environment."
echo
echo $echo_n "Remove anonymous users? [Y/n] $echo_c"
read reply
if [ "$reply" = "n" ]; then
echo " ... skipping."
else
remove_anonymous_users
fi
echo
remove_anonymous_users() {
do_query "DELETE FROM mysql.user WHERE User='';"
if [ $? -eq 0 ]; then
echo " ... Success!"
else
echo " ... Failed!"
clean_and_exit
fi
return 0
}