搭建MySQL集群一主一从

1.修改master的配置文件/etc/my.cnf

1
2
3
4
5
6
7
8
9
10
11
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个), 入系统库等
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库，如果有多个数据库，可重复此参数，每个数据库一行
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT

2.重启服务

3.在主服务上授权slave

1
2
3
4
5
6
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '授权密码，下面用到';
#可授权给具体ip 'slave'@'ip'

#mysql8用下面
CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '授权密码，下面用到';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';

4.查询master状态（记下File与Position值，主机不再操作）

1
show master status;

image-20210207162725895

5.修改slave的配置文件

1
2
3
4
#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay

6.重启从机mysql

7.从机执行复制主机命令

1
2
3
4
5
CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave', # 用户名，与上面一致
MASTER_PASSWORD='密码', #与上面一致
MASTER_PORT=3306, #主机端口
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=653;(pisition的值)

8.启动从服务器复制功能

1
2
3
4
#之前有开启的话要先停止
stop slave;

start slave;

9.查看从服务器状态

1
2
3
4
show slave status\G; # \G:将查询到的横向表格纵向输出，方便阅读
#Slave_IO_Running: Yes
#Slave_SQL_Running: Yes
#说明配置成功

image-20210207164123829

10.测试是否同步

注：数据库必须是主配置文件中binlog-do-db配置的数据库才会同步

image-20210207170933867

搭建双主双从

主机master1配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates 
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2 
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1

主机master2配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#主服务器唯一ID
server-id=3 #启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates 

#防止插入时自增主键与master1冲突
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2 
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=2

slave1配置

1
2
3
4
#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay

slave2配置

1
2
3
4
#从服务器唯一ID
server-id=4 
#启用中继日志
relay-log=mysql-relay

接下来操作与上面一主一从差不多，就是两个master也要互相复制对方

Mycat配置

！注：Mycat1.6连接mysql8时需修改mysql的加密策略

1
2
3
4
5
6
-- 修改密码为用不过期
ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; 
-- 修改密码并指定加密规则为mysql_native_password
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
-- 刷新权限
flush privileges;

配置文件

schema.xml：定义逻辑库，表、分片节点等内容。

rule.xml：定义分片相关规则。

server.xml：定义用户以及系统相关变量，如端口等。

schema.xml写MySQL连接信息

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
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
	</schema>
	<!--database为mysql的数据库名 -->
	<dataNode name="dn1" dataHost="host1" database="testMycat" />
	<dataHost name="host1" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="主机1IP:端口" user="账号"
				   password="密码">
			<!-- can have multi read hosts -->
			<readHost host="hostS2" url="从机1IP:端口" user="账号" password="密码" />
		</writeHost>
		<writeHost host="hostS1" url="主机2IP:端口" user="账号"
				   password="密码">
			<readHost host="hostS3" url="从机2IP:端口" user="账号" password="密码" />
		</writeHost>
	</dataHost>
</mycat:schema>

#balance="1": 全部的readHost与stand by writeHost参与select语句的负载均衡。
#balance配置负载均衡类型：
#"0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
#"1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均#衡。
#balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。
#balance="3"，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力

#writeType="0": 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个
#writeType="1"，所有写操作都随机的发送到配置的 writeHost，1.5 以后废弃不推荐
#writeHost，重新启动后以切换后的为准，切换记录在配置文件中:dnindex.properties 。 #switchType="1": 1 默认值，自动切换。
# -1 表示不自动切换
# 2 基于 MySQL 主从同步的状态决定是否切换。

server.xml里添加Mycat登陆用户信息

1
2
3
4
<user name="my用户名">
		<property name="mycat密码">密码</property>
		<property name="schemas">TESTDB</property>
</user>

在bin，目录下启动Mycat

./mycat console ： 控制台运行

./mycat start ： 后台运行

（图中可知，Mycat是用Java写的）

image-20210207200845130

连接

1
2
3
4
#与mysql连接一样
#8066是数据连接端口，即应用程序连此端口
#9066是Mycat管理端口
mysql -u[上面server配置的用户名] -p[密码] -P [端口] -h[ip]

创建表并测试

1
2
3
create table user(id int, name varchar(11),address varchar(11));
insert into user values(1,'张三',@@hostname);
#不同mysql的主机名不同@@hostname

image-20210207211246172

数据分片

垂直拆分（分库分表）

指按照业务将表进行分类，分布到不同的数据库上面，有紧密关联关系的表应该在一个库里，相互没有关联关系的表可以分到不同的库里。将数据或者说压力分担到不同的库上面。（不同库中表不同）

水平拆分（分表）

按照某个字段的某种规则来分散到多个库之中，不同库的表中包含一部分数据。（不同库拥有相同表）

主从数据同步原理

学习链接

学习链接2

END