Mysql重置ROOT密码

发表于 更新于 分类于 ， 阅读次数： Waline：

引言

常在河边走哪有不湿鞋！忘记 ROOT 密码也是很正常的，本文主要介绍忘记数据库各个版本的 root 密码时如何重置密码

正文

一般的思路是关闭实例后使用 --skip-grant-tables 参数重启MySQL，再重置密码

修改设置文件

1
vim /etc/my.cnf

在 [mysqld] 的位置添加

1
skip-grant-tables

重启服务

1
systemctl restart mysql

使用 空密码 登录

1
$ mysql -u root -p  # 提示输入密码时直接敲回车

MySQL

各个版本不一样

大于 5.7

修改

1
2
3
4
5
6
7
use mysql;
update user set authentication_string='' where user='root';
update user set plugin='mysql_native_password' where user='root';
flush privileges;
alter user 'root'@'localhost' identified by 'qwe123qwe';
flush privileges;
exit;

小于 5.7

修改

1
2
3
4
use mysql;
alter user 'root'@'localhost' identified by 'qwe123QWE...';
flush privileges;
exit;

保存，将配置文件的 skip-grant-tables 删除后，重启MySQL服务

1
systemctl restart mysql

使用新密码登录即可

脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
cat<<'EOF' >reset_mysql_password.sh 
#!/usr/bin/env bash
export PATH=$PATH:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:~/bin

# Check if user is root
if [ $(id -u) != "0" ]; then
    echo "Error: You must be root to run this script!"
    exit 1
fi

echo "+-------------------------------------------------------------------+"
echo "|           Usage: ./reset_mysql_password.sh                        |"
echo "+-------------------------------------------------------------------+"

if [ -s /usr/local/mariadb/bin/mysql ]; then
    DB_Name="mariadb"
    DB_Ver=`/usr/local/mariadb/bin/mysql_config --version`
elif [ -s /usr/local/mysql/bin/mysql ]; then
    DB_Name="mysql"
    DB_Ver=`/usr/local/mysql/bin/mysql_config --version`
else
    echo "MySQL/MariaDB not found!"
    exit 1
fi

while :;do
    DB_Root_Password=""
    read -p "Enter New ${DB_Name} root password: " DB_Root_Password
    if [ "${DB_Root_Password}" = "" ]; then
        echo "Error: Password can't be NULL!!"
    else
        break
    fi
done

echo "Stoping ${DB_Name}..."
/etc/init.d/${DB_Name} stop
echo "Starting ${DB_Name} with skip grant tables"
/usr/local/${DB_Name}/bin/mysqld_safe --skip-grant-tables >/dev/null 2>&1 &
sleep 5
echo "update ${DB_Name} root password..."
if echo "${DB_Ver}" | grep -Eqi '^8.0.|^5.7.|^10.[234].'; then
    /usr/local/${DB_Name}/bin/mysql -u root << EOF
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY '${DB_Root_Password}';
EOF
else
    /usr/local/${DB_Name}/bin/mysql -u root << EOF
update mysql.user set password = Password('${DB_Root_Password}') where User = 'root';
EOF
fi

if [ $? -eq 0 ]; then
    echo "Password reset succesfully. Now killing mysqld softly"
    if command -v killall >/dev/null 2>&1; then
        killall mysqld
    else
        kill `pidof mysqld`
    fi
    sleep 5
    echo "Restarting the actual ${DB_Name} service"
    /etc/init.d/${DB_Name} start
    echo "Password successfully reset to '${DB_Root_Password}'"
else
    echo "Reset ${DB_Name} root password failed!"
fi
EOF

赋予执行权限

1
chmod +x reset_mysql_password.sh

执行

1
./reset_mysql_password.sh

输入新密码即可

1
2
3
4
+-------------------------------------------------------------------+
|           Usage: ./reset_mysql_root_password.sh                   |
+-------------------------------------------------------------------+
Enter New mysql root password: