人人尽说江南好,游人只合江南老。
春水碧于天,画船听雨眠。
MySQL主从复制
mysql主从复制:分摊读写压力(cpu计算压力)
写交给主库,读有主从分摊处理(原因是写操作较少,读操作较多)
1、主从复制原理图
2、搭建主库mysql_master
1.主库准备工作
1.拉取mysql5.7镜像
docker pull mysql:5.7
2.准备主库配置
# 创建主需要的配置目录 mkdir -p /etc/mysql/master mkdir -p /etc/mysql/master/conf mkdir -p /etc/mysql/master/data# 给文件夹设置所有权限!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! chmod -R 777 /etc/mysql
# 创建主需要的配置文件my.cnf vim /etc/mysql/master/conf/my.cnf
my.cnf内容如下:
[mysqld] # 集群环境该节点的唯一的id值 server-id=1 # 主从复制:主有写操作时 主会记录写日志到该日志文件中 log-bin=master-bin # 设置不要复制的数据库 binlog-ignore-db=mysql binlog-ignore-db=mydb # 设置需要复制的数据库 主会将该库的写操作记录到log-bin日志文件中 binlog-do-db=guli_oms # 字符编码 character_set_server=utf8 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid default-time_zone='+8:00' # logbin日志文件的格式:statement->记录写sql(批量操作主从效率高,@@hostname之类系统变量获取主从执行时不一致) ,row->记录写后的数据(和前面相反) binlog_format=STATEMENT
3.创建mysql_master容器
docker run -d -p 3316:3306 \ --name mysql_master \ --privileged=true \ --restart=always \ -v /etc/mysql/master/data:/var/lib/mysql:rw \ -v /etc/mysql/master/conf/my.cnf:/etc/mysql/my.cnf:rw \ -e MYSQL_ROOT_PASSWORD=123456 \ mysql:5.7
4.远程连接主机
使用sqlyog分别连接docker启动的mysql主库:注意修改端口号,Master对外映射的端口号是3316
2.binlog日志三种格式
1.statement
statement->记录写sql(批量操作主从效率高,@@hostname之类系统变量获取主从执行时不一致)
2.row
row->记录写后的数据
3.mixed
可以根据有没有函数自动切换前俩种格式,但是它也识别不了系统变量,主机名字不一样就会造成主从不一致
-- "@@"首先标记会话系统变量,如果会话变量不存在,则标记全局系统变量 SELECT @@hostname;
系统变量 规范:以“@@”开头
启动MySQL服务,生成MySQL实例期间,MySQL将为MySQL服务器内存中的系统变量赋值。 这些系统变量定义了当前MySQL服务实例的属性、特征。
3.查看主mysql的状态
这个时候,要是你也能出的来效果就最好了,出不来也没事。。因为我们很多人都出不来,拷别人虚拟机的。。。
-- 查看主mysql的状态 SHOW MASTER STATUS
接入点:从机从接入点开始复制。之前主机发生的任何事情跟我从机无关,我只从接入这个点开始复制
3、搭建从库mysql_slave
搭建从库准备工作
1.准备从库配置
# 创建从需要的配置目录 mkdir -p /etc/mysql/slave/conf mkdir -p /etc/mysql/slave/data# 给文件夹设置所有权限!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! chmod -R 777 /etc/mysql
这里要再重写设置权限,不然sqlyog登录不上去。
# 创建从需要的配置文件my.cnf vim /etc/mysql/slave/conf/my.cnf
my.cnf内容如下:
[mysqld] server-id=2 relay-log=relay-bin character_set_server=utf8 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid default-time_zone='+8:00'
2.创建mysql_slave容器
docker run -d -p 3326:3306 \ --name mysql_slave \ --restart=always \ -v /etc/mysql/slave/data:/var/lib/mysql:rw \ -v /etc/mysql/slave/conf/my.cnf:/etc/mysql/my.cnf:rw \ -e MYSQL_ROOT_PASSWORD=123456 \ mysql:5.7
3.远程连接主机
Slave对外映射的端口号是3326
4、建立主从关系
mysql用户可以分为普通用户和root用户,root用户是超级管理员,拥有所有权限。包括创建用户、删除用户、修改用户密码等管理权限;普通用户只拥有被授予的各种权限
1.主库创建slave账户
# 创建slave用户 CREATE USER 'slave01'@'%'; # 设置密码 ALTER USER 'slave01'@'%' IDENTIFIED BY '123456'; # 授权 GRANT REPLICATION SLAVE ON *.* TO 'slave01'@'%'; flush privileges;
在user表中
----------------------------------------------
2.从库配置主从关系
如果从机中搭建过主从,要重新设置主从
stop slave;
reset master
从库中执行sql
CHANGE MASTER TO MASTER_HOST = '192.168.2.108',MASTER_PORT = 3316,MASTER_USER = 'slave01',MASTER_PASSWORD = '123456',MASTER_LOG_FILE = 'master-bin.000004',MASTER_LOG_POS = 13988;
master_port:主库的ip地址
master_port:主库的端口
master_user:用户名
master_password:密码
master_log_file:主库查询的file项对应的值
master_log_pos:主库查询的的值
-- 查看主mysql的状态 SHOW MASTER STATUS
---------------------
3.开启主从复制
#启动slave同步
START SLAVE;
#查看同步状态
SHOW SLAVE STATUS;
主从搭建成功,如下:
------------------
4.测试主从
主库中创建guli_oms,从库也会自动的跟着创建该数据库。创建别的库,从库不会跟的,因为主库的my.conf配置过。。SHOW MASTER STATUS也看到过
在主库中的guli_oms中创建表,从库也会跟着。达到主从复制的目的
MyCat读写分离
通过MyCat和MySQL的主从复制配合搭建数据库的读写分离,实现MySQL的高可用性。
java项目连接数据库连接mycat,sql交给mycat接收,由它根据配置好的参数,判断读写应该交给哪个数据库来处理。
大体就是下面这个样子
<writeHost host="hostM1" url="jdbc:mysql://192.168.1.171:3316" user="root"password="123456"><readHost host="hostS1" url="jdbc:mysql://192.168.1.171:3326" user="root" password="123456" /> </writeHost>
1、安装MyCat
把压缩包ftp上传到/opt目录下并解压(压缩包我也是老师的资料中给的。。。)
tar -zxvf /opt/Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gz
解压后的mycat/conf目录下存放的是它的配置文件:
其中核心的配置有3个:
server.xml:配置MyCat作为虚拟数据库的信息(地址、数据库名、用户名、密码等信息)mycat启动时初始化的配置
schemal.xml:mycat代理的真实数据库的信息,实现读写分离
mycat通过逻辑库 将真实数据库表加载到该逻辑库中 展示给连接mycat的用户
rule.xml:分库分表规则配置
2、MyCat初步认识
重要:mycat本身不存储数据,只是在中间做一些业务逻辑
1、mycat是我们的程序与mysql数据库之间的桥梁,我们的请求不再直接到达数据库,而是到达mycat(解耦)
2、mycat中需要配置好主库从库关系,mycat根据配置决定读和写的去向。我们的Jdbc连接是连接mycat,程序代码没有任何变化。
3、mycat基本原理就是拦截转发
它拦截用户发送过来的SQL语句,对SQL做了一些特定的分析后,将此SQL发送到真实的数据库。
3、MyCat常用命令
进入 mycat/bin目录:cd /opt/mycat/bin
启动: ./mycat start
控制台启动: ./mycat console
停止: ./mycat stop
重启: ./mycat restart
状态: ./mycat status
日志文件:mycat/logs/wrapper.log
4、schema.xml 配置读写分离
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1"><table name="oms_order,oms_order_item,oms_order_operate_history,oms_order_return_apply,oms_order_return_reason,oms_order_setting,oms_refund_info" dataNode="dn1" />
</schema>
<dataNode name="dn1" dataHost="localhost1" database="guli_oms" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="2"writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="hostM1" url="jdbc:mysql://192.168.2.108:3316" user="root"password="123456"><readHost host="hostS1" url="jdbc:mysql://192.168.2.108:3326" user="root" password="123456" /></writeHost>
</dataHost>
writeHost和readHost分别指定写和读,balance=0的话 代表不使用读写分离
启动mycat
启动失败的话,查看
tail -30 /opt/mycat/logs/wrapper.log
测试mycat读写分离
1.sqlyog连接mycat
2.插入SQL,再读取
INSERT INTO oms_order_operate_history VALUES('2' , '10011' , '9528' , NOW() ,0 , @@hostname);SELECT * FROM oms_order_operate_history;
balance=2的情况下,很不幸我他喵的都是从主库中读的
修改一下schema.xml 中balance=4。重启mycat
很遗憾,还是没有读写分离。。。
分库分表
ali开发手册
1、分库分表准备工作
1.在mysql_mycat库中删除上面的测试数据。
DELETE FROM oms_order_operate_history;
2.创建新的容器mysql_master2
# 创建主2需要的配置目录 mkdir /etc/mysql/master2 mkdir /etc/mysql/master2/conf mkdir /etc/mysql/master2/data# 设置权限 chmod -R 777 /etc/mysql/# 创建主需要的配置文件my.cnf vim /etc/mysql/master2/conf/my.cnf
my.cnf内容如下:
[mysqld] server-id=3 log-bin=master-bin # 设置不要复制的数据库 binlog-ignore-db=mysql # 设置需要复制的数据库 binlog-do-db=guli_oms character_set_server=utf8 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid default-time_zone='+8:00' binlog_format=STATEMENT
创建容器mysql_master2启动:
docker run -d -p 3336:3306 \ --name mysql_master2 \ --privileged=true \ --restart=always \ -v /etc/mysql/master2/data:/var/lib/mysql:rw \ -v /etc/mysql/master2/conf/my.cnf:/etc/mysql/my.cnf:rw \ -e MYSQL_ROOT_PASSWORD=123456 \ mysql:5.7
sqlyog连接
在该数据库中导入guli_oms整个库
注意:3316/3326/3336 三个mysql的guli_oms库中的表都必须是空的
2、数据分片
数据分片是指通过某种特定的条件,将我们存放在同一个数据库中的数据分散存放到多个数据库(主机)上面,以达到分散单台设备负载的效果。
数据的切分(Sharding)根据其切分规则的类型,可以分为两种切分模式:
1.垂直(纵向)切分:是按照不同的表来切分到不同的数据库(主机)之上
将原本一个库中的多张表存到多个不同的数据库(主机),将一个库的读写压力分摊到多个库中,分布式每个微服务只连接自己的数据库,已经实现。
2.水平(横向)切分:是根据表中的数据的逻辑关系,将同一个表中的数据按照某种条件拆分到多台数据库(主机)的多张表中,这些多张表结构一样,多张表数据组合起来代表所有的数据。
分表可以减少单表数据量,查询时提高速度
逻辑库:在mycat中的数据库,但是真实物理不存在。
将多个库中的表合并到逻辑库中,项目中只要连接mycat就可以操作所有表
逻辑表:逻辑库中包含多个逻辑表,逻辑表可以映射mysql真实存在的物理表,逻辑表和物理表可 以一多一,也可以一对多(数据分片)
将拆分的多张表数据合并到一张逻辑表中,增删改查(指定条件查询)时,mycat可以根据rule规则快速判断到哪张表中操作数据
3、mycat配置分表
1.在rule.xml中配置分表规则
<tableRule name="oms_order_rule"><rule><columns>user_id</columns><algorithm>mod-long</algorithm></rule>
</tableRule><function name="mod-long" class="io.mycat.route.function.PartitionByMod"><!-- how many data nodes --><property name="count">2</property>
</function>
2.在schema.xml中新增配置
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1"><!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置--><!--fetchStoreNodeByJdbc 启用ER表使用JDBC方式获取DataNode--><table name="oms_order_return_reason,oms_order_setting,oms_refund_info" dataNode="dn1" /> <table name="oms_order" dataNode="dn1,dn2" fetchStoreNodeByJdbc="true" rule="oms_order_rule" ></table>
</schema><dataNode name="dn2" dataHost="localhost2" database="guli_oms" /><dataHost name="localhost2" maxCon="1000" minCon="10" balance="1"writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><!-- can have multi write hosts --> <writeHost host="hostM2" url="jdbc:mysql://192.168.1.171:3336" user="root"password="123456"></writeHost><!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
重启mycat,测试分库分表
INSERT INTO oms_order(id,user_id,order_sn,create_time,username)
VALUES('1' , '1001' ,'1' , NOW() , 'zhangsan');
INSERT INTO oms_order(id,user_id,order_sn,create_time,username)
VALUES('2' , '1002' ,'2' , NOW() , 'zhaosi');
INSERT INTO oms_order(id,user_id,order_sn,create_time,username)
VALUES('3' , '1003' ,'3' , NOW() , 'wangwu');
INSERT INTO oms_order(id,user_id,order_sn,create_time,username)
VALUES('4' , '1004' ,'4' , NOW() , 'qianliu');INSERT INTO oms_order_item(id,order_id,spu_name)
VALUES('1' ,'2' , '小米手机1');
INSERT INTO oms_order_item(id,order_id,spu_name)
VALUES('2' ,'4' , '小米手机2');
INSERT INTO oms_order_item(id,order_id,spu_name)
VALUES('3' ,'1' , '小米手机3');
INSERT INTO oms_order_item(id,order_id,spu_name)
VALUES('4' ,'3' , '小米手机4');
测试联查
SELECT * FROM oms_order t1 LEFT JOIN oms_order_item t2 ON t1.id = t2.order_id;
4、跨库join问题
两个数据库再同一台主机上可以做关联查询;两张表在不同的主机上是不能做join关联查询的
解决思路:子表的记录与所关联的父表记录存放在同一个数据分片上
在schema.xml这解决的,设置了ER表,ER表的分片字段就是那个外键“order_id”
<table name="oms_order" dataNode="dn1,dn2" fetchStoreNodeByJdbc="true" rule="oms_order_rule" ><!--name: 定义子表的表名;primaryKey:子表的主键;joinKey:与父表建立关联关系的列名;parentKey>:父表的主键名--><childTable name="oms_order_item" primaryKey="id" joinKey="order_id" parentKey="id" /><childTable name="oms_order_return_apply" primaryKey="id" joinKey="order_id" parentKey="id" /><childTable name="oms_order_operate_history" primaryKey="id" joinKey="order_id" parentKey="id" /></table>
ER表:指父子表中的子表,该表依赖于别的另外一张表。比如订单详情表(子表)和订单表 (父表)