基于MYSQL的主从同步和读写分离

目录

一.完成MySQL主从同步（一主两从）

 1.主库配置

2.建立同步账号

3.锁表设置只读

4.备份数据库数据

5.主库备份数据上传到从库

6.从库上还原备份

7.解锁

8.从库上设定主从同步

9.启动从库同步开关

10.检查状态

二.基于MySQL一主两从配置，使用Mycat2完成MySQL读写分离配置

1.在docker上启动一个3306的MySQL

2.配置Mycat物理库地址

3.启动MyCAT

4.使用可视化工具连接mycat  

​编辑5.创建数据源

6.创建集群

7.创建逻辑库

8.修改逻辑库的数据源

9.测试读写分离是否成功(在MyCAT里面测试)

---

一.完成MySQL主从同步（一主两从）

 1.主库配置

启用二进制日志
选择一个唯一的server-id

[root@master ~]# vim /etc/my.cnf
[mysqld]
log_bin = mysql-bin
server_id = 3

2.建立同步账号

mysql8.0 [(none)]>grant replication slave on *.* to rep@'%' identified by '030118';

3.锁表设置只读

为后面备份准备，注意生产环境要提前申请停机时间；

mysql> flush tables with read lock;

4.备份数据库数据

[root@master ~]# mysqldump -uroot -p030118 --set-gtid-purged=OFF -B chap03 friend school > /server/backup/db.sql

5.主库备份数据上传到从库

[root@master ~]# scp /server/backup/db.sql 192.168.140.134:/tmp
[root@master ~]# scp /server/backup/db.sql 192.168.140.135:/tmp

6.从库上还原备份

mysql -uroot -p030118 < /tmp/db.sql

7.解锁

mysql> unlock tables;

8.从库上设定主从同步

在主库上查看从库同步所需参数 

 两个从服务器上操作一样

mysql8.0 [(none)]>change master to-> master_host="192.168.140.133",-> master_user="rep",-> master_password="030118",-> master_log_file="mysql-bin.000006",-> master_log_pos=1412;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

9.启动从库同步开关

mysql> start slave;

10.检查状态

mysql> show slave status\G

看到两个yes代表同步成功

测试主从同步 
 主库

 从库

二.基于MySQL一主两从配置，使用Mycat2完成MySQL读写分离配置

1.在docker上启动一个3306的MySQL

[root@master ~]# docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 --lower_case_table_names=1[root@master ~]# docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
8bcbe566742f        mysql:5.7           "docker-entrypoint..."   7 seconds ago       Up 6 seconds        0.0.0.0:3306->3306/tcp, 33060/tcp   mysql

2.配置Mycat物理库地址

在启动之前我们要配置物理库的地址，要不然MyCAT启动就会报错。
配置文件位置: mycat/conf/datasources/prototypeDs.datasource.json

[root@master ~]# vim /data/mycat/conf/datasources/prototypeDs.datasource.json
{"dbType":"mysql","idleTimeout":60000,"initSqls":[],"initSqlsGetConnection":true,"instanceType":"READ_WRITE","maxCon":1000,"maxConnectTimeout":3000,"maxRetryCount":5,"minCon":1,"name":"prototypeDs","password":"123456","type":"JDBC","url":"jdbc:mysql://localhost:3306?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8","user":"root","weight":0
}

3.启动MyCAT

[root@master bin]# cd /data/mycat/bin/
[root@master bin]# ./mycat start
Starting mycat2...
[root@master bin]# ./mycat status
mycat2 is running (19357).

4.使用可视化工具连接mycat  

5.创建数据源

-----------------------------主机-----------------------------------
/*+ mycat:createDataSource{"dbType":"mysql","idleTimeout":60000,"initSqls":[],"initSqlsGetConnection":true,"instanceType":"READ_WRITE","logAbandoned":true,"maxCon":1000,"maxConnectTimeout":3000,"maxRetryCount":5,"minCon":1,"name":"m1","password":"030118","queryTimeout":0,"removeAbandoned":false,"removeAbandonedTimeoutSecond":180,"type":"JDBC","url":"jdbc:mysql://192.168.140.133:3306/db1?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true","user":"root","weight":0
}*/;
--------------------------从机-----------------------------------
/*+ mycat:createDataSource{"dbType":"mysql","idleTimeout":60000,"initSqls":[],"initSqlsGetConnection":true,"instanceType":"READ","logAbandoned":true,"maxCon":1000,"maxConnectTimeout":3000,"maxRetryCount":5,"minCon":1,"name":"m1s1","password":"030118","queryTimeout":0,"removeAbandoned":false,"removeAbandonedTimeoutSecond":180,"type":"JDBC","url":"jdbc:mysql://192.168.140.134:3306/db1?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true","user":"root","weight":0
}*/;
----------------------------从机----------------------------------------------
/*+ mycat:createDataSource{"dbType":"mysql","idleTimeout":60000,"initSqls":[],"initSqlsGetConnection":true,"instanceType":"READ","logAbandoned":true,"maxCon":1000,"maxConnectTimeout":3000,"maxRetryCount":5,"minCon":1,"name":"m1s1","password":"030118","queryTimeout":0,"removeAbandoned":false,"removeAbandonedTimeoutSecond":180,"type":"JDBC","url":"jdbc:mysql://192.168.140.135:3306/db1?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true","user":"root","weight":0
}*/;

6.创建集群

/*! mycat:createCluster{
"clusterType":"MASTER_SLAVE",
"heartbeat":{
"heartbeatTimeout":1000,
"maxRetry":3,
"minSwitchTimeInterval":300,
"slaveThreshold":0
},
"masters":[
"m1"
],
"maxCon":2000,
"name":"prototype",
"readBalanceType":"BALANCE_ALL",
"replicas":[
"m1s1","m1s2"
],
"switchType":"SWITCH"
} */;

7.创建逻辑库

CREATE DATABASE db1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

8.修改逻辑库的数据源

vim /data/mycat/conf/schemas/db1.schema.json

在里面添加 "targetName":"prototype",

[root@master mysqlms]# cat /data/mycat/conf/schemas/db1.schema.json
{
"customTables":{},
"globalTables":{},
"normalProcedures":{},
"normalTables":{},
"schemaName":"db1",
"shardingTables":{},
"targetName":"prototype",
"views":{}
}

9.测试读写分离是否成功(在MyCAT里面测试)

重启MyCAT：
[root@master mysqlms]# cd /data/mycat/bin/
[root@master bin]# ./mycat restart
在MyCAT里面创建一个sys_user表：
CREATE TABLE SYS_USER( ID BIGINT PRIMARY KEY, USERNAME VARCHAR(200) NOT NULL,
ADDRESS VARCHAR(500));
过注释生成物理库和物理表：
如果物理表不存在，在 MyCAT2 能正常启动的情况下，根据当前配置自动创建分片表，全局表和物理
表：
/*+ mycat:repairPhysicalTable{} */;
在MyCAT里面向sys_user表添加一条数据：
INSERT INTO SYS_USER(ID,USERNAME,ADDRESS) VALUES(1,"XIAOMING","WUHAN");
最后在m1,m1s1,m1s2上查看即可