这本来是一篇讲述怎么在Linux上完成MySQL的安装、新建用户并授权的博文,后来查阅了不少资料,看到一篇有意思的文章,思绪就开始泛滥了。
mysql> FLUSH PRIVILEGES;
也许你看到大多数讲解MySQL授权的文章最后都让你使用上面的命令来刷新MySQL的权限,但很多情况下可能都是毫无意义的(文末讲这个问题)。不求甚解的求知习惯是危险的,如果对一项技术有追求,应该花时间去了解背后的原理和逻辑。查看更新请访问我的 个人博客网站。
MySQL安装
参考 https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-16-04
$ sudo apt-get update
$ sudo apt-get install mysql-server
通过mysql_secure_installation
安全向导修改root用户密码,设置是否允许root远程登陆以及决定是否删除匿名账号和测试数据库等。[MySQL开发者文档的传送门]:
$ sudo mysql_secure_installation
在生产环境中,安全起见,建议设置root用户不允许远程登陆,如果只是学习测试用就无所谓了。此外,匿名账号最好删掉,不然容易出现奇奇怪怪的问题,如StackOverflow中提到的这个已授权的用户本地登陆后却出现MySQL Access Denied,高票答案中对这个问题有很详细的解答,感兴趣的读者可以打开看看。后来在MySQL的开发者文档中也看到了关于这个问题的说明: 传送门。这个问题总结起来就是:匿名账号由于指定了具体的host(localhost
),比使用通配符(%
)的账号拥有了优先权(在user表中排在前面因此先被检查),导致实名用户在本地登陆时实际上是以匿名用户身份登陆的。host是特定值的账号之所以在user表中排在前面,是因为这张表遵循了以下排序规则Access Control, Stage 1: Connection Verification:
The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so 198.51.100.13 and 198.51.100.0/255.255.255.0 are considered equally specific.) The pattern ‘%’ means “any host” and is least specific. The empty string ” also means “any host” but sorts after ‘%’. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means “any user” and is least specific). For rows with equally-specific Host and User values, the order is nondeterministic.
扯远了,MySQL安装完之后一般就启动了,通过下面的命令检查MySQL服务的状态:
$ systemctl status mysql.service
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2018-08-14 10:14:28 CST; 2min 0s ago
Process: 990 ExecStartPost=/usr/share/mysql/mysql-systemd-start post (code=exited, status=0/SUCCESS)
Process: 976 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 989 (mysqld)
CGroup: /system.slice/mysql.service
└─