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

What is 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, its internal additional tool script can also achieve multiple Slave read load balancing MMM is a set of flexible script procedures, based on Perl implementation, used to monitor and fail over mysql replication and manage Mysql Master-Master replication configuration on the MMM highly available architecture description as follows: 1) mmm_mon: monitoring process, responsible for all monitoring work, determine and handle all node role activities. This script needs to run on the supervisor 2) mmm_agent: the agent process that runs on each Mysql server, completes the monitoring probe work, and performs simple remote service settings. This script needs to run 3) mmm_control on each node: a simple script that provides commands to manage the mmm_mond process. 4) the monitor side 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 monitor will migrate the VIP to other Mysql during the whole supervision process. Relevant authorized users need to be added to the Mysql so that Mysql can support the maintenance of the monitoring machine. Authorized users include a mmm_monitor and a mmm_agent user. Case environment deployment:

This case environment is simulated by five servers.

Host operating system IP address master1 CentOS7 192.168.195.128master2 CentOS7 192.168.195.137slave1 CentOS7 192.168.195.140slave2 CentOS7 192.168.195.141monitor CentOS7 192.168.195.142 case implementation 1, first configure the ALI cloud source, and then install the epel-release source. CentOS does not have mysql-mmm software package by default. It is officially recommended to use epel source. EPerl source and MMM should be installed on all five hosts.

Service firewalld stop

Setenforce 0

Wget-0 / etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo / / use the yum source provided by Aliyun

Yum-y install epel-release

Yum clean all & & yum makecache

2. Build mysql multi-master and multi-slave mode (1) install mariadb online on the four hosts of master1,master2,slave1,slave2 (free community version of mysql)

Yum-y install mariadb-server mariadb

Systemctl start mariadb

(2) modify the configuration file my.cnf first modify the master1 main configuration file

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

Systemctl restart mariadb

Netstat-anpt | grep 3306

Tcp 0 0 0.0.0.0 3306 0.0.0.015 * LISTEN 5029/mysqld

When there is no problem, copy the configuration file my.cnf to the other three database servers and start the server. The server_id parameters of each mysql host cannot be the same, and the other profile parameters are the same.

Scp / etc/my.cnf root@192.168.195.137:/etc/

Scp / etc/my.cnf root@192.168.195.140:/etc/

Scp / etc/my.cnf root@192.168.195.141:/etc/

Note: the server_id parameters in the configuration file cannot be the same and need to be modified. The second server_id=2, the third 3, and the fourth 4 (3) configure Master-Master master replication-two master servers replicate each other. First enter the location of mysql to view log-bin logs and poss values (update position location change at any time during authorization) on master1.

MariaDB [(none)] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql_bin.000003 | 411 | | mysql,information_schema | |

+-+

1 row in set (0.00 sec)

On master2

MariaDB [(none)] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql_bin.000004 | 491 | | mysql,information_schema | |

+-+

1 row in set (0.00 sec)

Master1,master2 enhance access to each other Note: specify the log file name and location parameters on the master2 on the master1. On m2, you want to specify M1 in turn. Check the authorization on master1 at any time when authorizing.

MariaDB [(none)] > grant replication slave on *. * to 'replication'@'192.168.195.%' identified by' abc123'

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > change master to master_host='192.168.195.137',master_user='replication',master_password='abc123',master_log_file='mysql_bin.000004',master_log_pos=491

Query OK, 0 rows affected (0.06 sec)

MariaDB [(none)] > flush privileges

Query OK, 0 rows affected (0.00 sec)

Authorize on master2

MariaDB [(none)] > grant replication slave on *. * to 'replication'@'192.168.195.%' identified by' abc123'

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)] > change master to master_host='192.168.195.128',master_user='replication',master_password='abc123',master_log_file='mysql_bin.000003',master_log_pos=411

Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)] > flush privileges

Query OK, 0 rows affected (0.01 sec)

View the master-slave status of the master1,master2 server separately

Start slave; / / enable synchronization function

Show slave status\ G

