Sharding-JDBC implementation of sub-library sub-table + read and write separation

Sharding-JDBC is used to achieve C_ORDER table sub-library sub-table + read and write separation

Shard c_ORDER table based on user_id

Set up one master and two slave architecture for Master1 and Master2 respectively

Implement read/write separation based on master1 and Master2 clusters

MySQL environment setup

MySQL installation

MySQL > install MySQL on 6 servers

If the mariadb database is installed on CentOS7 by default, you may encounter unexpected problems when saving Chinese.
yum remove mariadb-libs.x86_64
Copy the code
Download the MySql installation package
wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
Copy the code
Installing software Packages
rpm -ivh mysql-community-release-el7-5.noarch.rpm
Copy the code
Install the MySQL service program
yum install mysql-community-server
Copy the code
Restart the MySQL service
service mysqld restart
Copy the code
At this point, MySQL is successfully installed on the server.
Change the password

Mysql -uroot -p mysql -uroot -p mysql -uroot -p mysql -uroot -p
SET PASSWORD = PASSWORD('root123456');
Copy the code
MySQL: /etc/rc.local MySQL: /etc/rc.local
/etc/init.d/mysql start
Copy the code

After the installation is complete, try using Navicat to connect. If access is denied, you can temporarily disable the firewall:

CentOS 7.0 uses the firewall by default

Checking the Firewall Status
firewall-cmd --state
Copy the code
Stop the firewall
systemctl stop firewalld.service
Copy the code
Disable the firewall startup upon startup
systemctl disable firewalld.service 
Copy the code

1130 -host XXX is not allowed to connect to this MySQL server. The MySQL server does not support remote connection.

Log in to the server and enter the database
Mysql -uroot -p PasswordCopy the code
Set the permissions
grant all privileges on*. *to root@"%" identified by "root123456";
Copy the code
Make the configuration take effect localhost Run the following command to make the configuration take effect immediately.
flush privileges;
Copy the code
It has been successfully modified and can be connected remotely through Navicat (or some other tool)

MySQL primary/secondary configuration

The Master node

Run the vi /etc/my. CNF command to modify the Master configuration file

# bin_log configuration
log_bin=mysql-bin 
Ensure that each server ID does not have the same ID
server-id=1 
sync-binlog=1 
binlog-ignore-db=information_schema 
binlog-ignore-db=mysql 
binlog-ignore-db=performance_schema 
binlog-ignore-db=sys 
# relay_log configuration
relay_log=mysql-relay-bin 
log_slave_updates=1 
relay_log_purge=0
Copy the code

Restart the service

systemctl restart mysqld
Copy the code

The master library grants authorization to the slave library

mysql> grant replication slave on*. *to root@The '%' identified by 'password'; 
mysql> grant all privileges on*. *to root@The '%' identified by 'password'; 
mysql> flush privileges; Master_log_file ='mysql-bin.000007',master_log_pos=154 mysql>show master status;
Copy the code

Slave node

Modify the MySQL configuration file my. CNF of the slaves and set the server ids of the two slaves to 2 and 3 respectively

# bin_log configuration
log_bin=mysql-bin 
Ensure that each server ID does not have the same ID
server-id=2 
sync-binlog=1 
binlog-ignore-db=information_schema 
binlog-ignore-db=mysql 
binlog-ignore-db=performance_schema 
binlog-ignore-db=sys 
# relay_log configuration
relay_log=mysql-relay-bin 
log_slave_updates=1 
relay_log_purge=0 
read_only=1
Copy the code

Restart the service

systemctl restart mysqld
Copy the code

Open the synchronization

Log in to MySQL and run the MySQL command on the Slave node, for example, run the following command (note that the parameters are the same as those displayed in the show master status operation) :

change master to master_host='10.211.55.14',master_port=3306,master_user='root',master_password ='root123456',master_log_file='mysql-bin.000001',master_log_pos=120;

start slave; // Start synchronizationCopy the code

Configure semi-synchronous replication

The Master node

