多源复制遇到CREATE USER FAILED错误

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 warning (0.02 sec)
Rows matched: 10  Changed: 10  Warnings: 1

mydba@192.168.85.132,3306 [replcrash]> show warnings;
+---------+------+-------------------------------------------------------------------+
| Level   | Code | Message                                                           |
+---------+------+-------------------------------------------------------------------+
| Warning | 1681 | 'PASSWORD' is deprecated and will be removed in a future release. |
+---------+------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

View Code

Changed: 10，再细看把整张表给更新，傻眼+++

# 主库Rotate日志
mydba@192.168.85.132,3306 [replcrash]> flush binary logs;

# 查看主库用户信息
mydba@192.168.85.132,3306 [replcrash]> select user,host,authentication_string from mysql.user;
+---------------+--------------+-------------------------------------------+
| user          | host         | authentication_string                     |
+---------------+--------------+-------------------------------------------+
| root          | localhost    | *A4B6157319038724E3560894F7F932C8886EBFCF |
| mysql.session | localhost    | *A4B6157319038724E3560894F7F932C8886EBFCF |
| mysql.sys     | localhost    | *A4B6157319038724E3560894F7F932C8886EBFCF |
| repl          | 192.168.85.% | *A4B6157319038724E3560894F7F932C8886EBFCF |
| mydba         | 192.168.85.% | *A4B6157319038724E3560894F7F932C8886EBFCF |
| zst           | localhost    | *A4B6157319038724E3560894F7F932C8886EBFCF |
| resolve       | db%.zst.com  | *A4B6157319038724E3560894F7F932C8886EBFCF |
| restoree      | 192.168.85.% | *A4B6157319038724E3560894F7F932C8886EBFCF |
| monitor       | %            | *A4B6157319038724E3560894F7F932C8886EBFCF |
| test          | %            | *A4B6157319038724E3560894F7F932C8886EBFCF |
+---------------+--------------+-------------------------------------------+
10 rows in set (0.00 sec)

# 查看从库用户信息
mydba@192.168.85.133,3306 [replcrash]> select user,host,authentication_string from mysql.user;
+---------------+--------------+-------------------------------------------+
| user          | host         | authentication_string                     |
+---------------+--------------+-------------------------------------------+
| root          | localhost    | *A7E26519238B6EA2F943D5FAC3CD7812AD8F87E5 |
| mysql.session | localhost    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| repl          | 192.168.85.% | *A424E797037BF97C19A2E88CF7891C5C2038C039 |
| mydba         | 192.168.85.% | *A7E26519238B6EA2F943D5FAC3CD7812AD8F87E5 |
| zst           | localhost    | *4E832DC6A6F24719A68C1242068114AA77CA60D0 |
| resolve       | db%.zst.com  | *A7E26519238B6EA2F943D5FAC3CD7812AD8F87E5 |
| restoree      | 192.168.85.% | *A7E26519238B6EA2F943D5FAC3CD7812AD8F87E5 |
| monitor       | %            | *1975D095AC033CAF4E1BF94F7202A9BBFEEB66F1 |
| test          | %            | *0CB5F227B3E98395CA0C6F1427427E77ADF49F89 |
+---------------+--------------+-------------------------------------------+
10 rows in set (0.00 sec)

View Code

主库的密码update为同一个值，庆幸这些值不会应用到从库。此时查看复制正常，使用旧密码连接主库也正常

mydba@192.168.85.133,3306 [(none)]> pager cat | egrep 'Master_Log_File|Relay_Master_Log_File|Read_Master_Log_Pos|Exec_Master_Log_Pos|Running'
PAGER set to 'cat | egrep 'Master_Log_File|Relay_Master_Log_File|Read_Master_Log_Pos|Exec_Master_Log_Pos|Running''
mydba@192.168.85.133,3306 [(none)]> show slave status\G
              Master_Log_File: mysql-bin.000108
          Read_Master_Log_Pos: 3849
        Relay_Master_Log_File: mysql-bin.000108
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 3849
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
1 row in set (0.00 sec)

# 使用旧密码也还可以登录
C:\Users\Administrator>mysql -h192.168.85.132 -P3306 -umydba -p
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 5.7.19-log MySQL Community Server (GPL)

View Code

