MySql教程

MySQL 运维

本文主要是介绍MySQL 运维,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

1 日志

1.1 错误日志

错误日志是MySQL中最重要的日志之一,它记录了当mysqld启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时,建议首先查看此日志。

该日志是默认开启的,默认存放目录/var/log/,默认的日志文件名为mysqld.log。查看日志位置:

# 查看日志位置
show variables like '%log_error%';
# 查看日志最后50条
tail -50 /var/log/mysql.log

image-20220418231756714

1.2 二进制日志

二进制日志(BINLOG)记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句,但不包括数据查询(SELECT、SHOW)语句。

作用:

  • 灾难时的数据恢复
  • MySQL的主从复制

在MySQL8版本中,默认二进制日志是开启着的,涉及到的参数如下:

show variables like '%log_bin%';

image-20220418231646818

日志格式

MySQL服务器中提供了多种格式来记录二进制日志,具体格式及特点如下:

日志格式 含义
STATEMENT 基于SQL语句的日志记录,记录的是SQL语句,对数据进行修改的SQL都会记录在日志文件中。
ROW 基于行的日志记录,记录的是每一行的数据变更。(默认)
MIXED 混合了STATEMENT和ROW两种格式,默认采用STATEMENT,在某些特殊情况下会自动切换为ROW进行记录。
show variables like '%binlog_format%';

image-20220418232903411

日志查看

由于日志是以二进制方式存储的,不能直接读取,需要通过二进制日志查询工具mysqlbinlog来查看,具体语法:

mysqlbinlog[参数选项] logfilename

参数选项:
指定数据库名称,只列出指定的数据库相关操作。
-d
忽略掉日志中的前n行命令。
-o
将行事件(数据变更)重构为SQL语句
-v
将行事件(数据变更)重构为SQL语句,并输出注释信息
-w

image-20220418235419536

在mysql8.0/data目录下

mysqlbinlog binlog.000003
# update 语句,直接查看比较乱 使用 -v
mysqlbinlog -v binlog.000003
image-20220418235627663

上图中,where 是更新之前,set 是更新之后

修改binlog_format为 STATEMENT

打开 my.cnf

# 添加
binlog_format = STATEMENT
# STATEMENT 不需要加 -v
mysqlbinlog binlog.000003

image-20220419000421419

  • select 不会记录在日志中
  • 建表语句会被记录

日志清理

对于比较繁忙的业务系统,每天生成的binlog数据巨大,长时间不清除,会占用大量磁盘空间。可以通过以下几种方式清理日志:

指令 含义
reset master 删除全部binlog日志,删除之后,日志编号,将从binlog.000001重新开始
purge master logs to 'binlog.* * * * *' 删除* * * * * *编号之前的所有日志
purge master logs before 'yyyy-mm-dd hh24:mi:ss' 删除日志为“yyyy-mm-dd hh24:mi:ss"之前产生的所有日志
# 删除 000002 号之前的日志
purge master logs to 'binlog.000002';

# 重置
reset master;

也可以在mysql的配置文件my.cnf中配置二进制日志的过期时间、设置了之后,二进制日志过期会自动删除。

show variables like '%binlog_expire_logs_seconds%';
# 结果如下 默认存放 2592000s,即 30 天 

image-20220419001507850

1.3 查询日志

查询日志中记录了客户端的所有操作语句,而二进制日志不包含查询数据的SQL语句。默认情况下,查询日志是未开启的。如果需要开启查询日志,可以设置以下配置:

show variables like '%general%';

image-20220419003016411

my.cnf

# querylog
general_log = on
general_log_file=/usr/local/mysql8.0/data/mysql-query.log

image-20220419003540742

开启查询日志,内存占用比较大,没必要还是保持关闭。

1.4 慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time 设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志,默认未开启。long_query_time默认为10秒,最小为0,精度可以到微秒

#慢查询日志
slow_query_log=1
#执行时间参数
long_query_time=2

查询参数配置

show variables like '%slow_query%';

image-20220419082424117

配置my.cnf

# slow_query_log

slow_query_log=1        # on
long_query_time=2       # 2s
slow_query_log_file=/usr/local/mysql8.0/data/mysql-slow.log

查看日志

tail mysql-slow.log

image-20220419090049588

默认情况下,不会记录管理语句,也不会记录不使用索引进行查找的查询

可以使用log_slow_admin_statements和更改此行为log_queries_not_using_indexes,如下所述。

# my.cnf

#记录执行较慢的管理语句
log_slow_admin_statements =1
#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes =1

通过查询慢日志,定位处执行较慢的sql语句进行优化

2 主从复制

2.1 概述

主从复制是指将主数据库DDL和DML操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步

MySQL支持一台主库同时向多台从库进行复制,从库同时也可以作为其他从服务器的主库,实现链状复制。

image-20220419092626248

MySQL复制的优点主要包含以下三个方面:

  • 主库出现问题,可以快速切换到从库提供服务。
  • 实现读写分离,降低主库的访问压力。(增删改在主库,查询在从库)
  • 可以在从库中执行备份,以避免备份期间影响主库服务。

2.2 原理

image-20220419093217179

从上图来看,复制分成三步:

  • Master主库在事务提交时,会把数据变更记录在二进制日志文件Binlog
  • slave从库读取主库的二进制日志文件Binlog,写入到从库的中继日志Relay Log
  • slave重做中继日志中的事件,将改变反映它自己的数据

2.3 搭建

  • 准备好两台服务器之后,在上述的两台服务器中分别安装好MySQL,并完成基础的初始化准备工作。

    # 开放指定的3306端口号:
    firewall-cmd --zone=public --add-port=3306/tcp -permanent
    firewall-cmd –reload
    
    
    #-------------------------------------------#
    
    # 查看firewall服务状态
    systemctl status firewalld
    # 查看firewall的状态
    firewall-cmd --state
    
    # 关闭服务器的防火墙:
    systemctl stop firewalld
    # 关闭服务器的防火墙开机自启:
    systemctl disable firewalld
    
    
    
    # 补充 windows 关闭防火墙
    win + r : cmd
    # 检查防火墙状态
    Netsh Advfirewall show allprofiles
    # 关闭防火墙 off 后有一个空格
    NetSh Advfirewall set allprofiles state off 
    
  • 检查mysql 运行状态

    systemctl status mysqld 
    
  • 主库配置

    • 修改配置文件 my.cnf

      #mysql服务ID,保证整个集群环境中唯一,取值范围:1~ 2^32-1,默认为1
      server-id=1
      #是否只读,1代表只读,0代表读写
      read-only=0
      
      #忽略的数据,指不需要同步的数据库
      #binlog-ignore-db=mysql
      
      #指定同步的数据库
      #binlog-do-db=db01
      
    • 重启 mysql 服务

    • 登录mysql,创建远程连接的账号,并授予主从复制权限

      # 创建itcast用户,并设置密码,该用户可在任意主机连接该MySQL服务
      CREATE USER 'kite'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123';
      # 为 'kite'@'%' 用户分配主从复制权限
      GRANT REPLICATION SLAVE ON *.* TO 'kite'@'%';
      
    • 通过指令,查看二进制日志坐标

      show master status;
      
      # 字段含义说明:
      file:从哪个日志文件开始推送日志文件
      position:从哪个位置开始推送日志
      binlog_ignore_db:指定不需要同步的数据库
      

      image-20220419104027294

  • 从库配置

    • 修改配置文件

      #mysql服务ID,保证整个集群环境中唯一,取值范围:1-232-1,和主库不一样即可
      server-id=2
      #是否只读,1代表只读,0代表读写
      read-only=1
      # 上述只读是针对普通用户的,如果要让超级管理员也是只读,可设置
      super-read-only=1
      
    • 重启 mysql 服务

    • 登录mysql,设置主库配置

      CHANGE REPLICATION SOURCE TO SOURCE_HOST='101.201.100.130', SOURCE_USER='kite', SOURCE_PASSWORD='Root@123', SOURCE_LOG_FILE='binlog.000001', SOURCE_LOG_POS=156;
      
      # 上述是8.0.23中的语法。如果mysql是8.0.23之前的版本,执行如下SQL:
      CHANGE MASTER TO MASTER_HOST='101.201.100.130', MASTER_USER='kite', MASTER_PASSWORD='Root@123', MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=156;
      
    • 开启同步操作,查看主从同步状态

      # 8.0.22 之后
      start replica
      # 8.0.22 之前
      start slave
      
      # 关闭之前开启的主从复制线程
      stop slave IO_THREAD;
      # ---------------------------- #
      
      #查看主从同步状态
      #8.0.22 之后
      show replica status
      show replica status\G
      #8.0.22 之前
      show slave status
      show slave status\G
      
    • 查看是否成功,主要看 replica_IO_Runningreplica_SQL_Running

      image-20220419104646646

  • 进行测试

