【后端之旅】五、MySQL 该怎么写（下篇）

这里是 MySQL 相关知识点的下篇。最重要的部分就是日志，特别是二进制日志和慢查询日志，贯穿了整个下篇。如果你在大中厂工作，可能还需要考虑分库分表，这里提供了一些思路。

日志

• 错误日志
  • 记录了 MySQL 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息
  • 默认开启，默认位置: /var/log/mysqld.log
  • 相关命令:

    -- 查看日志位置
    SHOW VARIABLES LIKE '%log_error%';
    

• 二进制日志
  • 记录了所有的 DDL、DML 语句，但不包括数据查询 (SELECT、SHOW)语句
  • 具体作用有:
    1. 灾难时进行数据恢复
    2. MySQL 的主从复制
  • 二进制日志格式有:
    • STATEMENT: 基于 SQL 语句的日志记录，记录的是 SQL 语句，对数据进行修改的 SQL 都会记录在日志文件中
    • ROW: 默认格式。进行行日志记录，记录的是每一行的数据变更（即 变更前的记录和变更后的记录）
    • MIXED: 混合了 STATEMENT 和 ROW 两种格式，默认采用 STATEMENT，在某些特殊情况下会自动切换为 ROW 格式
  • MySQL 8 版本默认开启
  • 相关命令:

    -- 查看二进制日志位置
    SHOW VARIABLES LIKE '%log_bin%';
    
    -- 查看二进制日志格式
    SHOW VARIABLES LIKE '%binlog_format%';
    
    -- 查看二进制日志文件，其中 OPTIONS 包括
    -- -d 指定数据库名称，只列出指定的数据相关操作
    -- -o 忽略日志中的前 n 行命令
    -- -v 将行事件(数据变更)重构为 SQL 语句
    -- -w 将行事件(数据变更)重构为 SQL 语句，并输出注释信息
    mysqlbinlog [OPTIONS] log_file_name
    
    -- 清除二进制文件方式一: 删除全部 binlog 日志，删除以后，日志编号将从 binlog.000001 重新开始
    RESET MASTER;
    
    -- 清除二进制文件方式二: 删除 ****** 编号之前的所有日志
    PURGE MASTER LOGS TO 'binlog.******';
    
    -- 清除二进制文件方式三: 删除日志为 "yyyy-mm-dd hh24:mi:ss" 之前产生的所有日志
    PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh24:mi:ss';
    
    -- 查看当前配置的二进制日志的过期时间(默认为 30 天)
    SHOW VARIABLES LIKE '%binlog_expire_logs_seconds%';
    

• 查询日志
  • 记录了客户端的所有操作语句
  • 默认不开启
  • 若要开启查询日志，可在 /etc/my.cnf 文件添加以下配置:

    # 该选项用于开启查询日志，0 表示关闭，1 表示开启
    general_log = 1
    
    # 该选项用于指定查询日志的文件名，如果未指定，则默认的文件名为 /var/log/mysql/[hostname].log
    general_log_file = /var/log/mysql/mysql_query.log
    

  • 相关命令:

    -- 查看查询日志位置
    SHOW VARIABLES LIKE '%general%';
    

• 慢查询日志
  • 记录了所有执行时间超过参数 long_query_time 设置值(默认为 10 秒，精度可以到微秒)并且扫描记录数不小于 min_examined_row_limit 的所有 SQL 语句
  • 默认不开启
  • 默认不记录管理语句，也不会记录不使用索引进行查询的语句
  • 若要开启慢查询日志，可在 /etc/my.cnf 文件添加以下配置:

    # 该选项用于开启慢查询日志，0 表示关闭，1 表示开启
    slow_query_log = 1
    
    # 执行时间参数
    long_query_time = 5
    
    # 记录执行较慢的管理语句
    log_slow_admin_statements = 1
    
    # 记录执行较慢的未使用索引的语句
    log_queries_not_using_indexes = 1
    

主从复制

• 概念:
  • 主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行(即 重做)，从而使得从库和主库的数据保持同步
• 优点:
  1. 主库出现问题后，可快速切换到从库提供服务
  2. 实现读写分离，降低主库的访问压力
  3. 可在从库中执行备份，避免备份期间影响主库服务
• 原理:
  1. Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中
  2. 从库读取主库的二进制文件 Binlog，写入到从库的中继日志 Relaylog 中
  3. Slave 重做中继日志中的事件，将改变反映它自己的数据
