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

╰+哭是因爲堅強的太久メ 2022-10-07 13:51 32阅读 0赞

alMysql5.7更改用户密码，报错“Unknown trigger has an error in its body: 'Unknown system

**阿里云 RDS MySQL物理备份文件恢复到自建数据库**

参考文档：[https://help.aliyun.com/document\_detail/41817.htm?spm=a2c4g.11186623.2.7.2a164bd6aOrq8j\#concept-41817-zh][https_help.aliyun.com_document_detail_41817.htm_spm_a2c4g.11186623.2.7.2a164bd6aOrq8j_concept-41817-zh]

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，注释下配置文件中的my.conf “skip-grant-tables”，重新登录，输入新的密码。

到此mysql更新密码完成。

\[mysqld\]  
lower\_case\_table\_names=1  
**\#skip-grant-tables**  
  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x5ZnF5cg_size_16_color_FFFFFF_t_70][]

重启MYSL 服务

1.  **> Kill -9 PID**
2.  **> /usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/data/backup-my.cnf --user=mysql --datadir=/home/mysql/data &**
3.  **> mysql** **登录**
4.  **> mysql> update mysql.user set authentication\_string = password('techops') where user='root' and host='%';**
5.  **mysql> flush privileges;**

[https_help.aliyun.com_document_detail_41817.htm_spm_a2c4g.11186623.2.7.2a164bd6aOrq8j_concept-41817-zh]: https://help.aliyun.com/document_detail/41817.htm?spm=a2c4g.11186623.2.7.2a164bd6aOrq8j#concept-41817-zh
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x5ZnF5cg_size_16_color_FFFFFF_t_70]: /images/20221005/328f49e07433430ea7fee8008c4d505c.png