MySQL Master Master Mutual Provisioning architecture

MySQL Master-Master Interoperability Architecture

Enterprise MySQL clusters are highly available, scalable, manageable, and cost-efficient. MySQL Master master is a common solution in the enterprise. In this architecture, although the mutual master from, but at the same time only one MySQL can read and write, a mysqk can only read operation

1. Configuration

Environment: DB1 (Master) mysql-5.1.73-3.el6_5.x86_64 192.168.32.130

DB2 (slave) mysql-5.1.73-3.el6_5.x86_64 192.168.32.129

MySQL vip:192.168.32.100

Installation

Yum-y install MySQL Mysql-devel

/etc/init.d/mysqld start

Modify the MySQL configuration file

DB1 Configuration

[Mysqld]

Server-id=1

Log-bin=mysql-bin

Relay-log=mysql-relay-bin

Replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

DB2 Configuration

[Mysqld]

server-id=2

Log-bin=mysql-bin

Relay-log=mysql-relay-bin

Replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

Server-id : Node identity, master and slave cannot be the same, must be globally unique. Log-bin is the Binlog log feature of MySQL, and Mysql-bin represents a naming format for log files. Relay-log defines the naming format of the Relay-log log file. Replicate-wild-ignore-table is a replication filtering option that allows you to filter databases or tables that you do not need to replicate.

use BINLOG-DO-DB (logging database) with caution and binlog-ignore-db ( is not to log the database name, the middle of multiple databases separated by commas; ), Replicate-do-db ( you can specify which library's data is replicated only ), Replicate-ignore-db ( Filtering is not a query-based string, but is based on your used database )

Recommended for Replicate-wild-ignore-table and replicate-wild-do-table

Why the BINLOG-DO-DB option for MySQL is dangerous-bingqihan-chinaunix blog

Http://blog.chinaunix.net/uid-24500107-id-2602925.html

Create a replication user and authorize

Create a replication user in the Db1mysql library

Grant Replication Slave on * * to ' repl_user ' @ ' 192.168.32.129 ' identified by ' www.123 ';

Show master status;

Set DB1 as your home server in the Db2mysql library

Change Master tomaster_host= ' 192.168.32.130 ', master_user= ' repl_user ', master_password= ' www.123 ', master_log_file= ' Mysql-bin.000002 ', master_log_pos=342;

The Master_log_file and master_log_pos on this side are the results of the DB1 on the show master status.

Start the slave service

Start slave;

Mysql> show Slave status\g;

1. row***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.32.130

Master_user:repl_user

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000002

read_master_log_pos:342

relay_log_file:mysql-relay-bin.000002

relay_log_pos:251

relay_master_log_file:mysql-bin.000002

Slave_io_running:yes

Slave_sql_running:yes

replicate_do_db:

replicate_ignore_db:

Replicate_do_table:

Replicate_ignore_table:

Replicate_wild_do_table:

replicate_wild_ignore_table:mysql.%,test.%,information_schema.%

last_errno:0

Last_error:

skip_counter:0

exec_master_log_pos:342

relay_log_space:406

Until_condition:none

Until_log_file:

until_log_pos:0

Master_ssl_allowed:no

Master_ssl_ca_file:

Master_ssl_ca_path:

Master_ssl_cert:

Master_ssl_cipher:

Master_ssl_key:

seconds_behind_master:0

Master_ssl_verify_server_cert:no

last_io_errno:0

Last_io_error:

last_sql_errno:0

Last_sql_error:

1 row in Set (0.00 sec)

ERROR:

No query specified

The focus here is on slave_io_running and slave_sql_running. These two are the master-slave replication threads that the slave node runs. Normally both of these values are Yes

Create a replication user in the Bd2mysql library

Grant Replication Slave on * * to ' repl_user ' @ ' 192.168.32.130 ' identified by ' www.123 ';

Show Masterstatus;

Set DB2 as your primary server in DB1

Change Master tomaster_host= ' 192.168.32.129 ', master_user= ' repl_user ', master_password= ' www.123 ', master_log_file= ' Mysql-bin.000005 ', master_log_pos=267;

The Master_log_file and master_log_pos on this side are the results of the DB2 on the show master status.

Start the slave service

Start slave

show slave status\g;

View slave Run status

Create database on DB1 to view on DB2, create database on DB2 to view on DB1, find all synchronized

Show variables like ' server% '; View server_id

This article from "Do not abandon!" Do not give up "blog, be sure to keep this source http://6437769.blog.51cto.com/6427769/1654364

MySQL Master Master Mutual Provisioning architecture