排障集合———忘记原密码,如何重新修改MySQL登入密码 【Redis】主从复制 网易2021批笔试题解 msf工具之木马程序制作以及伪装 【JAVA】滴滴-2021校招在线笔试-DE数据开发试卷-0913 机器学习13 -- 无监督学习之自监督 2020年全国数学建模大赛 B题部分代码和模型的建立 jumpserver安装(centos) Redis 知识点总结 通过RabitMQ实现分布式事务 高焕堂《嵌入式UML设计》读书笔记_第一章 2020年中国5G在物联网行业:中国通信运营商5G投资达1.23万亿元(可下载) 面试题精选:数据伪造 繁忙的都市(并查集、Kruskal) BFS 力扣 200.岛屿数量 深度操作系统Deepin V20正式版 2020-09-13 【OS】Bankers Algorithm 用于调用的参数太少/写入位置时发生冲突/检测到无效的异常处理程序例程 后渗透之各种维持权限的后门原理即利用 MIPS Branch Target Buffer动态分支预测(BTB) Oracle实战优化:INSERT ALL关键字的应用 Linux中MySQL数据库的使用②-----数据的基本操作 理论+实验——MySQL备份与恢复 MySQL常用数据库函数 MySQL 备份与恢复(完全备份恢复--增量备份恢复+案例演示) MySQL之基础总结部分 Oracle实战优化:递归+分析函数+OLAP函数的应用 Linux中MySQL数据库的使用③-----编码和基本数据类型 理论+实验:MySQL备份与恢复(完整备份、增量备份) “数”聚永川 “智”引未来——永川区大数据智能化产业发展强劲 从一款防疫App感受新加坡大数据智能化气息 “数”聚永川“智”引未来——永川区大数据智能化产业发展强劲 从连接量变到数据质变 物联网将二次爆发 重磅发布!猎芯半导体首创全球最小支持5G物联网的多模多频射频PA芯片 从精准授信到助企惠民,江苏银行物联网金融派上大用场 Python Selenium UI自动化_WebDriver元素_8大定位方式+总结(持续更新完善) Python中的继承、抽象基类和接口 Datawhale学习笔记【阿里云天池 金融风控-贷款违约预测】task1 赛题理解 Pytorch - torchvision计算机视觉工具库 linux 重点笔记 Ubuntu18.04安装ROS Melodic(一路到站型) 小甲鱼笔记:数据结构——线性表(一)线性表的顺序存储结构,线性表顺序存储结构的增,删,插入元素操作 实战比特币脚本编程(1) JAVA WEB DAY 01_Tomcat & Servlet Java基础算法之堆排序(Heap Sort) synchronized批量重偏向与批量撤销 终于等到了!阿里P8历时九个月整理,Java面试宝典,核心知识点笔记在此 “数字心脏”动态解析消费密码,国家级消费市场大数据联合实验室在上海先行先试 全世界运行着大约230亿台物联网设备,安全问题如何解?
您的位置:首页 >运维 >

排障集合———忘记原密码,如何重新修改MySQL登入密码

忘记密码,无法登入数据库

root@localhost ~]# mysql -uroot -p//密码输入错误,无法登入Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)[root@localhost ~]# mysqladmin -u root -p password '123456'//忘记原始密码,也无法靠mysqladmin修改密码方式登入Enter password: mysqladmin: connect to server at 'localhost' failederror: 'Access denied for user 'root'@'localhost' (using password: YES)'

解决办法