Slave_I0_Running: Yes / / these two option parameters must be yes

Slave_SQL_Running: Yes

(4) Test master synchronization and create a new library school on master1

MariaDB [(none)] > create database school

Query OK, 1 row affected (0.01sec)

You can see the library school you just created on master2. Synchronization has been completed.

MariaDB [(none)] > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | school |

| | test |

+-+

5 rows in set (0.01 sec)

(5) configure slave1,slave2 as master1 to first view the status value of master1 from the database

MariaDB [(none)] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql_bin.000003 | 583 | | mysql,information_schema | |

+-+

1 row in set (0.00 sec)

Set the settings separately on slave1,slave2

MariaDB [(none)] > change master to master_host='192.168.195.128',master_user='replication',master_password='abc123',master_log_file='mysql_bin.000003',master_log_pos=583

Check the master / slave status of slave1,slave2 start slave; / / enable the synchronization function

Show slave status\ G

Slave_I0_Running: Yes / / these two option parameters must be yes

Slave_SQL_Running: master replication synchronization between Yes database servers, master-slave replication synchronization configuration 3, installation configuration mysql-mmm (1) yum online installation of mysql-mmm*yum-y install mysql-mmm* on all servers (2) configure mmm after installation, modify / etc/mysql-mmm/mmm_common.conf configuration file, the content of the configuration file is the same for all hosts in the system Including the modified content of the monitoring host monitor with bold part

Vim / etc/mysql-mmm/mmm_common.conf

Active_master_role writer

Cluster_interface ens33

Pid_path / run/mysql-mmm-agent.pid

Bin_path / usr/libexec/mysql-mmm/

Replication_user replicant

Replication_password abc123

Agent_user mmm_agent

Agent_password agent

Ip 192.168.195.128

Mode master

Peer db2

Ip 192.168.195.137

Mode master

Peer db1

Ip 192.168.195.140

Mode slave

Ip 192.168.195.141

Mode slave

Hosts db1, db2

Ips 192.168.195.188 / / set up virtual IP

Mode exclusive

Hosts db3, db4

Ips 192.168.195.200, 192.168.195.210 / / set virtual IP

Mode balanced

Remote replication override profile:

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

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

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

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

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

(3) configure on the monitor server

Cd / etc/mysql-mmm/ change password

Vim mmm_mon.conf

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

Auto_set_online 10 / / timeout connection time is 10 seconds

Monitor_user mmm_monitor / / user name

Monitor_password 123456 / / password

(4) modify the mmm_agent.conf of all databases

Vim / etc/mysql-mmm/mmm_agent.conf

Modify this db1 / / to db1,db2,db3,db4 respectively

(5) authorize mmm_agent,mmm_moniter on all databases

MariaDB [(none)] > grant replication client on *. * to 'mmm_monitor'@'192.168.195.%' identified by' 123456'

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > grant super, replication client, process on *. * to 'mmm_agent'@'192.168.195.%' identified by' agent'

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)] > flush privileges

Query OK, 0 rows affected (0.01 sec)

(6) start the agent and monitor start mysql-mmm-agentsystemctl start mysql-mmm-agent.service on the database server, start the monitoring service on the monitor server, and test the mysql-mmm-montiorsystemctl start mysql-mmm-monitor.service test cluster on the monitor server.

[root@localhost ~] # mmm_control show

Db1 (192.168.195.128) master/ONLINE. Roles: writer (192.168.195.188)

Db2 (192.168.195.137) master/ONLINE. Roles:

Db3 (192.168.195.140) slave/ONLINE. Roles: reader (192.168.195.210)

Db4 (192.168.195.141) slave/ONLINE. Roles: reader (192.168.195.200)

All OK is required to check the status

[root@localhost ~] # mmm_control checks all

Db4 ping [last change: 2018-09-08 14:53:06] OK

Db4 mysql [last change: 2018-09-08 14:53:06] OK

Db4 rep_threads [last change: 2018-09-08 14:53:06] OK

Db4 rep_backlog [last change: 2018-09-08 14:53:06] OK: Backlog is null