2.3、binlog

grant+update语句在主库的binlog

[root@ZST1 logs]# mysqlbinlog -v --base64-output=decode-rows mysql-bin.000108 |more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#171116 11:22:33 server id 1323306  end_log_pos 123 CRC32 0x4861f6e9    Start: binlog v 4, server v 5.7.19-log created 171116 11:22:33
# Warning: this binlog is either in use or was not closed properly.
# at 123
#171116 11:22:33 server id 1323306  end_log_pos 194 CRC32 0x4bf5e123    Previous-GTIDs
# 8ab82362-9c37-11e7-a858-000c29c1025c:1-69347
# at 194
==================== GRANT操作对应的日志Start ====================
#171116 11:28:19 server id 1323306  end_log_pos 259 CRC32 0x965501a5    GTID    last_committed=0        sequence_number=1       rbr_only=no
SET @@SESSION.GTID_NEXT= '8ab82362-9c37-11e7-a858-000c29c1025c:69348'/*!*/;
# at 259
#171116 11:28:19 server id 1323306  end_log_pos 493 CRC32 0xce77ebc8    Query   thread_id=5     exec_time=0     error_code=0
use `replcrash`/*!*/;
SET TIMESTAMP=1510802899/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
GRANT SELECT ON `sakila`.* TO 'test'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*0CB5F227B3E98395CA0C6F1427427E77ADF49F89'
/*!*/;
==================== GRANT操作对应的日志End ====================
# at 493
==================== UPDATE操作对应的日志Start ====================
#171116 11:34:36 server id 1323306  end_log_pos 558 CRC32 0xaa76365b    GTID    last_committed=1        sequence_number=2       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '8ab82362-9c37-11e7-a858-000c29c1025c:69349'/*!*/;
# at 558
#171116 11:34:36 server id 1323306  end_log_pos 635 CRC32 0x42f7046b    Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1510803276/*!*/;
BEGIN
/*!*/;
# at 635
#171116 11:34:36 server id 1323306  end_log_pos 807 CRC32 0x2f85fb49    Table_map: `mysql`.`user` mapped to number 4
# at 807
#171116 11:34:36 server id 1323306  end_log_pos 3771 CRC32 0xadc9af46   Update_rows: table id 4 flags: STMT_END_F
### UPDATE `mysql`.`user`
### WHERE
###   @1='localhost'
###   @2='root'
###   @3=2
###   @4=2
###   @5=2
###   @6=2
###   @7=2
###   @8=2
###   @9=2
###   @10=2
###   @11=2
###   @12=2
###   @13=2
###   @14=2
###   @15=2
###   @16=2
###   @17=2
###   @18=2
###   @19=2
###   @20=2
###   @21=2
###   @22=2
###   @23=2
###   @24=2
###   @25=2
###   @26=2
###   @27=2
###   @28=2
###   @29=2
###   @30=2
###   @31=2
###   @32=1
###   @33=''
###   @34=''
###   @35=''
###   @36=0
###   @37=0
###   @38=0
###   @39=0
###   @40='mysql_native_password'
###   @41='*A7E26519238B6EA2F943D5FAC3CD7812AD8F87E5'
###   @42=1
###   @43=1505715158
###   @44=NULL
###   @45=1
### SET
###   @1='localhost'
###   @2='root'
###   @3=2
###   @4=2
###   @5=2
###   @6=2
###   @7=2
###   @8=2
###   @9=2
###   @10=2
###   @11=2
###   @12=2
###   @13=2
###   @14=2
###   @15=2
###   @16=2
###   @17=2
###   @18=2
###   @19=2
###   @20=2
###   @21=2
###   @22=2
###   @23=2
###   @24=2
###   @25=2
###   @26=2
###   @27=2
###   @28=2
###   @29=2
###   @30=2
###   @31=2
###   @32=1
###   @33=''
###   @34=''
###   @35=''
###   @36=0
###   @37=0
###   @38=0
###   @39=0
###   @40='mysql_native_password'
###   @41='*A4B6157319038724E3560894F7F932C8886EBFCF'
###   @42=1
###   @43=1505715158
###   @44=NULL
###   @45=1
...
### UPDATE `mysql`.`user`
### WHERE
###   @1='%'
###   @2='test'
###   @3=1
###   @4=1
###   @5=1
###   @6=1
###   @7=1
###   @8=1
###   @9=1
###   @10=1
###   @11=1
###   @12=1
###   @13=1
###   @14=1
###   @15=1
###   @16=1
###   @17=1
###   @18=1
###   @19=1
###   @20=1
###   @21=1
###   @22=1
###   @23=1
###   @24=1
###   @25=1
###   @26=1
###   @27=1
###   @28=1
###   @29=1
###   @30=1
###   @31=1
###   @32=1
###   @33=''
###   @34=''
###   @35=''
###   @36=0
###   @37=0
###   @38=0
###   @39=0
###   @40='mysql_native_password'
###   @41='*0CB5F227B3E98395CA0C6F1427427E77ADF49F89'
###   @42=1
###   @43=1510802899
###   @44=NULL
###   @45=1
### SET
###   @1='%'
###   @2='test'
###   @3=1
###   @4=1
###   @5=1
###   @6=1
###   @7=1
###   @8=1
###   @9=1
###   @10=1
###   @11=1
###   @12=1
###   @13=1
###   @14=1
###   @15=1
###   @16=1
###   @17=1
###   @18=1
###   @19=1
###   @20=1
###   @21=1
###   @22=1
###   @23=1
###   @24=1
###   @25=1
###   @26=1
###   @27=1
###   @28=1
###   @29=1
###   @30=1
###   @31=1
###   @32=1
###   @33=''
###   @34=''
###   @35=''
###   @36=0
###   @37=0
###   @38=0
###   @39=0
###   @40='mysql_native_password'
###   @41='*A4B6157319038724E3560894F7F932C8886EBFCF'
###   @42=1
###   @43=1510802899
###   @44=NULL
###   @45=1
# at 3771
#171116 11:34:36 server id 1323306  end_log_pos 3849 CRC32 0x752ce098   Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1510803276/*!*/;
COMMIT
/*!*/;
==================== UPDATE操作对应的日志End ====================
# at 3849
#171116 12:41:52 server id 1323306  end_log_pos 3896 CRC32 0x7d007885   Rotate to mysql-bin.000109  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@ZST1 logs]#

