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

The following content mainly brings you a detailed explanation of MySQL-MMM high-availability cluster deployment. The knowledge mentioned here is slightly different from books. It is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

Introduction to MMM

MMM (MySQL Primary Primary replication Manager) is a set of scripts 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, only one master is allowed to write to one 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 switchover. It can be said that MMM script program on the one hand realizes the function of failover, on the other hand, its internal additional tool scripts can also achieve read load balancing of multiple Slave.

MMM is a flexible script program based on perl, which is used to monitor and fail over mysql replication, and to manage the configuration of MySQL Master-Master replication, as shown in the figure:

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 that runs on each MySQL CVM, performs monitoring probe work and performs simple remote service settings. This script needs to be 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 virtual IP (VIP), including a writable VIP and multiple readable IP. Through the management of supervision, these IP will be bound to the available MySQL. When one machine goes down, the supervisor will migrate the VIP to other MySQL.

Experimental environment: (mariadb database is a branch of mysql, and their commands and operations are the same)

This experimental environment is simulated by five servers. The experimental environment is shown in the table.

1. Build mariadb multi-master and multi-slave mode

(1) install mariadb

1) all servers are configured with ALI cloud sources, and then epel-release sources are installed.

[root@localhost ~] # wget-O / etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo[root@localhost ~] # yum-y install epel-release [root@localhost ~] # yum clean all & & yum makecache

2) set up a local YUM source.

[root@localhost ~] # yum-y install mariadb-server mariadb [root@localhost ~] # systemctl stop firewalld.service / / turn off the firewall / / [root@localhost ~] # setenforce 0

3) modify the mariadb-m1 main configuration file.

[root@localhost ~] # vim / etc/my.cnf [mysqld] / add / / 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 / / Database name that does not require synchronization / / character_set_server=utf8log_bin=mysql_bin / / enable binlog log for master-slave data replication / / server_id=1 / / the value of each server-id should not be the same / / log_slave_updates=true / / this database is down How much is the standby database takeover / / sync_binlog=1auto_increment_increment=2 / / field incremented at a time / auto_increment_offset=1 / / the starting value of the self-increment field / / [root@localhost ~] # systemctl start mariadb.service / / enable mariadb// [root@localhost ~] # netstat-anpt | grep 3306tcp 00 0.0.0.0 root@localhost 3306 0.0.0.0 systemctl start mariadb.service * LISTEN 3434/mysqld

4) when there is no problem, copy the configuration file to the other 3 database servers.

[root@localhost ~] # scp / etc/my.cnf root@192.168.126.139:/etc/ m2max / [root@localhost ~] # scp / etc/my.cnf root@192.168.126.136:/etc/ m3ram / [root@localhost ~] # scp / etc/my.cnf root@192.168.126.137:/etc/ m4max /

Note: the server-id of each mariadb host cannot be the same, and other profile parameters can be the same.

(2) configure mariadb-m1 and mariadb-m2 main mode

1) check the location of log bin and POS values first.

M1: [root@localhost ~] # mysqlMariaDB [(none)] > show master status +-- + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+ | mysql_bin.000001 | 245 | | mysql Information_schema | +-+ 1 row in set (0.00 sec) m2: [root@localhost ~] # mysqlMariaDB [(none)] > show master status +-- + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+ | mysql_bin.000003 | 245 | | mysql Information_schema | +-- + 1 row in set (0.00 sec)

2) mariadb-m1 and mariadb-m2 enhance each other's access rights.

M1:MariaDB [(none)] > grant replication slave on *. * to 'replication'@'192.168.126.%' identified by' 123456; Query OK, 0 rows affected (0.03 sec) MariaDB [(none)] > change master to master_host='192.168.126.139',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=411 Query OK, 0 rows affected (0.18 sec) m2:MariaDB [(none)] > grant replication slave on *. * to 'replication'@'192.168.126.%' identified by' 123456 Query OK; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)] > change master to master_host='192.168.126.138',master_user='replication',master_password='123456',master_log_file='mysql_bin.000001',master_log_pos=411;Query OK, 0 rows affected (0.03 sec)

