mysql dba

/**
1）连接层：客户端和服务器端建立连接，客户端发送SQL至服务器端；
2）SQL层：对SQL语句进行查询处理；
3）存储引擎层：与数据库文件打交道，负责数据的存储和读取。

1）InnoDB存储引擎：它是MySQL 5.5版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等。
2）MyISAM存储引擎：在MySQL 5.5版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少。

由于 MySQL 数据库体积小、速度快、总体拥有成本低、开放源代码，其有着广泛的应用，一般中小型网站的开发都选择 MySQL 作为网站数据库。由于其社区版的性能卓越，因此搭配 PHP 和 Apache 服务器可组成良好的开发环境。
支持多线程，充分利用 CPU 资源
优化的 SQL 查询算法，有效地提高查询速度。
既能够作为一个单独的应用程序应用在客户端服务器网络环境中，也能够作为一个库而嵌入其他的软件中。
提供 TCP/IP、ODBC 和 JDBC 等多种数据库连接途径。
支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
支持多种存储引擎。
MySQL 5.6 是比较稳定的（GA）发布系列，只针对漏洞修复重新发布，不增加会影响稳定性的新功能。
**/

--使用下面的语句可以修改当前数据库临时的默认存储引擎
SET default_storage_engine=myisam
SET default_storage_engine=innodb
--查看库中引擎
show ENGINES

--查看慢查询设置
SHOW VARIABLES LIKE 'SLOW_QUERY%'

set global slow_query_log = on; 

set global long_query_time=5

show global variables like 'long_query_time'

show variables like '%quer%';

mysqldumpslow -t 10  /data/mysql/mysql-slow.log

explain
select sleep(10)

--备份
mysqldump -u root -p esi xueke > D:xueke.sql

select * from mysql.user

/**
--主从复制
（1）主数据库进行增删改操作后，相应操作记录的语句（比如 create database test）会记录到binlog日志文件中（binlog日志文件一般和数据库data文件夹在一起）。

（2）从数据库会请求主数据库的binlog日志文件，获取到新的操作语句，然后在自己的从数据库上自动执行相同的操作语句，进而实现主从的同步。

详细主从复制过程

（1）Master开启bin-log功能，binlog日志文件用于记录数据库的增删改操作。

（2）需要开启三个线程，Master：I/O线程；Slave：I/O线程，SQL线程。

（3）Slave start；通过I/O线程连接Master，并且请求某个bin-log，position之后的内容。

（4）Master服务器收到Slave I/O线程发过来的日志请求信息，然后Master I/O线程将bin-log内容、position返回给Slave IO线程。

（5）Slave服务器收到bin-log日志内容，将bin-log日志内容写入到relay-log中继日志，创建一个master.info文件，该文件记录master IP、用户名、密码、master bin-log名称、bin-log position。
 

（6）Slave已经开启了sql线程，由sql线程实时监测relay-log日志内容是否有更新，如果有更新，则解析文件中的sql语句，并在Slave数据库中执行相同的操作语句。

注：可以通过show slave status \G  来查看具体的中继日志路径以及连接的master的其他信息。
**/

--特别需要注意的一点是触发器的begin end;之间的语句的执行效率一定要高，资源消耗要小。
/**
BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后。
FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器，也就是说触发器的触发频率是针对每一行数据触发一次。
        ①INSERT型触发器：插入某一行时激活触发器，可能通过INSERT、LOAD DATA、REPLACE 语句触发(LOAD DAT语句用于将一个文件装入到一个数据表中，相当与一系列的INSERT操作)；

　　②UPDATE型触发器：更改某一行时激活触发器，可能通过UPDATE语句触发；

　　③DELETE型触发器：删除某一行时激活触发器，可能通过DELETE、REPLACE语句触发。

如果不需要某个触发器时一定要将这个触发器删除，以免造成意外操作，这很关键。
**/
--创建触发器
create TRIGGER test_trg
AFTER delete
ON xueke for EACH ROW
BEGIN 
    delete from 05xueke where researchfield = old.researchfield;