View Code

grant+update语句在从库的binlog

[root@ZST2 logs]# mysqlbinlog -v --base64-output=decode-rows mysql-bin.000021 |more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#171116 11:24:29 server id 1333306  end_log_pos 123 CRC32 0x97380513    Start: binlog v 4, server v 5.7.19-log created 171116 11:24:29
# Warning: this binlog is either in use or was not closed properly.
# at 123
#171116 11:24:29 server id 1333306  end_log_pos 234 CRC32 0x0e28801c    Previous-GTIDs
# 8ab82362-9c37-11e7-a858-000c29c1025c:496-69347,
# 93f69708-9c39-11e7-b7f8-000c2900c99c:1-284
# at 234
==================== 本地GRANT操作对应的日志Start(误操作) ====================
#171116 11:27:31 server id 1333306  end_log_pos 299 CRC32 0x237b657c    GTID    last_committed=0        sequence_number=1       rbr_only=no
SET @@SESSION.GTID_NEXT= '93f69708-9c39-11e7-b7f8-000c2900c99c:285'/*!*/;
# at 299
#171116 11:27:31 server id 1333306  end_log_pos 533 CRC32 0xf6511861    Query   thread_id=3     exec_time=1     error_code=0
use `replcrash`/*!*/;
SET TIMESTAMP=1510802851/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
GRANT SELECT ON `sakila`.* TO 'test'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*0CB5F227B3E98395CA0C6F1427427E77ADF49F89'
/*!*/;
# at 533
#171116 11:28:07 server id 1333306  end_log_pos 598 CRC32 0x9631a777    GTID    last_committed=1        sequence_number=2       rbr_only=no
SET @@SESSION.GTID_NEXT= '93f69708-9c39-11e7-b7f8-000c2900c99c:286'/*!*/;
# at 598
#171116 11:28:07 server id 1333306  end_log_pos 696 CRC32 0xd826df1b    Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1510802887/*!*/;
drop user test@'%'
/*!*/;
==================== 本地GRANT操作对应的日志End(误操作) ====================
# at 696
==================== 复制GRANT操作对应的日志Start ====================
#171116 11:28:19 server id 1323306  end_log_pos 761 CRC32 0xa645c110    GTID    last_committed=2        sequence_number=3       rbr_only=no
SET @@SESSION.GTID_NEXT= '8ab82362-9c37-11e7-a858-000c29c1025c:69348'/*!*/;
# at 761
#171116 11:28:19 server id 1323306  end_log_pos 995 CRC32 0x4924dc5a    Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1510802899/*!*/;
GRANT SELECT ON `sakila`.* TO 'test'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*0CB5F227B3E98395CA0C6F1427427E77ADF49F89'
/*!*/;
==================== 复制GRANT操作对应的日志End ====================
# at 995
==================== 复制UPDATE操作对应的日志Start ====================
#171116 11:34:36 server id 1323306  end_log_pos 1060 CRC32 0xab7a1a57   GTID    last_committed=3        sequence_number=4       rbr_only=no
SET @@SESSION.GTID_NEXT= '8ab82362-9c37-11e7-a858-000c29c1025c:69349'/*!*/;
# at 1060
#171116 11:34:36 server id 1323306  end_log_pos 1137 CRC32 0x40d16e6e   Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1510803276/*!*/;
BEGIN
/*!*/;
# at 1137
#171116 11:34:36 server id 1323306  end_log_pos 1215 CRC32 0x464fab8c   Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1510803276/*!*/;
COMMIT
/*!*/;
==================== 复制UPDATE操作对应的日志End ====================
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@ZST2 logs]#