主从复制,这是从当前开始的,之前的数据如果需要同步,可以将主库数据导出,从库进行导入

3 分库分表

3.1 介绍

随着互联网及移动互联网的发展,应用系统的数据量也是成指数式增长,若采用单数据库进行数据存储,存在以下性能瓶颈:

  • I0瓶颈:热点数据太多,数据库缓存不定,产生大量磁盘I0,效率较低。请求数据太多,带宽不够,网络I0瓶颈。
  • CPU瓶颈:排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源,请求数太多,CPU出现瓶颈。

image-20220419150855830

分库分表的中心思想都是将数据分散存储,使得单一数据库/表的数据量变小来缓解单一数据库的性能问题,从而达到提升数据库性能的目的。

image-20220419151058661

拆分策略

垂直拆分

垂直分库:以表为依据,根据业务将不同表拆分到不同库中。

  • 每个库的表结构都不一样。
  • 每个库的数据也不一样。
  • 所有库的并集是全量数据。

image-20220419151432212

垂直分表:以字段为依据,根据字段属性将不同字段拆分到不同表中。

  • 每个表的结构都不一样。
  • 每个表的数据也不一样,一般通过一列(主键/外键)关联。
  • 所有表的并集是全量数据。

image-20220419151449269

水平拆分

水平分库:以字段为依据,按照一定策略,将一个库的数据拆分到多个库中。

  • 每个库的表结构都一样。
  • 每个库的数据都不一样。
  • 所有库的并集是全量数据。

image-20220419151855282

水平分表:以字段为依据,按照一定策略,将一表的数据拆分到多个表中。

  • 每个表的表结构都一样。
  • 每个表的数据都不一样。
  • 所有表的并集是全量数据。

实现技术

shardingJDBC:基于AOP原理,在应用程序中对本地执行的SQL进行拦截,解析、改写、路由处理。需要自行编码配置实现,只支持java语言,性能较高。

MyCat:数据库分库分表中间件,不用调整代码即可实现分库分表,支持多种语言,性能不及前者。

image-20220419152636145 image-20220419152710852

3.2 Mycat 概述

Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。可以像使用mysql一样来使用mycat,对于开发人员来说根本感觉不到mycat的存在。

优势:

  • 性能可靠稳定
  • 强大的技术团队
  • 体系完善
  • 社区活跃

安装 jdk 和 mycat

####安装 jdk

# 解压
tar -zxvf jdk-8u144-linux-x64.tar.gz -C /usr/local
# 配置环境变量
vim /etc/profile 【加入以下内容】
export JAVA_HOME=/usr/local/jdk1.8.0_221
export JRE_HOME=${JAVA_HOME}/jre
export CLASSPATH=.:${JAVA_HOME}/lib:${JRE_HOME}/lib
export PATH=${JAVA_HOME}/bin:$PATH
# 激活配置
source /etc/profile 
# 检验
java -version
##### 安装 mycat
tar -zxvf Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz -C /usr/local

# 更换 /usr/local/mycat/lib/mysql-connector-java-5.1.35.jar 为新版本
# 为新版本授权
chmod 777 mysql-connector-java-8.0.22.jar



mycat 安装后的目录

  • bin:存放可执行文件,用于启动停止mycat
  • conf:存放mycat的配置文件
  • lib:存放mycat的项目依赖包(jar)
  • logs:存放mycat的日志文件

image-20220419164419775

3.3 Mycat 入门

由于tb_order表中数据量很大,磁盘lo及容量都到达了瓶颈,现在需要对tb_order表进行数据分片,分为三个数据节点,每一个节点主机位于不同的服务器上,具体的结构,参考下图:

image-20220419164603047

首次登录

关闭防火墙

# Linux
systemctl stop firewalld
systemctl start firewalld

# windows
NetSh Advfirewall set allprofiles state off 
NetSh Advfirewall set allprofiles state on 

准备三台服务器(我只准备了两台,外加一台本地mysql)

分片配置

进入到 ../mycat/conf 目录下

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">
	<schema name="DB01" checkSQLschema="false" sqlMaxLimit="100">
		<table name="travelrecord,address" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
	</schema>
	<dataNode name="dn1" dataHost="dhost1" database="db01" />
	<dataNode name="dn2" dataHost="dhost2" database="db01" />
	<dataNode name="dn3" dataHost="dhost3" database="db01" />

	<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
	   	<writeHost host="M1" url="jdbc:mysql://101.201.100.130:3306?" user="root" password="ll546546"/>
	</dataHost>
	<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="M1" url="jdbc:mysql://59.110.217.19:3306?" user="root" password="ll546546"/>
	</dataHost>
	<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
    	<heartbeat>select user()</heartbeat>
    	<writeHost host="M1" 
                   url=
    "jdbc:mysql://127.0.0.1:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" 
                   user="root" password="ll546546"/>
        </dataHost>
</mycat:schema>

server.xml

<!-- 只展示了改动的部分 -->
<user name="root" defaultAccount="true">
    <property name="password">ll546546</property>
    <property name="schemas">DB01</property>

    <!-- 表级 DML 权限设置 -->
    <!-- 		
  <privileges check="false">
   <schema name="DB01" dml="0110" >
    <table name="tb01" dml="0000"></table>
    <table name="tb02" dml="1111"></table>
   </schema>
  </privileges>		
   -->
</user>

<user name="user">
    <property name="password">ll546546</property>
    <property name="schemas">DB01</property>
    <property name="readOnly">true</property>
</user>
# 如果使用Windows 对 schema.xml 和 server.xml 进行修改,要记得重新对两个文件进行许可
chmod 777 schema.xml
chmod 777 server.xml

启动 mycat

切换到 bin 目录下

bin/mycat start
bin/mycat stop
bin/mycat console

image-20220419172759313

可以查看是否成功

tail -f logs/wrapper.log

mysql 连接mycat

# mycat 端口号 8066 
mysql -h 101.201.100.130 -P 8066 -uroot -p
# mycat 管理端口号 9066 
# linux 重置密码等级
set global validate_password.policy = 0;
set global validate_password.length = 4;

3.4 Mycat 配置

三个核心配置文件

  • schema.xml
  • server.xml
  • rule.xml
<!-- schema.xml 中 -->
rule="auto-sharding-long"
分片规则使用给出的规则,在conf/auto-sharding-long.txt 中,给三台服务器分别2000000的数据(如下图)

image-20220419214228220

schema.xml

schema.xml作为MyCat中最重要的配置文件之一,涵盖了MyCat的逻辑库、逻辑表、分片规则、分片节点及数据源的配置。

主要包含以下三组标签:

  • schema标签

  • datanode标签

  • datahost标签

schema标签

<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100" >
	<table name="TB_ ORDER" dataNode="dnl,dn2,dn3" rule="auto-sharding-long" />
</schemas>

schema标签用于定义MyCat实例中的逻辑库,一个MyCat实例中,可以有多个逻辑库,可以通过schema标签来划分不同的逻辑库。MyCat中的逻辑库的概念,等同于MySQL中的database概念,需要操作某个逻辑库下的表时,也需要切换逻辑库(use xxx)。

  • name:指定自定义的逻辑库库名(区分大小写)
  • checkSQLschema:在SQL语句操作时指定了数据库名称,执行时是否自动去除;true:自动去除,false:不自动去除
  • sqlMaxLimit:如果未指定limit进行查询,列表查询模式查询多少条记录
table标签

table标签定义了MyCat中逻辑库schema下的逻辑表,所有需要拆分的表都需要在table标签中定义。

  • name:定义逻辑表表名,在该逻辑库下唯一

  • dataNode:定义逻辑表所属的dataNode,该属性需要与dataNode标签中name对应;多个dataNode逗号分隔

  • rule:分片规则的名字,分片规则名字是在rule.xml中定义的

  • primaryKey:逻辑表对应真实表的主键

  • type:逻辑表的类型,目前逻辑表只有全局表和普通表,如果未配置,就是普通表;全局表,配置为global

dataNode标签

<dataNode name="dn1" dataHost="dhost1" database="db01" />
<dataNode name="dn2" dataHost="dhost2" database="db01" />
<dataNode name="dn3" dataHost="dhost3" database="db01" />

dataNode标签中定义了MyCat中的数据节点,也就是我们通常说的数据分片。一个dataNode标签就是一个独立的数据分片。

核心属性:

  • name:定义数据节点名称
  • dataHost:数据库实例主机名称,引用自dataHost标签中name属性
  • database:定义分片所属数据库

