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 [mysqld] ## Set up server_id, be unique server-id=1 ## Enable binlog log-bin=mysql-bin ## binlog cache binlog_cache_size=1M ## binlog format (mixed, statement, row, default format is statement) binlog_format=mixed
-- slave [mysqld] ## Set up server_id, be unique server-id=2 ## Enable binlog for use when Slave is the Master of other Slave log-bin=mysql-slave-bin relay_log=edu-mysql-relay-bin ## If you need to synchronize functions or stored procedures log_bin_trust_function_creators=true ## binlog cache binlog_cache_size=1M ## binlog format (mixed, statement, row, default format is statement) binlog_format=mixed ## 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 slave_skip_errors=1062
3. Start mysql
3.1. Pull image
docker pull mysql:5.7
3.2. Create gateway
docker network create --driver bridge --subnet 172.18.0.0/16 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 172.18.0.11 --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 172.18.0.12 --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 FLUSH PRIVILEGES;
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='172.21.24.249', -- Intranet IP
master_port=13311, -- mysql External port
master_user='reader', -- master mysql user name
master_password='reader', -- slave mysql password
master_log_file='mysql-bin.000005',
master_log_pos=591;
-- 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_Host: 172.17.0.2
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`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4; -- 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"