3) check the master-slave status of mariadb-m1 and mariadb-m2 servers respectively.

MariaDB [(none)] > start slave;MariaDB [(none)] > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.126.139 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin.000003 Read_Master_Log_Pos: 411 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql_bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes

After the master synchronization is configured, check that the synchronization status Slave_IO and Slave_SQL is YES, indicating that the master synchronization is successful.

4) Test master synchronization and create a new library dba in mariadb-m2.

M2:MariaDB [(none)] > create database dba; / / create database / / Query OK, 1 row affected (0.09 sec) m1:MariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | dba | / / synchronization succeeded / / | mysql | | performance_schema | | test | +-+ 5 rows in set (0.02 sec)

(3) configure mariadb-m3 and mariadb-m4 as slave libraries of mariadb-m1

1) check the status value of mariadb-m1 master first.

MariaDB [(none)] > show master status +-- + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+ | mysql_bin.000001 | 581 | | mysql Information_schema | +-- + 1 row in set (0.00 sec)

2) mariadb-m3 and mariadb-m4 are executed respectively.

MariaDB [(none)] > change master to master_host='192.168.126.138',master_user='replication',master_password='123456',master_log_file='mysql_bin.000001',master_log_pos=581;Query OK, 0 rows affected (0.01 sec)

3) check the master / slave status of mariadb-m3 and mariadb-m4 servers respectively, and the results are as follows:

MariaDB [(none)] > start slave;MariaDB [(none)] > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.126.138 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin.000001 Read_Master_Log_Pos: 581 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql_bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes

4) set up the database in mariadb-m1 and test the master-slave, master-master and synchronization.

MariaDB [(none)] > create database dba01; / / create database / / Query OK in M1, 1 row affected (0.01sec) MariaDB [(none)] > show databases / / mariadb-m3 View result / / +-+ | Database | +-+ | information_schema | | dba01 | | mysql | | performance_schema | | test | +-+ 5 rows in set (0.03 sec) MariaDB [(none)] > show databases / / mariadb-m4 View result / / +-+ | Database | +-+ | information_schema | | dba01 | | mysql | | performance_schema | | test | +-+ 5 rows in set (0.04 sec) 2. Install and configure MySQL-MMM

(1) install MMM on all servers and note that the epel source should be configured.

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

(2) modify the / etc/mysql-mmm/mmm_common.conf configuration file, which is the same for all hosts in the system, including the monitoring host mysql-monitor.

[root@localhost ~] # cd / etc/mysql-mmm/ [root@localhost mysql-mmm] # vim mmm_common.confactive_master_role writer cluster_interface ens33 / / Network Card name / / pid_path / run/mysql-mmm-agent.pid bin_path / usr/libexec/mysql-mmm/ replication_user replication replication_password 123456 / / Log in Record password agent_user mmm_agent agent_password 123456 / / master server m1max / ip 192.168.126.138 mode master peer db2 / / master server m2amp / ip 192.168.126.139 mode master peer db1 / / master server m3max / ip 192.168.126.136 mode Slave / / Master server m4ram / ip 192.168.126.137 mode slave hosts db1 Db2 / / M1 m2 IP VIP// mode exclusive hosts db3 / ips 192.168.126.188 / / Virtual IP VIP// mode exclusive hosts db3, db4 / / m3 m4Accord / ips 192.168.126.190, 192.168.126.199 / / Virtual IP VIP// mode balanced

(3) Edit the / etc/mysql-mmm/mmm_mon.conf file on the monitoring host.

[root@localhost ~] # vim / etc/mysql-mmm/mmm_mon.conf include mmm_common.conf ip 127.0.0.1 pid_path / run/mysql-mmm-monitor.pidinclude mmm_common.conf ip 127.0.0.1 pid_path / run/mysql-mmm-monitor.pid bin_path / usr/libexec/ Mysql-mmm status_path / var/lib/mysql-mmm/mmm_mond.status ping_ips 192.168.126.138192.168.126.139192.168.126.136192.168.126.136.137 / IP/ auto_set_online 10 / / automatic online time of the monitoring server is 10 seconds / / # 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_monitor / / user name / / monitor_password 123456 / / password / /