dataHost标签

该标签在MyCat逻辑库中作为底层标签存在,直接定义了具体的数据库实例、读写分离、心跳语句。

<datalost name="dhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysq1"dbDriver="jdbc">
	<heartbeat>select user()</heartbeat>
	<writeHost host="master" url="jdbe:mysql://101.201.100.130:3306?useSSL=falsefamp:serverTimezonesAsia/Shanghaifamp:characterEncoding=utf8",user="root" password="1234">
	</writeHosts>
</dataHost>

核心属性:

  • name:唯一标识,供上层标签使用

  • maxCon/minCon:最大连接数/最小连接数

  • balance:负载均衡策略,取值0,1,2,3

  • writeType:写操作分发方式

    • 0:写操作转发到第一个writeHost,第一个挂了,切换到第二个;
    • 1:写操作随机分发到配置的writeHost
  • dbDriver: 数据库驱动,支持native、jdbc

宏观查看schema.xmlimage-20220419220351968

rule.xml

rule.xml中定义所有拆分表的规则,在使用过程中可以灵活的使用分片算法,或者对同一个分片算法使用不同的参数,它让分片过程可配置化。主要包含两类标签:tableRule、Function

image-20220420170221397

server.xml

server.xml配置文件包含了MyCat的系统配置信息,主要有两个重要的标签:system、user。

system

<system>
    <property name="nonePasswordLogin">0</property>
    <property name="useHandshakeV10">1</property>
    <property name="usesqlStat">1</property>
</system>

user

image-20220420170920552

<!-- 表级 DML 权限设置 -->
<privileges check="TRUE">					<!-- 是否开启DML权限检查,默认为false  -->
    <schema name="TESTDB" dml="0110" >				<!-- 配置指定逻辑库的权限  -->
        <table name="tb01" dml="0000"></table>		<!-- 配置指定逻辑表的权限,就近原则  -->
        <table name="tb02" dml="1111"></table>	<!-- 配置指定逻辑表的权限,就近原则  -->
    </schema>				<!-- 0000 对应 增改查删 -->
</privileges>

3.5 Mycat 分片 !

垂直分库

在业务系统中,涉及以下表结构,但是由于用户与订单每天都会产生大量的数据,单台服务器的数据存储及处理能力是有限的,可以对数据库表进行拆分,原有的数据库表如下

image-20220419221832714

<!-- schema.xml -->

<schema name="SHOPPING" checkSQLschema="true" sqlMaxLimit="100" >
    <table name="tb_goods_base" dataNode="dnl" primaryKey="id"/>
    <table name="tb_goods_brand" dataNode="dnl" primaryKey="id"/>
    <table name="tb_goods_cat" dataNode="dnl" primaryKey="id"/>
    <table name="tb_goods_desa" dataNode="dnl" prilarykey="goods_id"/>
    <table name="tb_goods_item" dataNode="dnl" primaryKey="id"/>
    <table name="tb_order_item" dataNode="dn2" primaryKey="id"/>
    <table name="tb_order_master" dataNode="dn2" primaryKey="order_id"/>
    <table name="tb_order_pay log" dataNode="dn2" primaryKey="out_trade_no"/>
    <table name="tb_user" dataNode="dn3" primaryKey="id"/>
    <table name="tb_user_addresa" dataNode="dn3" primaryKey="id"/>
    <table name="th_areas provinces" dataNode="dn3" primaryKey="id"/>
    <table name="tb_areas_city" dataNode="dn3" primaryKey="id"/>
    <table name="tb_areas_region" dataNode="dn3" primaryKey="id"/>
	<!-- 垂直分表的时候才会涉及rule ,此处为分库不涉及 -->
</schema>

<dataNode name="dnl" dataHost="dhostl" database="shopping" />
<dataNode name="dn2" dataHost="dhost2" database="shopping" />
<dataNode name="dn3" dataHost="dhost3" database="shopping" />

全局表配置

对于省、市、区/县表 tb_areas_provinces, tb_areas_city, tb_areas_region是属于数据

字典表,在多个业务模块中都可能会遇到,可以将其设置为全局表 type="global",利于业务操作。

<table name="th_areas provinces" dataNode="dn3" primaryKey="id" type="global" />
    <table name="tb_areas_city" dataNode="dn3" primaryKey="id" type="global" />
    <table name="tb_areas_region" dataNode="dn3" primaryKey="id" type="global" />

