mysql常见错误之ERROR 1290、1840

前言：

重装mysql5.7.26数据库后，在修改root密码和导入数据时分别了error 1290,1840两个错误，特此记录一下处理过程

错误码1：ERROR 1290

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> alter user ‘root’@’%’ identified by ‘root#2019’;
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this 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
解决方法
mysql> update user set authentication_string=password(‘root2019’),password_expired=‘N’ where user=‘root’;

mysql> flush privileges;

错误码2：ERROR 1840

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

[root@yjgkserver25 data]# mysql -uroot -p </home/mysql/backup_all_db.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
[root@yjgkserver25 data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright © 2000, 2019, 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> show master status;
±----------------±---------±-------------±-----------------±-----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±----------------±---------±-------------±-----------------±-----------------------------------------+
| mybinlog.000003 | 194 | | | 58561766-b883-11eb-87c1-525400e8d78f:1-2 |
±----------------±---------±-------------±-----------------±-----------------------------------------+

解决方法
mysql> reset master;
mysql> exit
[root@yjgkserver25 data]# mysql -uroot -p </home/mysql/backup_all_db.sql

总结

1、因/etc/my.cnf配置文件的mysqld模块中加入skip-grant-tables参数，所以登录时不需要输入root密码，把root密码修改完成后，需要将该参数注释
2、mysql初始化成功后，在error.log日志里会生成root用户的临时密码，也可以用来初始登陆，即不需要在/etc/my.cnf添加skip-grant-tables参数
3、因重新初始化数据库并开启GTID，原来导出的备份文件包含了set @@GLOBAL.GTID_PURGED=xx，需要在新库执行reset master操作 将@@GLOBAL.GTID_EXECUTED清空才能导入，将备份文件里的set @@GLOBAL.GTID_PURGED=xx删除是不是也可以呢？