MySQL笔记_5

日志

• MySQL中日志分为四类: 错误日志、二进制日志、查询日志、慢查询日志

错误日志

• 记录了当mysqld启动和停止时，以及服务器在运行过程中发生的任何严重错误时的相关信息。服务器出现故障无法使用就看这个日志
• 默认开启，存放在/var/log/中，日志文件名为mysqld.log
• 查看错误日志位置: show variables like '%log_error%';

二进制日志

binlog是server层日志

• binlog记录了所有的DDL, DML语句，不包含查询语句
• binlog默认开启，查看二进制日志位置: show variables like '%log_bin%';
• 最开始MySQL只有MyISAM引擎，只有binlog用于归档，没有crash-safe的能力，后面InnoDB设计了另一套日志系统，利用redo log来实现crash-safe的能力

作用

1. 灾难时数据恢复
2. MySQL主从复制

日志格式

• 查看二进制的日志格式: show variables like '%binlog_format%';

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

• 如果要修改二进制文件的日志格式，就去修改/etc/my.cnf，添加binlog_format=STATMENT后，使用systemctl restart mysqld重新启动服务即可

查看二进制日志

mysqlbinlog [参数] binlog文件名
参数:

• -d 指定数据库名称，只列出指定数据库的相关操作
• -o 忽略日志中前n行命令
• -v 将行事件(数据变更)重构为SQL语句
• -vv 将行事件(数据变更)重构为SQL语句，并输出注释信息

删除二进制日志

配置二进制日志的过期时间，到了过期时间会自动删除: show variables like '%binlog_expire_logs_seconds%';

指令	含义
reset master	删除全部的binlog日志，删除之后日志编号从binlog.000001重新开始
purge master logs to ‘binlog.******’	删除******之前的所有日志
purge master logs before ‘yyyy-mm-dd hh24:mm:ss’	删除日志为’yyyy-mm-dd hh24:mm:ss’之前产生的所有日志

更新语句流程

1. 执行器调用引擎找到ID=2的一行，如果这一行的数据页本身就在内存中，那么就直接返回给执行器，否则读入内存后再返回
2. 执行器拿到引擎返回的数据，加1，调用引擎写入新数据
3. 引擎将数据更新到内存中，同时更新redo log，此时redo log处于prepare状态，告知执行器写入完成，可以提交事务。
4. 执行器生成binlog，并将binlog写入磁盘
5. 执行器调用引擎的提交接口，把redo log状态改为commit状态，完成更新

这里的prepare和commit就是两阶段提交，为了保证两个日志逻辑一致，所以只有在binlog写入完成以后，redo log才会从prepare变为commit

binlog记录的是数据操作的原始逻辑操作，也就是sql语句，所以回放的时候使用的是binlog

• 某时刻有一次删表操作，需要找到最近的一次全量备份，从备份的时间点开始，回放binlog，重放到误删表的时刻即可

如果不是两阶段提交, 例如更新ID=2的记录中c字段原始 = 0, 更新后 + 1

1. 先写redo log，后写binlog

   • redo log写完了，binlog没有写完就宕机了，恢复后系统依然可以得到c=1，但是binlog没有写完，所以binlog没有这个语句，备份日志就有语句丢失，如果需要使用binlog恢复恢复临时库，就少了一次更新，导致最终临时库中c=0, 与原始库不同

2. 先写binlog，后写redo log

   • binlog写完了，但是redo log还没有写，崩溃恢复以后事务无效，所以c=0，但是binlog已经将c=1的更新语句记录下来了，所以使用binlog进行恢复的时候就会得到c=1，与原始库不同

• 如果面临数据库的扩容问题的时候，就需要全量备份 + binlog，如果binlog和redo log记录的状态不一致，就会导致主从不一致问题

查询日志

• 查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询语句。
• 默认未开启，使用show variables like '%general%';查看

按照如下方式修改/etc/my.cnf可以打开查询日志

general_log=1
general_log_file=mysql_query.log # 设置日志的文件名，如果没有指定则默认为host_name.log

systemctl restart mysqld # 重启mysql服务

慢查询日志