View Code

当前环境下，主库上的binlog等效于从库上的relay-log，在主库的binlog中我们看到grant语句前面有use `replcrash`;从库在应用此relay-log时，不会过滤其后面的DDL/DCL语句，所以grant语句应用到从库，从库的binlog也看到对应记录，间接表明从库应用了grant语句。
主库的binlog中的UPDATE `mysql`.`user`语句影响的数据在mysql库下，从库应用此relay-log时，会过滤这些语句，所在update不会应用到从库，从库的binlog可以看到GTID_NEXT= '8ab82362-9c37-11e7-a858-000c29c1025c:69349'是一个空事务(BEGIN;COMMIT)

三、如何修复mysql.user

前面的update语句把主库的mysql.user密码更新错了，怎么还原到更新前的值？我自己的环境本身主、从mysql库是保持一致的，而且设置了复制过滤，update操作不会应用到从库，因此可以借助从库来修复主库的mysql.user

3.1、利用select into outfile + awk

# 数据导入导出限制目录
mydba@192.168.85.133,3306 [replcrash]> show variables like 'secure_file_priv';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | /tmp/ |
+------------------+-------+
1 row in set (0.03 sec)
# 导出数据
mydba@192.168.85.133,3306 [replcrash]> select user,host,authentication_string from mysql.user into outfile '/tmp/mysql_user.sql';
Query OK, 10 rows affected (0.00 sec)
# 生成rollback语句(注意单引号)
[root@ZST2 logs]# cat /tmp/mysql_user.sql |awk '{print "update mysql.user set authentication_string='\''"$3"'\'' where user='\''"$1"'\'' and host='\''"$2"'\'';"}' >/tmp/roll_mysql_user.sql
[root@ZST2 logs]# cat /tmp/roll_mysql_user.sql
update mysql.user set authentication_string='*A7E26519238B6EA2F943D5FAC3CD7812AD8F87E5' where user='root' and host='localhost';
update mysql.user set authentication_string='*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' where user='mysql.session' and host='localhost';
update mysql.user set authentication_string='*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' where user='mysql.sys' and host='localhost';
update mysql.user set authentication_string='*A424E797037BF97C19A2E88CF7891C5C2038C039' where user='repl' and host='192.168.85.%';
update mysql.user set authentication_string='*A7E26519238B6EA2F943D5FAC3CD7812AD8F87E5' where user='mydba' and host='192.168.85.%';
update mysql.user set authentication_string='*4E832DC6A6F24719A68C1242068114AA77CA60D0' where user='zst' and host='localhost';
update mysql.user set authentication_string='*A7E26519238B6EA2F943D5FAC3CD7812AD8F87E5' where user='resolve' and host='db%.zst.com';
update mysql.user set authentication_string='*A7E26519238B6EA2F943D5FAC3CD7812AD8F87E5' where user='restoree' and host='192.168.85.%';
update mysql.user set authentication_string='*1975D095AC033CAF4E1BF94F7202A9BBFEEB66F1' where user='monitor' and host='%';
update mysql.user set authentication_string='*0CB5F227B3E98395CA0C6F1427427E77ADF49F89' where user='test' and host='%';
[root@ZST2 logs]#

