MySQL主从复制读写分离
安装多个MySQL服务
相关资源:
https://snailclimb.gitee.io/javaguide/#/?id=读写分离amp分库分表
参考:https://blog.csdn.net/qq_33101675/article/details/114396476
采用解压版安装第二个MySQL服务:https://downloads.mysql.com/archives/community/
注意两个MySQL不要安装在同一路径下,
自建my.ini文件(端口另起):
[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:
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服务:
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.
启动服务:
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配置:
[mysqld]
# 主从复制的数据库的名字
binlog-do-db=t_ms
# 忽略mysql数据库
binlog-ignore-db=mysql
# 开启二进制日志
log-bin=mysql-bin
# 设置server-id 一般取IP最后一段
server-id=1
重启mysql
为从服务器创建账号、授权、刷新权限、查看master状态:
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:
[mysqld]
# 设置server-id,必须唯一
server-id=2
重启服务
执行同步语句:
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同步状态:
START SLAVE;STOP SLAVE;SHOW SLAVE STATUS;
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
在主库新添一张表,insert、update、delete数据,看看从表;
然后从表新增数据,看看主表。
主从复制完成。
MySQL-DDL
库:db_ms
/*
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;
读写分离源码
DJOSIMON