MySQL 运维

1 日志

1.1 错误日志

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

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

# 查看日志位置
show variables like '%log_error%';

# 查看日志最后50条
tail -50 /var/log/mysql.log

1.2 二进制日志

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

作用：

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

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

show variables like '%log_bin%';

日志格式

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

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

show variables like '%binlog_format%';

日志查看

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

mysqlbinlog[参数选项] logfilename

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

在mysql8.0/data目录下

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

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

修改binlog_format为 STATEMENT

打开 my.cnf

# 添加
binlog_format = STATEMENT

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

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 天

1.3 查询日志

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

show variables like '%general%';

my.cnf

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

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

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%';

配置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

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

可以使用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支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。

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

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

2.2 原理

从上图来看，复制分成三步：

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:指定不需要同步的数据库

从库配置

修改配置文件

#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_Running 和 replica_SQL_Running

进行测试

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

3 分库分表

3.1 介绍

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

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

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

拆分策略

垂直拆分

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

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

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

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

水平拆分

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

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

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

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

实现技术

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

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

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的日志文件

3.3 Mycat 入门

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

首次登录

关闭防火墙

# 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

可以查看是否成功

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的数据(如下图)

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.xml

rule.xml

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

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

<!-- 表级 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 分片！

垂直分库

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

<!-- 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"

分片规则

范围

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

取模

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

一致性hash

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

枚举

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

应用指定

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

固定分片hash算法

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

特点：

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

<!-- 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算法，算出分片。

按（天）日期分片

分片规则-自然月

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

3.6 Mycat 管理及监控

mycat原理

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 @@datasource 、show @@datanode 、reload @@config

Mycat-eye

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

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

4 读写分离

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

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

一主一从准备

MySQL的主从复制，是基于二进制日志（binlog）实现的

一主一从读写分离

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

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

参数值	含义
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互为备机。架构图如下：

双主双从搭建

修改配置文件

#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
```

双主双从读写分离

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，测试查询及更新操作，判定是否能够进行读写分离，以及读写分离的策略是否正确。
- 当主库挂掉一个之后，是否能够自动切换。

posted @ 2022-04-23 11:22 Kite_Lee 阅读(116) 评论(0) 编辑收藏举报

刷新页面返回顶部

MySQL 运维

1 日志

1.1 错误日志

1.2 二进制日志

日志格式

日志查看

修改binlog_format为 STATEMENT

日志清理

1.3 查询日志

1.4 慢查询日志

2 主从复制

2.1 概述

2.2 原理

2.3 搭建

3 分库分表

3.1 介绍

拆分策略

垂直拆分

水平拆分

实现技术

3.2 Mycat 概述

安装 jdk 和 mycat

3.3 Mycat 入门

首次登录

3.4 Mycat 配置

schema.xml

schema标签

table标签

dataNode标签

dataHost标签

rule.xml

server.xml

system

user

3.5 Mycat 分片 ！

垂直分库

全局表配置

水平分库

分片规则

范围

取模

一致性hash

枚举

应用指定

固定分片hash算法

字符串hash解析

按（天）日期分片

分片规则-自然月

3.6 Mycat 管理及监控

mycat原理

Mycat管理

Mycat-eye

4 读写分离

一主一从准备

一主一从读写分离

双主双从介绍

双主双从搭建

双主双从读写分离

3.5 Mycat 分片！