MySQL双主热备问题处理

最新推荐文章于 2025-01-13 00:55:00 发布

weixin_34392435

最新推荐文章于 2025-01-13 00:55:00 发布

阅读量171

点赞数

文章标签：数据库 shell 运维

本文介绍了MySQL主从复制过程中常见的几种错误情况及其解决方案，包括因服务器ID相同导致的复制失败、权限不足引发的问题以及连接失败的情况。同时提供了具体的操作步骤以帮助读者快速定位并解决问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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: NULL
Master_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 read
mysql> 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: NULL
Master_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