Shulou(Shulou.com)06/01 Report--

What is MMM?

MMM (MySQL Primary Primary replication Manager) is a set of scripts that support dual-master failover and dual-master day-to-day management. MMM is developed in Perl language, which is mainly used to monitor and manage MySQL Master-Master (double master) replication. Although it is called dual master replication, only one master is allowed to write to the master at the same time, and the other alternative master provides partial read service to speed up the warm-up of the backup master during the master-master switch. it can be said that the script program MMM realizes the function of failover on the one hand. On the other hand, the additional tool scripts can also achieve read load balancing of multiple Slave.

MMM provides both automatic and manual ways to remove the virtual IP of servers with high replication latency in a group of servers. At the same time, it can also back up data and achieve data synchronization between the two nodes. Because MMM cannot fully guarantee the consistency of data, MMM is suitable for scenarios where data consistency is not very high, but you want to maximize business availability. For those businesses that require high data consistency, a highly available architecture such as MMM is not recommended.

MMM is a flexible scripting program based on perl implementation for monitoring and failover of mysql replication, and for managing the configuration of MySQL Master-Master replication.

The description of the MMM highly available architecture is as follows

Mmm_mon: monitor the process, be responsible for all monitoring work, determine and handle all node role activities. This script needs to run mmm_agent on the supervisor: an agent process running on each MySQL server, monitoring probe work, and performing simple remote service settings. This script needs to run mmm_control on the supervisor: a simple script that provides commands for the mmm_mond process. The supervisor of mysql_mmm will provide multiple virtual IP (VIP), including a writable VIP and multiple readable VIP, managed by supervision. These IP will be bound to the available MySQL, and when one MySQL goes down, the supervisor will migrate the VIP to another MySQL.

During the whole supervision process, the relevant authorized users need to be added to the MySQL so that the 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 MMM's backup tool. What is a MariaDB database?

MariaDB database management system is a branch of MySQL and is mainly maintained by the open source community. The purpose of licensing MariaDB with GPL is to be fully compatible with MySQL, including API and the command line, making it an easy replacement for MySQL. In terms of storage engine, use XtraDB instead of MySQL's InnoDB. MariaDB's transaction-based Maria storage engine replaces MySQL's MyISAM storage engine, which uses a variant of Percona's XtraDB,InnoDB, and branch developers want to provide access to the upcoming MySQL 5.4 InnoDB performance. This version also includes PrimeBase XT (PBXT) and the FederatedX storage engine.

Project environment

This project uses five centos7 servers to simulate and build.

Host operating system IP address VIP main software MariaDB-m1 master server CentOS-7-x86_64172.16.10.23172.16.10.100MariaDB, MMMMariaDB-m2 master server CentOS-7-x86_64172.16.10.20172.16.10.100MariaDB, MMMMariaDB-m3 slave server CentOS-7-x86_64172.16.10.24172.16.10.110MariaDB, MMMMariaDB-m4 slave server CentOS-7-x86_64172.16.10.22172.16.10.120MariaDB, MMMMariaDB-monitorCentOS-7-x86_64172.16.10.21MMM

Set up MariaDB multi-master and multi-slave mode, install MariaDB to 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 / / clear all, and update the metadata cache

Configuring the epel source must operate on all five servers

Install mariadbyum-y install mariadb-server mariadb to turn off firewall and selinux firewall systemctl disable firewalld.servicesystemctl stop firewalld.servicesetenforce 0

The above three steps are the same on the other three database servers. Repeat them three times.

Configure the mariadb configuration file vim / etc/ my.cnf [mysqld] log_error=/var/lib/mysql/mysql.err / / error log file location log=/var/lib/mysql/mysql_log.log / / access log file location log_slow_queries=/var/lib/mysql_slow_queris.log / / slow log file location binlog-ignore-db=mysql,information_schema / / mysql Information_schema these two databases do not generate binary log files character_set_server=utf8log_bin=mysql_bin / / binary log files function enable server_id=10 / / id each machine is different log_slave_updates=true / / enable synchronous sync_binlog=1 / / 1 enable the service for the security value auto_increment_increment=2auto_increment_offset=1 systemctl start mariadb / / enable the service netstat-anpt | grep 3306 / / View the service status

When the configuration file is modified, you only need to delete the original [mysqld] directly, about nine lines. Replace with the above, the modification method of the four servers (MariaDB-m1, MariaDB-m2, MariaDB-m3, MariaDB-m4) is basically the same, the only difference is that the server_id is not the same, as long as it is different.

Configure MariaDB-m1 and MariaDB-m2 main mode to view the location of log bin log and posvalue first.

Operate mysql / / login database show master status in MariaDB-m1 and MariaDB-m2 respectively

MariaDB-m1 and MariaDB-m2 enhance each other's access rights

