Mysql高级部分（终极篇）

语法：

变量:

系统变量 （服务器重启会失效）

• 全局变量 global（由系统提供，属于服务器层面）
• 会话变量 session

用法：

# 查看所有系统[会话]变量 + like 可以模糊搜索
# 查看全局变量
show global  variables [like '%关键字%'];
# 查看会话变量
show session variables [like '%关键字%'];

# 查看系统[会话]变量赋值
# 方式1：select @@global|session.变量名;
# 方式2：select global|session  变量名; 

# 修改系统变量
set @@global|session.变量名 = 值;

自定义变量

• 用户变量（自己定义的变量）
• 局部变量（仅仅在begin end中有效）

定义用户变量：

# 声明用户变量
set @用户变量名=值;
# 赋值（更新值）
set @用户变量名=值;
# 使用
select @用户变量名

定义局部变量：

DECLARE 变量名 字段类型  默认值;
例子：DECLARE sal int default 5; 

变量赋值：
方式1 ：SET sal = 值;
方式2 ：select(*) into sal from 表;

条件判断：

语法： if(条件,值1,值2)

if 结构

语法：
if 条件1 then 语句1;
	elseif 条件2 then 语句2;
		...
	else 语句n;
end if;

# 例子：根据身高值，判断属于什么身材
declare height int default 185;
declare des varchar(50) default '';
if height >= 180 then 
		set des = '身材高高';
	elseif height >=170 and height < 180 then 
		set des = '中等身材';
	else 
		set des = '小小身材';
end if;

case结构

语法：
case [表达式或字段]
	when 值1 then 语句1;
	when 值2 then 语句2; ...
	else 语句n;
and case;

循环结构：

while循环

语法：
【名称：】 while 循环条件 do  
	循环体
end while 【名称】;

# 例子 从1加到n的值
declare total int default 0;
declare start int default 0;
while start < = n  do
	set total = total + start;
	set start = start + 1;
end while

repeat循环

当满足条件退出循环，while是满足条件才执行。

语法：
【名称：】 repeat 循环体
until 循环条件;

例子：从1加到n的值
declare total int default 0;
repeat 
	set total = total + n;
	set n = n-1;
	until n = 0
end repeat

loop循环

语法：
【名称：】 loop 循环体
end loop 【名称】;

例子：从1加到n的值
c:loop
	set total = total + n;
	set n = n-1;
	if n<=0 then
		leave c;
	end if
		end loop c;
select total;

循环控制语句：

leave：类似break，跳出所在循环

iterate: 类似于continue，用于结束本次循环，继续下一次

例子：

批量插入,循环 1>20 跳出循环
create procedure test_ont(in inste int)
begin
	declare i int default 1; #定义变量
	a:while i<=inste do
		insert into 表 (id,name) values (i,'名字');
		if i>20 when leave a; # i>20 #跳出循环
		end if;
		set i = i+1;
	 end while a; #结束循环
end $
执行
call test_ont(100)$

存储过程

类似于方法，一组预先编译好的sql语句集合。适合做批量插入、更新

优点：提高代码重用性、简化操作、减少编译次数、减少数据库服务器的连接

创建存储过程：

create procedure 存储过程名字（参数列表）
begin  
	(一组sql语句)  
end

# 例子：
delimiter $
create procedure pro_test1()
begin  
	select 'name名';
end $
# 调用存储过程：
call pro_test1() $

补充，创建存储过程 - 传递参数

参数模式：

• in 输入参数
• out 输出参数
• inout 输入输出

# 例子1： 空参数
delimiter $
create procedure my1()
begin
	( insert into 表(...) values(...); )
end $
调用语句：call 存储过程名字();
call my1()$

# 例子2： 带参数的存储过程
delimiter $
create procedure my2(in name varchar(200),in pas varchar(200))
begin
	# 声明变量result是0
	declare result int default 0  
	select count(*) into result from 表 bo
    where bo.name = name and bo.pas = pas; 
    select if(result>0,'成功','失败');  #使用变量
end $
执行
call my2("李白",”喝酒“)$

# 例子3： 带返回值的存储过程
delimiter $
create procedure my3(in name varchar(200),out p1 varchar(200),out p2 int)
begin
	select b.name,b.p2 into p1,p2 from boy b
	inner join .... where b.name = name;
end $
执行
call my3("李白",@p1,@p2)$
可以通过当前窗口的得到变量p1、p2，通过select输出查看。
select @p1;
select @p2;

# 例子4： 带inout参数的存储过程
delimiter $
create procedure my4(inout a int),inout b int)
begin
	set a = a*2;
	set b = b*2;
end $
执行
set @m=10$
set @n=20$
call my4(@m,@n)$
select  @m,@n$

删除存储过程：

drop procedure 存储过程名字;

查看存储过程：

show create procedure 存储过程名字;

#查询某个库中的所有存储过程;
show name from mysql.proc where db='库名';

# 查询存储过程的状态信息
show procedure status;

# 查询某个存储过程的定义
show procedure 存储过程名字 \G;

存储函数

类似于方法，一组预先编译好的sql语句集合。适合做处理数据后返回一个结果。

和存储过程的区别：函数仅有一个返回，存储过程可以多个或者没有。

存储函数可以在select查询中使用，存储过程不可以；存储过程可以有创建表语句和事务等等，存储函数不可以；

创建函数：

create function 函数名(参数列表) 返回类型
begin  
	(一组sql语句)  
end
调用语法：
select 函数名(参数列表)

# 例子1： 空参数有返回
delimiter $
create function my1() returns int
begin
	declare c int default 0; #定义变量
	select count(*) into c   #赋值
	from 表
	return c;
end $
调用语句:
select my1()$

# 例子2： 有参有返回
delimiter $
create function my2(name varchar(200)) returns int
begin
	set @sal = 0; #定义用户变量
	select salary into @sal #赋值
	from 表 where l_name = name;
	return @sal;
end $
调用语句:
select my2('李白')$

# 注意：一般mysql默认不允许创建函数
# 查看是否允许自创函数
show variables like 'log_bin_trust_function_cteatots'; 
# 允许自创函数 (不加global仅对当前窗口生效)
set global log_bin_trust_function_cteatots=1;

查看函数：

show create function 函数名;

删除函数：

drop function 函数名;

触发器

是指增删改之前或之后，触发定义的SQL集合，相当于事件。现在的触发器只支持行级触发，不支持语句触发。

创建触发器：

create trigger 触发器名字
after|before  insert|update|delete
	SQL语句
end;

例子：通过触发器记录emp的数据变更日记，记录增删改操作。
步骤1. 创建emp_log表，记录日志，字段：操作时间、类型、参数

# 记录新增触发器
delimiter $
create trigger emp_insert_trigger
after  insert
on emp表
# 行级触发
for each row
begin
	insert into emp_logs(id,time,type,params) values
	(null,now(),'insert',
     concat('插入后的数据 主键',new.id,'name=',new.name));
end $

# 记录修改触发器
delimiter $
create trigger emp_update_trigger
after  update
on emp表
# 行级触发
for each row
begin
	insert into emp_logs(id,time,type,params) values
	(null,now(),'update',
     concat('修改前数据',old.id,'name=',old.name,' --- 修改后的数据',new.id ....));
end $

# 记录删除触发器
delimiter $
create trigger emp_delete_trigger
after  delete
on emp表
# 行级触发
for each row
begin
	insert into emp_logs(id,time,type,params) values
	(null,now(),'delete',
     concat('删除的数据 主键',old.id,'name=',old.name));
end $

查看触发器：

show trigger;
show triggers\G;

删除触发器：

drop trigger [数据库] 触发器的名字;

Mysql8新特性

窗口函数：over

通过窗口函数进行分组，原先是100条记录，分组后还是100条；而聚合函数不是这样的。

CREATE TABLE `sales` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `city` varchar(255) DEFAULT NULL COMMENT '城市',
  `name` varchar(500) DEFAULT NULL COMMENT '地区',
  `num` int(11) DEFAULT NULL COMMENT '销量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试表';
例子：
-- 每个城市的销售总额,占市的比例和总比例
SELECT city '城市', NAME '区', num as '当前销量', 
SUM(num) over (PARTITION by city) as '市区销量',
num / SUM(num) over (PARTITION by city) as '市比例',
SUM(num) over () as '总销量',
num / SUM(num) over () as '总销量比例'
FROM sales
ORDER BY city,name

序号函数

ROW_NUMBER()
# 查询表中每个城市下销量降序排列的各个地区信息
SELECT city '城市', NAME '区', num as '当前销量', 
ROW_NUMBER() over (PARTITION by city ORDER BY num desc) as row_num
FROM sales
# 查询表中每个城市下销量最高的的各个地区信息
SELECT * FROM (
    SELECT city '城市', NAME '区', num as '当前销量', 
    row_number() over (PARTITION by city ORDER BY num desc) as row_num
    FROM sales 
) t
WHERE t.row_num <= 2

RANK()
# 与ROW_NUMBER() 区别, 如果有通过的值，排序是相同的，下一个元素是+n
SELECT city '城市', NAME '区', num as '当前销量', 
RANK() over (PARTITION by city ORDER BY num desc) as row_num
FROM sales 

DENSE_RANK()
# 与ROW_NUMBER() 区别, 如果有通过的值，排序是相同的，下一个元素是+1
SELECT city '城市', NAME '区', num as '当前销量', 
DENSE_RANK() over (PARTITION by city ORDER BY num desc) as row_num
FROM sales 

# ROW_NUMBER() 排序 1，2，3，4
# RANK()       排序 1，2，2，4
# ROW_NUMBER() 排序 1，2，2，3
select 
ROW_NUMBER() over (ORDER BY num desc) as '排序1',
RANK() over (ORDER BY num desc) as '排序2',
ROW_NUMBER() over (ORDER BY num desc) as '排序3'
from 表;

分布函数

PERCENT_RANK()
# 像概率函数，越往下pr越接近1
# 计算表中城市为“北京”的下的区的PERCENT_RANK值
SELECT city '城市', NAME '区', num as '当前销量', 
RANK() over (PARTITION by city ORDER BY num desc) as r,
PERCENT_RANK() over (PARTITION by city ORDER BY num desc) as pr
FROM sales 
WHERE city = "北京"

写法2：
# window w 相当于别名
SELECT city '城市', NAME '区', num as '当前销量', 
RANK() over w as r,
PERCENT_RANK() over w as pr
FROM sales 
WHERE city = "北京" window w as (PARTITION by city ORDER BY num desc)

CUME_DIST()
# 小于或等于当前销售的比例 ，就是比当前价格小的有多个个，比例
SELECT city '城市', NAME '区', num as '当前销量', 
CUME_DIST() over w as pr
FROM sales 
WHERE city = "北京" window w as (PARTITION by city ORDER BY num desc)

前置函数

LAG(字段，行)
# 返回当前行的前n行的值
# 查询当前一个销售与前一个销售的查值
SELECT city '城市', NAME '区', num as '当前销量', 
LAG(num,1) over w as '前一行的销量'
FROM sales 
WHERE city = "北京" window w as (PARTITION by city ORDER BY num desc)

LEAD(字段，行)
# 返回当前行的上n行的值
# 查询当前一个销售与上一个销售的查值
SELECT city '城市', NAME '区', num as '当前销量', 
LEAD(num,1) over w as '下一行的销量'
FROM sales 
WHERE city = "北京" window w as (PARTITION by city ORDER BY num desc)

首尾函数

FIRST_VALUE(字段)
# 返回第一行的值
# 查询当前一个销售与第一个销售的查值
SELECT city '城市', NAME '区', num as '当前销量', 
FIRST_VALUE(num) over w as '第一行的销售'
FROM sales 
WHERE city = "北京" window w as (PARTITION by city ORDER BY num desc)

LAST_VALUE(字段)
# 返回尾部的值
# 查询当前一个销售与尾部的查值
SELECT city '城市', NAME '区', num as '当前销量', 
LAST_VALUE(num) over w as '第n行的销售'
FROM sales 
WHERE city = "北京" window w as (PARTITION by city ORDER BY num desc)

NTH_VALUE(字段,行)
# 返回第n行的值
# 查询当前一个销售与第n个销售的查值
SELECT city '城市', NAME '区', num as '当前销量', 
NTH_VALUE(num,2) over w as '第2行的销量',
NTH_VALUE(num,4) over w as '第4行的销量'
FROM sales 
WHERE city = "北京" window w as (PARTITION by city ORDER BY num desc)

NTILE(分几组)
# 将这些数据分为几组，例子：分2组
SELECT city '城市', NAME '区', num as '当前销量', 
NTILE(2) over w as '分组'
FROM sales 
WHERE city = "北京" window w as (PARTITION by city ORDER BY num desc)

行转列

# GROUP_CONCAT
SELECT
  t.id,
  t.e_id,
  GROUP_CONCAT(distinct e.name) as name
FROM 表 t
LEFT JOIN 表 e ON t.id = e.t_id
group by t.id,t.e_id
ORDER BY t.id desc

模糊查询

# 当我们sql里字段是，分割的，进行模糊匹配，使用find_in_set函数（要查询到值，字段名）
# 数据库的值是 1，3，13  模糊匹配3，只想把3查询出来，13不需要查询出来
SELECT * FROM t_sm_contract_disclosure 
WHERE find_in_set('3',code)
# 如like 查询
CONCAT(CONCAT(',',t.dept_Code),',') like '%," + deptCodes.get(i) + ",%'
# 补充：escape 是占位符 .. and (t.name like :name escape '/' ) 

公用表表达式

普通公用表达式

WITH cte_e
as (select id from 表 )
select * from 表2 join cte_e on 表2.id = cte_e.id;

递归公用表达式

# 递归查询 - 
# dept_id 部门id，dept_name部门名称，dept_code部门编码，parent_id父级部门，status状态，
# ROOT_CODE部门编码 （可通过该字段，查询该部门的下属部门有哪些）
SELECT
	`t`.`dept_id`   AS `dept_id`,
	`t`.`dept_name` AS `dept_name`,
	`t`.`dept_code` AS `CODE`,
	`t`.`parent_id` AS `parent_id`,
	`t`.`status`    AS `status`,
	`t`.`root_code` AS `ROOT_CODE` 
FROM
	( WITH recursive `cte` 		  (`dept_id`,`dept_code`,`dept_name`,`parent_id`,`status`,`ROOT_CODE`,`ROOT_ORDER`) 
     AS ( 
		SELECT
            `t0`.`dept_id` AS `dept_id`,
            `t0`.`dept_id` AS `dept_code`,
            `t0`.`dept_name` AS `dept_name`,
            `t0`.`parent_id` AS `parent_id`,
            `t0`.`status` AS `status`,
            `t0`.`dept_id` AS `root_code`,
            `t0`.`dept_id` AS `root_order`
	    FROM `sys_dept` `t0` 
	    WHERE `t0`.`status` = 0  
	    UNION ALL
		SELECT
             `t2`.`dept_id` AS `dept_id`,
             `t2`.`dept_id` AS `dept_code`,
             `t2`.`dept_name` AS `dept_name`,
             `t2`.`parent_id` AS `parent_id`,
             `t2`.`status` AS `status`,
             `t1`.`ROOT_CODE` AS `ROOT_CODE`,
			  concat( `t1`.`ROOT_CODE`, '->', `t2`.`dept_id` ) AS `root_order` 
		FROM `sys_dept` `t2` JOIN `cte` `t1` 
	    WHERE `t2`.`parent_id` = `t1`.`dept_code` AND 1=1
       ) 
	SELECT 
     `t1`.`dept_id` AS `dept_id`,
     `t1`.`dept_id` AS `dept_code`,
     `t1`.`dept_name` AS `dept_name`,
     `t1`.`parent_id` AS `parent_id`,
     `t1`.`status` AS `status`,
     `t1`.`ROOT_CODE` AS `root_code`,
     `t1`.`ROOT_ORDER` AS `ROOT_ORDER` 
	FROM`cte` `t1` 
	ORDER BY `t1`.`ROOT_ORDER` 
) `t`
WHERE t.ROOT_CODE = "部门id"

# 案例2
WITH recursive `cte`
as (
    # 先查询最顶级的人（领导）id=1就是那个领导，字段n 代表：顶级
	select id,name,manager_id,1 as n from 表 id =1;
    UNION ALL
    select a.id,a.name,a.manager_id,n+1 as n from 表 as a 
    join cte on a.manager_id = cte.id;
)
# 查询前3级的部门人员
select id,name from cte where cte.n >=3;

其他信息

Linux版的安装配置

在Linux系统上安装Mysql，（在Linux下安装Mysql8上有）

注意：CentOS6 是mysql服务，CentOS7 是mariadb服务

修改字符集

# mysql8默认编码是utf8mb4,之前默认是latin1
# 查看字符集，
mysql> show variables like 'character%';

# 修改mysql的配置文件中的字符集键值，修改/etc/my.cnf文件
添加 character_set_server=utf8

# 修改完后，重启mysql的服务
systemctl restart mysqld

# 修改数据库的字符集
alter database 库名 default character set 'utf8' collate 'utf8_general_ci';
alter table 表名 default character set 'utf8' collate 'utf8_general_ci';

比较规则

# 通常的比较规则 utf8_general_ci
_ai  不区分重音
_as  区分重音
_ci  不区分大小写
_cs  区分大小写
_bin 二进制比较
# 查看字符集比较规则
show collation like 'utf8%';

存储引擎:

查看命令 : show engines;

当前默认的存储引擎:

show variables like '%storage_engine%';   

常用的存储引擎：MyISAM | InnoDB

区别：

• MyISAM 不支持外键、事务、只缓存索引、表锁，适合查询
• InnoDB 支持外键、事务、缓存真实数据、行锁，适合增删改

mysql的目录

# 查看mysql数据存放目录
show variables like 'datadir';
# 配置文件目录
/usr/share/mysql-8.0, /etc/mysql my.cnf

用户与权限管理

登录mysql

# 普通登录
mysql -u root -p
mysql -h ip地址 -P 3306 -p 数据库名

创建修改删除用户

use mysql;
# 创建用户
create user '用户名' identified by '密码';
# 查看用户
select host,user from user;
# 修改用户
update mysql.user set user='新用户名' where user='旧用户名';
FLUSH PRIVILEGES;
# 删除用户
drop user '用户名'@'host信息';  
# 例子：drop user 'root1'@'%';

修改用户密码

# 修改root用户密码
alter user user() IDENTIFIED by '新密码';
# 修改其他用户密码
set password for '用户名'@'host信息'= '新密码';

密码过期

# 180天后，密码过期
set persist default_password_lifetime = 180; 
# 给某个用户设置 never（从不过期）INTERVAL 90 DAY (90天过期)
alter user '用户名'@'host信息' password expire never;

权限