• 搭建:
  1. 服务器准备 - 准备 Master 和 Slave 服务器各一台并安装好 MySQL (学习环境关闭防火墙，生产环境开放指定的 3306 端口)
  2. 修改主库配置文件 /etc/my.cnf

     # MySQL 服务 ID，保证整个集群环境中唯一，取值范围为 1 ~ 2^32 - 1，默认为 1
     server-id=1
     
     # 是否为只读，1 表示只读，0 表示读写
     read-only=0
     
     # 指定需忽略同步的数据库 (默认为空)
     # binlog-ignore-db=db1,db2
     
     # 指定需同步的数据库 (默认为全部数据库)
     # binlog-do-db=db1,db2
     

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

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

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

     -- 字段含义说明:
     -- - file: 从哪个日志文件开始推送二进制文件
     -- - position: 从哪个位置开始推送二进制文件
     -- - binlog_do_db: 需要同步的库名
     -- - binlog_ignore_db: 需要忽略的库名
     SHOW MASTER STATUS;
     

  5. 修改从库配置文件 /etc/my.cnf

     # MySQL 服务 ID，保证整个集群环境中唯一，取值范围为 1 ~ 2^32 - 1，必须和主库不一样
     server-id=2
     
     # 是否为只读，1 表示只读，0 表示读写
     read-only=1
     
     # 是否对超级管理员也为只读
     # super-read-only=1
     

  6. 登录 MySQL 从库，修改主库的连接信息

     -- 以下命令的字段含义说明:
     -- - SOURCE_HOST/MASTER_HOST: 主库 IP 地址
     -- - SOURCE_USER/MASTER_USER: 连接主库的用户名
     -- - SOURCE_PASSWORD/MASTER_PASSWORD: 连接主库的密码
     -- - SOURCE_LOG_FILE/MASTER_LOG_FILE: 主库的 binlog 文件名
     -- - SOURCE_LOG_POS/MASTER_LOG_POS: 主库的 binlog 文件的位置
     
     -- 8.0.23 版本之前的命令为
     CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='ego', MASTER_PASSWORD='123456', MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=35;
     
     -- 8.0.23 版本之后的命令为
     CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.1.1', SOURCE_USER='ego', SOURCE_PASSWORD='123456', SOURCE_LOG_FILE='binlog.000001', SOURCE_LOG_POS=35;
     

  7. 从库开启同步操作

     -- 8.0.22 之前的命令为
     START SLAVE;
     
     -- 8.0.22 之后的命令为
     START REPLICA;
     

  8. 从库查看同步状态

     -- 8.0.22 之前的命令为
     SHOW SLAVE STATUS;
     
     -- 8.0.22 之后的命令为
     SHOW REPLICA STATUS;
     

分库分表

• 垂直拆分
  • 垂直分库
    • 概念: 以表为依据，根据业务将不同表拆分到不同库中
    • 特点:
      1. 每个库的表结构都不一样
      2. 每个库的数据也不一样
      3. 所有库的并集是全量数据
  • 垂直分表
    • 概念: 以字段为依据，根据字段属性将不同字段拆分到不同表中
    • 特点:
      1. 每个表的结构都不一样
      2. 每个表的数据也不一样，一般通过一列（主键/外键）关联
      3. 所有表的并集是全量数据
• 水平拆分
  • 水平分库
    • 概念: 以字段为依据，按照一定的策略，将一个库的数据拆分到多个库中
    • 特点:
      1. 每个库的表结构都一样
      2. 每个库的数据都不一样
      3. 所有库的并集是全量数据
  • 水平分表
    • 概念: 以字段为依据，按照一定的策略，将一个表的数据拆分到多个表中
    • 特点:
      1. 每个表的表结构都一样
      2. 每个表的数据都不一样
      3. 所有表的并集是全量数据
• 实现技术:
  • shardingJDBC:
    • 基于 AOP 原理
    • 在应用程序中对本地执行 SQL 进行拦截，并解析、改写、路由处理
    • 需要自行编码配置实现
    • 只支持 Java 语言
    • 性能较高
  • MyCat:
    • 数据库分库分表中间件
    • 不用调整代码即可实现分库分表（可以像使用 MySQL 一样来使用 MyCat）
    • 支持多种语言
    • 性能相对较低

MyCat

• MyCat 概述
  • 描述: MyCat 是一个阿里巴巴开源的、基于 Java 语言编写的 MySQL 数据库中间件。开发可以无感知地使用 MyCat。
  • 官网: www.mycat.org.cn/
  • 分层:
    • schema —— 逻辑库
      • table —— 逻辑表
        • dataNode —— 分片节点，每个分片节点对应一个物理结构（即独立的节点主机 IP:PORT）