• 记录所有执行时间超过参数long_query_time，并且扫描记录数不小于min_examined_row_limit的所有SQL语句的日志，默认未开启。
• long_query_time默认10秒，最小为0，默认文件名为localhost-slow.log
• 慢查询默认不会记录管理语句，也不会记录不使用索引进行的查询语句。使用log_slow_admin_statements和log_queries_not_using_indexes更改此行为，即

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

redo log

redo log是引擎层日志

• Writing-Ahead Logging(WAL)先写日志，再写磁盘，避免每次更新一条数据就要写入磁盘，导致大量的IO消耗
• 当一条记录需要更新，InnoDB就先写入到redo log中，并更新内存，就算更新完成。然后在适当的时候再将记录更新到磁盘中。因为写入日志是顺序写入的，比写入磁盘这种随机写入要快的多。
• redo log的大小是固定的，可以配置为一组4个文件，每个文件大小是1GB，从头开始写，写到末尾会重新从头写入
  

• writepos是当前写入的位置，边写边后移。
• checkpoint是当前擦除的位置，边擦边后移，擦除时将当前记录更新到数据文件中。
• writepos和checkpoint就是可以写入的位置，如果二者相等，表明此时不能写入新的数据了，需要先擦除一些记录，才能继续写入。
• InnoDB通过redo log保证数据库重启以后依然可以实现数据恢复，提交的记录都不会丢失，就是crash-safe

redo log VS binlog

1. redo log是InnoDB引擎特有的，binlog属于server层，所有引擎都可以使用
2. redo log是物理日志，记录在某个数据页上修改了什么；binlog是逻辑日志，记录原始逻辑，比如对ID = 2这行的c字段+1
3. redo log循环写，空间固定，会消耗完；binlog追加写入，一定大小后切换到下一个，不会覆盖以前的日志

redo log刷脏页

1. 内存数据页与磁盘数据页不同，内存数据页就是脏页，内存写入磁盘后，就是干净页
2. 更新写入redo log，没有写入磁盘，就是脏页
   • MySQL空闲，就会flush到磁盘上
   • MySQL正常关闭，会flush到磁盘
   • redo log写满了，没有办法接受行的请求，所有更新暂停，flush脏页
   • 内存不够用了，要先flush一部分
     • 读取数据的时候，需要申请行的页，那么如果没有内存空间了，就需要淘汰干净页。如果没有干净页，就需要flush脏页以后变成干净页再淘汰

控制刷脏页

1. 配置innodb_io_capacity参数，设置为磁盘的IOPS，告诉InnoDB磁盘能力，可以利用fio工具测试
   fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
   • 设置这个参数，可以控制全力刷脏页。如果设置的较低，就会导致刷脏页速度变慢
2. 控制全力刷脏页速度的占比，有两个因素，脏页再内存中占比和redo log写盘的速度

   • 配置参数innodb_max_dirty_pages_pct，默认75%，InnoDB会计算当前脏页的占比M

   • 每次日志记录都有编号，跟checkpoint对应序号的差输入函数计算，得到N，N越大，计算结果越大。最后N和M取最大值，然后取百分号乘上全力刷脏页的速度，并以这个速度进行flush
     

   • 脏页的比例通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total计算的得到
     select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';select @a/@b;

   • 刷新脏页的时候，如果旁边也是脏页，就会继续刷新旁边的页

     • innodb_flush_neighbors = 1就是连坐刷新
     • innodb_flush_neighbors = 0表示只刷自己

   • 机械硬盘的话，连坐刷新有意义；但如果是SSD，建议设置为0，8.0版本后默认为0

总结

1. redo log用于恢复主机故障时没有更新的数据；binlog用于备份
2. 恢复数据时:
   • 如果redo log的状态为commit，表明binlog也是成功的，就可以直接恢复数据；
   • 如果redo log状态是prepare，则需要查询binlog对应的事务是否成功，来决定是需要回滚还是执行
3. 因此，redo log保证了crash-safe的能力，innodb_flush_log_at_trx_commit参数表示redo log持久化方式
   • innodb_flush_log_at_trx_commit = 1表示每次事务的redo log都持久化到磁盘上，可以保证每次异常重启以后数据都不会丢失；
   • sync_binlog = 1表示每次事务的binlog都会持久化到磁盘
   • 所以这两个参数都建议设置为1
   • 两个参数有0 1 2三个值可选，
     • 0表示每秒将log buffer同步到os buffer，并刷新到磁盘；
     • 1表示每个事务都刷新到磁盘；
     • 2表示每个事务提交都将log buffer同步到os buffer中，但是每秒刷新到磁盘

