MySQL SpringBoot

MySQL主从复制读写分离

SpringBoot整合多数据源实现读写分离

Posted by Deng Jia on 2021-09-26

安装多个MySQL服务

相关资源：

https://snailclimb.gitee.io/javaguide/#/?id=%e8%af%bb%e5%86%99%e5%88%86%e7%a6%bbamp%e5%88%86%e5%ba%93%e5%88%86%e8%a1%a8

https://shardingsphere.apache.org/document/legacy/3.x/document/cn/manual/sharding-jdbc/usage/read-write-splitting/

参考：https://blog.csdn.net/qq_33101675/article/details/114396476

采用解压版安装第二个MySQL服务：https://downloads.mysql.com/archives/community/

注意两个MySQL不要安装在同一路径下，

自建my.ini文件（端口另起）：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
[mysqld]
# 设置服务端端口
port=3307
# 设置mysql的安装目录
basedir=F:\dev\MySQL_slave\mysql-8.0.23-winx64-slave
# 设置mysql数据库的存放目录
datadir=F:\dev\MySQL_slave\mysql-8.0.23-winx64-slave\data
# 允许最大连接数
max_connections=100
# 允许连接失败的次数
max_connect_errors=10
# 服务端使用的字符集默认为utf8mb4
character-set-server=UTF8MB4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password

[mysql]
# 设置mysql客户端默认字符集
default-character-set=UTF8MB4

[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3307
# 设置客户端默认使用的字符集
default-character-set=UTF8MB4

管理员打开cmd，进入bin，

初始化mysql：

1
2
3
4
5
F:\dev\MySQL_slave\mysql-8.0.23-winx64-slave\bin>mysqld --defaults-file=F:\dev\MySQL_slave\mysql-8.0.23-winx64-slave\my.ini --initialize --console
2021-09-26T12:48:34.244070Z 0 [System] [MY-013169] [Server] F:\dev\MySQL_slave\mysql-8.0.23-winx64-slave\bin\mysqld.exe (mysqld 8.0.23) initializing of server in progress as process 34396
2021-09-26T12:48:34.256488Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-09-26T12:48:34.828093Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-09-26T12:48:35.998369Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: aF2v!6i0sjLC

记录密码：aF2v!6i0sjLC

初始化mysql服务：

1
2
F:\dev\MySQL_slave\mysql-8.0.23-winx64-slave\bin>mysqld install MySQL_slave --defaults-file="F:\dev\MySQL_slave\mysql-8.0.23-winx64-slave\my.ini"
Service successfully installed.

启动服务：

1
2
3
F:\dev\MySQL_slave\mysql-8.0.23-winx64-slave\bin>net start MySQL_slave
MySQL_slave 服务正在启动 .
MySQL_slave 服务已经启动成功。

使用navicat建立连接，使用到初始密码，会自动提示改密。

最终：一主一从。

MySQL主从复制

MySQL-master

my.ini配置：

1
2
3
4
5
6
7
8
9
[mysqld]
# 主从复制的数据库的名字
binlog-do-db=t_ms
# 忽略mysql数据库
binlog-ignore-db=mysql
# 开启二进制日志
log-bin=mysql-bin
# 设置server-id 一般取IP最后一段
server-id=1

重启mysql

为从服务器创建账号、授权、刷新权限、查看master状态：

1
2
3
4
5
6
7
8
CREATE
USER 'dj' @'127.0.0.1' IDENTIFIED BY '123456';
GRANT REPLICATION
SLAVE ON *.* TO 'dj' @'127.0.0.1';
FLUSH
PRIVILEGES;
SHOW
MASTER STATUS;
File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set
mysql-bin.000001 156

MySQL-slave

my.ini：

1
2
3
[mysqld]
# 设置server-id，必须唯一
server-id=2

重启服务

执行同步语句：

1
2
3
4
5
6
CHANGE
MASTER TO MASTER_HOST = '127.0.0.1',
MASTER_USER = 'dj',
MASTER_PASSWORD = '123456',
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 156;

启动/停止slave同步进程、查看slave同步状态：

1
2
3
4
START
SLAVE;STOP
SLAVE;SHOW
SLAVE STATUS;
  • Slave_IO_Running: Yes
  • Slave_SQL_Running: Yes

在主库新添一张表，insert、update、delete数据，看看从表；

然后从表新增数据，看看主表。

主从复制完成。

MySQL-DDL

库：db_ms

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
/*
 Navicat Premium Data Transfer

 Source Server         : local_mysql
 Source Server Type    : MySQL
 Source Server Version : 80023
 Source Host           : localhost:3306
 Source Schema         : db_ms

 Target Server Type    : MySQL
 Target Server Version : 80023
 File Encoding         : 65001

 Date: 29/09/2021 12:01:26
*/

SET NAMES utf8mb4;
SET
FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`
(
    `DEPTNO` int NOT NULL,
    `DNAME`  varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    `LOC`    varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    PRIMARY KEY (`DEPTNO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept`
VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept`
VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO `dept`
VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `dept`
VALUES (40, 'OPERATIONS', 'BOSTON');

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`
(
    `EMPNO`    int NOT NULL,
    `ENAME`    varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    `JOB`      varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    `MGR`      int NULL DEFAULT NULL,
    `HIREDATE` date NULL DEFAULT NULL,
    `SAL`      double(7, 2
) NULL DEFAULT NULL,
  `COMM` double(7, 2) NULL DEFAULT NULL,
  `DEPTNO` int NULL DEFAULT NULL,
  PRIMARY KEY (`EMPNO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp`
VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20);
INSERT INTO `emp`
VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);
INSERT INTO `emp`
VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);
INSERT INTO `emp`
VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20);
INSERT INTO `emp`
VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);
INSERT INTO `emp`
VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30);
INSERT INTO `emp`
VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10);
INSERT INTO `emp`
VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, NULL, 20);
INSERT INTO `emp`
VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10);
INSERT INTO `emp`
VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30);
INSERT INTO `emp`
VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, NULL, 20);
INSERT INTO `emp`
VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30);
INSERT INTO `emp`
VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20);
INSERT INTO `emp`
VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);

-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade`
(
    `GRADE` int NULL DEFAULT NULL,
    `LOSAL` int NULL DEFAULT NULL,
    `HISAL` int NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of salgrade
-- ----------------------------
INSERT INTO `salgrade`
VALUES (1, 700, 1200);
INSERT INTO `salgrade`
VALUES (2, 1201, 1400);
INSERT INTO `salgrade`
VALUES (3, 1401, 2000);
INSERT INTO `salgrade`
VALUES (4, 2001, 3000);
INSERT INTO `salgrade`
VALUES (5, 3001, 9999);

-- ----------------------------
-- Table structure for t_val
-- ----------------------------
DROP TABLE IF EXISTS `t_val`;
CREATE TABLE `t_val`
(
    `t_id`    int UNSIGNED NOT NULL AUTO_INCREMENT,
    `t_value` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_val
-- ----------------------------
INSERT INTO `t_val`
VALUES (1, 'value');
INSERT INTO `t_val`
VALUES (2, '主库再插入一条数据后，再看看从库。');
INSERT INTO `t_val`
VALUES (3, '233311');
INSERT INTO `t_val`
VALUES (4, 'value不为blank');

SET
FOREIGN_KEY_CHECKS = 1;

读写分离源码

gitee：https://gitee.com/DENG-JIAAA/read_write_splitting

FEATURED TAGS
MySQL SpringBoot
FRIENDS