The construction of scalable distributed database cluster

Each microservice application we design can accommodate high concurrency calls, so the database it connects to must also have this property to form a high-performance organic whole. Whether you install your own database or use a database provided by a cloud service provider, scalability is a prerequisite. For example, MySQL, MongoDB, and Redis are all capable of distributed cluster design. MySQL cluster design and installation

In the cluster design of MySQL, we first use master-slave synchronization design to build database cluster, and then use this cluster in the form of grouping to achieve high availability design through master-slave synchronization. For database access, OneProxy database proxy middleware will be used to achieve read/write separation design. Finally, the call to OneProxy will also use LVS(LinuxVirtualServer,

Linux virtual Server) technology to build a hot standby access mechanism. LVS will provide a virtual broadcast IP address, which is used as a VIP address for external services. Figure 13-1 shows the architectural design of this highly available database cluster.

Among them, the most important component is the cluster grouping design of database. This cluster grouping can continue to expand as the application platform evolves. During the installation and implementation process, we will create two cluster groups, each consisting of a host and two slave groups.

It should be noted that this highly available architecture design of read/write separation is completely transparent to a microservice application, regardless of how many clusters the database cluster consists of. Microservices call the database in the same configuration as before – a single data source for access, except that the corresponding connection address is changed to the VIP address provided by this highly available architecture.

Let’s start with the database installation and walk you through how to implement a highly available architecture in a distributed environment.

MySQL cluster host allocation

Six hosts are used to create two MySQL cluster groups. Table 13-1 shows the server resource allocation for cluster group 1.

Table 13-2 lists the server resources allocated to cluster group 2.

MySQL must be installed on all six hosts. You are advised to use the official YUM installation source to install the latest stable version of MySQL.

The yum installation source can be downloaded from the MySQL website.

Primary/secondary synchronization Settings

MySQL master/slave synchronization is set to read/write server on the host and read-only server on the slave

The data is synchronized from the host.

The following describes how to configure the master/slave synchronization of 10.10.10.35 (master) and 10.10.10.36 (slave).

1. Set the host

Run the following command to change the server name:

vi /etc/hostname
Copy the code

Change the file content to mysQ1-35.

Use the following command to modify the configuration of the database server:

Vi /etc/my.cnf Add the following configuration items to [mysqld] : [mysqld] # mysqld Log-bin =demo-mysql-bin # Set the memory allocated for each session, Cache used to store binary logs during transactions binlog_ cache_ size=1M # Mixed, statement,row The default format is Statement) binlog_ format=mixed # Number of days in which binary logs are automatically deleted or expired. The default value is 0, which does not automatically delete expire_ logs_ days=7 ## Skip all errors encountered in master/slave replication, or the specified type of error, to avoid slave replication interrupt ## 1032 Error Indicates data inconsistent between the master and slave databases. Slave_ skip_ errors=1032 # Relay_ 1og= Demo-mysq1-relay -bin # log_ slave Log_ slave_ updates=1 Increment_increment =2 # auto_increment offset=1Copy the code

After the configuration is complete, save the configuration and restart the database server using the following command:

service mysqld restart
Copy the code

Then log in to the server as root and use the following command to create a synchronous user and authorize:

Mysql > grant replication slave, replication client on *.* to 'user36'@'10.10.10.36' identified by 'user123456';Copy the code

User36 is the user name and user123456 is the password.

Use the following command to update permissions for the previous Settings to take effect immediately:

mysq1> flush privileges;
Copy the code

Run the following command to check the host status:

mysql> show master status;
Copy the code

The result is as follows:

IFile PositionI Binlog Do_ DB Binlog_ Ignore_ DBI Executed Gtid_ Set demo - mysql - bin. 000001 | mysql 1 row in the Set (0.00) sec)Copy the code

In the command, File indicates the name of the binary log File, and Position indicates the offset of the log saving Position. These two parameters will be used later in the slave setup.

2. Set the slave computer

The following uses slave server 10.10.10.36 as an example.

Run the following command to change the server name:

vi /etc/hostname
Copy the code

Change the content to mysqL-36.

Run the following command to modify the database configuration:

vi /etc/my.cnf
Copy the code

Add the following configuration items under [mysqld] :.

[mysq1d]
server_ id=36
binlog- ignore-db=mysq1
log-bin=demo-mysql-bin
Copy the code

Save the configuration and restart the database server. Then, log in to the database as user root and use the synchronization configuration shown below:

Mysql >change master to master_ host='10.10.10.35',master user='user36', master password=' user123456', master_ port=3306, master log file='demo-mysql-bin. 000001', master_ log_ _pos=123, master_ connect_ retry=30;Copy the code

Run the master_ log_ file command to set the log file of the host, and run the master_ log. pos command to set the offset of the log storage location of the host. The two parameters must be configured based on the status of the current host.

To start data synchronization, run the following command:

mysql>start slave;
Copy the code

Run the following command to check the synchronization status of the slave machine:

mysql>show slave status\G;
Copy the code

The result is as follows:

1. row
Slave_ IO_ State: Waiting for master to send event
Master_ Host: 10.10.10.35
Master User: user36
Master Port: 3306
Connect Retry: 30
Master_ Log_ File: demo-mysql -bin.000001
Read Master_ Log_ Pos: 123
Relay_ Log_ File: demo-mysq1 - relay-bin.000002
Relay_ Log_ Pos: 287
Relay Master_ Log_ File: demo-mysql-bin. 000001
Slave IO Running: Yes
Slave_ SQL Running: Yes 
Copy the code

In the preceding command output, if Slave_ IO_ Running and Slave_ SQL_ _Running are both Yes, the synchronization is successful.

You can set the slave machine of server 10.10.10.37 by referring to the above method.

Master master synchronization Settings

You can implement master/slave synchronization by configuring hosts in two cluster groups to synchronize each other.

Refer to section 13.2 to implement primary/secondary synchronization in cluster group 2.

The database configuration of the host 10.10.10.25 in cluster group 2 is similar to that in cluster group 1 except that the primary key configuration is slightly different to avoid conflicts. That is, the primary key is an even number, as shown in the following code:

[mysqld] server id=25 binlog- ignore-db=mysql log-bin=demo-mysql -bin binlog_ cache_ size=1M binlog_ format=mixed expire_ logs_ days=7 slave_ skip errors=1032 relay_ log=demo-mysql-relay-bin 1og slave_ updates=1 auto increment Increment =2 # Auto increment offset=2Copy the code

After the primary/secondary Settings for cluster group 2 are complete, the hosts in the two clusters can be synchronized to each other. Configure the primary/secondary Settings for host 10.10.10.35 in cluster group 1 and 10.10.10.25 in cluster group 2.

1. Configure hosts in cluster group 1

Create and authorize a synchronous user on host 10.10.10.35:

Mysql > grant replication slave, replication client on *.* to 'user25'@' 10.10.10.25 'identified by 'user123456';Copy the code

Update permission:

mysql> flush privileges;
Copy the code

View the host status:

mysql> show master status;
Copy the code

Note down the log file name and storage location offset in the view result.

2. Configure the slave machine of cluster group 2

Use the synchronization configuration shown below:

Mysql >change master to master_ host=' 10.10.10.35,master user='user25', master_ password= 'user123456', master_ port=3306, master log_ file=' demo-mysql-bin.000001', master_ 1og_ pos=123, master_ connect_ retry=30;Copy the code

Enter the log file name and storage offset based on the results queried by the host.

Start slave and synchronize:

mysql>start slave;
Copy the code

Viewing the synchronization status:

mysql>show slave status\G;
Copy the code

If the query result contains the following information, the synchronization is successful:

Slave_ I0 Running: Yes
Slave_ SQL Running: Yes
Copy the code

After the preceding configuration is complete, use 10.10.10.25 as the host and 10.10.10.35 as the slave to synchronize the master from the slave. Specific can refer to the above method to achieve. Once the master-slave setup is complete, the master-master synchronization setup is implemented.

To verify the synchronization Settings above, create databases on each host, perform some insert or delete operations, and then view the results on each slave. If all operations can be synchronized, the primary and secondary synchronization Settings are successful.

If synchronization fails, stop the slave machine, change the log file name and offset as required, and then start the slave machine to continue synchronization.

To stop the slave machine, run the following command:

mysql>stop slave;
Copy the code

It should be noted that in a production environment, it is recommended to use the UUID as the primary key of the database to avoid primary key conflicts and to facilitate the creation of more groups in the cluster.

Database agent middleware selection

After the realization of the database cluster, it has solved the performance bottleneck problem of the single server of the database, and also established a highly available distributed architecture, for applications and database clients, how to use the database to better use this high availability, high performance distributed cluster system? This is achieved with the help of database proxy middleware.

There are many database Proxy middleware of MySQL, most of which are open source, such as MyCat, Proxy, Amoeba and OneProxy, among which MyCat and OneProxy are excellent.

MyCat has excellent performance in heavy traffic access. It is developed in Java language, and its configuration file is in the form of XML, which is a bit complicated, especially its partition table configuration is a bit cumbersome. In addition, some users have complained about its stability, so OneProxy is recommended.

OneProxy is based on the design idea of the official Proxy middleware of MySQL. It has good stability and simple configuration. The concept of partition table is fundamental to the setting of MySQL partition table. They are consistent. Although it is commercially available for a fee, there is also a free community version available.

OneProxy is used for the read-write separation design

OneProxy can easily use MySQL’s cluster architecture. It can achieve high availability design by cluster grouping of databases, and can also achieve read/write separation design by master/slave synchronization. Figure 13-2 shows the network structure set up using OneProxy calls from two cluster groups.

Install OneProxy

The following uses version 6.0.0 as an example. OneProxy is installed on the machine whose IP address is 10.10.10.24.

After downloading the installation package, unzip it:

The tar xf oneproxy - rhel5 - on - v6.0.0 - ga. Tar. GzCopy the code