# 查看当前用户权限
show privileges;
# 查看某个用户权限
show grants for '用户名'@'host信息';
# 授权
grant 权限1,权限2 on 数据库.表名 TO 用户名@host信息 
# 例子：grant select,update on 数据库.* to 'yan'@'IP地址' 
# 回收权限
REVOCK 权限1,权限2 on 数据库.表名 TO '用户名'@'host信息'

角色

# 创建角色
create role '角色名'@'host信息';
# 给角色赋予权限 grant all privileges on ... 该表全部权限
grant  权限1,权限2 on 数据库.表名 TO '角色名'@'host信息'
# 查看某个角色权限
show grants for '角色名'@'host信息';
# 回收权限
REVOCK 权限1,权限2 on 数据库.表名 TO '角色名'@'host信息' 
# 删除
drop role ‘角色名’;
# 给用户赋予角色
grant '角色名'@'host信息' to '用户名'@'host信息'; 
# 激活角色
set GLOBAL activate_all_roles_on_login=ON;
# 查看当前会话已激活的角色
select CURRENT_ROLE();
# 撤销用户角色
REVOCK '角色名'@'host信息' from '用户名'@'host信息';

日志

错误日志：

默认是开启的，默认存放在/var/lib/mysql下 . err文件

# 查看日志位置：
show variables like 'log_error%';
# 查看日志内容
tail -f /路径名/文件名

二进制日志：

更改配置文件:/usr/my.cnf

# 开启
log_bin=mysqlbin
# 配置日志格式
binlog_format=STATEMENT

删除二进制日志

# 查看二进制文件
show binary logs;

# 方式1 删除全部（谨慎使用）
Reset Master

# 方式2 删除***(版本号)之前的日志，指定文件删除
purge master logs to "mysqlbin.***(版本号)"

# 方式3 配置文件里配置，超过n天后就删除
log_bin=mysqlbin
--expire_logs_days=3

查询日志：

# 开启 在配置文件配置
general_log=1
# 配置 查询日志的存放文件名
general_log_file=mysql_query.log

redo日志：

记录物理级别的修改操作，提供写入操作，保证事务的持久性；

该日志是写入磁盘中，根据存储引擎产生的日志，与二进制日志不同，二进制日志是事务提交后，才有binlog

undo日志：

回滚日志，回滚记录到某个版本，保证事务的一致性、原子性；

慢SQL日志：

# 开启 在配置文件配置 1 开启 0 关闭
slow_query_log=1
# 指定慢查询日志文件名
show_query_log_file=show_query.log
# 配置查询时间，超过这个时间认为慢SQL 10S
long_query_time=10

查询long_query_time时间

show variables like 'long%';

查看慢SQL

mysqldumpslow 日志名.log

分析日志：

查看SQL执行频率：

# 查询增删改的查询次数
show stayus like 'Com_';
# 查询Innodb引擎下增删改的查询次数
show status like 'Innodb_rows_%'

1. 慢查询日志

查看默认的日志文件：

可以在 默认地址 /var/lib/mysql/cocoon-show.log 查看慢SQL

日志分析工具 ：mysqldumpslow 通过分析要查询想要的SQL

mysqldumpslow --help

• s: 是表示按照何种方式排序；
• c: 访问次数
• l: 锁定时间
• r: 返回记录
• t: 查询行数
• al:平均锁定时间
• ar:平均返回记录数
• at:平均查询时间
• t:即为返回前面多少条的数据；
• g:后边搭配一个正则匹配模式，大小写不敏感的；

例子：

在Linux系统下，慢SQL日志下，执行

#查询 按访问次数前3的慢sql语句
mysqldumpslow -s c -t 3 -a cocoon-show.log
#查询 按慢SQL时间前3的慢sql语句
mysqldumpslow -s t -t 3 -a cocoon-show.log
#查询 返回记录最多3个SQL
mysqldumpslow -s r -t 3 /var/lib/mysql/atguigu-show.log
#查询 按照时间排序前3条含有左连接的查询语句
mysqldumpslow -s t -t 3 -g 'left join '/var/lib/mysql/atguigu-show.log

列出相对应的进程：

# 实时查看SQL执行情况
show processlist
kill 进程号
# 在sql里执行，如果高并发下出现死锁，可以杀掉进程

2. show Profile

mysql提供可以用来分析当前会话中语句执行的资源消耗情况。

默认情况，保存最近15次的运行结果。

步骤：

• 是否支持，查看当前的mysql版本是否支持。

  Show variables like 'profiling';
  或者
  select @@have_profiling;
  

• 开启功能，

  show variables like 'profiling';  
  set profiling=1;  // 开启
  

• 运行要执行的SQL语句

• 查看结果，

  show profiles;
  

• 查看第几行的SQL详情信息

  show profile for query 5 (5为上一步前面的问题SQL数字号码);
  show profile 【cpu,block io 参数】 for query n (n为上一步前面的问题SQL数字号码);
  

总结：

看结果分析，下面的选择的出现耗时严重，SQL语句会出现问题进行优化。

• converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。

• Creating tmp table 创建临时表

  • 拷贝数据到临时表
  • 用完再删除

• Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！

• locked

3. trace分析优化器执行：

# 开启优化器，设置返回结果为JSON格式，设置最大能使用内存大小，避免内存过小显示不全
set optimizer_trace="enabled=on",and_markers_in_json=on;
set optimizer_trace_max_mes_size=1000000;

# 执行SQL语句...
# 执行完SQl,进行分析上面的SQL语句，进行分析SQL如何执行
select * from information_schema.optimizer_trace\G;

4.全局查询日志

• 配置启用

  在mysql的my.cnf中，设置如下 #开启general_log=1 # 记录日志文件的路径 general_log_file=/path/logfile#输出格式log_output=FILE

• 编码启用

  set global general_log=1; #全局日志可以存放到日志文件中，

  set global log_output=‘TABLE’; 以表进行记录，之后编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看 select * from mysql.general_log;

• 不要在生产环境开启这个功能。

5.Sys schema视图

# 查询未使用的索引
select * from sys.schema_umused_indexes;
# 查看表的访问量
select table_schema,table_name,SUM(io_read_requests+io_write_requests) as io
from sys.schema_table_statistics group by table_schema,table_name order by io desc;
# 查看bufferpool较多的表
select object_schema,object_name,allocated,DATA
from sys.innodb_buffer_stats_by_table order by allocated limit 10;
# 查看全表扫描的表
select * from sys.statements_with_full_table_scans where db = 'dbname';
# 行锁阻塞情况
select * from sys.innodb_lock_waits;

