Mysql学习浅谈mysql密码遗忘和登陆报错的问题

发布时间：2020-12-12 02:50:39 所属栏目：MySql教程来源：网络整理

导读：《Mysql学习浅谈mysql密码遗忘和登陆报错的问题》要点：本文介绍了Mysql学习浅谈mysql密码遗忘和登陆报错的问题，希望对您有用。如果有疑问，可以联系我们。 MYSQL实例 mysql登录暗码忘记,其实解决办法很简单,只需要在mysql的主配置文件my.cnf里添加一行“

《Mysql学习浅谈mysql密码遗忘和登陆报错的问题》要点：
本文介绍了Mysql学习浅谈mysql密码遗忘和登陆报错的问题，希望对您有用。如果有疑问，可以联系我们。

MYSQL实例mysql登录暗码忘记,其实解决办法很简单,只需要在mysql的主配置文件my.cnf里添加一行“跳过授权表”的参数选择即可!

MYSQL实例在my.cnf中添加下面一行：

MYSQL实例[root@test-huanqiu ~]# vim /etc/my.cnf ? ? ? ? ? ? ?//在[mysqld]区域里添加
........
skip-grant-tables? ? ? ? ? ? ? ? ? ? ?? //跳过授权表

MYSQL实例然后重启mysql服务,即可无暗码登录

MYSQL实例[root@test-huanqiu ~]# /etc/init.d/mysqld restart

MYSQL实例登录后重置暗码

MYSQL实例
[root@test-huanqiu ~]# mysql 
mysql> select host,user,password from mysql.user;
+--------------------+------+-------------------------------------------+
| host | user | password |
+--------------------+------+-------------------------------------------+
| localhost | root | *481ACA1BD6D1E86221244904E9C0FABA33B40B84 |
| host-192-168-1-117 | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| host-192-168-1-117 | | |
+--------------------+------+-------------------------------------------+
6 rows in set (0.00 sec)
mysql> update mysql.user set password=password("123456") where host="localhost" and user="root";
Query OK,1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK,0 rows affected (0.01 sec)
mysql> select host,password from mysql.user;
+--------------------+------+-------------------------------------------+
| host | user | password |
+--------------------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| host-192-168-1-117 | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| host-192-168-1-117 | | |
+--------------------+------+-------------------------------------------+
6 rows in set (0.00 sec)
mysql>

MYSQL实例再次将my.cnf里添加的那一行注释,然后重启mysql

MYSQL实例[root@test-huanqiu ~]# vim /etc/my.cnf
........
#skip-grant-tables

MYSQL实例[root@test-huanqiu ~]# /etc/init.d/mysqld restart

MYSQL实例[root@test-huanqiu ~]# mysql -p123456
mysql>

MYSQL实例-----------------------------------------------------------------------------------------------------------------------

MYSQL实例发现的一个坑：

MYSQL实例mysql之前进行了全量备份,在恢复后,发现用之前的暗码登陆不进去了!

MYSQL实例使用上面的方法,无暗码登陆后再重置暗码,但是重置暗码后发现仍然登陆不进去.

MYSQL实例最后发现是因为mysql.user表内容被清空了!

MYSQL实例mysql> select host,password from user;
Empty set (0.00 sec)

MYSQL实例解决：

MYSQL实例插入数据,再重置暗码

