mysql 日志学习

潇火

已于 2024-11-08 04:37:24 修改

阅读量767

点赞数 26

CC 4.0 BY-SA版权

文章标签： mysql 学习数据库

于 2024-11-04 13:40:27 首次发布

本文链接：https://blog.youkuaiyun.com/say_xiaohuo/article/details/137239158

日志

错误日志

记录了当mysqld启动和停止时，以及服务器在运行过程中发生任何任何严重错误时的相关信息；当数据库出现鼓掌导致无法正常使用时，建议首先查看此日志
该日志默认开启，存放在/var/log/mysqld.log

二进制日志

二进制日志（BINLOG）记录了所有的DDL语句和DML语句，也就是记录了表和表数据的变更；但不包括数据查询select, show语句
存放路径 /var/lib/mysql/binlog
二进制日志的一般作用于灾难时的数据恢复、mysql的主从复制

通过 show variables like '%binlog_format%' 查看当前实例的日志格式

日志格式

STATEMENT：每一条会修改数据的 SQL 语句都会记录在 binlog 中。这种格式可能会有非确定性行为，因为在某些情况下，相同的 SQL 语句在不同的机器或时间点上执行可能会产生不同的结果（例如，如果涉及到当前时间或随机值）。
ROW：基于行的复制，即记录每一行被修改的内容。这种格式更加确定，但可能会产生更大的 binlog 文件。
MIXED：MySQL 会根据执行的 SQL 语句自动选择使用 STATEMENT 还是 ROW 格式。如果 MySQL 认为使用 STATEMENT 格式安全，就会使用它；否则，会使用 ROW 格式。

由于日志是以二进制方式存储的，无法直接读取，需要通过二进制日志查询工具查看

mysqlbinlog [参数] filename

-d 指定数据库名称，只列出指定的数据库相关操作

-o 忽略日志中的前n行命令

-v 将行事件（数据变更）重构为sql语句,详细模式（verbose mode）。在这种模式下，mysqlbinlog 会输出更多的信息，例如时间戳、执行的线程 ID 等，使得输出更加易于阅读和理解

-vv 在上面的前提下同时输出注释信息

对于繁忙的的业务系统，每天生成的binlog数据巨大，长时间不清理会占用大量磁盘空间

也可以设置日志过期时间，在my.cnf中设置binlog_expire_logs_seconds的参数单位为秒

查询日志

查询日志中记录了客户端的所有操作语句，不同于二进制日志不包含查询数据的sql语句。默认情况下查询日志是关闭的，通过修改my.cnf文件开启

general_log = 1

general_log_file = /var//log/host_name.log

设置日志的文件名，默认为host_name.log

慢查询日志

慢查询日志默认关闭状态
在 /etc/my.cnf 文件中添加以下配置

# 开启慢日志查询开关
slow_query_log = 1

# 设置慢日志时间为1秒，超过两秒的会被标记为慢查询，记录到日志中

long_query_time = 1

配置完重启mysql服务后，日志生成在 /var/lib/mysql/localhost-slow.log 中，通过对日志中记录的低效率语句进行sql优化来提升性能

主从复制

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

主从复制的好处主要包含：
1、主库出现问题，可以快速切换到从库提供服务
2、实现读写分离，降低单库读写压力
3、可以在从库中执行备份，避免备份期间影响主库服务

主从复制原理

主库中进行DML和DDL语句时，二进制日志binlog会记录所有的语句，从库中有两组线程，IOThread读取主库中的binlog并将其返回给从库并写到从库中的relay log中，SQLThread读取relay log并将里面的语句在从库中执行，将主库中的数据变化同步到自身，保证主从同步

配置

主库配置

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

# mysql服务ID，保证整个集群环境中唯一
server-id = 1

# 1表示只读，0表示读写
read-only = 0

2、重启mysql服务

3、登录mysql，创建远程连接的用户，并赋予该用户主从复制权限
CREATE USER 'slave01'@'%' IDENTIFIED WITH mysql_native_password BY 'psw';
GRANT REPLICATION SLAVE ON *.* TO 'slave01'@'%';

4、查看二进制日志坐标

show master status
查询当前二进制文件写到哪一个文件(file)和哪一个位置(position)，将当前位置往后的记录同步至从库

从库配置

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

# mysql服务ID，保证整个集群环境中唯一
server-id = 2

# 1表示只读，0表示读写
read-only = 1

从库只要负责查询操作，不需要负责写入操作，仅针对普通用户，无法限制超级管理员，可以通过

super-read-only = 1

来限制超级管理员的只读

2、重启mysql服务

3、登录mysql 设置主库配置

CHANGE REPLICATION SOURCE TO

SOURCE_HOST='P1',SOURCE_USER='P2',SOURCE_PASSWORD='P3',

SOURCE_LOG_FILE='P4',SOURCE_LOG_POS='P5';

P1：主服务器的IP地址或主机名。

P2：连接到主服务器的MySQL用户名。

P3：上述用户的密码。

P4：开始复制的二进制日志文件名。

P5：开始复制的二进制日志位置。

4、开启同步

start replica;

5、查看同步

show replica status;

分库分表

单数据库存在以下性能瓶颈：
1、IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低；请求数据太多，带宽不够，网络IO效率低
2、CPU瓶颈：排序、分组、连接查询、聚合统计等sql会消耗大量的CPU资源
以上两种情况很影响服务器的性能

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

拆分策略

垂直拆分

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

特点：
1、每个库的表结构都不一样
2、每个库的数据也不一样
3、所有库的并集是全量数据

垂直分表：以字段为依据，将不同字段拆分到不同的表中

特点：
1、每个表的结构都不一样
2、每个表的数据也不一样，一般通过主键/外键关联
3、所有表的并集是全量数据

水平拆分

水平分库：以字段为依据，按一定策略，将一张表中的数据分散存储在多个数据库中，每个数据库中都有一张相同结构的表

特点：
1、每个库的结构都一样
2、每个库的数据都不一样
3、所有库的并集是全量数据

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

特点：
1、每个表的结构都一样
2、每个表的数据都不一样
3、所有表的并集是全量数据

实现技术：
1、shardingJDBC：基于AOP原理（面向切面的编程，对业务逻辑进行隔离，降低耦合度，提高重用性和开发效率），在应用程序中对本地执行的sql进行拦截、解析、改下、路由处理，只支持java语言
2、mycat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者