[Mysql] high-availability cluster MMM, mysql cluster mmm

[Mysql] high-availability cluster MMM, mysql cluster mmm
I,Common topology of Replication

The replication architecture has the following basic principles:

(1) Each slave can have only one master;

(2) Each slave can have only one unique server ID;

(3) Each master can have many slave instances;

(4) If you set log_slave_updates, slave can be the master of other slave, thus spreading master updates.

MySQL does not support multi-master Replication-that is, one slave can have multiple masters. However, through some simple combinations, we can build a flexible and powerful replication architecture.

1.1 single master and multi-slave

In practical application scenarios, MySQL replication over 90% is an architecture mode where one Master node is replicated to one or more Slave instances. It is mainly used for database Expansion Solutions for applications with high read pressure. As long as the pressure on the Master and Slave is not too high (especially on the Slave end), the latency of asynchronous replication is usually very small. Especially since the replication method on the Slave end is changed to two threads for processing, the delay Problem on the Slave end is reduced.

When slave increases to a certain number, slave's load on the master and network bandwidth will become a serious problem.

This structure is simple, but flexible enough to meet the needs of most applications. Some suggestions:

(1) Different slave plays different roles (for example, using different indexes or different storage engines );

(2) Use a Server Load balancer instance as the slave master and only copy the slave database;

(3) Use a remote slave for disaster recovery;

1.2 Master-Master in Active Mode

After the replication environment is set up, won't circular replication between two MySQL instances? In fact, MySQL has long thought of this, so the current MySQL server-id is recorded in MySQL BinaryLog, and this parameter must be explicitly specified when we set up MySQLReplication, in addition, the server-id parameter values of Master and Slave must be different in order to successfully build MySQL replication. Once the value of server-id is available, MySQL can easily determine which MySQLServer was originally generated for a change, so it is easy to avoid loop replication.

Active Master-Master replication has some special functions. For example, both geographically distributed parts require their own writable data copies. The biggest problem with this structure is the update conflict. Assume that a table has only one row (one column) and its value is 1. If the two servers execute the following statement simultaneously:

Run the following command on the first Server:

Mysql> UPDATE tbl SET col = col + 1;

Run the following command on the second server:

Mysql> UPDATE tbl SET col = col * 2;

So what is the result? One server is 4 and the other server is 3, but this does not produce errors.

In fact, MySQL does not support multi-master Replication supported by other DBMS ), this is a huge limitation of the MySQL replication function (the difficulty of Multi-master servers lies in resolving update conflicts). However, if you have such requirements, you can use MySQL Cluster, by combining Cluster and Replication, you can build a powerful and high-performance database platform. However, you can simulate the replication of multiple master servers in other ways.

1.3 active-passive mode Master-Master this is the master-master structure changes, it avoids the disadvantages of the M-M, in fact, this is a fault tolerance and high availability system. The difference is that one service can only perform read-only operations. 1.4 cascade replication architecture Master-Slaves

In some application scenarios, the read/write pressure may be significantly different, And the read pressure may be extremely high. A Master may need 10 or more server load balancers to support the read injection pressure. At this time, the Master will be more difficult, because there are more SlaveIO threads connected only, so when the write pressure is a little higher, the Master node consumes a lot of resources because of replication, which can easily lead to replication latency.

How can this problem be solved? At this time, we can use MySQL to record the changed BinaryLog information generated by replication on the Slave end, that is, enable the-log-slave-update option. Second-level (or more) replication reduces the pressure on the Master node due to replication. That is to say, we first use a few MySQL instances to replicate from the Master. We call these machines the first-level Slave cluster, then, other Slave instances are copied from the first-level Slave cluster. The server Load balancer instance that replicates data from the first-level Server Load balancer instance, which is called the second-level Server Load balancer cluster. If necessary, we can continue to add more levels of replication. In this way, we can easily control the number of Slave attached to each MySQL. This architecture is called the Master-Slaves architecture.

This multi-layer cascade replication architecture easily solves the risk that the Master node becomes a bottleneck because of too many Slave instances. Of course, if conditions permit, I am more inclined to suggest you solve this problem by splitting it into multiple Replication clusters.

