Mysql
複製中的主/從 MySQL 錯誤
我已經在兩個獨立的數據庫之間設置了主/從，並開始正常工作，直到從出現錯誤並且不同步。
這是奴隸的狀態：
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+--+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+--+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+--------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+--------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+----+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+----+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+ | Slave_IO_State | Master_Host | 主_使用者 | 主埠 | 連接重試 | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | 複製_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | 最後_錯誤 | 跳過計數器 | Exec_Master_Log_Pos | Relay_Log_Space | 直到_條件 | 直到_Log_File | 直到_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_文件 | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_延遲 | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | 自動定位 | Replicate_Rewrite_DB | 頻道名稱 | Master_TLS_Version | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_延遲 | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | 自動定位 | Replicate_Rewrite_DB | 頻道名稱 | Master_TLS_Version | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_延遲 | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | 自動定位 | Replicate_Rewrite_DB | 頻道名稱 | Master_TLS_Version | | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_延遲 | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | 自動定位 | Replicate_Rewrite_DB | 頻道名稱 | Master_TLS_Version | | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_延遲 | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | 自動定位 | Replicate_Rewrite_DB | 頻道名稱 | Master_TLS_Version | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | 自動定位 | Replicate_Rewrite_DB | 頻道名稱 | Master_TLS_Version | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | 自動定位 | Replicate_Rewrite_DB | 頻道名稱 | Master_TLS_Version | +----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+----+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+----+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+ | 等待master發送事件 | 192.168.1.133 | 奴隸使用者 | 3306 | 60 | mysql-bin.000033 | 154 | iMac-de-MAVA-relay-bin.000002 | 320 | mysql-bin.000001 | 是 | 沒有 | | | | | | | 第1396章 查詢時出現錯誤“'fetchers'@'localhost''的操作 CREATE USER 失敗。預設數據庫：''。查詢：'CREATE USER 'fetchers'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*A516E88562C2C5E0D1EA9875F2910B36584C217A'' | 0 | 第1166章 5614936 | 無 | | 0 | 沒有 | | | | | | 空 | 沒有 | 0 | | 第1396章 查詢時出現錯誤“'fetchers'@'localhost''的操作 CREATE USER 失敗。預設數據庫：''。查詢：'CREATE USER 'fetchers'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*A516E88562C2C5E0D1EA9875F2910B36584C217A'' | | 1 | aae7157c-8bf4-11e6-a79a-4ccc6a4f12cd | /usr/local/mysql-5.7.15-osx10.11-x86_64/data/master。資訊 | 0 | 空 | | 86400 | | | 161007 13:37:26 | | | | | 0 | | | | +----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+----+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+----+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+ 1 行成組（0,00 秒）問題是我只需要同步數據庫中的一些表，但似乎嘗試同步所有表。
如何避免複製繼續工作的錯誤？
mysql> 顯示從屬狀態\G; ****************************** 1. 行 ************************ ******* Slave_IO_State：等待主機發送事件 Master_Host：192.168.1.133 Master_User：slave_user 主埠：3306 連接重試：60 Master_Log_File：mysql-bin.000034 Read_Master_Log_Pos：68326 Relay_Log_File: iMac-de-MAVA-relay-bin.000002 Relay_Log_Pos：320 Relay_Master_Log_File：mysql-bin.000001 Slave_IO_Running：是 Slave_SQL_Running：否 Replicate_Do_DB： Replicate_Ignore_DB： Replicate_Do_Table： Replicate_Ignore_Table： Replicate_Wild_Do_Table： Replicate_Wild_Ignore_Table： Last_Errno: 1396 Last_Error：查詢時出現錯誤“'fetchers'@'localhost''操作 CREATE USER 失敗。預設數據庫：''。查詢：'CREATE USER 'fetchers'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*A516E88562C2C5E0D1EA9875F2910B36584C217A'' 跳過計數器：0 Exec_Master_Log_Pos：1166 中繼日誌空間：5812332 直到_條件：無 直到_Log_File： 直到_Log_Pos：0 Master_SSL_Allowed：否 Master_SSL_CA_文件： Master_SSL_CA_Path： Master_SSL_Cert： Master_SSL_Cipher： Master_SSL_Key： Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert：否 Last_IO_Errno: 0 Last_IO_Error： Last_SQL_Errno：1396 Last_SQL_Error：查詢時出現錯誤“'fetchers'@'localhost''操作 CREATE USER 失敗。預設數據庫：''。查詢：'CREATE USER 'fetchers'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*A516E88562C2C5E0D1EA9875F2910B36584C217A'' Replicate_Ignore_Server_Ids： Master_Server_Id：1 Master_UUID：aae7157c-8bf4-11e6-a79a-4ccc6a4f12cd Master_Info_File：/usr/local/mysql-5.7.15-osx10.11-x86_64/data/master.info SQL_延遲：0 SQL_Remaining_Delay：空 Slave_SQL_Running_State： Master_Retry_Count：86400 主綁定： Last_IO_Error_Timestamp： Last_SQL_Error_Timestamp：161007 13:37:26 Master_SSL_Crl： Master_SSL_Crlpath： Retrieved_Gtid_Set： Executed_Gtid_Set： 自動位置：0 Replicate_Rewrite_DB： 頻道名稱： Master_TLS_Version： 1 行成組（0,00 秒） 錯誤： 未指定查詢
您發布的奴隸狀態說
查詢時出現錯誤“‘fetchers’@’localhost’‘的操作 CREATE USER 失敗。預設數據庫：’’。查詢：‘CREATE USER ‘fetchers’@’localhost’ IDENTIFIED WITH ‘mysql_native_password’ AS ‘*A516E88562C2C5E0D1EA9875F2910B36584C217A’’
建議 #1
有些人建議執行 FLUSH PRIVILEGES。所以，在你的情況下，那將是
STOP SLAVE; FLUSH PRIVILEGES; START SLAVE;如果錯誤仍然出現在從站上，您將不得不執行
STOP SLAVE; DROP USER 'fetchers'@'localhost' START SLAVE;在奴隸上。
MySQL 5.7 可能已經在 Master 上生成了一個警告，並在 Slave 上傳遞了它。MySQL 5.7 的 GRANT 操作變得更加嚴格。以後請先嘗試創建使用者，再使用
ALTER USER設置密碼。建議 #2
使用者
'fetchers'@'localhost'必須已經存在於從站上如果 MySQL 5.7 在 Master 和 Slave 上，你應該執行
CREATE USER IF NOT EXISTS 'fetchers'@'localhost'
'fetchers'@'localhost'如果已經存在，這將什麼都不做。複製將從那裡開始。