mysql-Database Master Configuration

Environment:

Host A (HUANGZP2): 172.16.115.157

Host B (HUANGZP3): 172.16.115.100

Description: Two-way master-slave configuration, mutual master from; Both servers can give users access (with redundancy), tasks in half points, can not specify

I. Install MySQL mysql-server on all two hosts

yum install MySQL mysql-server-y

Two. Edit the configuration file/etc/my.cnf

Add content to host a profile:

Log-bin=mysql-bin

server-id=157

Replicate-do-db=test

Binlog-ignore-db=mysql

Binlog-ignore-db=information_schema

auto_increment_increment=2

Auto_increment_offset=1

Host B configuration file add content:

Log-bin=mysql-bin

server-id=100

Replicate-do-db=test

Binlog-ignore-db=mysql

Binlog-ignore-db=information_schema

auto_increment_increment=2

auto_increment_offset=2

Description

• log-bin =mysql-bin   #表示开启二进制文件

• server-id=100   #server ID

• replicate-do-db=test  # Indicates that only the test library is synchronized, and replicate-do-table indicates that only the specified table is synchronized

• binlog-ignore-db=mysql   #表示不记录mysql库的二进制文件

• auto-increment-increment=2   #表示依次增长2

Note: The other host is set to Auto-increment-increment=2,auto-increment-increment=1 and the final primary key ID is 2, 4, 6 ... guaranteed primary key ID does not conflict when primary master synchronizes

Three. Restart the master-slave MySQL and set up two sets of master/slave (the second set of master and slave servers just in turn)

"First master-slave-host A-master"

Grant Replication Slave on * * to ' silen ' @ ' 172.16.115.100 ' identified by ' 123456 ';

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/8D/81/wKiom1ieyJqAvxOkAAAQ2aAFHyQ080.png "title=" 31. PNG "alt=" Wkiom1ieyjqavxokaaaq2aafhyq080.png "/>

Show master status;

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/8D/81/wKiom1ieyLCwOrTTAAAX6-jbdls467.png "title=" 32. PNG "alt=" Wkiom1ieylcworttaaax6-jbdls467.png "/>

"First master-slave-host B-slave"

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/8D/7E/wKioL1ieyMzh_YbkAAAXWRQH41E161.png "title=" 33. PNG "alt=" Wkiol1ieymzh_ybkaaaxwrqh41e161.png "/>

"Second set of master-slave-host B-master"

Description: The authorized user name can be different

Grant Replication Slave on * * to ' silen ' @ ' 172.16.115.157 ' identified by ' 123456 ';

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/8D/81/wKiom1ieyO_hRRWWAAAQ_8YByCU929.png "title=" 34. PNG "alt=" Wkiom1ieyo_hrrwwaaaq_8ybycu929.png "/>

Show master status;

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/8D/7E/wKioL1ieyPvS9d7RAAAXalKfhmo563.png "title=" 35. PNG "alt=" Wkiol1ieypvs9d7raaaxalkfhmo563.png "/>

"Second set of master-slave-host A-slave"

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/8D/81/wKiom1ieyR3RTgzTAAAXB0XqTbo434.png "title=" 36. PNG "alt=" Wkiom1ieyr3rtgztaaaxb0xqtbo434.png "/>

Four. The host opens slave respectively, and check slave status show slave status\g;

Host A

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/8D/81/wKiom1ieyULymTWRAABDqDlBr40402.png "title=" 41. PNG "alt=" Wkiom1ieyulymtwraabdqdlbr40402.png "/>

Host B

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/8D/7E/wKioL1ieyVHRWyBvAABCdtlAy3Y410.png "title=" 42. PNG "alt=" Wkiol1ieyvhrwybvaabcdtlay3y410.png "/>

Five. Create a table in the test library on the B server Tb1,a server also generates TB1

Host B

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/8D/7E/wKioL1ieyWKQZiSTAAAasm8UjF8338.png "title=" 51. PNG "alt=" Wkiol1ieywkqzistaaaasm8ujf8338.png "/>

Host A

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/8D/81/wKiom1ieyW_xMnDGAAA566S7x3I408.png "title=" 52. PNG "alt=" Wkiom1ieyw_xmndgaaa566s7x3i408.png "/>

Six. Creating a table in the test library on a server Tb2,b server also generates TB2

Host A

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/8D/81/wKiom1ieyX3TRjIoAAAXypOecKU584.png "title=" 61. PNG "alt=" Wkiom1ieyx3trjioaaaxypoecku584.png "/>

Host B

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/8D/7E/wKioL1ieyYjiD6ExAAAM-nqXw9U529.png "title=" 62. PNG "alt=" Wkiol1ieyyjid6exaaam-nqxw9u529.png "/>

This article from "A Horse Tread Plains" blog, reproduced please contact the author!

mysql-Database Master Configuration