Deploying MYSQL-MMM high-availability clusters

about MMM
? 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 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 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 have a high level of conformance to data, it is highly recommended that you use MMM as a highly available architecture.

Mysql-mmm Advantages and Disadvantages

Advantages : High availability, scalability, automatic failover, for master synchronization, at the same time only provide a database write operation, to ensure the consistency of the data.
disadvantage : Monitor node is a single point, can be combined with keepalived to achieve high availability, the number of host requirements, the need to achieve read and write separation, the program is a challenge.
Mysql-mmm Working principle

? MMM (master-master replication managerfor mysql,mysql primary master Replication Manager) is a flexible script that is based on Perl and is used to monitor and fail-over Mysql replication. And can manage the configuration of MySQL Master-master replication (only one node is writable at the same 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 certain MySQL downtime, 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 Supervisor 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.

• experimental Environment (MARIADB database is a branch of MySQL save installation time it instead of MySQL)

Host	IP	main software
MySQL master server 1	192.168.126.129	Epel-release, Mysql-mmm, mariadb
MySQL Master server 2	192.168.126.130	Epel-release, Mysql-mmm, mariadb
MySQL from server 1	192.168.126.131	Epel-release, Mysql-mmm, mariadb
MySQL from server 2	192.168.126.132	Epel-release, Mysql-mmm, mariadb
Monitor	192.168.126.132	Epel-release, Mysql-mmm

• Set up local Yum source (4 MySQL servers are noted in different places)

   #yum -y install mariadb-server mariadb#systemctl stop firewalld.service#setenforce 0#vi /etc/my.cnf  （9dd 删除原有的配置 添加下面的） [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_schema  //不需要同步的数据库名称character_set_server=utf8log_bin=mysql_bin                  //开启binlog日志用于主从复制server_id=1                            //4台id设置不同log_slave_updates=truesync_binlog=1auto_increment_increment=2    //字段一次递增多少auto_increment_offset=1           //自增字段的起始值：1，3，5，7...等 奇数ID# systemctl start mariadb  //开启服务

• Configure Primary Master Synchronization

• Primary server 1

  mysal> grant replication slave on *.* to ‘replication‘@‘192.168.126.%‘ identified by ‘123456‘;  主2 ： 查看文件名 和偏移量mysql> show master status;主1 ：在主1上指定主2的IP 、日志文件、 偏移量mysql> change master to master_host=‘192.168.126.130‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000001‘,master_log_pos=775;mysql> start slave;  //开启同步mysql> show slave status\G;   //查看同步情况

• Primary server 2

  mysal> grant replication slave on *.* to ‘replication‘@‘192.168.126.%‘ identified by ‘123456‘;  主1 ： 查看文件名 和偏移量mysql> show master status;主2 ：在主2上指定主1的IP 、日志文件、 偏移量mysql> change master to master_host=‘192.168.126.129‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000001‘,master_log_pos=775;mysql> start slave;

• Two slave servers

  主1 ： 查看文件名 和偏移量mysql> show master status;两台从服务器：在从服务器上指定主1的IP 、日志文件、 偏移量mysql> change master to master_host=‘192.168.126.129‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000001‘,master_log_pos=775;mysql> start slave;

• Install MMM installed on all servers, Epel source to be configured well

  # yum -y install mysql-mmm*# cd /etc/mysql-mmm/# vi /etc/mysql-mmm/mmm_common.conf   //所有主机上都要配置

  ……

Cluster_interface Ens33
......
Replication_user replication???? Copy user password
Replication_password 123456???? Copy user
Agent_user mmm_agent???? Proxy User
Agent_password 123456???? Proxy User Password

IP 192.168.126.129
Mode master??? Main
Peer DB2??? The host name of the server that is equivalent to MASTER-DB1, which is the server host name of MASTER-DB2

IP 192.168.126.130
Mode Master
Peer DB1

IP 192.168.126.131
Mode slave???? From

IP 192.168.126.132
mode slave

<role writer>
Hosts DB1, DB2
IPs 192.168.126.200???? Externally provided virtual IP for write operations
Mode exclusive???? The exclusive representative allows only one master, that is, to provide only one write IP
</role>

<role reader>
Hosts DB3, DB4
IPs 192.168.126.210, 192.168.126.220????//Virtual IP that provides read operations externally
Mode balanced????//balanced represents load Balancing
</role>

• Replicating to other servers

  SCP mmm_common.conf [Email protected]:/etc/mysql-mmm/

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

• Monitor server Configuration

  # cd /etc/mysql-mmm/# vi mmm_mon.confping_ips      192.168.126.129,192.168.126.130,192.168.126.131,192.168.126.132 //数据库服务器地址auto_set_online     10        //自动上线时间 10秒.....

• Authorization for mmm_agent and mmm_moniter on all databases

  mysql> grant super, replication client, process on *.* to ‘mmm_agent‘@‘192.168.126.%‘ identified by ‘123456‘;mysql> grant replication client on *.* to ‘mmm_monitor‘@‘192.168.126.%‘ identified by ‘123456‘;mysql> flush privileges;   //刷新权限

• Modify the mmm_agent.conf of all databases

  # vi /etc/mysql-mmm/mmm_agent.conf....this db1         //主1为db1、  主2为db2、   从1为db3 、  从2为db4

• Restart the server on all servers

  Systemctl Start Mysql-mmm-agent.service

  # systemctl enable mysql-mmm-agent.service # systemctl start mysql-mmm-monitor.service

• Monitor server

  # mmm_control show //查看各节点的情况# mmm_control checks all // 检测监控功能是否完善   全是OK# mmm_control move_role writer db1     //切换虚拟IP 绑定的主机 db1

Deploying MYSQL-MMM high-availability clusters