-
乱删一通mysql.user之后发现无法登陆了,开始寻求解法
-
在my.cnf中的[mysqld]后添加–skip-grant-tables,可以不用密码权限登陆,所以我开始找mycnf的存放地点,在window中这个叫my.ini,在linux中叫my.cnf,一般存放在安装目录下,但是更简单的方式就是使用find命令
# 命令 find / -iname '*my.cnf*' # 结果,我们的mysql是在lampp里面的,所以选lampp /etc/my.cnf /etc/my.cnf.d /var/lib/docker/overlay2/db8c35257aa0dd8ae70841822ccb4eaa788ec00c9802e313567ae81dd967566b/diff/etc/mysql/my.cnf /var/lib/docker/overlay2/ab1715dcc58f89fb953c5b0fe7c34b120d33e44317275512da69fc5122202ac7/diff/etc/mysql/conf.d/my.cnf /var/lib/docker/volumes/43ad614af331b266655b7ad953a6095825b431565f734e558a325fb79bc9ae5c/_data/conf.d/my.cnf /var/lib/docker/volumes/43ad614af331b266655b7ad953a6095825b431565f734e558a325fb79bc9ae5c/_data/my.cnf /var/lib/docker/volumes/875fcd937422596be6865f929562b63b470e6485435d10040a4fef3f035d9047/_data/conf.d/my.cnf /var/lib/docker/volumes/875fcd937422596be6865f929562b63b470e6485435d10040a4fef3f035d9047/_data/my.cnf /opt/lampp/etc/.my.cnf.swp /opt/lampp/etc/.my.cnf.swo /opt/lampp/etc/my.cnf
-
添加好后进入里头修改权限,我发现会出现很多问题,由于自己是匿名用户而无法修改,不知道怎么弄了一个localhost的权限,之后关闭数据库,重启再进入,先查一下数据库的mysql.user表信息
use mysql; select host,user,password from user; #查询结果如下 +-----------+------+----------+ | host | user | password | +-----------+------+----------+ | localhost | root | | | 127.0.0.1 | root | 123456 | | % | root | | +-----------+------+----------+ 3 rows in set (0.00 sec)
-
修改文件时老是不小心放到后台运行,如何恢复呢,办法在这里
jobs -l # 查看后台运行的程序 fg %num # 选中后台运行号,将这个进程放到前台运行 kill -9 %num # 杀死某一个进程 # 还经常会生成一个swp(swap)文件,直接删就可以,具体参考下文 https://blog.csdn.net/qq_42200183/article/details/81531422
-
使用远程连接上之后,发现只有information_schema和一个test数据库,其他都没有,这是为什么?本地查询的结果和远程连接的完全不同
# 这是本地查询的结果 MariaDB [mysql]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | phpmyadmin | | test | +--------------------+ 5 rows in set (0.00 sec)
-
于是我按照网络上一个博主的方法查看了远程root用户的权限信息
MariaDB [mysql]> show grants for 'root'@'%'; +----------------------------------+ | Grants for root@% | +----------------------------------+ | GRANT USAGE ON *.* TO 'root'@'%' | +----------------------------------+ 1 row in set (0.00 sec)
-
按照他的也创建一个远程用户但是报错
MariaDB [(none)]> create user 'root'@'%' identified by ''; ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'%'
-
所以我试着先删再创
# 先删,不知道为什么是影响了0行,但确实是删了 MariaDB [(none)]> drop user 'root'@'%'; Query OK, 0 rows affected (0.00 sec) MariaDB [mysql]> select host,user,password from user; +-----------+------+----------+ | host | user | password | +-----------+------+----------+ | localhost | root | | | 127.0.0.1 | root | 123456 | +-----------+------+----------+ 2 rows in set (0.00 sec) # 创建远程用户ok! MariaDB [mysql]> create user 'root'@'%' identified by ''; Query OK, 0 rows affected (0.00 sec) # 查询结果正常 MariaDB [mysql]> select host,user,password from user; +-----------+------+----------+ | host | user | password | +-----------+------+----------+ | localhost | root | | | 127.0.0.1 | root | 123456 | | % | root | | +-----------+------+----------+ 3 rows in set (0.00 sec) # 但是赋权的时候就不ok,说是本地登陆的用户没有用密码?什么意思? MariaDB [mysql]> grant all privileges on *.* to 'root'@'%' identified by '' with grant option; ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) # 网上有人说8.0之后的版本需要分开写这段语句,就像这样,但还是报错 MariaDB [mysql]> set password for 'root'@'%' = password("123456"); Query OK, 0 rows affected (0.00 sec) MariaDB [mysql]> grant all privileges on *.* to 'root'@'%' with grant option; ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) # 因为一直报localhost的错,所以给localhost设置个密码,因为之前是空密码登陆的,然后重登一下再试 MariaDB [mysql]> set password for 'root'@'localhost' = password("123456"); Query OK, 0 rows affected (0.00 sec) # 还是报错,这就有点难搞了,估计是权限不够 MariaDB [(none)]> grant all privileges on *.* to 'root'@'%' with grant option; ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
-
又是惊奇的上网冲浪时间,查了很多,最后发现是本地root用户没有grant_priv权限,使用老哥的神奇小命令,修改权限,之后需要刷新权限(flush privileges)并且重新登陆才能生效,再赋权,就可以了
# 查看root用户所有权限 select * from user where user='root'; #增加grant权限,这一步根据情况来,哪个没有加哪个 update mysql.user set Grant_priv='Y' where User='root' and Host='localhost'; #刷新 flush privileges; #重新登陆再执行,就ok! MariaDB [(none)]> grant all privileges on *.* to 'root'@'%' with grant option; Query OK, 0 rows affected (0.00 sec)
-