Mysql5.7修改用户密码，报错“Unknown trigger has an error in its body: ‘Unknown system variable ‘maintain_user”

Mysql5.7更改用户密码，报错“Unknown trigger has an error in its body: 'Unknown system variable 'maintain_user”，这里是因为恢复数据后，直接通过“skip-grant-tables”进入Mysql修改用户密码，忽略了触发器造成的，报出上面的错误。

正常情况下，mysql5.7用户密码修改，口令为：

mysql> update mysql.user set authentication_string = password('新的密码') where user='用户名' and host='%';
mysql> flush privileges;

直接更改完退出，即可。

当出现“Unknown trigger has an error in its body: 'Unknown system variable 'maintain_user”时，是因为有触发器没有删除造成的，

查询并删除，

mysql> select trigger_schema,trigger_name from information_schema.triggers;
+----------------+----------------------------+
| trigger_schema | trigger_name               |
+----------------+----------------------------+
| sys            | sys_config_insert_set_user |
| sys            | sys_config_update_set_user |
+----------------+----------------------------+
2 rows in set (0.03 sec)
mysql> drop trigger sys.sys_config_insert_set_user;
Query OK, 0 rows affected (0.06 sec)

mysql> drop trigger sys.sys_config_update_set_user;
Query OK, 0 rows affected (0.02 sec)

再次执行密码更新操作。

mysql> update mysql.user set authentication_string = password('新的密码') where user='用户名' and host='%';
ERROR 1064 (42000): Unknown trigger has an error in its body: 'Unknown system variable 'maintain_user_list''
mysql> 

仍然报错，说明触发器没有删除彻底，找到触发器文件，彻底删除。

找到自己数据恢复的目录，查找以“.trg”为结尾的触发器文件，这里数据恢复目录为/home/mysql/data，执行find查找口令:

[root@localhost ~]# find /home/mysql/data/ -iname *.trg
/home/mysql/data/mysql/proxies_priv.TRG
/home/mysql/data/mysql/user.TRG

查询到2个触发器的文件，更改后缀名，

[root@localhost ~]# mv /home/mysql/data/mysql/user.TRG /home/mysql/data/mysql/user.TRG.back
[root@localhost ~]# mv /home/mysql/data/mysql/proxies_priv.TRG /home/mysql/data/mysql/proxies_priv.TRG.back

退出Mysql，重新登录进来，切换到mysql.user表，重新更新密码

[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update mysql.user set authentication_string = password('新的密码') where user='用户名' and host='%';
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

修改成功，退出mysql，注释下“skip-grant-tables”，重新登录，输入新的密码。

到此mysql更新密码完成。

---

关注我的公众号，更多干货，资源等你来一起学习。

---

 

本文地址：https://blog.csdn.net/best_luxi/article/details/107152085