install plugin rpl_semi_sync_master soname 'semisync_master.so';
show variables like '%semi%';
Copy the code

Run the vi /etc/my. CNF command to modify the MySQL configuration file

# Enable semi-synchronous replication automatically
rpl_semi_sync_master_enabled=ON 
rpl_semi_sync_master_timeout=1000
Copy the code

Restarting the MySQL service

systemctl restart mysqld
Copy the code

Slave node

Perform the following steps for both Slave nodes.

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Copy the code

Run the vi /etc/my. CNF command to modify the MySQL configuration file

# Enable semi-synchronous replication automatically
rpl_semi_sync_slave_enabled=ON
Copy the code

Restart the service

systemctl restart mysqld
Copy the code

Test the semi-synchronization status

Check whether semi-synchronization is enabled by checking parameters in the MySQL command line interface.

show variables like '%semi%';
Copy the code

Then check the MySQL log again.

cat /var/log/mysqld.log
Copy the code

You can view semi-synchronization information in logs, for example:

Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000005, 154)
Copy the code

Create a table

Create the database and order tables by manipulating the main library

Create database test_db:

create database test_db;
Copy the code

Create order table c_order:

use test_db;

CREATE TABLE `c_order`(
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `is_del` bit(1) NOT NULL DEFAULT 0 COMMENT 'Deleted or not'.`user_id` int(11) NOT NULL COMMENT 'user id'.`company_id` int(11) NOT NULL COMMENT 'the company id'.`publish_user_id` int(11) NOT NULL COMMENT 'User ID of user B'.`position_id` int(11) NOT NULL COMMENT 'job ID'.`resume_type` int(2) NOT NULL DEFAULT 0 COMMENT 'Resume type: 0 Attachment 1 online'.`status` varchar(256) NOT NULL COMMENT 'Post status Post status WAIT- to be processed AUTO_FILTER- PREPARE_CONTACT- to be communicated REFUSE- ARRANGE_INTERVIEW- Notify interview'.`create_time` datetime NOT NULL COMMENT 'Creation time'.`update_time` datetime NOT NULL COMMENT 'Processing time',
 PRIMARY KEY (`id`),
 KEY `index_userId_positionId` (`user_id`.`position_id`),
 KEY `idx_userId_operateTime` (`user_id`.`update_time`))ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
Copy the code

The slave library is replicated and the order table created in the master library can be queried:

use test_db;
SHOW TABLES;
Copy the code

Set up the project

Create project Sharding-jDBC-demo

