Shulou(Shulou.com)06/01 Report--

Hot and cold data separation

With the continuous growth of business, the data in cluster fragments will also increase with the passage of time, a considerable part of which is rarely used, such as order records, transaction records, commodity reviews and other data from a few years ago. This part of the data is called cold data, on the contrary, the frequently used data is called hot data.

We all know that when the amount of data in a single table in MySQL exceeds 20 million, read and write performance degrades sharply. If most of the stored data are high-value hot data, you can spend money to expand the cluster fragmentation, because these data can bring benefits. But if it's low-value, cold data, there's no need to spend it.

Therefore, we need to strip the cold data from the cluster slicing and store it in a special archive database in order to free up storage space and reduce the storage pressure of cluster slicing. Let the cluster sharding store only hot data as far as possible to maintain a good read and write performance without wasting storage space on cold data:

The InnoDB engine is not suitable for use on archive databases because InnoDB does not have high transient write performance. TokuDB produced by Percona is usually used as the storage engine of the archive database. Because the engine has the following characteristics:

High compression ratio, high write performance, online index creation and field support transaction features support master-slave synchronization to build Replication clusters

The previous section introduced the concept of hot and cold data separation, and in this section we build a high-availability Replication cluster for archiving cold data. Although it is an archived library, it has to be highly available. After all, a single point of failure of the database is not allowed in the actual enterprise. And the data in the archive library is not unused, it's just that the probability of using it is not high.

The Replication cluster architecture in this article is designed as follows:

The so-called Replication cluster is what we often call the master-slave architecture. In the Replication cluster, nodes are divided into Master and Slave roles. Master mainly provides write services, Slave provides read services, and Slave is usually set to read_only.

Data synchronization between master and slave nodes is carried out asynchronously. Slave uses a thread to listen to the binlog log of the Master node. When the binlog log of the Master changes, the thread will read the binlog log contents of the Master and write to the local relay_log. Then the mysql process reads the relay_log regularly and writes the data to the local binlog file, thus realizing the data synchronization between the master and the slave. As shown in the following figure:

In order to ensure the high availability of the Replication cluster, we need to make the master-slave relationship between the two database nodes to achieve two-way data synchronization. Only in this way can the master-slave switch be carried out when the master node dies, otherwise the master node will not synchronize data with the slave node after recovery, which will lead to data inconsistency between nodes:

Preparatory work

Next, to prepare the pre-environment for the cluster, you need to create four virtual machines first, of which two are installed with Percona Server as Replication clusters, and two with Haproxy and Keepalived as load balancers and dual-machine hot backups:

Role HostIPHaproxy+KeepalivedHA-01192.168.190.135Haproxy+KeepalivedHA-02192.168.190.143Percona Servernode-A192.168.190.142Percona Servernode-B192.168.190.131

Each virtual machine is configured as follows:

Environment release Notes:

Operating system: CentOS 8Percona Server:8.0.18TokuDB:8.0.18Haproxy:1.8.15-5.el8Keepalived:2.0.10-4.el8_0.2 install TokuDB

As mentioned earlier, InnoDB should adopt TokuDB because it is not suitable to be used as a storage engine for archive database. TokuDB can be installed on any derivative of MySQL, and this article uses Percona Server, a derivative of MySQL, as a demonstration.

I have installed Percona Server on the two virtual machines 192.168.190.142 and 192.168.190.131 beforehand. If you don't know how to install it, please refer to: install Percona Server database (in CentOS 8). Next, we begin to install TokuDB for Percona Server.

First, make sure that the jemalloc library is available on the system before installing TokuDB. If not, you can install it using the following command:

[root@node-A ~] # yum install-y jemalloc [root@node-A ~] # ls / usr/lib64/ | the path where the grep jemalloc # library file resides libjemalloc.so.1 [root@node-A ~] #

Add the configuration of the path where the jemalloc library file is located in the configuration file:

[root@node-A ~] # vim / etc/ my.cnf.. [MySQL _ safe] malloc-lib=/usr/lib64/libjemalloc.so.1

After completing the modification of the configuration file, restart the database service:

[root@node-A ~] # systemctl restart mysqld

In order to ensure the write performance of TokuDB, we need to adjust the large page memory management settings of the Linux system, as follows:

# Defragment memory using dynamic memory allocation instead of pre-allocated memory [root@node-A ~] # echo never > / sys/kernel/mm/transparent_hugepage/enabled# [root@node-A ~] # echo never > / sys/kernel/mm/transparent_hugepage/defrag

Install the TokuDB engine through the official yum repository:

[root@node-A ~] # yum install-y https://repo.percona.com/yum/percona-release-latest.noarch.rpm[root@node-A ~] # percona-release setup ps80 [root@node-A ~] # yum install-y percona-server-tokudb.x86_64

Then use the ps-admin command to install the TokuDB engine on MySQL:

[root@node-A] # ps-admin-- enable-tokudb-uroot-p

Restart the database service:

[root@node-A ~] # systemctl restart mysqld

When the database restart is complete, execute the ps-admin command again to activate the TokuDB engine:

[root@node-A] # ps-admin-- enable-tokudb-uroot-p

Finally, use the show engines; statement to verify that the TokuDB engine has been successfully installed on MySQL:

Configure master-slave relationship

First, create database accounts for synchronization on the two nodes:

Create user 'backup'@'%' identified by' Abc_123456';grant super, reload, replication slave on *. * to 'backup'@'%';flush privileges;Tips: after you have created an account, you need to log in to each other on both nodes to ensure that the account is available

Then modify the MySQL configuration file:

[root@node-A ~] # vim / etc/my.cnf [mysqld] # set the node's idserver_id=101# to enable binloglog_bin=mysql_bin# and enable relay_logrelay_log=relay_bin

The other node has the same configuration, except that the server_id cannot be the same:

[root@node-B ~] # vim / etc/ my.cnf [mysqld] server_id=102log_bin=mysql_binrelay_log=relay_bin

After modifying the configuration file, restart the MySQL service:

[root@node-A ~] # systemctl restart mysqld [root@node-B ~] # systemctl restart mysqld configure the master-slave relationship of node-B to node-A

Enter the MySQL command line terminal of node-B and execute the following statements:

Mysql > stop slave;-- stop master-slave synchronization mysql > change master to master_host='192.168.190.142', master_port=3306, master_user='backup', master_password='Abc_123456';-- configure connection information of Master node mysql > start slave;-- start master-slave synchronization

Use the show slave status\ G; statement to view the master-slave synchronization status. The values of Slave_IO_Running and Slave_SQL_Running are both Yes to indicate that the master-slave synchronization status is normal:

Configure the master-slave relationship of node-A to node-B

In order to achieve two-way synchronization, node-An and node-B need a master-slave relationship with each other, so you also need to configure the master-slave relationship between node-An and node-B. Enter the MySQL command line terminal of node-An and execute the following statements respectively. Note that the master_host here needs to be the ip of node-B:

Mysql > stop slave;-- stop master-slave synchronization mysql > change master to master_host='192.168.190.131', master_port=3306, master_user='backup', master_password='Abc_123456';-- configure connection information of Master node mysql > start slave;-- start master-slave synchronization

After the same configuration is completed, use the show slave status\ G; statement to view the master-slave synchronization status. The values of Slave_IO_Running and Slave_SQL_Running are both Yes to indicate that the master-slave synchronization status is normal:

Test master-slave synchronization

After configuring the master-slave synchronization relationship between the two nodes, we have completed the construction of the Replication cluster. Next, let's create an archive table on any node to see if the data can be synchronized properly between the two nodes. The specific table SQL is as follows:

Create table t_purchase_201909 (id int unsigned primary key, purchase_price decimal (10,2) not null comment 'purchase price', purchase_num int unsigned not null comment 'purchase quantity', purchase_sum decimal (10,2) not null comment 'purchase price', purchase_buyer int unsigned not null comment 'purchaser', purchase_date timestamp not null default current_timestamp comment 'purchase date', company_id int unsigned not null comment 'purchase enterprise's id' Goods_id int unsigned not null comment 'Commodity id', key idx_company_id (company_id), key idx_goods_id (goods_id)) engine=TokuDB comment' Archive Table of purchase data for September 2019'

I can synchronize normally here, as shown in the figure, both nodes can see this table:

Install Haproxy

So far, we have completed the construction and testing of the Replication cluster. The next step is to make the Replication cluster highly available, and it's Haproxy's turn. Haproxy is an agent software that provides high availability, load balancing and applications based on TCP (layer 4) and HTTP (layer 7). Haproxy can be used to load balance the MySQL cluster, giving the cluster high availability and giving full play to the performance of the cluster.

Haproxy is an established load balancing component, so the installation package with this component in CentOS's yum repository is very easy to install. The installation commands are as follows:

[root@HA-01 ~] # yum install-y haproxy

After the installation is complete, edit the configuration file of Haproxy, add the monitoring interface and the configuration of the database node that requires an agent:

[root@HA-01 ~] # vim / etc/haproxy/haproxy.cfg# adds the following configuration item at the end of the file # Monitoring interface configuration listen admin_stats # bound ip and listening port bind 0.0.0.0etc/haproxy/haproxy.cfg# 4001 # access protocol mode http # URI relative address stats uri / dbs # Statistical report format stats realm Global\ statistics # for Login monitoring interface account password stats auth admin:abc123456# database load balancer configuration listen proxy-mysql # bound ip and listening port bind 0.0.0.0 ip 3306 # access protocol mode tcp # load balancing algorithm # roundrobin: polling # static-rr: weight # leastconn: minimum connection # source: request source ip balance roundrobin # Log format option tcplog # need to be load balanced host server node-A 192.168.190.142 check port 3306 check port 3306 weight 1 maxconn 2000 server node-B 192.168.190.131 maxconn 3306 check port 3306 weight 1 maxconn 2000 # detect dead chain option tcpka using keepalive

Since port 3306 is configured for TCP forwarding and 4001 is used as the access port for the Haproxy monitoring interface, these two ports need to be opened on the firewall:

[root@HA-01 ~] # firewall-cmd-- zone=public-- add-port=3306/tcp-- implementation [root @ HA-01 ~] # firewall-cmd-- zone=public-- add-port=4001/tcp-- implementation [root @ HA-01 ~] # firewall-cmd-- reload

After completing the above steps, start the Haproxy service:

[root@HA-01 ~] # systemctl start haproxy

Then use the browser to access the monitoring interface of Haproxy. The first visit will require you to enter a user name and password, which is configured in the configuration file:

After logging in successfully, you will see the following page:

The monitoring interface of Haproxy also provides comprehensive monitoring information. In this interface, we can see the connection information of each host and its own status. When the host cannot connect, the Status column displays DOWN, and the background color changes to red. In the normal state, the value is UP and the background color is green.

The other Haproxy node is also installed and configured using the above steps, so I won't repeat it here.

Test Haproxy

After the Haproxy service is set up, let's use remote tools to test whether we can connect to the database properly through Haproxy. As follows:

After the connection is successful, execute some SQL statements on Haproxy to see if you can insert and query the data properly:

We set up Haproxy to make the Replication cluster highly available, so finally, to test whether the Replication cluster has high availability, first stop one of the nodes:

[root@node-B ~] # systemctl stop mysqld

At this point, from the monitoring interface of Haproxy, you can see that the node-B node has been offline:

Now that there is one node left in the cluster, let's execute some SQL statements on Haproxy to see if the data can be inserted and queried properly:

As can be seen from the test results, insert and query statements can still be executed normally. In other words, even if a node is turned off at this time, the whole database cluster can still be used normally, indicating that the Replication cluster is now highly available.

Using Keepalived to realize the High availability of Haproxy

After achieving the high availability of the Replication cluster, we also have to achieve the high availability of Haproxy, because Haproxy, as an entry responsible for receiving client requests and forwarding them to the back-end database cluster, inevitably requires high availability. Otherwise, if Haproxy has a single point of failure, it will not be able to access all the database cluster nodes that are proxied by Haproxy, which will have a great impact on the whole system.

Only one available Haproxy needs to exist at the same time, otherwise the client will not know which Haproxy to connect to. This is why the virtual IP of Keepalived is adopted, which allows multiple nodes to replace each other with the same IP, and the client only needs to connect to the virtual IP from beginning to end. Therefore, it is Keepalived's turn to achieve the high availability of Haproxy. Before installing Keepalived, you need to enable the VRRP protocol of the firewall:

[root@HA-01 ~] # firewall-cmd-- direct-- permanent-- add-rule ipv4 filter INPUT 0-- protocol vrrp-j access [root @ HA-01 ~] # firewall-cmd-- reload

You can then install Keepalived using the yum command:

[root@HA-01 ~] # yum install-y keepalived

After the installation is complete, edit the configuration file for keepalived:

[root@HA-01 ~] # mv / etc/keepalived/keepalived.conf / etc/keepalived/keepalived.conf.bak # does not use the included configuration file [root@HA-01 ~] # vim / etc/keepalived/keepalived.confvrrp_instance VI_1 {state MASTER interface ens32 virtual_router_id 51 priority 100 advert_int 1 authentication {auth_type PASS auth_pass 123456} virtual_ipaddress {192.168.190.101}}

Configuration instructions:

State MASTER: defines the node role as master. When the role is master, the node can get VIP without contention. Multiple master is allowed in the cluster, and when there are multiple master, the master needs to compete for VIP. For other roles, VIPinterface ens32 can only be obtained when master is offline: define the name of the network card that can be used for external communication. The name of the network card can be viewed through the ip addr command: virtual_router_id 51: define the id of the virtual route, with a value of 0-255. the value of each node needs to be unique, that is, it cannot be configured to the same priority 100. define weight. The higher the weight, the more priority to get VIPadvert_int 1: define the detection interval of 1 second authentication: define the authentication information used in heartbeat check auth_type PASS: define the authentication type as password auth_pass 123456: define a specific password virtual_ipaddress: define virtual IP (VIP), need to be an IP under the same network segment, and each node needs to be consistent

After completing the above configuration, start the keepalived service:

[root@HA-01 ~] # systemctl start keepalived

When the keepalived service starts successfully, you can use the ip addr command to see the virtual IP bound to the Nic:

The other node is also installed and configured using the above steps, so I won't repeat it here. Note, however, that virtual_router_id cannot be configured the same, and virtual_ipaddress must be configured as the same virtual ip.

Test Keepalived

We have completed the installation and configuration of Keepalived above, and finally we will test whether the Keepalived service is properly available and whether Haproxy is already highly available.

First, test whether the virtual IP can ping properly on other nodes, and check the configuration if you can't ping it. As shown in the picture, I can communicate with ping normally here:

Common virtual IP ping failure situations:

Incorrect firewall configuration, virtual IP that does not correctly turn on VRRP protocol configuration is not in the same network segment as the IP of other nodes, Keepalived configuration is incorrect, or Keepalived does not start successfully at all

After confirming that you can ping the virtual IP of Keepalived from outside, use Navicat to test whether you can connect to the database through the virtual IP:

After the connection is successful, execute some statements to test whether it can be inserted properly and query the data:

There is almost no problem at this point. Finally, test the high availability of Haproxy and turn off the Keepalived and Haproxy services on one of the Haproxy nodes:

[root@HA-01 ~] # systemctl stop keepalived [root @ HA-01 ~] # systemctl stop haproxy

Then execute some statements again to test whether the data can be inserted properly and query the data. The following shows that the Haproxy node already has high availability:

Finally, all the services are restored to the running state to verify whether the data is consistent after the stopped node is restored. As follows, the data of my two Replication nodes are the same:

Practice data archiving

At this point, we have completed the construction of a highly available Replication cluster. The next step is to practice how to strip a large amount of cold data from PXC cluster shards and archive them to Replication clusters. I have two PXC cluster shards here:

You can refer to another article about PXC clusters: introducing Mycat for PXC clusters and building a complete high-availability cluster architecture.

There is a t_purchase table in each slice, and the table SQL is as follows.

Create table t_purchase (id int unsigned primary key, purchase_price decimal (10,2) not null comment 'purchase price', purchase_num int unsigned not null comment 'purchase quantity', purchase_sum decimal (10,2) not null comment 'purchase price', purchase_buyer int unsigned not null comment 'purchaser', purchase_date timestamp not null default current_timestamp comment 'purchase date', company_id int unsigned not null comment 'purchase enterprise's id' Goods_id int unsigned not null comment 'goods id', key idx_company_id (company_id), key idx_goods_id (goods_id)) comment' purchase list'

A total of 100w pieces of purchase data are stored in each slice:

Among them, 60w items of purchase data are purchased before 2019-11-01:

The requirement now is to split off and archive all the data prior to 2019-11-01. How can this be achieved? It must be troublesome to write your own code, but fortunately, a tool for archiving data is provided in the Percona toolkit: pt-archiver, which makes it easy to complete data archiving and saves you the trouble of writing your own archiving program. Pt-archiver has two main uses:

Export online data to offline for data processing to clean up expired data, and archive the data to a local archive table, or a remote archive server

To use pt-archiver, you must first install the Percona toolkit:

[root@node-A ~] # yum install-y https://repo.percona.com/yum/percona-release-latest.noarch.rpm[root@node-A ~] # percona-release enable ps-80 release [root@node-A ~] # yum install-y percona-toolkit

After the installation is complete, verify that the pt-archiver command is available:

[root@node-A] # pt-archiver-- versionpt-archiver 3.1.0 [root@node-A] #

Then you can use the pt-archiver command to archive the data. First, you need to create an archive table in the Replication cluster, whose name is suffixed with the date of the archived data, and the storage engine uses TokuDB. The specific table SQL is as follows:

Create table t_purchase_201910 (id int unsigned primary key, purchase_price decimal (10,2) not null comment 'purchase price', purchase_num int unsigned not null comment 'purchase quantity', purchase_sum decimal (10,2) not null comment 'purchase price', purchase_buyer int unsigned not null comment 'purchaser', purchase_date timestamp not null default current_timestamp comment 'purchase date', company_id int unsigned not null comment 'purchase enterprise's id' Goods_id int unsigned not null comment 'Commodity id', key idx_company_id (company_id), key idx_goods_id (goods_id)) engine=TokuDB comment' Archive Table of purchase data for October 2019'

Then use the pt-archiver command to complete the data archive, as shown in the following example:

[root@node-A ~] # pt-archiver-- source hackers 192.168.190.100PowerPrun3306 repertoire upright adminary pamphleAbcend123456-- dest hobbies 192.168.190.101mPrun3306 moulding archiveParticipals-- dest hints 123456packs-- no-check-charset-- where 'purchase_date < "2019-11-01 0:0:0"'-progress 50000-- bulk-delete-bulk-insert-limit=100000-statisticsTips:pt-archiver command is used for data import using load data statement So make sure MySQL turns on local_infile. Archiving data will fail if it is not enabled, you can use the set global local_infile = 'ON'; statement to open local_infile.

The command parameter description:

-- source: specify which database to read the data from-- dest: specify the database to which the data will be archived-- no-check-charset: the character set that does not check the data-- where: specify which data to archive. In this case, the data before 2019-09-11 will be archived-- progress: specify how many pieces of data to be archived to print status information-- bulk-delete: specify batch deletion of archived data. The deletion of data is guaranteed by transactions. There will be no deletion of data without successful archiving-- bulk-insert: specify bulk writes to archived data-- limit: specify how many pieces of data are archived each time-- statistics: print statistics after the archived data is completed.

Wait about 15 minutes for the data to be archived, and the output statistics are as follows:

At this point, you can see that the 60w data has been stored in the archive table on the Replication cluster:

In the original PXC cluster, there is only 40w of data left:

In this way, we have completed the separation of hot and cold data, and stored a large amount of cold data in the designated archive database.

Summary: separate hot and cold data, store low-value cold data in the archive library, maintain the reading and writing efficiency of hot data, save archived data using TokuDB engine, have high-speed writing characteristics, use dual-computer hot backup scheme to build archive library, have high availability, use pt-archiver to export a large amount of data and archive storage, and it is simple and easy.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.