(4) authorize mmm_agent and mmm_moniter on all databases.

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

(5) modify the mmm_agent.conf of all databases.

[root@localhost mysql-mmm] # vim / etc/mysql-mmm/mmm_agent.conf include mmm_common.conf# The 'this' variable refers to this server. Proper operation requires# that 'this' server (db1 by default), as well as all other servers, have the# proper IP addresses set in mmm_common.conf.this db1 / / adjust one by one according to the plan / /

(6) start mysql-mmm-agent on all database servers.

[root@localhost mysql-mmm] # systemctl start mysql-mmm-agent.service [root@localhost mysql-mmm] # systemctl enable mysql-mmm-agent.service / / Boot / /

(7) start the mysql-mmm-monitor monitoring host.

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

(8) check the situation of each node on the monitoring server.

[root@localhost] # mmm_control show db1 (192.168.126.138) master/ONLINE. Roles: writer (192.168.126.188) / / Virtual IP// db2 (192.168.126.139) master/ONLINE. Roles: db3 (192.168.126.136) slave/ONLINE. Roles: reader (192.168.126.190) db4 (192.168.126.137) slave/ONLINE. Roles: reader (192.168.126.199)

(9) failover

1) stop M1 to confirm whether the virtual address 188 is moved to m2. Note: the Lord will not preempt

[root@localhost mysql-mmm] # systemctl stop mariadb.service / / stop M1 master server / / [root@localhost ~] # mmm_control show db1 (192.168.126.138) master/HARD_OFFLINE. Roles: / / offline status / / db2 (192.168.126.139) master/ONLINE. Roles: writer (192.168.126.188) db3 (192.168.126.136) slave/ONLINE. Roles: reader (192.168.126.190) db4 (192.168.126.137) slave/ONLINE. Roles: reader (192.168.126.199)

[root@localhost mysql-mmm] # systemctl stop mariadb.service / / stop m3 slave server / /

[root@localhost] # mmm_control show db1 (192.168.126.138) master/HARD_OFFLINE. Roles: db2 (192.168.126.139) master/ONLINE. Roles: writer (192.168.126.188) db3 (192.168.126.136) slave/HARD_OFFLINE. Roles: db4 (192.168.126.137) slave/ONLINE. Roles: reader (192.168.126.190), reader (192.168.126.199)

3) authorize login for the monitor address on the M1 server.

MariaDB [(none)] > grant all on *. * to 'testdba'@'192.168.126.140' identified by' 123456 MariaDB [(none)] > flush privileges; / / Refresh / /

4) Log in on the monitoring server.

[root@localhost ~] # yum install mariadb-server mariadb- y [root@localhost ~] # mysql-utestdba-p-h 192.168.126.188 / / Virtual IP//Enter password: / / password 123456Unip. MariaDB / omit / / MariaDB [(none)] >

5) create data on the monitoring server and test the synchronization.

MariaDB [(none)] > create database abc01;Query OK, 1 row affected (0.01sec) MariaDB [(none)] > show databases / / Master server M1andandrowr + | Database | +-+ | information_schema | | abc01 | | dba01 | | mysql | | performance_schema | | test | +- -- + 6 rows in set (0.03 sec) MariaDB [(none)] > show databases / / Slave server M3According to information_schema + | Database | +-+ | information_schema | | abc01 | | dba01 | | mysql | | performance_schema | | test | +-+

6 rows in set (0.05 sec)

For the above detailed explanation of MySQL-MMM high availability cluster deployment, if you have more information, you can continue to follow our industry promotion. If you need professional answers, you can contact the pre-sales and after-sales ones on the official website. I hope this article can bring you some knowledge updates.

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.