end
--调用触发器
delete from xueke where researchfield = 'PHYSICS';
--删除触发器
drop TRIGGER if EXISTS test_trg;
--查看触发器
SHOW TRIGGERS 
--查看所有触发器
SELECT * FROM information_schema.triggers

--创建函数
CREATE FUNCTION test_f()
RETURNS varchar(20)
RETURN
(select researchfield from xueke where researchField = 'CHEMISTRY');
--调用函数
select test_f();
--删除函数
drop FUNCTION if EXISTS test_f

--查看所有用户
SELECT * from mysql.user
--查看用户
select * from mysql.user where USER = 'jack';
--删除用户
drop USER 'jack'@'localhost_3306'
--创建用户
CREATE USER 'alice'@'localhost_3306' IDENTIFIED BY '123'
--修改用户
RENAME USER alice@'localhost_3306' to 'jack'@'localhost_3306'
--修改用户密码
SET PASSWORD FOR 'jack'@'localhost_3306' = PASSWORD('123456');
--授予权限
GRANT SELECT,INSERT on *.*
TO 'jack'@'localhost_3306'
IDENTIFIED BY '123456'
WITH GRANT OPTION
--授予所有权限
GRANT ALL PRIVILEGES ON db_csv.*
TO 'jack'@'localhost_3306'
IDENTIFIED BY '123456';
--刷新用户权限
FLUSH PRIVILEGES
--查看该用户所有权限
SHOW GRANTS FOR 'jack'@'localhost_3306'

--收回用户权限
REVOKE ALL on *.* from 'jack'@'localhost_3306'
GRANT ALL ON *.* to 'jack'@'localhost_3306'
#REVOKE DELETE on *.* from 'jack'@'localhost_3306'
#grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。

-- 表分区
create table tab_name(id int,name varchar(20),datetime TIMESTAMP)
partition by list(TO_DAYS(datetime))(partition p1 VALUES in(TO_DAYS('2021-01-01')),PARTITION p2 VALUES in (TO_DAYS('2021-01-02')));
-- 添加一个表分区
alter table tab_name add PARTITION(PARTITION p3 VALUES in (TO_DAYS('2021-01-03')));
-- 查看表分局状态，和分区名
SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='subject_analysis_dayair_aport_university_bk';

-- range
create table emp
(empno varchar(20) not null ,empname varchar(20),deptno int,birthdate date,salary int)
partition by range(salary)
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than maxvalue
);
-- list
create table emp
(empno  varchar(20) not null ,empname varchar(20),deptno  int,birthdate date not null,salary int)
partition by list(deptno)
(
partition p1 values in  (10),
partition p2 values in  (20),
partition p3 values  in  (30)
);
-- hash
create table emp
(empno varchar(20) not null ,empname varchar(20),deptno int,birthdate date not null,salary int)
partition by hash(year(birthdate))
partitions 4;

--key
create table emp
(empno varchar(20) not null ,empname varchar(20),deptno int,birthdate date not null,salary int)
partition by key(birthdate)
partitions 4;

-- 分区表操作
alter table emp drop partition p1,p2;
alter table emp add partition (partition p4 values in (40));
alter table emp add partition (partition p4 values less than (40));

--分解分区
alter table emp reorganize partition p1 into
(partition p1 values less than (10),partition p4 values less than (40));
--合并分区
alter table emp reorganize partition p1,p2 into
(partition p1 values less than (10), partition p2 values less than (20));  -- 不会丢失数据
--重新定义hash 分区
alter table emp partition by hash(deptno) partitions 7;
--删除所有分区
alter table emp removepartitioning;
--删除后重建分区
alter table emp rebuild partition p1,p2;
--优化分区
alter table emp optimize partition p1,p2;
--分析分区
alter table emp analyze partition p1,p2;
--修复分区
alter table emp repairpartition p1,p2;
--检查分区
alter table emp check partition p1,p2;