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

Introduction to MMM:

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 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 is a flexible scripting program based on Perl to monitor and fail over mysql replication and manage the configuration of Mysql Master-Master replication

Description of MMM High availability Architecture:

1.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 machine

2.mmm_agent: an agent process that runs on each Mysql server, performs monitoring probe work and performs simple remote service settings. This script needs to be run on each node

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

The monitoring side of 4.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 one Mysql goes down, the monitor 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 and a mmm_agent user.

Deployment of the lab case environment:

This lab uses five servers to simulate the construction (CentOS7 operating system)

Master server master1: ip:192.168.100.10 db1 vip: 192.168.100.199

Master server master2:ip: 192.168.100.20 db2

Slave server slave1: ip:192.168.100.30 db3 vip: 192.168.100.33

Slave server slave2: ip: 192.168.100.40 db4 vip: 192.168.100.44

Monitoring server monitor ip: 192.168.100.50

The process of the experiment has been sorted out as follows:

-all master01/master02/slave01/slave02 needs to install mysql database-

Configure the ALI cloud source, and then install the epel-release source. (all four master and slave servers need to be installed)

Systemctl stop firewalld.service

Setenforce 0

Wget-O / etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

Yum-y install epel-release / / install the epel source

Yum clean all & & yum makecache / / clear everything and reset the data cache

-set up a local yum source-do it directly below

Yum-y install mariadb-server mariadb (all four master and slave servers need to be installed)

Systemctl stop firewalld.service

Setenforce 0

Systemctl start mariadb

-modify the ml master configuration file-(required for all four master and slave servers)-

Vi / etc/my.cnf (9dd deletes previous content)

Vi / etc/my.cnf (9dd deletes the content before the [mysqld] tag and reinserts the following configuration)

[mysqld]

Log_error=/var/lib/mysql/mysql.err / / enable error log function

Log=/var/lib/mysql/mysql_log.log / / enable general logging function

Log_slow_queries=/var/lib/mysql_slow_queris.log / / enable slow log function

Binlog-ignore-db=mysql,information_schema / / name of the database that does not require synchronization

Character_set_server=utf8 / / sets the default character set to utf-8

Log_bin=mysql_bin / / enable binary log for master-slave data replication

Server_id=1 / / the value of each server_id cannot be the same.

Log_slave_updates=true / / this database is down, and the standby database takes over

Sync_binlog=1

Auto_increment_increment=2 / / fields are incremented by 2 at a time

The starting value of auto_increment_offset=1 / / self-increment field: 1, 3, 5, 5, 7. Equal odd ID

After modification:

Systemctl stop firewalld.service

Setenforce 0

Systemctl start mariadb

Netstat-anpt | grep 3306

-when there is no problem, copy the configuration file to the other 3 database servers and start the server-

One-machine configuration or: scp / etc/my.cnf root@192.168.100.10:/etc/ this command is executed on the file source server

Note: server_id=2 2, 3 11, 4 22

-configure master master replication-two master servers replicate each other-

Show master status; / / record the name and location of the log file, and view it on both hosts.

-grant subordinate permissions to m2 on M1 and M1 on m2.

Grant replication slave on. To 'replication' @' 192.168.100% 'identified by' 123456hosts; / / both hosts execute and never need

Change master to master_host='192.168.100.20',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=245

Note: on M1, specify the log file name and location parameters on m2. On m2, you want to specify M1 in turn. (note the IP address, file name and offset to be changed)

Start slave; / / enable synchronization function

Show slave status\ G

Slave_I0_Running: Yes

Slave_SQL_Running: Yes

-do it on both sets-pay attention to the changes in log files and location parameters-

Note: both slaves need to point to one of the master servers (point to M1 here)

Change master to master_host='192.168.100.10',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=245

Start slave; / / enable synchronization

Show slave status\ Gumbnail / View status

-Test master-slave, master-master, synchronization-

Set up the database and test the synchronization

-install MMM- on all servers.-Note, epel source should be configured.

Yum-y install mysql-mmm*

-configure mmm after installation--

Cd / etc/mysql-mmm/

Vim mmm_common.conf / / should be configured on all hosts, and multiple copies should be directly copied.

Cluster_interface ens33

