环境

  • A: 172.21.110.100
  • B:172.21.110.101
  • mysql5.6

创建同步用户

  • A:

    grant replication slave on *.* to 'sync'@'172.21.110.101' identified by '123456';
    flush privileges;
  • B:

    grant replication slave on *.* to 'sync'@'172.21.110.101' identified by '123456';
    flush privileges;

服务配置

  • A:

    # MASTER DB #
    #binlog-do-db                   = m2m
    binlog-ignore-db               = mysql,information_schema,performance_schema
    auto-increment-increment       = 2
    auto-increment-offset          = 1
    # SLAVE DB #
    #replicate-do-db                = m2m
    replicate-ignore-db            = mysql,information_schema,performance_schema
    log-slave-updates = ON
  • B:

    # MASTER DB #
    #binlog-do-db                   = m2m
    binlog-ignore-db               = mysql,information_schema,performance_schema
    auto-increment-increment       = 2
    auto-increment-offset          = 2
    # SLAVE DB #
    #replicate-do-db                = m2m
    replicate-ignore-db            = mysql,information_schema,performance_schema
    log-slave-updates = ON

查看2个服务器的日志状态

  • A:

    mysql> flush tables with read lock; #防止进入新的数据 
    Query OK, 0 rows affected (0.00 sec) 
    mysql> show master status\G;
    *************************** 1. row ***************************
               File: binlog.000003
           Position: 120
       Binlog_Do_DB: 
     Binlog_Ignore_DB: mysql,information_schema,performance_schema
    Executed_Gtid_Set: 
  • B:

    mysql> flush tables with read lock; #防止进入新的数据 
    Query OK, 0 rows affected (0.00 sec) 
    mysql> show master status\G;
    *************************** 1. row ***************************
               File: binlog.000003
           Position: 120
       Binlog_Do_DB: 
     Binlog_Ignore_DB: mysql,information_schema,performance_schema
    Executed_Gtid_Set: 

开启同步

  • A:

    CHANGE MASTER TO MASTER_HOST='172.21.110.101',MASTER_PORT=3306,MASTER_USER='sync',MASTER_PASSWORD='123456',MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=120;
  • B:

    CHANGE MASTER TO MASTER_HOST='172.21.110.100',MASTER_PORT=3306,MASTER_USER='sync',MASTER_PASSWORD='123456',MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=120;
  • 2台服务器都启动slave进程

    mysql> start slave; 
    Query OK, 0 rows affected (0.00 sec)

查看同步状态

  • 如存在以下状态,则表示设置成功

    mysql> show slave status\G; 
    *************************** 1. row *************************** 
    ... 
    ... 
    Slave_IO_Running: Yes 
    Slave_SQL_Running: Yes 
    ... 
    ... 

发表评论