45讲对于日志的叙述

1. redo log在prepare状态，写入binlog之前，崩溃了，恢复后因为redo log没有提交，binlog没有写，事务回滚，并且由于没有写binlog，所以从库无影响
2. redo log没有commit, 写入binlog之后，
   • 如果redo log事务完整，也就是已经有了commit标识(但是还没有commit)，就直接提交
   • 如果redo log只有prepare，判断binlog是否存在并完整
     • 如果binlog存在并完整，则提交事务
     • 否则回滚事务
     • binlog判断完整性：
       • statement格式binlog的最后会有COMMIT
       • row格式binlog最后会有XID EVENT
       • 5.6版本之后引入了binlog-checksum验证binlog完整性

   所以只要binlog完整，就可以通过redo log恢复数据，因为binlog完整的话，从库就会拿去用，就需要主库也有同样的数据

3. 如果没有两阶段提交，那么redo log写好了，就无法回滚，此时如果binlog写入失败，也无法回滚，导致不一致
4. 只有binlog无法支持崩溃恢复，因为binlog是WAL的，如果先写了，但是事务没有commit，此时崩溃，就会不一致
5. 如果只有redo log，可以崩溃恢复，也不需要两阶段提交。但是binlog可以归档，而且redo log循环写，历史无法保留；并且高可用，依赖监听binlog
6. redo log可以直接设置为4个文件，每个1GB。
   • innodb_log_file_size指定每个redo log大小，默认48M
   • innodb_log_files_in_group指定 个数，默认2
7. redo log没有记录页面的完整数据，所以不能更新磁盘，数据最终写入都依赖内存数据页的脏页

有两张表，like和friend，任何一方单方面关注的时候，只会添加在like表中，如果互相关注，需要在friend表中添加。如果两个人同时点击关注对方，就有可能出现两个单方面关注，friend表为空。
- like表添加一个字段relationship
- 为1表示userid关注likeid
- 为2表示likeid关注userid
- 为3表示互相关注
- 设定userid < likeid

• 插入数据比较两个用户id(A B)大小
  • 如果A < B

    begin; /*启动事务*/
    insert into `like`(user_id, liker_id, relation_ship) values(A, B, 1) on duplicate key update relation_ship=relation_ship | 1;
    select relation_ship from `like` where user_id=A and liker_id=B;
    /*
    代码中判断返回的 relation_ship，
    如果是1，事务结束，执行 commit
    如果是3，则执行下面这两个语句：
    */
    insert ignore into friend(friend_1_id, friend_2_id) values(A,B);
    commit;

  • 如果A > B

    begin; /*启动事务*/
    insert into `like`(user_id, liker_id, relation_ship) values(B, A, 2) on duplicate key update relation_ship=relation_ship | 2;
    select relation_ship from `like` where user_id=B and liker_id=A;
    /*
    代码中判断返回的 relation_ship，
    如果是2，事务结束，执行 commit
    如果是3，则执行下面这两个语句：
    */
    insert ignore into friend(friend_1_id, friend_2_id) values(B,A);
    commit;

  • insert on duplicate强制占住一行，select relationship在行锁保护下进行
  • | 按位或 和 insert ignore避免重复调用

主从复制

• 主从复制就是将主数据库的DDL, DML语句通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行(重做)，从而使主从库数据同步
• MySQL支持一台主库同时向多台从库进行复制，从库也可以作为其他服务器的主库，实现链状复制

优点

1. 主库出现问题，快速切换从库提供服务(高可用)
2. 实现读写分离，降低主库访问压力
3. 在从库备份，避免备份期间影响主库服务

原理

主数据库执行DML, DDL语句后，会写入binlog日志中。此时从数据库的IOthread会读取主数据库的binlog，写入自己的中继日志Relay log中，然后从数据库的SQLthread会读取Relay log，并重做其中的DML, DDL语句，实现主从复制

主从复制分三步:

1. master在事务提交时，把数据变更记录在二进制日志文件binlog中
2. 从库读取主库的binlog，写入到从库的中继日志Relay Log中
3. slave重做中继日志的事件，改变自己的数据

