mysql主主复制

mysql主主复制

150服务器和170服务器

配置170服务器

  • 修改配置文件

    cat /etc/my.cnf
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    server-id=2
    master-host=192.168.1.150
    master-user=slave
    master-password=123456
    master-connect-retry=60
    
    # 也开启binlog日志
    log-bin=mysqlslave-bin-log
    binlog-do-db=shopping
    binlog-ignore-db=mysql
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    
  • 创建一个授权用户

    mysql> grant replication slave on *.* to "slave170"@"192.168.1.150" identified by "123456";
    
  • 重启服务

    [[email protected] ~]# service mysqld restart
    

配置150服务器

  • 修改配置文件

    cat /etc/my.cnf
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    log-bin=mysqllog
    server-id=1
    binlog-do-db=shopping
    
    master-host=192.168.1.170
    master-user=slave170
    master-password=123456
    replicate-do-db=shopping
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    
  • 重启服务

    [[email protected] ~]# service mysqld restart
    
  • 查看150服务器的从配置状态,Slave_IO_RunningSlave_SQL_Running都为yes表示正常

    mysql> show slave status \G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.170
                      Master_User: slave170
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysqlslave-bin-log.000001
              Read_Master_Log_Pos: 257
                   Relay_Log_File: mysqld-relay-bin.000003
                    Relay_Log_Pos: 411
            Relay_Master_Log_File: mysqlslave-bin-log.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: shopping
    
  • 如果配置错误导致同步失败,可以尝试

    mysql> stop slave;
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> reset slave;
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.07 sec)