MySQL雙主熱備問題處理，mysql主熱備

MySQL雙主熱備問題處理，mysql主熱備

1. Slave_IO_Running: No

mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State:                  Master_Host: 192.16.10.21                  Master_User: cahms                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000003          Read_Master_Log_Pos: 20398               Relay_Log_File: mysqld-relay-bin.000001                Relay_Log_Pos: 4        Relay_Master_Log_File: mysql-bin.000003             Slave_IO_Running: No            Slave_SQL_Running: Yes              Replicate_Do_DB:          Replicate_Ignore_DB:           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: 20398              Relay_Log_Space: 106              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: 1593                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have                                equal MySQL server ids; these ids must be different for replication to                                work (or the --replicate-same-server-id option must be used on slave but                                this does not always make sense; please check the manual before using                               it).               Last_SQL_Errno: 0               Last_SQL_Error:1 row in set (0.00 sec)

(1) master and slave have equal MySQL server ids
【解決辦法】
修改/etc/my.cnf 下的server-id的值，確保master和slave的server-id不一樣即可；

(2) Error reading packet from server:

Access denied; you need the REPLICATION SLAVE privilege for this operation ( server_errno=1227)

【解決辦法】主伺服器給的複製許可權不夠，重新賦予許可權。

> grant replication slave on *.* to 'uname'@'對端ip' identified by 'password'；> flush privileges；

== e.g ==

> grant replication slave on *.* to 'root'@'192.16.10.21'     identified by 'root';> grant replication slave on *.* to 'root'@'192.16.10.23'   identified by 'root';

2. Slave_IO_State:

Waiting to reconnect after a failed master event readmysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting to reconnect after a failed master event read                  Master_Host: 192.16.10.231                  Master_User: cahms                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000003          Read_Master_Log_Pos: 106               Relay_Log_File: mysqld-relay-bin.000001                Relay_Log_Pos: 4        Relay_Master_Log_File: mysql-bin.000003             Slave_IO_Running: No            Slave_SQL_Running: Yes              Replicate_Do_DB:          Replicate_Ignore_DB:           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: 106              Relay_Log_Space: 106              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: 0               Last_SQL_Error:1 row in set (0.00 sec)

【解決辦法】

> grant replication slave on *.* to 'root'@'192.16.10.228'  identified by 'root';> grant replication slave on *.* to 'root'@'192.16.10.231'     identified by 'root';> flush privileges;

3.Another MySQL daemon already running with the same unix socket.

原因多個Mysql進程使用了同一個socket。 兩個方法解決：

第一個是立即關機 使用命令 shutdown -h now 關機，關機後在啟動，進程就停止了。

第二個直接把mysql.sock檔案改名即可。也可以刪除，推薦改名。
然後就可以啟動mysql了。

下面是國外原文

To prevent the problem from occurring, you must perform a graceful
shutdown of the server from the command line rather than powering off
the server.

# shutdown -h now

This will stop the running services before powering down the machine.
Based on Centos, an additional method for getting it back up again when you run into this problem is to move mysql.sock:

# mv /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock.bak# service mysqld start

Restarting the service creates a new entry called mqsql.sock

熱門推薦

• LNMP環境搭建——Nginx篇

• 在RHEL6.5中配置本地YUM源

• Ubuntu下Zabbix安裝及使用問題

• MySQL雙主熱備問題處理

• Rsync同步錯誤處理
• Fix Elementary Boot Screen (plymouth)
  After Installing Nvidia Drivers
• LNMP環境搭建——MySQL篇
• Shell Step by Step (3) —— Stdin & if
• Shell Step by Step (4) —— Cron & Echo

著作權聲明：本文為博主原創文章，未經博主允許不得轉載｜Copyright ©2011-2015, Kallen Ding, All Rights Reserved.