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

Introduction to MMM

MMM (Master-Master relication manager fro MySQL, MySQK Master replication Manager) is a set of procedures that support dual-master failover and dual-master day-to-day management. It is mainly used to monitor and manage MySQL Master-Master (dual master) replication. Although it is called dual master replication, one master is allowed to write to one master at the same time in the business, and the other option master provides partial read service to speed up the warm-up of the backup master during the master-master switch. On the one hand, it can realize the function of failover, and it can also realize the read load balance of multiple Slave.

The description of the MMM highly available architecture is as follows:

Mmm_mon: monitor the process, load all monitoring work, absolutely and handle all node role activities. This script needs to be run on the supervisor.

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 the supervised machine.

Mmm_control: 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. These IP will be bound to the available MySQL. When a MySQL goes down, the supervisor will migrate the VIP to other MySQL.

Case environment

The environment of this case is simulated with five servers:

Host operating system IP address main software

Mysql-master1 centos7x86_64 192.168.213.174 mysql-mmm*

Mysql-master2 centos7x86_64 192.168.213.177 mysql-mmm*

Mysql-slave 1 centos7x86_64 192.168.213.179 mysql-mmm*

Mysql-slave 2 centos7x86_64 192.168.213.173 mysql-mmm*

Mysql-monitor centos7x86_64 192.168.213.178 mysql-mmm*

one。 Build MySQL multi-master and multi-slave mode

(1) download and install the epel source online, and install all five servers.

[root@master1 ~] # wget-O / etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

# download the yum source of aliyun to the local network yum source

[root@master1 ~] # yum-y install epel-release # download epel feed

[root@master1 ~] # yum clean all & & yum makecache # clear everything and reset the original data cache

(2) download mariadb (a branch of mysql) and turn off the firewall function (mariadb is only installed on 4 master and slave servers)

[root@master1 ~] # yum-y install mariadb-server mariadb

[root@master1 ~] # systemctl stop firewalld.service # turn off the firewall

[root@master1 ~] # setenforce 0

(3) modify the configuration file of mariadb

[root@master1 ~] # vim / etc/my.cnf

[mysqld]

Log_error=/var/lib/mysql/mysql.err # error log path

Log=/var/lib/mysql/mysql_log.log # General query Log

Log_slow_queries=/var/lib/mysql_slow_queris.log # slow query log path

Binlog-ignore-db=mysql,information_schema # Database name that does not require synchronization

The default character set of character_set_server=utf8 # is utf8

Log_bin=mysql_bin # enable binary logging

Server_id=1 # server id, note: the id of each server is different to ensure uniqueness

Log_slave_updates=ture # allows log updates from the server

Sync_binlog=1 # allows synchronization of binary logs from the server

How much is the auto_increment_increment=2 # field incremented at a time

Auto_increment_offset=1 # starting value of the increment segment

(4) start the mysql service and check whether the default port is enabled.

[root@master1 ~] # systemctl restart mariadb

[root@master1 ~] # netstat-ntap | grep 3306

Tcp 0 0 0.0.0.0 3306 0.0.0.015 * LISTEN 41241/mysqld

(5) set the login password for database root users

[root@master1 ~] # mysqladmin-u root password '123'

[root@master1] # mysql-uroot-p

Enter password:

Welcome to the MariaDB monitor. Commands end with; or\ g.

The other three database servers have the same installation and configuration as master1, and all other parameters of the configuration file are the same, except for server-id. Therefore, you can upload the configuration file on the master 1 server and modify the server-id.

[root@master01] # scp-r / etc/my.cnf root@192.168.213.177:/etc/my.cnf

[root@master01] # scp-r / etc/my.cnf root@192.168.213.179:/etc/my.cnf

[root@master01] # scp-r / etc/my.cnf root@192.168.213.173:/etc/my.cnf

two。 Configure master 1 he master 2 main mode

(1) check the log bin log and pos value first

Master 1

MariaDB [(none)] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql_bin.000003 | 554 | | mysql,information_schema | |

+-+

1 row in set (0.00 sec)

Master 2

MariaDB [(none)] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql_bin.000003 | 554 | | mysql,information_schema | |

+-+

1 row in set (0.00 sec)

(2) master 1 and master 2 enhance access to each other

Grant subordinate permissions to m2 on M1

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

Query OK, 0 rows affected (0.35 sec)

MariaDB [(none)] > change master to master_host='192.168.213.177',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos= 554; # master 2 log bin logs and poss values