Move the program files to the /usr/local/ directory:

mv oneproxy /usr/ local/oneproxy
Copy the code

Switch to the oneProxy directory:

cd /usr/ local/oneproxy
Copy the code

Create launcher:

CP oneproxy .service /etc/ init.d/oneproxy
Copy the code

Modify the launcher configuration:

vi /etc/ init.d/oneproxy 
Copy the code

Modify the working directory as follows:

ONEPROXY_ HOME=/usr/loca1/ oneproxy
Copy the code

After saving the changes, set the execution permission of the launcher:

chmod a+x /etc/init. d/oneproxy
Copy the code

Use the following command to start OneProxy:

service oneproxy start
Copy the code

Use the following command to set up OneProxy after startup:

chkconfig --add oneproxy
chkconfig oneproxy on
Copy the code

High availability read/write separation configuration

When using OneProxy, you must create a single user with full permissions for each database. When you create a user, you must do so on a host in the database cluster.

For example, for the order service database “orderDB”, users can be created and authorized using the following command:

mysql> grant all privileges on orderdb.* to 'orderuser'@'8' identified by
'12345678' with grant option;
Copy the code

Create a user with full permissions named orderUser, password 12345678, and set it to be accessible anywhere, in this case on a secure LAN.

Note that the above authorized user will be stored in the mysql database user table, although we have ignored the mysql database synchronization in the master/slave Settings, but when creating the user, we did not use the use command to switch the database, so the above created user will be synchronized on each slave machine. If you want to disable this synchronization, you can add a configuration to the host’s database configuration that ignores the synchronization of the User table in the master/slave Settings. After doing this, the command to authorize the database must be executed on each server.

On the OneProxy server, run the following command in the ust/local/ OneProxy directory to generate the password encryption string:

. /bin/mysqlpwd 12345678
Copy the code

This generates the encrypted password string shown below:

40739ED24B5DC118DC16397AB1 4E64C680637C0D
Copy the code

Use the following command to edit the OneProxy configuration:

Vi. / conf/ porxy. conf The following shows the read/write separation configuration using two cluster groups: [oneproxy] keepalive event-threads = 4 log-file = 1og/oneproxy.1og pid-file = log/oneproxy.pid lck-file = Log /oneproxy. LCK mysql-version = 5.7.19 proxy-address = :3306 proxy-master-addresses.1 = 10.10.10.35: 3306@group1 proxy-master-addresses.2 = 10.10.10.25 :3306@group2 proxy-slave-addresses.1 = 10.10.10.36: 3306@group1 proxy-slave-addresses.2 = 10.10.10.37:3306@group1 proxy-slave-addresses.3 = 10.10.10.26:3306@group2 Proxy - slave - addresses. 4 = 10.10, 10.27:3306 @ group2 proxy - user - list. 1 = orderuser ed24b5dc118dc1/40739 6397AB14E64C680637C0D@orderdb proxy-user-list.2 = merchantuser/ 40739ED24B5DC118DC16397AB14E64C680637C0D@merchantdb proxy-part-tables.1 = /usr/ local/oneproxy/conf/part1. txt proxy-charset = utf8_ bin proxy-group-policy.1 = groupl:read balance proxy-group-policy.2 = group2: read balance proxy-group-security.1 = group1:0 proxy-group-security.2 = group2:0 proxy-security-level = 0 Proxy-repl ication-check=1 proxy-httptitle = OneProxy MonitorCopy the code

Among them, only configure the order service database “orderDB” access user orderUser, and merchant service database “merchantDB” access user merchantUser, other database access users can refer to the above method to add in.

After saving the configuration, run the following command to set the read permission for the configuration file:

chmod 660 conf/proxy. conf
Copy the code

Then, restart OneProxy for the previous configuration to take effect.

Table 13-3 describes the preceding parameters.

OneProxy also has an administrative background. After OneProxy is started, you can log in through the MySQL client. The default port is 4041, the user name is admin, and the password is OneProxy.

For example, you can use the following command to log in from a machine that has MySQL installed:

Mysql -u admin -h 10.10.10.24 -p4041-poneproxyCopy the code

After logging in to the management background, you can run commands listed in Table 13-4.

Note: Before using the commands in italics, understand the meaning of the commands to the system. Otherwise, data inconsistency may occur or the system may become unavailable.

In addition, you can also use the monitoring port to view the connection status of each database server in the browser, using the following links to open the monitoring console:

http://10.10.10.24:8080
Copy the code

Other detailed instructions on OneProxy configuration can be found on the OneProxy developer’s blog.

Three things to watch ❤️

If you find this article helpful, I’d like to invite you to do three small favors for me:

1. Like, forward, have your “like and comment”, is the motivation of my creation.

2. Follow the public account “Java rotten pigskin” and share original knowledge from time to time.

3. Also look forward to the follow-up article ing🚀

4. [666] Scan the code to obtain the learning materials package

Article is not original, and all of us feel while reading the article good remember thumb up forward + attention oh ~ this article source: www.toutiao.com/i6907204947…