学习笔记0807

触发器

在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句
创建

CREATE TRIGGER trigger_name
BEFORE/ALTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW --行级触发器
BEGIN
	trigger_stmt;
END;

查看

SHOW TRIGGERS;

删除

DROP TRIGGER [schema_name.]trigger_name; --如果没有指定schema_name，默认为当前数据库

日志

错误日志记录MySQL发生严重错误时的错误信息

查看错误日志所在的文件

SHOW VARIABLES LIKE '%log_error%';

二进制日志记录了所有DDL和DML语句
作用：1、灾难时的数据恢复；2、MySQL的主从复制。在MySQL8中默认是开启的

show variables like '%log_bin%'

日志格式
STATEMENT 存SQL
ROW 存每一行的数据变更
MIXED 两种都有，默认采用STATEMENT，特殊情况切换为ROW
查看二进制日志的方式

mysqlbinlog [参数选项] logfilename

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

删除日志
reset master 删除全部日志
purge master logs to ‘binlog.******’ 删除******编号之前的所有日志
purge master logs before ‘yyyy-mm-dd hh24:mi:ss’ 删除日志为’yyyy-mm-dd hh24:mi:ss’之前产生的所有日志

配置日志过期时间

show variables like '%binlog_expire_logs_seconds%';

查询日志中记录了客户端所有操作，默认情况下是未开启的。

show variables like '%general%';

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

#该选项用来开启查询日志 0关闭1开启
general_log=1
#设置日志的文件名，默认为host_name.log
general_log_file=mysql_query.log

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

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

默认情况下，不会记录管理语句，不会记录不适用索引进行查找的语句。更改此行为的方式

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

主从复制

主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从数据库中
支持一台主库同时向多台从库进行复制
作用：
主库出现问题，可以快速切换到从库提供服务
实现读写分离，降低主库的访问压力
可以在从库中执行备份，以避免备份期间影响主库服务

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

#mysql服务ID，保证整个集群环境中唯一，取值范围1-2的32次方-1，默认为1
server-id=1
#是否只读，1代表只读，0代表读写
read-only=0
#忽略的数据，指不需要同步到数据库
#binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01

主库配置
登录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：指定不需要同步的数据库
从库配置
登录mysql，设置主库配置

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.xxx.xxx',MASTER_USER='xxx',MASTER_PASSWORD='xxx',MASTER_LOG_FILE='xxx',MASTER_LOG_POS=xxx;

开启同步操作

start replica; #8.0.22之后
start slave;   #8.0.22之前

查看主从同步状态

show replica status; #8.0.22之后
show slave status;   #8.0.22之前

分库分表

单数据库存在问题
IO瓶颈
CPU瓶颈

分库分表是将数据分散存储，使单一数据库/表的数据量变小来缓解单一数据库的性能问题

拆分策略
垂直拆分：垂直分库、垂直分表
水平拆分：水平分库、水平分表

垂直分库：以表为依据，根据业务将不同表拆分到不同库中
特点：
1.每个库的表结构都不一样。
2.每个库的数据也不一样。
3.所有库的并集是全量数据。
垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中
特点
1.每个表的结构都不一样。
2.每个表的数据也不一样，一般通过一列（主键/外键）关联。
3.所有表的并集是全量数据。
水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中
特点：
1.每个库的表结构都一样。
2.每个库的数据都不一样。
3.所有库的并集是全量数据。
水平分表：以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。
特点：
1.每个表的表结构都一样。
2.每个表的数据都不一样。
3.所有表的并集是全量数据。

实现技术
shardingJDBC 只支持java语言，性能较高
MyCat 性能不及前者

Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。可以像使用MySQL一样使用MyCat
优势：
性能可靠稳定
强大的技术团队
体系完善
社区活跃