mysql笔记

1 mysql常见问题

1.1 无法连接

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

单个：
grant all privileges on *.* to root@'localhost' identified by "123456" with grant option
允许ip段：
grant all privileges on *.* to root@'localhost,192.168.*.*' identified by "123456" with grant option; 
注：模糊匹配ip地址在有的mysql版本中不支持

1.2 Ubuntu18.04安装MySQL后普通用户无法登录

mysql5.7安装完成后普通用户不能进mysql，原因：root的plugin被修改成了auth_socket，用密码登陆的plugin应该是mysql_native_password，直接用root权限登录就不用密码,修改root密码和登录验证方式：

mysql> select user, plugin from mysql.user;
+------------------+-----------------------+
| user             | plugin                |
+------------------+-----------------------+
| root             | auth_socket           |
| mysql.session    | mysql_native_password |
| mysql.sys        | mysql_native_password |
| debian-sys-maint | mysql_native_password |

update mysql.user set authentication_string=PASSWORD('root'), plugin='mysql_native_password' where user='root';
flush privileges;
exit;

重启mysql 服务

 sudo service mysql restart