Query OK, 0 rows affected (0.02 sec)

The permission from M1 should also be granted on m2.

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

Query OK, 0 rows affected (0.35 sec)

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

Query OK, 0 rows affected (0.02 sec)

(3) enable the synchronization of two master servers and query the master-slave status.

Tart slave; / / enable synchronization function

Show slave status\ G

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

Slave_SQL_Running: Yes

Master synchronization configuration is complete. Check that the status Slave_IO and Slave_SQI are yes, indicating that the master synchronization is successful.

three。 Configure slave servers Slave1 and Slave2 as slave libraries for Master 1

View the status value of master1

MariaDB [(none)] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql_bin.000003 | 554 | | mysql,information_schema | |

+-+

1 row in set (0.00 sec)

Do it in slave1 and slave2 respectively

MariaDB [(none)] > change master to master_host='192.168.213.174',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos= 554; # ip of master1

Query OK, 0 rows affected (0.02 sec)

Turn on synchronization and check the synchronization status

Start slave; / / enable synchronization function

Show slave status\ G

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

Slave_SQL_Running: Yes

Four. Install and configure MySQL-MMM

(1) install MMM. Centos does not have mysql-mmm software package by default. Using epel source, epel and MMM are installed on all five servers.

[root@master1 ~] # yum install mysql-mmm*-y

(2) modify the configuration file of MMM. The content of the configuration file is the same for all hosts in the system, including monitoring host mysql-monitor.

[root@master1 ~] # cd / etc/mysql-mmm/ # configuration file path

[root@master1 mysql-mmm] # vim mmm_common.conf

Cluster_interface ens33 # network card changed to ens33

Pid_path / run/mysql-mmm-agent.pid

Bin_path / usr/libexec/mysql-mmm/

Replication_user replicant # users with permissions granted by Mariadb

Replication_password 123456 # user password that gives permission

Agent_user mmm_agent # agent client proxy user

Agent_password 123456 # proxy user password

# Master server master1

Ip 192.168.213.174

Mode master # status is: master

Peer db2 # and master server master 2 switch to each other

# Master server master2

Ip 192.168.213.177

Mode master # status is: master

Peer db1

# slave1 from the server

Ip 192.168.213.179

Mode slave # status is: slave

# slave2 from the server

Ip 192.168.213.173

Mode slave # status is: slave

# write operation

Hosts db1, db2 # main servers master1 and master2 have write operations

Ips 192.168.213.100 # specify virtual IP

Mode exclusive # specifies the mode. Only one IP address can exist for db1 and db2, and only one host can write.

# read operation

Hosts db3, db4

Ips 192.168.213.110, 192.168.213.120 # Virtual IP

Mode balanced # load balancer. Read operations are performed from db3 and db4 hosts.

(3) remote replication to transfer the mmm_common.conf configuration file to the other 4 hosts

[root@master1 mysql-mmm] # scp mmm_common.conf root@192.168.213.177:/etc/mysql-mmm/

The authenticity of host '192.168.213.177 (192.168.213.177)' can't be established.

ECDSA key fingerprint is SHA256:KM7QwLupjrfzZ2YQdMOoGKJtIUgtz2agvwTzZOPHu2k.

ECDSA key fingerprint is MD5:f1:32:f7:7f:b7:eb:4e:9e:2e:fa:7e:8a:56:88:fe:c1.

Are you sure you want to continue connecting (yes/no)? Yes

Warning: Permanently added '192.168.213.177' (ECDSA) to the list of known hosts.

Root@192.168.213.177's password: # the login password of the other root user

Mmm_common.conf 100% 842 329.6KB/s 00:00

[root@master1 mysql-mmm] # scp mmm_common.conf root@192.168.213.179:/etc/mysql-mmm/

The authenticity of host '192.168.213.179 (192.168.213.179)' can't be established.

ECDSA key fingerprint is SHA256:HtLFtvYxQF5ER0eA1uKE8VgRx038LWpDYBbp1S1CrJ8.

ECDSA key fingerprint is MD5:23:41:18:56:8e:ed:f3:65:b1:5f:96:11:e9:11:cb:29.

Are you sure you want to continue connecting (yes/no)? Yes

Warning: Permanently added '192.168.213.179' (ECDSA) to the list of known hosts.

Root@192.168.213.179's password:

Mmm_common.conf 100% 842 376.0KB/s 00:00

