MySQL(运维篇)

1.日志

1.1错误日志

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

        该日志是默认开启的，默认存放目录/var/log/，默认的日志文件名为mysqld.log

1.2二进制日志

1.2.1介绍

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

        作用：1.灾难时的数据恢复

                    2.MySQL的主从复制。在MySQL8版本中，默认二进制日志是开启的，涉及到的参数：

1.2.2日志格式

1.2.3日志查看

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

默认为row的时候，+v

当类型为statement时，不用+v（直接记录SQL语句）

 1.2.4日志删除

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

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

1.3查询日志

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

        修改MySQL的配置文件/etc/my.cnf文件，添加以下内容(修改完后需要重启mysqld)：

1.4慢查询日志

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

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

2.主从复制

2.1概述

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

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

优点：

*        主库出现问题，可以快速切换到从库提供服务

*        实现读写分离，降低主库的访问压力

*        可以从库中执行备份，以避免备份期间影响主库服务

2.2原理（二进制日志文件）

        复制分为三步：

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

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

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

2.3搭建

2.3.1服务器准备

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

2.3.2主库配置

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

*        2.重启MySQL服务器

*        3.登录MySQL，创建远程连接的账号，并授予主从复制权限

*        4.通过指令，查看二进制日志坐标

 

        字段含义说明：

file        从哪个日志文件开始推送日志文件

position        从哪个位置开始推送日志

binlog_ignore_db        指定不需要同步的数据库

 2.3.3从库配置

*        1.修改配置文件/etc/cnf

*        2. 重新启动MySQL服务

*        3.登录MySQL，设置主库配置

上述是8.0.23中的版本，如果是之前的版本如下

*        4.开启同步操作

*        5.查看主从同步状态

 

正常的表现：

3.分库分表

3.1介绍

3.1.1问题分析

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

*        1.IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。请求数据太多，带宽不够，网络IO瓶颈。

*        2.CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈。 

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

3.1.2拆分策略

3.1.3垂直拆分

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

特点：        *        1.每个库的表结构都不一样

                *        2.每个库的数据也不一样

                  *        3.所有库的并集是全量数据

    

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

特点：        *        1.每个表的结构都不一样

                   *        2.每个表的数据也不一样，一般通过一列（主键/外键）关联

                   *        3.所有表的并集是全量数据

 3.1.4水平拆分

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

特点：        *        1.每个库的表结构都一样

                   *        2.每个库的数据都不一样

                   *        3.所有库的并集是全量数据 

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

特点：        *        1.每个表的表结构都一样

                   *        2.每个表的数据都不一样

                   *        3.所有表的并集是全量数据

3.1.5实现技术

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

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

3.2MyCat概述

3.2.1介绍

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

优势：        *        性能可靠稳定

                        *        强大的技术团队

                                *        体系完善

                                        *        社区活跃

3.2.2概念介绍

3.3MyCat入门 

3.3.1需求

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

3.3.2环境准备

3.3.3分片配置（schema.xml）

 

3.3.4分片配置（server.xml）

配置mycat的用户及用户的权限信息：

3.3.5启动服务

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

        启动完成后，可以查看logs目录下的启动日志，查看Mycat是否启动完成。

3.3.6分片测试

        通过以下指令，就可以连接并登录Mycat

        然后可以在Mycat中创建表，并往表结构中插入数据，查看数据在MySQL中的分布情况。

3.4MyCat配置

3.4.1schema.xml

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

主要包含三组标签:

*        schema标签

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

*         schema标签（table）

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

*        datanode标签

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

 

*        datahost标签

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

3.4.2rule.xml

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

3.4.3server.xml

        配置文件包含了MyCat的系统配置信息，主要有两个重要的标签：system、user。

*        system标签

        对应的系统配置项及其含义，参考资料。

*        user标签

3.5MyCat分片

3.5.1垂直拆分

*        场景

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

*        准备

*        配置

*        测试

        在mycat的命令行中，通过source指令导入表结构，以及对应的数据，查看数据分布情况。

 

        查询用户的收件人及收件人地址信息（包含省、市、区）

*        全局表配置

        对于省、市、区/县表tb_areas_provinces,tb_areas_city,tb_areas_region,是属于数据字典表，在多个业务模块中都可能会遇到，可以将其设置为全局表，利于业务操作。 

3.5.2水平拆分

*        场景

        在业务系统中，有一张表（日志表），业务系统每天都会产生大量的日志数据，单台服务器的数据存储及处理能力是有限的，可以对数据库表进行拆分。

*        准备

        分别在三台MySQL上创建数据库itcast。

*        配置

3.5.3分片规则-范围

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

 

3.5.4分片规则-取模

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

 3.5.5分片规则-一致性Hash

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

 

3.5.6分片规则-枚举

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

3.5.7分片规则-应用指定

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

3.5.8分片规则-固定分片hash算法

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

 

3.5.8分片规则-字符串hash解析

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

3.5.9分片规则-按（天）分片

 

3.5.10分片规则-自然月

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

3.6MyCat管理及监控

3.6.1Mycat原理

3.6.2Mycat管理

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

*        8066数据访问端口，即进行DML和DDL操作。

*        9066数据库管理端口，即mycat服务管理控制功能，用于管理mycat的整个集群状态。

3.6.3Mycat-eye

*        介绍

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

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

*        Mycat-eye

安装

        Zookeeper安装和Mycat-web安装

*        配置

        开启Mycat的实时统计功能（server.xml）

        <property name="useSqlStat">1</property>        <!-- 1为开启实时统计，0为关闭 --  >

*        在Mycat监控界面配置服务地址

 

4.读写分离

4.1介绍 

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

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

 

4.2一主一从

4.2.1原理

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

4.2.2环境准备

4.3一主一从读写分离

4.3.1配置

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

 4.3.2测试

        连接MyCat，并在Mycat中执行DML、DQL查看是否能够进行读写分离。

        问题：主节点Master宕机之后，业务系统就只能读，而不能写入数据了。

4.4双主双从

4.4.1介绍

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

 4.4.2准备工作

        我们需要准备5台服务器，具体的服务器及软件安装情况如下：

4.4.3搭建

*        主库配置（Master1-192.168.200.211）

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

 

2.重启MySQ服务器

 

*        主库配置（Master2-192.168.200.213）

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

2.重启MySQL服务器

 

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

通过指令，查看两台主库的二进制日志坐标

 

*        从库配置（Slave1-192.168.200.212）

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

2.重启MySQL服务器

 

*        从库配置（Slave1-192.168.200.214）

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

 

2.重启MySQL服务器

 

*        两台从库配置关联的主库（在从库中的MySQL中执行）

        需要注意slave1对应的是master1，slave2对应的是master2.

启动两台从库主从复制，查看从库状态。

*        两台主库相互复制

Master2复制Master1，Master1复制Master2

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

4.5双主双从读写分离

4.5.1配置

        MyCat控制台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控制，通过writeType和switchType来完成失败自动切换的。