MySQL Master Master Mutual Provisioning architecture

Source: Internet
Author: User
Tags db2 log log

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.