Mariadb-mmm High-availability clusters

What is MMM?

MMM (MySQL master replication Manager) 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 master provides a partial read service to speed up the preheating of the alternate host during the main master switchover. Can say mmm this set of scripts to implement 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 the data, MMM is suitable for scenarios where the consistency of data is not very high, but wants to maximize the availability of the business. For businesses with high data consistency requirements, it is highly recommended that you use MMM as a highly available architecture.

MMM is a flexible script that is based on Perl and is used to monitor and failover MySQL replication, and to manage the configuration of MySQL master-master replication.

A description of the MMM high-availability architecture is as follows

• Mmm_mon: Monitor processes, responsible for all monitoring tasks, determine and process all node role activities. This script needs to run on the monitor machine
• Mmm_agent: Run the agent process on each MySQL server, complete the monitoring of probe work and perform simple remote service settings. This script needs to run on the monitor machine
• Mmm_control: A simple script that provides commands for the Mmm_mond process
• Mysql_mmm's regulatory side will provide multiple virtual IPs (VIPs), including a writable VIP, multiple readable VIPs, through regulatory management. These IPs are bound to the available MySQL, and when one MySQL goes down, the monitor migrates the VIP to other MySQL.
  Throughout the regulatory process, it is necessary to add relevant authorized users to MySQL so that MySQL can support the maintenance of the supervision machine. Authorized users include a mmm_monitor user and a mmm_agent user, and add a mmm_tools user if you want to use the MMM Backup tool.

What is a mariadb database?

MARIADB database management System is a branch of MySQL, mainly by the open source community in the maintenance, the use of GPL licensing MARIADB is fully compatible with MySQL, including the API and command line, so that it can easily become a substitute for MySQL. For the storage engine, use XTRADB instead of MySQL InnoDB. MARIADB, the transaction-based Maria Storage engine, replaces the MySQL MyISAM storage engine, which uses a variant of the Percona Xtradb,innodb, which the branch developers want to provide access to the upcoming MySQL 5.4 InnoDB performance. This version also includes the PrimeBase XT (PBXT) and Federatedx storage engines.

Project Environment

This project uses five CENTOS7 server simulation to build

Host	Operating System	IP Address	VIP	main software
MARIADB-M1 Primary Server	Centos-7-x86_64	172.16.10.23	172.16.10.100	MariaDB, MMM
MARIADB-M2 Primary Server	Centos-7-x86_64	172.16.10.20	172.16.10.100	MariaDB, MMM
MARIADB-M3 from the server	Centos-7-x86_64	172.16.10.24	172.16.10.110	MariaDB, MMM
MARIADB-M4 from the server	Centos-7-x86_64	172.16.10.22	172.16.10.120	MariaDB, MMM
Mariadb-monitor	Centos-7-x86_64	172.16.10.21	MMM

Build MARIADB Multi-master multi-slave mode install mariadb configure Ali Cloud Source, install epel-release source

wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repoyum -y install epel-releaseyum clean all && yum makecache   //清空所有，重新更新元数据缓存

Configuring the Epel source must be operated on five servers

Installing MARIADB

yum -y install mariadb-server mariadb

Shutting down firewalls and selinux firewalls

systemctl disable firewalld.servicesystemctl stop firewalld.servicesetenforce 0

The above three steps on the other three database server A touch, repeat three times

Configuring the MARIADB 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        //mysql,information_schema这两个数据库不生成二进制日志文件character_set_server=utf8log_bin=mysql_bin                                //二进制日志文件功能开启server_id=10                                     //id每台都不相同log_slave_updates=true                           //开启同步sync_binlog=1                                    //1为安全值auto_increment_increment=2auto_increment_offset=1

Open service

systemctl start mariadb    //开启服务netstat -anpt | grep 3306  //查看服务状态

When the configuration file is modified, simply delete the original [mysqld] directly, about nine lines. Replace with the above content, four servers (MARIADB-M1, mariadb-m2, MARIADB-M3, MARIADB-M4) modification method basically consistent, the only difference is server_id not the same, as long as the difference.

Configuring MARIADB-M1, mariadb-m2 main main mode

• Check the location of log bin logs and POS values first
  operate separately in MARIADB-M1, mariadb-m2

  mysql   //登陆数据库show master status;

  
  