[root@master1 mysql-mmm] # scp mmm_common.conf root@192.168.213.173:/etc/mysql-mmm/

The authenticity of host '192.168.213.173 (192.168.213.173)' can't be established.

ECDSA key fingerprint is SHA256:w910JWPfehgM09d+OlOiC6q61NjELLHDh6LWojkuYL0.

ECDSA key fingerprint is MD5:94:a5:a1:e0:4d:14:cc:3c:ff:8c:24:e5:3e:e1:2b:cd.

Are you sure you want to continue connecting (yes/no)? Yes

Warning: Permanently added '192.168.213.173' (ECDSA) to the list of known hosts.

Root@192.168.213.173's password:

Mmm_common.conf 100% 842 565.0KB/s 00:00

[root@master1 mysql-mmm] # scp mmm_common.conf root@192.168.213.178:/etc/mysql-mmm/

The authenticity of host '192.168.213.178 (192.168.213.178)' can't be established.

ECDSA key fingerprint is SHA256:ABSTPGOHvqKvUsfwD/uf5ESPpdT1RjvucRpzMqcUuzI.

ECDSA key fingerprint is MD5:f5:3a:8c:8b:1e:d5:a3:33:24:32:03:2d:4d:3e:e8:68.

Are you sure you want to continue connecting (yes/no)? Yes

Warning: Permanently added '192.168.213.178' (ECDSA) to the list of known hosts.

Root@192.168.213.178's password:

Mmm_common.conf 100% 842 277.8KB/s 00:00

(4) authorize access for monitoring monitor on 4 databases

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

Query OK, 0 rows affected (0.00 sec)

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

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)] > flush privileges; # Refresh permissions

Query OK, 0 rows affected (0.00 sec)

(5) you need to edit the mmm_agent.conf configuration file on the database host and modify it to different values according to different hosts.

[root@master1 mysql-mmm] # vim mmm_agent.conf

This db1 # is modified to db1, db2, db3, db4, respectively

Five. Monitor server monitor configuration

[root@localhost ~] # cd / etc/mysql-mmm/

[root@localhost mysql-mmm] # vim mmm_mon.conf

Ping_ips 192.168.213.174192.168.213.177192.168.213.179192.168.213.173 # monitors the IP address of all databases

Auto_set_online 10 # set 10 seconds to go live

Monitor_user mmm_monitor # user name

Monitor_password 123456 # password

Six. Start monitoring and agent

(1) start mysql-mmm-agent on all database servers

[root@master1 mysql-mmm] # systemctl start mysql-mmm-agent.service

[root@master1 mysql-mmm] # systemctl enable mysql-mmm-agent.service

(2) start the monitoring service mysql-mmm-monitor

[root@localhost mysql-mmm] # systemctl start mysql-mmm-monitor.service

(3) Test cluster

[root@localhost mysql-mmm] # mmm_control show

Db1 (192.168.213.174) master/ONLINE. Roles: writer (192.168.213.100) # Virtual IP

Db2 (192.168.213.177) master/ONLINE. Roles:

Db3 (192.168.213.179) slave/ONLINE. Roles: reader (192.168.213.110)

Db4 (192.168.213.173) slave/ONLINE. Roles: reader (192.168.213.120)

# the corresponding real IP

All OK is required to check the status.

[root@localhost mysql-mmm] # mmm_control checks all

Db4 ping [last change: 2018-09-10 10:50:52] OK

Db4 mysql [last change: 2018-09-10 10:50:52] OK

Db4 rep_threads [last change: 2018-09-10 10:50:52] OK

Db4 rep_backlog [last change: 2018-09-10 10:50:52] OK: Backlog is null

Db2 ping [last change: 2018-09-10 10:50:52] OK

Db2 mysql [last change: 2018-09-10 10:50:52] OK

Db2 rep_threads [last change: 2018-09-10 10:50:52] OK

Db2 rep_backlog [last change: 2018-09-10 10:50:52] OK: Backlog is null

Db3 ping [last change: 2018-09-10 10:50:52] OK

Db3 mysql [last change: 2018-09-10 10:50:52] OK

Db3 rep_threads [last change: 2018-09-10 10:50:52] OK

Db3 rep_backlog [last change: 2018-09-10 10:50:52] OK: Backlog is null

Db1 ping [last change: 2018-09-10 10:50:52] OK

Db1 mysql [last change: 2018-09-10 10:50:52] OK

Db1 rep_threads [last change: 2018-09-10 10:50:52] OK

