一、 问题背景
给主库一个用户重置了一下密码,突然收到告警主从同步中断了...登上从库检查发现报错如下
mysql> show slave status\G;
...
Last_Errno: 1396
Last_Error: Error 'Operation ALTER USER failed for 'bakuser'@'localhost'' on query. Default database: ''. Query: 'ALTER USER 'bakuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*401A96j9D7DFE8E2931E4EB2471108769258A6BB3''
检查一下主从库用户情况,发现原来从库没有这个用户,无法执行重置密码的命令所以同步报错了。
二、 解决方法
查看主库该用户权限情况
mysql> select user,host from mysql.user;
+-----------+------------+
| user | host |
+-----------+------------+
| bakuser | localhost |
| mysql.sys | localhost |
| root | localhost |
+-----------+------------+
7 rows in set (0.00 sec)
mysql> show grants for bakuser@'localhost';
+--------------------------------------------------------+
| Grants for bakuser@localhost |
+--------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'bakuser'@'localhost' |
+--------------------------------------------------------+
1 row in set (0.00 sec)
在从库创建用户并授权
GRANT ALL PRIVILEGES ON *.* TO 'bakuser'@'localhost' IDENTIFIED BY 'xxx';
此时直接利用show slave status\G;检查会发现同步还是断的,报错跟之前一样。
还需要重新启动一下同步,再进行检查
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
同步恢复