Mariadb-mmm High-availability clusters

Source: Internet
Author: User
Tags db2 failover

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 MariaDB, MMM
MARIADB-M2 Primary Server Centos-7-x86_64 MariaDB, MMM
MARIADB-M3 from the server Centos-7-x86_64 MariaDB, MMM
MARIADB-M4 from the server Centos-7-x86_64 MariaDB, MMM
Mariadb-monitor Centos-7-x86_64 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 -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=‘‘,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=‘‘,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=‘‘,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                    /run/mysql-mmm-monitor.pidbin_path            /usr/libexec/mysql-mmmstatus_path         /var/lib/mysql-mmm/mmm_mond.statusping_ips  ,,,     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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.