MYSQL实例
mysql> insert into user(host,password) values("localhost","root","123456");
Query OK,1 row affected,3 warnings (0.01 sec)
mysql> select host,password from user;
+-----------+------+----------+
| host | user | password |
+-----------+------+----------+
| localhost | root | 123456 |
+-----------+------+----------+
1 row in set (0.00 sec)
mysql> update mysql.user set password=password("123456") where host="localhost" and user="root";
Query OK,1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select host,password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into user(host,password) values("127.0.0.1",3 warnings (0.00 sec)
mysql> select host,password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | 123456 |
+-----------+------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> update mysql.user set password=password("123456") where user="root";
Query OK,1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql> select host,password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+

MYSQL实例然后使用重置后的暗码就能正常登陆了!

MYSQL实例------------------------------------------------------------------------------------------------------------------
mysql登录报错1：

MYSQL实例[root@test-huanqiu ~]# mysql -p123456
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

MYSQL实例[root@test-huanqiu ~]# ps -ef|grep mysql
root 28279 1 0 12:55 ? 00:00:00 /bin/sh /usr/local/mysql//bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/data/mysql.pid
mysql 29059 28279 0 12:55 ? 00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql/ --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql//lib/plugin --user=mysql --log-error=/data/mysql/data/mysql-error.log --pid-file=/data/mysql/data/mysql.pid --socket=/usr/local/mysql/var/mysql.sock --port=3306
root 30726 11268 0 12:58 pts/2 00:00:00 grep mysql

MYSQL实例可知,当前mysql.sock文件路径是/usr/local/mysql/var/mysql.sock,

MYSQL实例解决办法：做软链接

MYSQL实例[root@test-huanqiu ~]# ll /usr/local/mysql/var/mysql.sock
rwxrwxrwx. 1 mysql mysql 0 Nov 29 12:55 /usr/local/mysql/var/mysql.sock
[root@test-huanqiu ~]# rm -f /var/lib/mysql/mysql.sock
[root@test-huanqiu ~]# ln -s /usr/local/mysql/var/mysql.sock /var/lib/mysql/mysql.sock

MYSQL实例这样就没问题了
[root@test-huanqiu ~]# mysql -p123456
mysql>

MYSQL实例----------------------------------------------------------------------------------------------------
启动mysql的时候报错：

MYSQL实例Starting MySQL.... ERROR! The server quit without updating PID file (/data/mysql/data/mysql.pid).

MYSQL实例尝试的解决办法：

MYSQL实例(1)权限问题

MYSQL实例可能是mysql.pid文件没有写的权限,将mysql的安装目录和数据目录的权限都设置成mysql启动用户权限.比如都修改为mysql:mysql权限

MYSQL实例(2)可能进程里已经存在mysql进程

MYSQL实例ps -ef|grep mysql 查出要是有mysql进程存在,就kill掉,再尝试重启mysql

MYSQL实例(3)可能是多次在机器上安装mysql,有残余数据影响了服务的启动.
去mysql的数据目录看看,如果存在mysql-bin.index,就立刻删掉它,它就是罪魁祸首了!

MYSQL实例(4)mysql在启动时没有指定配置文件时会使用/etc/my.cnf配置文件,请打开这个文件查看在[mysqld]节下有没有指定数据目录.

MYSQL实例在[mysqld]下添加设置,如datadir = /data/mysql/data

MYSQL实例(5)skip-federated字段问题

MYSQL实例检查一下my.cnf文件中有没有没被注释掉的skip-federated字段,如果有就立即注释掉.

MYSQL实例(6)错误日志目录不存在

MYSQL实例去my.cnf文件下是否有log日志配置路径,如果有,查看下日志目录是否存在,日志目录权限要确保是mysql启动用户权限.

MYSQL实例(7)selinux惹的祸,如果是centos系统,默认会开启selinux

MYSQL实例闭它,打开/etc/selinux/config,把SELINUX=enforcing改为SELINUX=disabled后存盘退出重启机器试试.

MYSQL实例(8)重新初始化mysql数据试试

MYSQL实例切换到mysql的安装目录下

MYSQL实例./scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql/data --user=mysql

MYSQL实例－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

MYSQL实例使用mysql服务端授权的信息登录mysql,报错如下：

MYSQL实例ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.14' (111)

MYSQL实例可能的原因有：

MYSQL实例1)可能网络连接问题,远程ping 192.168.1.14,能ping通,排除此情况；

MYSQL实例2)mysql服务端192.168.1.14的iptables里做了3306端口连接的白名单限制；

MYSQL实例3)mysql服务端192.168.1.14的my.cnf文件里配置了bind_address地址绑定,不允许本机连接；

MYSQL实例4)mysql服务端192.168.1.14的my.cnf文件里配置了skip_networking,这使用MySQL只能通过本机Socket连接(socket连接也是

MYSQL实例本地连接的默认方式),放弃对TCP/IP的监听；

MYSQL实例5)排查DNS解析问题,检查mysql服务端192.168.1.14的my.cnf文件里是否设置了skip_name_resolve.这个参数加上后,不支

MYSQL实例持主机名的连接方式.

MYSQL实例6)排查--port问题,有可能mysql服务端192.168.1.14的MySQL port不是默认3306,比如是3307端口,这样,远程连接的时候要加上--port=3307

MYSQL实例7)排查用户和暗码问题,其实用户和暗码的错误,不会出现111的,所以排除用户暗码问题

MYSQL实例ERROR 1045 (28000): Access denied for user 'root'@'XXXX' (using password: YES)

MYSQL实例以上这篇浅谈mysql暗码遗忘和登陆报错的问题就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持编程之家PHP.

编程之家培训学院每天发布《Mysql学习浅谈mysql密码遗忘和登陆报错的问题》等实战技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培养人才。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!