mysql 日志学习

日志

 

错误日志

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

 

二进制日志

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

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

日志格式

1. STATEMENT：每一条会修改数据的 SQL 语句都会记录在 binlog 中。这种格式可能会有非确定性行为，因为在某些情况下，相同的 SQL 语句在不同的机器或时间点上执行可能会产生不同的结果（例如，如果涉及到当前时间或随机值）。
2. ROW：基于行的复制，即记录每一行被修改的内容。这种格式更加确定，但可能会产生更大的 binlog 文件。
3. 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：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者

 

 

 

读写分离