问题一:
参考docker 官方的mysql文档中,使用如下命令在docker创建一个容器为mysql,设置配置文件、数据挂载,root的密码为123213
docker run --name tools-mysql -p 3306:3306 --privileged=true -v /home/tools/mysql/conf.d:/etc/mysql/conf.d -v /home/tools/mysql/datSQL_ROOT_PASSWORD=123123 -d mysql:latest
但是在进入mysql容器后,发现无法使用root 123123
登陆进去,提示如下:
root@3a91127e32b0:/# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
下面的解决方法,也适用于忘记了root密码, 如何更改
解决:
先尝试一下没有密码能否登录,直接输入
root@3a91127e32b0:/# mysql
搜索一番后,发现文章连接MySQL数据库时常见故障问题的分析与解决对错误ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
分析的很详细,但我的问题是,根本进不去数据库,所有修改授权访问的ip,修改密码都无法进行
又一番搜索,找到了一篇文章,由于我使用的docker,操作步骤有些不一样,而且有些不适用mysql 8.0,下面列出我的有效步骤
1,在挂载的配置文件中,添加如下配置,添加skip-grant-tables,一定要在mysqld下面
[mysqld]
skip-grant-tables
注意,开启skip-grant-tables,会自动开启skip-networking,阻止远程连接。以下摘自官方文档
Because this is insecure, if the server is started with the –skip-grant-tables option, it enables –skip-networking automatically to prevent remote connections.
2,docker restart tools-mysql
重启mysql,再尝试登陆mysql,遇到需要输入密码,直接回车
3,执行 use mysql
但是原文的第4步更改密码,始终报错
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '("123") where user="root"' at line 1
又进行不下去了,又经过一番搜索尝试,找到了这篇Centos7重置Mysql 8.0.1 root 密码
上面报错的原因:在mysql 5.7.9以后废弃了password字段和password()函数;
4、给root用户重置密码;
首先查看当前root用户相关信息,在mysql数据库的user表中;
select host, user, authentication_string, plugin from user;
host: 允许用户登录的ip,%表示可以远程;
user:当前数据库的用户名;
authentication_string: 用户密码;
plugin: 密码加密方式;
mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| % | root | $A$005$ i=q{UG(G~?_`oD)FqNsHAKYyvUkHbloZDos6mNGoRJmVyKBBqzHGpZD7f/ | caching_sha2_password |
| localhost | mysql.infoschema | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | root | $A$005$<4Uvms>T y#no5E/Hyf..Fujny8Byr1tKlqXWRwCihCkiJefsEXX/C7 | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
5、如果当前root用户authentication_string字段下有内容,先将其设置为空;
update user set authentication_string='' where user='root' and host='localhost';
6、退出mysql, 删除my.cnf文件最后的 skip-grant-tables 重启mysql服务;
使用root用户进行登录,因为上面设置了authentication_string为空,所以可以免密码登录;
mysql -u root -p
passwrod:直接回车
7、使用ALTER修改root用户密码;
ALTER user 'root'@'localhost' IDENTIFIED BY '123123'
如果不关闭--skip-grant-tables,
直接运行上面的命令,会报如下错误:
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
我后来把data(挂载数据的目录)清空后,重新安装,发现进入容器后又可以登陆进去了,真是奇怪了,只是远程登陆不了
问题二:
远程连接不上,提示如下:
plugin 'caching_sha2_password' cannot be loaded
原因:
使用的plugin不一样,需要使用mysql_native_password
而上面的是caching_sha2_password
解决:
使用如下命令修改
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'xue123';
Query OK, 0 rows affected (0.50 sec)
mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| % | root | *1066B97AE017C2ACE56EF0001E8B2C3444CC54AC | mysql_native_password |
| localhost | mysql.infoschema | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | root | $A$005$oW!)
>b8^Z3.StHzyYbgM9IWEmItq.womSEd3LiCdoQIKefNdftmbi0a2 | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
问题三:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
重新登陆sql,就可以了,不知道什么问题,难道是因为刚刚启动,连接的太着急了?
参考:
Authentication plugin ‘caching_sha2_password’ cannot be loaded
Centos7重置Mysql 8.0.1 root 密码
连接MySQL数据库时常见故障问题的分析与解决
Mysql 登录提示”ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)”