MySQL数据库技术--运维篇

MySQL运维篇

日志

错误日志

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

该日志是默认开启的，默认存放目录/var/log/，默认的日志文件名为mysqld.log。查看日志位置：
show variables like ‘%log_error%’;

二进制日志

二进制日志（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 指定数据库名称，只列出指定的数据库相关操作
  -o 忽略掉日志中的前n行命令
  -v 将行事件（数据变更）重构为SQL语句
  -w 将行事件（数据变更）重构为SQL语句，并输出注释信息

• 日志删除
  对于比较繁忙的业务系统，每天生成的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%’;

查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。由于查询操作比较多，数据量大，因此默认情况下，查询日志是未开启的。如果需要开启查询日志，可以设置以下配置：
show variables like '%general%';

修改MySQL的配置文件/etc/my.cnf文件，添加如下内容：
#该选项用来开启查询日志，可选值：0或者1；0代表关闭，1代表开启
general_log=1
#设置日志的文件名，如果没有指定，默认的文件名为host_name.log
general_log_file=mysql_query.log

慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志，默认未开启。Long_query_time默认为10秒，最小为0，精度可以到微秒。
#慢查询日志
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复制的优点主要包括以下三个方面：
1. 主库出现问题，可以快速切换到从库提供服务。
2. 实现读写分离，降低主库的访问压力。
3.可以在从库中执行备份，以避免备份期间影响主库服务。

原理

MySQL的主从复制原理如下。

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

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

搭建

• 服务器准备
  
  准备好两台服务器之后，在上述的两台服务器中分别安装好MySQL，并完成基础的初始化准备工作。
• 主库配置
  1. 修改配置文件/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
     

  2. 重启MySQL服务
     systemctl restart mysqld
  3. 登录mysql，创建远程连接的账号，并授予主从复制权限

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

  4. 通过指令，查看二进制日志坐标
     show master status;
     字段含义说明：
     file：从哪个日志文件开始推送日志文件
     position：从哪个位置开始推送日志
     binlog_ignore_db：指定不需要同步的数据库
• 从库配置

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

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

  2. 重启MySQL服务
     systemctl restart mysqld

  3. 登录mysql，设置主库配置
     CHANGE REPLICATION SOURCE TO SOURCE_HOST=’xxx.xxx’,SOURCE_USER=’xxx’,SOURCE_PASSWORD=’xxx’,SOURCE_LOG_FILE=’xxx’,SOURCE_LOG_POS=xxx;
     上述时8.0.23中的语法，如果MySQL是8.0.23之前的版本，执行如下SQL：
     CHANGE MASTER TO MASTER_HOST=’xxx.xxx.xxx.xxx’,MASTER_USER=’xxx’,MASTER_PASSWORD=’xxx’,MASTER_LOG_FILE=’xxx’,MASTER_LOG_POS=xxx;

     参数名	含义	8.0.23之前
     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

  4. 开启同步操作
     start replica; #8.0.22之后
     start slave; #8.0.22之前

  5. 查看主从同步状态
     show replica status; #8.0.22之后
     show slave status; #8.0.22之前
     

• 测试
  1. 在主库上创建数据库、表，并插入数据

     create database db01;
     use db01;
     create table tb_user(
     		id int(11) primary key not null auto_increment,
     		name varchar(50) not null,
     		sex varchar(1)
     ) engine=innodb default charset=utf8mb4;
     insert into tb_user(id,name,sex) values(null,’Tom’,’1’),(null,’Trigger’,’0’);
     

  2. 在从库中查看数据，验证主从是否同步

分库分表

介绍

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

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

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

• 拆分策略
  

  1. 垂直拆分
     
  2. 水平拆分
     

• 实现技术
  

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

MyCat

• MyCat概述

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

     优势：
     ①性能可靠稳定
     ②强大的技术团队
     ③体系完善
     ④社区活跃
     

  2. 安装
     MyCat是采用Java语言开发的开源的数据库中间件，支持Windows和Linux运行环境，下面介绍MyCat的Linux中的环境搭建。我们需要在准备好的服务器中安装如下软件。
     ①Mysql
     ②JDK
     ③MyCat

     服务器	安装软件	说明
     192.168.200.210	JDK、MyCat	MyCat中间件服务器
     192.168.200.210	MySQL	分片服务器
     192.168.200.213	MySQL	分片服务器
     192.168.200.214	MySQL	分片服务器

  3. 目录结构
     
     bin：存放可执行文件，用于启动停止mycat
     conf：存放mycat的配置文件
     lib：存放mycat的项目依赖包（jar）
     logs：存放mycat的日志文件

  4. 概念介绍
     

• MyCat入门
  1. 需求
     由于tb_order表中数据量很多，磁盘IO及容量都到达了瓶颈，现在需要对tb_order表进行数据分片，分为三个数据节点，每一个节点主机位于不同的服务器上，具体的结构，参考下图：
     
  2. 环境准备
     
  3. 分片配置（schema.xml）
     /mycat/conf/schema.xml
     
     
     分片配置（server.xml）
     配置mycat的用户及用户的权限信息：
     
     分片配置（rule.xml）
     配置分片规则
  4. 测试——启动服务
     切换到MyCat的安装目录，执行如下指令，启动MyCat:
     #启动
     bin/mycat start
     #停止
     bin/mycat stop
     MyCat启动之后，占用端口号8066。
     启动完毕之后，可以查看logs目录下的启动日志，查看MyCat是否启动完成。
     
     只要输出的最后出现划红线的信息，就代表启动成功。
  5. 测试——分片测试
     通过如下指令，就可以连接并登录MyCat。
     mysql -h 192.168.174.131 -P 8066 -uroot -p123456
     然后就可以在MyCat中来创建表，并往表结构中插入数据，查看数据在MyCat中的分布情况。

     CREATE TABLE TB_ORDER(
     		Id BIGINT(20) NOT NULL,
     		Title VARCHAR(100) NOT NULL,
     		PRIMARY KEY (id)
     ) ENGINE=INNODB DEFAULT CHARSET=utf8;
     INSERT INTO TB_ORDER(id,title) VALUES(1,’goods1’);
     INSERT INTO TB_ORDER(id,title) VALUES(2,’goods2’);
     INSERT INTO TB_ORDER(id,title) VALUES(3,’goods3’);
     INSERT INTO TB_ORDER(id,title) VALUES(1000000,’goods1000000’);
     INSERT INTO TB_ORDER(id,title) VALUES(10000000,’goods10000000’);
     

