MYSQL/MARIADB based on MMM for read-write separation and high availability

Objective

MMM (master-master replication managerfor mysql,mysql primary master Replication Manager) is a flexible script that is based on Perl and is used to monitor and fail-over Mysql replication. And can manage the configuration of MySQL Master-master replication (only one node is writable at the same time).

MMM

Advantages and Disadvantages

Advantages: High availability, scalability, automatic failover, for master synchronization, at the same time only provide a database write operation, to ensure the consistency of the data.

Cons: The monitor node is a single point that can be combined with keepalived for high availability.

Working principle

Mysql-mmm's regulatory side will provide multiple virtual IP (VIP), including a writable VIP, multiple readable VIPs, through regulatory management, these IP will be tied to the available server, when one server down, the VIP migration to other servers.

Implementation process

Experimental topology

650) this.width=650; "Src=" http://mmbiz.qpic.cn/mmbiz_jpg/ Ip70vic417doshq4rlialkwpno0el0xiiefj02bptzedogqhcgrnbwtibzpnrazh8qabgsiapcfynmgicnuhztfaw9g/640?wx_fmt=jpeg &tp=webp&wxfrom=5&wx_lazy=1 "style=" Margin:0px;padding:0px;height:auto;vertical-align:middle;width: auto; "alt=" 640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy "/>

#注: System Environment CentOS6.6

#VIP172.16.10.30 is a writable VIP, the other three groups are readable VIPs

#可写VIP只能在Master之间切换, readable VIP can switch between master and slave

#前端应用可连接任意可读VIP进行数据读取, connect a writable VIP for data writing

Dual Master one from configuration

DB1 Configuration

[[Email protected] ~] # vim/etc/mysql/my.cnf

DataDir =/mydata/data

Log-bin=/mydata/binlogs/master-bin #二进制文件位置

Relay-log=/mydata/relaylogs/relay-bin #中继日志位置

Binlog_format=mixed

server-id = 1

Auto_increment_offset=1 #双主复制中自增长字段的起始值

auto_increment_increment=2 #双主复制中自增长字段的步长

Log_slave_updates = 1 #开启从库更新操作写入二进制日志功能

Sync_binlog = 1 #可保证事务日志及时写入磁盘文件

Skip-name-resolve #禁用DNS反向解析, if you do not write this, you need to communicate at each node based on host name

READ_ONLY = 1

[[Email protected] ~] # service Mysqld start

Authorize available replication users to record binary log locations

650) this.width=650; "Src=" http://mmbiz.qpic.cn/mmbiz_jpg/ Ip70vic417doshq4rlialkwpno0el0xiie2bojwfiak0vn6aqzudibqvhfbb3v9swmvpibib9j655zt3dvyz1o2ia47qw/640?wx_fmt=jpeg &tp=webp&wxfrom=5&wx_lazy=1 "style=" Margin:0px;padding:0px;height:auto;vertical-align:middle;width: auto; "alt=" 640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy "/>

DB2 Configuration

[[Email protected] ~] # vim/etc/mysql/my.cnf

DataDir =/mydata/data

Log-bin=/mydata/binlogs/master-bin

Relay-log=/mydata/relaylogs/relay-bin

Binlog_format=mixed

server-ID = 11

auto_increment_offset=2

auto_increment_increment=2

Log_slave_updates = 1

Sync_binlog = 1

Skip-name-resolve

READ_ONLY = 1

[[Email protected] ~] # service Mysqld start

Authorize available replication users to record binary log locations

#因为实验之前都做了完整初始化, here the binary files are in the same location

DB3 Configuration

[[Email protected] ~] # vim/etc/mysql/my.cnf

DataDir =/mydata/data

Relay-log=/mydata/relaylogs/relay-bin

server-ID = 111

READ_ONLY = 1

Skip-name-resolve

[[Email protected] ~] # service Mysqld start

Connect to each server

DB1 Connection DB2

DB2 Connection DB1

DB3 Connection DB1

Master-Slave testing

Data consistent, master-slave replication partially completed

MMM configuration

Install the required packages