主库配置

1. 修改/etc/my.cnf

   # MySQL服务ID，保证集群环境中唯一，取值范围为(1 ~ 2^32-1)，默认为1
   server-id=1
   # 是否只读，1表示只读，0表示读写
   read-only=0
   # 忽略的数据，指不需要同步的数据库
   # binlog-ignore-db=mysql
   # 指定同步的数据库
   # binlog-do-db=db01
   # 不指定这两个设置，则表示所有数据库都需要同步

2. 重启MySQL服务: systemctl restart mysqld
3. 登录MySQL，创建远程连接账号，授予主从复制权限

   -- 创建用户，并设置密码，可以在任意主机连接mysql服务
   create user '用户名'@'%' identified with mysql_native_password by '密码';
   -- 为用户分配主从复制权限
   grant replication slave on *.* to '用户名'@'%';

4. 查看二进制日志坐标: show master status;

   字段含义:
   file: 从哪个日志文件开始推送
   position: 从日志文件的哪个位置开始推送日志
   binlog_ignore_db: 指定不需要同步的数据库

从库配置

1. 修改/etc/my.cnf

   # MySQL服务ID，保证集群环境中唯一，取值范围为(1 ~ 2^32-1)，和主库不一样即可
   server-id=2
   # 是否只读，1表示只读，0表示读写
   read-only=1

   • 从库的read-only=1表示从库只需要查询即可
   • 但是这个只读只是针对普通用户权限，对超级管理员仍然是可写
   • 如果需要禁止超级管理员的写权限，则需要在/etc/my.cnf中再指定一个super-read-only=1参数
2. 重启MySQL服务: systemctl restart mysqld
3. 登录从库的MySQL，设置主库配置

   change replication source to source_host='主机ip', source_user='用户名', source_password='密码', source_log_file='同步的日志文件', source_log_pos=日志文件开始同步的位置; --8.0.23
   
   change master to master master_host='主机ip', master_user='用户名', master_password='密码', master_log_file='同步的日志文件', master_log_pos=日志文件开始同步的位置; --8.0.23之前

4. 开启同步

   start replica; --8.0.22
   start slave; --8.0.22之前

5. 查看主从同步状态

   show replica status; --8.0.22
   show slave status; --8.0.22之前

   • 文件显示比较乱，可以在后面加上\G，就会按行显示:show replica status\G;
   • 主要看两行:
     • Replica_IO_Running: Yes
     • Replica_SQL_Running: Yes
     • 只要这两行开启了，主从复制就没有问题

分库分表

1. IO瓶颈: 热点数据太多，数据库缓存不足，产生大量磁盘I/O，请求数据太多，带宽不够
2. CPU瓶颈: 排序、分组、连接查询、聚合统计等SQL会消耗大量的CPU资源，请求数太多，CPU出现瓶颈

• 拆分维度分为垂直拆分和水平拆分，拆分粒度方面分为分库和分表

  1. 垂直分库: 以表为依据，将不同的业务拆分到不同数据库中
     特点: 每个库表结构不一样、库中数据不同，所有库的并集才是全部的数据
  2. 垂直分表: 以字段为依据，根据字段属性将不同字段拆分到不同的表中
     特点: 每个表结构不一样、每个表的数据不同，一般通过一列(主键/外键)关联、所有表的并集是全部数据
  3. 水平分库: 以字段为依据，将一个库的数据拆分到多个库中
     特点: 每个库表结构一样、每个库数据不同、所有库的并集是全部的数据
  4. 水平分表: 以字段为依据，将一个表的数据拆分到多个表中
     特点: 每个表的表结构一样、每个表的数据不同、所有表的并集是全部的数据
  • shardingJDBC: 基于AOP，在应用程序对本地执行的SQL进行拦截，解析，改写，路由处理。只支持Java，性能好
  • MyCat: 数据库分库分表的中间件，不用调整代码即可实现分库分表，支持多种语言
  • 使用mysql一样的链接命令可以登录mycat，只有端口不一样，mycat端口是8066

MyCat结构

• 所有数据都存储在物理结构内
  

• 分片规则决定了某个数据应该插入到哪一个库的表中，一般水平分表的时候需要指定，默认是auto-sharding-long，其中规则为:根据id选择对应的数据库，但是如果超过了1500万，则直接报错