• MyCat配置
  1. schema.xml
     schema.xml作为MyCat中最重要的配置文件之一，涵盖了MyCat的逻辑库、逻辑表、分片规则、分片节点及数据源的配置。
     主要包含以下三组标签：
     schema标签 datanode标签 datahost标签
     ①schema.xml标签
     
     schema标签用于定义MyCat实例中的逻辑库，一个MyCat实例中，可以有多个逻辑库，可以通过schema标签来划分不同的逻辑库。MyCat中的逻辑库的概念，等同于MySQL中的database概念，需要操作某个逻辑库下的表时，也需要切换逻辑库（use xxx）。
     核心属性：
     name：指定自定义的逻辑库库名
     checkSQLschema：在SQL语句操作时指定了数据库名称，执行时是否自动去除；true：自动去除，false：不自动去除。
     sqlMaxLimit：如果未指定limit进行查询，列表查询模式查询多少条记录
     ②schema.xml标签（table）
     
     Table标签定义了MyCat中逻辑库schema下的逻辑表，所有需要拆分的表都需要在table标签中定义。
     核心属性：
     name：定义逻辑表表名，在该逻辑库下唯一
     dataNode：定义逻辑表所属的dataNode，该属性需要与dataNode标签中name对应；多个dataNode逗号分隔
     rule：分片规则的名字，分片规则名字是在rule.xml中定义的
     primaryKey：逻辑表对应真实表的主键
     type：逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配置为global
     ③dataNode标签
     
     dataNode标签中定义了MyCat中的数据节点，也就是我们通常说的数据分片。一个dataNode标签就是一个独立的数据分片。
     核心属性：
     name：定义数据节点名称
     dataHost：数据库实例主机名称，引用自dataHost标签中name属性
     database：定义分片所属数据库
     ④dataHost标签
     
     该标签在MyCat逻辑库中作为底层标签存在，直接定义了具体的数据库实例、读写分离、心态语句。
     核心属性：
     name：唯一标识，供上层标签使用
     maxCon/minCon：最大连接数/最小连接数
     balance：负载均衡策略，取值0，1，2，3
     writeType：写操作分发方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二个；1：写操作随机分发到配置的writeHost）
     dbDriver：数据库驱动，支持native、jdbc
  2. rule.xml
     rule.xml中定义所有拆分表的规则，在使用过程中可以灵活的使用分片算法，或者对同一个分片算法使用不同的参数，它让分片过程可配置化。主要包含两类标签：tableRule、Function。
     
  3. server.xml
     server.xml配置文件包含了MyCat的系统配置信息，主要有两个重要的标签：system、user。
     ①system标签
     
     ②user标签
     
• MyCat分片
  
  
  
  
  
  
  
  
• 分片规则
  范围分片
  
  
  取模分片
  
  
  一致性hash算法
  
  
  枚举分片
  
  
  应用指定
  
  
  固定分片hash算法
  
  
  字符串hash解析
  
  
  按（天）日期分片
  
  
  按自然月分片
  
  
• MyCat管理及监控

  1. Mycat原理
     

  2. Mycat管理
     Mycat默认开通2个端口，可以在server.xml中进行修改。
     ①8066数据访问端口，即进行DML和DDL操作。
     ②9066数据库管理端口，即mycat服务管理控制功能，用于管理mycat的整个集群状态。
     mysql -h 192.168.174.131 -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统计

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

     Mycat-eye运行过程中需要依赖zookeeper，因此需要先安装zookeeper。
     ②安装
     zookeeper安装
     MyCat-web安装
     ③访问
     http://192.168.174.131:8082/mycat
     ④配置
     开启Mycat的实时统计功能（server.xml）
     <property name=”useSqlStat”>1</property> <!-- 1为开启，0为关闭-->
     在Mycat监控界面配置服务地址
     

读写分离

介绍

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

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

一主一从

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

• 环境准备

  主机	角色	用户名	密码
  192.168.174.131	master	root	1234
  192.168.174.132	slave	root	1234

• 读写分离配置
  
  

• 测试
  连接Mycat，并在Mycat中执行DML、DQL查看是否能够进行读写分离。
  问题：主节点Master宕机之后，业务系统就只能够读，而不能写入数据了。

双主双从

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

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

  编号	IP	预装软件	角色
  1	192.168.200.210	MyCat、MySQL	MyCat中间件服务器
  2	192.168.200.211	MySQL	M1
  3	192.168.200.212	MySQL	S1
  4	192.168.200.213	MySQL	M2
  5	192.168.200.214	MySQL	S2

• 搭建

  1. 先配置两个主库信息
     
     
  2. 创建用于主从复制的账户
     
  3. 再配置两个从库信息
     
     
     
  4. 配置两个主库相互复制
     
  5. 测试
     

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

• 测试
  登录MyCat，测试查询及更新操作，判定是否能够进行读写分离，以及读写分离的策略是否正确。

  当主库挂掉一个后，是否能够自动切换。