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://s3.51cto.com/wyfs02/M00/6E/CD/wKioL1WI7XeSwpE7AAFbsf5YYTc394.jpg "title=" mariadb-based on MMM for read-write separation and high availability. jpg "alt=" wkiol1wi7xeswpe7aafbsf5yytc394.jpg "/>

#注: System environment centos6.6#vip172.16.10.30 is writable VIP, other three groups are readable vip# writable VIP can only switch between master, readable VIP can switch between master and slave # Front-end application can connect any readable VIP for data reading, connect writable VIP for data writing

Dual Master one from configuration

DB1 Configuration

[[email protected] ~]# vim /etc/mysql/my.cnf datadir = /mydata/datalog-bin= /mydata/binlogs/master-bin        #二进制文件位置relay-log=/mydata/relaylogs/ relay-bin     #中继日志位置binlog_format =mixedserver-id        = 1auto_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 read_only       = 1  [[email protected on each node based on host name]  ~]# service mysqld Start 

Authorize available replication users to record binary log locations

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/CA/wKiom1WHvdKCD3JSAAGApMycIjo175.jpg "title=" 1.jpg " alt= "Wkiom1whvdkcd3jsaagapmycijo175.jpg"/>

DB2 Configuration

[[email protected] ~]# vim /etc/mysql/my.cnf  datadir = /mydata/datalog-bin=/mydata/binlogs/master-bin        relay-log=/mydata/relaylogs/relay-bin    binlog_format=mixedserver-id        = 11auto_increment_offset=2           auto_increment_increment=2      log_slave_updates = 1             sync_binlog = 1   skip-name-resolveread_only       = 1[[email protected] ~]#  service mysqld start 

Authorize available replication users to record binary log locations

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/CA/wKiom1WHv86h7NWfAAGApMycIjo536.jpg "title=" 1.jpg " alt= "Wkiom1whv86h7nwfaagapmycijo536.jpg"/>

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

DB3 Configuration

[Email protected] ~]# vim/etc/mysql/my.cnf datadir =/mydata/datarelay-log=/mydata/relaylogs/relay-binserver-id = 111read_only = 1skip-name-resolve[[email protected] ~]# service mysqld start

Connect to each server

DB1 Connection DB2

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/C7/wKioL1WH4BmSvZCCAAIvhSxxE70529.jpg "title=" 2.jpg " alt= "Wkiol1wh4bmsvzccaaivhsxxe70529.jpg"/>

DB2 Connection DB1

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/C7/wKioL1WH4DODxovDAAItwXooMVE622.jpg "title=" 3.jpg " alt= "Wkiol1wh4dodxovdaaitwxoomve622.jpg"/>

DB3 Connection DB1

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/CA/wKiom1WH32nCVL7ZAAIsnj8NGCk293.jpg "title=" 4.jpg " alt= "Wkiom1wh32ncvl7zaaisnj8ngck293.jpg"/>

Master-Slave testing

#DB1创建数据库MariaDB [(None)]> CREATE database TestDB; Query OK, 1 row affected (0.14 sec) #DB2创建表MariaDB [(None)]> CREATE table TESTDB.TB1 (name char (k) not NULL); Query OK, 0 rows affected (0.19 sec) #DB3查看数据MariaDB [(none)]> show tables in testdb;+------------------+| Tables_in_testdb |+------------------+| TB1 |+------------------+1 row in Set (0.13 sec)

Data consistent, master-slave replication partially completed

MMM configuration

Install the required packages

[[email protected] ~]# Yum install mysql-mmm*-y# installing MYSQL-MMM package Group on all nodes

Authorized monitoring and Proxy users

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/C8/wKioL1WIAaezt1-sAAErcnD6lcM503.jpg "title=" 5.jpg " alt= "Wkiol1wiaaezt1-saaercnd6lcm503.jpg"/>

#DB节点全部需要授权, you can authorize it on a single node

[[email protected] ~]# vim /etc/mysql-mmm/mmm_common.conf active_master_role       writer
#将以上文件同步到DB1-db3 Node [[email protected] ~]# scp /etc/mysql-mmm/mmm_common.conf  172.16.10.123:/etc/mysql-mmm/mmm_common.conf                                            100%  776      0.8kb/s   00:00 [[email protected] ~]# scp /etc/ mysql-mmm/mmm_common.conf 172.16.10.124:/etc/mysql-mmm/mmm_common.conf                                             100%  776     0.8KB/s   00:00 [[email  Protected] ~]# scp /etc/mysql-mmm/mmm_common.conf 172.16.10.125:/etc/mysql-mmm/mmm_common.conf                                             100%  776     0.8kb/s   00:00
[[email protected] ~]# vim /etc/mysql-mmm/mmm_mon.confinclude mmm_common.conf< monitor>    ip                   127.0.0.1    pid_path             /var/run/mysql-mmm/mmm_mond.pid    bin_path             /usr/libexec/mysql-mmm     status_path         /var/lib/mysql-mmm/mmm_ mond.status    ping_ips             172.16.10.123,172.16.10.124,172.16.10.125  #监控主机     auto_set_online      60    # The kill_host_bin does not exist  By default, though the&nbSp;monitor will    # throw a warning about it missing.   See the section 5.10  "kill host    #  Functionality " in the pdf documentation.    #    #  kill_host_bin     /usr/libexec/mysql-mmm/monitor/kill_host     #</monitor>
DB1-DB3 Configuration
[[email protected] ~]# vim/etc/mysql-mmm/mmm_agent.confinclude mmm_common.conf# the ' this ' variable refers to this server  . Proper operation requires # that's ' this ' server (db1 by default), as-well as all other servers, with the # Proper IP addres SES set in Mmm_common.conf.this db1#db1-db3 modifies this configuration file (DB1,DB2,DB3) according to its actual role
Start the service
#在各监控节点启动mmm-agent Service [[email protected] ~]# service mysql-mmm-agent startstarting mmm agent Daemon: [OK] #在Monitor上启动mmm-monitor service [[email protected] ~]# service mysql-mmm-monitor startstarting mmm monitor Daemo N: [OK]
View current status
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/D0/wKiom1WI6Wqj-5EnAACzM2x0XS8838.jpg "title=" 6.jpg " alt= "Wkiom1wi6wqj-5enaaczm2x0xs8838.jpg"/>
View each node VIP status
#以db1为例
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/D1/wKiom1WJAHrCfN1HAAHS3xzhmes734.jpg "title=" 7.jpg " alt= "Wkiom1wjahrcfn1haahs3xzhmes734.jpg"/>
Highly Available tests
Simulating DB1 failures, viewing node status
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/D1/wKiom1WJARyTjEYQAAErGw_U4LQ539.jpg "title=" 8.jpg " alt= "Wkiom1wjarytjeyqaaergw_u4lq539.jpg"/>
And look at DB1 's VIP status.
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/D1/wKiom1WJAZ3gZebzAAGdNtYlFew337.jpg "title=" 9.jpg " alt= "Wkiom1wjaz3gzebzaagdntylfew337.jpg"/>
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 experiment first of all here, interested friends can continue to explore based on the keepalived to realize the high availability of monitor, here do not delve into the experimental process encountered problems can message exchange. The above is only for individual learning to organize, if there are mistakes, big God do not spray ~ ~ ~
This article is from the "North Scholar" blog, please make sure to keep this source http://scholar.blog.51cto.com/9985645/1664551
MYSQL/MARIADB based on MMM for read-write separation and high availability