@[toc]

I. Purpose and environment of the experiment

Experimental purpose:

MySQL is the most commonly used open source database product on the Internet. But we usually develop and use, mostly with stand-alone services. However, in actual production, the amount of data is often extremely large, and the data security requirements are higher, so the performance and security of the standalone MySQL cannot meet the requirements. So in a production environment, MySQL must build a master-slave replication architecture, and implement a highly available architecture based on some tools. Then, on top of that, you can implement a read-write separation architecture based on some middleware. Finally, if the amount of data is very large, it must also be able to implement the architecture of sub-database and sub-table.

Of course, the architecture construction of MySQL is relatively complex, usually by specialized operation and maintenance personnel to build. Therefore, the purpose of this experiment is not to learn how to build a MySQL cluster, but to bring you a certain understanding of the MySQL architecture in the production environment, so that you can use the MySQL production architecture in your own production projects. At the same time, it is also to lay a foundation for future study of ShardingSphere sub-database sub-table.

Experimental environment:

1. Two Linux servers: centos7

Mysql 8.0.20 mysql 8.0.20

Description:

Installation of MySQL: The basic installation of Linux services, which has been explained in the MySQL optimization course before, will not be covered here. For details about how to install the MySQL server, see the manual on the official website.

MySQL installation manual address: dev.mysql.com/doc/refman/…

If you want to experiment on Linux and have problems with MySQL installation, you can recommend using the pagoda panel to save a lot of MySQL installation problems.

In addition, students who are familiar with Docker can directly use Docker to build, which is more simple and efficient and also the mainstream way for Internet enterprises to build services in the future. You can also pay attention when you learn Docker in the future.

Two, basic environment introduction

CentOS7 is installed on both servers.

1. 192.168.232.128 is deployed as the primary mysql node

2. 192.168.232.129 is deployed as the secondary mysql node

Mysql version: mysql-8.0.20

To facilitate the use of the two mysql services, you need to enable the remote login permission. To enable the remote login permission, you need to log in to mysql on the local host and run the following statement:

Use mysql; updateuser set host=The '%' where user='root';
flush privileges;
Copy the code

3. Set up primary and secondary clusters

1. Theoretical basis

What is the use of master-slave architecture? By setting up MySQL primary and secondary clusters, you can ease the pressure of MySQL data storage and access.

1. Data security

Add a data backup to the master service. For this purpose, you can build master-slave architectures, or you can build reciprocal architectures based on master-slave architectures.

2, read and write separation

For most JAVA business systems, read requests are much higher than write requests. In this case, when the access pressure of the primary service is too high, the secondary service can share the data read requests. The primary service only handles the data write requests, which greatly relieves the database access pressure.

Understand that MySQL’s master-slave architecture is only a foundation for achieving read/write separation. Read/write separation still requires some middleware support, such as ShardingSphere.

3. Failover – High availability

When the primary MySQL service is down, the secondary MySQL service can be switched to the primary MySQL service and continue to provide data read and write functions.

For high availability architectures, master/slave data synchronization is only a prerequisite for failover. To achieve MySQL master/slave switchover, some other middleware is needed to achieve it. Such as MMM, MHA, MGR.

In a typical project, read/write separation may not be necessary if the database access pressure is not so high, but master/slave architectures and high availability architectures are.

2, the principle of synchronization

The master-slave architecture of MySQL services is generally implemented through binlog log files. That is, open binlog on the master service to record every step of the database operation, and then there will be an IO thread on the slave service, responsible for establishing a TCP connection with the master service, request the master service to transfer binlog over. The primary library has an IO dump thread that transfers binlogs to the secondary LIBRARY’S I/O threads over the TCP connection. The IO thread from the service then writes the binlog data it reads to its own relay log file. Then another SQL thread from the service will read the contents of the relay log and repeat the operation to restore the data. The usual read-write separation configuration for MySQL must be based on a master-slave architecture.

MySQL binlog can be used not only for master/slave synchronization, but also for cache data synchronization.

Canal, for example, can simulate a slave node, initiate binlog synchronization to MySQL, and then land data to Redis, Kafka and other components to realize real-time data flow.

