mysql -1045

-- 在配置mysql -mmm 架构时，配置 master- to - master 时，  master B 节点 一直报错 1045， 按理这样的错不应该了，在记忆中这样的报错 主要存在 replicate user  密码错误所致，

    但是，一般配置时，个人有个小小的习惯就是，把命令先通过notepad++ 写好，然后条条复制到服务器中去。

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 10.118.242.215
                  Master_User: mmm
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: bin.000012
          Read_Master_Log_Pos: 288
               Relay_Log_File: relay.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: bin.000012
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: information_schema,performance_schema,mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 288
              Relay_Log_Space: 120
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master 'mmm@10.118.242.215:3308' - retry-time: 60  retries: 2
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 215
                  Master_UUID: 5bc54365-5b59-11e5-a132-005056aa2157
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 200228 14:52:46
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)


ERROR: 
No query specified

--

[mysql@mysql05 ~]$ perror 1045
MySQL error code 1045 (ER_ACCESS_DENIED_ERROR): Access denied for user '%-.48s'@'%-.64s' (using password: %s)

-- 一般的方法是：

stop slave

然后重置 一下：

mysql> CHANGE MASTER TO
    -> MASTER_HOST='10.118.242.215',
    -> MASTER_USER='mmm',
    -> MASTER_PASSWORD='mmm123',
    -> MASTER_PORT=3308,     -- 这里如果不写 port 表示默认端口3306
    -> MASTER_LOG_FILE='bin.000012',
    -> MASTER_LOG_POS=288;
Query OK, 0 rows affected, 2 warnings (0.01 sec)


mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

--结果还是一样：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 10.118.242.215
                  Master_User: mmm
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: bin.000012
          Read_Master_Log_Pos: 288
               Relay_Log_File: relay.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: bin.000012
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: information_schema,performance_schema,mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 288
              Relay_Log_Space: 120
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master 'mmm@10.118.242.215:3308' - retry-time: 60  retries: 1
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 215
                  Master_UUID: 5bc54365-5b59-11e5-a132-005056aa2157
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 200228 14:51:46
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)


ERROR: 
No query specified

--然后排查 master A / B 节点的系统  iptables  和 selinux  是否关闭：

--[root@mysql05 ~]# iptables -L
Chain INPUT (policy ACCEPT)
target     prot opt source               destination         


Chain FORWARD (policy ACCEPT)
target     prot opt source               destination         


Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination 

[root@mysql05 ~]# cat /etc/selinux/config |head -8


# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these two values:

最后通过 show slave status\G;   报错依旧：

--既然密码出现问题，那看看mysql.user 这表的信息怎样：

mysql> select host,user,password from user;
+-------------+--------+-------------------------------------------+
| host        | user   | password                                  |
+-------------+--------+-------------------------------------------+
| localhost   | root   | *F861720E101148897B0F5239DB926E756B1C28B3 |
| sfpay-muban | root   | *F861720E101148897B0F5239DB926E756B1C28B3 |
| 127.0.0.1   | root   | *F861720E101148897B0F5239DB926E756B1C28B3 |
| ::1         | root   | *F861720E101148897B0F5239DB926E756B1C28B3 |
| %           | recopy | *E6F49F9DDF4EF3398C05CAAC1DDBA11D0A5E6281 |
| %           | root   | *F861720E101148897B0F5239DB926E756B1C28B3 |
| 10.118.%    | query  | *36FB4C5713701C2170E456A084E08BB49E9BBE80 |
| 10.118.     | mmm    | *A4D914D10D6802C301270012AA25F91AA6EDAF8E |
| 10.118.%    | mmm    | *A4D914D10D6802C301270012AA25F91AA6EDAF8E |
+-------------+--------+-------------------------------------------+
9 rows in set (0.00 sec)

--貌似发现了点端倪，这应该是问题的出发点所致：

--调整处理：

mysql> delete from user where host='10.118.';
Query OK, 1 row affected (0.01 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user,password from user;
+-------------+--------+-------------------------------------------+
| host        | user   | password                                  |
+-------------+--------+-------------------------------------------+
| localhost   | root   | *F861720E101148897B0F5239DB926E756B1C28B3 |
| sfpay-muban | root   | *F861720E101148897B0F5239DB926E756B1C28B3 |
| 127.0.0.1   | root   | *F861720E101148897B0F5239DB926E756B1C28B3 |
| ::1         | root   | *F861720E101148897B0F5239DB926E756B1C28B3 |
| %           | recopy | *E6F49F9DDF4EF3398C05CAAC1DDBA11D0A5E6281 |
| %           | root   | *F861720E101148897B0F5239DB926E756B1C28B3 |
| 10.118.%    | query  | *36FB4C5713701C2170E456A084E08BB49E9BBE80 |
| 10.118.%    | mmm    | *A4D914D10D6802C301270012AA25F91AA6EDAF8E |
+-------------+--------+-------------------------------------------+
8 rows in set (0.00 sec)

---在看看结果：

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.118.242.215
                  Master_User: mmm
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: bin.000012
          Read_Master_Log_Pos: 581
               Relay_Log_File: relay.000002
                Relay_Log_Pos: 277
        Relay_Master_Log_File: bin.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: information_schema,performance_schema,mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 581
              Relay_Log_Space: 440
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 215
                  Master_UUID: 5bc54365-5b59-11e5-a132-005056aa2157
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)


ERROR: 
No query specified


---  ok 了， 看来这不知何时敲了这一行命令，看来，细心是关键：