多源复制遇到CREATE USER FAILED错误

最新推荐文章于 2024-05-30 16:26:09 发布

a86793222

最新推荐文章于 2024-05-30 16:26:09 发布

阅读量1.4k

点赞数

文章标签： awk 数据库 python

原文链接：http://www.cnblogs.com/Uest/p/7845680.html

版权

本文分析了在多源复制过程中遇到CREATE USER FAILED错误的原因，重点探讨了binlog_format='STATEMENT'时的问题。通过验证推断，发现即使在忽略mysql库同步的情况下，某些操作仍可能被复制。提出了三种修复mysql.user的方法：select into outfile + awk、MyISAM文件拷贝和binlog2sql。最后总结了操作DDL/DCL时的注意事项，并提出了关于权限表更新和多源复制中同名数据库问题的疑问。

摘要由CSDN通过智能技术生成

MySQL Multi-Source Replication enables a replication slave to receive transactions from multiple sources simultaneously. Multi-source replication does not implement any conflict detection or resolution when applying the transactions, and those tasks are left to the application if required.

一、问题描述

多源复制，从库在开启复制之前，执行change replication filter replicate_ignore_db=(mysql); 按理应该忽略了mysql库的同步，为什么开启复制之后报错

Last_Error: Error 'Operation CREATE USER failed for 'repl'@'192.168.85.%'' on query. Default database: ''. Query: 'CREATE USER 'repl'@'192.168.85.%' IDENTIFIED WITH 'mysql_native_password' AS '*A424E797037BF97C19A2E88CF7891C5C2038C039''

首先怀疑使用了statement格式的日志(binlog_format='STATEMENT')，提问者没有说binlog_format。按照一贯思维，如果binlog_format='ROW'，它会检查变更数据所在的database是否匹配过滤选项；如果binlog_format='STATEMENT'，它会检查default database(use dbname)是否匹配过滤选项。
第一个推断：binlog_format='STATEMENT'，create user语句不是在mysql库下运行~

二、验证推断

2.1、create user

为了验证推断，在自己的一主一从环境(Gtid+Row)测试create user语句

# 从库复制过滤
mydba@192.168.85.133,3306 [(none)]> stop slave sql_thread;
Query OK, 0 rows affected (0.01 sec)

mydba@192.168.85.133,3306 [(none)]> change replication filter replicate_ignore_db=(mysql);
Query OK, 0 rows affected (0.00 sec)

mydba@192.168.85.133,3306 [(none)]> start slave sql_thread;
Query OK, 0 rows affected (0.01 sec)


# 主库Rotate日志
mydba@192.168.85.132,3306 [replcrash]> flush binary logs;
Query OK, 0 rows affected (0.12 sec)

mydba@192.168.85.132,3306 [replcrash]> show master status;
+------------------+----------+--------------+------------------+----------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                            |
+------------------+----------+--------------+------------------+----------------------------------------------+
| mysql-bin.000108 |      194 |              |                  | 8ab82362-9c37-11e7-a858-000c29c1025c:1-69347 |
+------------------+----------+--------------+------------------+----------------------------------------------+
1 row in set (0.00 sec)

mydba@192.168.85.132,3306 [replcrash]> select user,host from mysql.user;
+---------------+--------------+
| user          | host         |
+---------------+--------------+
| monitor       | %            |
| mydba         | 192.168.85.% |
| repl          | 192.168.85.% |
| restoree      | 192.168.85.% |
| resolve       | db%.zst.com  |
| mysql.session | localhost    |
| mysql.sys     | localhost    |
| root          | localhost    |
| zst           | localhost    |
+---------------+--------------+
9 rows in set (0.00 sec)


# 从库Rotate日志
mydba@192.168.85.133,3306 [replcrash]> flush logs;
Query OK, 0 rows affected (0.07 sec)

mydba@192.168.85.133,3306 [replcrash]> show master status;
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                        |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------------+
| mysql-bin.000021 |      234 |              |                  | 8ab82362-9c37-11e7-a858-000c29c1025c:1-69347,
93f69708-9c39-11e7-b7f8-000c2900c99c:1-284 |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mydba@192.168.85.133,3306 [replcrash]> select user,host from mysql.user;
+---------------+--------------+
| user          | host         |
+---------------+--------------+
| monitor       | %            |
| mydba         | 192.168.85.% |
| repl          | 192.168.85.% |
| restoree      | 192.168.85.% |
| resolve       | db%.zst.com  |
| mysql.session | localhost    |
| mysql.sys     | localhost    |
| root          | localhost    |
| zst           | localhost    |
+---------------+--------------+
9 rows in set (0.02 sec)


# 主库创建新用户(注意这里默认的db是replcrash)
mydba@192.168.85.132,3306 [replcrash]> grant select on sakila.* to 'test'@'%' identified by '1234qwer';
Query OK, 0 rows affected, 1 warning (0.06 sec)

View Code

从库查看也创建了'test'@'%'用户，binlog_format='ROW'，怎么把过滤的mysql库中的create user给传到复制上去了？！

Note
Only DML statements can be logged using the row format. DDL statements are always logged as statements, even when binlog_format=ROW. All DDL statements are therefore always filtered according to the rules for statement-based replication. This means that you must select the default database explicitly with a USE statement in order for a DDL statement to be applied.

前面的grant语句在用户不存在时会创建用户，其实这类DCL语句也是记录为STATEMENT格式。我们注意grant语句默认的db是replcrash，并不是replicate_ignore_db选项中的mysql(虽然grant最终改变的对象是在mysql)，所以这条grant语句不会过滤。
如果我们使用：use mysql;grant select on sakila.* to 'test'@'%';默认db变成mysql，符合replicate_ignore_db选项中的mysql过滤条件，这条语句不会被应用到Slave~
如果我们使用：use replcrash;update mysql.user... 由于是ROW格式下的DML操作，语句影响的数据在mysql库，符合replicate_ignore_db选项中的mysql过滤条件，这条语句不会被应用到Slave~

2.2、update 授权表

测试update时忘记where条件，把整个mysql.user的密码给更新了

# 更新用户密码(本意只更新'test'@'%'用户)
mydba@192.168.85.132,3306 [replcrash]> update mysql.user set authentication_string=password('1234');
Query OK, 10 rows affected, 1