[root@localhost ~]# vi /etc/my.cnf //修改配置[mysqld]……省略部分skip-grant-tables //插入空授权登入配置[root@localhost ~]# systemctl restart mysqld //重启MySQL[root@localhost ~]# mysql -uroot -pEnter password: //直接回车,登入成功Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.7.20 Source distribution……省略部分mysql> mysql> desc mysql.user;//查看表结构,找到密码存放字段,5.0版本在authentication_string字段中+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Field| Type| Null | Key | Default | Extra |+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Host | char(60)| NO | PRI | | || User | char(32)| NO | PRI | | || Select_priv| enum('N','Y') | NO | | N | || Insert_priv| enum('N','Y') | NO | | N | || Update_priv| enum('N','Y') | NO | | N | || Delete_priv| enum('N','Y') | NO | | N | || Create_priv| enum('N','Y') | NO | | N | || Drop_priv| enum('N','Y') | NO | | N | || Reload_priv| enum('N','Y') | NO | | N | || Shutdown_priv| enum('N','Y') | NO | | N | || Process_priv | enum('N','Y') | NO | | N | || File_priv| enum('N','Y') | NO | | N | || Grant_priv | enum('N','Y') | NO | | N | || References_priv| enum('N','Y') | NO | | N | || Index_priv | enum('N','Y') | NO | | N | || Alter_priv | enum('N','Y') | NO | | N | || Show_db_priv | enum('N','Y') | NO | | N | || Super_priv | enum('N','Y') | NO | | N | || Create_tmp_table_priv| enum('N','Y') | NO | | N | || Lock_tables_priv | enum('N','Y') | NO | | N | || Execute_priv | enum('N','Y') | NO | | N | || Repl_slave_priv| enum('N','Y') | NO | | N | || Repl_client_priv | enum('N','Y') | NO | | N | || Create_view_priv | enum('N','Y') | NO | | N | || Show_view_priv | enum('N','Y') | NO | | N | || Create_routine_priv| enum('N','Y') | NO | | N | || Alter_routine_priv | enum('N','Y') | NO | | N | || Create_user_priv | enum('N','Y') | NO | | N | || Event_priv | enum('N','Y') | NO | | N | || Trigger_priv | enum('N','Y') | NO | | N | || Create_tablespace_priv | enum('N','Y') | NO | | N | || ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | || ssl_cipher | blob| NO | | NULL| || x509_issuer| blob| NO | | NULL| || x509_subject | blob| NO | | NULL| || max_questions| int(11) unsigned| NO | | 0 | || max_updates| int(11) unsigned| NO | | 0 | || max_connections| int(11) unsigned| NO | | 0 | || max_user_connections | int(11) unsigned| NO | | 0 | || plugin | char(64)| NO | | mysql_native_password | || authentication_string| text| YES| | NULL| || password_expired | enum('N','Y') | NO | | N | || password_last_changed| timestamp | YES| | NULL| || password_lifetime| smallint(5) unsigned| YES| | NULL| || account_locked | enum('N','Y') | NO | | N | |+------------------------+-----------------------------------+------+-----+-----------------------+-------+45 rows in set (0.00 sec)mysql> select host,user,authentication_string from mysql.user;//查看host,user,authentication_string上字段的数据+-----------+---------------+-------------------------------------------+| host| user| authentication_string |+-----------+---------------+-------------------------------------------+| localhost | root| *6691484EA6B50DDDE1926A220DA01FA9E575C18A || localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || % | myadm | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C || localhost | myadm | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C || % | bbs | *6691484EA6B50DDDE1926A220DA01FA9E575C18A || localhost | bbs | *6691484EA6B50DDDE1926A220DA01FA9E575C18A || localhost | ltp_admin | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-----------+---------------+-------------------------------------------+8 rows in set (0.00 sec)mysql> select host,user,authentication_string from mysql.user where user='root' and host='localhost';//精准过滤出root用户的密码字段+-----------+------+-------------------------------------------+| host| user | authentication_string |+-----------+------+-------------------------------------------+| localhost | root | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |+-----------+------+-------------------------------------------+1 row in set (0.00 sec)mysql> update mysql.user set authentication_string=null where user='root' and host='localhost';Query OK, 1 row affected (0.01 sec)Rows matched: 1Changed: 1Warnings: 0mysql> select host,user,authentication_string from mysql.user where user='root' and host='localhost';+-----------+------+-----------------------+| host| user | authentication_string |+-----------+------+-----------------------+| localhost | root | NULL|+-----------+------+-----------------------+1 row in set (0.00 sec)mysql> \qBye[root@localhost ~]# vi /etc/my.cnf //修改配置[mysqld]……省略部分# skip-grant-tables //将这条免密登入注释掉[root@localhost ~]# systemctl restart mysqld//重启生效配置[root@localhost ~]# mysql -uroot -pEnter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)[root@localhost ~]# mysql -uroot -pEnter password://已经是空密码状态了,直接回车登入Welcome to the MySQL monitor.Commands end with ; or \g.……省略部分mysql> mysql> \q //退出MySQL ,可以重新设置密码了Bye[root@localhost ~]# mysqladmin -u root -p password '123456' //重新设置密码Enter password: mysqladmin: [Warning] Using a password on the command line interface can be insecure.Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.[root@localhost ~]# mysql -uroot -p //验证,使用新密码登入成功Enter password: Welcome to the MySQL monitor.Commands end with ; or \g.……省略部分mysql> 

郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。