"MySQL" High-availability cluster MMM

First,Common topologies for replication

The architecture of replication has the following basic principles:

(1) Each slave can have only one master;

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

(3) Each master can have a lot of slave;

(4) If you set Log_slave_updates,slave to be the master of other slave, it will spread the update of master.

MySQL does not support multi-master server replication (multimaster Replication)-that is, a slave can have more than one master. However, with a few simple combinations, we can build a flexible and powerful replication architecture.

1.1 Single Master and multiple slave

In real-world scenarios, MySQL replication over 90% is a master copy to one or more slave schema patterns, primarily for low-cost, scalable solutions to database-side applications with large read-stress ratios. Because as long as the pressure of master and slave is not too great (especially the slave pressure), the delay of asynchronous replication is generally very small. Especially since the slave end of the replication mode changed to two thread processing, but also reduced the slave end of the delay problem.

When the slave is increased to a certain number, the slave load on the master and the network bandwidth become a serious problem

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

(1) Different slave play different roles (e.g. using different indexes, or different storage engines);

(2) using a Slave as the backup master, copy only;

(3) Use a remote slave for disaster recovery;

1.2 Master-master of Active mode

Does this set up a replication environment that does not result in a cyclic replication of two MySQL? In fact, MySQL has long thought of this, so in MySQL Binarylog recorded the current MySQL Server-id, and this parameter is also the time we build mysqlreplication must be explicitly specified, Moreover, the Server-id parameter values of master and slave are more inconsistent than necessary to make the mysqlreplication build successfully. Once you have the value of Server-id, it is easy for MySQL to tell which mysqlserver the change originated from, so it is easy to avoid a recurring replication situation.

Active Master-master replication has some special uses. For example, two parts that are geographically distributed require a copy of their own writable data. The biggest problem with this structure is the update conflict. Assume that a table has only one row (one column) of data, with a value of 1, and if two servers execute the following statements, respectively:

Execute on the first server:

mysql> UPDATE tbl SET col=col + 1;

Execute on the second server:

mysql> UPDATE tbl SET col=col * 2;

So what's the result? One server is 4 and the other server is 3, but this does not produce an error.

In fact, MySQL does not support a number of other DBMS-supported multi-master server replication (multimaster Replication), which is a significant limitation of the replication functionality of MySQL (the difficulty of a multi-master server is to resolve the update conflict), but if you really have that need, You can use MySQL Cluster and combine Cluster and replication to build a powerful, high-performance database platform. However, there are other ways to emulate this multi-master server replication.

1.3 Active-Passive mode master-master This is a master-master structure change, it avoids the disadvantage of m-m, in fact, this is a fault-tolerant and high-availability system. The difference is that one of the services can only be read-only. 1.4 Cascade Replication Architecture Master–slaves-slaves

In some applications, it may be that the pressure difference between reading and writing is large, the reading pressure is particularly large, and a master may need 10 or more slave to support the pressure of the reading. At this time, master will be more difficult, because only the Slaveio thread is more, so write a little bit more pressure, the master side because replication will consume more resources, it is easy to cause replication delay.

How to solve this problem? At this point, we can use MySQL to record the binarylog information on the slave side of the change that was generated, that is, to open the-log-slave-update option. Then, a two-level (or more-level) copy is required to reduce the stress on the master side as a result of replication. That is, we first replicate from master through a handful of MySQL machines, which we would call the first level slave cluster, and then the other slave to replicate from the first level slave cluster. From the first level slave to replicate the slave, I call it the second level slave cluster. If necessary, we can continue to add more layers of replication. This makes it easy for us to control the number of slave attached to each mysql. This architecture I call master-slaves-slaves architecture.

This multi-level replication architecture, it is easy to solve the master side because the secondary slave too many to become the bottleneck of the risk, of course, if the conditions allow, I would prefer to recommend by splitting into multiple replication clusters to solve

The above bottleneck problem. After all, slave did not reduce the amount of write, all slave actually still applied all the data change operations and did not reduce any write Io. Conversely, the more slave, the greater the total amount of write Io for the entire cluster, the more we don't have a very obvious feeling, simply because it's scattered over multiple machines, so it's not easy to show.

In addition, increasing the cascade level of replication, the same change to the bottom of the slave need to pass the MySQL will also be more, the same may cause a longer delay risk.

And if we were to solve this by splitting the cluster, it would probably be much better, and a split cluster would require more complex technologies and more complex application architectures.

1.5 The advantage of this structure with the master-master structure (master-master with slaves) from the server is that it provides redundancy. Geographically distributed replication structure, it does not have a single node failure problem, and can also put read-intensive requests on the slave.

cascading replication, to a certain extent, does solve the problem that Master has become a bottleneck because of the excessive number of slave attached to it, but he does not solve the problem of manual maintenance and the possibility of re-building replication after an exception needs to be switched. This naturally brings out the replication architecture that Dualmaster and cascade replication, which I call the master-master-slaves architecture