The above bottlenecks. After all, Slave does not reduce the write volume, and all Slave actually applies all data change operations without any write IO reduction. On the contrary, the more Slave, the more I/O writes to the entire cluster. We do not have a very obvious feeling. It is just because it is scattered across multiple machines, so it is not very easy to express.

In addition, the cascade level of replication is increased, and the same change is required to pass to the bottom-layer Slave, which may lead to a longer delay.

If we solve this problem by splitting the cluster, it may be much better. Splitting the Cluster also requires more complex technologies and more complex application system architecture.

1.5 The Master-Master structure (Master-Master with Slaves) with slave servers has the advantage of providing redundancy. Geographically distributed replication structure, which does not have a single node failure problem, and can also put read-intensive requests on slave.

To a certain extent, cascade replication solves the bottleneck caused by the excessive number of Slave instances attached to the Master, however, it cannot solve the problem of manual maintenance and abnormal switchover. This naturally extends the Replication architecture that combines DualMaster with cascade Replication. I call it the Master-Slaves architecture.

Compared with the Master-Slaves architecture, the difference is only that the first-level Slave cluster is replaced with a separate Master, as a backup Master, then copy the Slave to a Slave cluster.

This architecture combines DualMaster with cascade Replication,The biggest benefit is that the write operation of the Master cannot be affected by the replication of the Slave cluster, at the same time, when the Master needs to be switched, Replication will basically not be redeployed. However, this architecture also has a drawback, that is, the Standby Master may become a bottleneck, because if the subsequent Slave cluster is large, the standby Master may become a bottleneck due to excessive SlaveIO thread requests.Of course, when the standby Master does not provide any read services, the bottleneck may not be very high. If a bottleneck occurs, you can perform cascade replication again after the standby Master, build a multi-layer Slave cluster. Of course, the more levels of cascade replication, the more obvious the data delay that may occur in the Slave cluster. Therefore, before using cascade replication, you also need to evaluate the impact of data delay on the application system.

Ii. mysql master-slave Replication

Principle of master-slave Replication

It can be divided into synchronous replication and asynchronous replication. In the actual replication architecture, most of them are asynchronous replication. The basic process of replication is as follows:

1). the IO process on the Slave connects to the Master and requests the log content after the specified location of the specified log file (or from the initial log;

2 ). after the Master receives a request from the Slave IO process, it reads the log information after the specified location of the log based on the request information by the IO process responsible for replication and returns it to the Slave IO process. Besides the information contained in the log, the returned information also includes the name of the bin-log file on the Master end and the location of the bin-log;

3 ). after the Slave IO process receives the information, it adds the received log content to the end of the relay-log file at the Slave end in sequence, and record the file name and location of the bin-log on the Master end to the master-info file, in this way, you can clearly tell the Master "from where a bin-log is to be read, send it to me ";

4 ). after the Slave SQL process detects the newly added content in relay-log, it will immediately parse the relay-log content into the executable content during actual execution on the Master side, and then play back the content.

Install configurations

Master server: 192.168.1.30

Slave server slave: 192.168.1.25

Master Configuration

My. cnf

#bin_logbinlog_format=mixedmax_binlog_size=200Mlog_bin=/data/logs/mysql/binarylog/mysql_binexpire_logs_days=7binlog-ignore-db = mysql,information_schema,performance_schemaserver_id=1binlog-do-db=abc

Authorization

GRANT REPLICATION SLAVE ON abc.* to 'sync'@'192.168.1.30' identified by '123456'

Slave Configuration

Server-id = 131 # slave server id, do not use the same master-host = 192.168.1.30 # specify the master server IP address master-user = sync # specify the username master-password = 123456 # password master- port = 3306 # port used for synchronization master-connect-retry = 60 # resumable copy-ignore-db = information_schema, mysql, performance_schema # Shield synchronized replicate-do-db = abc # Name of the synchronized database replicate_do_table = tt # Name of the synchronized data table

Authorization

change master to master_host='192.168.1.30',master_port=3306,master_user='sync',master_password='123456',master_log_file='mysql_bin.000041',master_log_pos=538;

Start slave

