MYSQL-5.6.26 Primary master replication

The environment is as follows:
Centos6.5_64

MySQL5.6.26
master1:192.168.1.112
master2:192.168.1.114

MySQL Installation

Here is ignored, mainly is a careful. Sometimes a careless, you will find that the installation failed.

Configure Master1
1. Modify the My.cnf file to add the following:
1
2
3
4
5
6
7
8
9
10
11
12
13
[MySQL

Server-id =1 #数据库ID
Log-bin=myslq-bin #启用二进制日志
BINLOG-DO-DB=TUDOU1 #需要同步的数据库, here Sync tudou1 and TUDOU2 two databases
Binlog-do-db=tudou2
Binlog-ignore-db=mysql #忽略同步的数据库
Log-bin=/var/log/mysqlbin/bin_log #设置生成的log文件名,
If you do not var/log/mysqlbin this directory, you need to create and execute Chown-r mysql.mysql/var/log/mysqlbin
Log-slave-updates #把从库的写操作记录到binlog中
expire_logs_days=365 #日志文件过期天数, default is 0, indicates no expiration
auto-increment-increment=2 #设定为主服务器的数量 to prevent auto_increment fields from repeating
Auto-increment-offset=1 #自增长字段的初始值, there is no self-increment in multiple master environments
Long ID Repeat

2. Add a copy of the account backup
1
2
3
[Email protected] ~]# mysql-uroot-p123456
Mysql>grant replication Slave on . to [email protected] '% ' identified by ' 123456 ';
Mysql>flush privileges;
3. Add MySQL port 3306 to the firewall and restart the firewall to take effect
1
-A input-m state--state new-m tcp-p TCP--dport 3306-j ACCEPT
4. Test on Master2 If the backup user can connect to the database on the Master1
1
[Email protected] ~]# mysql-ubackup-h 192.168.1.112-p123456
Configure Master2

1. Modify the My.cnf file to add the following:
1
2
3
4
5
6
7
8
9
10
11
12
13
[Mysqld]

Server-id =2 #数据库ID
log-bin=myslq-bin #启用二进制日志
binlog-do-db=tudou1 #需要同步的数据库, where tudou1 and TUDOU2 two databases are synchronized
BINLOG-DO-DB=TUDOU2
Binlog-ignore-db=mysql #忽略同步的数据库
Log-bin=/var/log/mysqlbin/bin_log #设置生成的log文件名, If you do not var/log/mysqlbin this directory, you need to create and execute chown-r mysql.mysql/var/log/mysqlbin
log-slave-updates #把从库的写操作记录到binlog中
expire_logs_days=365 #日志文件过期天数, default is 0, means no expiration
auto-increment-increment=2 #设定为主服务器的数量, prevents auto_increment fields from repeating
auto-increment-offset=2 #自增长字段的初始值, there will be no self-increment
long ID duplicates

in more than one master environment

2. Add a copy of the account backup
1
2
3
[Email protected] ~]# mysql-uroot-p123456
Mysql>grant replication Slave on . to [email protected] '% ' identified by ' 123456 ';
Mysql>flush privileges;
3. Add MySQL port 3306 to the firewall and restart the firewall to take effect
1
-A input-m state--state new-m tcp-p TCP--dport 3306-j ACCEPT
4. Test on Master1 If the backup user can connect to the database on the Master2
1
[Email protected] ~]# mysql-ubackup-h 192.168.1.114-p123456
Configuring Master1-master2 Synchronization

Restarting the MySQL service for Master1 and Master2
1
[[Email protected] ~]# service MySQL restart
View Master Status
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)
Set Master1 sync from 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
If the following two items appear, the configuration is successful!
Slave_io_running:yes
Slave_sql_running:yes

Set Master2 sync from 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
If the following two items appear, the configuration is successful!
Slave_io_running:yes
Slave_sql_running:yes

To test the primary master synchronization:
Enter the Master1 MySQL database
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21st
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 (ten), 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)
Enter Master2 to see if there is a tudou1 this database and test table.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21st
22
23
mysql> use TUDOU1;
Reading table information for completion of table and column names
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 ');
In the Master1 database, you will find that the data you just inserted into the master2 is also inserted into the database.
1
2
3
4
5
6
7
8
9
Mysql> select from test;
+----+------+
| ID | name |
+----+------+
| 1 | A |
| 3 | B |
| 5 | C |
| 6 | D |
+----+------+

MYSQL-5.6.26 Primary master replication