MySQL Master master configuration

MySQL Master backup synchronization, is actually each other master-slave, A to B master-slave, B to a master-slave

Configuration is similar to MySQL master and slave, just a little bit to change

Note: Before experimenting, please close iptables and SELinux, iptables-f && service iptables Save

The SELinux value of vim/etc/selinux/config is changed to Disabled

Environment Description: a:192.168.3.116

b:192.168.3.251

Set login password for abmysql, command: mysqladmin-uroot password ' yourpass '

Each login Abmysql

A:grant Replication Slave,file On * * to ' repl ' @ ' 192.168.3.251 ' identified by ' 123456 ';

Flush privileges;

B:grant Replication Slave,file On * * to ' repl ' @ ' 192.168.3.116 ' identified by ' 123456 ';

Flush privileges;

Configure each MySQL configuration file

A:

Vim/etc/my.cnf

Change the Server-id and add the following statement

Server-id = 1

Binlog-do-db=test

Binlog-ignore-db=mysql

Replicate-do-db=test

Replicate-ignore-db=mysql

Log-slave-updates

Sync_binlog=1

auto_increment_increment=2

Auto_increment_offset=1

B:

Server-id = 2

Binlog-do-db=test

Binlog-ignore-db=mysql

Replicate-do-db=test

Replicate-ignore-db=mysql

Log-slave-updates

Sync_binlog=1

auto_increment_increment=2

auto_increment_offset=2

Where: log-slave-updates logs the replicated SQL executed to the binary log
Sync_binlog when a binary log is written to the Binlog file, the MySQL server synchronizes it to disk
Auto_increment_increment,auto_increment_offset is primarily used in primary master replication to control the operation of auto_increment columns

Restart the Abmysql service and enter the MySQL database

A:flush tables with read lock;

Show master status; View the Master_log_file and Master_log_pos values for a at this time

Unlock tables;

Slave stop;

Change Master to master_host= ' 192.168.3.251 ', master_user= ' repl ', master_password= ' 123456 ', master_log_file= ' Mysql-bin.000004 ', master_log_pos=106; #此处的master_log_file和master_log_pos为B上show Master Status value

Start slave;

B:

Flush tables with read lock;

Show master status; View Master_log_file and Master_log_pos values for this time B

Unlock tables;

Slave stop;

Change Master to master_host= ' 192.168.3.116 ', master_user= ' repl ', master_password= ' 123456 ', master_log_file= ' Mysql-bin.000004 ', master_log_pos=106; #此处的master_log_file和master_log_pos为A上show Master Status value

Start slave;

View status, show slave status on A and b respectively;

Slave_io_running:yes

Slave_sql_running:yes

It means that the mutual master from the configuration succeeds;

Test: For synchronous database test operation, CREATE table Tb1 on a, show tables on B, if the tb1 created on a indicates that a to B synchronization succeeds, and on B create table tb2,a show tables; appears TB2 , the B to a synchronization succeeds;

This article is from "The Miracle Teenager" blog, please be sure to keep this source http://raffaelexr.blog.51cto.com/8555551/1749915

MySQL Master master configuration