索引

索引简介

可以帮助数据库高效获取数据的数据结构。Mysql目前提供的4种索引：BTREE索引、HASH索引（HASH算法不支持范围查询）、R-tree索引、Full-text索引。

索引结构：

• BTREE结构 （多路平衡搜索树）
• B+TREE结构

Mysql索引使用B+TREE，在这基础上增加了指向叶子节点的链表指针，提高区间访问的性能。

索引分类：（按功能分）

• 主键索引
• 单值索引
• 唯一索引
• 复合索引

索引语法：

# 创建索引  mysql8版本支持升降序 desc降序
create 【unioue】 index 索引名 on 表名(字段名 desc)
例子：create index id_c_name on city(c_name)
# 创建复合索引
create 【unioue】 index 索引名 on 表名(字段名1,字段名2,字段名3);
# 例子：
# 创建唯一性索引
CREATE unique index 索引名 on 表(字段);
# 创建普通索引
CREATE index 索引名 on 表(字段);           
# 创建复合索引
CREATE index 索引名 on 表(字段1,字段2); 
# 创建空间索引，（非空）
CREATE spatial index 索引名 on 表(字段1); 
# 创建全文索引（只能对字符串）
CREATE fulltext index 索引名 on 表(字段1); 
# 然后查询语句,不需要写like查询，需要写MATCH + AGAINST
select * from 表 where MATCH(字符1,字段2) AGAINST('要模糊查询的词');
# 修改主键索引
alter table 表 add primary key 表(字段); 

# 查看索引
show index from 表

# 查看索引使用情况
show globaln status like 'Handler_read%';

# 删除索引
drop index 【索引名】 on 表

哪些情况需要创建索引

• 主键
• 频繁作为查询条件的字段
• 外键关系建立索引
• 组合索引性价比高
• 查询中排序字段
• 查询中统计或者分组字段

不创建索引

• 表数据太少
• 经常增删改的字段
• where 条件用不到的字段
• 过滤性条件，比如 男女，1,0

性能下降SQL慢

• 查询数据过多 ： 分库分表，条件过滤尽量减少
• 关联了太多的表，Join过多：用 A 表的每一条数据扫描 B表的所有数据。所以尽量先过滤
• 没有索引：建立索引
• 服务器调优修改参数配置：修改my.cnf文件

Explain

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，分析你的查询语句或是表结构的性能瓶颈。

语法：Explain + SQL语句

# 以json的格式进行观察，可看到每个索引的成本和大小
EXPLAIN FORMAT = JSON select * from 表;

各字段解释：

• id：查询中执行select子句或操作表的顺序，id相同，执行顺序由上至下，id相同不同，id值越大优先执行。（id每个号码，代表一趟独立查询，趟数越少越好）

• select_type：查询类型，用于区别普通查询、联合查询、子查询等的复杂查询

• table：这一行的数据是关于哪张表

• type：访问类型排列，从最好到最坏依次是：

  system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range(尽量保证) > index > ALL 
  eq_ref (唯一性索引)  ref (非唯一性索引) range (范围查询) 
  index (遍历索引树,index是从索引中读取的，而all是从硬盘中读的) all (全表扫描)
  index_merge (需要多个索引组合使用，通常出现在有 or )
  index_subquery (利用索引来关联子查询，不再全表扫描)
  

• possible_keys：可能应用在这张表中的索引

• key：实际使用的索引

• key_len：表示索引中使用的字节数，值越大越好

• ref：显示索引的哪一列被使用

• rows：显示查询时必须检查的行数，值越少越好

• Extra：额外字段，order by / group by 是否用到索引

  Using filesort ：排序字段没有用到索引
  Using temporary ：group by 没有用到索引
  using join buffer ：两张表关联字段没有用到索引
  impossible where ：不可能，sql逻辑错误
  USING index ：用到了索引
  Using where ：过滤条件用到了索引
  select tables optimized away ：用到了优化器
  

SQL优化

单表查询优化

1.单值索引例子：
explain select sql_no_cache * from 表 where age = 20;
优化：需要给age 建立索引
create index idx_age on 表(age);

2.复合索引例子
explain select sql_no_cache * from 表 where age = 20 and uid =4;
优化：需要给age/uid 建立索引
create index idx_age_uid on 表(age,uid);

关联查询优化

explain select sql_no_cache * from 表1 left join 表2 on 表1.id = 表2.uid; 
添加索引优化，被驱动表需要加索引
alter table 表1 add index 索引名 ('字段uid');

优化：

• 保证被驱动表的join 字段被索引
• left join 优先选择小表作为驱动表
• inner join 数据库会自动选驱动表
• 子查询 尽量不作为被驱动表，不能使用索引
• 能用关联查询直接关联，不用子查询

子查询尽量不要使用not in / not exists 用left outer join on xxx is null 代替

排序分组优化

1.例子：无过滤不索引
创建age,uid索引
explain select sql_no_cache * from 表1 order by age,uid;  # 索引无效
explain select sql_no_cache * from 表1 order by age,uid limit 10; # 索引有效

2.例子：顺序错，必排序
explain select sql_no_cache * from 表1 where age = 1 order by uid,age; 
uid和age顺序错，索引失效

3.例子：方向反，必排序
explain select sql_no_cache * from 表1 where age = 1 order by uid desc,age; 
要么都是升序，要么都是降序

4.分组优化,如果分组避免排序结果消耗，禁止排序
select age,count(*) from 表 group by age;
优化：
select age,count(*) from 表 group by age order by null;

如果不在索引列上，filesort有两种算法:

• 双路排序: 多路排序需要借助 磁盘来进行排序，比较快，但是需要内存空间足够
• 单路排序: 从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，每一行都保存在内存中。

现在的mysql使用单路排序，提高Order By速度优化策略：

• 禁止使用select *
• 增大sort_buffer_size 参数的设置，用于单路排序的内存大小，缓冲区
• 增大max_length_for_sort_data 参数的设置，单次排序字段大小。增大排序区的大小

In 和 exists 区别

A 表数据小于B表数据exists优于in

例子：

select bo.* from boy bo
where not exists (
	select id from beauty b
	where bo.id = b.b_id
)
# 用in代替
select bo.* from boy bo
where bo,id not in (
	select id from beauty b
)