There are two requirements for setting up a master/slave cluster:

• The versions of MySQL on both sides must be the same.At least the version of the master service must be smaller than that of the slave service
• The time between the two nodes must be synchronized.

3. Set up primary and secondary clusters

3.1 Configuring the Master Server

CNF: /etc/my.cnf: /etc/my.cnf: /etc/my.cnf: /etc/my.cnf: /etc/my.cnf: /etc/my.cnf: /etc/my.cnf: /etc/my.cnf: /etc/my.cnf: /etc/my.cnf We open the my.cnf file of the MySQL main service, and in the file there is a line of server-ID and a configuration to turn off domain name resolution. Then restart the service.

[mysqld]
server-id=47
# open binlog
log_bin=master-bin
log_bin-index=master-bin.index
skip-name-resolve
Set the connection port
port=3306
Set mysql installation directory
basedir=/usr/local/mysql
Mysql > select * from 'mysql'
datadir=/usr/local/mysql/mysql-files
# Maximum number of connections allowed
max_connections=200
The number of connection failures allowed.
max_connect_errors=10
The default character set used by the server is UTF8
character-set-server=utf8
The default storage engine to use when creating new tables
default-storage-engine=INNODB
The mysql_native_password plugin is used by default
#mysql_native_password
default_authentication_plugin=mysql_native_password
Copy the code

Configuration description: The following attributes need to be modified:

Server-id: indicates the unique id of a service node. You need to assign a separate ID to each service in the cluster.

Log_bin: Enables Binlog recording and specifies the file name.

Log_bin-index: indicates a Binlog file

Run the service mysqld restart command to restart the MySQL service

Then, we need to assign a replication slave to user root.

#Logging in to the primary database
mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'root'@'%';
flush privileges;
#View the synchronization status of the primary node.
show master status;
Copy the code

In actual production environments, the root user is not used directly, but a user with full rights is created to perform primary/secondary synchronization.

The File and Position in the result of this directive record the binlog File of the current log and the index in the File.

The Binlog_Do_DB and Binlog_Ignore_DB fields indicate libraries that need to record binlog files and those that do not. The fact that we are not currently configured means that we are logging for the full library. How these two fields are configured is described later.

When binlog is enabled, all operations in the database are logged to the Datadir, cycling through a set of polling files. The command finds the File and Position of the current log File and location. Later, when you configure the slave service, you need to use this File and Position to tell you where to start the binLog from the service.

3.2 Configuring the Slave Service

Next, let’s configure the slave service mysqls. Open mysqls config file my.cnf and modify the config file:

[mysqld]
Master and slave libraries need to be inconsistent
server-id=48
Open MySQL relay log
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
Enable binary logging from the service
log-bin=mysql-bin
Write the updated data to the binary log
log-slave-updates=1
Set port 3306
port=3306
Set mysql installation directory
basedir=/usr/local/mysql
Mysql > select * from 'mysql'
datadir=/usr/local/mysql/mysql-files
# Maximum number of connections allowed
max_connections=200
The number of connection failures allowed.
max_connect_errors=10
The default character set used by the server is UTF8
character-set-server=utf8
The default storage engine to use when creating new tables
default-storage-engine=INNODB
The mysql_native_password plugin is used by default
#mysql_native_password
default_authentication_plugin=mysql_native_password
Copy the code

Configuration description: The following attributes need to be noted:

Server-id: indicates the unique id of a service node

Relay -log: enables relay-log of the secondary service.

Log-bin: Enables bin-log logging of the secondary service.

Then we start the mysqls service and set its master node synchronization state.

#Logging in to the slave service
mysql -u root -p;
#Set the synchronization master node:
CHANGE MASTER TO
MASTER_HOST='192.168.232.128',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='master-bin.000004',
MASTER_LOG_POS=156
GET_MASTER_PUBLIC_KEY=1;
#Open the slave
start slave;
#View the master/slave synchronization statusshow slave status; Or show slave status \G; That's a neat way to look at itCopy the code

Note that the MASTER_LOG_FILE and MASTER_LOG_POS specified in the CHANGE MASTER command must be the same as those found in the MASTER service.

In addition, if you want to check whether the master/slave architecture is successful, you can also check whether the File and Position properties between the master and slave services are consistent.

We focus on the two properties of the red box, which are consistent with the master node, indicating that the master-slave synchronous setup is successful.

As you can see from the result of this directive, there are a number of Replicate_ properties that specify which databases and which table configurations are synchronized between the two services. It’s just that in our example none of this is configured, marking it as full-library synchronization. We’ll fill in later on how to configure the libraries and tables that need to be synchronized.

3.3 Primary/Secondary Cluster Testing

Showdatabases is used to check the database status of the two MySQL services

Then we create a database on the primary server

mysql> create database syncdemo;
Query OK, 1 row affected (0.00 sec)
Copy the code

We then use show Databases to see if the syncDemo database has been synchronized to the slave service.

Next we continue to create a table in the syncDemo database and insert a data entry.

mysql> use syncdemo;
Database changed
mysql> create table demoTable(id int not null);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into demoTable value(1);
Query OK, 1 row affected (0.01 sec)
Copy the code

We then check whether the demoTable is synchronized to the slave service as well as the master service.

As you can see from the experiment above, all the data operations we performed in the master service have been synchronized to the slave service. In this way, we set up a master – slave cluster completed.

If Slave_SQL_Running=no is found on the slave server, the master/slave synchronization fails. This may be because a write operation was performed on the slave database, which conflicted with the SQL operation synchronized from the slave, or the transaction was rolled back after the slave was restarted from the service.

If the slave transaction is rolled back, you can restart the master/slave synchronization in the following way:

mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;
Copy the code

Another solution is to re-log the binlog file messages for the master node

mysql> stop slave ;
mysql> change master to. mysql> start slave ;

Copy the code

In this way, pay attention to the file and location of the binlog. If the file is not connected to the previous synchronization, some data will be lost. So not very often.

3.4 Cluster Construction and Expansion:

After completing this basic MySQL master-slave cluster, we can do further experiments:

1. Full synchronization and partial synchronization

As mentioned earlier, we currently configure master/slave synchronization for the full database configuration, but in the actual environment, there is no need to do a backup for the full database, and only need to do synchronization for some particularly important libraries or tables. So how do you synchronize the configuration for libraries and tables?

First on the Master side: in my.cnf, you can specify which libraries or tables to log against

The binary database name to synchronize
binlog-do-db=masterdemo
Keep binary logs for only 7 days in case the disk is full of logs (optional)
expire-logs-days  = 7
# Database not backed up
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys

Copy the code

Then on the Slave side: in my.cnf, you need to configure the mapping between the backup library and the library of the master service.

If the salve library name is the same as the master library name, use this configuration
replicate-do-db = masterdemo 
If the master library name [mastdemo] is different from the salve library name [mastdemo01], use the following configuration:
replicate-rewrite-db = masterdemo -> masterdemo01
Specify the tables that need to be synchronized if not all
replicate-wild-do-table=masterdemo01.t_dict
replicate-wild-do-table=masterdemo01.t_num

Copy the code

After the configuration is complete, you can see the Binlog_Do_DB and Binlog_Ignore_DB parameters in action in the show master status command.

2. Configure read/write separation

Note that the MySQL master-slave cluster is unidirectional, that is, it can only be synchronized from the master service to the slave service, and the tables from the slave service cannot be synchronized to the master service.

Therefore, in this architecture, to ensure data consistency, it is often necessary to ensure that data is only written on the master service and data is only read from the slave service. This feature is known as read-write separation. Note, however, that the master and slave of mysql cannot provide read/write separation services. The services must be implemented by themselves. This is also an important function of ShardingSphere that we will learn later.

In the master-slave architecture of MySQL, data writing from the slave service is strictly restricted. Once data is written from the slave service, data inconsistency will occur. It is also easy for slave services to fail to synchronize data during transactions.

If you want to limit user writing, you can set the read_only parameter to 1 in the slave service (set global read_only=1;). . This allows you to restrict the user from writing data. But there are two caveats to this property:

