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

First, the functional features of MMM

MMM, short for (Master-Master replication manager for MySQL), is a set of script programs that support dual-master failover and dual-master daily management, while MMM is developed in Perl language, its main functions are:

First, it is used to monitor and manage the master master replication (Master-Master) of the MySQL database. Although it is the main master structure, because of the characteristics of the database itself, in the business logic, only one master library is allowed to write at the same time, while the other master library acts as a backup. Once the currently used master library fails, it will immediately switch to another master library.

Second, it is used to monitor and manage MySQL database master-slave replication (Master-Slave), and the master-slave replication here, first, the main role of the slave database is to balance the Read load, and secondly, it also acts as a backup.

Advantages of MMM:

1. Open source is free, and it has been a long time since the product was launched to take shape, with mature technical support from its predecessors.

2. It comes from within MySQL and belongs to MySQL's own technology.

3. Use script to control and manage, which is simple and easy to understand.

4. The function is relatively perfect, with all kinds of Niu X tools and kits.

Disadvantages of MMM:

1. Due to the limitation of self-construction of MMM, there can only be one write point, and its expansibility will be a bottleneck in large enterprises.

2. For read-write separation and Read load balancing, it cannot be done by itself, but needs to be handled manually by the enterprise, or with the help of third-party software.

II. Analysis of MMM architecture

The topology shown in the figure above

1. The direction of the arrow represents the flow of data.

2. For the master structure, we need to configure each other as master and slave in two master databases (master An and master B). When either master database (such as master A) is writable, the other master library (such as master B) will synchronize to the data it (master A) has just written, and vice versa.

3. For the master-slave structure, currently, master B belongs to the master library, so you need to configure slave An and slave B as the slave database. No matter it is master An or master B, when data is written, it will be synchronized to slave An and slave B. If, currently, master An is writable, it will first synchronize data to master B, and then from master B to slave An and slave B. When this structure is used for data synchronization, if the amount of data is large (large enterprises), there may be a certain delay.

4. The Monitor monitoring server monitors and manages the four databases of Master A, Master B, Slave An and Slave B at the same time, which refers to monitoring the status of four databases (service accessible and service unreachable). In the production environment, once the database fails, it will be catastrophic and affect the fate of the enterprise. Therefore, in the production environment, if there are four database servers: double master and double slave, four database servers cannot fail at the same time, or two master databases cannot fail at the same time. If the currently written master database (master A) fails due to hardware failure or service collapse, the Monitor server will immediately switch the virtual IP 192.168.20.254 to another master database (master B). The period of time is so short that customers will hardly notice it, and so will the slave database.

5. For read-write separation, we will manually configure (or implement with third-party software) during the deployment of application services. Virtual write IP: 192.168.20.254. Separate configuration of virtual read IP:192.168.20.252 and 192.168.20.253 to do read-write separation. The main purpose is to load balance and reduce the pressure on the server.

Third, the detailed explanation of master-slave synchronous configuration

1. Environmental installation

A. Database planning. Here we install 4 MySQL databases and 1 Monitor server. The configuration is as follows:

B. Virtual IP planning:

C. Modify the server hosts file and paste the following (note that each server needs to be added):

192.168.20.134 masterA

192.168.20.128 masterB

192.168.20.129 slaveA

192.168.20.130 slaveB

192.168.20.131 cluster-proxy

[root@cluster-mysqla ~] # vim / etc/hosts

D. Close firewalld:

[root@cluster-mysqla ~] # systemctl stop firewalld

[root@cluster-mysqla ~] # systemctl disable firewalld

E. Close selinux

[root@cluster-mysqla ~] # setenforce 0

F, configure Yum source and epel source

Configure it according to your own needs, and I won't repeat it here.

G, the server does time synchronization

Synchronize network time or your own time server. I won't repeat it here.

2. Install MySQL

About the installation of MySQL, please refer to my other article: https://blog.51cto.com/4746316/2313577

3. Configure MySQL

[root@cluster-mysqla ~] # vim / etc/my.cnf

[mysqld]

Basedir = / usr/local/mysql

Datadir = / wdata/mysql/data

Tmpdir = / wdata/mysql/temp

Plugin-dir = / usr/local/mysql/plugin

Server_id = 1

Socket = / wdata/mysql/data/mysql.sock

Pid-file = / wdata/mysql/data/mysql.pid

Explicit_defaults_for_timestamp = true

Lower_case_table_names = 1

Open_files_limit = 65535

Event_scheduler = 1

Log_slave_updates = 1

[root@cluster-mysqlb ~] # vim / etc/my.cnf

[mysqld]

Basedir = / usr/local/mysql

Datadir = / wdata/mysql/data

Tmpdir = / wdata/mysql/temp

Plugin-dir = / usr/local/mysql/plugin

Server_id = 2

Socket = / wdata/mysql/data/mysql.sock

Pid-file = / wdata/mysql/data/mysql.pid

Explicit_defaults_for_timestamp = true

Lower_case_table_names = 1

Open_files_limit = 65535

Event_scheduler = 1

Log_slave_updates = 1