水平分库

在业务系统中,有一张表(日志表),业务系统每天都会产生大量的日志数据,单台服务器的数据存储及处理能力是有限的,可以对数据库表进行拆分rule = "mod-long"

image-20220420183700475

分片规则

范围

根据指定的字段及其配置的范围与数据节点的对应情况,来决定该数据属于哪一个分片。

image-20220420184414237

取模

根据指定的字段值与节点数量进行求模运算,根据运算结果,来决定该数据属于哪一个分片。

image-20220420184635801

一致性hash

所谓一致性哈希,相同的哈希因子计算值总是被划分到相同的分区表中,不会因为分区节点的增加而改变原来数据的分区位置。

image-20220420185010385

image-20220420185041683

枚举

通过在配置文件中配置可能的枚举值,指定数据分布到不同数据节点上,本规则适用于按照省份、性别、状态拆分数据等业务。

image-20220420185503107

应用指定

运行阶段由应用自主决定路由到那个分片,直接根据字符子串(必须是数字)计算分片号。

image-20220420190330042

固定分片hash算法

该算法类似于十进制的求模运算,但是为二进制的操作,例如,取id的二进制第10位与1111111111进行位&运算。

特点:

  • 如果是求模,连续的值,分别分配到各个不同的分片;但是此算法会将连续的值可能分配到相同的分片,降低事务处理的难度。
  • 可以均匀分配,也可以非均匀分配。
  • 分片字段必须为数字类型。

image-20220420191727265

<!-- rule.xml -->
	<tableRule name="sharding-by-longhash">
		<rule>
			<columns>id</columns>
			<algorithm>sharding-by-long-hash</algorithm>
		</rule>
	</tableRule>

	<function name="sharding-by-long-hash" 
			class="io.mycat.route.function.PartitionByLong">
		<property name="partitionCount">2,1</property>
		<property name="partitionLength">256,512</property>
		<!-- 2,1 和 256, 512 代表 2个256, 1个512 -->
	</function>

字符串hash解析

截取字符串中的指定位置的子字符串,进行hash算法,算出分片。

image-20220420193942480

按(天)日期分片

image-20220420194619970

分片规则-自然月

使用场景为按照月份来分片,每个自然月为一个分片

image-20220420195337200

3.6 Mycat 管理及监控

mycat原理

image-20220420200243552

image-20220420200455240

Mycat管理

Mycat默认开通2个端口,可以在server.xml中进行修改

  • 8066数据访问端口,即进行DML和DDL操作
  • 9066数据库管理端口,即mycat服务管理控制功能,用于管理mycat的整个集群状态
mysql -h 192.168.200.210 -p 9066 -uroot -p123456
命令 含义
show @@help 查看Mycat管理工具帮助文档
show @@version 查看Mycat的版本
reload @@config 重新加载Mycat的配置文件
show @@datasource 查看Mycat的数据源信息
show @@datanode 查看MyCat现有的分片节点信息
show @@threadpool 查看Mycat的线程池信息
show @@sql 查看执行的SQL
show @@sql.sum 查看执行的SQL统计

show @@datasourceshow @@datanodereload @@config

Mycat-eye

Mycat-web(Mycat-eye)是对mycat-server提供监控服务,功能不局限于对mycat-server使用。他通过JDBc连接对Mycat、Mysql监控,监控远程服务器(目前仅限于linux系统)的cpu、内存、网络、磁盘。

Mycat-eye运行过程中需要依赖zookeeper,因此需要先安装zookeeper。

image-20220420202448498

4 读写分离

读写分离,简单地说是把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操作,从数据库提供读操作,这样能有效地减轻单台数据库的压力。

通过MyCat即可轻易实现上述功能,不仅可以支持MySQL,也可以支持Oracle和SQL Server。

image-20220419233855821

一主一从准备

MySQL的主从复制,是基于二进制日志(binlog)实现的

image-20220420203312118

一主一从读写分离

首先搭建好主从复制的两台服务器

MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控制

image-20220420205215613

image-20220420205247227