Compared to the master-slaves-slaves architecture, the difference is simply replacing the first-level slave cluster with a separate master, as an alternate master, and then copying from this alternate master to a slave cluster.

The greatest benefit of this dualmaster architecture is that it avoids the impact of the primary master's write operations from the replication of the slave cluster. While the master master needs to switch, there is basically no case of re-replication. However, this architecture also has a disadvantage, that is, the backup master may become a bottleneck, because if the subsequent slave cluster is larger, the standby master may be a bottleneck because of excessive SLAVEIO thread requests. of course, the standby master does not provide any read service, the bottleneck is not particularly high probability, if there is a bottleneck, you can also re-cascade replication after the standby master, set up a multilayer slave cluster. Of course, the more levels of Cascade replication, the more noticeable the data latency that may occur in the slave cluster, so before considering multi-level replication, you need to evaluate the impact of the data latency on the application system.

Second, MySQL master-slave replication

principle of Master-slave replication

is divided into synchronous replication and asynchronous replication, and most of the actual replication schemas are asynchronous. The basic process for replication is as follows:

1). Slave The above IO process connects to master, and requests the log content from the specified location (or from the beginning of the log) to the designated log file;

2). After master receives a request from the IO process from slave, the IO process that is responsible for the replication reads the log information after the specified location of the log according to the requested information and returns the IO process to the slave. In addition to the information contained in the log, the returned information includes the name of the Bin-log file returned to the master side and the location of the Bin-log;

3). After the slave IO process receives the information, the received log content is added to the end of the Relay-log file on the slave side, and the file name and location of the Bin-log read to the master side are recorded in the Master-info file. In order to be able to tell Master clearly at the next reading, "I need to start from somewhere in the Bin-log log content, please send me";

4). Slave's SQL process detects new additions to the relay-log and immediately resolves the contents of the Relay-log as executable content at the real execution of the master side and then plays back.

Installation configuration

Primary server master:192.168.1.30

From 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

Authorized

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

Slave configuration

server-id=131 #从服务器ID号, not the same as the primary ID master-host=192.168.1.30 #指定主服务器IP地址master-user=sync # Specify the user name that can be synchronized on the primary server master-password=123456 #密码master-port=3306 # sync Port master-connect-retry=60 # Breakpoint New Connection Time Replicate-ignore-db=information_schema,mysql,performance_schema #屏蔽对mysql库的同步replicate-DO-DB=ABC # Name of the synchronized database replicate_do_table = TT #同步的数据表的名称

Authorized

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_hos              t: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-bi n.000004 relay_log_pos:283 relay_master_log_file:mysql_bin.000042 Slave_io_running:ye S Slave_sql_running:yes Replicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_T Able:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last _errno:0 last_error:skip_counter:0 exec_master_log_pos:120 Re           lay_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 have 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) 

Several issues to be aware of when MySQL replication single-table or multi-table replication

1. The database names of the main and slave libraries must be the same;

2. The replication of the master and slave libraries can be accurate to the table, but need to be restarted immediately if you need to change the main library or the data structure of the library from slave;

3. The configuration information of master cannot be written directly in the MySQL configuration file, and it needs to be completed with the Change Master command;

4. Specify that the replicate_do_db must be configured in the MY.CNF and cannot be completed with the Change Master command;

5. If not cleaned up in time, the accumulation of binary log files may be full of disk space, you can add expire_logs_days=7 in the configuration file, only the last 7 days of the log, it is recommended that when slave no longer used, through the reset slave to cancel Relaylog;

Finally, write a shell script, using Nagios to monitor the slave two "yes", if only one or 0 "yes", it indicates the master-slave problem, send alarm SMS

Third, Mysql-mmmWorking principle

MMM (master-master replication managerfor mysql,mysql primary master Replication Manager) is a flexible script that is based on Perl and is used to monitor and fail-over Mysql replication. And can manage the configuration of MySQL Master-master replication (only one node is writable at the same time).

• Mmm_mond: Monitor processes, responsible for all monitoring tasks, determine and process all node role activities. This script needs to be run on a supervised machine

• MMM_AGENTD: Run the agent process on each MySQL server, complete the monitoring of probe work and perform simple remote service settings. This script needs to be run on a supervised machine

• Mmm_control: A simple script that provides commands to manage the Mmm_mond process

Mysql-mmm's regulatory side will provide multiple virtual IP (VIP), including a writable VIP, multiple readable VIPs, through regulatory management, these IP will be tied to the available MySQL, when a certain MySQL downtime, the supervision will transfer VIP to other MySQL.

Throughout the regulatory process, it is necessary to add relevant authorized users to MySQL so that MySQL can support the maintenance of the supervision machine. Authorized users include a mmm_monitor user and a mmm_agent user, and add a mmm_tools user if you want to use the MMM Backup tool.

Advantages and Disadvantages

• Advantages: High availability, scalability, automatic failover, for primary master synchronization, only one database write operation at the same time, ensure the consistency of the data