Db2 ping [last change: 2018-09-08 14:53:06] OK

Db2 mysql [last change: 2018-09-08 14:53:06] OK

Db2 rep_threads [last change: 2018-09-08 14:53:06] OK

Db2 rep_backlog [last change: 2018-09-08 14:53:06] OK: Backlog is null

Db3 ping [last change: 2018-09-08 14:53:06] OK

Db3 mysql [last change: 2018-09-08 14:53:06] OK

Db3 rep_threads [last change: 2018-09-08 14:53:06] OK

Db3 rep_backlog [last change: 2018-09-08 14:53:06] OK: Backlog is null

Db1 ping [last change: 2018-09-08 14:53:06] OK

Db1 mysql [last change: 2018-09-08 14:53:06] OK

Db1 rep_threads [last change: 2018-09-08 14:53:06] OK

Db1 rep_backlog [last change: 2018-09-08 14:53:06] OK: Backlog is null

Switch the virtual IP:192.168.195.188 to the db2 server

[root@localhost ~] # mmm_control move_role writer db2

OK: Role 'writer' has been moved from' db1' to 'db2'. Now you can wait some time and check new roles info!

[root@localhost ~] # mmm_control show

Db1 (192.168.195.128) master/ONLINE. Roles:

Db2 (192.168.195.137) master/ONLINE. Roles: writer (192.168.195.188)

Db3 (192.168.195.140) slave/ONLINE. Roles: reader (192.168.195.210)

Db4 (192.168.195.141) slave/ONLINE. Roles: reader (192.168.195.200)

Simulated failover 1) stop the main db1 database, wait a few seconds, you can see that the database db1 is in HARD_OFFLINE (offline state), and the existence of the database cannot be detected.

[root@localhost ~] # mmm_control show

Db1 (192.168.195.128) master/HARD_OFFLINE. Roles:

Db2 (192.168.195.137) master/ONLINE. Roles: writer (192.168.195.188)

Db3 (192.168.195.140) slave/ONLINE. Roles: reader (192.168.195.210)

Db4 (192.168.195.141) slave/ONLINE. Roles: reader (192.168.195.200)

2) stop the slave db3 database, and the slave virtual IP will all be on another normal database

[root@localhost ~] # mmm_control show

Db1 (192.168.195.128) master/HARD_OFFLINE. Roles:

Db2 (192.168.195.137) master/ONLINE. Roles: writer (192.168.195.188)

Db3 (192.168.195.140) slave/HARD_OFFLINE. Roles:

Db4 (192.168.195.141) slave/ONLINE. Roles: reader (192.168.195.200), reader (192.168.195.210)

Reopen the db3 database and return to the original location from the virtual IP

[root@localhost ~] # mmm_control show

Db1 (192.168.195.128) master/ONLINE. Roles:

Db2 (192.168.195.137) master/ONLINE. Roles: writer (192.168.195.188)

Db3 (192.168.195.140) slave/ONLINE. Roles: reader (192.168.195.210)

Db4 (192.168.195.141) slave/ONLINE. Roles: reader (192.168.195.200)

To test the data synchronization status, the monitoring machine monitor acts as the customer to remotely log in to the mysql service for testing. In theory, the monitoring server only plays the role of monitoring alone. Here, it is also used as the client:

Yum install-y mariadb-server mariadb

Systemctl start mariadb.service

Remote login authorization on the database server

MariaDB [(none)] > grant all on *. * to 'test'@'192.168.195.142' identified by' 123123'

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)] > flush privileges

Query OK, 0 rows affected (0.00 sec)

Remotely log in to the mysql service at monitor and create a library shujuku

Mysql-utest-p123123-h 192.168.195.188 / / Virtual IP

MariaDB [school] > create database shujuku

Query OK, 1 row affected (0.01sec)

Log in to the other four nodes and you can see the newly created library shujuku, which proves that the cluster synchronization is successful.

MariaDB [school] > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | school |

| | shujuku |

| | test |

+-+

6 rows in set (0.00 sec)

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.