mysql忘记密码root
2015-05-21如果mysql密码忘记了，可以用skip-grant-tables参数无密码访问后修改密码。
1、vi my.cnf
在[mysqld]下增加一行
skip-grant-tables
2、重启mysql
/etc/init.d/mysqld restart
3、无密码登陆
mysql -uroot -p
4、修改root密码
use mysql
select user,password,host from user;
a)、set password for root@localhost = password('123');
running with the --skip-grant-tables option so it cannot execute this statement
b)、UPDATE user SET Password = password ( 'newpasswd' ) WHERE user = 'root' and host='localhost';
c)、mysqladmin -uroot -p123456 password 123
You cannot use 'password' command as mysqld runs
with grant tables disabled (was started with --skip-grant-tables).
Use: "mysqladmin flush-privileges password '*'" instead
d)、grant all privileges on 库名.表名 to '用户名'@'IP地址' identified by '密码'
grant all privileges on 库名.表名 to 'root'@'127.0.0.1' identified by '123456'
5、将my.cnf里的skip-grant-tables删除
6、mysql8忘记密码
a)、忘记密码
修改/etc/my.cnf增加
skip-grant-tables
b)、update user set authentication_string='' where user='root' ;
c)、systemctl restart mysqld
d)、无密码登陆mysql
mysql -uroot -e"alter USER '${item}user'@'${IP}' IDENTIFIED WITH mysql_native_password BY '${pw}';"
实例 cat forget_mysql_root_pw.sh
#!/bin/sh
##clear pw
sed -i '/mysql_native_password/a skip-grant-tables' /etc/my.cnf
systemctl restart mysqld
pw=123456
##add pw
mysql --connect-expired-password -uroot -Dmysql -e"alter USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '${pw}'"
mysql --connect-expired-password -uroot -Dmysql -e"FLUSH PRIVILEGES;"
mysqladmin -u root password $pw
##
cat << EOF > conn_root.sh
export MYSQL_PWD='${pw}'
mysql -u root -Dmysql
EOF