mysql主從配置失敗，主從通訊失敗

標籤：verify   mys   ash   ide   false   word   waiting   replicat   data   

配置mysql主從的時候，檢查slave狀態，發現報錯資訊，

Error 'The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement' on query.

mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.100.100                  Master_User: repl                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: BP.000001          Read_Master_Log_Pos: 1047               Relay_Log_File: BP2-relay-bin.000002                Relay_Log_Pos: 276        Relay_Master_Log_File: BP.000001             Slave_IO_Running: Yes            Slave_SQL_Running: No              Replicate_Do_DB:          Replicate_Ignore_DB:           Replicate_Do_Table:       Replicate_Ignore_Table:      Replicate_Wild_Do_Table:  Replicate_Wild_Ignore_Table:                   Last_Errno: 1290                   Last_Error: Error 'The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement' on query. Default database: ''. Query: 'GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.100.101' IDENTIFIED BY PASSWORD '*137BC20D2DD742D4394762A7F009AE373961DF0D''                 Skip_Counter: 0          Exec_Master_Log_Pos: 120              Relay_Log_Space: 1374              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: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:               Last_SQL_Errno: 1290               Last_SQL_Error: Error 'The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement' on query. Default database: ''. Query: 'GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.100.101' IDENTIFIED BY PASSWORD '*137BC20D2DD742D4394762A7F009AE373961DF0D''  Replicate_Ignore_Server_Ids:             Master_Server_Id: 100                  Master_UUID: 274d9644-7398-11e8-8f4c-000c29a272ec             Master_Info_File: /data/mysql/master.info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State:           Master_Retry_Count: 86400                  Master_Bind:      Last_IO_Error_Timestamp:     Last_SQL_Error_Timestamp: 180725 10:41:22               Master_SSL_Crl:           Master_SSL_Crlpath:           Retrieved_Gtid_Set:            Executed_Gtid_Set:                Auto_Position: 01 row in set (0.00 sec)

分析：

mysql驗證失敗-->密碼不對-->排除，因為在my.cnf配置了skip-grant跳過密碼驗證配置參數

         -->設定檔-->是否skip-grant參數影響了主與從之間的通訊-->去掉#skip-grant參數後解決

解決方案：

把mysql設定檔my.cnf的skip-grant參數刪掉，或者注釋掉即可。

[mysqld]datadir=/data/mysqlsocket=/tmp/mysql.sockserver-id=101#skip-grant

mysql主從配置失敗，主從通訊失敗