Springboot sharding JDBC integrated environment preparation

Keywords: Java MySQL

Only when you know your age and try your best to do well what you can do at this age can you have the freedom of the next age

1, Overview  

Shardingsphere Jdbc is positioned as a lightweight Java framework and provides additional services in the Jdbc layer of Java. It uses the client to connect directly to the database and provides services in the form of jar package. It can be understood as an enhanced version of Jdbc driver, which is fully compatible with Jdbc and various ORM frameworks.

Document address: https://shardingsphere.apache.org/document/current/cn/overview/

2, MySQL master-slave replication principle  

A long connection is maintained between slave Library B and master library a. There is a thread inside main library a, which is dedicated to serving the long connection from library B. The complete process of a transaction log synchronization is as follows:

  • On slave database B, set the IP, port, user name and password of master database A through the change master command, and where to start requesting binlog. This location contains the file name and log offset.
  • Execute the start slave command on slave library B. at this time, the slave library will start two threads, I/O thread and SQL thread in the figure. The I/O thread is responsible for establishing a connection with the main library.
  • After verifying the user name and password, main database A starts to read binlog locally and send it to database B according to the location transmitted from database B.
  • After getting the binlog from library B, it is written to the local file, which is called the relay log.
  • The SQL thread reads the relay log, parses the commands in the log, and executes them.

Due to the introduction of multi-threaded replication scheme, SQL thread has evolved into multiple threads. Master-slave replication is not synchronous in full real time, but asynchronous in real time. There is an execution delay between the master and slave services. If the pressure on the master server is great, it may lead to a large delay between the master and slave servers.  

3, Environment preparation (dock configuration Mysql master-slave)

1. Create the corresponding directory of mysql host  

-- master mysql Corresponding directory
mkdir -p /home/mysql/sharding-mysql/master-mysql/cnf
mkdir -p /home/mysql/sharding-mysql/master-mysql/data
-- slave mysql Corresponding directory
mkdir -p /home/mysql/sharding-mysql/slave-mysql/cnf
mkdir -p /home/mysql/sharding-mysql/slave-mysql/data

2. Create mysql.cnf configuration file

-- master mysql,cnf
vim /home/mysql/sharding-mysql/master-mysql/cnf/mysql.cnf
-- slave mysql.cnf
vim /home/mysql/sharding-mysql/slave-mysql/cnf/mysql.cnf

The complete configuration file is as follows:

-- master
## Set up server_id, be unique
## Enable binlog
## binlog cache
## binlog format (mixed, statement, row, default format is statement)
-- slave
## Set up server_id, be unique
## Enable binlog for use when Slave is the Master of other Slave
## If you need to synchronize functions or stored procedures
## binlog cache
## binlog format (mixed, statement, row, default format is statement)
## Skip all errors encountered in master-slave replication or specified types of errors to avoid the interruption of slave side replication
## For example, the 1062 error refers to the duplication of some primary keys, and the 1032 error is due to the inconsistency between the primary and secondary database data

3. Start mysql

3.1. Pull image

docker pull mysql:5.7

3.2. Create gateway

docker network create --driver bridge --subnet self_network

3.3. Startup

-- start-up master master server
docker create -p 13311:3306 \ -- Mapping port
--network self_network \ -- Set gateway
--network-alias master-mysql01 \ -- Gateway alias
--ip --name master-mysql01 \ --ip Fixed Intranet Ip address
-v /home/mysql/sharding-mysql/master-mysql/cnf:/etc/mysql/conf.d \
-v /home/mysql/sharding-mysql/master-mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456  mysql:5.7
-- start-up slave from server
docker create -p 13312:3306 --network self_network 
--network-alias slaver-mysql01  
--ip --name slaver-mysql01 
-v /home/mysql/sharding-mysql/slave-mysql/cnf:/etc/mysql/conf.d 
-v /home/mysql/sharding-mysql/slave-mysql/data:/var/lib/mysql 
-e MYSQL_ROOT_PASSWORD=123456   mysql:5.7
-- Start in sequence docker container(create replace with run It can be started directly)
docker start container ID

3.4. Add a user reader that replicates master data for use from the server

-- docker ps Container information found
-- Enter container
docker exec -it container ID/Container name /bin/bash
-- Sign in mysql
mysql -u root -p123456
-- Create user
GRANT REPLICATION SLAVE ON *.* to 'reader'@'%' identified by 'reader';
-- Create action take effect

3.5. Configure slave link master

① Log in to mysql from the master server to view the master_log_file,master_log_pos two parameters

-- Sign in mysql
mysql -u root -p123456
mysql> show master status;
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| mysql-bin.000003 |      591 |              |                  |                   |
1 row in set (0.00 sec)
-- If docker establish mysql Container not set IP Address can be queried
docker inspect --format='{{.NetworkSettings.IPAddress}}' Container name

② Set the connection information of the master server from the server

-- Sign in mysql
mysql -u root -p123456
-- Execute command setting information
change master to 
master_host='', -- Intranet IP
master_port=13311, -- mysql External port
master_user='reader', -- master mysql user name
master_password='reader', -- slave mysql password
-- start-up slave mysql
start slave;
-- query slave information
show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_User: reader
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 591
               Relay_Log_File: edu-mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
Slave_IO_Running: Yes,Slave_SQL_Running: Yes It means that the startup is successful.

4, Verify

1.Dbeaver link master-slave mysql

Dbeaver download address: https://dbeaver.io/download/

-- Create corresponding database
CREATE DATABASE `sharding-jdbc-db`
-- new table
-- `sharding-jdbc-db`.t_user definition
CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `nickname` varchar(100) DEFAULT 'JueDream',
  `password` varchar(100) DEFAULT '123456',
  `sex` int(11) DEFAULT '1',
  `age` int(11) DEFAULT '1',
  `birthday` varchar(50) DEFAULT '1997-12-19',
  PRIMARY KEY (`id`)
-- Query the existing corresponding table from the library

✨ "It should be that God knocked over the Milky way and the stars fell to the earth. Only when the water is sparkling and your eyes are vast"  

Posted by victor on Wed, 17 Nov 2021 17:43:43 -0800