Mysql的入门到精通

Mysql入门到精通

一、基础部分弥补

1、所有聚合运算Null不参与运算

2、Having可以运行聚合函数where不允许

3、执行顺序

FROM–>WHERE–>GROUP BY–>HAVING–>SELECT–>ORDER BY–>LIMIT

4、DDL数据定义语言||DML数据操作语言||DQL数据查询语言||DCL数据控制语言(创建数据库用户，控制用户权限等)

5、外键约束的级联

alter table 表名 add constraint 外键名称 foreign key 外键字段 reference 主表名（主表字段名） on update cascade on delete cascade

6、内连接（查询是的A B 两表的交集部分）

​ 隐式内连接

select 字段列表 form 表1，表2 where 条件

​ 显示内连接

select 字段列表 form 表1 [inner]join 表2  on  条件

7、外连接（查询的是外联的所有数据以及 与另一表的交集部分数据，即使左右外联没有数据也会查询）

左连接：

select 字段列表 form 表1 left [outer] join 表2  on  条件

右连接：

select 字段列表 form 表1 right [outer] join 表2  on  条件

8、自连接（表自己和自己建立笛卡尔积）

select 字段列表 form 表1  别名1  join 表1 别名2 on  条件

9、联合查询

union all  --直接进行组合
union  	   --组合并去重

10、事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或者撤销操作请求，即这些操作要么同时成功，要么同时失败。

select @@autocommit;  --自动提交
set @@autocommit=0    --开启手动提交
commit --提交事务

-----------------------------------------------------
start transaction

--具体事务操作

commit --提交事务

rollback  --事务回滚

11、事务的四大特性

1、原子性：不可分割，要么成功，要么都失败

2、一致性：事务完成时，必须使所有数据保持一致

3、隔离性：数据库的隔离机制，A 事务不会影响B事务

4、持久性：事务一旦提交或回滚事务，数据就永久改变

12、并发事务引发的问题

1、脏读：一个事务读取到另一个事务还没有提交的数据

2、不可重复读：一个事务先后读取同一数据，但 2次读取的数据不同，称之为不可重复读。

3、幻读：一个事务按条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据以及存在，好像出现了幻影。

13、事务的隔离级别

select @@transaction_isolation --查看事务的隔离级别
set [session|global] transcation isolation level {以上四种} --会话级别以及全局级别

二、高级部分

1、Mysql体系结构

结构：
1、连接层：连接池
2、服务层：sql接口、解析器、查询优化器、缓存
3、存储引擎层：可拔插存储引擎（不同的存储引擎的索引机构是不一样的，innodb是5.5以后mysql默认的引擎）
4、存储层：将数据存储在文件系统上。

2、存储引擎

**定义：**存储引擎就是存储，建立索引，更新/查询数据等技术的实现方式。存储引擎是基于表的而不是基于库的，所以存储引擎也可被称为表类型。

Mysql5.5以后默认引擎Innodb，兼顾高可靠性，高可用性。(支持 事务、外键、行级锁)，每个innodb表都有一个对应的xxx.idb文件，存储了该表的结构、数据和索引。

create table 表名(
)engine=innodb
show engine --可查看数据库可支持的引擎。
innodb_file_per_table  --每个表是否对应一个idb文件保存该表的结构
idb2sdi account.idb   --控制台输入可以查看idb文件的数据结构

Innodb的了逻辑存储结构：

Extent（区） 一个区的大小是固定的  1M
Page （页）是磁盘操作的最小单元 16K

关于MyISAM（Mysql早期引擎）

特点：不支持事务、支持表锁、不支持行锁、访问速度快。

自上到下分别为：数据文件-->索引文件-->以及表结构文件

关于Memory引擎，表数存储在内存中，只能作为临时表以及缓存使用，访问速度快

特点：支持hash索引，支持表锁

文件：只有sdi表结构文件

---

3、存储引擎的选择

4、MysqlLinux版本安装

1、下载网站（我的虚拟机为centos7）

https://dev.mysql.com/downloads/mysql/

2、创建一个mysql文件将压缩包复制到文件下并解压