1. The read-only mode set by read_only=1 does not affect the slave synchronous replication function. MySQL slave library set read_only=1, run the “show slave status\G” command to check the status of the salve, you can see that the salve still reads the master log, and applies the log to the slave library. Ensure consistent synchronization between primary and secondary databases;

2. The read-only mode set by read_only=1 limits the data modification operations of common users, but does not limit the data modification operations of users with super permission. After setting read_only=1 in MySQL, common application users will report errors when performing INSERT, UPDATE, and delete operations that cause data changes. However, users with super permission will report errors when performing INSERT, UPDATE, and delete operations that cause data changes. For example, if you log in to the database as user root locally or remotely, you can still perform DML operations on data changes. If the user with super permission needs to write data, set super_read_only to 0. In addition, if you want to disable write operations even for super users, use “Flush tables with read lock;” This setting also prevents master/slave synchronous replication!

3. Other cluster methods

Here we set up a master and slave MySQL master and slave synchronization cluster, with the basic functions of data synchronization. In a production environment, larger and more complex clusters are built based on this, depending on business conditions and load.

For example, to further improve the read capability of the entire cluster, you can expand the cluster to have one master and multiple slaves. In order to reduce the data synchronization pressure of the master node, the master/slave cluster with multiple levels of slave can be extended.

To improve the high availability of the entire cluster, you can expand the cluster with multiple hosts.

We can also expand out of the mutual master and slave cluster or even ring master and slave cluster to achieve MySQL multi-active deployment.

To set up a slave cluster, open a slave process on the master service and point to the current binlog file address and location of the slave node.

Here we use the most traditional Binlog method to build the cluster, which is based on the log record point method to carry out master/slave synchronization. In this experiment, the Executed_Grid_Set column is actually another way to set up master-slave synchronization, known as the GTID construct. The essence of GTID is also a binlog-based master/slave synchronization, but it identifies the synchronization progress based on a global transaction ID. The GTID global transaction ID is a globally unique and trending distributed ID policy. We’re not going to build it here.

3.5. GTID synchronizes the cluster

The above cluster is based on Binlog log points, which is also the most traditional MySQL cluster construction method. In this case, you can see that there is an Executed_Grid_Set column that is not used yet. In fact, this is another way of setting up master-slave synchronization, known as GTID. This mode was introduced from MySQL5.6.

The essence of GTID is to implement master/slave synchronization based on Binlog, but it identifies the synchronization progress based on a global transaction ID. GTID is the global transaction ID. It is globally unique and increasing. It can ensure that a unique ID can be generated in the replication cluster for each transaction committed on the primary node.

In GTID based replication, first of all from the server will tell the primary server has been performed from the server which GTID transaction value, and then the main library will have to put all not in the execution of the transaction from the library, send to be executed from the library, and the use of GTID replication can guarantee the same transaction in the specified only once from the library, This avoids data inconsistencies due to offset problems.

It is built in much the same way as the master-slave architecture as a whole was built above. You just need to change some configuration in my.cnf.

On the master node:

Gtid_mode =on enforce_gtid_consistency= ON log_bin= ON Server_id = Set a single binlog_format=rowCopy the code

On the slave node:

Gtid_mode =on enforce_gtid_consistency=on log_slave_updates=1 server_id= Set it aloneCopy the code

Then restart the primary and secondary services respectively to enable GTID synchronous replication.

4. Expand the cluster

Now that we have set up a MySQL cluster architecture with one master and one slave, it is relatively easy to expand to a cluster architecture with one master and many slaves. We only need to add a binlog replication.

However, if our cluster has been running for a while, there is a problem if we want to expand the new slave node, the previous data cannot be recovered from the binlog. When extending a new slave node, a data replication operation needs to be added.

MySQL data backup and restoration operations are relatively simple, can be directly completed by SQL statements. You can use the mysqldump tool in the bin directory of mysql.

mysqldump -u root -p --all-databases > backup.sql
#Enter the password

Copy the code

With this command, you can export all data from the entire database as backup. SQL, then distribute this backup. SQL to the new MySQL server, and execute the following command to import all data into the new MySQL service.