Db1 rep_backlog [last change: 2018-09-10 10:50:52] OK: Backlog is null

Seven. Fault testing

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

[root@master1 mysql-mmm] # systemctl stop mariadb.service

[root@localhost mysql-mmm] # mmm_control show

Db1 (192.168.213.174) master/HARD_OFFLINE. Roles:

Db2 (192.168.213.177) master/ONLINE. Roles: writer (192.168.213.100)

Db3 (192.168.213.179) slave/ONLINE. Roles: reader (192.168.213.110)

Db4 (192.168.213.173) slave/ONLINE. Roles: reader (192.168.213.120)

If the MySQL database is down, the virtual IP will all be on another normal database.

Restart the database of the primary db1

[root@master1 mysql-mmm] # systemctl start mariadb.service # start mysql database

[root@localhost mysql-mmm] # mmm_control show

Db1 (192.168.213.174) master/AWAITING_RECOVERY. Roles: # waiting for reply

Db2 (192.168.213.177) master/ONLINE. Roles: writer (192.168.213.100)

Db3 (192.168.213.179) slave/ONLINE. Roles: reader (192.168.213.110)

Db4 (192.168.213.173) slave/ONLINE. Roles:reader (192.168.213.120)

[root@localhost mysql-mmm] # mmm_control show

Db1 (192.168.213.174) master/ONLINE. Roles: # normal status (online)

Db2 (192.168.213.177) master/ONLINE. Roles: writer (192.168.213.100)

Db3 (192.168.213.179) slave/ONLINE. Roles: reader (192.168.213.110)

Db4 (192.168.213.173) slave/ONLINE. Roles: reader (192.168.213.120)

Although the primary server db1 comes online again, the virtual IP will not be reset

(3) stop the database db3 from the server

[root@localhost mysql-mmm] # systemctl stop mariadb.service

[root@localhost mysql-mmm] # mmm_control show

# Warning: agent on host db3 is not reachable

Db1 (192.168.213.174) master/ONLINE. Roles:

Db2 (192.168.213.177) master/ONLINE. Roles: writer (192.168.213.100)

Db3 (192.168.213.179) slave/HARD_OFFLINE. Roles: # when the db3 is turned off from the server, the virtual IP will all drift to the spirit bit and a slave server db4

Db4 (192.168.213.173) slave/ONLINE. Roles: reader (192.168.213.110), reader (192.168.213.120)

Start the database from db3

[root@localhost mysql-mmm] # systemctl start mariadb.service

[root@localhost mysql-mmm] # mmm_control show

# Warning: agent on host db3 is not reachable

Db1 (192.168.213.174) master/ONLINE. Roles:

Db2 (192.168.213.177) master/ONLINE. Roles: writer (192.168.213.100)

Db3 (192.168.213.179) slave/AWAITING_RECOVERY. Roles:

Db4 (192.168.213.173) slave/ONLINE. Roles: reader (192.168.213.110), reader (192.168.213.120)

[root@localhost mysql-mmm] # mmm_control show

Db1 (192.168.213.174) master/ONLINE. Roles:

Db2 (192.168.213.177) master/ONLINE. Roles: writer (192.168.213.100)

Db3 (192.168.213.179) slave/ONLINE. Roles: reader (192.168.213.120) # after the server was launched, the virtual IP was restored again

Db4 (192.168.213.173) slave/ONLINE. Roles: reader (192.168.213.110)

Eight. Test data synchronization status

Test the remote login mysql service with the monitoring server as the client, and install the mariadb database on the monitoring server.

[root@localhost mysql-mmm] # yum install mariadb-server mariadb- y

Log in to the MariaDB database for the monitoring server on the main database db1

MariaDB [(none)] > grant all on *. * to 'testdba'@'192.168.213.178' identified by' 123456'

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)] > flush privileges

Query OK, 0 rows affected (0.00 sec)

Log in to the MariaDB database using virtual IP on the monitoring server, insert the data, and create a database

[root@localhost mysql-mmm] # mysql- utestdba-p-h 192.168.213.100

Enter password:

Welcome to the MariaDB monitor. Commands end with; or\ g.

MariaDB [school] > create database mogo

Query OK, 1 row affected (0.01sec)

Log in to the other four databases and view the newly created database mogo, which proves that the cluster synchronization is successful.

MariaDB [(none)] > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mogo |

| | mysql |

| | performance_schema |

| | test |

+-+

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.