SpringBoot e-Commerce project mall (25K + STAR) address: github.com/macrozheng/…

Abstract

The traditional MySql read-write separation scheme is implemented by dynamically switching data sources in the code according to the type of SQL statement. So is there any middleware that can automatically implement read-write separation? Xiaomi’s open source database middleware Gaea can be implemented. Next, we will explain in detail how to use Gaea to achieve read and write separation of MySql.

Gaea profile

Gaea is a database middleware based on MySql protocol developed by the e-commerce R&D Department of Xiaomi in China. Currently, Gaea is widely used in Xiaomi Mall mainland and overseas, including orders, communities, events and other businesses. Gaea supports basic features such as database and table, SQL routing, read and write separation, among which the database and table scheme is compatible with the routing modes of myCAT and Kingshard.

MySql primary/secondary replication

MySql master-slave replication: Gaea requires a MySql master-slave replication environment.

Install it directly under Linux

At present, the official way is to install Gaea directly under Linux. We will install Gaea in this way first.

Install the Go environment

Since Gaea is written in Go, we need to install the Go environment first.

  • To install the Go environment, download it from golang.org/dl/

  • After downloading, unzip to/mydataDirectory;
Tar -zxvf go1.13.5.linux-amd64.tar.gz -c /mydata/Copy the code
  • add/mydata/go/binDirectory to PATH variable:
Edit the environment variable configuration file
vim /etc/profile
# add on the last line
export GOROOT=mydata/go
export PATH=$PATH:$GOROOT/bin
# refresh the configuration file
source /etc/profile
Copy the code
  • Check the version number and check whether the installation is successful:
go version
Copy the code
  • The Go locale is successfully installed if the following information is displayed:
Go version go1.13.5 Linux/amd64Copy the code

Install Gaea

Since Gaea does not provide an installation package, we need to compile the source code ourselves to get the executable.

  • Download Gaea source code, directly download zip package, download address: github.com/XiaoMi/Gaea

  • Decompress the downloaded package into /mydata/gaea/ directory:

unzip Gaea-master.zip
Copy the code
  • Enter the/mydata/gaea/Directory, usemakeCommand to source compilation:
make build
Copy the code

  • Note: Due to network problems, some Go dependencies may not download, resulting in compilation failure. Try several times to succeed.

  • Gaea execution file gaEA will be generated in the /mydata/gaea/bin directory after compiling:

  • Because we didn’t build itetcdConfiguration center, so you need to modify the local configuration file/mydata/gaea/etc/gaea.iniTo change the configuration type tofile:
; The configuration type is file or etCD. File does not support hot loading
config_type=file
Copy the code
  • Add a namespace configuration file to configure our master and slave databases. The configuration file address is:/mydata/gaea/etc/file/namespace/mall_namespace_1.json

  • The configuration file is as follows:
{
    "name": "mall_namespace_1"."online": true."read_only": false."allowed_dbs": {
        "mall": true
    },
    "slow_sql_time": "1000"."black_sql": [
        ""]."allowed_ip": null."slices": [{"name": "slice-0"."user_name": "root"."password": "root"."master": "192.168.6.132:3307"."slaves": ["192.168.6.132:3308"]."statistic_slaves": null."capacity": 12."max_capacity": 24."idle_timeout": 60}]."shard_rules": null."users": [{"user_name": "macro"."password": "123456"."namespace": "mall_namespace_1"."rw_flag": 2."rw_split": 1."other_property": 0}]."default_slice": "slice-0"."global_sequences": null
}
Copy the code

Namespace Configuration File

