主主复制

MySQL MM复制
1. db1配置：

1.1 创建复制用户
  GRANT REPLICATION SLAVE ON *.* TO slave2_user IDENTIFIED BY 'slave2_pwd';

1.2 修改my.cnf
[mysqld]
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1

master-host = db2
master-user = slave1_user
master-password = slave1_pwd
master-connect-retry = 60
replicate-do-db = exampledb

log-bin = mysql-bin
binlog-do-db = exampledb

relay-log = slave-relay
relay-log-index = slave-relay-log.index

expire_logs_days        = 10
max_binlog_size         = 500M
slave-skip-errors=126,1062

1.3 查看MASTER STATUS
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
记录参数，MASTER FILE:mysql-bin.000002, POS: 98,并拷贝数据库文件到db2
UNLOCK TABLES;
\q

1.4 修改复制参数
SLAVE STOP;
CHANGE MASTER TO
MASTER_HOST='db2',
MASTER_USER='slave1_user',
MASTER_PASSWORD='slave1_pwd',
MASTER_LOG_FILE='mysql-bin.000009',
MASTER_LOG_POS=783;
SLAVE START;

 

2. db2配置：

2.1 创建复制用户
  GRANT REPLICATION SLAVE ON *.* TO slave1_user IDENTIFIED BY 'slave1_pwd';

1.2 修改my.cnf
[mysqld]
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2

master-host = db1
master-user = slave2_user
master-password = slave2_pwd
master-connect-retry = 60
replicate-do-db = exampledb

log-bin = mysql-bin
binlog-do-db = exampledb

relay-log = slave-relay
relay-log-index = slave-relay-log.index

expire_logs_days        = 10
max_binlog_size         = 500M
slave-skip-errors=126,1062

1.3 查看MASTER STATUS
SHOW MASTER STATUS;
\q
####记录参数，MASTER FILE:mysql-bin.000009, POS: 783

1.4 修改复制参数
SLAVE STOP;
CHANGE MASTER TO
MASTER_HOST='db1',
MASTER_USER='slave2_user',
MASTER_PASSWORD='slave2_pwd',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=98;
SLAVE START;