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

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

 

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

 

   重启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;

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值