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更新密码完成。