MySQL主从复制原理
Mysql的主从复制中主要有三个线程：master（binlog dump thread）、slave（I/O thread 、SQL thread），Master一条线程和Slave中的两条线程。
master（binlog dump thread）主要负责Master库中有数据更新的时候，会按照binlog格式，将更新的事件类型写入到主库的binlog文件中。
并且，Master会创建log dump线程通知Slave主库中存在数据更新，这就是为什么主库的binlog日志一定要开启的原因。
I/O thread线程在Slave中创建，该线程用于请求Master，Master会返回binlog的名称以及当前数据更新的位置、binlog文件位置的副本。
然后，将binlog保存在 「relay log（中继日志）」 中，中继日志也是记录数据更新的信息。
SQL线程也是在Slave中创建的，当Slave检测到中继日志有更新，就会将更新的内容同步到Slave数据库中，这样就保证了主从的数据的同步。
MySQL主从环境搭建
设置server-id值并开启binlog参数开启gtid [mysqld] server_id=10 log_bin=master gtid_mode=on enforce_gtid_consistency=true
server_id 主从库不能重复
使用docker搭建
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 1224ecb4130b mysql:5.7 "docker-entrypoint..." 25 minutes ago Up 25 minutes 33060/tcp, 0.0.0.0:3309->3306/tcp mysql3 e8ac80097bd2 mysql:5.7 "docker-entrypoint..." 25 minutes ago Up 25 minutes 33060/tcp, 0.0.0.0:3308->3306/tcp mysql2 e9c72c63c9c2 mysql:5.7 "docker-entrypoint..." 25 minutes ago Up 25 minutes 33060/tcp, 0.0.0.0:3307->3306/tcp mysql1
docker 里面不好编辑 cp 到服务器操作
docker cp mysql1:/etc/mysql/conf.d/docker.cnf /root/mysqlcnf/
主库设置
在 /etc/mysql/conf.d/mysql1.cnf 添加
server_id=10 log_bin=master gtid_mode=on enforce_gtid_consistency=true
从库1配置
在/etc/mysql/conf.d/mysql2.cnf 添加
server_id=11 log_bin=slave gtid_mode=on enforce_gtid_consistency=true
从库2配置
在/etc/mysql/conf.d/mysql3.cnf 添加
server_id=12 log_bin=slave gtid_mode=on enforce_gtid_consistency=true
然后上传到各个docker中
[root@mysql mysqlcnf]# docker cp /root/mysqlcnf/mysql1.cnf mysql1:/etc/mysql/conf.d/docker.cnf [root@mysql mysqlcnf]# docker cp /root/mysqlcnf/mysql2.cnf mysql2:/etc/mysql/conf.d/docker.cnf [root@mysql mysqlcnf]# docker cp /root/mysqlcnf/mysql3.cnf mysql3:/etc/mysql/conf.d/docker.cnf # 重启 [root@mysql mysqlcnf]# docker restart mysql1 mysql2 mysql3 mysql1 mysql2 mysql3
主库配置
建立同步账号 mysql> grant replication slave on *.* to 'rep'@'192.168.1.%' identified by '123456';
从库配置
change master to master_host='192.168.1.50', master_user='repl', master_password='123456', master_auto_position=1;
验证
主库创建一个数据库
从库
主从配置成功
MySQL读写分离
一主两从
环境搭建
需要这三个文件
要有Java环境 mycat
-rw-r--r-- 1 root root 127431820 Mar 31 00:31 jdk-8u261-linux-x64.rpm -rw-r--r-- 1 root root 151819628 May 9 2022 mycat2-1.21-release-jar-with-dependencies.jar -rw-r--r-- 1 root root 1246974 May 9 2022 mycat2-install-template-1.21.zip
安装jdk
1、解压 mycat2-install-template-1.21.zip
2、将mycat2-1.21-release-jar-with-dependencies.jar 放在mycat 的lib中
3、给mycat下的bin目录赋可执行权限+x
4、在bin目录下启动mycat
1、unzip mycat2-install-template-1.21.zip -d /data/ 2、mv mycat2-1.21-release-jar-with-dependencies.jar /data/mycat/lib/ 3、chmod +x /data/mycat/bin/* 4、 [root@mysql bin]# ./mycat start Starting mycat2... mycat2 is already running. [root@mysql bin]# ./mycat status mycat2 is running (18162).
5、使用连接工具连接端口8066
6、主从配置 之前已经配好
7、添加主的数据源（可读可写）
{ "dbType":"mysql", "idleTimeout":60000, "initSqls":[], "initSqlsGetConnection":true, "instanceType":"READ_WRITE", "logAbandoned":true, "maxCon":1000, "maxConnectTimeout":3000, "maxRetryCount":5, "minCon":1, "name":"m1", "password":"123456", "queryTimeout":0, "removeAbandoned":false, "removeAbandonedTimeoutSecond":180, "type":"JDBC", "url":"jdbc:mysql://127.0.0.1:3306/db1?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true", "user":"root", "weight":0 }
8、添加两个从的数据源
{ "dbType":"mysql", "idleTimeout":60000, "initSqls":[], "initSqlsGetConnection":true, "instanceType":"READ", "logAbandoned":true, "maxCon":1000, "maxConnectTimeout":3000, "maxRetryCount":5, "minCon":1, "name":"m1s1", "password":"123456", "queryTimeout":0, "removeAbandoned":false, "removeAbandonedTimeoutSecond":180, "type":"JDBC", "url":"jdbc:mysql://127.0.0.1:3307/db1?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true", "user":"root", "weight":0 }
{ "dbType":"mysql", "idleTimeout":60000, "initSqls":[], "initSqlsGetConnection":true, "instanceType":"READ", "logAbandoned":true, "maxCon":1000, "maxConnectTimeout":3000, "maxRetryCount":5, "minCon":1, "name":"m1s2", "password":"123456", "queryTimeout":0, "removeAbandoned":false, "removeAbandonedTimeoutSecond":180, "type":"JDBC", "url":"jdbc:mysql://127.0.0.1:3308/db1?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true", "user":"root", "weight":0 }
9、查看
10、创建集群
{ "clusterType":"MASTER_SLAVE", "heartbeat":{ "heartbeatTimeout":1000, "maxRetryCount":0, "minSwitchTimeInterval":300, "showLog":true, "slaveThreshold":0.0 }, "masters":[ "m1" ], "maxCon":2000, "name":"prototype", "readBalanceType":"BALANCE_ALL", "replicas":[ "m1s1", "m1s2" ], "switchType":"SWITCH" }
11、创建逻辑库
CREATE DATABASE db1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
12、修改逻辑库的数据源
{ "customTables":{}, "globalTables":{}, "normalProcedures":{}, "normalTables":{}, "schemaName":"db1", "shardingTables":{}, "targetName":"prototype", "views":{} }
13、测试读写分离是否成功
use db1 CREATE TABLE SYS_USER( ID BIGINT PRIMARY KEY, USERNAME VARCHAR(200) NOT NULL, ADDRESS VARCHAR(500)); INSERT INTO SYS_USER(ID,USERNAME,ADDRESS) VALUES(1,"XIAOMING","WUHAN");