View Code

将生成的脚本roll_mysql_user.sql应用到主库即可

3.2、MyISAM文件拷贝

# 拷贝文件
[root@ZST2 logs]# ll /data/mysql/mysql3306/data/mysql/user.*
-rw-r-----. 1 mysql mysql 10816 Oct 30 15:06 /data/mysql/mysql3306/data/mysql/user.frm
-rw-r-----. 1 mysql mysql  1260 Nov 16 11:28 /data/mysql/mysql3306/data/mysql/user.MYD
-rw-r-----. 1 mysql mysql  4096 Nov 16 11:28 /data/mysql/mysql3306/data/mysql/user.MYI
[root@ZST2 logs]# scp /data/mysql/mysql3306/data/mysql/user.* root@192.168.85.132:/data/mysql/mysql3306/data/mysql/

# 注意检查拷贝过去的文件chown
# kill -HUP
[root@ZST1 ~]# kill -HUP `pidof mysqld`

View Code

借助MyISAM引擎特性，注意修改文件属主信息

3.3、binlog2sql

解析主库上的binlog得到要闪回的位置，利用binlog2sql得到操作前的记录

[root@ZST1 ~]# cd /tools/binlog2sql/binlog2sql
[root@ZST1 binlog2sql]# python binlog2sql.py --flashback -h192.168.85.132 -P3306 -umydba -p'mysql5719' -dmysql -tuser --start-file='mysql-bin.000108' --start-position=635 --stop-position=3849

View Code

mysql.user字段较多，update以ROW格式记录在binlog中，生成的闪回语句较长~

四、总结及疑问

4.1、总结

开篇问题的原因是，提问者在Master1、Master2没有指定default database的情况下创建了同名用户，在从库设置复制过滤后启动复制，Master1、Master2上的create user语句都应用到从库，导致第二次create user failed. 因此在操作DDL/DCL时记得USE DBNAME;

4.2、疑问

• update权限表，在什么时候生效？
内存结构中的权限信息何时被更新：FLUSH PRIVILEGES会强行让MySQL更新Load到内存中的权限信息；GRANT、REVOKE或者CREATE USER和DROP USER操作会直接更新内存中的权限信息；重启MySQL会让MySQL完全从grant tables中读取权限信息。
内存结构中的权限信息更新之后对已经连接上的用户何时生效：对于Global Level的权限信息的修改，仅仅只有更改之后新建连接才会用到，已经连接上的session并不会受到影响。对于Database Level的权限信息的修改，只有当客户端请求执行了“use database_name”命令之后，才会在重新校验中使用到新的权限信息。对于Table Level和Column Level的权限，在下一次需要使用到该权限的Query被请求的时候生效。
本例中update mysql.user是属于Global Level，只有在FLUSH PRIVILEGES后，新建连接才会要求使用新密码，当前已连接上的session不受影响。
• 系统库(information_schema、performance_schema、sys)会复制吗？
• 多源复制Master存在同名数据库，会出现什么情况？
对于同名数据库，Slave上的数据会错乱，因为Master1、Master2对同名数据库的变更全部会应用到Slave，一旦它们操作相同对象，就会出现交错。

posted @ 2017-11-16 18:29 Uest 阅读(2806) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

	【作者】：醒嘞
	【出处】： http://www.cnblogs.com/Uest/
	【声明】：本文内容仅代表个人观点。如需转载请保留此段声明，且在文章页面明显位置给出原文链接！

多源复制遇到CREATE USER FAILED错误

一、问题描述

二、验证推断

2.1、create user

2.2、update 授权表

2.3、binlog

三、如何修复mysql.user

3.1、利用select into outfile + awk

3.2、MyISAM文件拷贝

3.3、binlog2sql

四、总结及疑问

4.1、总结

4.2、疑问

公告