mysql> start slave;mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.1.30                  Master_User: sync                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql_bin.000042          Read_Master_Log_Pos: 120               Relay_Log_File: mysql-relay-bin.000004                Relay_Log_Pos: 283        Relay_Master_Log_File: mysql_bin.000042             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 120              Relay_Log_Space: 710              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 1                  Master_UUID: 7789d104-20d3-11e5-a394-0050563accdf             Master_Info_File: /data/mysql/master.info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it           Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 01 row in set (0.00 sec)

Notes for mysql replication single-table or multi-table replication

1. The name of the master database and slave database must be the same;

2. The replication between the master database and slave database can be accurate to the table, but slave needs to be restarted immediately when the data structure of the master database or slave database needs to be changed;

3. You cannot directly write the configuration information of the master in the mysql configuration file. You need to use the change master command;

4. Specifying replicate_do_db must be configured in my. cnf and cannot be completed using the change master command;

5. if it is not cleared in time, the accumulated binary log file may fill up the disk space. You can add expire_logs_days = 7 to the configuration file to keep the logs for the last seven days, we recommend that you use reset slave to cancel relaylog when slave is no longer used;

Finally, write a shell script and use nagios to monitor two "yes" of slave. If only one or zero "yes" is found, it indicates that the Master/slave node has a problem and sends an alert message.

Iii. MySQL-MMMWorking Principle

MMM (Master-Master replication managerfor Mysql, Mysql Master replication manager) is a set of flexible script programs, based on perl implementation, used for mysql replication Monitoring and Fault migration, and can manage the configuration of mysql Master-Master replication (only one node can be written at a time ).

• Mmm_mond: monitors the process and determines and processes all node role activities. This script needs to be run on the supervisor

• Mmm_agentd: A proxy process running on each mysql server, which completes probe monitoring and executes simple remote service settings. This script needs to be run on the supervised Machine

• Mmm_control: A simple script that provides commands for managing the mmm_mond Process

Mysql-mmm regulators provide multiple virtual IP addresses (VIPS), including one writable VIP address and multiple readable VIP addresses. These IP addresses are bound to available mysql through regulatory management, when a mysql instance goes down, the supervisor will migrate the VIP instance to another mysql instance.

During the whole supervision process, related authorized users need to be added to mysql so that mysql can support the maintenance of the supervision machine. The authorized users include an mmm_monitor user and an mmm_agent user. To use the mmm backup tool, add an mmm_tools user.

Advantages and disadvantages

• Advantages: high availability, good scalability, automatic failover in case of faults. For master-master synchronization, only one database write operation is provided at the same time to ensure data consistency.

• Disadvantage: The Monitor node is a single point and can be combined with Keepalived for high availability.

Database allocation

monitor192.168.1.29master-1192.168.1.25master-2192.168.1.26slave 1192.168.1.27slave 2192.168.1.28VIPwriter192.168.0.31reader192.168.0.32reader192.168.0.33

Unified authorization

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.%' IDENTIFIED BY 'replication';

Master-1 configure 192.168.1.25

[Mysqld] # bin_logbinlog_format = mixedmax_binlog_size = 200Mlog_bin =/data/logs/mysql/binarylog/mysql_binexpire_logs_days = 0log_slave_updates # When a primary fault occurs, another one immediately takes over sync-binlog = 1 # Each automatic update, high security, the default value is 0 # relay_logrelay_log = mysql-relay-bin # replicateserver_id = 1binlog-do-db = abc # databases that need to record binary logs. Multiple binlog-ignore-db = mysql, information_schema, performance_schema # databases that do not need to record binary logs. Multiple databases are separated by commas (,). auto_increment_increment = 1 # auto_increment_offset = 1 # start value of the auto-increment field at a time, set different replicate-do-db = abc # databases to be synchronized. Write multiple replicate-ignore-db = information_schema # databases that are not synchronized, write multiple rows replicate-ignore-db = mysql # non-synchronous database, multiple write multiple rows

Authorization

change master tomaster_host='192.168.1.26',master_user='replication',master_password='replication',master_log_file='mysql-bin.000006',master_log_pos=120;

Master-2 configure 192.168.1.26

