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

MMM (Master-Master replication manager for MySQL,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 with Perl language, which is mainly used to monitor and manage MySQL Master-Master (double master) replication. Although it is also called dual master replication, only one master is allowed to write to the master at the same time, and the other backup master provides partial read service to speed up the preheating of the backup master during the master-master switchover. It can be said that MMM script programs realize 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 the requirement for 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.

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 be run on the supervisor.

Mmm_agent: an agent process running on each MySQL server, performing monitoring probe work and executing a simple remote service shezhi.cijiaoben needs to 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 regulatory management, these IP will be bound to the available MySQL. When a MySQL goes down, the supervisor will migrate the VIP to other MySQL. Deployment environment host operating system IP address VIP address mysql-m1 master server CentOS 7.4 x86 '64192.168.100.200192.168.100.100 (Writer) mysql-m2 master server CentOS 7.4 x86' 64192.168.100.201192.168.100.100 (Writer) mysql-monitorCentOS 7.4 x86_64192.168.100.204mysql-m3 slave server CentOS 7.4 x86' 64192.168.100.202192.168.100.101 (Reader) mysql-m4 Deploy and install the required software packages from the server CentOS 7.4 x8631 64192.168.100.203192.168.100.102 (Reader) Modify the configuration file (four MySQL servers operate in the same way)

Set up the ALI cloud source and install the epel-release source.

Wget-O / etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo # download ALI Cloud Source yum-y install epel-release # install epel-release Source yum clean all & & yum makecache # clear the cache and generate a new cache file

Install the MariaDB database and enable the service (same as MySQL, and the steps for building are the same)

Yum-y install mariadb-server mariadbsystemctl stop firewalld.servicesetenforce 0systemctl start mariadb modifies the database master configuration file Send to another host and start the server # 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 # Library that does not generate binaries character_set_server=utf8 # character set log_bin=mysql_bin # binary log file function server_id=1log_slave_updates=true # enable synchronization of sync_binlog=1auto_increment_increment=2auto_increment_offset=1# scp / etc/my.cnf root@192.168.100.201:/etc# scp / etc/my.cnf root@192.168.100.202:/etc# scp / etc/my.cnf Root@192.168.100.203:/etc# Note: the server_id in the configuration file of the other three hosts needs to be modified. Master master replication can be configured by ensuring inconsistency (M1 and m2 master servers synchronize with each other)

Check the log file name and offset value of the other party

Mysql > show master status

Primary servers enhance access to each other (M1 and m2 servers are executed respectively)

# mysql-m1grant replication slave on *. * to 'replication'@'172.168.100.%' identified by' 123456 password # use the account replication password as 123456change master to master_host='172.168.100.201',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 # mysql-m2grant replication slave on *. * to 'replication'@'172.168.100.%' identified by' 123456 change master to master_host='172.168.100.200',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=245;# when executed on MariaDB-m2, the master_host address is the MariaDB-m1 address

Enable synchronization to view the master-slave status of the server

Start slave;show slave status\ Gantra # Slave_IO_Running: Yes# Slave_SQL_Running: Yes

Configure m3 and M4 servers as slave servers

Show master status;# views the log file of M1 server and the offset value (note the change of log file and offset value) # M1 and m2 synchronize with each other, where you only need to execute M1 on change master to master_host='172.168.100.200',master_user='replication',master_password='123456',master_log_file='mysql_bin.000004',master_log_pos=245;# m3 and M4, respectively.

Check the master-slave status of m3 and M4 respectively

Start slave;show slave status\ Gantra # Slave_IO_Running: Yes# Slave_SQL_Running: Yes

Install and configure MMM schema

Earlier, we configured the ALI cloud source using wget and easily installed MariaDB instead of MySQL, and built master synchronization and master-slave replication to complete the experiment. Now let's complete the MMM architecture.

Install mysql-mmm and modify the configuration file (five hosts have the same configuration)

# yum-y install mysql-mmm* / / We configured the epel source earlier Direct yum installation # vim / etc/mysql-mmm/mmm_common.conf / / configuration is as follows: active_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.168.100.200mode masterpeer Db2ip 172.168.100.201mode masterpeer db1ip 172.168.100.202mode slaveip 172.168.100.203mode slavehosts db1 Db2ips 172.168.100.100mode exclusivehosts db3, db4ips 172.168.100.101, 172.168.100.102mode balanced

Quickly modify configuration files for other hosts

Scp mmm_common.conf root@192.168.100.200:/etc/mysql-mmm/scp mmm_common.conf root@192.168.100.201:/etc/mysql-mmm/scp mmm_common.conf root@192.168.100.202:/etc/mysql-mmm/scp mmm_common.conf root@192.168.100.203:/etc/mysql-mmm/

Modify the name of the mmm_agent.conf file on the host

# vim / etc/mysql-mmm/mmm_agent.confthis db1 / / modified to db1, db2, db3 and db4 according to the plan

Authorize mmm_agent and mmm_moniter on all hosts

Mysql > grant super, replication client, process on *. * to 'mmm_agent'@'192.168.100.%' identified by' 123456 permissions MySQL > grant replication client on *. * to 'mmm_monitor'@'192.168.100.%' identified by' 123456 permissions # flush privileges; / / Reload permissions table

Configure the monitoring host (configured on mysql-monitor)

# vim / etc/mysql-mmm/mmm_mon.confinclude mmm_common.confip 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.168.100.200172.168.100.201172.168.100.202172.168.100.203auto _ set_online 10 # launch time changed to 10s# 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 enhance it.

Systemctl disable firewalld.servicesystemctl stop firewalld.servicesetenforce 0 starts the service to check the status of each node systemctl start mysql-mmm-monitor.service# ERROR: Can't connect to monitor daemonkeeper. If there is an error, you can try to restart the service to solve the mmm_control check all# check whether the monitoring server monitors all hosts is perfect # check the results of all OK, then the deployment is complete

At this point, MySQL-MMM has been successfully deployed, further, we can combine Amoeba to achieve read-write separation, Writer's virtual VIP can be written to the database, and Reader can be used to read data. And carry on the dual computer hot standby to the monitor server through Keepalived.

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.