mysql-5.6.26 主主復(fù)制

環(huán)境如下:
CentOS6.5_64

MySQL5.6.26
master1:192.168.1.112
master2:192.168.1.114

mysql 安裝

這里就忽略了，主要就是一個(gè)細(xì)心。有時(shí)候一個(gè)不小心，就會(huì)發(fā)現(xiàn)安裝失敗。

配置master1
1.修改my.cnf文件，增加如下內(nèi)容：
1
2
3
4
5
6
7
8
9
10
11
12
13
[mysql

server-id =1 #數(shù)據(jù)庫(kù)ID
log-bin=myslq-bin #啟用二進(jìn)制日志
binlog-do-db=tudou1 #需要同步的數(shù)據(jù)庫(kù),這里同步tudou1和tudou2兩個(gè)數(shù)據(jù)庫(kù)
binlog-do-db=tudou2
binlog-ignore-db=mysql #忽略同步的數(shù)據(jù)庫(kù)
log-bin=/var/log/mysqlbin/bin_log #設(shè)置生成的log文件名，
如果沒(méi)有var/log/mysqlbin這個(gè)目錄，則需要?jiǎng)?chuàng)建，并且執(zhí)行chown -R mysql.mysql /var/log/mysqlbin
log-slave-updates #把從庫(kù)的寫(xiě)操作記錄到binlog中
expire_logs_days=365 #日志文件過(guò)期天數(shù)，默認(rèn)是 0，表示不過(guò)期
auto-increment-increment=2 #設(shè)定為主服務(wù)器的數(shù)量，防止 auto_increment 字段重復(fù)
auto-increment-offset=1 #自增長(zhǎng)字段的初始值，在多臺(tái) master 環(huán)境下，不會(huì)出現(xiàn)自增
長(zhǎng) ID 重復(fù)

2.添加一個(gè)復(fù)制的帳號(hào)backup
1
2
3
[root@localhost ~]# mysql -uroot -p123456
mysql>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 ACCEPT
4.在master2上測(cè)試用backup用戶是否能連接到master1上的數(shù)據(jù)庫(kù)
1
[root@master ~]# mysql -ubackup -h 192.168.1.112 -p123456
配置master2

1.修改my.cnf文件，增加如下內(nèi)容：
1
2
3
4
5
6
7
8
9
10
11
12
13
[mysqld]

server-id =2 #數(shù)據(jù)庫(kù)ID
log-bin=myslq-bin #啟用二進(jìn)制日志
binlog-do-db=tudou1 #需要同步的數(shù)據(jù)庫(kù),這里同步tudou1和tudou2兩個(gè)數(shù)據(jù)庫(kù)
binlog-do-db=tudou2
binlog-ignore-db=mysql #忽略同步的數(shù)據(jù)庫(kù)
log-bin=/var/log/mysqlbin/bin_log #設(shè)置生成的log文件名，如果沒(méi)有var/log/mysqlbin這個(gè)目錄，則需要?jiǎng)?chuàng)建，并且執(zhí)行chown -R mysql.mysql /var/log/mysqlbin
log-slave-updates #把從庫(kù)的寫(xiě)操作記錄到binlog中
expire_logs_days=365 #日志文件過(guò)期天數(shù)，默認(rèn)是 0，表示不過(guò)期
auto-increment-increment=2 #設(shè)定為主服務(wù)器的數(shù)量，防止 auto_increment 字段重復(fù)
auto-increment-offset=2 #自增長(zhǎng)字段的初始值，在多臺(tái) master 環(huán)境下，不會(huì)出現(xiàn)自增
長(zhǎng) ID 重復(fù)

2.添加一個(gè)復(fù)制的帳號(hào)backup
1
2
3
[root@localhost ~]# mysql -uroot -p123456
mysql>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 ACCEPT
4.在master1上測(cè)試用backup用戶是否能連接到master2上的數(shù)據(jù)庫(kù)
1
[root@master ~]# mysql -ubackup -h 192.168.1.114 -p123456
配置master1-master2同步

重啟master1和master2的mysql服務(wù)
1
[root@localhost ~]# service mysql restart
查看master狀態(tài)
master1
1
2
3
4
5
6
7
mysql> 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)
master2
1
2
3
4
5
6
7
mysql> 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)
設(shè)置master1從master2同步
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.114',MASTER_PORT=3306,MASTER_USER='backup',MASTER_PASSWORD='123456',MASTER_LOG_FILE='bin_log.00
0001',MASTER_LOG_POS=120;
mysql>start slave;
mysql>show slave status\G;
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.114
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin_log.000001
Read_Master_Log_Pos: 120
Relay_Log_File: master-relay-bin.000002
Relay_Log_Pos: 281
Relay_Master_Log_File: bin_log.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如出現(xiàn)以下兩項(xiàng)，則說(shuō)明配置成功！
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

設(shè)置master2從master1同步
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.112',MASTER_PORT=3306,MASTER_USER='backup',MASTER_PASSWORD='123456',MASTER_LOG_FILE='bin_log.00
0001',MASTER_LOG_POS=120;
mysql>start slave;
mysql> show slave status\G;
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.112
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin_log.000001
Read_Master_Log_Pos: 120
Relay_Log_File: master2-relay-bin.000002
Relay_Log_Pos: 281
Relay_Master_Log_File: bin_log.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如出現(xiàn)以下兩項(xiàng)，則說(shuō)明配置成功！
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

測(cè)試主主同步：
進(jìn)入master1 mysql數(shù)據(jù)庫(kù)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> create database tudou1;
Query OK, 1 row affected (0.02 sec)

mysql> use tudou1;
Database changed
mysql> 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)
進(jìn)master2，查看是否有tudou1這個(gè)數(shù)據(jù)庫(kù)和test表。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> use tudou1;
Reading table information for completion of table and column names
You 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的數(shù)據(jù)庫(kù)中就會(huì)發(fā)現(xiàn)剛在master2插入的數(shù)據(jù)也插入到數(shù)據(jù)庫(kù)了。
1
2
3
4
5
6
7
8
9
mysql> select from test;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 3 | b |
| 5 | c |
| 6 | d |
+----+------+