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

MMM introduction

MMM (Master-Master replication manager for MySQL) is a set of scripts that support dual-master failover and dual-master daily 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 double master replication, only one master is allowed to write to one master at the same time, and the other alternative master provides partial read service to speed up the warm-up of the backup master at the time of 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 completely guarantee data consistency, 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.

Advantages and disadvantages of MySQL-MMM

Advantages: high availability, good expansibility, automatic failure switching, for master and master synchronization, only one database write operation is provided at the same time to ensure the consistency of the data.

Cons: the Monitor node is a single point and can be combined with Keepalived to achieve high availability.

How MySQL-MMM works

MMM (Master-Master replication managerfor Mysql,Mysql Master Master replication Manager) is a set of flexible scripts based on perl implementation for monitoring and failover of mysql replication

And can manage the configuration of mysql Master-Master replication (only one node is writable at a time).

Mmm_mond: monitor the process, be responsible for all monitoring work, determine and handle all node role activities. This script needs to be run on the supervisor.

Mmm_agentd: agent processes that run on each mysql server (Master and Slave), perform monitoring probe work and perform simple remote service settings. This script needs to be run on the supervised machine.

Mmm_control: a simple script that provides commands for managing mmm_mond processes.

The supervisor of mysql-mmm will provide multiple virtual IP (VIP), including a writable VIP and multiple readable VIP. Through the management of supervision, these IP will be bound to the available mysql. When a mysql goes down, the supervisor will migrate the VIP.

To other 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, if

If you want to use mmm's backup tool, you also need to add a mmm_tools user.

Basic experimental environment

Experimental system: CentOS 7_x86_64

Experimental premise: the firewall and selinux are turned off!

Experimental description: there are 5 hosts in this experiment, and the IP allocation is shown in the table.

Experimental software: mariadb mysql-mmm mysql-mmm-monitor mysql-mmm-agent

Function, IP address and alias assignment table

Function IPid master server-192.168.137.10M1 master server two 192.168.137.11M2 slave server-192.168.137.12s1 slave server two 192.168.137.13s2mm monitoring 192.168.137.14mo

Experiment description topology diagram

Operation!

Of all the machines!

Turn off the firewall and security policy of all machines systemctl stop firewalld.service setenforce 0 get Aliyun's yum basic configuration wget-O / etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo install Epel source yum-y install epel-release clear yum cache, re-establish metadata yum clean all & & yum makecache install mariadb,MMM client and server yum install mariadb-server mariadb- yyum install mysql-mmm*-y

M1

Modify the M1 configuration file to configure mariadb# vim / etc/my.cnf to replace the following [mysqld] log_error=/var/lib/mysql/mysql.errlog=/var/lib/mysql/mysql_log.loglog_slow_queries=/var/lib/mysql_slow_queris.logbinlog-ignore-db=mysql,information_schemacharacter_set_server=utf8log_bin=mysql_binserver_id=1log_slave_updates=true / / allow slave to update the log sync _ binlog=1auto_increment_increment=2auto_increment_offset=1 start the service systemctl start mariadb.service

Use the scp command to synchronously copy the configuration file my.cnf to the other three mysql hosts, except for the monitoring machine MO. Scp / etc/my.cnf root@192.168.137.11:/etc/scp / etc/my.cnf root@192.168.137.12:/etc/scp / etc/my.cnf root@192.168.137.13:/etc/ must be modified separately in each machine / etc/my.cnf server_id=1 here M2 is changed to server_id=2, and so on

Successfully installed one by one

[root@cent] # mysqlWelcome to the MariaDB monitor. Commands end with; or\ g.Your MariaDB connection id is 2Server version: 5.5.60-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MariaDB [(none)] >

Master-slave replication

M _ 1m _ 2