参数值 含义
0 N开启读写分离机制,所有读操作都发送到当前可用的writeHost上
1 全部的readHost与备用的writeHost都参与select语句的负载均衡(主要针对于双主双从模式)
2 所有的读写操作都随机在writeHost,readHost上分发
3 所有的读请求随机分发到writeHost对应的readHost上执行,writeHost不负担读压力

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="ITCAST_RW" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="dhost1" database="test_13_rw" />

        <dataHost name="dhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="master" url="jdbc:mysql://101.201.100.130:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" 
                           user="root"  password="ll546546">
                        <readHost host="slave" url="jdbc:mysql://59.110.217.19:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" 
                                   user="root"  password="ll546546">
                        </readHost>
                </writeHost>    
        </dataHost>
</mycat:schema> 

server.xml

<user name="root" defaultAccount="true">
    <property name="password">ll546546</property>
    <property name="schemas">ITCAST_RW</property>
    <property name="defaultSchema">ITCAST_RW</property>
    <!--No MyCAT Database selected 错误前会尝试使用该schema作为schema,不设置则为null,报错 -->
    <!-- 表级 DML 权限设置 -->
    <!--            
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>           
                 -->
</user>
<user name="user">
    <property name="password">ll546546</property>
    <property name="schemas">ITCAST_RW</property>

主库 关闭mysql 服务以后,依然可以进行查询操作,其他操作不行。

双主双从介绍

一个主机Master1用于处理所有写请求,它的从机Slave1和另一台主机Master2还有它的从机Slave2负责所有读请求。当Master1主机宕机后,Master2主机负责写请求,Master1、Master2互为备机。架构图如下:

image-20220420213921034

双主双从搭建

image-20220420214309297

  • 修改配置文件
#mysql服务ID,保证整个集群环境中唯一,取值范围:1-232-1,默认为1
server-id=1		# 1,2,3,4
#指定同步的数据库
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03
#在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates
  • 重启 mysql 服务器

  • 两台主库创建账户并授权

    创建itcast用户,并设置密码,该用户可在任意主机连接该MySOL服务
    CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
    #为'itcast'@'%'用户分配主从复制权限
    GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%1;
    
    • 通过指令,查看两台主库的二进制日志坐标
    show master status ;
    
  • 从库配置(Slave1-192.168.200.212)

    • 修改配置文件/etc/my.cnf

      mysql服务ID,保证整个集群环境中唯一,取值范围:1-232-1,默认为1
      server-id=2
      
    • 重启MySQL服务器

      systemctl restart mysqld
      
  • 两台从库配置关联的主库

    CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx', MASTER_USER='xxx', MASTER_PASSWORD='xxx',
    MASTER_LOG_FILE='xxx',MASTER_LOG_POS=xxx;
    
    • 需要注意slave1对应的是master1,slave2对应的是master2

    • 启动两台从库主从复制,查看从库状态

      start slave;
      show slave status\G
      
  • 两台主库相互复制

    • Master2复制Master1,Master1复制Master2

      CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx', MASTER_USER='XX', MASTER_PASSWORD='XX',
      MASTER_LOG_FILE='xxx',MASTER_LOG_POS=XxX;
      
    • 启动两台从库主从复制,查看从库状态

      start slave;
      show slave status\G
      

双主双从读写分离

image-20220420220210328

  • balance="1"
    • 代表全部的readHost与stand by writeHost参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与M2互为主备),正常情况下,M2,S1,S2都参与select 语句的负载均衡;
  • writgType
    • 0:写操作都转发到第1台writeHost,writeHost1挂了,会切换到writeHost2上;
    • 1:所有的写操作都随机地发送到配置的writeHost上;
  • switchType(writeHost1挂了,切换到writeHost2时)
    • -1:不自动切换
    • 1:自动切换

schema.xml

<dataHost name="dhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="master1" url="jdbc:mysql://101.201.100.130:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" 
               user="root"  password="ll546546">
        <readHost host="slave" url="jdbc:mysql://59.110.217.19:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" 
                  user="root"  password="ll546546">
        </readHost>
    </writeHost>  
    <writeHost host="master2" url="jdbc:mysql://101.201.100.???:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" 
               user="root"  password="ll546546">
        <readHost host="slave2" url="jdbc:mysql://59.110.217.?:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" 
                  user="root"  password="ll546546">
        </readHost>
    </writeHost>  
</dataHost>
  • 测试
    • 登录MyCat,测试查询及更新操作,判定是否能够进行读写分离,以及读写分离的策略是否正确。
    • 当主库挂掉一个之后,是否能够自动切换。
这篇关于MySQL 运维的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!