MyCat配置

1. schema.xml包含三组标签: schema, datanode, datahost
   • schema核心属性:
     • name: 指定自定义的逻辑库库名
     • checkSQLschema: 在SQL语句操作时指定了数据库名称，执行时是否自动去除。如果为true，表示第一次指定一下，后面就可以不用再指定数据库名称了。如果为false，则表明需要使用use 数据库名;进行指定
     • sqlMaxLimit: 如果没有指定limit进行查询，列表查询模式返回查询了多少条记录
   • schema.table核心属性:
     • name: 定义逻辑表表名，在该逻辑库下唯一
     • dataNode: 定义逻辑表所属的dataNode，需要与dataNode标签中的name对应，多个dataNode使用逗号分割
     • rule: 分片规则名字，在rule.xml中定义
     • primaryKey: 逻辑表对应真实表的主键
     • type: 逻辑表的类型，只有全局表和普通表，默认为普通表。
   • dataNode核心属性:
     • name: 定义数据节点名称
     • dataHost: 数据库实例主机名称，引用自dataHost标签中的name属性
     • database: 定义分片需要的数据库
   • dataHost核心属性:
     • name: 唯一标识，供上层标签使用
     • maxCon/minCon: 最大/最小连接数
     • balance: 负载均衡策略，取值0,1,2,3
     • writeType: 写操作分发方式，0表示写操作转发到第一个writeHost，第一个挂了就转发到第二个，1表示写操作随机分发到配置的writeHost
     • dbDriver: 数据库驱动，支持native, jdbc
2. ruler.xml包含两类标签: tableRule、function
3. server.xml主要包含两个标签: system、user

MyCat分片

• 字典表类型的表可以设置成全局表

分片规则

1. 范围分片auto-sharding-long
2. 取模mod-long
3. 枚举sharding-by-intfile-enumstatus
4. 一致性哈希sharding-by-murmur: 计算指定的哈希值，根据哈希值计算落在哪一个逻辑表中。相同的哈希因子计算值总是划分到相同的分区表中，不会因为分区节点的增加而改变原来数据的分区位置
5. 应用指定sharding-by-substring: 运行阶段由应用自主决定路由到哪个分片，并直接根据字符子串(必须是数字)计算分片号
6. 固定分片哈希算法sharding-by-long-hash: 类似于十进制取模，但是为二进制操作。比如取低10位和1111111111进行位&运算。分片长度最大默认为1024
7. 字符串哈希解析sharding-by-stringhash: 截取子字符串，进行哈希算法，与1023进行位&运算得出分片
8. 按天分片sharding-by-date
9. 按月份分片sharding-by-month

固定分片哈希算法 VS 取模

1. 取模运算连续的值会分配到不同的分片，固定分片哈希算法中连续的值会分配到相同的分片，降低事务处理难度
2. 固定分片哈希算法既可以均匀分配，也可以不均匀分配
3. 分片字段必须是数字

MyCat管理

• 默认有两个端口，分别是8066和9066
  • 8066是数据访问端口，进行DML以及DDL操作
  • 9066是数据库管理端口，即MyCat的服务管理控制功能，用于管理MyCat的整个集群状态

命令	含义
show @@help	查看管理工具帮助文档
show @@version	查看mycat的版本
reload @@config	重新加载mycat的配置文件
show @@datasource	查看mycat的数据源信息
show @@datanode	查看mycat现有的分片节点信息
show @@threadpool	查看mycat线程池信息
show @@sql	查看执行的sql
show @@sql.sum	查看执行的sql统计

读写分离

• 主库写，从库读，配置MyCat的writeHost以及readHost

• 读写分离需要修改balance=1 or 3，问题在于主库宕机了，就只能进行查询了(不满足高可用性)

  参数值	含义
  0	不开启读写分离机制，所有读操作发送到当前可用的writeHost上
  1	全部的readHost与备用的writeHost都参与select语句的负载均衡(主要针对双主双从)
  2	所有的读写操作都随机在writeHost，readHost上分发 (没有读写分离)
  3	所有的读请求随机分发到writeHost对应的readHost上执行，writeHost不负担读压力

• 为了避免主数据库挂了以后服务宕机的问题，可以使用双主双从的设置。