Check the log and the location parameter MariaDB [(none)] > show master status;, and the result is the same MariaDB [(none)] > show master status. +-- + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+ | mysql_bin.000003 | 245 | | mysql Information_schema | +-+ 1 row in set (0.00 sec) M1 Magi M2 Mutual Authorization M1 Magazine M2sec MariaDB [(none)] > grant replication slave on *. * to 'replication'@'192.168.137.%' identified by' 123456' Synchronous M1:change master to master_host='192.168.137.11',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=245;M2:change master to master_host='192.168.137.10',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=245

Synchronization in S1 Magazine S2, where M1 is used as the synchronization object.

Change master to master_host='192.168.137.10',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=245;flush privileges; / / Refresh

Verify salve

Start salveMariaDB [(none)] > start slave;Query OK, 0 rows affected (0.00 sec) to view slave status MariaDB [(none)] > show slave status\ G see that these two items are yes, that is, successful Slave_IO_Running: YesSlave_SQL_Running: Yes

Configure MMM

Any machine.

Modify as follows

Vim / etc/mysql-mmm/mmm_common.conf cluster_interface ens33 / / modify the name of the network card pid_path / run/mysql-mmm-agent.pid bin_path / usr/libexec/mysql-mmm/ replication_user replication replication_password 123456 / / password agent_user mmm_agent agent_password 123456 / / password ip 192.168.137.10 / / M1 address mode master peer db2 / / points to M2 ip 192.168.137.11 / / M2 address mode master peer db1 / / point to M1 ip 192.168.137.12 / / S1 address mode slave ip 192.168.137.13 / / S2 address mode slave hosts db1 Db2 / / M1 and M2 ips 192.168.137.100 / / M1 and M2 jointly virtualize an IP mode exclusive hosts db3, db4 / / S1 and S2 ips 192.168.137.200, 192.168.137.150 / / S1 MagneS2 virtual IP mode balanced replicated to the other four hosts Including MO monitor scp / etc/mysql-mmm/mmm_common.conf root@192.168.137.11:/etc/mysql-mmm/

Operation of MO monitor computer

Vim / etc/mysql-mmm/mmm_mon.conf is modified as follows: 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.137.10192.168.137.11192.168. 137.12192.168.137.13192.168.137.14 IP auto_set_online / list all databases 10 / / Link wait time # 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_monitor monitor_password 123456 / / password debug 0

MMM is authorized in MySQL, each machine is required.

Grant super, replication client,process on *. * to 'mmm_agent'@'192.168.137.%' identified by' 123456 grant replication client on *. * to 'mmm_monitor'@'192.168.137.%' identified by' 123456crash Flush privileges; / / refresh

Update the server name of each machine, can not be the same, I set 12345.

Vim / etc/mysql-mmm/mmm_agent.conf is modified as follows: this db1

Set up five!

Start the service

M1Magi M2Magi S2 start the agentsystemctl start mysql-mmm-agent.servicesystemctl enable mysql-mmm-agent.service monitor computer and start the monitoring service systemctl start mysql-mmm-monitor.service

The inspection of the monitoring service, everything is normal as follows!