The configuration format of a namespace is JSON. The namespace contains sub-table, non-sub-table, and instance configuration information, which can be changed during runtime.

  • Overall configuration description:

    The field names The field type Field meaning
    name string The namespace name
    online bool Whether online or not, logical online use
    read_only bool Read-only or not, namespace level
    allowed_dbs map A database that is allowed to be accessed through a proxy
    default_phy_dbs map Default database name, one-to-one mapping to allowed_dbs
    slow_sql_time string Slow SQL time, in ms
    black_sql The string array The blacklist SQL
    allowed_ip The string array Whitelist IP
    slices The map array A master and slave physical instance. See slice configuration for specific map fields
    shard_rules The map array For details about how to configure sub-database, sub-table, and special table, see Shard configuration
    users The map array For details about the user configuration required by the application to connect to gaEA, see Users Configuration

  • Slice configuration:

    The field names The field type Field meaning
    name string The shard name is automatically and orderly generated
    user_name string Database user name
    password string Database password
    master string Primary instance address
    slaves The string array From the database address, you can configure more than one
    statistic_slaves The string array Statistical slave instance address list
    capacity int Size of the connection pool between gaeA_Proxy and each instance
    max_capacity int Maximum connection pool size for gaeA_Proxy with each instance
    idle_timeout int Idle connection lifetime between GAeA_proxy and back-end mysql, unit: second

  • The users configuration:

    The field names The field type Field meaning
    user_name string Database agent username through which the client accesses the database
    password string The database agent password is used by the client to access the database
    namespace string Corresponding namespace
    rw_flag int Read/write identifier, read-only =1, read/write =2
    rw_split int Whether read/write separation, non-read/write separation =0, read/write separation =1
    other_property int Currently, this parameter is used to indicate whether to go to the statistics slave instance. Common user =0, statistics user =1

Run in a Docker container

Since the official only provides direct installation and running mode under Linux, here we provide another way to run, in the Docker container as a service.

Package as a Docker image

There is no packaged Gaea image in Docker Hub. We need to build one by ourselves. The following details are how to build a Gaea Docker image.

  • Here we use Dockerfile to build a Docker image. The content of Dockerfile is as follows:
The mirror needs the underlying mirror on which it depends
FROM golang:latest
/go/ gaea-master docker will automatically decompress.tar.gz files
ADD Gaea-master.tar.gz /go/Gaea-master
Move the decompressed source code to the /go/gaea directory
RUN bash -c 'mv /go/Gaea-master/Gaea-master /go/gaea'
Go to /go/gaea
WORKDIR /go/gaea
Package gaEA source code to compile
RUN bash -c 'make build'
The service is running on port 13306
EXPOSE 13306
# specifies the command to execute when the Docker container is started
ENTRYPOINT ["/go/gaea/bin/gaea"]
Specify the name of the maintainer
MAINTAINER macrozheng
Copy the code
  • To do this, we need to convert the Gaea source compression package to.tar.gzThe format is easy to decompress in the Docker container and can be usedCompression softwareTo achieve:

  • Then use Docker command to build Gaea Docker image:
Docker build-t GAEA :1.0.2.Copy the code
  • Build success console output:

  • Copy the locally installed Gaea configuration file to/mydata/gaea-docker/etc/Directory:
cp -r /mydata/gaea/etc/ /mydata/gaea-docker/etc/
Copy the code
  • Start the Gaea container with the Docker command:
docker run -p 13306:13306 --name gaea \
-v /mydata/gaea-docker/etc:/go/gaea/etc \
-dGaea: 1.0.2Copy the code

Test read/write separation

Test thread: first we close the master/slave replication of the slave instance, and then through the Gaea agent to operate the database, insert a data, if there is this data in the master instance but not in the slave instance, indicating that the write operation is to go to the master library. Then query the table data through the Gaea agent. If the data does not exist, it indicates that the read operation is from the secondary library, which proves that the read and write separation is successful.

  • Connect to the Gaea agent through Navicat. Note that the account password is the content configured in the namespace of Gaea and the port is the service port of Gaea.

  • Navicat connects to the master library and the slave library to view data. The following three database connections are established.

  • throughstop slaveCommand to shut downmysql-slaveMaster/slave replication for instance:

  • Through Gaea agent intestInsert a row into a table:

  • View in the main librarytestSQL > select * from ‘table’;

  • View from the librarytestSQL > select * from primary database;

  • View directly in the agenttestIf no data is found in the table, it indicates that the read operation is from the library.

Used with SpringBoot

In our SpringBoot application, we just need to use the Gaea proxy service directly as a database service to achieve read and write separation. You don’t have to add any read-write separation logic to your code.

The resources

For more information, please refer to the official documentation: github.com/XiaoMi/Gaea

The public,

Mall project full set of learning tutorials serialized, attention to the public number the first time access.