mysql -u root -p < backup.sql
#Enter the password

Copy the code

Now that the new MySQL service has all the historical data, you can follow the steps above to configure data synchronization for the Slave service.

5. Semi-synchronous replication

1. Understand semi-synchronous replication

So far, we have been able to build MySQL master-slave cluster, mutual master cluster, but our cluster has a hidden danger, is likely to lose data. Why is that? This starts with MySQL master slave data replication analysis.

By default, the primary and secondary MySQL clusters use an asynchronous replication mechanism. After executing the user-submitted transaction, the master service writes to the binlog and returns a successful response to the client. The binlog is sent asynchronously by a dump thread to the Slave Slave service.

Because the process of sending the binlog is asynchronous. The master service does not know whether the binlog has been successfully synchronized when it reports back to the client. If the master service is down and the slave service has not been backed up to the newly executed binlog, data may be lost.

How to solve this problem? It depends on the semi-synchronous replication mechanism of MySQL to ensure data security.

Semi-synchronous replication is a mechanism before asynchronous replication and full synchronous replication. The master library does not return the client response immediately after executing the transaction committed by the client, but waits for at least one slave library to receive and write to the relay log before returning to the client. MySQL waits 10 seconds for confirmation by default. If it does not receive an ACK within 10 seconds, it degrades to asynchronous replication.

Semi-synchronous replication improves data security compared with asynchronous replication. However, this security is not absolute. It only guarantees that the binlog after the transaction is committed will be transferred to at least one slave library, and does not guarantee that the binlog applied to the slave library will be successful. Semi-synchronous replication, on the other hand, also imposes a certain amount of latency, which is at least the time it takes a TCP/IP request to make a round trip. The overall performance of the service will be degraded. When there is a problem with the slave service, the master service has to wait longer until the slave service recovers or the request times out before responding to the user.

2. Create a semi-synchronous replication cluster

Semi-synchronous replication needs to be implemented based on specific extension modules. Mysql from version 5.5 onwards comes with this module by default. This module is contained in semisync_master.so and semisync_slave.so files in the lib/plugin directory of mysql installation directory. The Semisync_master module needs to be installed on the master service and the Semisync_slave module needs to be installed on the slave service.

First we log in to the main service and install semisync_master module:

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like 'rpl_semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set.1 warning (0.02 sec)

mysql> set global rpl_semi_sync_master_enabled=ON;
Query OK, 0 rows affected (0.00 sec)

Copy the code

The first of these three lines is to install the semi-synchronous replication module through the extension library, and you need to specify the filename of the extension library.

In the second line, view global system parameters. Rpl_semi_sync_master_timeout is the maximum waiting time for semi-synchronous replication. The default value is 10 seconds.

The third line turns on the semi-synchronous replication switch.

When you look at the system parameters in the second line, the last parameter, rpl_semi_semi_SYNc_master_WAIT_point, actually represents a semi-synchronous replication mode.

Semi-synchronous replication can be done in two ways, one of which is the AFTER_SYNC default we see here. In this way, the master library writes logs to the binlog, copies them to the slave library, and then waits for the slave library to respond. The master commits the transaction and returns a success response to the client.

The other method is called AFTER_COMMIT. He’s not by default. In this way, after the master writes to the binlog, it waits for the binlog to be copied to the slave, the master commits its own local transaction, waits for the slave to return a successful response, and then the master returns a response to the client.

Then we log in to the slave service and install the smeisync_slave module

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like 'rpl_semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | OFF   |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set.1 warning (0.01 sec)

mysql> set global rpl_semi_sync_slave_enabled = on;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'rpl_semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set.1 warning (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

Copy the code

The installation process on the slave server is similar, but after installing the semi-synchronous plug-in on the slave server, you need to restart the slave service.

6. Data delay of master/slave architecture:

One of the hidden problems in our master-slave cluster is that there is a delay between such master-slave replicates. This is easier to do when you do read/write separation. That is, data is written to the master service and read data is read from the slave service. In this case, the master-slave replication delay may cause data to be inserted but not found. Of course, this is hard to do in our current cluster, but it’s easy to do in a larger cluster.

