Architecture Design: System Storage (--MYSQL) Simple master-slave scheme and the problem of exposure

1. Overview

From the beginning of this article we will spend a certain amount of time to introduce to the reader MySQL various service clusters of the way to build. The general idea is to start with the simplest MySQL master-slave scheme, which extends the more complex clustering scenarios and describes how the latter can be improved for these shortcomings. MySQL has a lot of clustering techniques, and this article will select some typical cluster scenarios to introduce to the reader.

2, MySQL most simple master-slave scheme and working principle

The version we're explaining is still based on the most current versions of version 5.6 in the production environment, some of which have been improved in version 5.7 and the latest version 8.0, but this does not affect the reader's understanding of the technical idea of building a MySQL cluster through articles, This mechanism can even be extended to mariadb. For example, the log copy mechanism (Replicaion mechanism) that comes with MySQL comes up immediately.

The log replication mechanism that comes with MySQL is called mysql-replicaion. From the early version 5.1 of MySQL, there is replicaion technology, developed to the existing version of the technology is already very mature, through its support technicians can make a variety of MySQL cluster structure. Of course, we'll also introduce some MySQL cluster scenarios supported by third-party software/components later in this article.

2-1. Basic working principle of mysql-replicaion

The replicaion mechanism, technically speaking, has two basic roles: Master and Salve. The master node is responsible for outputting data to one or more targets in the replicaion mechanism, while the Salve node is responsible for accepting data from the master node in the replicaion mechanism. in the actual business environment, the master node and the Salve node have another name: the Write node and the Read node--yes, using the replicaion mechanism we can build a MySQL Cluster service that targets read-write separation . However, in order to ensure that readers are not ambiguous in reading the content of the article, we will use the master node and the Salve node in this article (and subsequent articles). The replicaion mechanism relies on the MySQL service's binary log synchronization data:

As shown, salve establishes a network connection to the master node after booting, and when the binary log of the master node changes, one or more of the MySQL Salve service nodes will hear these change logs through the network. The Salve node then writes these changes locally to the relay log file (Relay log), in order to try to avoid the MySQL service failing to synchronize the data in the event of an exception, similar to how the InnoDB Log previously described works. When the relay log file has completed recording, the MySQL Salve service will reflect these changes to the corresponding data table and complete the data synchronization process. Finally salve updates the update point (Position) in the Redo log file and prepares for the next replicaion operation.

In this process, multiple features can be configured, such as the number of data operations on the master node and the number of log writes can be configured through the Sync_binlog parameter, the information structure of the log data can be configured through the Binlog_format parameter, and can be sync_ The Relay_log parameter configures the ratio of the system receive log data on the Salve node to the number of times the relay log file is written. These parameters and some other parameters used in the example are described in the following subsections of this article.

2-2, MySQL one master more from the way of building

After introducing the basic working mode of MySQL replicaion mechanism, we quickly build a MySQL cluster consisting of a master node and a Salve node. Readers can extend any master-slave cluster solution from this one-master-slave mysql cluster scenario:

This example is set up with version 5.6, and of course version 5.7 installs similarly. In addition, the process of installing MySQL services and basic setup on a Linux operating system (Centos 5.6/5.7/6.x) is not explained here because of the length and location of the article. We will install the master node and the Salve node of the cluster separately on the Linux operation of the following IP:

• MySQL Master Service: 192.168.61.140

• MySQL Salve Service: 192.168.61.141

2-2-1, setting the master server

The first thing you need to do is change the MySQL Master Service my.cnf Master profile, primarily to turn on the binary logging feature on the master node (note that the log here is not the InnoDB engine log).

# my.cnf文件中没有涉及Replicaion机制的配置信息，就不在这里列出了......# 开启日志log_bin# 以下这些参数会在后文进行说明sync_binlog=1binlog_format=mixedbinlog-do-db=qiangbinlog_checksum=CRC32binlog_cache_size=2Mmax_binlog_cache_size=1Gmax_binlog_size=100M# 必须为这个MySQL服务节点设置一个集群中唯一的 server id信息server_id=140......

In the master node settings, there are many parameters to the log generation, storage, transmission process control. Refer to the MySQL website for details: http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html. Here we mainly outline the parameters that appear in the configuration examples above:

• Sync_binlog: This parameter can be set to any value from 1 to N. This parameter indicates how many times the MySQL service has successfully completed the write operation of the binary log file after the number of inseparable data operations (for example, a transaction operation under the InnoDB engine). Set to 1 o'clock, the number of times to write log files is the most frequent, but also cause a certain amount of I/O performance consumption, but at the same time such a setting value is the safest.

