用户名: 密码: 忘记密码? 注册

Mysql master to master 配置实例

作者:  时间: 2010-09-13

分别在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