The root of this problem lies in the fact that the business-oriented master service data is concurrently written by multiple threads, while the slave service data is slowly pulled by a single thread, which will cause a difference in efficiency. So the key to solving this problem is to get the slave service to copy binlog data in parallel using multiple threads.

MySQL has supported parallel replication since version 5.7. You can set slave_parallel_workers to a number greater than 0 on the slave service, and then set the slave_parallel_type parameter to LOGICAL_CLOCK.

MySQL high availability solution — understand

Our previous MySQL service clusters were built using MySQL’s own functionality. However, such a cluster does not have the function of high availability. If the primary MySQL service fails, the secondary MySQL service cannot automatically switch to the primary MySQL service. If you want to achieve high availability of MySQL, you need to use some third-party tools to achieve.

There are three common MySQL cluster schemes: MMM, MHA, and MGR. All three high availability frameworks have something in common:

• Monitor the Master node in the primary and secondary replication clusters
• Automatically migrate Master via VIP.
• Reconfigure other slaves in the cluster to synchronize the new Master

1, MMM

MMM(master-Master Replication Managerfor Mysql) is a set of scripts implemented in Perl to monitor and failover Mysql clusters. He needs two masters. Only one Master provides services at a time.

It is through a VIP(virtual IP) mechanism to ensure high availability of the cluster. In the entire cluster, the primary node uses a VIP address to provide data read and write services. When a fault occurs, the VIP will migrate from the original primary node to other nodes to provide services.

Advantages:

• Provides read and write VIP configuration, so that all read and write requests can achieve high availability
• The toolkit is relatively complete and does not require additional development scripts
• After failover, the MySQL cluster can be monitored for high availability

Disadvantages:

• Semi-synchronous replication is recommended to reduce the probability of failure
• Currently MMM community lacks maintenance and does not support gTID-based replication

Applicable scenarios:

• Both read and write need to be highly available
• Log point-based replication

2, MHA

Master High Availability Manager and Tools for MySQL. Is a Perl script based tool developed by the Japanese. This tool is designed to monitor the status of the master library. When a master node is found to be faulty, the slave node with new data is promoted to become the new master node. In the meantime, the MHA will obtain additional information from other slave nodes to avoid data consistency problems. The MHA also provides the online switching capability of mater nodes, that is, switching master-slave nodes on demand. The MHA implements failover within 30 seconds and maximizes data consistency during failover. Inside Taobao, there is a similar TMHA product.

MHA needs to be deployed separately, including Manager Node and Node Node. The Manager node is generally a single machine deployed. Node nodes are typically deployed on each MySQL machine. The Node Node has to perform some operations by parsing each MySQL log.

The Manager Node will detect the nodes in the cluster to determine whether the MySQL running on each Node machine is normal. If a Master is found to be faulty, it will directly promote one of its slaves to Master, and then let the other slaves be attached to the new Master, completely transparent.

Advantages:

• MHA supports GTID as well as log point replication
• In contrast to MMM, MHA attempts to recover the old binary log from the old Master, but not always successfully. If you want fewer data loss scenarios, the MHA architecture is recommended.

Disadvantages:

The MHA needs to develop its own VIP transfer scripts.

The MHA monitors only the status of the Master, not the status of the Slave

3, MGR

MGR: MySQL Group Replication. MySQL group replication is a group replication mechanism introduced by MySQL in 5.7.17. It mainly solves the data consistency problem of traditional asynchronous replication and semi-synchronous replication.

A replication group is composed of several nodes. After a transaction is submitted, it can only be submitted after the resolution and approval of more than half of the nodes. Group replication is introduced to solve the data inconsistency problem caused by traditional asynchronous replication and semi-synchronous replication. MGR relies on the distributed consistency protocol (a variant of Paxos protocol) to achieve the ultimate consistency of distributed data and provide a true high availability solution for data (whether the solution is reliable after landing remains to be discussed).

Multi-master mode is supported, but single-master mode is officially recommended:

• In multi-master mode, clients can randomly write data to MySQL nodes
• In single-master mode, the MGR cluster selects the primary node to handle write requests, and the primary node and other nodes can handle read requests.

Advantages:

• Basically no latency, latency is much smaller than asynchronous
• Multi-write mode is supported, but is not yet mature
• Strong data consistency ensures that data transactions are not lost

Disadvantages:

• Innodb only, and each table must provide a primary key.
• This parameter can be used only in GTID mode and the log format is ROW.

Applicable service scenarios:

• It is sensitive to master-slave delay
• You want to provide high availability for write to write services without installing third-party software
• Strongly consistent data scenario

Five, the database and table

We did a lot of experiments earlier to help you understand the master-slave cluster of MySQL. The role of master-slave cluster, from the perspective of our development, is to support read and write separation, which is also the focus of our study of ShardingSphere later. We will introduce the sub-database sub-table in this part.

In this way, the service system distributes data write requests to master nodes and data read requests to slave nodes, which greatly improves the performance of the entire database cluster. However, it should be noted that the entire logic of the table and library is implemented by the client itself. For MySQL cluster, master/slave synchronization is a necessary prerequisite to achieve read/write separation.

1, what is the use of sub-database sub-table

Depots table in order to solve the performance degradation due to large amount of data of the database, the original split into several independent database of database, the data tables are split into a number of tables, making a single database, a single data table data quantity is small, so as to improve database performance of mesh.

For example, in a microservice architecture, each service is assigned a separate database, which is called a repository. Some service log tables are divided into different tables by month, which is called sub-tables.

2. The way of dividing database and table

Sub-database sub-table consists of sub-database and sub-table, and these two parts can be collectively referred to as data sharding, which aims to divide data into different storage units. In addition, from the point of view of separation, can be divided into vertical fragments and horizontal fragments.

• Vertical sharding: Data is sharded based on services. His core idea is to turn the library dedicated. Before splitting, a database consisted of multiple data tables, each corresponding to a different business. After the split, the tables are classified according to services and distributed to different databases or tables, thus distributing pressure to different databases or tables. For example, the following figure shards the user table and order table vertically into different databases:

Vertical sharding often requires architectural and design adjustments. Often, it is too late to respond to rapidly changing business requirements. Moreover, it does not really solve the performance bottleneck of a single point database. Vertical sharding can alleviate the problems caused by data volume and access, but it cannot cure them completely. If the amount of data in the table still exceeds the threshold that can be carried by a single node after vertical sharding, horizontal sharding is required for further processing.

• Horizontal sharding: also called horizontal sharding. As opposed to vertical sharding, it no longer categorizes data according to business logic. Instead, it divides data into multiple libraries or tables according to certain rules through a certain field (or fields), with each shard containing only a portion of the data. For example, sharding by primary key is shown below.

Common sharding strategies are:

Take mod: advantages of uniform storage data, disadvantages of expansion is very troublesome

Fragmented by scope: It is easy to expand capacity, and data is not evenly distributed

Sharding by time: It is easy to distinguish hotspot data.

Sharding by enumerated values: for example, sharding by region

Partitioning according to the target field prefix: Custom service rule fragmentation

Horizontal sharding breaks through the bottleneck of single machine data processing theoretically, and expands relative freedom. It is a standard solution of database and table.

Generally speaking, in the system design stage, we should determine the vertical branch library and vertical branch table scheme according to the tightness of the business coupling. In the case of the data volume and access pressure is not particularly large, we should first consider the cache, read and write separation, index technology and other schemes. If the amount of data is very large and continues to increase, then consider the scheme of horizontal sub-database and horizontal sub-table

3. Disadvantages of separate database and separate table

While data sharding solves performance, availability, and single point of backup and recovery issues, distributed architecture introduces many new problems while gaining benefits.

• Transaction consistency problem

The original stand-alone database has a good transaction mechanism to help us ensure data consistency. However, since the data is distributed in different libraries or even different servers, the problem of distributed transaction will be inevitable after the database is divided into different tables.

• Associated query across nodes

When there is no repository, we can easily perform associative queries across tables. But after the database is divided, the tables are scattered to different databases, and the associated query cannot be carried out.

