MySQL Master Master sync

Environment Introduction:

CentOS-6.7 64-bit

Master1:blog-mysql-1 192.168.133.128

Master2:blog-mysql-2 192.168.133.132

MySQL version 5.5.33 binary package installation

Check whether the test environment of two machines meets the requirements. and ensure that you can log in to MySQL normally

Take one of these as an example:

Hostname

Ifconfig eth0 | grep "inet addr" | awk ' {print $} ' | Cut-d:–f2

Mysql–v

Cat/etc/redhat-release

Uname-a

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M01/8D/01/wKiom1iBeeOzvwm9AABG_v425iM233.png "height="/>

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M02/8D/01/wKiom1iBeeTyhvxMAABC1TqzMuc631.png "height=" 233 "/>

This is the preparation of the two-master environment, then edit the master 1 my.cnf file

For easy access or as before, first filter the #, the beginning of the line bar

cat/etc/my.cnf | Grep-v ^# | Grep-v ^$ > Tmp.log

Cat Tmp.log >/ETC/MY.CNF

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M02/8C/FE/wKioL1iBeeTQ3PLTAAAR_u35yRc048.png "height="/>

Add the following fields in [Mysqld] in the my.cnf file of Master 1

Vim/etc/my.cnf

Server-id = 1

Log-bin = Mysql-bin

Auto_increment_increment = 2

Auto_increment_offset = 1

Log-slave-updates

Expire_logs_days = 7

Skip-name-resolve

Lower_case_table_names=1

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M00/8D/01/wKiom1iBeeXTcvcDAAAWT_-1U0o779.png "height=" 142 "/>

The above parameters

Auto_increment_increment = 2 #自增ID的间隔, e.g. 1 3 5 interval 2

Auto_increment_offset = 1 #ID的初始位置

Modifying a configuration file requires restarting the database for Master 1

/etc/init.d/mysqld restart

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M01/8D/01/wKiom1iBeebQvg60AAAPt65wvYc655.png "height=" "/>"

Log in to Master 1 MySQL

mysql-uroot-p123456

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M01/8D/01/wKiom1iBeebgzdBUAABDCYoWubc389.png "height=" 232 "/>

Create an account on Master 1 to sync to master 2

Grant Replication Slave on * * to [e-mail protected] '% ' identified by ' 123456 ';

Flush privileges;

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M02/8D/01/wKiom1iBeejCHEbbAAAbT698LUA011.png "height="/>

Check the operation just now.

Select User,host from Mysql.user;

Show grants for [email protected] '% ';

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M01/8C/FE/wKioL1iBeemAwD63AABEQmI3yqo543.png "height=" 338 "/>

Continue Operation Master1 Lock table, view Binlog file and POS location node

Flush table with read lock;

Show master status;

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M00/8D/01/wKiom1iBeeqS6jkRAAAlEaSEoPw986.png "height=" 174 "/>

Opens a new window, exports the Master 1 database file, and passes the SCP command to Master 2.

mysqldump-uroot-p123456--events-a-B >/root/test.sql

SCP Test.sql [Email Protected]:/root

Yes

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M01/8D/01/wKiom1iBeeuiHmT3AABKlVw-iuM797.png "height=" 169 "/>

Solution Table Master 1

Unlock tables;

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M00/8D/01/wKiom1iBeezSWRpKAAALKBU8Plg922.png "height="/>

Log in to Master 2, and now import the database Test.sql

mysql-uroot-p123456 < Test.sql

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M00/8C/FE/wKioL1iBee2j7TiYAAAKrc8XD4o409.png "height="/>

Edit the My.cnf file for Master 2, just as you did above, only note that Server-id and offset

cat/etc/my.cnf | Grep-v ^# | Grep-v ^$ > Tmp.log

Cat Tmp.log >/ETC/MY.CNF

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M01/8C/FE/wKioL1iBee3BsIHyAAASPsN5uvE957.png "height="/>

Server-id = 2

Log-bin = Mysql-bin

Auto_increment_increment = 2

Auto_increment_offset = 2

Log-slave-updates

Expire_logs_days = 7

Skip-name-resolve

Lower_case_table_names=1

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M02/8C/FE/wKioL1iBee7hi-pzAABIhNfDhkM392.png "height=" 441 "/>

Don't forget to restart MySQL after modifying the configuration file

/etc/init.d/mysqld restart

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M02/8D/01/wKiom1iBee-jhu68AAAQSYsUFxU064.png "height="/>

Log in to the MySQL database of Master 2

mysql-uroot–p123456

Change MASTER to

Master_host= ' 192.168.133.128 ',

master_port=3306,

Master_user= ' Yuci ',

Master_password= ' 123456 ',

Master_log_file= ' mysql-bin.000001 ',

master_log_pos=332;

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M02/8C/FE/wKioL1iBefCgpItPAABkcfua3aI642.png "height=" 344 "/>

View two IO SQL threads connected successfully

Start slave;

Show Slave Status\g

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M00/8D/01/wKiom1iBefHD2GbAAABIY4Xl2Mw044.png "height=" 280 "/>

Create an account on Master 2 to sync to master 1, refresh and check

In fact, two MySQL created two accounts, account can not be duplicated

Grant Replication Slave on * * to [e-mail protected] '% ' identified by ' 123456 ';

Flush privileges;

Select User,host from Mysql.user;

Show grants for [email protected] '% ' ;

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M00/8C/FE/wKioL1iBefHxnC6NAABkm2arogo689.png "height=" 462 "/>

View Binlog files and pos numbers for Master 2

Show master status;

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M01/8D/01/wKiom1iBefLhcVokAAAZKMXf-YU918.png "height=" 117 "/>

Return to Master 1, set account synchronization parameters

Pay attention to each parameter in the input, do not put it and the previous account, user name, password to reverse the

Change MASTER to

master_host= ' 192.168.133.132 ',

master_port=3306,

master_user= ' yuci1 ',

master_password= ' 123456 ',

master_log_file= ' mysql-bin.000002 ',

master_log_pos=333;

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M01/8C/FE/wKioL1iBefbyXmjpAAAqfOGjtA4963.png "height=" 246 "/>

Start slave on Master 1 and view

Start slave;

Show Slave Status\g

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M02/8D/01/wKiom1iBefeBp94xAABMBFGRDFE203.png "height=" 295 "/>

So far, the two-master environment, even if the building is complete, next test.

Create the database on Master 1 first Yucitest1

Create Database Yucitest1;

show databases;

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M00/8D/01/wKiom1iBeffje9MJAAAlj8O24TE345.png "height=" 235 "/>

To view all the databases on Master 2, you can see the yucitest1 you just created, delete them, create the YUCITEST2 database

show databases;

Drop database Yucitest1;

Create Database Yucitest2;

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M01/8D/01/wKiom1iBefijpenIAAAt2pqbcDw383.png "height=" 283 "/>

Back to Master 1 see YUCITEST2, Environment Build success

show databases;

650) this.width=650; "Style=" background-image:none;border-bottom:0px;border-left:0px;margin:0px;padding-left:0px ;p adding-right:0px;border-top:0px;border-right:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M01/8C/FE/wKioL1iBefjDEEd7AAAVq06xfA8653.png "height=" 193 "/>

This article is from the "LULU" blog, make sure to keep this source http://aby028.blog.51cto.com/5371905/1893336

MySQL Master Master sync