CentOS 7 mariadb-mmm High Availability cluster

Mariadb-mmm High-availability clusters

1.MYSQL-MMM Overview

MMM (Master-master Replication Manager for MySQL) is a set of scripting programs that support dual-master failover and dual-master daily management. MMM is developed using the Perl language, which is mainly used to monitor and manage MySQL Master-master (dual master) replication, although it is called dual master replication, but only one master is allowed to write at the same time in the business, and the other one provides partial read services to speed up the preheating of the alternate master at the time of primary master switching. Can say mmm this set of scripts to achieve the function of failover, on the other hand, its internal additional tool script can also achieve multiple slave read load Balancing
MMM provides both automatic and manual removal of the virtual IP of a server with high replication latency in a set of servers, as well as the ability to back up data, achieve data synchronization between two nodes, and more. Because MMM does not fully guarantee the consistency of data, MMM is suitable for the data consistency requirements are not very high, but also want to maximize the business availability of the scene. For businesses that require high data consistency, it is highly recommended to use MMM as a highly available architecture

2, mysql-mmm advantages and disadvantages

Advantages: High availability, scalability, automatic failover, for primary master synchronization, only one database write operation at the same time, ensure the consistency of the data
Cons: The monitor node is a single point that can be combined with keepalived for high availability

3, Mysql-mmm work original

MMM is a flexible scripting program based on Perl, used to monitor and failover MySQL replication, and to manage the configuration of MySQL Master-master replication (only one node is writable at a time)

Mmm_mond: Monitor processes, responsible for all monitoring tasks, determine and process all node role activities. This script needs to be run on a supervised machine

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

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

Throughout the regulatory process, you need to add the relevant authorized user to MySQL so that MySQL can support the maintenance of the supervision machine.

master1:192.168.94.11 DB1

Master2: 192.168.94.22 DB2

Slave1: 192.168.94.33 db3

Slave2: 192.168.94.44 DB4

Monitor: 192.168.94.55 Monitor

Vip:

192.168.94.250 writer

192.168.94.251 Reader

192.168.94.252 Reader

=============================================================================================================== =============================

Install MARIADB on Master1, Master2, slave1, Slave2, respectively

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

Modify the MY.CNF configuration file for Master1, Master2

[root@sqlmaster1 ~]# vim /etc/my.cnf
# Add the following content under [mysqld]
Log-bin=mysql-bin
Log-slave-updates=true
Server-id=11 # id number cannot be the same master2 is 22
Relay-log=relay-log-bin
Relay-log-index=relay-log-bin.index

Modify the MY.CNF configuration file for slave1, slave2

[root@sqlslave1 ~]# vim /etc/my.cnf
# Add the following content under [mysqld]
Server-id=33 # slave2 is 44
Relay-log=relay-log-bin
Relay-log-index=relay-log-bin.index

Start MARIADB

[root@sqlmaster1 ~]# systemctl start mariadb
[root@sqlmaster2 ~]# systemctl start mariadb
[root@sqlslave1 ~]# systemctl start mariadb
[root@sqlslave2 ~]# systemctl start mariadb

Configure primary master replication, two master grant permissions to each other

# master1
[root@sqlmaster1 ~]# mysql
MariaDB [(none)]> show master status;
MariaDB [(none)]> grant replication slave on *.* to ‘sqlsync‘@‘192.168.94.%‘identified by ‘123123’;
MariaDB [(none)]> flush privileges;
# master2 Do the same thing
[root@sqlmaster2 ~]# mysql
MariaDB [(none)]> show master status;
MariaDB [(none)]> grant replication slave on *.* to ‘sqlsync‘@‘192.168.94.%‘identified by ‘123123’;
MariaDB [(none)]> flush privileges;
=========================================================== =========================================================== =======
# master1
MariaDB [(none)]> show master status;
# master2
MariaDB [(none)]> change master to master_host='192.168.94.11', master_user=‘sqlsync', master_password=‘123123’, master_log_file=‘mysql_bin.000001’, master_log_pos=358;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;
# Slave_IO_Running, Slave_SQL_Running are all Yes
=========================================================== =========================================================== =======
# master2
MariaDB [(none)]> show master status;
# master1
Change master to master_host=‘192.168.94.22‘, master_user=‘sqlsync’, master_password=‘123123’, master_log_file=‘mysql_bin.000001’, master_log_pos=358;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;

# Slave_IO_Running, Slave_SQL_Running are all Yes 

Configuring Master-slave replication slave1, slave2

 # slave1
[root@sqlslave1 ~]# mysql
MariaDB [(none)]> change master to master_host='192.168.94.11', master_user=‘sqlsync', master_password=‘123123’, master_log_file=‘mysql_bin.000001’, master_log_pos=358;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;
# Slave_IO_Running, Slave_SQL_Running are all Yes
# slave2 Do the same operation
[root@sqlslave2 ~]# mysql
MariaDB [(none)]> change master to master_host='192.168.94.11', master_user=‘sqlsync', master_password=‘123123’, master_log_file=‘mysql_bin.000001’, master_log_pos=358;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;
# Slave_IO_Running, Slave_SQL_Running are all Yes 

Installing the MYSQL-MMM server

wget -o/etc/Yumrepos.d/centos-base.repo http://mirrors.aliyun.com/repo/ Centos-7.repo yuminstall epel-release mysql-mmm*

Configure Mysql-mmm

[root@sqlmaster1 ~]# vim /etc/mysql-mmm/mmm_common.conf

Editing the mmm_mon.conf configuration file on the monitor server

[Email protected] ~]# vim/etc/mysql-mmm/mmm_mon.conf
[root@sqlmaster1 ~]# scp /etc/mysql-mmm/mmm_common.conf 192.168.94.22:/etc/mysql-mmm/

[root@sqlmaster1 ~]# scp /etc/mysql-mmm/mmm_common.conf 192.168.94.33: /etc/mysql-mmm/

[root@sqlmaster1 ~]# scp /etc/mysql-mmm/mmm_common.conf 192.168.94.44:/etc/mysql-mmm/

[root@sqlmaster1 ~]# scp /etc/mysql-mmm/mmm_common.conf 192.168.94.55:/etc/mysql-mmm/

[root@sqlmaster1 ~]# vim /etc/mysql-mmm/mmm_agent.conf

# master1 is this db1 , master1 is this db2 , slave1 is this db3 , slave2 is this db4

Authorized Mmm-agent

~]# mysqlmariadb [(none)]'mmm_agent'@'192.168.94.%'  '123123';
MRAIDDB [(None)]> flush privileges;

Open Agent Service

 
[root@sqlmaster1 ~]# systemctl enable mysql-mmm-agent
Created symlink from /etc/systemd/system/multi-user.target.wants/mysql-mmm-agent.service to /usr/lib/systemd/system/mysql-mmm-agent.service.
[root@sqlmaster1 ~]# systemctl start mysql-mmm-agent

Detecting on the monitor server

CentOS 7 mariadb-mmm High Availability cluster