Mysql-mmm High Availability

Mysql-mmm High Availability MMM introduction

MMM (Master-master Replication Manager for Mysql,mysql primary master Replication Manager) is a set of scripting programs that support dual-master failover and dual-master daily management. This set of scripts consists of several components:

1, Mmm_mon: Monitor the process, responsible for all monitoring work, determine and handle all node role activities. This script needs to be run on a supervised machine.

2, Mmm_agent: Run the agent process on each MySQL server, complete the monitoring of the probe work and perform simple remote service settings. This script needs to be run on a supervised machine.

3. Mmm_control: A simple script that provides commands to manage the mmm_mond process.

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 MySQL, when a certain MySQL outage, the supervision will transfer VIP to other MySQL.

Experimental environment

Host	IP Address
MYSQL-M1 Primary Server	192.168.58.131
MYSQL-M2 Primary Server	192.168.58.136
MYSQL-M3 from the server	192.168.58.146
MYSQL-M4 from the server	192.168.58.147
Mysql-monitor Monitoring Proxy Server	192.168.58.148

Constructing MySQL multi-master multi-slave mode in the experiment process

1, on four MySQL server, all installed MySQL, process omitted

2, configure the Ali cloud Source, and then install the epel-rlease source, in order to install the MYSQL-MMM tool kit below.