Execute # MariaDB-m1grant replication slave on *. * to 'replication'@'172.16.10.%' identified by' 123456 password on the two main servers / use account with replication password as 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 / / when executed on MariaDB-m1, the master_host address is the MariaDB-m2 address # MariaDB-m2grant replication slave on *. * to 'replication'@'172.16.10.%' identified by' 123456 master / execute separately on two master, slave does not need to execute 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 / / when executing on MariaDB-m2, the master_host address enables synchronization for the MariaDB-m1 address, while checking the master-slave status of the server.

The result of Slave_IO_Running: Yes and Slave_SQL_Running: Yes must be yesstart slave;show slave status\ G

Test whether the master synchronization is successful

Create a library on the master server, and then check to see if there is a newly created library on the slave server. if the newly created library exists, then try to delete the newly created library on the slave server. If the library on the master server is deleted successfully, it means that the master replication is created successfully.

Configure MariaDB-m3 and MariaDB-m4 as slave servers for MariaDB-m1

Operate on MariaDB-m3 and MariaDB-m4

View the location of log bin logs and poss values on MariaDB-m1 mysqlshow master status

Pay attention to the changes in log files and location parameters

Execute 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; on MariaDB-m3 and MariaDB-m4 to view the master-slave status start slave;show slave status\ G on MariaDB-m3 and MariaDB-m4 respectively

Install and configure MMM install MMM

We have installed the epel source on all five servers in the initial operation, so we only need to install it using yum here, and all servers need to install mysql-mmm*

Yum-y install mysql-mmm* configuration MMM

The profile is the same on all five servers

Vim / etc/mysql-mmm/mmm_common.confactive_master_role writercluster_interface ens33pid_path / run/mysql-mmm-agent.pidbin_path / usr/libexec/mysql-mmm/replication_user replicationreplication_password 123456agent_user mmm_agentagent_password 123456ip 172.16.10.23mode masterpeer db2ip 172.16.10.20mode masterpeer db1ip 172.16.10.24mode slaveip 172.16.10.22mode slavehosts db1 Db2ips 172.16.10.100mode exclusivehosts db3, db4ips 172.16.10.110, 172.16.10.120mode balancedip 172.16.10.22mode slavehosts db1, db2ips 172.16.10.100mode exclusivehosts db3, db4ips 172.16.10.110, 172.16.10.120mode balanced

Quickly modify the configuration file scp / etc/mysql-mmm/mmm_common.conf root@172.16.10.20:/etc/mysql-mmm/scp / etc/mysql-mmm/mmm_common.conf root@172.16.10.24:/etc/mysql-mmm/scp / etc/mysql-mmm/mmm_common.conf root@172.16.10.22:/etc/mysql-mmm/scp / etc/mysql-mmm/mmm_common.conf root@172 for other servers . 16.10.21:/etc/mysql-mmm/

Modify the / etc/mysql-mmm/mmm_agent.conf file on the database host, according to different hosts, modify different names vim / etc/mysql-mmm/mmm_agent.confthis db1 / / to db1, db2, db3, db4 respectively

Schedule all database servers to authorize grant super, replication client, process on *. * to 'mmm_agent'@'172.16.10.%' identified by' 123456 licenses for mmm_agent on all databases, grant replication client on *. * to 'mmm_monitor'@'172.16.10.%' identified by' 123456licenses for mmm_moniter on all databases, and reload permission table flush privileges Start mysql-mmm-agentsystemctl start mysql-mmm-agent.service / / enable service systemctl enable mysql-mmm-agent.service / / join the boot configuration monitoring host on all database servers to modify the / etc/mysql-mmm/mmm_mon.conf file vim / etc/mysql-mmm/mmm_mon.confinclude mmm_common.confip 127.0.0.1pid_path on the monitoring host (MariaDB-monitor) / run/mysql-mmm-monitor.pidbin_path / usr/libexec/mysql-mmmstatus_path / var/lib/mysql-mmm/mmm_mond.statusping_ips 172.16.10.23172.16.10.20172.16.10.24172.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_user mmm_monitormonitor_password 123456debug 0

Turn off the firewall and selinux firewall systemctl disable firewalld.servicesystemctl stop firewalld.servicesetenforce 0 to start the service, and check the status of each node systemctl start mysql-mmm-monitor.service

ERROR: Can't connect to monitor daemonkeeper. If an error occurs, try to restart the service to solve the problem.

Check whether the monitoring of all databases by the monitoring server is perfect. Mmm_control checks all / / check results need to be all ok

Fault test stops M1 to see if VIP drifts to m2 systemctl stop mariadb.service / / closes M1

Systemctl start mariadb.service / / enable M1 master will not preempt

Stop m3, check VIP drift status systemctl stop mariadb.service / / close m3

Systemctl start mariadb.service / / enable m3Magic VIP will return to their respective servers

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.