Mysql master to master 配置实例

amwdnuff

Mysql master to master 配置实例

分别在2个数据库上面添加复制slave的权限：


GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO xxx@'192.168.39.%' IDENTIFIED BY 'xxx'


Master1的配置：


server-id = 1
auto-increment-increment = 2
auto-increment-offset = 1


master-user = xxx
master-password = xxx
master-host = 192.168.39.80
#master-connect-retry = 60
replicate-do-db = common
replicate-ignore-db = mysql,information_schema


log-bin=64-bin
sync_binlog = 1
binlog-do-db=common
binlog-ignore-db = mysql,information_schema
binlog_format=mixed




Master 2 的配置：


server-id = 2
auto-increment-increment = 2
auto-increment-offset = 2


master-user = xxx
master-password = xxx
master-host = 192.168.39.81
#master-connect-retry = 60
replicate-do-db = common
replicate-ignore-db = mysql,information_schema


log-bin = beta-bin
sync_binlog = 1
binlog-do-db = common
binlog-ignore-db = mysql,information_schema
binlog_format = mixed


查看master slave IO/SQL线程状态：


master 1


mysql> show master status\G
*************************** 1. row ***************************
            File: beta-bin.000005
        Position: 106
    Binlog_Do_DB: common
Binlog_Ignore_DB: mysql,information_schema
1 row in set (0.02 sec)




mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.39.81
                  Master_User: jack
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 64-bin.000006
          Read_Master_Log_Pos: 20305
               Relay_Log_File: BETA1-relay-bin.000021
                Relay_Log_Pos: 248
        Relay_Master_Log_File: 64-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: common
          Replicate_Ignore_DB: mysql,information_schema




master 2


mysql> show master status\G
*************************** 1. row ***************************
            File: 64-bin.000006
        Position: 20305
    Binlog_Do_DB: common
Binlog_Ignore_DB: mysql,information_schema
1 row in set (0.03 sec)


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.39.80
                  Master_User: jack
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: beta-bin.000005
          Read_Master_Log_Pos: 106
               Relay_Log_File: ubuntu64-relay-bin.000023
                Relay_Log_Pos: 250
        Relay_Master_Log_File: beta-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: common
          Replicate_Ignore_DB: mysql,information_schema