Mysql高级

一、日志

1、错误日志

错误日志是MySQL中最重要的日志之一，它记录了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。
该日志默认开启，存放目录/var/log/，默认的日志文件名为mysqld.log。
查看日志位置： show variables like '%log_error%'

2、二进制日志

（1）含义

二进制日志 binlog 记录了所有的DDL和DML语句，但不包括数据查询语句（SELECT、SHOW）。
作用：

• 灾难时的数据恢复
• Mysql主从复制

查看日志 show variables like '%log_bin%'

（2）日志格式

MYSQL服务器中提供了多种格式来记录二进制日志，查询格式语句：show variables like '%binlog_format%'。
具体格式如下：

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

修改日志格式：vim /etc/my.cnf binlog_format=STATEMENT

（3）日志 删除

指令	含义
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配置文件配置过期时间

show variables  like '%binlog_expire_logs_seconds%';

3、查询日志

查询日志中记录了客户端的所有操作语句。默认情况下，查询日志是关闭的。

show variables like '%general%';

修改mysql的配置文件/etc/my.cnf文件，添加以下内容

# 开启查询日志： 0:关闭，1:开启
general_log=1
#日志文件名，默认host_name.log
general_log_file=mysql_query.log

4、慢查询日志

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

#开启慢查询日志
slow_query_log=1
#执行时间参数
long_query_time=2

二、主从复制

1、概述

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

优点:

• 主库出现问题，可以快速切换到从库提供服务。
• 实现读写分离，降低主库的访问压力。
• 可以在从库中执行备份，以免备份期间影响主库服务。

2、原理

主从复制分为三步：

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

3、搭建

（1）主库配置

修改配置文件my.cnf

#mysql服务ID,默认为1
server-id=1
#是否只读，1代表只读，0代表读写
read-only=0

重启Mysql服务器

systemctl restart mysqld

登录mysql，创建远程连接账号，并授予主从复制权限

CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';

GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';

查看二进制日志坐标

show master status;

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

（2）从库配置

修改配置文件my.cnf

server-id=2
read-only=1

重启服务

systemctl restart mysqld

登录mysql，设置主库配置

# 8.0.23版本
CHANGE REPLICATION SOURCE TO SOURCE_HOST='xxx.xxx',SOURCE_USER='xxx',SOURCE_PASSWORD='xxx',SOURCE_LOG_FILE='xxx',SOURCE_LOG_POS='xxx';

# 8.0.23之前版本
CHANGE MASTER TO MASTER_HOST='xxx.xxx',MASTER_USER='xxx',MASTER_PASSWORD='xxx',MASTER_LOG_FILE='xxx',MASTER_LOG_POS='xxx';

开启同步操作

start replica;
start slave;

查看主从同步状态

show replica status;
show slave status;

三、分库分表

（1）介绍

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

拆分策略

策略	含义	特点	图例
垂直分库	以表为依据，根据业务将不同表拆分到不同库中	1、每个库的表结构都不一样。2、每个库的数据也不一样。 3、所有库的并集是全量数据。	图1
垂直分表	以字段为依据，根据字段属性将不同字段拆分到不同表中	1、每个表的结构都不一样。2、每个表的数据也不一样。 3、所有表的并集是全量数据。	图2
水平分库	以字段为依据，按照一定的策略，将一个库的数据拆分到多个库中	1、每个库的表结构都一样。2、每个库的数据不一样。 3、所有库的并集是全量数据。	图3
水平分表	以字段为依据，按照一定的策略，将一个表的数据拆分到多个表中	1、每个表的结构都一样。2、每个表的数据都不一样。 3、所有表的并集是全量数据。	图4

（2）MyCat2

略

3、分配规则

1. 范围：根据指定的字段及其配置的范围与数据节点的对应情况，来决定该数据属于哪一个分片。
2. 取模：根据指定的字段值与节点数量进行求模运算，根据运算结果，来决定该数据属于哪一个分片。
3. 一致性hash：相同的哈希因子计算值总是被划分到相同的分区表中，不会因为分区节点的增加而改变原来数据的分区位置
4. 枚举： 通过在配置文件中配置可能的枚举值,指定数据分布到不同数据节点上,本规则适用于按照省份、性别、状态拆分数据等业务。
5. 应用指定： 运行阶段由应用自主决定路由到那个分片，直接根据字符子串(必须是数字)计算分片号
6. 固定分片hash算法：该算法类似于十进制的求模运算，但是为二进制的操作，例如，取 id 的二进制低 10 位 与 1111111111 进行位& 运算。
7. 字符串hash解析：截取字符串中的指定位置的子字符串,进行hash算法，算出分片
8. 按(天)日期分片：天数间隔
9. 自然月：使用场景为按照月份来分片,每个自然月为一个分片

四、读写分离

1、介绍

把对数据库的读和写操作分开，以对应不同的数据库服务器。主数据库提供写操作，从数据库提供读操作，这样能有效的减轻单台数据库的压力。
通过MyCat即可轻易实现上述功能。

2、一主一从读写分离

MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件中datahost标签的balance属性控制。balance默认是0.

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

3、双主双从读写分离

（1）介绍

一个主机master1用于处理所有的写请求，它的从机slave1和另一台主机master2，还有它的从机slave2负责所有读请求。当master1主机宕机后，master2主机负责写请求，master1、master2互为备机。

（2）搭建

• 主库配置
  修改配置文件/etc/my.cnf

#两个主库的server_id不一样,其他一样
server_id=1
#指定同步的数据库
binlog-do-db=db101
binlog-do-db=db102
binlog-do-db=db103
#在作为从数据库的时候，有写入从操作也要更新二进制日志文件
log_slave_updates

重启mysql服务器

systemctl restart mysqld

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

#创建itcast用户，并设置密码，该用户可在任意主机连接该MySOL服务
CREATE USER 'itcast@'%' IDENTIFIED WITH mysql native password BY Root@123456'
#为“itcast @'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO itcast @'%';

通过指令，查看两台主库的二进制日志坐标

show master status 

• 从库配置 (slave)

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

#
#mysql 服务ID，保证整个集群环境中唯一，取值范围: 1 - 232-1，默认为1
server-id=2

重启MySQL服务器

systemctl restart mysqld

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

CHANGE MASTER TO MASTER HOST='xxxx, MASTER USER='x, MASTER PASSWORD='oxMASTER LOG FILE='x, MASTER LOG POS=xxx;

需要注意slave1对应的是master1，slave2对应的是master2.
启动两台从库主从复制，查看从库状态

start slave;
show slave status \G:

• 两台主库相互复制

Master2 复制 Master1，Master1 复制 Master2

CHANGE MASTER TO MASTER HOST='..0', MASTER USER='', MATER PASSWORD='xxMASTER LOG FILE=', MASTER LOG POS=xxx;

启动两台从库主从复制，查看从库状态

start slave;
show slave status \G

（3）读写分离

4、总结