Authorized monitoring and Proxy users

Monitor Configuration

DB1-DB3 Configuration

650) this.width=650; "Src=" http://mmbiz.qpic.cn/mmbiz_png/ ip70vic417doshq4rlialkwpno0el0xiieothl79v5h0cn2r4bjc1ayhyphvricyb4xjwnv1edyut0ib2c8cngivlw/640?wx_fmt=png& Tp=webp&wxfrom=5&wx_lazy=1 "style=" Margin:0px;padding:0px;height:auto;vertical-align:middle;width:auto; " alt= "640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy="/>

Start the service

650) this.width=650; "Src=" http://mmbiz.qpic.cn/mmbiz_png/ ip70vic417doshq4rlialkwpno0el0xiiehthact0k0xr19u3vsdfiz2qprvyvzdtbzmljnibn6dwdl49kphuo90q/640?wx_fmt=png& Tp=webp&wxfrom=5&wx_lazy=1 "style=" Margin:0px;padding:0px;height:auto;vertical-align:middle;width:auto; " alt= "640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy="/>

View current status

650) this.width=650; "Src=" http://mmbiz.qpic.cn/mmbiz_jpg/ Ip70vic417doshq4rlialkwpno0el0xiieaiaj2hf6d2jktl67pohickgtrgpuld1m6yu1vokiaicx8lot7plvjaj7iq/640?wx_fmt=jpeg &tp=webp&wxfrom=5&wx_lazy=1 "style=" Margin:0px;padding:0px;height:auto;vertical-align:middle;width: auto; "alt=" 640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy "/>

View each node VIP status

650) this.width=650; "Src=" http://mmbiz.qpic.cn/mmbiz_png/ Ip70vic417doshq4rlialkwpno0el0xiielru9ibia0ncx9tyonggfmss6pakbnkzznnwsaafyjhzibx7wvmtrskniaa/640?wx_fmt=png &tp=webp&wxfrom=5&wx_lazy=1 "style=" Margin:0px;padding:0px;height:auto;vertical-align:middle;width: auto; "alt=" 640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy= "/>650" this.width=650; "src=" http:// mmbiz.qpic.cn/mmbiz_jpg/ Ip70vic417doshq4rlialkwpno0el0xiieyvx4bkk9wvosmpdd4xay2iaevlv4marbhsfm8ck1izww9icqa2p0wtyw/640?wx_fmt=jpeg &tp=webp&wxfrom=5&wx_lazy=1 "style=" Margin:0px;padding:0px;height:auto;vertical-align:middle;width: auto; "alt=" 640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy "/>

Highly Available tests

Simulating DB1 failures, viewing node status

650) this.width=650; "Src=" http://mmbiz.qpic.cn/mmbiz_jpg/ Ip70vic417doshq4rlialkwpno0el0xiiey11dgtiaxdvqiaauopaaurbkdxuju9k1ltxemjznyt7xv8t5sd17vnna/640?wx_fmt=jpeg &tp=webp&wxfrom=5&wx_lazy=1 "style=" Margin:0px;padding:0px;height:auto;vertical-align:middle;width: auto; "alt=" 640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy "/>

And look at DB1 's VIP status.

650) this.width=650; "Src=" http://mmbiz.qpic.cn/mmbiz_jpg/ Ip70vic417doshq4rlialkwpno0el0xiieicib0r1d16akpxh9kqwdsiadiaxdcogkm3wdoegteayt9pdgqqzrp68pka/640?wx_fmt=jpeg &tp=webp&wxfrom=5&wx_lazy=1 "style=" Margin:0px;padding:0px;height:auto;vertical-align:middle;width: auto; "alt=" 640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy "/>

VIP has been transferred to other nodes, other interested please self-test, here will not show

The end

MYSQL/MARIADB based on MMM to achieve read-write separation and high-availability experiments first of all here, interested friends can continue to explore based on the keepalived implementation of the high availability of monitor, here do not delve into.

This article from "rendering unchanged Yesterday" blog, declined reprint!

MYSQL/MARIADB based on MMM for read-write separation and high availability