MySQL运维

日志

错误日志

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

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

日志变量

1
2
# 查看日志位置
show variables like '%log_error%';

image-20230227160452905

查看日志

1
tail -50f ./xxx.err

image-20230227160802360

二进制日志

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

作用

  • 灾难数据恢复
  • MySQL主从复制
1
show variables like '%log_bin%';

image-20230227161951389

image-20230227162219636

日志格式

MySQL服务器中提供了多种格式来记录二进制日志

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

日志变量

1
show variables like '%binlog_format%';

image-20230227162726006

查看日志

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

语法
1
mysqlbinlog [options] log_filename
选项
1
2
3
4
-d # 指定数据库名称，列出指定的数据库相关操作
-o # 忽略日志的前n行命令
-v # 将行事件（数据变更）重构为SQL语句
-w # 将行事件（数据变更）重构为SQL语句并输出注释信息

image-20230227165854111

删除日志

对于比较繁忙的业务系统，每天生成的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配置文件中配置二进制日志的过期时间，设置后二进制日志过期会自动删除

1
show variables like '%binlog_expire_logs_seconds%';

image-20230227170902710

查询日志

查询日志中记录客户端所有操作语句，而二进制日志不包含查询数据的SQL语句，默认情况下，查询日志是未开启的。

日志变量

1
show variables like '%general%';

image-20230227171226909

修改配置文件

1
2
3
4
5
# /etc/my.cnf配置文件
# 设置查询日志是否开启：0->关闭 1->开启
general_log = 1
# 设置日志的文件名，如果没有指定，默认的文件名为hos    t_name.log
general_log_file=mysql_query.log

image-20230227171943243

慢查询日志

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

1
2
3
4
# 设置慢查询日志是否开启
slow_query_log = 1
# 设置执行时间参数（单位：秒）
long_query_time = 2

默认情况下，慢查询日志不会记录管理语句和不使用索引进行查找的查询。可以使用log_show_admin_statements和log_queries_not_using_indexes。

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

主从复制

概述

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

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

image-20230228142608267

优点

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

  • 实现读写分离，降低主库的访问压力，另外从服务器可以使用MyISAM，提升查询性能以及节约系统的开销

  • 主从服务器负责各自的读和写，极大程度缓解了锁的争用

    从库中执行备份（添加全局锁），避免备份期间影响主库服务

原理

MySQL主从复制主要涉及三种线程：binlog线程、I/O线程和SQL线程

  • binlog线程：负责将主服务器上的数据更改写入二进制日志(binary log)中
  • I/O线程：负责从主服务器上读取二进制日志(binary log)，并写入从服务器的中继日志(relay log)中
  • SQL线程：负责读取中继日志(relay log)并重放其中的SQL语句

img

步骤

  • 主库在提交事务时，binlog线程会将数据变更记录在二进制日志文件binary log中
  • 从库I/O线程从主库读取二进制文件binary log，并写入从库的中继日志relay log中
  • 从库SQL线程读取中继日志relay log并重放其中的SQL语句

配置

img

搭建

分库分表

介绍

image-20230509174116100

使用单个数据库进行数据存储存在以下性能瓶颈：

  • IO瓶颈：热点数据过多，导致缓存不足，产生大量磁盘IO；请求数量过多，带宽不足导致网络IO瓶颈
  • CPU瓶颈：排序、分组、连接查询、集合统计等SQL语会耗费大量CPU资源，请求数量多会出现CPU瓶颈

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

拆分策略

image-20230509191742925

水平拆分

image-20230509193450618

img

垂直拆分

image-20230509192210033

img

  • 垂直分库：以表为依据，根据业务将不同的表拆分到不同库中，通常按照数据库中表的密集程度部署到不同的库中。
    • 每个库的表结构和数据不一样，所有库的并集是全量数据
  • 垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中，通常按照列的关系密集程度进行切分，也可以将经常被使用的列和不经常被使用的列切分到不同的表中。
    • 某个表的表结构和数据不一样，一般通过一列（主键/外键）关联，所有表的并集是全量数据

实现技术

image-20230509193730407

MyCat概述

MyCat入门

MyCat配置

MyCat分片

MyCat管理与监控

读写分离

总结