MySQL Primary master replication model

A model that demonstrates MySQL's dual-master replication:

Physical machine for Win7, virtual machine is 2 units CENTOS7

Node 1:192.168.255.2

Node 2:192.168.255.3

Yum installation mariadb-server5.5

Configuration Node 1:

]# vim/etc/my.cnf

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/82/8B/wKiom1dYT6bwzlf4AABqi3ptfsM606.png "title=" 1.png " alt= "Wkiom1dyt6bwzlf4aabqi3ptfsm606.png"/>

Configuration Node 2

]# vim/etc/my.cnf

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/82/89/wKioL1dYULmj_A4lAACHhhBi1yA503.png "title=" 2.png " alt= "Wkiol1dyulmj_a4laachhhbi1ya503.png"/>

Start the MySQL service separately:

]# systemctl Start Mariadb.service

On two nodes: set the account for replication permissions:

> GRANT REPLICATION slave,replication CLIENT on * * to ' repluser ' @ ' 192.168.255.% ' identified by ' replpass ';

> FLUSH privileges;

Note: In the actual work, only a single IP can be authorized, not the form of a wildcard authorization, if there are multiple IPs, each IP is executed separately authorization statement;

Then, set two nodes to point to each other as the primary node;

Record the location of the other's binary log files in advance, and then start copying from this location:

Node 1: View and record the location of the binary log file office

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/82/89/wKioL1dYUePBK28sAAAWJnmsvdM967.png "title=" 3.png " Width= "height=" 138 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:700px;height:138px; "alt=" Wkiol1dyuepbk28saaawjnmsvdm967.png "/>

The record position is 499 and will be used when Node 2 is set;

Node 2: View and record the location of the binary log file office

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/82/89/wKioL1dYUrKCaIzRAAAVaGiFhss174.png "title=" 4.png " Width= "height=" 139 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:700PX;HEIGHT:139PX; "alt=" Wkiol1dyurkcaizraaavagifhss174.png "/>

The record position is 509 and will be used when node 1 is set;

Set Node 1:

> Change MASTER to master_host= ' 192.168.255.3 ', master_user= ' repluser ', master_password= ' Replpass ', Master_log_ File= ' master-bin.000003 ', master_log_pos=509;

Set Node 2:

> Change MASTER to master_host= ' 192.168.255.2 ', master_user= ' repluser ', master_password= ' Replpass ', Master_log_ File= ' master-bin.000003 ', master_log_pos=499;

On two nodes: set

> START SLAVE;

At this point, the two nodes can be viewed:

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/82/8B/wKiom1dYU9vTDLX5AACti_E5HSU330.png "style=" width : 700px;height:843px; "title=" 5.png "width=" "height=" 843 "border=" 0 "hspace=" 0 "vspace=" 0 "alt=" Wkiom1dyu9vtdlx5aacti_e5hsu330.png "/>

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/82/89/wKioL1dYVOWgch3oAACqfz3JpNY161.png "style=" width : 700px;height:814px; "title=" 6.png "width=" "height=" 814 "border=" 0 "hspace=" 0 "vspace=" 0 "alt=" Wkiol1dyvowgch3oaacqfz3jpny161.png "/>

The display content and the master-slave copy are basically the same, but are the two-master model;

At this time, the two-master model configuration is complete, both sides can be copied through the other side;

Verify Dual-Master:

For example, in Node 1, create a new library:

> CREATE DATABASE mydb;

and view the status of Node 1:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/82/8B/wKiom1dYVhHjEA5gAACv04w4Mw8149.png "title=" 7.png " alt= "Wkiom1dyvhhjea5gaacv04w4mw8149.png"/>

On Node 2:

View Status:

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/82/8B/wKiom1dYVq3AN-_tAACpO2DgCsE275.png "title=" 8.png " Width= "723" height= "831" border= "0" hspace= "0" vspace= "0" style= "width:723px;height:831px;" alt= "wkiom1dyvq3an-_ Taacpo2dgcse275.png "/>

To view the new library synced to:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/82/89/wKioL1dYWAHTE23fAAArFLBL5DQ790.png "title=" 9.png " alt= "Wkiol1dywahte23faaarflbl5dq790.png"/>

At this point, has verified the node 1 write operation, Node 2 synchronization node 1 after the completion of data backup;

After verifying that Node 2 has a write operation, the node 1 synchronizes node 2 and completes the number of backups;

On Node 2:

To create a table in a new library:

> Use MyDB

> CREATE TABLE tb1 (id INT UNSIGNED not NULL auto_increment PRIMARY key,name CHAR (30));

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/82/8B/wKiom1dYWKrAhGiGAAAcobYn8TU126.png "title=" 10.png "Width=" 720 "height=" 162 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:720px;height:162px; "alt=" Wkiom1dywkrahgigaaacobyn8tu126.png "/>

View Node 2 status:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/82/89/wKioL1dYWjzTQYMnAAAWN7ziynI710.png "title=" 11.png "Width=" 723 "height=" 151 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:723px;height:151px; "alt=" Wkiol1dywjztqymnaaawn7ziyni710.png "/>

To view the results after synchronization on Node 1:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/82/89/wKioL1dYWrjRALKgAACbrWPghRg939.png "title=" 12.png "alt=" Wkiol1dywrjralkgaacbrwpghrg939.png "/>

To view the tables synced to:

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/82/8B/wKiom1dYWk7Cywf5AABMssuS_R4421.png "title=" 13.png "alt=" Wkiom1dywk7cywf5aabmssus_r4421.png "/>

Continue to verify the autogrow offset:

Continue on Node 1: Insert new value To field

> INSERT into TB1 (name) VALUES (' Kobe byrant '), (' Michael Jordan '), (' Yao Ming ');

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/82/89/wKioL1dYXJmy8dStAAActmBY8gA472.png "title=" 14.png "alt=" Wkiol1dyxjmy8dstaaactmby8ga472.png "/>

On Node 2:

Also inserts a new value for the field, verifying that the auto-grow offset does not duplicate the ID

> INSERT into TB1 (name) VALUES (' Zhu Uuanzhang '), (' Zhu Di '), (' Zhu Yue ');

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/82/8B/wKiom1dYXA7BZlNCAAAZIJGFfCY509.png "title=" 15.png "alt=" Wkiom1dyxa7bzlncaaazijgffcy509.png "/>

Verifiable, auto-grow offset implementation when the new values are inserted on both sides, the ID is not duplicated;

The above is the configuration implementation of the MySQL master replication model.

MySQL Primary master replication model