[Mysqld] # bin_logbinlog_format = mixedmax_binlog_size = 200Mlog_bin =/data/logs/mysql/binarylog/mysql_binexpire_logs_days = 0log_slave_updates # When a primary fault occurs, another one immediately takes over sync-binlog = 1 # Each automatic update, high security, the default value is 0 # relay_logrelay_log = mysql-relay-bin # replicateserver_id = 2binlog-do-db = abc # databases that need to record binary logs. Multiple binlog-ignore-db = mysql, information_schema, performance_schema # databases that do not need to record binary logs. Multiple databases are separated by commas (,). auto_increment_increment = 1 # auto_increment_offset = 1 # start value of the auto-increment field at a time, set different replicate-do-db = abc # databases to be synchronized. Write multiple replicate-ignore-db = information_schema # databases that are not synchronized, write multiple rows replicate-ignore-db = mysql # non-synchronous database, multiple write multiple rows

Authorization

change master tomaster_host='192.168.1.25',master_user='replication',master_password='replication',master_log_file='mysql-bin.000009',master_log_pos=220;

Possible problems

1. Slave_IO_Running = NO, Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file ', error_code: 1236

2. Slave_ SQL _Running = NO, Error 'can't create database 'abc'; database exists 'on query. Default database: 'abc'. Query: 'create database abc'

3. Authorization failed

After the master synchronization configuration is complete, check the synchronization status. If YES is set for Slave_IO and Slave_ SQL, the master synchronization is successful.

Slave-1 and slave-2 are slave databases of master-1.

change master tomaster_host='192.168.1.25',master_user='replication',master_password='replication',master_log_file='mysql-bin.000002',master_log_pos=434;

View the following information on slave1 and slave2: the master-slave replication is successful. But the data didn't come over. This is because the master-slave replication principle only synchronizes the added, deleted, and modified records after configuration. The previous data cannot be synchronized. We can back up the master database and then restore it.

mysql> show slave status \G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.1.25                  Master_User: replication                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql_bin.000009          Read_Master_Log_Pos: 410               Relay_Log_File: mysql-relay-bin.000012                Relay_Log_Pos: 283        Relay_Master_Log_File: mysql_bin.000009             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB: abc          Replicate_Ignore_DB: information_schema,mysql           Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 410              Relay_Log_Space: 725              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 2                  Master_UUID: fc4e74ed-563f-11e5-bff1-000c29ee3b5c             Master_Info_File: /data/mysql/master.info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it           Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 0

MySQL-MMM installation Configuration

By default, CentOS does not have the mysql-mmm package. We recommend that you use the epel network source. epel is installed on all five servers:

rpm -ivh http://mirrors.ustc.edu.cn/fedora/epel/6/x86_64/epel-release-6-8.noarch.rpm

Monitor node Installation

yum -y install mysql-mmm-monitor

Db node Installation

yum -y install mysql-mmm-agent

Db nodes authorize monitor access

GRANT REPLICATIONCLIENT ON *.* TO 'mmm_monitor'@'192.168.1.%' IDENTIFIED BY '123456';GRANT SUPER,REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.1.%' IDENTIFIED BY '123456';

Mmm_common.conf file (five identical servers)

Active_master_role writer 
Mmm_agent.conf on the db end
Include mmm_common.confthis db1 # change to the Host Name of the local machine, that is, db1, db2, db3, and db4.
Management Terminal mmm_mon.conf File
Include plugin <monitor> ip 127.0.0.1 pid_path/var/run/mysql-mmm/mmm_mond.pid bin_path/usr/libexec/mysql-mmm status_path/var/lib/mysql-mmm/mmm_mond.status ping_ips 192.168.1.25, 192.168.1.26, 192.168.1.27, 192.168.0.28 # Real database IP address, to check whether the network is normal auto_set_online 10 # automatically set the online time after recovery </monitor> Start MySQL-MMM
Start the db proxy
/etc/init.d/mysql-mmm-agent startchkconfigmysql-mmm-agent on
Start the monitor Console
/etc/init.d/mysql-mmm-monitor startchkconfigmysql-mmm-monitor on
View Cluster status
[root@localhost ~]# mmm_control show
 
References
MySQL of MMM
Configure MySQL MMM from scratch