• MyCat 入门
  • 配置文件 /config/schema.xml 示例

    <mycat:schema>
        <!-- 逻辑表 -->
        <schema name="DB01" checkSQLSchema="false" sqlMaxLimit="100">
            <!-- rule 表示分片规则，可填配置名称位于 rule.xml 中，是最重要的一个配置 -->
            <table name="t_user" dataNode="dn1,dn2" rule="auto-sharding-long" />
        </schema>
    
        <!-- 数据节点 -->
        <dataNode name="dn1" dataHost="dbhost1" database="db01" />
        <dataNode name="dn2" dataHost="dbhost2" database="db01" />
    
        <!-- 主机节点 -->
        <dataHost name="dbhost1" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="jdbc">
            <heartbeat>select user()</heartbeat>
            <writeHost host="master" url="jdbc:mysql://192.168.0.1:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf-8" user="root" password="123456" />
        </dataHost>
        <dataHost name="dbhost2" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="jdbc">
            <heartbeat>select user()</heartbeat>
            <writeHost host="master" url="jdbc:mysql://192.168.0.2:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />
        </dataHost>
    </mycat:schema>
    

  • 配置文件 /config/server.xml 示例

    <mycat:server>
        <!-- 用于配置 MyCat 系统参数信息 -->
        <system>
            <property name="nonePasswordLogin">0</property>
            <property name="useHandshakeV10">1</property>
            <property name="useSqlStat">1</property>
        </system>
        <!-- 用于配置 MyCat (其实是 MySQL)的用户及用户的权限信息 -->
        <user name="root" defaultAccount="true">
            <property name="password">123456</property>
            <property name="schemas">DB01</property>
        </user>
        <user name="user">
            <property name="password">123456</property>
            <property name="schemas">DB01</property>
            <property name="readOnly">true</property>
            <!-- 表级 DML 权限设置 -->
            <privileges check="true">
                <!-- 0110 分别表示：增查改删 -->
                <schema name="DB01" dml="0110">
                    <table name="t_user" dml="1110" />
                </schema>
            </privileges>
        </user>
    </mycat:server>
    

  • 配置文件 /config/rule.xml 示例

    <!-- 用于配置分片规则信息 -->
    <mycat:rule>
        <tableRule name="auto-sharding-long">
            <rule>
                <columns>id</columns>
                <algorithm>rang-long</algorithm>
            </rule>
        </tableRule>
        <tableRule name="sharding-by-month">
            <rule>
                <columns>create_time</columns>
                <algorithm>partbymonth</algorithm>
            </rule>
        </tableRule>
    
        <function name="rang-long" class="com.mycat.mycat.route.function.AutoPartitionByLong">
            <property name="mapFile">autopartition-long.txt</property>
        </function>
    </mycat:rule>
    

  • MyCat 启动后默认占用 8066 端口，启动命令为：

    mysql -h 127.0.0.1 -P 8066 -u root -p
    

• 常用分片规则
  • 范围
  • 取模
  • 枚举
  • 一致性 hash
  • 固定分片 hash
  • 字符串 hash
  • 按天分片
  • 按自然月分片

读写分离

• 概念
  • MySQL 的主从复制是基于二进制日志(binlog)实现的
  • 写操作(增、删、改)走主库，读操作(查)走从库
  • 可以使用 MyCat 实现读写分离，这样就避免了应用程序需要连接多个数据库的问题
• 一主一从搭建
  • 主库只负责写，从库只负责读。一旦主库机器宕机，写操作就完全失效了
• 一主一从读写分离
  • 使用 MyCat 配置实现
• 双主双从搭建
  • 主库和从库均可同步对方的数据，从库此时相当于备用数据库，一旦主库宕机，从库可以继续提供写入服务。直到主库机器重启后，主库自动参考从库同步数据

    1 → 2
    ↕
    3 → 4
    

  • 主库一 /etc/my.cnf 配置:

    # MySQL 服务 ID，取值范围 1 - (2^32 - 1)
    server-id=1
    
    # 指定同步的数据库
    binlog-do-db=db01
    binlog-do-db=db02
    binlog-do-db=db03
    
    # 在作为从数据库时，有写入操作也要更新二进制日志文件
    log-slave-updates
    

  • 主库二 /etc/my.cnf 配置:

    # MySQL 服务 ID，取值范围 1 - (2^32 - 1)
    server-id=3
    
    # 指定同步的数据库
    binlog-do-db=db01
    binlog-do-db=db02
    binlog-do-db=db03
    
    # 在作为从数据库时，有写入操作也要更新二进制日志文件
    log-slave-updates
    

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

    -- 创建 ego 用户，并设置密码，该用户可在任意主机连接该 MySQL 服务
    CREATE USER 'ego'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
    
    -- 为 ego 用户分配主从复制权限
    GRANT REPLICATION SLAVE ON *.* TO 'ego'@'%';
    
    -- 查看两台主库的二进制日志坐标
    SHOW ego STATUS;
    

  • 从库一 /etc/my.cnf 配置:

    # MySQL 服务 ID，取值范围 1 - (2^32 - 1)
    server-id=2
    

  • 从库二 /etc/my.cnf 配置:

    # MySQL 服务 ID，取值范围 1 - (2^32 - 1)
    server-id=4
    

  • 分别两台从库配置关联的主库

    -- 从库关联主库
    CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx', MASTER_USER='xxx', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='xxx', MASTER_LOG_POS=xxx;
    
    -- 启动从库同步
    START SLAVE;
    
    -- 查看从库状态
    SHOW SLAVE STATUS;
    

  • 分别设置两个主库相互复制

    -- 主库一关联主库二，主库二关联主库一
    CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx', MASTER_USER='xxx', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='xxx', MASTER_LOG_POS=xxx;
    

• 双主双从读写分离
  • 通过 MyCat 中的 schema.xml 中的 dataHost 标签的 balance 属性来实现读写分离和负载均衡
  • 通过 MyCat 中的 schema.xml 中的 dataHost 标签的 writeType、switchType 属性来实现失败自动切换