# in
select * from emp e where e.did in (select id from dept d);
# exists 
select * from emp e where exists (select 1 from dept d where d.id = e.did);

or优化：

可以使用union 代替 or 语句

select * from 表1 where id = 1 union select * from 表1 where id = 10 

limit 分页优化：

分页查询，分页页数过大，limit 10000,10 查询速度变慢，利用索引完成分页：

select * from 表1 t ,(select id from 表1 order by id limit 20000,10) a 
where t.id = a.id;

正则表达式 regexp

# 查询以J为开头查询
select  * from 表 where name regexp '^j';
# 查询以J为结尾查询
select  * from 表 where name regexp 'j&';
# 查询包含了 uvw 的查询
select  * from 表 where name regexp '[uvw]';

强制使用某个索引：

强制使用某个索引，不使用推荐的其他索引。

select * from 表 use index (索引名) where 条件查询;

优化insert语句：

insert into 表 (列名,..) values (值1,...);
insert into 表 (列名,..) values (值2,...);
优化：
insert into 表 (列名,..) values (值1,...), (值2,...), (值3,...);

批量数据脚本

大批量插入数据：

使用load命令导入数据，提高导入效率，尽量导入的数据是主键自增。

导入前关闭唯一性校验、手动提交事务：

# 先禁止唯一性约束检查
set unique_checks=0;
# 禁止自动提交事务
set autocommit = 0;
# 导入后再开启
set unique_checks=1;
set autocommit = 1;

导入：

# load .... 文件名  .... 每个字段以 ,分割  每行使用换行分割
load data local infile '/root/sql1.log' into table '表1' fields terminated by ',' 
lines terminated by '\n';

往表里插入1000W数据，做压力测试：

一 创建表结构
新建库 create database bigData; 
建表 dept 
CREATE TABLE dept ( 
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
	dname VARCHAR(20) NOT NULL DEFAULT "",
	loc VARCHAR(13) NOT NULL DEFAULT ""
) ENGINE=INNODB DEFAULT CHARSET=UTF8 ;    
建表 emp
CREATE TABLE emp (  
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/ 
	ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/  
	job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/  
	mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/  
	hiredate DATE NOT NULL,/*入职时间*/  
	sal DECIMAL(7,2) NOT NULL,/*薪水*/  
	comm DECIMAL(7,2) NOT NULL,/*红利*/  
	deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/ 
)ENGINE=INNODB DEFAULT CHARSET=UTF8 ; 

二 创建函数,保证每条数据都不同
2.1 随机产生字符串
DELIMITER $$
	CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
	BEGIN  ##方法开始 
	DECLARE chars_str VARCHAR(100) DEFAULT   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; ##声明字符窜长度为 100 的chars_str,默认值  
	DECLARE return_str VARCHAR(255) DEFAULT ''; 
	DECLARE i INT DEFAULT 0; ##循环开始 
	WHILE i < n DO   
	SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
	##concat 连接函数 ，substring(a,index,length) 从index处开始截取 
	SET i = i + 1; 
	END WHILE; 
	RETURN return_str;
END $$ 
#假如要删除 drop function rand_string;
2.2 随机产生字符串  用于随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num( ) RETURNS INT(5)  
BEGIN    
	DECLARE i INT DEFAULT 0;   
	SET i = FLOOR(100+RAND()*10);  
	RETURN i;   
END $$  
#假如要删除 drop function rand_num;

三 创建存储过程
3.1 创建往emp表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE insert_emp10000(IN START INT(10),IN max_num INT(10))  
BEGIN  
	DECLARE i INT DEFAULT 0; #set autocommit =0 把autocommit设置成0 ；提高执行效率
    SET autocommit = 0;    
    REPEAT ##重复 
    SET i = i + 1;   
    INSERT INTO emp10000 (
        empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno 
    ) VALUES (
        (START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),FLOOR(1+RAND()*20000),FLOOR(1+RAND()*1000),rand_num());   
    UNTIL i = max_num   
    ##直到  上面也是一个循环 
    END REPEAT;  ##满足条件后结束循环 
    COMMIT;   
    ##执行完成后一起提交 
END $$ 
#重置$$结尾符  DELIMITER;  删除存储过程 drop PROCEDURE insert_emp; 
3.2 创建往dept表中插入数据的存储过程
# 执行存储过程，往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))  
BEGIN  
	DECLARE i INT DEFAULT 0;    
	SET autocommit = 0;     
	REPEAT   
	SET i = i + 1;   
	INSERT INTO dept (deptno ,dname,loc ) VALUES (
        START +i ,rand_string(10),rand_string(8));   
    UNTIL i = max_num   
    END REPEAT;   
    COMMIT;   
END $$  
#重置$$结尾符 DELIMITER ;  删除存储过程 drop PROCEDURE insert_dept;

四.调用存储过程
dept 表
DELIMITER ;
CALL insert_dept(100,10); 
emp 表
#执行存储过程，往emp表添加50万条数据
DELIMITER ;  #将 结束标志换回 ;
CALL insert_emp(100001,500000);  
CALL insert_emp10000(100001,10000);  

五.检测 大量数据案例
EXPLAIN  SELECT * FROM emp WHERE deptno =101 AND empno <101000 ORDER BY ename ;

查询缓存

开启查询缓存，我们执行相同SQl时，服务器会从缓存中读取，如果数据被修改删除，缓存就失效。

查询缓存配置：

# 当前数据库是否支持缓存
show variables like 'hava_query_cache';
# 当前是否开启缓存
show variables like 'query_cache_type';
# 查看缓存占用大小
show variables like 'query_cache_size';
# 查询缓存的状态情况  返回结果Qcache_hits 查询命中缓存数
show status like 'Qcache%';

开启查询缓存：

在my.cnf配置中 (0 关闭 1打开 2 指定语句会缓存)
query_cache_type = 1

查询缓存：

SQL_CACHE    查询结果可缓存
SQL_NO_CACHE 查询结果不缓存
例子：
select SQL_CACHE id,name from 表;
select SQL_NO_CACHE id,name from 表;

优化Mysql参数

myisam存储引擎内存优化：

• key_buffer_size 索引块缓存区大小，建议至少1/4可用内存分配key_buffer_size
• read_buffer_size 如果经常顺序扫描表，值可用增大，不建议太大值，会造成内存浪费
• read_rnd_buffer_size 如果排序，order by 语句值增大，不建议太大值，会造成内存浪费