[root@cluster-mysqlc ~] # vim / etc/my.cnf

[mysqld]

Basedir = / usr/local/mysql

Datadir = / wdata/mysql/data

Tmpdir = / wdata/mysql/temp

Plugin-dir = / usr/local/mysql/plugin

Server_id = 3

Socket = / wdata/mysql/data/mysql.sock

Pid-file = / wdata/mysql/data/mysql.pid

Explicit_defaults_for_timestamp = true

Lower_case_table_names = 1

Open_files_limit = 65535

Event_scheduler = 1

Log_slave_updates = 1

[root@cluster-mysqld ~] # vim / etc/my.cnf

[mysqld]

Basedir = / usr/local/mysql

Datadir = / wdata/mysql/data

Tmpdir = / wdata/mysql/temp

Plugin-dir = / usr/local/mysql/plugin

Server_id = 4

Socket = / wdata/mysql/data/mysql.sock

Pid-file = / wdata/mysql/data/mysql.pid

Explicit_defaults_for_timestamp = true

Lower_case_table_names = 1

Open_files_limit = 65535

Event_scheduler = 1

Log_slave_updates = 1

Remember that when modifying the configuration file, be sure to include the cascading replication option (that is, log_slave_updates = 1)

4. Restart the MySQL database of 4 servers

[root@cluster-mysqla ~] # service mysqld restart

5. Configure MySQL master-slave synchronization

A, configure the main main structure

Create the users needed for master-slave synchronization in mastera and masterb databases

Mysql > grant replication slave on *. * to slaveuser@ "192.168.20.%" identified by "123456"

Mastera

(1) View master status

Mysql > show master status

(2) set master database information, including master database IP, master database user name, master database password, master database log file, master database log file offset

Mysql > change master to master_host= "192.168.20.128", master_user= "slaveuser", master_password= "123456", master_log_file= "mysql-bin.000007", master_log_pos=3766631

(3) start the slave library

Mysql > start slave

(4) View the status of slave library

Mysql > show slave status\ G

Description:

1. Master_host: the IP address of the main database. Here, set the IP address of ask masterb.

2. Master_log_file: the log file name of the master database, obtained by using the show master status; query in masterb.

3. Master_log_file: the log file offset of the database is also obtained by using show master status; query in masterb.

Masterb

(1) View master status

Mysql > show master status

(2) set master database information, including master database IP, master database user name, master database password, master database log file, master database log file offset

Mysql > change master to master_host= "192.168.20.134", master_user= "slaveuser", master_password= "123456", master_log_file= "mysql-bin.000005", master_log_pos=3766317

(3) start the slave library

Mysql > start slave

(4) View the status of slave library

Mysql > show slave status\ G

Description:

After executing the show slave status\ G; command, we will see the current details of the slave library, but there are three areas that require special attention, as shown in the following figure:

1. The values of Slave_IO_Running and Slave_SQL_Running must be Yes, otherwise master-slave synchronization cannot be performed.

Slave_IO_Running:I/O thread monitoring

Slave_SQL_Running:SQL thread monitoring

2. Master_UUID: this value must be unique. The UUID of master database and slave database cannot be the same. If it is a cloned server, the value of this UUID should be the same. In this case, you need to modify the contents of auto.cnf under the MySQL data directory (if you don't know how to change it, you can change one bit at will), as shown in the figure:

[root@cluster-mysqlc data] # vim auto.cnf

3. Slave_SQL_Running_State records the status of the SQL thread. Generally, when the SQL thread is NO or Connecting, the detailed cause of the error will be indicated here. You can troubleshoot the error according to the prompt here.

4. When the Slave_IO_Running value is NO, the user name and password of the IP address of the main database are set incorrectly. Reset and restart slave can return to normal. When the Slave_SQL_Running value is NO or Connecting, the reasons for the errors are rich, and you need to troubleshoot according to the prompts.

B. Configure the master-slave structure

Following the configuration mode of the previous main main structure, execute the following statement on the MySQL command line in slavea and slaveb, respectively:

Mysql > change master to master_host= "192.168.20.128", master_user= "slaveuser", master_password= "123456", master_log_file= "mysql-bin.000007", master_log_pos=3766631

Mysql > start slave

Mysql > show slave status\ G

C. Verify master-slave synchronization

1. Create a games database in mastera and check whether it exists in other database servers.

Mysql > create database games

2. Check whether the newly created database exists in the other three servers

Masterb

Slavea

Slaveb

The above three pictures show that it is normal for mastera to synchronize to the other three databases.

3. Create users mmmagent, monitor and discuz in masterb, and check whether they exist in the other three database servers

Mysql > grant all privileges on *. * to mmmagent@ "192.168.20.%" identified by "123456"

Mysql > grant all privileges on *. * to monitor@ "192.168.20.%" identified by "123456"

4. Check whether the newly created account exists in the other three servers

Mastera

Slavea

Slaveb

The above three pictures show that it is normal for masterb to synchronize to the other three databases.

Master-slave synchronization to this, deployment completed, the next issue, we will start the core part of this chapter, MMM configuration.

To be continued. See you in the next issue. Please look forward to it.

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.