Source code address: github.com/sixj0/shard…


      
<project  xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.sixj</groupId>
    <artifactId>sharding-jdbc-demo</artifactId>
    <version>0.0.1 - the SNAPSHOT</version>
    <name>sharding-jdbc-demo</name>
    <description>Sharding-JDBC implementation of sub-database sub-table and read and write separation</description>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <spring-cloud.version>Dalston.SR1</spring-cloud.version>
        <spring-boot.version>2.2.5. RELEASE</spring-boot.version>
        <mysql-connector.version>5.1.48</mysql-connector.version>
        <shardingsphere.version>4.1.0</shardingsphere.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>${spring-boot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>${spring-boot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>${spring-boot.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-connector.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${shardingsphere.version}</version>
        </dependency>
    </dependencies>


    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <testSource>1.8</testSource>
                    <testTarget>1.8</testTarget>
                </configuration>
            </plugin>
        </plugins>
    </build>



</project>
Copy the code

Configuration information:

Data source information for six databases
spring.shardingsphere.datasource.names=master1,slave1,slave2,master2,slave3,slave4

spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1.jdbc-url=JDBC: mysql: / / 10.211.55.14:3306 / test_db? useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=root123456

spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=JDBC: mysql: / / 10.211.55.16:3306 / test_db? useSSL=false
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=root123456

spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave2.jdbc-url=JDBC: mysql: / / 10.211.55.17:3306 / test_db? useSSL=false
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=root123456

spring.shardingsphere.datasource.master2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master2.jdbc-url=JDBC: mysql: / / 10.211.55.15:3306 / test_db? useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master2.username=root
spring.shardingsphere.datasource.master2.password=root123456

spring.shardingsphere.datasource.slave3.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave3.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave3.jdbc-url=JDBC: mysql: / / 10.211.55.18:3306 / test_db? useSSL=false
spring.shardingsphere.datasource.slave3.username=root
spring.shardingsphere.datasource.slave3.password=root123456

spring.shardingsphere.datasource.slave4.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave4.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave4.jdbc-url=JDBC: mysql: / / 10.211.55.19:3306 / test_db? useSSL=false
spring.shardingsphere.datasource.slave4.username=root
spring.shardingsphere.datasource.slave4.password=root123456
#id uses the snowflake algorithm
spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE
#sharding-database-table shards c_order table based on user_id
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=master$->{user_id % 2 + 1}
spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=master$->{1.. 2}.c_order
Master-slave Implements read/write separation based on master1 and master2 clusters
spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave1,slave2
spring.shardingsphere.sharding.master-slave-rules.master2.master-data-source-name=master2
spring.shardingsphere.sharding.master-slave-rules.master2.slave-data-source-names=slave3,slave4
# Load balancing for multiple slave libraries
spring.shardingsphere.masterslave.load-balance-algorithm-type=ROUND_ROBIN
Print to execute SQL
spring.shardingsphere.props.sql.show=true
Copy the code

Entity class:

@Entity
@Table(name = "c_order")
public class COrder implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "is_del")
    private Boolean isDel;

    @Column(name = "user_id")
    private Integer userId;

    @Column(name = "company_id")
    private Integer companyId;

    @Column(name = "publish_user_id")
    private Integer publishUserId;

    @Column(name = "position_id")
    private Integer positionId;

    @Column(name = "resume_type")
    private Integer resumeType;

    @Column(name = "status")
    private String status;

    @Column(name = "create_time")
    private Date createTime;

    @Column(name = "update_time")
    private Date updateTime;
}
Copy the code

Order table Repository:

public interface COrderRepository extends JpaRepository<COrder.Long> {}Copy the code

The test class:

@SpringBootTest(classes = ShardingJdbcDemoApplication.class)
class ShardingJdbcDemoApplicationTests {

    @Autowired
    private COrderRepository cOrderRepository;
		
  	/** * generates 20 records */
    @Test
    public void testAdd(a) {
        for (int i = 100; i <120; i++) {
            COrder cOrder = new COrder();
            cOrder.setDel(false);
            cOrder.setUserId(i);
            cOrder.setCompanyId(new Random().nextInt(10));
            cOrder.setPublishUserId(new Random().nextInt(10));
            cOrder.setPositionId(new Random().nextInt(10));
            cOrder.setResumeType(new Random().nextInt(1));
            cOrder.setStatus("ARRANGE_INTERVIEW");
            cOrder.setCreateTime(new Date());
            cOrder.setUpdateTime(newDate()); cOrderRepository.saveAndFlush(cOrder); }}@Test
    public void testFind(a) { List<COrder> cOrderList = cOrderRepository.findAll(); cOrderList.forEach(cOrder -> { System.out.println(cOrder.toString()); }); }}Copy the code

Execution effect:

User_id in Master1 is even

The user_id in Master2 is odd

Read data from Master1’s slave library and Master2’s slave library when querying all records

Sharding-JDBC implementation of sub-library sub-table + read and write separation

MySQL environment setup

MySQL installation

MySQL primary/secondary configuration

The Master node

Run the vi /etc/my. CNF command to modify the Master configuration file

Restart the service

The master library grants authorization to the slave library

Slave node

Restart the service

Open the synchronization

Configure semi-synchronous replication

The Master node

Slave node

Test the semi-synchronization status

Create a table

Set up the project

Related Posts

Docker installed Redis

Automatic assembly practice, diy write a Springboot-starter

Performance pursuit: word long figure reveal 30 + 8 main server program thread model | NodeJS, Apache, Nginx, Netty, Redis, Tomcat, MySQL, Zuul