innoDB存储引擎内存优化：

• innodb_buffer_pool_size 存储表数据和索引数据的最大缓存区大小，值越大，访问表需要的磁盘IO越少
• innodb_log_buffer_size 决定日志缓存大小
• table_cache 同时打开表的个数，默认：2402，值在512-1024最佳

Mysql并发参数

• max_connections 允许连接Mysql数据库最大数量，默认151
• back_log 如果max_connections 值满了，会放到这里堆栈区，默认是50，不超过900，超过报错
• table_open_cache 控制所有SQL语句执行线程打开表缓存的数量
• thread_cache_size 加快连接数据库的速度，控制Mysql线程的数量
• innodb_lock_wait_timeout 设置事务等待行锁时间。默认50ms

优化数据库结构

1. 拆表 - 冷热数据分离
2. 增加中间表、冗余字段
3. 优化数据类型

锁机制

锁的分类

从对数据操作的类型（读\写）分

• 读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响。
• 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的粒度分

​ 表锁、行锁、间隙锁

表锁

偏向于偏向MyISAM存储引擎、无死锁、发生冲突概率高，不支持并发。Mysql的表级锁有两种模式：共享读锁、独占写锁。 偏读。

建表：

create table mylock( 
    id int not null primary key auto_increment, 
    name varchar(20)
)engine myisam; 

手动增加表锁：

lock table 表名 read或者write; 
# book表上读锁，book2表上写锁
lock table book read,book2 write; 

查看表上加过的锁:

show open tables; 

释放锁：

unlock tables;
unlock tables 表名;

读锁和写锁的区别：

读锁会阻塞写，但是不会阻塞读，写锁会把读和写都堵塞。

如果分析表锁定：

先查看哪些表被锁

 show open tables; 

然后可以通过 show status like ‘table%’; 通过检查两个值的状态分析表锁定。两个变量 table_locks_immediate 产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即值加1; table_locks_waited 出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待值加1），此值越高存在严重的表级锁争用情况。

行锁

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；发生锁冲突的概率最低，并发度也最高。偏写。

行锁支持事务 ACID属性：

• 原子性 ：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行
• 一致性：在事务开始和完成时，数据都必须保持一致状态。
• 隔离性：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。
• 持久性：事务完成之后，它对于数据的修改是永久性的。

并发事务处理带来的问题：

• 更新丢失：两个事务同时操作同一个数据，A 执行+1操作，B执行-1操作，最后保存的是B执行的操作。
• 脏读：事务A读了事务B已修改未提交数据，如果B回滚，A读的数据是无效的。
• 不可重复读：事务A读了事务B已提交的修改数据，事务A读了2次数据，发现2次数据不一样，不符合隔离性。
• 幻读：事务A读了事务B新增未提交数据，如果B回滚，A读的数据是无效的。

事务隔离级别：

• 未提交读： 脏读、不可重复读、幻读都可能发生
• 已提交读： 不可重复读、幻读可能发生
• 可重复读：默认的，幻读可能发生
• 可序列化：最高的级别

查看当前数据库的事务隔离级别：

show variables like 'tx_isolation';

修改自动提交:

set autocommit = 0;

建表：

create table inlock( 
    id int not null primary key auto_increment, 
    name varchar(20)
)engine innodb; 

行锁定基本演示:

SessionA SessionB更新但是不提交，没有手写commit ; Session_B 被阻塞，只能等待提交更新解除阻塞。

无索引行锁升级为表锁：

索引引用不当，会引起行锁变成表锁。

updata 表 set a =1 where b = 100;  # b的类型是错误的，会导致行锁变表锁

间隙锁危害：

数据最好是连续的，如果不连续，即使这个值并不存在，也会被锁定。

update 表1 set b='abd' where a >1 and a <6; 
# 如果数据库没有 a =3 的数据
另一个Session 执行新增 a = 3的操作，如果 上面的没有执行完，会出现阻塞情况。

锁定一行：

for update 锁定一行，其他操作会被阻塞，直到该行会话提交。如果要恢复这行数据，要进行锁定这行。

begin;
select * from 表 where a=5 for update;

分析行锁：

检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况，

show status like 'innodb_row_lock%'; 

• Innodb_row_lock_current_waits：当前正在等待锁定的数量；

• Innodb_row_lock_time：从系统启动到现在锁定总时间长度；

• Innodb_row_lock_time_avg：每次等待所花平均时间；

• Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；

• Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

  注意 Innodb_row_lock_time_avg、Innodb_row_lock_waits、Innodb_row_lock_time这三个值，如果这三值很高，分析为什么等待这么慢。最后可以通过 SELECT * FROM information_schema.INNODB_TRX\G;来查询正在被锁阻塞的sql语句。

优化建议：

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
• 尽可能较少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 锁住某行后，尽量不要去调别的行或表，赶紧处理被锁住的行然后释放掉锁。
• 涉及相同表的事务，对于调用表的顺序尽量保持一致。
• 在业务环境允许的情况下,尽可能低级别事务隔离

主从复制

复制的基本原理

主从复制是用来建立一个和主数据库完全一样的数据库环境，称为从数据库，主数据库只有一个。而其它服务器充当从服务器。此时主服务器会将更新信息写入到一个特定的二进制文件中。并会维护文件的一个索引用来跟踪日志循环。这个日志可以记录并发送到从服务器的更新中去。

好处：

可以提高吞吐量、数据库高可用，锁表概率更低，数据备份一主一从常见配置

步骤1：主机配置

找到Mysql配置文件，[mysqld] 下修改下面内容

vim /etc/my.cnf

[mysqld]
# 主服务器唯一ID
server-id = 1  
log-bin=自己本地的路径/data/mysqlbin  # 启用二进制日志，日志的存放地址（前提把文件创建好）
log-err=自己本地的路径/data/mysqlerr  # 启用二进制日志，错误日志存放地址（前提把文件创建好）
basedir="xxxxx/" # 根目录 （可选填写）
read-only=0 # 主机，1 只读 0 读写（默认是0）
# 设置日志文件保留的时长，单位是秒
binlog_expire_logs_seconds=6000
# 单个二进制日志大小
max_binlog_size=200M
# 设置不要复制的数据库
binlog-ignore-db=mysql
# 设置需要复制的数据库（先创建好）
binlog-do-db=需要复制的主数据库名字  
# 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M
# 设置logbin格式 有3种格式 
# 默认 STATEMENT(函数支持不好) 默认的 ROW(行模式大量修改效率不行，但支持存储引擎) MIXED (综合，推荐)
binlog_format=MIXED
# 二进制日志过期清理时间，默认是0（不自动清理）
expire_logs_days=7
# 跳过主从复制中遇到的所有错误，避免主从复制中断，1062错误是指的一些主键重复 1032是主从数据不一致
slave_skip_errors=1062

