mysql version < 5.7
- step1 停止服务(测试我没stop也成功了)
/etc/init.d/mysqld stop
- step2 修改配置,去除登录验证【不安全,任意用户都可以访问数据库】
vi /etc/my.cnf
//在[mysqld]的段中加上一句:
skip-grant-tables
//有的已存在,直接打开#注释即可
- step3 重启mysql
service mysqld restart
- step4 登录并重置mysql密码
/usr/bin/mysql 或mysql
mysql> USE mysql ;
mysql> UPDATE user SET Password = password('newpassword') WHERE User = 'root' ;
mysql> flush privileges ; //必须要flush一下,亲测不flush不生效
注意:
有时候登不上,请查看你默认登录的是localhost还是127.0.0.1用户,有可能默认登录的是localhost是没有密码,而127.0.0.1用户有密码。此时可通过mysql -h 有密码且正确的host -u[NAME] -p[PASSWORD]
登录修改user重置密码-
其他:
更新root用户的操作权限,打开权限:
update user set Select_priv = 'Y' where user='root';
update user set Insert_priv = 'Y'where user='root';
update user set Update_priv = 'Y'where user='root';
update user set Delete_priv = 'Y'where user='root';
update user set Create_priv = 'Y'where user='root';
update user set Drop_priv = 'Y'where user='root';
update user set Reload_priv = 'Y'where user='root';
update user set Shutdown_priv = 'Y'where user='root';
update user set Process_priv = 'Y'where user='root';
update user set File_priv = 'Y'where user='root';
update user set Grant_priv = 'Y'where user='root';
update user set References_priv ='Y'where user='root';
update user set Index_priv ='Y'where user='root';
update user set Alter_priv ='Y'where user='root';
update user set Show_db_priv ='Y'where user='root';
update user set Super_priv ='Y'where user='root';
update user set Create_tmp_table_priv ='Y'where user='root';
update user set Lock_tables_priv ='Y'where user='root';
update user set Execute_priv ='Y'where user='root';
update user set Repl_slave_priv ='Y'where user='root';
update user set Repl_client_priv ='Y'where user='root';
update user set Create_view_priv ='Y'where user='root';
update user set Show_view_priv ='Y'where user='root';
update user set Create_routine_priv ='Y'where user='root';
update user set Alter_routine_priv ='Y'where user='root';
update user set Create_user_priv ='Y'where user='root';
mysql 5.7.25
5.7版本mysql user表没有password字段了
,无法使用update user实现改密码,可如下操作
mysql> set password for 用户名@host = password(‘新密码’);
eg: set password for root@localhost = password(’’)
如果修改还不成功,请对准版本号参考 mysql5.7重置密码
2019.02.25更新
服务器有台机子,库密码被测试改了,测试请假了可我急用,准备按照以前的老方法,加个用户或直接改密码,结果失败了。步骤如下:
1.改配置文件,免密登录
vi /etc/my.cnf
向mysqld下加入skip-grant-tables
service mysqld restart
2.登录修改
mysql
后面无论是
set password for 用户名@host = password('新密码');
还是
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
都会提示在skip-grant-tables下无法更改
最终解决:
1.修改配置,免密登录,同上
2.查看user表
mysql
use mysql;
select * from user;
3.手动拷贝一条记录,修改用户名后插入user
拷贝一个root记录到文本编辑器(字段比较多,用替换工具替换|
变成,
或者'
),修改User名和拼凑成insert语句 插入
insert into user(‘jyjin’,…所有字段) values(…所有root字段值);
发现插入成功,最后在update修改authentication_string
4.通过update语句修改authentication_string实现改密码
update user set authentication_string = password(‘123456’) where user = ‘jyjin’; //这里jyjin是唯一的
flush privileges;
5.还原配置
还原my.cnf注释掉skip-grant-tables;
service mysqld restart
6.重启
mysql -ujyjin -p123456
登录成功!