.

Replication_user replication

Replication_password 123456

Agent_user mmm_agent

Agent_password 123456

Ip 192.168.100.10

Mode master

Peer db2

Ip 192.168.100.20

Mode master

Peer db1

Ip 192.168.100.30

Mode slave

Ip 192.168.100.40

Mode slave

Hosts db1,db2

Ips 192.168.100.199 / / Virtual IP

Mode exclusive

Hosts db3,db4

Ips 192.168.100.33, 192.168.100.44 / / Virtual IP

Mode balanced

Remote replication override profile:

Scp mmm_common.conf root@192.168.100.20:/etc/mysql-mmm/

Scp mmm_common.conf root@192.168.100.30:/etc/mysql-mmm/

Scp mmm_common.conf root@192.168.100.40:/etc/mysql-mmm/

Vim / etc/mysql-mmm/mmm_common.conf check one by one

-the last monitoring server also needs to install mmm- (epel source is installed first)-

Systemctl stop firewalld.service

Setenforce 0

Wget-0 / etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

Yum-y install epel-release

Yum clean all & & yum makecache (but not executed)

Yum install-y mysql-mmm*

Scp mmm_common.conf root@192.168.100.50:/etc/mysql-mmm/ execute on M1

-configure on the monitor server--

Cd / etc/mysql-mmm/

Vim mmm_mon.conf

Ping_ips 192.168.100.10192.168.100.20192.168.100.30192.168.100.40 / the address of the server monitored by the monitor

Auto_set_online 10 / / Auto launch time 10 seconds

Monitor_user mmm_monitor

Monitor_password 123456 / / change password

-authorize mmm_agent on all databases-(enter the database)-

Grant super, replication client, process on. To 'mmm_agent'@'192.168.100.%' identified by' 123456'

-authorize mmm_moniter on all databases-(enter the database)-

Grant replication client on. To 'mmm_monitor'@'192.168.48.%' identified by' 123456'

Flush privileges; / / Refresh

-modify the mmm_agent.conf- of all databases

Vim / etc/mysql-mmm/mmm_agent.conf

This db1 / / adjust one by one according to the previous plan of the agent name

-start mysql-mmm-agent-- on the database server where it is located

Systemctl start mysql-mmm-agent.service

Systemctl enable mysql-mmm-agent.service / / Boot self-start

-configure on the monitor server

Cd / etc/mysql-mmm/

Vim mmm_mon.conf

.

Ping_ips

192.168.100.10192.168.100.20192.168.100.30192.168.100.40 / / Database server address

Auto_set_online 10 / / Auto launch time 10 seconds

.

Systemctl start mysql-mmm-monitor.service / / start the monitoring service mysql-mmm-montior

Mmm_control show / / View the status of each node:

Db1 (192.168.235.132) master/ONLINE. Roles:writer (192.168.100.199)

Db2 (192.168.235.191) master/ONLINE. Roles:

Db3 (192.168.235.177) slave/ONLINE. Roles:reader (192.168.100.33)

Db4 (192.168.235.181) slave/ONLINE. Roles:reader (192.168.100.44)

Mmm_control checks all / / requires all kinds of OK

Mmm_control move_role writer db2 / / switch the virtual IP200 to the db2 server

Service mariadb restart / / monitor computer as client

Systemctl restart mysql-mmm-agent

-Fault testing-

Stop M1 to confirm that the virtual address 200 is moved to m2. Note: the Lord will not preempt.

Systemctl stop mariadb.service

Then monitor the server to see if you switch to m2: mmm_control show

You can turn off one from the server and try it. Note: will never preempt

Log in to the database on the M1 server to authorize the login for the address of the monitor

Grant all on. To 'testdba'@'192.168.100.50' identified by' 123456'

Flush privileges; / / Refresh

In theory, the monitoring server only plays the role of monitoring alone. Here, we temporarily configure it as a client:

Yum install-y mariadb-server mariadb

-Log in on the monitoring server-

Mysql-utestdba-p-h 192.168.100.199 / / Virtual address

Create data and test synchronization

The script ends here! The summary of this article:

MySQL-MMM is suitable for scenarios where data consistency requirements are not very high, but you want to maximize business availability

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.