• MARIADB-M1, mariadb-m2 each other to improve access rights
  Execute separately on both primary servers

  #MariaDB-m1grant replication slave on *.* to ‘replication‘@‘172.16.10.%‘ identified by ‘123456‘;//使用账户为replication 密码为123456change master to master_host=‘172.16.10.20‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000003‘,master_log_pos=245;//当在MariaDB-m1上执行时，master_host地址为MariaDB-m2地址···#MariaDB-m2grant replication slave on *.* to ‘replication‘@‘172.16.10.%‘ identified by ‘123456‘;//在两台master上分别执行，slave不需要执行change master to master_host=‘172.16.10.23‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000003‘,master_log_pos=245;//当在MariaDB-m2上执行时，master_host地址为MariaDB-m1地址

• Turn on sync and view the server's master and slave status
  Slave_io_running:yes, Slave_sql_running:yes The results of these two entries must be Yes

  start slave;show slave status\G;

  
  

  To test whether the primary master synchronization was successful

  Create a library on the primary server and see if there is a newly created library from the server, and if the newly created library exists, then try to delete the newly created library from the server, and if the library on the master server is successfully deleted at this point, the primary master copy is created successfully.

  Configure MARIADB-M3, mariadb-m4 as mariadb-m1 from the server

  Operate on MARIADB-M3, MARIADB-M4

• View the location of the log bin log and POS values on the MARIADB-M1

  mysqlshow master status;

  Note Changes in log files and positional parameters
  

• executed separately on MARIADB-M3 and MARIADB-M4

  change master to master_host=‘172.16.10.23‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000004‘,master_log_pos=245;

• View master and slave status on Mariadb-m3, MARIADB-M4, respectively
  
  Installation Configuration mmm installation mmm

  We have installed the Epel source for all five servers in the first operation, so we only need to install Yum, and all the servers need to be installed mysql-mmm*

  yum -y install mysql-mmm*

  Configure MMM

  The configuration file is the same in the five servers

  Vim/etc/mysql-mmm/mmm_common.confactive_master_role writer

Quickly modify configuration files for other servers

scp /etc/mysql-mmm/mmm_common.conf [email protected]:/etc/mysql-mmm/scp /etc/mysql-mmm/mmm_common.conf [email protected]:/etc/mysql-mmm/scp /etc/mysql-mmm/mmm_common.conf [email protected]:/etc/mysql-mmm/scp /etc/mysql-mmm/mmm_common.conf [email protected]:/etc/mysql-mmm/

Modify the/etc/mysql-mmm/mmm_agent.conf file on the database host, modify different names according to different hosts

vim /etc/mysql-mmm/mmm_agent.confthis db1  //按着顺序分别修改为db1、db2、db3、db4

Make a wave arrangement on all database servers

• Authorization for mmm_agent on all databases

  grant super, replication client, process on *.* to ‘mmm_agent‘@‘172.16.10.%‘ identified by ‘123456‘;

• Authorization for Mmm_moniter on all databases

  grant replication client on *.* to ‘mmm_monitor‘@‘172.16.10.%‘ identified by ‘123456‘;

• Reload Permission table

    flush privileges;  

• Start mysql-mmm-agent on all database servers

  systemctl start mysql-mmm-agent.service    //开启服务systemctl enable mysql-mmm-agent.service   //加入开机自启动

  Configuring the monitoring host to modify the/etc/mysql-mmm/mmm_mon.conf file on the monitoring host (Mariadb-monitor)

  vim /etc/mysql-mmm/mmm_mon.confinclude mmm_common.conf<monitor>ip                  127.0.0.1pid_path            /run/mysql-mmm-monitor.pidbin_path            /usr/libexec/mysql-mmmstatus_path         /var/lib/mysql-mmm/mmm_mond.statusping_ips            172.16.10.23,172.16.10.20,172.16.10.24,172.16.10.22auto_set_online     10# The kill_host_bin does not exist by default, though the 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>

Shutting down firewalls and selinux firewalls

systemctl disable firewalld.servicesystemctl stop firewalld.servicesetenforce 0

Start the service to view the status of each node

systemctl start mysql-mmm-monitor.service

Error:can ' t connect to monitor daemon!, if an error occurs, try restarting the service to resolve

View the monitoring server for all database monitoring is complete

mmm_control checks all  //检查结果需为全部ok

The failure test stops M1 to see if the VIP is drifting to m2

systemctl stop mariadb.service   //关闭m1

systemctl start mariadb.service  //开启m1 主不会抢占

Stop M3 to see VIP drift status

systemctl stop mariadb.service   //关闭m3

systemctl start mariadb.service   //开启m3，VIP会重新回到各自服务器上

Mariadb-mmm High-availability clusters