At this point, you need to split the associated query into multiple queries, and then assemble the results.

• Cross-node paging, sorting functions

Problems such as limit paging and order by sorting become more complicated when querying across multiple libraries. The data needs to be sorted and returned in different shard nodes, and then the result sets returned by different shards are summarized and sorted again.

Memory crashes are very common.

• Primary key avoidance problem

In a partitioned database and table environment, since the data in the table is stored in different databases at the same time, the auto-growth used by the primary key is useless, and the ID generated by a partitioned database cannot be guaranteed to be globally unique. Therefore, global primary keys need to be designed separately to avoid cross-repository primary key duplication.

• Common table processing

In practical application scenarios, parameter tables and data dictionary tables are dependent tables with small amount of data and little change, and belong to high-frequency joint query. This type of table generally requires one copy in each database, and all operations on the common table are distributed to all branches.

• Operation and maintenance workload

In the face of scattered data after the sub-database sub-table, application development engineers and database administrators have become very heavy on the operation of the database. For every data read and write operation, they need to know which specific database table to operate on, which is one of the key challenges.

4. When do we need a separate database and table?

In the development manual published by Alibaba, it is suggested that if the MySQL single table records reach the level of 500W, or the single table capacity reaches 2GB, it is generally recommended to separate databases and tables. In consideration of the need to rebalance the data, so if you want to use the database and table, it is necessary to consider the scheme of the database and table in detail at the beginning of the system design, here to be divided into two cases.

Generally, for user data, which has a relatively slow growth in the later period, the number of users can be estimated according to the business volume of about three years, and the plan of sub-database and sub-table can be preset according to the standard.

However, for business data, which grows rapidly and stably, it is generally necessary to preset the sub-database and sub-table scheme at about twice the estimated value. And because the later expansion of the sub-database sub-table is very troublesome, so in the sub-database sub-table, as far as possible according to the situation, some more tables. It is better to calculate the data increment and never add more tables.

In addition, business scenarios and data distribution should be taken into account as far as possible when designing the scheme of database and table. On the premise of supporting business scenarios, try to ensure that data is evenly distributed.

Finally, once the use of sub-table, it will affect the flexibility of the data query business, for example, if the shard by userId, then the query by age, it will inevitably add a lot of trouble. If you have to sort, page, aggregate, and so on, you can easily get overwhelmed. At this time, we should try our best to design a demotion scheme while dividing database and table, such as transferring data to ES, which can realize more flexible big data aggregation query.

5, common sub-library sub-table components

As a result of sub – database sub – table, data is scattered in different databases, servers. As a result, manipulation of data cannot be done in a conventional way, and it presents a number of problems. Fortunately, not all of these problems need to be solved at the application level. There are many middleware options out there, so let’s take a look at them.

• Shardingsphere website address: shardingsphere.apache.org/document/cu…

Sharding-jdbc is an open source distributed database middleware developed by Dangdang. It is an ecosystem composed of open source distributed database middleware solutions. It is composed of three independent products, sharding-JDBC, Sharding-Proxy and Sharding-Sidecar (planned). They all provide standardized data sharding, distributed transaction and database governance functions, which can be applied to diverse application scenarios such as Java isomorphism, heterogeneous languages, containers, cloud native and so on.

And that’s what we’re going to focus on.

• Mycat’s official website is www.mycat.org.cn/

Based on the development of Ali open source Cobar products, Cobar’s stability, reliability, excellent architecture and performance as well as many mature use cases make MYCAT have a good starting point from the beginning, standing on the shoulders of giants, we can see further. Excellent open source projects and innovative ideas in the industry are widely integrated into MYCAT’s DNA, making MYCAT in many ways ahead of other similar open source projects, and even beyond some commercial products.

MyCAT grew out of Alibaba’s technology system, but it had nothing to do with the company.

• DBLE website address: opensource.actionsky.com/

The site contains several important products. Distributed middleware can be considered as an enhanced version of MyCAT, focusing on MySQL cluster management. There are also data transfer components and distributed transaction framework components to choose from.