MySQL数据库运维篇

一、日志

1.1、错误日志

它记录了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。
该日志是默认开启的，默认存放目录/var/logl，默认的日志文件名为psmysqld.log。
查看日志位置：show variables like '%log_error%'

1.2、二进制日志

通过mysql管理中的mysqlbinlog查看二进制文件，笔记在MYSQL管理中,主要包含DDL和DML语句,不包括查询语句。；
作用：1.灾难是的恢复 2.MySQL的主从复制

查看日志位置：show variables like 'binlog_format'

• 日志查看
  

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

1.3、查询日志

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

1.4、慢查询日志

记录所有执行时间超过long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志，默认是未开启的。

# 开启慢查询日志
slow_query_log = 1
# 执行时间参数，默认是10秒，最小是0，精度可以到微秒
long_query_time = 2


# 默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询
# 开启记录执行较慢的管理语句
log_slow_admin_statements = 1;
# 开启记录执行较慢的未使用索引的语句
log_queries_not_using_indexes = 1;

二、主从复制

2.1 概述

涉及主库（Master）和从库（Slave），主数据库的DDL和DML操作通过二进制日志传到从数据库（可以是多个），然后在从库上对这些日志重新执行（重做），从而使得从库和主库的数据保持同步。

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

优点：

• 主库出现问题，可以快速切换到从库提供服务——>避免崩溃
• 实现读写分离，降低主库的访问压力——>分工合作
• 可以在从库上执行备份，以避免备份期间影响主库服务——>备份恢复

2.2 原理

复制分为三步：

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

2.3 搭建

1）主从复制的服务器环境

• 关闭防火墙/指定端口
• 检查mysql的运行状态
  

2）主库配置

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

②重启mysql

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

④找到要从哪里开始复制——二进制文件的坐标

show master status;
/*
file：从哪个日志文件开始推送日志文件
position：从哪个位置开始推送日志
binlog_ignore_db：指定不需要同步的数据库
*/

3）从库配置
①修改配置文件/etc/my.cnf

②重启数据库

③设置主库的相关配置——让机器之间能够互相联系起来

④开启同步操作

# 8.0.22之后
start replic;
# 8.0.22之前
start slave;

⑤查看主从复制的状态

show replic status;
# IO running
# SQL running

三、分库分表

3.1、介绍

问题： 应用系统的数据量成指数式增长，如果采用单数据库进行数据存储，会存在性能瓶瓶

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

将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题。

拆分策略：

• 垂直拆分

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

    特点：

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

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

    特点：

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

• 水平拆分

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

    特点

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

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

    特点：

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

3.2 、Mycat概述

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

目录结构：

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

3.3 、Mycat入门

需求：

3.4 、Mycat配置

1）schema.xml：涵盖MyCat的逻辑库、逻辑表、分片规则、分片节点及数据源配置

主要包含以下三组标签

• schema标签：逻辑库、逻辑表

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

• datanode标签：数据节点
  

• datahost标签：接待你主机以及数据源的相关信息
  

总结：

2）rule.xml：定义了所有拆分表的规则，在使用过程中可以灵活使用分片算法，或者对同一个分片算法使用不同的参数，这一配置文件使分片过程可配置化

主要包含以下两组标签

• tableRule：根据哪个字段进行分片，当前分片规则指定的分片算法，是个引用
• Function：具体的算法的实现方法

3）server.xml：配置文件包含了MyCat的系统配置信息

主要包含两个标签

• system：对应系统配置项及其含义，环境选项
• user：能被哪些用户访问（用户及其权限0000-增改查删）
  

3.5、 Mycat分片

3.5.1、垂直拆分(分库)

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

分库不需要指定 rule，涉及分表需要使用rule；

3.5.2、水平拆分（分表）

3.5.3、分片规则

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

• 取模分片(mod-long)（字段为数字）
  根据指定的字段值与节点数量进行求模运算，根据运算结果，来决定该数据属于那一个分片
  

• 一致性hash（shading-by-murmur）

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

• 枚举分片（shading-by-intfile）
  通过 在配置文件中配置可能的枚举值，指定数据分布到不同数据节点上，本规则适用与按照身份、性别、状态拆分数据等业务。
  
  

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

• 固定分片hash算法
  该算法类似于十进制的求模运算，但是为二进制的操作。例如：取id的二进制低10位与1111111111进行位运算。
  
  

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

• 按（天）日期分片
  
  

3.6 、Mycat管理及监控

四、读写分离

把对数据库的读和写操作分开，以应对不同的数据库服务器。主数据库是提供写操作，从数据库是提供读操作——>减轻单台数据库的压力

• 一主一从
  

• 一主一从读写分离
  
  

• 双主双从
  

• 双主双从读写分离