• Binlog_format: This parameter can have three settings values: row, statement, and mixed. Row represents the final value that is modified after each row of the data table is written in a binary log. The salve nodes that participate in synchronization also refer to this final value to modify the data on their own data tables, and the statement form is to record data manipulation processes in the log, rather than the final execution results. Each participating Salve node resolves the process and forms the final record; the mixed setting value is a mixture of the above two recording modes, and the MySQL service automatically chooses the most appropriate logging mode for the current operating state.

• BINLOG-DO-DB: This parameter is used to set the name of the database on the MySQL master node that requires replicaion operations.

• Binlog_checksum: This parameter is used to set the log data check method used by the master node and the Salve node when the log file data is synchronized. This parameter is a new configuration feature that was supported at the beginning of version 5.6, and the default value is CRC32. If you have a MySQL node in a MySQL cluster that uses version 5.5 or earlier, set the value of this parameter to none.

• Binlog_cache_size: This parameter sets the cache size of the log data temporarily stored on the master node for each client connection session (session) that is used during the transaction. If you are not using an engine that supports transactions, you can ignore the setting for this value. But generally we will use the InnoDB engine, so this value is best set to 1m--2m, if more complex transactions are often performed, it can be appropriately increased to 3m--4m.

• Max_binlog_cache_size: This value represents the maximum value of the Binlog_cache area that can be used throughout the MySQL service. This value is not set in session, but globally.

• Max_binlog_size: This parameter sets the maximum size of a single binlog file. MySQL service in order to avoid binlog log errors or salve synchronization failures, new Binlog files are created in two cases: one is when the MySQL service restarts, and the other is that the Binlog file size reaches a set threshold (default is 1GB). The Max_binlog_size parameter is the setting of this threshold.

After you have completed the changes to the My.cnf file, the new configuration for restarting the Linux MySQL service takes effect. Next, you need to set up the Salve node information in the master node that can be connected, including the user and password information for the replicaion synchronization:

# only with MySQL client, can be set: # Here we use the root account directly to synchronize, but the production environment is not recommended to use this > Grant replication slave on *.*  to [email protected] identified by  ' 123456 '  # through the following command, you can view the master node working status after Setup is complete > Show Master status;+- ---------------+----------+--------------+------------------+-------------------+   | File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+----------------+----------+--------------+------------------+-------------------+  Span class= "Hljs-header" >|      kp2-bin.000002 | 404 |                  Qiang |                   | |+----------------+----------+--------------+------------------+-------------------+

In the description of the master node state above, the file attribute describes the name of the current binary log file, and its default location is under the Var/lib/mysql directory under the Linux operating system. The Position property describes the location of the data points in the log file where the log synchronization is currently completed. The Binlog_do_db property is the name of the database that we have previously set, which requires a replicaion operation, and the binlog_ignore_db attribute is a database name that is explicitly ignored and does not require a replicaion operation.

2-2-2, setting up Salve server

After completing the configuration of the MySQL master service, let's take a look at how the Salve node is set up. Here we only show the settings of a Salve node, and if you want to add a new salve node to the cluster, the configuration process is similar. It's all about adding a new Salve node description on the master node.

First, we still need to set the my.cnf file for the Salve node:

# my.cnf文件中没有涉及Replicaion机制的配置信息，就不在这里列出了......# 开启日志log-binsync_relay_log=1# 必须为这个MySQL服务节点设置一个集群中唯一的server id信息server_id=140......

The various control parameters of the relay log are also described in detail in the MySQL official documentation, where we only use the Sync_relay_log parameter. This parameter shows how many times the Salve node has successfully received master synchronization log information before it is brushed into the trunk log file. This parameter can be set to any value from 1 to N, although setting to 1 will consume some performance, but it is the safest for log data.

The setup of salve is relatively simple, then we need to turn on the corresponding synchronization function on the salve side. Primarily specify the master service address, user, and password information for synchronization:

# Please note that the username and password information set here is consistent with the settings on Master# also the file name specified by master log file must match the log file name used on Master> Change Master to master_host= ' 192.168.61.140 ', master_user= ' root ', master_password= ' 123456 ', master_log_file = ' kp2-bin.000002 ', master_log_pos=120;# start Savle sync> Start slave;# Then we can use the following command to view the synchronization status of the Salve node> Show slave status;************************** * 1. Row * **************************slave_io_state:waiting for Master to send eventmaster_host:192.168.61.140Master_user:rootmaster_port:3306connect_retry:60master_log_file:kp2-bin.000002read_master_log_pos:404relay_log_file:vm2-relay-bin.000002relay_log_pos:565relay_master_log_file:kp2-bin.000002Slave_io_running:yesSlave_sql_running:yes                    ......master_server_id:140MASTER_UUID:19632F72-9A90-11E6-82BD-000C290973DFmaster_info_file:/var/lib/mysql/master.infosql_delay:0Sql_remaining_delay:nullSlave_sql_running_state:slave have read all relay log; Waiting for the Slave I/O thread to update itmaster_retry_count:86400                   ......auto_position:0

Complete the above process, a master-slave MySQL cluster is configured to complete.

2-3, one master multiple from the use of the proposal

A master multi-slave MySQL cluster solution has been able to solve the performance requirements of most systems structured data storage . In particular, the data query frequency/number is much larger than the data write frequency/number of business scenarios, such as the commodity module of the e-commerce system, the vehicle/driver information module of the logistics system, the customer information module of the Telecom CRM system, the basic log data stored in the monitoring system. However, this architecture solution does not solve all the problems, and the solution itself has some obvious problems (discussed in detail later), so here we need to provide some suggestions for use for readers who will be using similar MySQL cluster scenarios.

• Master single-node performance should be strong enough and only responsible for data write operations: a master-slave MySQL cluster is primarily targeted at read-intensive business systems, with the main goal of separating the read and write pressures from the MySQL service . So the master node needs to focus on the business's write requests, which means that all of the write operation pressure on the business system is focused on this node (write business operations). We're not going to analyze the problems that this phenomenon can cause (the next step is to mention the problem with this approach), but this requires at least the performance of the master node to be strong enough. The performance here is not simply defined by the various configurations provided by the MySQL InnoDB engine (typically we use the InnoDB engine), and the performance that is squeezed as much as possible with the business features, and the fundamental need to improve the hardware performance of the master node.

  Using SSDs as a block storage base for MySQL services and using a RAID 10 disk array as a hardware layer build-up logic for a single MySQL service node in a production environment, the reader can, of course, make the necessary adjustments to the actual capacity and performance requirements of his production environment:

• A stand-alone salve node should be used as the standby master node, Although this approach is not intended as a basis for offsite live scenarios but can be used as a basis for local high-availability scenarios . Of course, to prevent backup failures due to log errors, the Salve node of this backup can also use a third-party synchronization mechanism other than the MySQL replicaion mechanism, such as Rsync, DRBD. Rsync is often used in the work of the author, the method of incremental synchronization of MySQL data, and the difference block synchronization method of DRBD is the way to find the most information on the Internet:

  In subsequent articles, we will also discuss the cluster tuning scenario for master nodes, and suggest how readers can use highly available scenarios that are appropriate for the system's own business. For example, use keepalived/heartbeat to switch the primary and standby master node:

• Complex statistical queries require dedicated salve nodes for support. Any MySQL service node that participates in real-time business processing in the production environment should be as simple as possible and require the use of indexes to support retrieval. In particular, a data table with very large data size must ensure that all retrieval operations have index support, otherwise table full scan will not only slow down the retrieval operation itself, but may also significantly slow down other transactional operations. With the execution plan function provided by MySQL, the technical staff can easily achieve the above requirements. If your business system has complex business query requirements, such as cyclical financial reporting, periodic business grouping statistics reports, and so on, then you'd better prepare one (or more) salve nodes out of the real-time business to do the job.

3. Problem of program exposure

But this MySQL cluster solution also has many problems that need to be further improved. In subsequent articles, we will discuss the following issues in the MySQL cluster in turn :

• Problems facing the top-level system: in MySQL a master multiple from the cluster, there is a service node. So when the upper business system is doing database operations (whether it is a write operation or a read operation), do you need to know these specific service nodes explicitly and connect them? It is important to know that when the higher-level business systems need to control the more important elements, the maintenance effort that needs to be put into the business system developer will grow at a geometric scale.

• High-availability plane problem: in MySQL one master multiple slave cluster, although there are multiple salve nodes (read business nature node), but generally there is only one master node (write business Nature node). A single (or multiple) Salve node crashes and does not have a significant impact on the entire cluster (but may affect one subsystem of the upper business system). Then the MySQL cluster's short board is the only master node--Once it crashes, the entire cluster will basically not work properly. So we have to think of some way to change that potential risk.

Architecture Design: System Storage (--MYSQL) Simple master-slave scheme and the problem of exposure