View the monitored host [root@zcent4 mysql-mmm] # mmm_control show db1 (192.168.137.10) master/ONLINE. Roles: writer (192.168.137.100) db2 (192.168.137.11) master/ONLINE. Roles: db3 (192.168.137.12) slave/ONLINE. Roles: reader (192.168.137.200) db4 (192.168.137.13) slave/ONLINE. Roles: reader (192.168.137.150) toggles virtual ip bound hosts [root@zcent4 mysql-mmm] # mmm_control move_role writer db2OK: Role 'writer' has been moved from' db1' to 'db2'. Now you can wait some time and check new roles info! [root@zcent4 mysql-mmm] # mmm_control show db1 (192.168.137.10) master/ONLINE. Roles: db2 (192.168.137.11) master/ONLINE. Roles: writer (192.168.137.100) db3 (192.168.137.12) slave/ONLINE. Roles: reader (192.168.137.200) db4 (192.168.137.13) slave/ONLINE. Roles: reader (192.168.137.150) testing monitoring function [root@zcent4 mysql-mmm] # mmm_control checks alldb4 ping [last change: 23:02:12 on 2018-09-07] OKdb4 mysql [last change: 2018-09-07 23:03:06] OKdb4 rep_threads [last change: 2018-09-07 23:11:57] OKdb4 rep_backlog [last change: 23:11:52 on 2018-09-07] OK: Backlog is nulldb2 ping [last change: 2018-09-07 22:57:09] OKdb2 mysql [last change: 2018-09-07 22:57:09] OKdb2 rep_threads [last change: 2018-09-07 22:57:09] OKdb2 rep_backlog [last change: 2018-09-07 22:57:09] OK: Backlog is nulldb3 ping [last change: 2018-09-07 23:02:09] OKdb3 mysql [last change: 2018-09-07 23:02:50] OKdb3 rep_threads [last change: 2018-09-07 23:11:42] OKdb3 rep_backlog [last change: 2018-09-07 23:11:36] OK: Backlog is nulldb1 ping [last change: 2018-09-07 22:57:09] OKdb1 mysql [last change: 2018-09-07 22:57:09] OKdb1 rep_threads [last change: 2018/09 / 07 22:57:09] OKdb1 rep_backlog [last change: 2018-09-07 22:57:09] OK: Backlog is null

Fault test!

First of all, M1M M2 is authorized to the test machine.

MariaDB [(none)] > grant all on *. * to 'testdb'@'192.168.137.14' identified by' 123456 question question OK, 0 rows affected (0.01 sec) MariaDB [(none)] > flush privileges;Query OK, 0 rows affected (0.00 sec)

Use the monitor computer as the client to test and log in successfully

[root@zcent4 mysql-mmm] # mysql- utestdb-p123456-h 192.168.137.100Welcome to the MariaDB monitor. Commands end with; or\ g.Your MariaDB connection id is 2430Server version: 5.5.60-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MariaDB [(none)] >

The client creates the database and uses the primary server to view

Client: MariaDB [(none)] > create database teest;Query OK, 1 row affected (0.00 sec) M1:MariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | mood | | mysql | | performance_schema | | teest | | test | +-+ 6 rows in set (0.00 sec)

Simulate M2 drop to see if the virtual IP of the primary server is automatically switched

M2: [root@cent] # systemctl stop mariadb.service monitor: [root@zcent4 mysql-mmm] # mmm_control show db1 (192.168.137.10) master/ONLINE. Roles: writer (192.168.137.100) db2 (192.168.137.11) master/HARD_OFFLINE. Roles: db3 (192.168.137.12) slave/ONLINE. Roles: reader (192.168.137.200) db4 (192.168.137.13) slave/ONLINE. Roles: reader (192.168.137.150)

Obviously, you can! Read-write replication is normal

Go online M2, but pay attention! M2 will not preempt, without this function!

Simulate S2 drop and observe the situation

S2: [root@cent] # systemctl stop mariadb.service monitor: [root@zcent4 mysql-mmm] # mmm_control show db1 (192.168.137.10) master/ONLINE. Roles: writer (192.168.137.100) db2 (192.168.137.11) master/ONLINE. Roles: db3 (192.168.137.12) slave/ONLINE. Roles: reader (192.168.137.150), reader (192.168.137.200) db4 (192.168.137.13) slave/HARD_OFFLINE. Roles: replacement has been implemented, and read-write replication is normal during this period

Summary

The 1.monitor program is responsible for monitoring the status of the db server, including the Mysql database, whether the server is running, whether the replication thread is normal, master-slave delay, etc., and it is also used to control the agent program to handle failures. two。 If the master-db1 master library is down and the master-db2 replication application lags behind master-db1, it becomes writable, and the data owner cannot guarantee consistency. 3.monitor will monitor the status of the db server every few seconds. If the db server has changed from failure to normal, then monitor will automatically set it to online state after 60s (default is 60s can be set to other values). In the course of the experiment, it was encountered that agent could not be started from the server, which was solved by rebooting. The reason is unknown.

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.