• Cons: The monitor node is a single point that 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 Licensing

GRANT REPLICATION SLAVE on * * to ' REPLICATION ' @ ' 192.168.1.% ' identified by ' REPLICATION ';

Master-1 Configuration 192.168.1.25

[Mysqld] #bin_logbinlog_format =mixedmax_binlog_size=200mlog_bin=/data/logs/mysql/binarylog/mysql_binexpire_logs _days=0log_slave_updates           #当一个主故障, another immediately take over Sync-binlog=1               #每条自动更新, high security, default is 0#relay_logrelay_log = MYSQL-RELAY-BIN#REPLICATESERVER_ID=1BINLOG-DO-DB=ABC           #需要记录二进制日志的数据库, multiple binlog-ignore-db=mysql separated by commas, Information_schema,performance_schema  #不需要记录二进制日志的数据库, multiple auto_increment_increment=1 separated by commas  #字段一次递增多少auto_ Increment_offset=1     #自增字段的起始值, values are set differently REPLICATE-DO-DB=ABC        #同步的数据库, multiple write multiline replicate-ignore-db = Information_ Schema #不同步的数据库, more than one write multiple lines replicate-ignore-db = MySQL #不同步的数据库, more than one write multiple lines

Authorized

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 Configuration 192.168.1.26

[Mysqld] #bin_logbinlog_format =mixedmax_binlog_size=200mlog_bin=/data/logs/mysql/binarylog/mysql_binexpire_logs _days=0log_slave_updates           #当一个主故障, another immediately take over Sync-binlog=1               #每条自动更新, high security, default is 0#relay_logrelay_log = MYSQL-RELAY-BIN#REPLICATESERVER_ID=2BINLOG-DO-DB=ABC           #需要记录二进制日志的数据库, multiple binlog-ignore-db=mysql separated by commas, Information_schema,performance_schema  #不需要记录二进制日志的数据库, multiple auto_increment_increment=1 separated by commas  #字段一次递增多少auto_ Increment_offset=1     #自增字段的起始值, values are set differently REPLICATE-DO-DB=ABC        #同步的数据库, multiple write multiline replicate-ignore-db = Information_ Schema #不同步的数据库, more than one write multiple lines replicate-ignore-db = MySQL #不同步的数据库, more than one write multiple lines

Authorized

Change Master tomaster_host= ' 192.168.1.25 ', master_user= ' replication ', master_password= ' replication ', Master_log_ File= ' mysql-bin.000009 ', master_log_pos=220;

Problems that may occur

1, slave_io_running = NO, Slave I/o: Got fatal error 1236 from master If reading data from binary log: ' Could not ' find fi RST 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 failure

Master Sync configuration Complete, view sync status Slave_io and slave_sql Yes indicates master sync succeeded

slave-1 and Slave-2 as master-1 from the library

Change Master tomaster_host= ' 192.168.1.25 ', master_user= ' replication ', master_password= ' replication ', Master_log_ File= ' mysql-bin.000002 ', master_log_pos=434;

In Slave1 and slave2 see the following instructions for master-slave replication success. But the data did not come, this is because the master-slave replication principle only synchronized configuration after the deletion and change records, the previous data is not synchronized, we can put the main database back up, and then restore

Mysql> Show slave status \g*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos  t:192.168.1.25 master_user:replication master_port:3306 connect_retry: master_log_file:mysql_bin.000009 read_master_log_pos:410 Relay_log_file:mysql-r elay-bin.000012 relay_log_pos:283 relay_master_log_file:mysql_bin.000009 Slave_io_runn Ing:yes Slave_sql_running:yes replicate_do_db:abc Replicate_ignore_db:information_sch Ema,mysql REPLICATE_DO_TABLE:REPLICATE_IGNORE_TABLE:REPLICATE_WILD_DO_TABLE:REPLICATE_WILD_IGN ore_table:last_errno:0 last_error:skip_counter:0 Exec_m aster_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 Have 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

CentOS does not have a mysql-mmm package by default, the official recommendation is to use Epel's network source, with five installed Epel:

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 node authorizes 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 (same as five)

Active_master_role      writer
DB End Mmm_agent.conf
Include Mmm_common.confthis db1   #分别修改为本机的主机名, i.e. DB1, DB2, DB3 and DB4
Admin-side mmm_mon.conf file
Include mmm_common.conf<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 to detect if the network is    normal     auto_set_online  #恢复后自动设置在线的时间 </monitor>
Start Mysql-mmm
DB Proxy-side boot
/etc/init.d/mysql-mmm-agent startchkconfigmysql-mmm-agent on
Monitor Management side Boot
/etc/init.d/mysql-mmm-monitor Startchkconfigmysql-mmm-monitor on
View cluster status
[Email protected] ~]# Mmm_control Show
Reference articles
"MMM MySQL"
"Configure MySQL MMM from scratch"
"MySQL" High-availability cluster MMM