MySQL运维篇

MySQL日志

错误日志

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

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

show variables like '%log_error%';

二进制日志

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

作用：

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

在MySQL8中，默认二进制日志是开启的，查看日志参数：

show variables like '%log_bin%';

参数说明：

• log_bin_basename：当前数据库服务器的binlog日志的基础名称(前缀)，具体的binlog文件名需要在该basename的基础上加上编号(编号从000001开始)
• log_bin_index：binlog的索引文件，记录了当前服务器关联的binlog文件有哪些

格式

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

可以使用命令查看格式：

show variables like '%binlog_format%';

如果需要配置二进制日志的格式，要在/etc/my.cnf中配置binlog_format参数

查看

由于日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具mysqlbinlog来查看，详情查看进阶篇中此部分

删除

对于比较繁忙的业务系统，每天生成的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之前产生的所有日志

也可以在mysql的配置文件中配置二进制日志的过期时间，设置后二进制日志过期会自动删除:

show variables like '%binlog_expire_logs_seconds%';

查询日志

查询日志中记录了客户端的所有操作语句。如果需要开启查询日志，可以修改MySQL的配置文件/etc/my.cnf文件，添加如下内容：

#该选项用来开启查询日志 ， 可选值：0或者1，0代表关闭，1代表开启
general_log=1
#设置日志的文件名，如果没有指定，默认的文件名为host_name.log
general_log_file=mysql_query.log

开启了查询日志之后，在MySQL的数据存放目录，也就是 /var/lib/mysql/目录下就会出现mysql_query.log文件。之后所有的客户端的增删改查操作都会记录在该日志文件之中，长时间运行后，日志文件将会非常大。

慢查询日志

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

如果需要开启慢查询日志，需要在MySQL的配置文件/etc/my.cnf中配置如下参数：

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

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements和更改此行为log_queries_not_using_indexes

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

主从复制

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

MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制，MySQL复制的优点主要包含以下三个方面：

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

原理

MySQL主从复制的核心为bin log，具体的过程如下：

复制分成三步：

1. Master主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中

2. 从库读取主库的二进制日志文件Binlog，写入到从库的中继日志Relay Log

3. slave重做中继日志中的事件，将改变反映它自己的数据

配置

主库

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

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

重启mysql服务器后，登录并创建远程连接的账号，授予主从复制权限：

#创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
CREATE USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
#为 'itcast'@'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'test'@'%';

查看二进制坐标：

show master status;

字段含义说明：

• file:从哪个日志文件开始推送日志文件
• position:从哪个位置开始推送日志
• binlog_ignore_db:指定不需要同步的数据库

从库

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

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，和主库不一样即可
server-id=2
#是否只读,1 代表只读, 0 代表读写
read-only=1

重启mysql服务器后，登录设置主库配置：

CHANGE REPLICATION SOURCE TO SOURCE_HOST='IP地址', SOURCE_USER='test',
SOURCE_PASSWORD='Root@123456', SOURCE_LOG_FILE='binlog.000004', SOURCE_LOG_POS=663;

参数名	含义	老版本参数
SOURCE_HOST	主库IP地址	MASTER_HOST
SOURCE_USER	连接主库的用户名	MASTER_USER
SOURCE_PASSWORD	连接主库的密码	MASTER_PASSWORD
SOURCE_LOG_FILE	binlog日志文件名	MASTER_LOG_FILE
SOURCE_LOG_POS	binlog日志文件位置	MASTER_LOG_POS

开启同步：

start replica;

查看同步状态：

show replica status;

分库分表

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

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

为了解决上述问题，需要对数据库进行分库分表处理

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

拆分策略

分库分表的形式，主要是两种：垂直拆分和水平拆分。按照拆分粒度又分为分库和分表。

垂直拆分

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

特点：

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

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

特点：

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

水平拆分

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

特点：

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

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

特点：

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

实现技术

• shardingJDBC：基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持java语言
• MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言

MyCat

Mycat是开源的基于Java语言编写的MySQL数据库中间件。可以像使用mysql一样来使用mycat。开发人员只需要连接MyCat即可，而具体底层用到几台数据库，每一台数据库服务器里面存储了什么数据，都无需关心。具体的分库分表的策略，只需要在MyCat中配置即可。

在MyCat的整体结构中，分为两个部分：上面的逻辑结构、下面的物理结构

在MyCat的逻辑结构主要负责逻辑库、逻辑表、分片规则、分片节点等逻辑结构的处理，而具体的数据存储还是在物理结构，也就是数据库服务器中存储的

配置

schema.xml

配置逻辑库、逻辑表、数据节点、节点主机等相关信息

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
    	<table name="TB_ORDER" 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="master" url="jdbc:mysql://IP地址1:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
    </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="master" url="jdbc:mysql://IP地址2:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
    </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="master" url="jdbc:mysql://IP地址3:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
    </dataHost>
</mycat:schema>

主要包含以下三组标签：

• schema标签
• datanode标签
• datahost标签

schema标签

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

核心属性：

• name：指定自定义的逻辑库库名
• checkSQLschema：在SQL语句操作时指定了数据库名称，执行时是否自动去除；true：自动去除，false：不自动去除
• sqlMaxLimit：如果未指定limit进行查询，列表查询模式查询多少条记录

table标签

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

• name：定义逻辑表表名，在该逻辑库下唯一
• dataNode：定义逻辑表所属的dataNode，该属性需要与dataNode标签中name对应；多个dataNode逗号分隔
• rule：分片规则的名字，分片规则名字是在rule.xml中定义的
• primaryKey：逻辑表对应真实表的主键
• type：逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表，全局表需要配置为global

