mysql--replication复制集典型配置

一：主从

机器a：192.168.1.100

机器b: 192.168.1.101

 

1,给a :my.cnf添加以下配置

server-id=100

log-bin=mysql-bin

binlog-format=mixed |row|statement

 2，在a上新建帐号

grant replication slave,replication client on *.* to 'repl'@'192.168.1.%' identified by 'repl';

 

 

3，给b:my.cnf添加以下配置

server-id=101

relay-log=mysql-relay

read-only=1

 4，b上执行：

change master to 
master_host = '192.168.1.100',
master_host = 'repl', 
master_password='repl',
master_log_file='mysql-bin.000001',
master_log_pos='xxx'

 其中master_log_file和master_log_pos通过在master上执行show master status确定，也可以直接在os上查看文件名确定file

5， b上执行

slave start

 

二：主主

机器a：192.168.1.100

机器b: 192.168.1.101

 

1，添加a:my.cnf 

server-id=100
log-bin=mysql-bin
binlog-format=mixed  #statement/row/mixed

relay-log=mysql-relay
log-slave-updates=1  #保证同步过来的数据写入自己的bin-log

auto_increment_increment=2 #自增步长
auto_increment_offset=1  #自增初始偏移量

 2，添加b:my.cnf

server-id=101
log-bin=mysql-bin
binlog-format=mixed  #statement/row/mixed

relay-log=mysql-relay
log-slave-updates=1  #保证同步过来的数据写入自己的bin-log

auto_increment_increment=2 #自增步长
auto_increment_offset=2  #自增初始偏移量

 

3 ,a，b建立帐号：

grant replication slave,replication client on *.* to 'repl'@'192.168.1.%' identified by 'repl';

 4，a执行：

change master to 
master_host = '192.168.1.101',
master_host = 'repl', 
master_password='repl',
master_log_file='mysql-bin.000001',
master_log_pos='xxx';

slave start;

 5,b执行:

change master to 
master_host = '192.168.1.100',
master_host = 'repl', 
master_password='repl',
master_log_file='mysql-bin.000001',
master_log_pos='xxx';

slave start;

 搞定！