环境如下:

CentOS6.5_64

MySQL5.6.26

master1:192.168.1.112master2:192.168.1.114

mysql 安装

这里就忽略了，主要就是一个细心。有时候一个不小心，就会发现安装失败。

配置master1

1.修改my.cnf文件，增加如下内容：12345678910111213[mysql

server-id =1 #数据库ID

log-bin=myslq-bin #启用二进制日志 binlog-do-db=tudou1 #需要同步的数据库,这里同步tudou1和tudou2两个数据库binlog-do-db=tudou2binlog-ignore-db=mysql #忽略同步的数据库log-bin=/var/log/mysqlbin/bin_log #设置生成的log文件名，如果没有var/log/mysqlbin这个目录，则需要创建，并且执行chown -R mysql.mysql /var/log/mysqlbinlog-slave-updates #把从库的写操作记录到binlog中expire_logs_days=365 #日志文件过期天数，默认是 0，表示不过期auto-increment-increment=2 #设定为主服务器的数量，防止 auto_increment 字段重复auto-increment-offset=1 #自增长字段的初始值，在多台 master 环境下，不会出现自增长 ID 重复

2.添加一个复制的帐号backup

123[root@localhost ~]# mysql -uroot -p123456mysql>grant replication slave on . to backup@'%' identified by '123456';mysql>flush privileges;3.在防火墙里面添加mysql端口3306，并重启防火墙生效1-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT4.在master2上测试用backup用户是否能连接到master1上的数据库1[root@master ~]# mysql -ubackup -h 192.168.1.112 -p123456配置master2

1.修改my.cnf文件，增加如下内容：

12345678910111213[mysqld]

server-id =2 #数据库ID

log-bin=myslq-bin #启用二进制日志 binlog-do-db=tudou1 #需要同步的数据库,这里同步tudou1和tudou2两个数据库binlog-do-db=tudou2binlog-ignore-db=mysql #忽略同步的数据库log-bin=/var/log/mysqlbin/bin_log #设置生成的log文件名，如果没有var/log/mysqlbin这个目录，则需要创建，并且执行chown -R mysql.mysql /var/log/mysqlbinlog-slave-updates #把从库的写操作记录到binlog中expire_logs_days=365 #日志文件过期天数，默认是 0，表示不过期auto-increment-increment=2 #设定为主服务器的数量，防止 auto_increment 字段重复auto-increment-offset=2 #自增长字段的初始值，在多台 master 环境下，不会出现自增长 ID 重复

2.添加一个复制的帐号backup

123[root@localhost ~]# mysql -uroot -p123456mysql>grant replication slave on . to backup@'%' identified by '123456';mysql>flush privileges;3.在防火墙里面添加mysql端口3306，并重启防火墙生效1-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT4.在master1上测试用backup用户是否能连接到master2上的数据库1[root@master ~]# mysql -ubackup -h 192.168.1.114 -p123456配置master1-master2同步

重启master1和master2的mysql服务

1[root@localhost ~]# service mysql restart查看master状态master11234567mysql> show master status;+----------------+----------+----------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+----------------+----------+----------------+------------------+-------------------+| bin_log.000001 | 120 | tudou1,tudou2 | mysql | |+----------------+----------+----------------+------------------+-------------------+1 row in set (0.00 sec)master21234567mysql> show master status;+----------------+----------+----------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+----------------+----------+----------------+------------------+-------------------+| bin_log.000001 | 120 | tudou1,tudou2 | mysql | |+----------------+----------+----------------+------------------+-------------------+1 row in set (0.00 sec)设置master1从master2同步1234567891011121314151617mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.114',MASTER_PORT=3306,MASTER_USER='backup',MASTER_PASSWORD='123456',MASTER_LOG_FILE='bin_log.000001',MASTER_LOG_POS=120;mysql>start slave;mysql>show slave status\G; 1. row Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.114Master_User: backupMaster_Port: 3306Connect_Retry: 60Master_Log_File: bin_log.000001Read_Master_Log_Pos: 120Relay_Log_File: master-relay-bin.000002Relay_Log_Pos: 281Relay_Master_Log_File: bin_log.000001Slave_IO_Running: YesSlave_SQL_Running: Yes如出现以下两项，则说明配置成功！Slave_IO_Running: YesSlave_SQL_Running: Yes

设置master2从master1同步

1234567891011121314151617mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.112',MASTER_PORT=3306,MASTER_USER='backup',MASTER_PASSWORD='123456',MASTER_LOG_FILE='bin_log.000001',MASTER_LOG_POS=120;mysql>start slave;mysql> show slave status\G; 1. row Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.112Master_User: backupMaster_Port: 3306Connect_Retry: 60Master_Log_File: bin_log.000001Read_Master_Log_Pos: 120Relay_Log_File: master2-relay-bin.000002Relay_Log_Pos: 281Relay_Master_Log_File: bin_log.000001Slave_IO_Running: YesSlave_SQL_Running: Yes如出现以下两项，则说明配置成功！Slave_IO_Running: YesSlave_SQL_Running: Yes

测试主主同步：

进入master1 mysql数据库1234567891011121314151617181920212223242526mysql> create database tudou1;Query OK, 1 row affected (0.02 sec)

mysql> use tudou1;

Database changedmysql> create table test(id int auto_increment,name varchar(10),primary key(id));Query OK, 0 rows affected (0.01 sec)

mysql> insert into test(name) values('a');

Query OK, 1 row affected (0.01 sec)

mysql> insert into test(name) values('b');

Query OK, 1 row affected (0.00 sec)

mysql> insert into test(name) values('c');

Query OK, 1 row affected (0.01 sec)

mysql> select * from test;

+----+------+| id | name |+----+------+| 1 | a || 3 | b || 5 | c |+----+------+3 rows in set (0.00 sec)进master2，查看是否有tudou1这个数据库和test表。1234567891011121314151617181920212223mysql> use tudou1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;+------------------+| Tables_in_tudou1 |+------------------+| test |+------------------+1 row in set (0.00 sec)

mysql> select from test;

+----+------+

| id | name |

+----+------+

| 1 | a |

| 3 | b |

| 5 | c |

+----+------+

3 rows in set (0.00 sec)

mysql>insert into test(name) values('d');

在master1的数据库中就会发现刚在master2插入的数据也插入到数据库了。

1

2

3

4

5

6

7

8

9

mysql> select from test;+----+------+| id | name |+----+------+| 1 | a || 3 | b || 5 | c || 6 | d | +----+------+