tar -xvf  mysql-8.0.30-1.el7.x86_64.rpm-bundle.tar  --解压

3、安装（rpm不会处理安装包的依赖关系，需要我们按顺序进行安装）

1、rpm -ivh mysql-community-common-8.0.30-1.el7.x86_64.rpm

2、rpm -ivh mysql-community-client-plugins-8.0.30-1.el7.x86_64.rpm

3、rpm -ivh mysql-community-libs-8.0.30-1.el7.x86_64.rpm
(3步骤可能出现的问题mariadb-libs 被 mysql-community-libs-8.0.30-1.el7.x86_64 取代，解决yum remove mysql-libs

4、rpm -ivh mysql-community-libs-compat-8.0.30-1.el7.x86_64.rpm

5、rpm -ivh mysql-community-devel-8.0.30-1.el7.x86_64.rpm
(5、可能需要插件：yum install openssl-devel)

6、rpm -ivh mysql-community-client-8.0.30-1.el7.x86_64.rpm

7、rpm -ivh mysql-community-icu-data-files-8.0.30-1.el7.x86_64.rpm 

8、 rpm -ivh mysql-community-server-8.0.30-1.el7.x86_64.rpm
(8、可能出现的问题net-tools 被 mysql-community-server-8.0.30-1.el7.x86_64 需要,解决办法：yum install net-tools)

4、启动服务

systemctl start mysqld  --启动服务
systemctl restart mysqld  --重启服务
systemctl stop mysqld  --停止服务

5、登入

mysql -u root -p      --root用户登入(初次登入可以进入/var/log/mysqld.log目录下查看rpm自动生成的初始化密码)
ALTER USER 'root'@'localhost' IDENTIFIED BY '1234';
set global validate_password.policy = 0;  --设置密码安全等级
set global validate_password.length = 4;  --设置密码长度

可能存在的问题:修改权限提示你要先修改密码，这里直接复制初始密码修改一位，再修改权限再修改密码。

6、设置用户访问

create user 'root'@'%' identified with mysql_native_password by '1234';
<!-- %通配符意味所有ip都可访问-->

7、分配权限

grant all on *.* to 'root'@'%';   //所有权限

### 查询端口是否开放
firewall-cmd --query-port=8080/tcp

### 开放80端口
firewall-cmd --permanent --add-port=80/tcp

### 移除端口
firewall-cmd --permanent --remove-port=8080/tcp

### 重启防火墙(修改配置后要重启防火墙)
firewall-cmd --reload

8、测试远程连接，测试前开启3306端口

### 查询端口是否开放
firewall-cmd --query-port=8080/tcp

### 开放80端口
firewall-cmd --permanent --add-port=80/tcp

### 移除端口
firewall-cmd --permanent --remove-port=8080/tcp

### 重启防火墙(修改配置后要重启防火墙)
firewall-cmd --reload

firwall-cmd：是Linux提供的操作firewall的一个工具；
--permanent：表示设置为持久；
--add-port：标识添加的端口；

5、索引

5.1、索引的结构

定义：索引是一种高效有序的数据结构来帮助我们获取数据。

索引的结构：

B树演变过程网站：

https://www.cs.usfca.edu/~galles/visualization/BTree.html

面试题：为什么Innodb的数据结构采用B+树

答：

1、采用二叉树的话顺序插入的层级会增加，检索的时候次数增加。
2、采用红黑数的话，红黑树也是二叉树都是二阶树。
3、B-tree虽然解决了上述2个问题但是，但是他每个节点都会保存数据，因为每个块的大小为16k，减少每个节点存储效率，检索性能降低。
4、B+Tree只在根节点存储数据的侧率解决了上述三个问题。

5.2、索引分类

聚集索引的选取规则：

1、如果存在主键，主键索引就是聚集索引。

2、如果不存在主键，将使用第一个唯一索引作为聚集索引

3、如果上述都不存在，则innodb将会自动生成一个rowid作为隐藏的聚集索引。

从二级索引到聚集索引的查询的过程叫做回表查询。

5.3、索引的语法

1、创建(联合索引，或单例索引的区别在于索引关联的字段数量)

create [unique|fulltext] index index_name on table_name(index_col_name,....)

2、查看索引

show index from table_name
show index from table_name\G

3、删除索引

drop index index_name on table_name

5.4、SQL性能分析

show [session|global] status like'Com_______'    查看查询、修改等操作次数

SQL的慢查询日志：

查看慢查询日志开启情况
show variables like 'slow_query_log';

show_query_log=1  //开启慢查询
long_query_time=2 //查询时间超过2秒就记录sql
	
慢sql日志存放目录：/var/lib/mysql/localhost-slow.log


tail -f localhost-slow.log  //实时监测sql

Profile使用：

select @@have_profiling;   //查看是否支持
select @@profiling         //查看开启的状态
set global profiling = 1;  //开启
执行sql后
show profile  			   //可以查看sql的查询情况
show profile for query query_id; //查看具体sql各个阶段执行情况
SHOW PROFILE cpu FOR QUERY query_id;   //查看sqlcup使用情况

Expain执行计划：

explain +sql

id 用来表示执行顺序，id相同自上而下，id越大执行优先级越高。
select_type :
1、Simple 简单表 2、Primary 主查询 或外层查询

possible_key 显示可能出现的索引，一个或多个。

非唯一索引会出现 ref
唯一索引则是    const
遍历所有索引    index
遍历所有表      all

Extra 表示额外信息。

5.5、索引的使用

1、最左前缀法则（主要针对联合索引）：

1、查询的时候必须针对联合索引进行添加查询条件，中间有跳过或者第一个索引不存在，则后面的索引将不会生效。查询条件顺序可以打乱。

2、进行范围查询时尽量运用 >=,<=,使用<>会导致右侧的查询条件不使用索引进行查询。

2、索引列运算操作：

1、运算操作导致索引失效

3、字符串：

1、字符串不用单引号导致索引失效

4、模糊查询：

如果仅仅是尾部进行模糊匹配索引不会失效，头部则会失效。

5、or连接的条件：

or 2个条件都有索引才会使用索引。

6、数据分布影响

如果查询数据占全表比例较大，则不会使用索引。

7、Sql提示

指定查询语句使用哪一索引

select * from table_name user index(index_name) where XXXXX			//建议
select * from table_name ignore index(index_name) where XXXXX		//忽略
select * from table_name force index(index_name) where XXXXX      //强制

8、覆盖索引

覆盖索引指的是查询列在索引中都能找到，避免使用select *。

extra展示信息：
using index condition   //采用回表查询效率变低
using where,using index //B+Tree中能找到对应数据不需要回表

9、前缀索引

当一个varchar text 存储一个很长的字符串，我们对该字段建立索引会导致查询效率降低，于是我们采用将它前面的一些字符采用前缀索引来实现。
create index  index_name on table_name(column(n))   //该字段前n个字符建立索引

select count(distinct,substring(filed_name,start_index,number))/count(filed_name) from table_name.

上述执行结果选择性，按需求进行设置n的大小。
注意：当我们执行查询时，不会第一时间换回结构，而是会去比对结果是否正确再决定返回反之则继续查询。

10、关于单列索引和联合索引的选择问题

联合索引优先级高于单列索引

5.6、索引设计原则

6、SQL优化

6.1、插入数据优化

1、批量插入

2、手动事务提交

3、主键顺序插入**（顺序插入的新能高于乱序插入）**

关于大批量的数据加入:

mysql --local-infile -u root -p 				//连接本地客户端是加载本地文件

set global local_infile = 1						//开启从本地加载文件导入数据开关

load data local infile ‘地址/XX.sql’ into table 'table_name' fields terminated by ',' lines terminated by '\n'
格式如上图

6.2、主键优化

表是根据主键顺序组织存放的，主键乱序插入可能导致页分裂。将50%加入新页然后改变页指针指向

关于页合并现象：

对表数据进行删除，会导致该数据对应的页所载的地址被声明可以被使用，但是不会真正出现删除。当该页标识可以被使用的记录达到50%，他会查看上一页或者下一页有没有可能性合并，并以优化空间使用。

MERGE_THRESHOLD:合并阈值默认是50%。

关于主键的设计原则：

1、尽量降低主键的长度
2、插入数据尽量选择顺序插入
3、尽量不要使用UUID，等非顺序结构数据（导致页分裂）
4、业务操作避免对主键的操作

6.3、order by 优化

1、using filesort

通过索引或者全表扫描获取满足条件的数据行，然后再缓冲区进行排序，所有不是通过索引直接返回排序结果的排序都叫filesort

2、using index

通过有序索引扫描直接返回有序数据，这种情况就叫using index 

【使用联合索引是：如果违背最左前缀发，或者一个desc，一个asc会导致使用filesort降低效率】

解决：
creat index index_name  on table_name(field1 asc ,field2 desc)
----------------------------------------------------------------------------------------
show variables like 'sort_buffer_size';  //默认256k，超过就会使用磁盘，可以适当提高缓冲区大小

6,4、group by 优化

适当建立联合索引，进行优化。
也必须满足最左前缀法则， group by 前where 加上 索引第一个字段也满足最左前缀法则。

6.5、Limit 优化

可以通过覆盖查询将limit结果表与原表建立笛卡尔积 通过table1.field=table2.field 来获取有用数据

6.6、Count 优化

MyISAM   会将总数放在磁盘保存获取数据效率高，前提没有where条件

优化思路：
Redis 

count(*)            //有几行数据就返回多少，不会去值，直接累加
count(field)        //有几行数据field字段不为null就返回多少，会去取字段的值来判断是否为null
count(id)			//一般来说有几行数据就返回多少，id为主键的情况下
count(1)			//将查询的每行数据放一个数据1，每次count都会比对里面的数据是1就+1，不会取值

6.7、Update 优化

更新数据时一定要根据索引字段进行修改（行锁），反之则会触发（表锁）。

7、视图

【视图只保存查询sql的逻辑，不保存查询结果。动态生成】

7.1、视图相关操作

create [or replace] view view_name  as (查询语句); 

show create view view_name  --查看视图创建语句

select * from view_name where .....

Alter view view_name as (查询语句)

drop view [if exists] view_name ..

7.2、视图检查选项

insert into view_name values(field1,field2)  --插入必须满足视图创建时的条件

create [or replace] view view_name  as (查询语句) with cascaded check option; --开启检查

create [or replace] view view_name  as (查询语句) with local check option; --开启检查不会影响基表

cascaded：

不加不会影响视图但会影响基表
cascaded 会递归向上补全with cascaded check option【不满足的条件不会影响基表以及视图】
local    不会向上补全只会在当前视图生效

7.3、视图的更新

以下情况视图不可进行更新操作：

视图数据必须和原表有一一对应的关系，包换聚合函数例如count，group by等查找出来的数据无法进行更新操作。

7.4、视图的作用

1、简化操作
2、视图查询以及修改能见到的数据，无法对表结构进行修改
3、数据独立：视图可以帮助用户屏蔽真实表结构带来的影响。

8、存储过程

思想：将多条数据库语句进行封装，避免多次网络请求数据库。

8.1、基本语法

create procedure 存储过程名称 ([参数])
begin:
sql 语句
end;
---------------------创建
call 存储过程名称([参数])
---------------------调用	
1、select * from information_schema.Routines where routines_schema ='itcast'

2、show create procedure 存储过程名称;
---------------------查看
drop procedure if exists 存储过程名称;
---------------------删除
注意：在命令行中非结束分号(中间查询语句也有分号)，此时应提前定义语句结束符号
delimiter $$

create procedure 存储过程名称 ([参数])
begin:
sql 语句;
end$$

8.2、变量

1、系统变量 Mysql服务器提供的，分为全局变量和会话变量。
如何查看系统变量：
1、show [session|global] variables;
2、show [session|global] variables like '...';
3、select @@[session|global] 变量名；
默认是session

设置系统变量
set [session|global] 变量名 = ;

重启sql后系统变量会恢复到默认值，想要永久生效需要到/etc/my.cnf 中进行相关配置。

局部变量：

begin：
declare 变量名 数据类型 default 0; //设置默认值 

1、set 变量名 := 值;
2、select count(*) into 变量名 from Student;
3、select 变量名
end

8.3、条件判断

1、if else 分支结构

if 条件1 then

elseif 条件2 then

else

end if

2、case 结构

--语法1
Case case_valie
		when  when_value1 then statement_list1
		when  when_value2 then statement_list2
		when  when_value3 then statement_list3
		else statement_list
end case
--语法2
Case 
		when  条件1 then statement_list1
		when  条件2 then statement_list2
		when  条件3 then statement_list3
		else statement_list
end case

案例：
DELIMITER $$
CREATE PROCEDURE p1(IN score INT )
BEGIN 
	DECLARE result VARCHAR(10);
	CASE 
		WHEN score >80 THEN SET result :='好';
		ELSE SET result :='差';
	END CASE;
	
	SELECT CONCAT('您的结果为:',result);
		
END$$

CALL p1(100);

DROP PROCEDURE p1;

3、while分支

案例：
DELIMITER $$
CREATE PROCEDURE p1(IN n INT)
BEGIN 
	DECLARE res INT DEFAULT 0;
	WHILE n>=0 DO 
	 SET res:=n+res;
	 SET n:=n-1;
	END WHILE;
	SELECT res;
END$$

CALL p1(100);

DROP PROCEDURE p1;

4、repeat(满足条件退出循环)

案例:
DELIMITER $$
CREATE PROCEDURE p1(IN n INT)
BEGIN 
	DECLARE res INT DEFAULT 0;
	REPEAT 
	  SET res :=res + n;
	  SET n :=n-1;
	UNTIL n<=0
	END REPEAT;
	SELECT res;
END$$

CALL p1(100);

DROP PROCEDURE p1;

5、loop（不指定退出条件就是死循环）leave退出循环，iterate 类似于continue

案例（sum是指定loop循环名称）:
DELIMITER $$
CREATE PROCEDURE p1(IN n INT)
BEGIN 
	DECLARE res INT DEFAULT 0;
	SUM:LOOP
	IF n <=0 THEN LEAVE SUM;
	END IF;
	IF n%2=1 THEN  
	SET n :=n-1;
	ITERATE SUM; 
	END IF;
	SET res :=res+n;
	SET n :=n-1;
	END LOOP;
	SELECT res;
END$$

CALL p1(100);

DROP PROCEDURE p1;

8.4、参数

1、
create procedure 存储过程名称 (in score int，out result varchar(10))

begin:

    if 条件1 then

    elseif 条件2 then

    else

    end if

end;

调用：
call 存储过程名称(68,@result);


2、

create procedure 存储过程名称 (inout score int)

begin:

    set score:=score * 0.5;

end;

set @score=198;
call 存储过程名称(@score)
select @score
 

游标（存储查询结果集的数据类型）

declare 游标名称 cursor for 查询语句
open 游标名称
fetch 游标名称 into [变量|变量]
close 游标名称

一来说就说查询到相关表的数据，后通过游标循环遍历再创建数据表（inset into 操作）。

8.5、条件处理程序

--案例：
1、delcare exit handler for sqlstate '02000' close 游标名称;
2、
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN 
	DECLARE num INT DEFAULT 0;
	DECLARE EXIT HANDLER FOR  SQLEXCEPTION SELECT '报错';
	INSERT INTO test (id,NAME) VALUES (10000055555555,'hell');
END$$

CALL p1();

DROP PROCEDURE p1;

9、存储函数

--案例
DELIMITER $$
CREATE FUNCTION fun1 (n INT)
RETURNS INT DETERMINISTIC
BEGIN
   DECLARE res INT DEFAULT 0;
   
   WHILE n>0 DO
   
   SET res :=res+n;
   SET n :=n-1;
   
   END WHILE;
   
   RETURN res;

END$$

SELECT fun1(100);

10、触发器

1、创建
create trigger trigger_name
before/after insert/update/delete    --在增删改操作之前或者之后触发
on table_name for each row           --行级触发器
begin
	触发器内容
end;
2、查看
show trigger
3、删除
drop trigger [schema_name].tirgger_name;

案例:
DELIMITER $$
CREATE TRIGGER  tb_user_insert_tirgger
AFTER INSERT  ON USER FOR EACH ROW 
BEGIN

INSERT INTO user_log (id,opertion,opertion_time,opertion_id,opertion_params) VALUES
(NULL,'insert',NOW(),new.id,CONCAT('插入数据内容为：id=',new.id,' name=',new.name));
END$$


SHOW TRIGGERS

DROP test.`tb_user_insert_tirgger`

11、锁

11.1、全局锁

一般需要对数据库进行备份的时候需要对数据库全局加锁，保证可读但是无法就行增删改确保数据的一致性。

flush tables with read lock   --加锁

mysqldump -uroot -p password schema_name>xxxx.sql

unlock tables;				--释放锁

mysqldump --single-transaction -h192.169.10.130 -uroot -p test > C:/db.sql

mysqldump --single-transaction -uroot -p1234 test>/etc/db.sql

11.2、表级锁

表加锁

lock tables table_name read/write  --加锁

unlock tables;		

--读锁所有会话查询都可执行增删改不会执行，写锁只有开启写锁的会话可以进行crud操作其他会话阻塞。

11.3、元数据锁

避免数据定义语句与数据库数据操作语句之前的冲突，dml和ddl冲突。

11.4、意向锁

--查看意向锁情况
 select object_schema , lock_type,lock_mode from performance_schema.data_locks;
 
 SELECT object_schema , lock_type,lock_mode ,lock_data FROM performance_schema.data_locks;

1、意向共享锁（IS）

开启意向共享锁:
一般开启事务就无法开启写锁。在查询语句语句后添加lock in share mode会开启意向共享锁。
当我们开启事务对表数据进行查询，其他会话中无法开启表写锁。此时可以开启意向共享锁，只支持开启读锁。
但是开启读锁后事务就无法完成增删改操作。造成死锁问题

2、意向排它锁（IX）

开启意向排它锁：
开启事务后进行增删改操作，自动开启意向排它锁。与表读写锁都无法兼容。

11.5、行级锁

1、行级锁是基于B+Tree的索引项进行加锁，不是对记录加锁。

2、间隙锁是对索引记录pre和next进行加锁防止插入

3、临键锁是行级锁和间隙锁的组合

一、行锁

1、共享锁：读共享

2、排它锁：写互斥

获取共享锁时与排它锁也会产生互斥冲突。

重点：如果通过增删改操作的字段没有加索引，会对整个表加锁行锁升级为表锁

二、间隙锁

加锁操作在查询语句后面加上 lock in share mode;

当对索引进行等值查询（唯一索引），他会在索引之间添加锁，防止幻读的问题出现。

当对索引进行等值查询（普通索引），他会在索引前一个数据和后一个数据之间的间隙添加锁.一个左闭右开的区间 [ ) 。

当对索引进行范围查询（唯一索引），他会在索引范围内添加间隙锁，以及行锁 [ ]。

12、Innodb引擎

mysql Linux数据文件存放目录/var/lib/mysql

1、逻辑存储结构

1、表空间：
一个表空间（idb文件），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。
2、段：
分为数据段和索引段以及回滚段，数据段就是b+tree的叶子节点，索引段就是非叶子节点。段用来管理多个区
3、区：
表空间的单元结构，每个区的空间大小为1M包含64个16k的连续数据页
4、页：
innodb磁盘管理的最小单元
5、行：
innodb存储引擎数据时是按行进行存放的。

2、架构

2.1内存：

2.2磁盘架构：

2.3、后台线程

3、事务原理

4、MVCC

MVCC中的三个隐式字段

MVCC实现原理

undolog 
当 insert时事务回滚日志只在事务未提交前被需要，事务提交后，可被立即删除。
而update delete时产生的undolog日志不仅在回滚时需要，快照读也需要，不会被删除。

13、Mysql管理

常用mysql工具：

mysql -h192.168.10.130 -P3306 -uroot -p1234 test -e "select * from user"
--  -p 密码
-- -e 表示直接连接
-- test 数据库名

show variables like '%secure_file_priv%';   //导出文件的安全路径
mysqldump -uroot -p1234 -T /var/lib/mysql-files/ test

mysqlimport -uroot -p1234 test /var/lib/mysql-files/xxxx.txt
source /var/lib/mysql-files/xxx.sql  

三、运维部分

1、日志

1.1、错误日志

数据库启动和停止，运行过程中发生任何严重错误时相关信息。

目录： /var/log/mysqld.log show variables like ‘%log_error%’

tail -50  查看最后五十行文件内容
tail -f 查看实时输出的信息 

1.2、二进制日志

记录数据库DDL和DML语句包含select 和show

show variables like '%log_bin%'   --二进制日志开启情况以及文件位置
show variables like  '%binlog_format%' --查看二进制日志格式

删除二进制文件：

reset master --删除全部
purge master logs for ‘binlog.xxxxx'  --删除指定日志之前的日志
purge master logs before "yyyy-MM-dd hh:mm:ss"  --删除指定日期前的日志

例如：purge master logs before "2022-08-13 16:13:00";

show variables like '%binlog_expire_logs_seconds%' --设置二进制过期时间

1.3、查询日志

查询日志包含了所有操作语句，默认情况下查询日志是关闭的.

show variables like '%general%';

my.cnf配置如下
slow_query_log=1    --开启慢查询
long_query_time=2	--超过多少秒记录慢查询日志
general_log=1		--开启查询日志

1.4、慢查询日志

log_slow_admin_statement = 1 --记录执行较慢的管理语句
log_queries_not_using_indexes=1 --记录执行较慢没有使用索引的语句

2、主从复制

原理：是基于二进制日志，使用IoThread来读取主库二进制日志，写入到一个中继日志replay.log，此时又会分配一个线程SQLThread来读取replay.log中的日志写入从库。

2.1、搭建

主库配置：

1、配置mysql配置文件
主库下的my.cnf:
server-id=1   --配置服务id保证整个集群中唯一，取值1-2^32-1 ,默认为1
read-only=0   --0表示读写都可，1表示只读
binlog-ignore-db=mysql --二进制日志忽略哪些数据库
binlog-do-db= test     --指定同步数据库

重启mysql服务   
创建用户用于远程连接并赋予主从复制权限
 create user 'slave'@'%' identified with mysql_native_password by '1234';
 
 grant replication slave on *.* to 'slave'@'%';
 
 show master status ;

从库配置：

server-id=2   
read-only=1  
以上虽然配置只读但是超级管理员还是可以使用其他操作
super-read-only=1  --管控包括超级管理员

重启mysql服务，登入mysql

change replication source to source_host='116.205.140.241',source_USER='slave',source_password='1234',source_log_file='binlog.000211',source_log_pos=1119;  --配置主从复制，在从库中运行

start slave; 或者 start replica;  --开启主从复制

show slave status;  --查看主从复制情况

3、分库分表

3.1、分库策略

垂直拆分：

水平拆分：

3.2、MyCat

数据库中间件解决mysql集群问题。

下载地址：http://dl.mycat.org.cn/
下载后解压即可
lib目录下connector 版本过低的话可以去maven仓库下一个上传
并授权
chmod 777 mysql-connector-java-8.0.30.jar

3.3、Mycat入门

1、分片配置

Schema.xml配置
-----------------------------------------------------------------------------------------
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="MyFirst" checkSQLschema="true" sqlMaxLimit="100">
		<table name="cat" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
	</schema>

	<dataNode name="dn1" dataHost="dhost1" database="MyFirstSlave" />
	<dataNode name="dn2" dataHost="dhost2" database="MyFirstSlave" />
	<dataNode name="dn3" dataHost="dhost3" database="MyFirstSlave" />


	<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>	

		<writeHost host="master" url="jdbc:mysql://192.168.10.130:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234"/>
	</dataHost>
	
	<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>	

		<writeHost host="master" url="jdbc:mysql://192.168.10.131:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234"/>
	</dataHost>
	
	<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>	
		<writeHost host="master" url="jdbc:mysql://192.168.10.132:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234"/>
	</dataHost>
</mycat:schema>
----------------------------------------------------------------------------------------
server.xml 需要修改的配置
----------------------------------------------------------------------------------------
	<user name="user">
		<property name="password">123456</property>
		<property name="schemas">MyFirst</property>
		<property name="readOnly">true</property>
	</user>

2、启动

到mycat目录下bin/mycat start
查看是否开启成功 tail -f logs/wraper.log
连接mycat:  mysql -h IP地址 -P 8066 -uroot -p

3.4、Mycat配置

1、schema.xml:

涵盖了逻辑库、逻辑表、数据节点、数据源配置

3.4、分片

1、垂直拆分

多表联查数据小的关联表可以设置为全局表  ，type=global

2、水平拆分（同MyCat入门）

可以将rule 修改为mod取余的分片规则，多个数据库用，号分割

3、分片规则

一致性Hash：根据字段的hash值进行分片

auto-sharding-long   --数值范围分片
mod-long              --取余分片
sharding-by-murmur   --一致性hash
sharding-by-enumstatus  --枚举分片 defualtNode超出枚举节点配置，记住修改columns
sharding-by-subString  --根据字段中部分字符进行分片

枚举分片：

应用指定：截取数字类型前几个

固定分片hash：将字段值与二级制1111111111进行&运算

字符串Hash解析算法：截取字符串进行hash运算再遇与1023进行位于运算

按天进行分片：

按月份进行分片：超过分片时间就会按月份从头开始进行分片

3.5、管理和监控

8066 dml ddl 端口
9066 管理端口mysql -h192.168.10.130 -P 9066 -uroot -p

3.6、mycat eye

1、zooker安装

A. 上传安装包 
	zookeeper-3.4.6.tar.gz
B. 解压
	tar -zxvf zookeeper-3.4.6.tar.gz -C /usr/local/
C. 创建数据存放目录
	cd /usr/local/zookeeper-3.4.6/
	mkdir data
D. 修改配置文件名称并配置
	cd config
	mv zoo_sample.cfg zoo.cfg
E. 配置数据存放目录
	dataDir=/usr/local/zookeeper-3.4.6/data
F. 启动Zookeeper
	bin/zkServer.sh start
	bin/zkServer.sh status

2、mycatweb安装

A. 上传安装包 
	Mycat-web.tar.gz
B. 解压
	tar -zxvf Mycat-web.tar.gz -C /usr/local/
C. 目录介绍
    etc         ----> jetty配置文件
    lib         ----> 依赖jar包
    mycat-web   ----> mycat-web项目
    readme.txt
    start.jar   ----> 启动jar
    start.sh    ----> linux启动脚本
D. 启动
	sh start.sh
E. 访问
	http://192.168.200.210:8082/mycat

备注:

​ 如果Zookeeper与Mycat-web不在同一台服务器上 , 需要设置Zookeeper的地址 ; 在/usr/local/mycat-web/mycat-web/WEB-INF/classes/mycat.properties文件中配置 :

4、读写分离

4.1、一主一从

4.2、双主双从

配置主库:

2个主库的my.cnf

server-id =1   --mysql服务id，保证整个集群环境唯一，取值范围1-2^23-1，默认为1
binlog-do-db=xxx     --指定同步数据库
log-slave-updates   --另一个主库更新时也要进行同步更新二级制文件

创建用于主从复制的用户

 create user 'slave'@'%' identified with mysql_native_password by '1234';
 
 grant replication slave on *.* to 'slave'@'%';
 
 show master status ;

从库：

server-id = 2 

change replication source to source_host='116.205.140.241',source_USER='slave',source_password='1234',source_log_file='binlog.000211',source_log_pos=1119;  --配置主从复制，在从库中运行

start slave; 或者 start replica;  --开启主从复制

show slave status;  --查看主从复制情况

主库直接的主从复制：

change replication source to source_host='116.205.140.241',source_USER='slave',source_password='1234',source_log_file='binlog.000211',source_log_pos=1119;  --配置主从复制，在从库中运行	

start slave; 或者 start replica;  --开启主从复制