Mysql---Master-Master Mutual Preparation

First, the main advantages

The concept of dual-machine hot standby simply say, is to maintain the status of two databases automatically synchronized.

Operations on any one database are automatically applied to another database, keeping two of the database data consistent. The benefits of doing this:

( 1 can do a disaster, one of which is broken can be switched to another.

( 2 load Balancing can be done to spread the request to any of these platforms, improving website throughput.  

Second, the environment

System Environment: Linux

Mysql version: 5.6.24

Master1ip : 192.168.0.1

Master2ip : 192.168.0.2

Third, the Operation procedure

(1) Create a dedicated backup user on Master1

"master1"

Mysql–uroot-pgrant replication Slave on * * to ' repl_user ' @ ' 192.168.0.2 ' identified by ' repl_user '; flush privileges;

(2) Turn on the primary serverBinarylog

"master1"

vi /etc/my.cnfserver_id = 1log-bin=master-binbinlog_format=mixed read-only=0                                                                                      binlog-ignore-db=mysql                                                                         binlog-ignore-db=information_schema                                                     binlog-ignore-db=performance_schemaauto-increment-increment=5                                                                           auto-increment-offset=1

(3) Get the status and synchronization status of the primary server

Lock Database First: Flush tables with read lock; Export data: mysqldump-root-p test>/tmp/test_0511.sql View binary log location for Master1: Show Master status\g;*************************** 1. row*************************** file:master-bin.000009 position:6001 binlog_do_db:binlog_ignore_db : Mysql,information_schema,performance_schemaexecuted_gtid_set:1 row in Set (0.00 sec) Error:no query specified Unlocked: Unlock tables;

(4) SettingMaster2databases that need to be replicated

"master2"

vi/etc/my.cnfserver_id = 2log-bin = Master-binbinlog_format = Mixed Replicate-ignore-db = Mysqlreplicate-ignore-db = info rmation_schemareplicate-ignore-db = Performance_schemarelay_log=mysqld-relay-binlog-slave-updates = on/etc/init.d/ mysqld restart

(5) Import initial state, start syncing.

Create DATABASE Test default charset utf8;use testsource/tmp/test_0511.sql change MASTER to master_host= ' 192.168.0.1 ', master_user= ' repl_user ', master_password= ' repl_user ', master_log_file= ' master-bin.000009 ', MASTER _log_pos=6001; Start slave;show slave status\g;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6C/E7/wKioL1VVwgnRgdriAAAw6FWTk0I006.jpg "title=" Qq20150515174615.png "alt=" Wkiol1vvwgnrgdriaaaw6fwtk0i006.jpg "/>

Note the red box in the graph, Two of them are. Yes , Description Open successfully.

(6)in theMaster2create a specially backed up user on the

"master2"

Mysql–uroot-pgrant replication Slave on * * to ' repl_user ' @ ' 192.168.0.1 ' identified by ' repl_user '; flush privileges;

(7) OpenMaster2of the primary serverBinarylog

Vi/etc/my.cnfread-only=0binlog-ignore-db=mysqlbinlog-ignore-db=information_schemabinlog-ignore-db=performance_ Schemaauto-increment-increment=10auto-increment-offset=2/etc/init.d/mysqld restart

(8) Turn on the primary serverBinarylog

Master2 Log Status:

Show Master status\g;*************************** 1. row*************************** file:master-bin.000002 position:120 binlog_do_db:binlog_ignore_db:m Ysql,information_schema,performance_schemaexecuted_gtid_set:1 row in Set (0.00 sec)

(9) ModificationMaster1configuration file

"maste1" Turn on the trunk

vi/etc/my.cnfreplicate-ignore-db = mysqlreplicate-ignore-db = Information_schemareplicate-ignore-db = Performance_ Schemarelay_log=mysqld-relay-binlog-slave-updates = on/etc/init.d/mysqld Restart

(10) Start Sync

Change MASTER to master_host= ' 192.168.0.2 ', master_user= ' repl_user ', master_password= ' Repl_user ', MAST Er_log_file= ' master-bin.000002 ', Master_log_pos=120;start slave;show slave status\g;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6C/E7/wKioL1VVwp3zfHgfAAAw6FWTk0I540.jpg "title=" Qq20150515174615.png "alt=" Wkiol1vvwp3zfhgfaaaw6fwtk0i540.jpg "/>

Error: [ERROR] Slave Sql:slave failed toinitialize relay log info structure from the repository, error_code:1872 workaround: Reset Slave

This article is from the "Notes" blog, so be sure to keep this source http://sunflower2.blog.51cto.com/8837503/1651691

Mysql---Master-Master Mutual Preparation