datanode标签

核心属性：

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

datahost标签

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

• name：唯一标识，供上层标签使用
• maxCon/minCon：最大连接数/最小连接数
• balance：负载均衡策略，取值 0,1,2,3
• writeType：写操作分发方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二个；1：写操作随机分发到配置的writeHost）
• dbDriver：数据库驱动，支持 native、jdbc

rule.xml

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

server.xml

配置用户名、密码，以及用户的访问权限信息

<user name="root" defaultAccount="true">
    <property name="password">123456</property>
    <property name="schemas">DB01</property>
    <!-- 表级 DML 权限设置 -->
    <!--
        <privileges check="true">
            <schema name="DB01" dml="0110" >
                <table name="TB_ORDER" dml="1110"></table>	# 对应IUSD为增改查删
            </schema>
        </privileges>
    -->
</user>
<user name="user">
    <property name="password">123456</property>
    <property name="schemas">DB01</property>
    <property name="readOnly">true</property>
</user>

上述的配置表示，定义了两个用户root和user，两个用户都可以访问DB01逻辑库，访问密码都是123456，但是root用户访问DB01逻辑库，既可以读，又可以写，但是user用户访问逻辑库是只读的

主要有两个标签：system、user

system标签

主要配置MyCat中的系统配置信息，对应的系统配置项及其含义

属性	取值	含义
charset	utf8	设置Mycat的字符集, 字符集需要与MySQL的字符集保持一致
nonePasswordLogin	0,1	0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户
useSqlStat	0,1	开启SQL实时统计,1 为开启,0 为关闭；开启之后, MyCat会自动统计SQL语句的执行情况
useGlobleTableCheck	0,1	是否开启全局表的一致性检测。1为开启，0为关闭
sequnceHandlerType	0,1,2	用来指定Mycat全局序列类型，0 为本地文件，1 为数据库方式，2 为时间戳列方式，默认使用本地文件方式，文件方式主要用于测试
idleTimeout	30	指定连接的空闲时间的超时长度;如果超时,将关闭资源并回收, 默认30分钟
serverPort	8066	定义MyCat的管理端口, 默认8066
managerPort	9066	定义MyCat的管理端口, 默认9066
sqlExecuteTimeout	300	执行SQL的超时时间, 如果SQL语句执行超时,将关闭连接; 默认300秒;

user标签

配置MyCat中的用户、访问密码，以及用户针对于逻辑库、逻辑表的权限信息

启动

切换到Mycat的安装目录，执行指令启动Mycat：

#启动
bin/mycat start
#停止
bin/mycat stop

Mycat启动之后，占用端口号8066

连接

mysql -h IP地址 -P 8066 -uroot -p123456

通过MySQL的指令来连接的MyCat，因为MyCat在底层实际上是模拟了MySQL的协议

MyCat管理

在MyCat中，当执行一条SQL语句时，MyCat需要进行SQL解析、分片分析、路由分析、读写分离分析等操作，最终经过一系列的分析决定将当前的SQL语句到底路由到那几个(或哪一个)节点数据库，数据库将数据执行完毕后，如果有返回的结果，则将结果返回给MyCat，最终还需要在MyCat中进行结果合并、聚合处理、排序处理、分页处理等操作，最终再将结果返回给客户端。

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

• 8066 数据访问端口，即进行 DML 和 DDL 操作。
• 9066 数据库管理端口，即 mycat 服务管理控制功能，用于管理mycat的整个集群状态

连接MyCat的管理控制台：

mysql -h IP地址 -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统计

读写分离

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

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

一主一从

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

schema.xml配置

<!-- 配置逻辑库 -->
<schema name="RW" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7"></schema>
<dataNode name="dn7" dataHost="dhost7" database="itcast" />
<dataHost name="dhost7" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="master1" url="jdbc:mysql://IP地址:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" >
    	<readHost host="slave1" url="jdbc:mysql://IP地址:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
    </writeHost>
</dataHost>

writeHost代表的是写操作对应的数据库，readHost代表的是读操作对应的数据库。所以我们要想实现读写分离，就得配置writeHost关联的是主库，readHost关联的是从库。

而仅仅配置好了writeHost以及readHost还不能完成读写分离，还需要配置一个负责均衡的参数balance，取值有4种，具体含义如下：

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

在一主一从模式的读写分离中，balance配置1或3都是可以完成读写分离的。

双主双从

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

schema.xml配置

配置逻辑库

<schema name="RW2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7"></schema>

配置数据节点

<dataNode name="dn7" dataHost="dhost7" database="db01" />

配置节点主机

<dataHost name="dhost7" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="master1" url="jdbc:mysql://IP地址1:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" >
    	<readHost host="slave1" url="jdbc:mysql://IP地址2:3306? useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
    </writeHost>
    <writeHost host="master2" url="jdbc:mysql://IP地址3:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" >
    	<readHost host="slave2" url="jdbc:mysql://IP地址4:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
    </writeHost>
</dataHost>

属性说明：

• balance=”1”：代表全部的readHost与stand by writeHost参与 select 语句的负载均衡，简单的说，当双主双从模式(M1 -> S1，M2-> S2，并且M1与M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡

• writeType

  • 0:写操作都转发到第1台writeHost, writeHost1挂了, 会切换到writeHost2上
  • 1:所有的写操作都随机地发送到配置的writeHost上

• switchType

  • -1:不自动切换
  • 1:自动切换

user.xml配置：配置root用户可以访问到逻辑库RW2

<user name="root" defaultAccount="true">
    <property name="password">123456</property>
    <property name="schemas">SHOPPING,TEST,RW2</property>
</user>