MySQL 主主复制 Master Master(主-主)

MySQL 主主复制 Master Master(主-主)

1.3.2.1. Master A

my.cnf 文件加入下面的内容

cp /etc/mysql/my.cnf /etc/mysql/my.cnf.old
vim /etc/mysql/my.cnf

[mysqld]
server-id = 1
log-bin=/data/mysql/binlog/binlog
binlog-do-db = test
binlog-ignore-db=mysql

log-slave-updates
sync_binlog=1
auto_increment_offset=1
auto_increment_increment=2
replicate-do-db = test
replicate-ignore-db = mysql,information_schema
			

创建复制权限

grant replication slave on *.* to 'replication'@'192.168.1.%' identified by '000000';
flush privileges;
			

			
mysql>flush tables with read lock;

mysql> show master status\G
*************************** 1. row ***************************
File: binlog.000007
Position: 107
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
			
			

1.3.2.2. Master B

创建复制权限

grant replication slave on *.* to 'replication'@'192.168.1.%' identified by '000000';
flush privileges;
			

my.cnf 文件加入下面的内容

[mysqld]
server-id = 2
log-bin = /data/mysql/binlog/binlog
replicate-do-db = test
replicate-ignore-db = mysql,information_schema

binlog-do-db = test
binlog-ignore-db=mysql
log-slave-updates
sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2
			

B 与 A 配置文件不同的两处。

server-id = 2
auto_increment_offset=2
			

			
mysql> show master status\G
*************************** 1. row ***************************
File: binlog.000005
Position: 107
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
			
			

1.3.2.3. 将Master A 数据库 同步到 Master B 两端数据库内容保持一致

Master A，首先锁表为只读状态

			
mysqldump --databases test > /tmp/test.sql
			
			

Master B 创建一个与Master A同名的空数据库,然后将备份文件恢复到数据