[[email protected] ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo[[email protected] ~]# yum -y install epel-release[[email protected] ~]# yum clean all && yum makecache

3. Configuration modification M1 master configuration file.

vim /etc/my.cnf [mysqld]log_error=/var/lib/mysql/mysql.err                     #配置错误日志log=/var/lib/mysql/mysql_log.log                       #配置常用日志log_slow_queries=/var/lib/mysql_slow_queris.log        #配置慢日志binlog-ignore-db=mysql,information_schema              #配置不需要记录二进制日志的数据库character_set_server=utf8                              #配置字符集log_bin=mysql_bin                                      #开启binlog日志用于主从数据复制server_id=1                                            #每台server-id的值不能一样log_slave_updates=true                                 #此数据库宕机，备用数据库接管sync_binlog=1                                          #允许同步二进制日志auto_increment_increment=2                             #字段依次递增多少auto_increment_offset=1                                #自增字段的起始值：1、3、5等奇数ID

After configuration, copy the configuration file to another master server.

[[email protected] ~]# scp /etc/my.cnf [email protected]:/etc/

4, configure MYSQL-M1, mysql-m2 main main mode
First view the location of the log bin log and POS values.

[[email protected] ~]# mysqlwelcome to the MariaDB Monitor. Commands End With; or \g.your MySQL connection ID is 1065Server version:5.5.24-log Source distributioncopyright (c), Oracle, Mari ADB Corporation Ab and others. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement. MySQL [(None)]> Show Master status;+------------------+----------+--------------+--------------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+--------------------------+|      mysql_bin.000002 |              107 | | Mysql,information_schema |+------------------+----------+--------------+--------------------------+1 row in Set (  0.00 sec) [[email protected] ~]# mysqlwelcome to the MariaDB Monitor. Commands End With; or \g.your MySQL connection ID is 1065Server version:5.5.24-log Source distributioncopyright (c), Oracle, Mari ADB Corporation Ab and others. Type ' help; ' or ' \h ' For help. Type ' \c ' to clear the current input statement. MySQL [(None)]> Show Master status;+------------------+----------+--------------+--------------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+--------------------------+|      mysql_bin.000002 |              107 | | Mysql,information_schema |+------------------+----------+--------------+--------------------------+1 row in Set ( 0.00 sec)

Then, MYSQL-M1, mysql-m2 to each other to enhance access rights.

mysql-m1MySQL [(none)]> grant replication slave on *.* to ‘replication‘@‘192.168.58.%‘ identified by ‘123456‘; MySQL [(none)]> change master to master_host=‘192.168.58.136‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000002‘,master_log_pos=107;MySQL [(none)]> flush privileges;mysql-m2MySQL [(none)]> grant replication slave on *.* to ‘replication‘@‘192.168.58.%‘ identified by ‘123456‘; MySQL [(none)]> change master to master_host=‘192.168.58.131‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000002‘,master_log_pos=107;MySQL [(none)]> flush privileges;

Finally see the MYSQL-M1, mysql-m2 server master and slave status, the main view

Slave_io_running:yes

Slave_sql_running:yes.

MySQL [(none)]> start slave;MySQL [(none)]> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.58.131                  Master_User: replication                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql_bin.000002          Read_Master_Log_Pos: 107               Relay_Log_File: localhost-relay-bin.000012                Relay_Log_Pos: 253        Relay_Master_Log_File: mysql_bin.000002             Slave_IO_Running: Yes            Slave_SQL_Running: Yes

Indicates that the primary master synchronization configuration was successful.
Test Master Master Sync, create a new library in MYSQL-M1 test01

mysql-m1MySQL [(none)]> create database test01;MySQL [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || #mysql50#.mozilla  || bbs                || mysql              || performance_schema || test               || test01             |+--------------------+7 rows in set (0.22 sec)mysql-m2                                #测试成功MySQL [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || #mysql50#.mozilla  || mysql              || performance_schema || test               || test01             |+--------------------+7 rows in set (0.22 sec)

5, configuration myqsl-m3, mysql-m4 as mysql-m1 from the library.

First, copy the/etc/my.cnf file on the mysql-m1 to MYQSL-M3, mysql-m4 two servers.

mysql-m1[[email protected] ~]# scp /etc/my.cnf [email protected]:/etc/[[email protected] ~]# scp /etc/my.cnf [email protected]:/etc/

View the status values for the database in MYSQL-M1.

MySQL [(none)]> show master status;+------------------+----------+--------------+--------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |+------------------+----------+--------------+--------------------------+| mysql_bin.000002 |      107 |              | mysql,information_schema |+------------------+----------+--------------+--------------------------+1 row in set (0.00 sec)

executed separately on MYSQL-M3 and MYSQL-M4.

mysql-m3MySQL [(none)]> change master to master_host=‘192.168.58.131‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000002‘,master_log_pos=107;mysql-m4MySQL [(none)]> change master to master_host=‘192.168.58.131‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000002‘,master_log_pos=107;

View the master-slave status of the mysql-m3, MYSQL-M4 server, respectively, as shown below.

MySQL [(none)]> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.58.131                  Master_User: replication                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql_bin.000002          Read_Master_Log_Pos: 107               Relay_Log_File: localhost-relay-bin.000012                Relay_Log_Pos: 253        Relay_Master_Log_File: mysql_bin.000002             Slave_IO_Running: Yes            Slave_SQL_Running: Yes

Installation Configuration Mysql-mmm

CentOS default does not have MYSQL-MMM software package, because before we epel official source has been installed, on the five hosts are installed MMM

[[email protected] ~]# yum -y install mysql-mmm*

After installation, the MMM is configured

[[email protected] mongodb1]# vim/etc/mysql-mmm/mmm_common.conf 

Configured on the terminal as the monitor server

cd /etc/mysql-mmm/ #改密码vim mmm_mon.conf

Authorization for mmm_agent on all databases

MySQL [(none)]> grant super, replication client, process on *.* to ‘mmm_agent‘@‘192.168.58.%‘ identified by ‘123456‘;#为mmm_agent授权

Authorization for Mmm_moniter on all databases

MySQL [(none)]> grant replication client on *.* to ‘mmm_monitor‘@‘192.168.58.%‘ identified by ‘123456‘;#为mmm_monitor授权MySQL [(none)]> flush privileges#刷新权限设置

Modify the mmm_agent.conf of all databases

[[email protected] mysql-mmm]# vim /etc/mysql-mmm/mmm_agent.confinclude mmm_common.conf# The ‘this‘ variable refers to this server. Proper operation requires# that ‘this‘ server (db1 by default), as well as all other servers, have the# proper IP addresses set in mmm_common.conf.this db1 #分别在四台数据库服务器上设置为db1,db2,db3,db4~ 

Start mysql-mmm-agent on all database servers

[[email protected] mysql-mmm]# systemctl start mysql-mmm-agent.service#启动mmm-agent服务[[email protected] mysql-mmm]# systemctl enable mysql-mmm-agent.service#设置mmm-agent服务开机启动

Configuring on the monitor server

[[email protected] mysql-mmm]# cd/etc/mysql-mmm/[[email protected] mysql-mmm]# vim mmm_mon.conf < monitor> IP 127.0.0.1 pid_path/run/mysql-mmm-monitor.pid bin_path/usr /libexec/mysql-mmm status_path/var/lib/mysql-mmm/mmm_mond.status ping_ips 192.168.58.131,192.168 .58.136,192.168.58.146,192.168.58.147 #指定监管的服务器IP地址 Auto_set_online # The Kill_host_bin does not exist by Default, though the monitor would # throw a warning about it missing. See the sections 5.10 "Kill Host # Functionality" in the PDF documentation. # # Kill_host_bin/usr/libexec/mysql-mmm/monitor/kill_host #</monitor>

When we stop the MYSQL-M3 MySQL service, the corresponding VIP is automatically bound to the MYSQL-M4

[[email protected] mysql-mmm]# mmm_control show db1(192.168.58.131) master/ONLINE. Roles: writer(192.168.58.100) db2(192.168.58.136) master/ONLINE. Roles: db3(192.168.58.146) slave/HARD_OFFLINE. Roles: db4(192.168.58.147) slave/ONLINE. Roles: reader(192.168.58.210)(192.168.58.200)

Mysql-mmm High Availability