# 保存退出
wq!

步骤2：从机配置

修改my.cnf配置文件

[mysqld]
# 从服务器唯一ID
server-id = 2  
# 开启中继日志
relay-log=mysql-relay
read-only=1 # 主机，1 只读 0 读写（默认是0）
# 表示slave将复制事件写进自己的二进制日志
# log_slave_updates=1

步骤3：重启服务、关闭防火墙

更改配置文件后，重启Mysql服务

# 关闭防火墙    systemctl stop firewalld 
# 停用MySQL服务  service mysql stop
# 查看MySQL服务  service mysql status
# 启动MySQL服务  service mysql start

步骤4：创建用户并授权给从机

主机创建用户：

GRANT REPLICATION SLAVE ON *.* TO '用户名'@'从机器数据库IP 或者 % 所有' IDENTIFIED BY '密码';
flush privileges;

# mysql8配置,需要多配置，如果是mysql5执行上面就可以
create user '用户名1'@'%' identified by '密码';
GRANT REPLICATION SLAVE ON *.* TO '用户名1'@'%';
ALTER USER '用户名1'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
flush privileges;

查询主机的状态：

show master status;
# 执行上面命令，得到 File(binlog日志) Position(接入点) Binlog_Do_DB(要复制的数据库) Binlog_IgnoreDB()
# 需要记录一下File、Position

从机操作：

在从机Mysql命令行上执行。

主机用户，是刚才在主机创建的用户和密码；

binlog日志名字在主机状态里查看，进行赋值；

CHANGE MASTER TO MASTER_HOST='主机IP地址',
MASTER_USER='主机用户',MASTER_PASSWORD='主机用户的密码',
MASTER_LOG_FILE='binlog日志名字',
MASTER_LOG_POS=具体的接入点值;

如果操作失败，需要重新配置，执行下面2个命令

stop slave;    # 停止同步操作
reset master;  # 重置主从配置，删除之前的中继日志，会再生成一份新的

然后执行下面命令，启动从服务器复制功能（都是从机操作）

start slave;

步骤5：检查是否成功

show slave status \G;  # 检查状态
# 如果结果下面的字段为Yes 代表配置成功
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes

多主多从配置，在mycat篇章有介绍

数据备份

mysqldump

# 语法 在linux环境下运行
mysqldump -u 用户名 -h 主机名 -p 密码 数据库 > 备份名称.sql
mysqldump -u root -p 数据库名 > /var/xxx/备份名称.sql  # 备份文件存储当前目录下 

# 备份全部的数据库
mysqldump -u root -p -A > /var/xxx/备份名称.sql

# 备份部分的表
mysqldump -u root -p 数据库名 表名1 表名2 > /var/xxx/备份名称.sql

# 备份某个的表部分数据
mysqldump -u root -p 数据库名 表名1 --where=“id<10” > /var/xxx/备份名称.sql

# 排除某些表的备份
mysqldump -u root -p 数据库名 --ignore-table=数据库名.表名 > /var/xxx/备份名称.sql

# 只备份结构
mysqldump -u root -p 数据库名 --no-data > /var/xxx/备份名称.sql

# 只备份数据
mysqldump -u root -p 数据库名 --no-create-info > /var/xxx/备份名称.sql

# 备份整个库包含存储过程
mysqldump -u root -p -R -E --databases 数据库名 > /var/xxx/备份名称.sql

导出文本文件

mysqldump -u root -p -T "/var/lib/mysql-files/" 数据库 表名
# 会导出txt后缀文件

# 导出文本文件，要求字段之间用逗号，间隔， 所有字符类型用双引号"括起来
mysqldump -u root -p -T "/var/lib/mysql-files/" 数据库 表名 --fields-terminated-by=',' --fields-optionally-enclosed-by='\"'

导入文本文件

# 在mysql环境下运行
load data infile '/var/lib/mysql-files/xxx.txt' into table 数据库.表名;

# 导入文本文件, 在mysql环境下运行
mysqlimport -u root -p 数据库 '/var/lib/mysql-files/xxx.txt' --fields-terminated-by=',' --fields-optionally-enclosed-by='\"'

恢复数据

# 语法 在linux环境下运行
mysql -u 用户名 -p 密码 数据库 < 备份名称.sql

# 恢复单库，如果备份文件里有数据库创建语句，就可以省略数据库
mysql -u root -p 数据库 < /var/xxx/备份名称.sql

# 从全量备份恢复单库
set -n '/^--Current Database: `某个数据库名`/,/^-- Current Databas: `/p`' 全量备份名称.sql > 某个数据库名.sql
# 分离出单个数据库备份，再执行 mysql -u root -p 数据库 < /var/xxx/某个数据库名.sql

# 从单库备份恢复单表
cat 单库备份文件.sql | sed -e '/./{H;$!d;}' -e 'X;/CREATE TABLE `class`/!d;q' > 某表的结构.sql
cat 单库备份文件.sql | grep --ignore-case 'insert into `class`' > 某表的数据.sql

# mysql> 需要在mysql环境上运行 
user 数据库名;
source /var/xxx/某表的结构.sql; 
source /var/xxx/某表的数据.sql; 

物理上备份

备份前，需要停止服务器，或者上锁，禁止新增修改删除数据，这种备份迁移不适用InnoDB存储引擎

# 禁止新增修改删除数据，全部的表上锁
FlUSH TABlES WITH READ LOCK; 
# 备份完，解锁
UNLOCK TABLES

# 在linux环境下运行，复制数据库目录，需要一定的权限
chown -R mysql.mysql /var/lib/mysql/dbname
# 执行拷贝  (在mysql目录下执行，将某个数据库文件或目录，备份到backup目录下 )
cp -r 某个数据库文件 ./backup/

# 恢复物理上的备份 (在mysql目录下执行,把backup目录下备份移动到当前目录下，进行恢复)
mv ./backup/某个数据库文件 ./
# 恢复完，需要重启mysql 重新登录