# MySQL-CSDN博客

本文链接：https://blog.csdn.net/csdn13424/article/details/137250645

MySQL

命令

基础命令

登陆：`mysql 库名 -h主机 -P3306 -u用户 -p密码`
数据库命令：以`;`或`\G`结尾，除密码和变量值之外不区分大小写。以`\c`终止。
命令行使用数据库命令：`mysql 库名 -h主机 -P3306 -u用户 -p密码 -e '数据库命令'`
数据库中使用系统命令：`system 系统命令`

查看信息：`status;`
查看版本信息：`select version();`
查看当前登陆用户：`select user();`
查看当前库：`select database();`

查看正在执行的程序：`show processlist;`
杀死正在执行的程序：`kill 命令的id号;`			# 用于存储过程的loop死循环

库的管理

进入库：`use 库名;`
查看当前库：`select database();`
查看所有库：`show databases;`
查看当前库的所有表：`show tables;`
查看指定库的所有表：`show tables from 库名;`
查看指定表的所有表字段的名字和约束：`desc 库名.表名;` 或 `desc 库名.表名\G`

查看指定库的创建语句：`show create database 库名;`
查看指定表的创建语句：`show create table 库名.表名;`

创建库：`create database [if not exists] 库名 [[default] character set 字符集] [[default] collate 校对规则];`
	+ 库名：数字、字母、下划线，区分字母大小写，不能纯数字
	+ character set utf8mb4：库下的表支持中文字符。MySQL 8版本默认支持中文。
	+ collate utf8mb4_general_ci：库下的表数据不区分大小写。MySQL 8版本默认不区分大小写。
无法直接修改库名

修改库：`alter database 库名 default character set utf8;`

删除库：`drop database if exists 库名;`
删除表：`drop table 库名.表名;`

库目录：`/var/lib/mysql/库名`

存储引擎InnoDB（默认，支持事务处理），每个表会存储为两个表文件
	`/var/lib/mysql/库名/表明.frm`：存储表字段，desc 表名
	`/var/lib/mysql/库名/表名.ibd`：表空间文件，存储表数据，select * from 表名

存储引擎myisam（适用于读操作较多的表），每个表会存储为三个表文件：
	`/var/lib/mysql/库名/表名.frm`：存储表字段，desc 表名
	`/var/lib/mysql/库名/表名.MYI`：存储索引，show index from 表名
	`/var/lib/mysql/库名/表名.MYD`：存储表数据，select * from 表名

表数据的管理

查增改删

查询表数据：
  单表查询：
	+ `select 表字段列表 from 表名 where 筛选条件 group by 表字段 order by 表字段 desc limit 起始0,总行数;`
  连接查询/多表查询：
    + 内连接：`select 表字段列表 from 表名1 inner join 表名2 on 连接条件 where 筛选条件 group by 表字段 order by 表字段 升降 limit 起始0,总行数;`
    + 左连接：`select 表字段列表 from 表名1 left join 表名2 on 连接条件 where 筛选条件 group by 表字段 order by 表字段 升降 limit 起始0,总行数;`
    + 右连接：`select 表字段列表 from 表名1 right join 表名2 on 连接条件 where 筛选条件 group by 表字段 order by 表字段 升降 limit 起始0,总行数;`
    + 全外连接：`(select语句) union (select语句);`去重 和 `(select语句) union all (select语句);`不去重
    + 三张表：以内连接举例：`select 表字段列表 from 表名1 inner join 表名2 on 连接条件1 inner join 表名3 on 连接条件2;`
        mysql> select name,basic,grade from employees as e inner join salary as s on e.employee_id=s.employee_id 
            -> inner join wage_grade as wg on basic between low and high 
            -> where year(date)=2018 and month(date)=12;
  嵌套查询：`select 表字段列表 from 表名 where 筛选条件 having 过滤条件`
    + select语句放在where之后的()内，将其查询结果当作筛选条件的值来使用
        # 查询运维部和财务部所有员工信息
        mysql> select * from employees where dept_id in (select dept_id from departments where dept_name in ("运维部","财务部"));
        # 查询人事部2018年12月所有员工的工资
        mysql> select employee_id,basic,bonus from salary 
            -> where year(date)=2018 and month(date)=12 and employee_id in (select employee_id from employees 
            -> where dept_id=(select dept_id from departments where dept_name="人事部"));
        mysql> select e.employee_id,basic,bonus from employees as e inner join salary as s on e.employee_id=s.employee_id 
            -> inner join departments as d on e.dept_id=d.dept_id 
            -> where year(date)=2018 and month(date)=12 and d.dept_name="人事部";
    + select语句放在having之后的()内，将其查询结果当作过滤条件的值来使用
        # 输出总人数比开发部总人数少的部门编号及总人数
        mysql> select dept_id as 部门编号,count(name) as total from employees group by dept_id 
            -> having total<(select count(name) from employees 
            -> where dept_id=(select dept_id from departments where dept_name='开发部'));
    + select语句放在from之后的()内，将其查询结果当作临时表来使用。"该临时表必须使用别名 `from () as 临时表别名`"
        # 查询3号部门、部门名称及其部门内员工的编号、名字和email
        mysql> select dept_name,employee_id,name,email
            -> from (select d.dept_name,e.* from departments as d inner join employees as e on d.dept_id=e.dept_id) as haha
            -> where dept_id=3;
    + select语句放在select之后的()内，将其查询结果当作表字段来使用
        # 查询每个部门的人数：dept_id dept_name 部门人数
        mysql> select d.*,(select count(name) from employees as e where d.dept_id=e.dept_id) as 部门人数 from departments as d;

插入表数据：
  values赋值：
    + `insert into 库名.表名 values(值列表1);`：插入一行
    + `insert into 库名.表名 values(值列表1),(值列表2),(值列表n);`：插入多行
    + `insert into 库名.表名(表字段a,表字段b) values("值a","值b");`：插入一行，只增加指定表字段内容
    + `insert into 库名.表名(表字段a,表字段b) values("值a1","值b1"),("值a2","值b2"),("值an","值bn");`：插入多行，只增加指定表字段内容
  set命令赋值：
	+ `insert into 库名.表名 set 表字段1=值1,表字段n=值n;`
  select查询结果赋值：
    + `insert into 库名1.表名1(select * from 库名2.表名2 where 筛选条件);`
    + `insert into 库名1.表名1(被赋值表字段1,被赋值表字段n)(select 查询表字段1,查询表字段n from 库名2.表名2 where 筛选条件);` 
        注意：`被赋值字段` 与 `查询表字段` 的名称可以不同，但数量和数据类型要对应。

修改表数据：`update 库名.表名 set 表字段1=值1,表字段n=值n [where 筛选条件];`

删除表数据：`delete from 库名.表名 [where 筛选条件];`
清空表数据：`truncate table 库名.表名;`
	注意：对于有自增长的表字段，truncate清空后新行从1起计，delete清空后新行继续之前起计。
	注意：truncate不能回滚（撤销操作找回数据），delete可以（涉及事务，可能可以回滚）。

筛选条件和过滤条件

`where 筛选条件` 的筛选条件或 `having 过滤条件` 的过滤条件：

是否相等：`where 表字段="字符串"` 
是否不等：`where 表字段!="字符串"` 
数值比较：`where 表字段 比较符 数字`，比较符两侧可以无空格，比较符：=  !=  >  >=  <  <=

范围匹配： in 、 not in 、 between and
	在范围内：`where 表字段 in ("字符串1","字符串2","字符串n")`
			where id in ("4","5","6")
			where id in (4,5,6)
	不在范围内：`where 表字段 not in ("字符串1","字符串2","字符串n")`
			where name not in ("root","mysql")
	数字区间内：`where 表字段 between 数字1 and 数字2`
			where id between 4 and 6

模糊匹配：`where 表字段 like '字符串'`（ _ 单个字符  % 任意个字符）
			where shell like "_b_n/bash"
			where name like "__%_"
正则匹配：`where 表字段 regexp '正则表达式'`
			where name regexp "^[0-9]"
			where name regexp "^r.*t$|[0-9]"

与：`where 筛选条件1 and或&& 筛选条件2`
或：`where 筛选条件1 or或|| 筛选条件2`
非：`where not 筛选条件` 
	注意：默认 `and` 优先级高于 `or`，可以使用 `()` 提高优先级
	`where 筛选条件1 or 筛选条件2 and 筛选条件3`：先判断 `筛选条件2 and 筛选条件3`，再 `筛选条件1 or and的判断结果`。
	`where (筛选条件1 or 筛选条件2) and 筛选条件3`：先判断 `筛选条件1 or 筛选条件2`，再 `or的判断结果 and 筛选条件3`。

是否空：`where 表字段 is null`
是否非空：`where 表字段 is not null`
	注意：空字符串 '' 不是 null

区分大小写：`where binary 筛选条件`
	注意：binary只对紧跟的单个筛选条件生效
	`where binary 筛选条件1 and 筛选条件2`：binary只对筛选条件1生效
	`where 筛选条件1 and binary 筛选条件2`：binary只对筛选条件2生效

查询结果的处理

查询结果的处理
显示效果：
    别名：`表字段或表名 as 别名` 或 `表字段或表名 别名` 
    去重：`distinct 表字段` 
            select distinct name,shell from test.user where id<5;		# 对name去重
    拼接：`concat(表字段, "拼接符号", 表字段)`
            select concat(name,"-",homedir,"<+",id,"=?",shell) as 用户信息  from user where id<5;

排序：`order by 表字段1 [排序方式],表字段2 [排序方式]`，默认升序`asc`，降序`desc`
分组：`group by 表字段`
		select dept_id,count(name) from employees group by dept_id;	# 根据dept_id统计部门人数
过滤：`having 过滤条件`，过滤一般与分组搭配使用
		select dept_id,count(name) from employees group by dept_id having count(name)>30;
分页：`limit 总行数`
	`limit 起始索引行号,总行数`，索引行号从0起计
	`limit 总行数 offset 起始索引行号`，索引行号从0起计
		select * from user where id<=10 limit 4 offset 0;	# 查询结果的前四行

# 查询人数少于开发部的部门和人数
1. 查询开发部的部门人数：select count(name) from employees where dept_id = (select depy_id from departments where dept_name = '开发部')
2. 查询每个部门的部门人数：select dept_id as 部门编号, count(name) as 部门人数 from employees group by depy_id
3. 过滤部门人数少于开发部的部门：
select dept_id as 部门编号, count(name) as 部门人数 from employees group by depy_id having 部门人数 < (select count(name) from employees where dept_id = (select depy_id from departments where dept_name = '开发部'));

MySQL函数

信息函数：`select 函数名(参数)`
    + `select version();`：查看当前MySQL的版本。
    + `select user();`：查看当前登陆用户。
    + `select database();`：查看当前所在的库。

字符函数作用：处理字符或字符类型的表字段的值。"不对原值产生影响"。
    + `length("字符串")`：返回"字节数"。
      `char_length("字符串")`：返回"字符数"。MySQL数据库一个汉字3个字节。
    + `upper("字符串")` 或 `ucase("字符串")` ：字母"大写"。
      `lower("字符串")` 或 `lcase("字符串")` ：字母"小写"。
    + `substr("字符串", 起始字符位置, 结束字符位置)`：返回"子字符串"。
    + `instr("长字符串","短字符串")`：返回短字符串在长字符串中的起始位置。
    + `trim("字符串")`：返回去除两侧空格的字符串。

数学函数作用：处理数字或数值类型的表字段的值。
    + `abs(数字)`：数字的绝对值。
    + `pi()`：圆周率，6位小数。
    + `round(数字, 位数)`：四舍五入。省略位数默认为0（约至个位）。位数可以为负数，例如，-1则约至十位。
    + `mod(被除数,除数)`：取余。
    + `ceiling(数字)` 或 `ceil(数字)`：向上取整。
    + `floor(数字)`：向下取整。

日期函数作用：获取系统或指定的日期与时间。时间串若为字符串要符合时间要求。
    + `curdate()`：获取当前日期。
      `curtime()`：获取当前时间。
      `now()`：获取当前日期和时间。
    + `year(时间串)`、`month(时间串)`、`day(时间串)`、`date(时间串)`：获取年、月、日、日期。
      `hour(时间串)`、`minute(时间串)`、`second(时间串)`、`time(时间串)`：获取时、分、秒、时间。
    + `monthname(时间串)`：获取月份名称。
      `dayname(时间串)`：获取日期对应的星期几名。
    + `quarter(时间串)`：获取一年中第几季度。
      `week(时间串)`：获取一年中第几周。
      `weekday(时间串)`：获取一周中的第几天。
    + `dayofyear(时间串)`：获取一年中第几天。
      `dayofmonth(时间串)`：获取一月中第几天。

聚集函数作用：统计数据。
    + `avg(表字段)`（average）：求平均值。
      `sum(表字段)`：求和。
    + `min(表字段)`：最小值。
      `max(表字段)`：最大值。
    + `count(表字段)`：统计个数。
      `count(*)`：统计行数。

判断函数：
    + `if(条件,"true结果内容","false结果内容")` ：判断条件是否成立并返回相应内容。
    + `ifnull("字符串1","字符串2")`：判断字符串1是否非空，非空返回 `字符串1`，空返回 `字符串2`。类似 `${变量:-字符串}` 。

case函数：
    + `case 表字段 ` 
      `when 值1 then 输出内容1 ` 
      `when 值2 then 输出内容2 ` 
      `when 值n then 输出内容n ` 
      `else 输出内容 ` 
      `end` 
    + `case` 
      `when 判断条件1 then 输出内容1`
      `when 判断条件2 then 输出内容2 `
      `when 判断条件n then 输出内容n `
      `else 输出内容 `
      `end` 
    + 特殊地，当输出内容相同时，可以使用`in ()`：
      `case` 
      `when 表字段 in (值1,值2,值n) then 输出内容1`
      `else 输出内容`
      `end`

表的管理

MySQL常用数据类型：
    + 字符类型：定长`char(字符个数)`，变长`varchar(字符个数)`
    + 数值类型：整型：`tinyint`、`smallint`、`int`、`bigint`，浮点：`float`、`double`
    	`整型或浮点 unsigned`：无符号存储（非负数）。例如：`int`范围为-2的31次方～2的31次方-1，`int unsigned`范围为0～2的32次方-1。
    	`整型(位数) zerofill`：值不够指定位数则前面用0填充。例如：`int(4) zerofill`，则10存储为`0010`，100存储为`0100`。
    + 枚举类型：只能选择一个值（单选）`enum("字符串1","字符串n")`，选择至少一个值（多选）`set("字符串1","字符串n")`
    + 日期类型：YYYY`year`、YYYYMMDD`date`、HHMMSS`time`、YYYYMMDDHHMMSS`datetime`、YYYYMMDDHHMMSS`timestamp`
    
查看当前库所有表：`show tables;`
查看表的创建语句：`show create table 库名.表名;`
查看表的所有表字段的名字和约束：`desc 库名.表名;` 或 `desc 库名.表名\G`
查看索引的详细信息：`show index from 库名.表名\G`，索引名是"Key_name"
查看查询过程中是否使用索引：`explain select语句\G`，使用的索引查找"key"，使用条件查找"Extra"

建表常用三范式：范式（NF，Normal Form）：1NF、2NF、3NF、4NF、5NF、BCNF。一般第三范式3NF就可以满足需求。
    + 1NF：每列保持原子性（即不可拆分）。例如：联系方式可以拆分为手机、邮箱、座机、微信等，而手机不可拆分。
    + 2NF：满足1NF，且表中的非关键属性依赖于整个候选关键字。
    + 3NF：满足2NF，且消除传递依赖，表中的非关键属性不应该依赖于其他非关键属性。例如：出生日期和年龄不应该同时出现在一个人的信息中。

创建表：
  不加约束：
	+ `create table 库名.表名(表字段1 数据类型1, 表字段n 数据类型n);`
  基本约束：非空`not null`，默认值`default "默认值"`，唯一可空`unique`
    + `create table 库名.表名(表字段1 数据类型1 [not null] [default "默认值"] [unique], 表字段n 数据类型n);`
  主键约束："唯一非空"`primary key`。一个表只能有一个主键约束
  	+ 方式一：`create table 库名.表名(表字段1 数据类型1 [基本约束] primary key, 表字段n 数据类型n);`
  	+ 方式二：`create table 库名.表名(表字段1 数据类型1 [基本约束], 表字段n 数据类型n, primary key(表字段1));`
  复合主键约束：复合主键的多个表字段作为一个整体判断：唯一（"不同时重复"）非空
  	+ `create table 库名.表名(表字段1 数据类型1 [基本约束], 表字段2 数据类型2 [基本约束], 表字段n 数据类型n, primary key(表字段1,表字段2));`
  自增长（自加1，数据必须是"整型"）：`auto_increment`
  	+ `create table 库名.表名(表字段 整型类型 auto_increment primary key);`
	+ `create table 库名.表名(表字段1 整型类型1 auto_increment, 表字段2 整型类型2, primary key(表字段1, 表字段2));`
	+ 注意：对于有自增长的表字段，truncate清空后新行从1起计，delete清空后新行继续之前起计。
  外键约束：`foreign key(从字段) references 主表(关联字段)`在从表添加外键约束，用于保证从表的从字段的值来自于主表的关联字段的值。
  	+ 主表和从表的存储引擎engine必须是"InnoDB"。MySQL 8版本默认的存储引擎是InnoDB。
  	+ 主表的关联字段一般有主键约束
  	+ 从表的从字段可以重复使用主表的关联字段的值，也可以为null，因此从字段需要使用主键约束。
  	+ 主表：`create table 库名.主表(关联表字段 数据类型 primary key, 表字段m 数据类型) engine=innodb;`
  	  从表：`create table 库名.从表(从表字段 数据类型 primary key, 表字段n 数据类型, foreign key(从表字段) references 库名.主表(关联表字段) [on update cascade] [on delete cascade]) engine=innodb;`
  	  	+ "on update cascade"从字段同步关联字段更新修改，"on delete cascade"从字段同步关联字段删除
  	  	+ 创建员工表和工资表：工资表的gz_id同步员工表的yg_id
  	  	mysql> create table db1.yg(yg_id int auto_increment primary key, name varchar(7)) engine=innodb;
		mysql> create table db1.gz(gz_id int primary key, salary float, foreign key(gz_id) references db1.yg(yg_id) on update cascade on delete cascade) engine=innodb;
  索引：`create table 库名.表名(表字段1 数据类型,表字段n 数据类型,index(表字段1));`：索引名默认同表字段。

复制表：
  复制表字段和数据，不复制key：
  	+ `create table 库名.表名 select语句;`
  复制表字段和key，不复制数据：
  	+ `create table 库名.表名 like 库名.旧表;`

删除表：`drop table 库名.表名;`

修改表：
  rename 修改表名：
    + `alter table 库名.表名 rename 库名.新表名;`
  add 添加表字段：
	+ `alter table 库名.表名 add 新表字段1 数据类型1 [基本约束] [auto_increment] [位置], add 新表字段2 数据类型2 [基本约束] [位置], primary key(表字段1[,表字段2]);`：
		+ 位置：`after 旧表字段`旧表字段之后，`first`首位，省略位置默认末位。
  drop 删除表字段：
  	+ `alter table 库名.表名 drop 旧表字段1, drop 旧表字段2;`
  modify "移动"表字段的位置：
  	+ `alter table 库名.表名 modify 旧表字段 旧数据类型 位置;`
  	+ 位置：`after 旧表字段`旧表字段之后，`first`首位。 
  modify 修改表字段的数据类型、约束：
  	+ `alter table 库名.表名 modify 旧表字段 新数据类型 [基本约束] [auto_increment] [primary key];`
  change 修改表字段的"名称"、数据类型、约束：
  	+ `alter table 库名.表名 change 旧表字段 新表字段 新数据类型 [基本约束];`
  discard tablespaces:
  	+ `alter table 库名.表名 discard tablespaces;`删除表空间文件
  
  旧表添加主键约束（一个表只能有一个主键约束）：
  	+ `alter table 库名.表名 add primary key(旧表字段);`
  删除主键约束（一个表只能有一个主键约束）：
  	+ `alter table 库名.表名 drop primary key;`

  从表添加外键约束：`alter table 库名.从表 add foreign key(从表字段) references 库名.主表(主表字段) on update cascade on delete cascade engine=innodb;`
  删除外键约束：`alter table 库名.从表 drop foreign key "外键名";`
  	查看"外键名"：`show create table 库名.从表 \G`，查看"CONSTRAINT `外键名`"

  旧表添加索引：`create index 索引名 on 库名.表名(表字段);`
  删除索引：`drop index 索引名 on 库名.表名;`
  验证索引：`explain select语句`

	mysql> desc test.salary;
        +-------------+------+------+-----+---------+----------------+
        | Field       | Type | Null | Key | Default | Extra          |
        +-------------+------+------+-----+---------+----------------+
        | id          | int  | NO   | PRI | NULL    | auto_increment |	PRI主键，auto_increment自增长
        | date        | date | YES  |     | NULL    |                |
        | employee_id | int  | YES  | MUL | NULL    |                |	MUL索引
        | basic       | int  | YES  |     | NULL    |                |
        | bonus       | int  | YES  |     | NULL    |                |
        +-------------+------+------+-----+---------+----------------+

检索目录：MySQL中，进行数据的导入导出操作时，存放数据的文件（.txt）要存储在mysql-server的检索目录下才能被识别。
	+ 在"/etc/my.cnf.d/mysql-server.cnf"中定义检索目录的位置，默认为`secure_file_priv=/var/lib/mysql-files/`
	+ 用户mysql要对检索目录有rwx权限，对检索目录的父目录有r-x权限。
	+ `show variables like "secure_file_priv";`：查看检索目录。		# `show variables;`查看配置
	+ `system vim /etc/my.cnf.d/mysql-server.cnf`：修改检索目录。

导入数据：将文件内容（文件内容需有规律）存储到数据库的表（需先创建表结构：表字段、数据类型、约束）中
	+ `load data infile "检索目录路径/导入文件" into table 库名.表名 fields terminated by "列分隔符" lines terminated by "行分隔符";`
		mysql>load data infile "检索目录路径/passwd" into table db1.user3 fields terminated by ":" lines terminated by "\n";

导出数据：列分隔符默认为制表符，行分隔符默认为换行符
	+ `select语句 into outfile "检索目录路径/导出文件" [fields terminated by "列分隔符"] [lines terminated by "行分隔符"];`
		mysql> select * from user into outfile '/var/lib/mysql-files/user.txt';

修改库名：创建新库——》创建新表（复制旧表的表字段和key）——》支持中文字符——》确定检索目录——》导出旧表的数据——》将数据导入新表
1.mysql> create database new default character set utf8mb4 default collate utf8mb4_general_ci;
2.mysql> create table new.user like old.user;  
3.mysql> show variables like "%char%";
  mysql> set character_set_database=utf8mb4;
4.mysql> show variables like 'secure_file_priv';
    +------------------+-----------------------+
    | Variable_name    | Value                 |
    +------------------+-----------------------+
    | secure_file_priv | /var/lib/mysql-files/ |
    +------------------+-----------------------+
    1 row in set (0.00 sec)
5.mysql> select * from old.user into outfile '/var/lib/mysql-files/user.txt';
6.mysql> load data infile '/var/lib/mysql-files/user.txt' into table new.user fields terminated by '\t' lines terminated by '\n';

用户管理

用户管理：
    + 创建用户：`create user 用户名@"客户端IP地址" identified by "密码";` 
    	+ '%' 表示任意IP地址，这意味着该用户可以从任何主机连接到数据库服务器。
    + 删除用户：`drop user 用户名@"客户端IP地址";` 
	+ 清空用户密码：`update mysql.user set authentication_string="" where host="客户端IP地址" and user="用户名"`
    + 重置用户密码：`set password for 用户名@"客户端IP地址"="密码";` 
    + 查看所有用户：`select user,host from mysql.user;` 
    + 客户端连接数据库：`mysql -h服务端IP地址 -u用户名 -p密码;` 
    + 查看当前登录用户名及IP地址：`select user();` 

用户权限：
    + 查看表数据 `select`、插入表数据 `insert`、更改表数据 `update`、删除表数据 `delete` 
    + 创建 `create`、删除 `drop`、修改 `alter`、查看所有库 `show databases` 
    + 导入数据 `file`、外键 `references`、索引 `index` 
    + 创建用户 `create user` 

用户权限管理：
    + 查看用户权限：`show grants for 用户名@"客户端IP地址";` 
    + 查看自身权限：`show grants;` 
    + 添加或追加用户权限：`grant 权限列表 on 库名.表名 to 用户名@"客户端IP地址";` 
    + 撤销用户权限：`revoke 权限列表 on 库名.表名 from 用户名@"客户端IP地址";` 
      + 客户端IP地址：本地`localhost`、所有`"%"` 、网段`"192.168.88.0/24"`或`"192.168.88.%"` 
      + 权限列表：所有权限`all`、无权限`usage`、部分权限`权限1,权限2,权限n`、部分字段`权限1,权限2(字段1,字段2),权限n`

mysql库存储用户权限信息，主要有以下表：
    + mysql.user：保存所有用户，及用户对所有库的权限
    + mysql.db：保存用户对某一个库的访问权限
    + mysql.tables_priv：保存用户对某一个表的访问权限
    + mysql.columns_priv：保存用户对某一个表字段的访问权限

忘记密码

破解密码步骤：修改mysqld服务运行方式——》删除原密码——》使修改生效——》让mysqld服务正常运行——》设置新密码。
- MySQL 8对密码格式有要求，MySQL 5.7则没有

// 破解密码
mysql数据库：
// 修改mysqld服务运行方式
[root@mysql ~]# vim /etc/my.cnf
     13 [mysqld]
     14 skip-grant-tables
[root@mysql ~]# systemctl restart mysqld.service

// 删除原密码
[root@mysql ~]# mysql -e 'desc mysql.user' | grep "authen"
	authentication_string   text    YES             NULL
[root@mysql ~]# mysql -e 'update mysql.user set authentication_string="" where host="localhost" and user="root"'

// 使修改生效。重新加载权限表并立即使所有对权限的更改生效
[root@mysql ~]# mysql -e 'flush privileges'

// 让mysqld服务正常运行
[root@mysql ~]# vim /etc/my.cnf.d/mysql-server.cnf
     13 [mysqld]
     14 #skip-grant-tables
[root@mysql ~]# systemctl restart mysqld.service

// 设置新密码
[root@mysql ~]# mysqladmin [-h localhost] -u root password '123'			# 设置密码方式一
[root@mysql ~]# mysql -e 'alter user root@localhost identified by "123"'	# 设置密码方式二

图形软件phpMyAdmin

// 数据库安装图形软件phpMyAdmin
mysql数据库：
[root@mysql ~]# ls 
	nginx-1.22.1.tar.gz  phpMyAdmin-5.2.1-all-languages.zip

// 部署nginx
[root@mysql ~]# yum -y install gcc make pcre-devel zlib-devel
[root@mysql ~]# tar -xf nginx-1.22.1.tar.gz
[root@mysql ~]# cd nginx-1.22.1
[root@mysql nginx-1.22.1]# ./configure
[root@mysql nginx-1.22.1]# make && make install
[root@mysql ~]# vim /usr/local/nginx/conf/nginx.conf
     43         location / {
     44             root   html;
     45             index  index.php index.html index.htm;
     46         }
     65         location ~ \.php$ {			
     66             root           html;			
     67             fastcgi_pass   127.0.0.1:9000;	
     68             fastcgi_index  index.php;	
     70             include        fastcgi.conf;
     71         }
[root@mysql ~]# /usr/local/nginx/sbin/nginx	

// 部署php
[root@mysql ~]# yum install -y php php-fpm php-mysqlnd php-json php-devel
[root@mysql ~]# vim /etc/php-fpm.d/www.conf
    38 ;listen = /run/php-fpm/www.sock
    39 listen = 127.0.0.1:9000  			
[root@mysql ~]# systemctl enable php-fpm.service --now

// 部署phpMyAdmin
[root@mysql ~]# yum install -y unzip
[root@mysql ~]# unzip phpMyAdmin-5.2.1-all-languages.zip
[root@mysql ~]# mv phpMyAdmin-5.2.1-all-languages  /usr/local/nginx/html/phpmyadmin
[root@mysql ~]# cd /usr/local/nginx/html/phpMyAdmin
[root@mysql phpMyAdmin]# cp config.sample.inc.php config.inc.php 
[root@mysql phpMyAdmin]# vim config.inc.php 
     16 $cfg['blowfish_secret'] = '1234.com'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
     30 $cfg['Servers'][$i]['host'] = 'localhost';

// 使用
客户端浏览器访问 http://数据库IP地址/phpMyAdmin，使用数据库用户密码登陆。

数据备份与恢复

物理备份（冷备）

物理备份（完全备份）是直接对数据库目录进行操作，一般为"冷备"（因为开始备份后不会备份新写入的数据）：
+ 备份（完全备份）：先停止数据库服务，再备份数据
  + 第一种方式，直接复制：`cp -ar /var/lib/mysql /备份目录/mysql.bak` 
  + 第二种方式，压缩：先`cd /var/lib/mysql`，再`tar -zcf /备份目录/mysql.tar.gz ./*`，由于tar压缩父目录路径，因此要先进入父目录。
+ 恢复：先停止数据库服务并删除原有的数据库数据 `rm -rf /var/lib/mysql/*`，再恢复数据，再重启数据库服务
  + 第一种方式，直接复制：先`cp -ar /备份目录/mysql.bak/* /var/lib/mysql` 
  + 第二种方式，解压缩：`tar -xf /备份目录/mysql.tar.gz -C /var/lib/mysql` 

# 物理备份数据库：停服务——》备份数据（拷贝或压缩）
mysql0服务器（192.168.88.50）：
[root@mysql0 ~]# systemctl stop mysqld.service
[root@mysql0 ~]# mkdir /bakdir
[root@mysql0 ~]# cp -ar /var/lib/mysql /bakdir/mysql.bak	// 第一种备份方式，直接拷贝，使用选项-a保留所有者所属组。
[root@mysql0 ~]# cd /var/lib/mysql
[root@mysql0 mysql]# tar -zcf /bakdir/mysql.tar.gz ./* 		/* 第二种备份方式，会压缩父目录路径因此要先进入父目录*/
[root@mysql0 mysql]# scp /bakdir/mysql.tar.gz 192.168.88.51:

# 物理恢复数据库：停服务——》删除数据库原有数据——》恢复数据（拷贝或解压）
mysql1服务器（192.168.88.51）：
[root@mysql1 ~]# systemctl stop mysqld.service
[root@mysql1 ~]# rm -rf /var/lib/mysql/*
[root@mysql1 ~]# tar -xf /bakdir/mysql.tar.gz -C /var/lib/mysql		// 压缩保留了所有者和所属组
[root@mysql1 ~]# systemctl start mysqld.service

逻辑备份

逻辑备份的备份策略有完全备份、差异备份、增量备份。
+ MySQL本身支持完全备份，可以直接使用mysqldump命令实现。
+ MySQL本身不支持增量备份与差异备份，可以通过xtrabackup来实现，xtrabackup命令的软件`percona`。

mysqldump（热备锁表）

mysqldump是软件mysql提供的命令，可以用于逻辑备份的完全备份，使用mysqldump必须是"热备"。
  + 使用mysqldump备份和恢复时，数据库必须是运行状态。
  + 但mysqldump备份和恢复数据时会"锁住表的写入"（`vim 备份文件.sql`可以看到`LOCK TABLES 表名 WRITE;`）。其它连接对该表的select和insert都要等待，备份完成之后才可以执行。因此mysqldump适合备份数据量小的数据，在网站访问量少的时候备份。
  + mysqldump的恢复是"覆盖"恢复。

备份数据：
  + 备份表：`mysqldump -u用户名 -p密码 库名 表名1 表名n > /备份目录/备份文件名.sql` 
  + 备份库：`mysqldump -u用户名 -p密码 -B 库名1 库名n > /备份目录/备份文件名.sql` （备份单个库可以省略 `-B`）
  + 备份全部库：`mysqldump -u用户名 -p密码 -A > /备份目录/备份文件名.sql` 
恢复数据：`mysql -u用户名 -p密码 库名 < /备份目录/备份文件名.sql` 
  + 如果备份文件是库，可以省略库名，如果备份文件是表，不能省略库名

例如：数据库系统root密码456，test库下有表salary、employees、departments
1.备份表：mysqldump -u用户名 -p密码 库名 表名1 表名n > /备份目录/备份文件名.sql
mysql0服务器（192.168.88.50）：
[root@mysql0 ~]# mysqldump -uroot -p456 test salary > /bakdir/test_salary.sql
[root@mysql0 ~]# mysqldump -uroot -p456 test employees departments > /bakdir/test_em_de.sql

2.备份库：mysqldump -u用户名 -p密码 -B 库名1 库名n > /备份目录/备份文件名.sql
[root@mysql0 ~]# mysqldump -uroot -p456 test > /bakdir/test.sql				// 单个库可以省略-B
[root@mysql0 ~]# mysqldump -uroot -p456 -B test db1 > /bakdir/test_db1.sql

3.备份全部库：mysqldump -u用户名 -p密码 -A > /备份目录/备份文件名.sql
[root@mysql0 ~]# mysqldump -uroot -p456 -A > /bakdir/allbak.sql

4.mysqldump备份和恢复数据时会锁住表
[root@mysql0 ~]# less /bakdir/test_em_de.sql
	……省略一万字
	LOCK TABLES `departments` WRITE;		// 可以看到该行。锁住该表的写入。此时不能向该表写入数据直到备份完成。
	……省略一万字
	UNLOCK TABLES;							// 可以看到该行。解锁该表
	……省略一万字
	
5. 恢复：覆盖已有数据
[root@mysql0 ~]# mysql -uroot -p456 库名 < /bakdir/备份文件名.sql  // 如果备份文件是库，可以省略库名，如果备份文件是表，不能省略库名
[root@mysql0 ~]# ls -l /var/lib/mysql							// 可以通过ls -l查看修改时间来确认是否被覆盖重写	
[root@mysql0 ~]# ls -l /var/lib/mysql/test

xtrabackup（热备不锁表）

MySQL本身不支持增量备份与差异备份，可以通过xtrabackup（名称来源extra）来实现，xtrabackup命令的软件percona。
- xtrabackup能实现热备不锁表，可以对数据做完全备份、差异备份、增量备份及对应的恢复操作。
- Innobackupex 是 Percona 提供的用于备份 MySQL 数据库的工具，主要用于备份 InnoDB 存储引擎的数据。Percona XtraBackup 是基于 Innobackupex 的，Innobackupex 同样是热备不锁表，但MySQL 8 不再支持 Innobackupex 这个工具。
xtrabackup_checkpoints是xtrabackup工具的一个文件，存储在备份目录下。 lsn（Log Sequence Number）是事务日志编号，也就是操作记录范围。其中flushed_lsn是xtrabackup识别数据与数据库差异的依据。
- backup_type：备份类型，可以是 full-backuped（完整备份）或 incremental（增量或差异备份）或 log-applied（恢复重做日志）。
- from_lsn：当前备份的起点，同上一个备份的终点。
- to_lsn：当前备份的终点。
- last_lsn：备份完成时数据库的最新LSN，由于热备不锁表，可能有新写入内容而大于to_lsn。
- flushed_lsn：已经写入磁盘的数据库的LSN，由于写入磁盘需要时间，可能滞后于last_lsn，同to_lsn。
备份数据：新备份目录可以不存在
- 完全备份：xtrabackup --host=127.0.0.1 --user=root --password=密码 --backup --target-dir=新完全备份目录绝对路径 --datadir=/var/lib/mysql
- 增量备份：xtrabackup --host=127.0.0.1 --user-root --password=密码 --backup --target-dir=新备份目录绝对路径 --datadir=/var/lib/mysql --incremental-basedir=参考目录绝对路径。新备份目录的数据是mysql目录的flushed_lsn与参考目录的flushed_lsn的差异部分。
- 差异备份：xtrabackup --host=127.0.0.1 --user-root --password=密码 --backup --target-dir=新备份目录绝对路径 --datadir=/var/lib/mysql --incremental-basedir=完全备份目录绝对路径。新备份目录的数据是mysql目录的flushed_lsn与完全备份目录的flushed_lsn的差异部分。
恢复数据：
1. 准备恢复数据：xtrabackup --prepare --apply-log-only --targer-dir=完全备份目录绝对路径
2. 合并数据：xtrabackup --prepare --apply-log-only --targer-dir=完全备份目录绝对路径 incremental-dir=增量备份目录绝对路径。将增量备份数据合并到完全备份目录。
3. 清空数据库目录/var/lib/mysql
4. 恢复数据：xtrabackup --copy-back --target-dir=完全备份目录绝对路径。会将合并数据后的完全备份目录的数据拷贝到/var/lib/mysql（mysql目录需要为空）。
5. chown -R mysql:mysql /var/lib/mysql，systemctl restart mysqld.service。

MySQL本身不支持增量备份与差异备份，可以通过xtrabackup（名称来源extra）来实现，xtrabackup命令的软件`percona`。
    + xtrabackup能实现热备不锁表，可以对数据做完全备份、差异备份、增量备份及对应的恢复操作。

`xtrabackup_checkpoints`是xtrabackup工具的一个文件，存储在备份目录下。 `lsn`（Log Sequence Number）是事务日志编号，也就是操作记录范围。其中`flushed_lsn`是xtrabackup识别数据与数据库差异的依据。
    + `backup_type`：备份类型，可以是 `full-backuped`（完整备份）或 `incremental`（增量或差异备份）或 `log-applied`（恢复重做日志）。
    + `from_lsn`：当前备份的起点，同上一个备份的终点。
    + `to_lsn`：当前备份的终点。
    + `last_lsn`：备份完成时数据库的最新LSN，由于热备不锁表，可能有新写入内容而大于`to_lsn`。
    + `flushed_lsn`：已经写入磁盘的数据库的LSN，由于写入磁盘需要时间，可能滞后于`last_lsn`，同`to_lsn`。

备份数据：新备份目录可以不存在
    + 完全备份：`xtrabackup --host=127.0.0.1 --user=root --password=密码 --backup --target-dir=新完全备份目录绝对路径 --datadir=/var/lib/mysql` 
    + 增量备份：`xtrabackup --host=127.0.0.1 --user-root --password=密码 --backup --target-dir=新备份目录绝对路径 --datadir=/var/lib/mysql --incremental-basedir=参考目录绝对路径`。新备份目录的数据是mysql目录的`flushed_lsn`与参考目录的`flushed_lsn`的差异部分。
    + 差异备份：`xtrabackup --host=127.0.0.1 --user-root --password=密码 --backup --target-dir=新备份目录绝对路径 --datadir=/var/lib/mysql --incremental-basedir=完全备份目录绝对路径`。新备份目录的数据是mysql目录的`flushed_lsn`与完全备份目录的`flushed_lsn`的差异部分。

恢复数据：
    1. 准备恢复数据：`xtrabackup --prepare --apply-log-only --targer-dir=完全备份目录绝对路径` 
    2. 合并数据：`xtrabackup --prepare --apply-log-only --targer-dir=完全备份目录绝对路径 incremental-dir=增量备份目录绝对路径`。将增量备份数据合并到完全备份目录。
    3. 清空数据库目录`/var/lib/mysql`
    4. 恢复数据：`xtrabackup --copy-back --target-dir=完全备份目录绝对路径`。会将合并数据后的完全备份目录的数据拷贝到`/var/lib/mysql`（mysql目录需要为空）。
    5. `chown -R mysql:mysql /var/lib/mysql`，`systemctl restart mysqld.service`。

增量备份与恢复（每一次）

增量备份数据：每周一完全备份，周二至周日增量备份
  	1. 周一：`xtrabackup --host=127.0.0.1 --user=root --password=456 --backup --target-dir=/opt/fullback --datadir=/var/lib/mysql` 
  	2. 周二：`xtrabackup --host=127.0.0.1 --user=root --password=456 --backup --target-dir=/opt/new2 --datadir=/var/lib/mysql --incremental-basedir=/opt/fullback` 
  	3. 周三：`xtrabackup --host=127.0.0.1 --user=root --password=456 --backup --target-dir=/opt/new3 --datadir=/var/lib/mysql --incremental-basedir=/opt/new2` 
  	4. 周四至周日操作类似周三
增量恢复数据：
  	1. 准备恢复数据：`xtrabackup --prepare --apply-log-only --targer-dir=周一完全备份` 
  	2. 合并数据：`xtrabackup --prepare --apply-log-only --targer-dir=周一完全备份 incremental-dir=周二增量备份`。
     `xtrabackup --prepare --apply-log-only --targer-dir=周一完全备份 incremental-dir=周三增量备份`。一直到周日。
  	3. 清空数据库目录
  	4. 恢复数据：`xtrabackup --copy-back --target-dir=周一完全备份`。
  	5. 递推修改数据库目录所属关系并重启数据库服务：`chown -R mysql:mysql /var/lib/mysql`，`systemctl restart mysqld.service`。

例如：数据库系统root密码456
// 安装percona
mysql服务器（192.168.88.50 和 192.168.88.51）：
[root@mysql ~]# ls percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal.tar.gz
	percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal.tar.gz
[root@mysql ~]# tar -xf percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal.tar.gz
[root@mysql ~]# mv percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal /usr/local/percona
[root@mysql ~]# echo "export $PATH=/usr/local/percona/bin/:$PATH" >> /etc/bashrc
[root@mysql ~]# source /etc/bashrc

// 增量备份：每周周一完全备份，周二至周日增量备份
mysql0服务器（192.168.88.50）：
1.周一完全备份
[root@mysql0 ~]# xtrabackup --host=127.0.0.1 --user=root --password=456 --backup --target-dir=/opt/fullback --datadir=/var/lib/mysql	
[root@mysql0 ~]# mysql -e "insert into test.salary(date,employee_id,basic,bonus) values("20230820",19,13000,3000)" -uroot -p456     // 通过插入数据来模拟增量 
2.周二增量备份
[root@mysql0 ~]# xtrabackup --host=127.0.0.1 --user=root --password=456 --backup --target-dir=/opt/new2 --datadir=/var/lib/mysql --incremental-basedir=/opt/fullback
[root@mysql0 ~]# !mysql
3.周三增量备份
[root@mysql0 ~]# xtrabackup --host=127.0.0.1 --user=root --password=456 --backup --target-dir=/opt/new3 --datadir=/var/lib/mysql --incremental-basedir=/opt/new2
[root@mysql0 ~]# !mysql
4.周四增量备份
[root@mysql0 ~]# xtrabackup --host=127.0.0.1 --user=root --password=456 --backup --target-dir=/opt/new4 --datadir=/var/lib/mysql --incremental-basedir=/opt/new3
[root@mysql0 ~]# !mysql
5.周五增量备份
[root@mysql0 ~]# xtrabackup --host=127.0.0.1 --user=root --password=456 --backup --target-dir=/opt/new5 --datadir=/var/lib/mysql --incremental-basedir=/opt/new4
[root@mysql0 ~]# !mysql
6.周六增量备份
[root@mysql0 ~]# xtrabackup --host=127.0.0.1 --user=root --password=456 --backup --target-dir=/opt/new6 --datadir=/var/lib/mysql --incremental-basedir=/opt/new5
[root@mysql0 ~]# !mysql
7.周日增量备份
[root@mysql0 ~]# xtrabackup --host=127.0.0.1 --user=root --password=456 --backup --target-dir=/opt/new7 --datadir=/var/lib/mysql --incremental-basedir=/opt/new6
    /* 每个备份目录下都有xtrabackup_checkpoints文件
	[root@mysql0 ~]# ls /opt/
		fullback  new2  new3  new4  new5  new6  new7
    [root@mysql0 ~]# cat /opt/fullback/xtrabackup_checkpoints 		
        backup_type = full-backuped		
        from_lsn = 0
        to_lsn = 25627446
        last_lsn = 25627456
        flushed_lsn = 25627446
    */

// 增量恢复
mysql1服务器（192.168.88.51）：
[root@mysql1 ~]# rsync -av root@192.168.88.50:/opt/* /opt
[root@mysql1 ~]# ls /opt
	fullback  new2  new3  new4  new5  new6  new7	
1.准备恢复数据
[root@mysql1 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/fullback
    /* 合并数据后完全备份的full-backuped变为log-applied
    [root@mysql1 ~]# cat /opt/fullback/xtrabackup_checkpoints 
        backup_type = log-applied		// full-backuped变为log-applied
        from_lsn = 0
        to_lsn = 25627446
        last_lsn = 25627456			
        flushed_lsn = 25627446
    [root@mysql1 ~]# cat /opt/new2/xtrabackup_checkpoints 
        backup_type = incremental
        from_lsn = 25627446
        to_lsn = 25630002
        last_lsn = 25630002
        flushed_lsn = 25630002
    */
2.合并数据
[root@mysql1 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/fullback --incremental-dir=/opt/new2
    /* 合并增量数据完全备份的to_lsn值同增量
    [root@mysql1 ~]# cat /opt/fullback/xtrabackup_checkpoints 
        backup_type = log-applied
        from_lsn = 0
        to_lsn = 25630002				// 合并增量数据to_lsn值同增量
        last_lsn = 25630002			
        flushed_lsn = 25630002
    */
[root@mysql1 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/fullback --incremental-dir=/opt/new3
[root@mysql1 ~]# ^new3^new4
[root@mysql1 ~]# ^new4^new5
[root@mysql1 ~]# ^new5^new6
[root@mysql1 ~]# ^new6^new7
3.清空数据库目录
[root@mysql1 ~]# rm -rf /var/lib/mysql/*
4.恢复数据
[root@mysql1 ~]# xtrabackup --copy-back --target-dir=/opt/fullback		// 会将备份的数据恢复到数据库目录
5.递推修改数据库目录归属关系
[root@mysql1 ~]# chown -R mysql:mysql /var/lib/mysql
6.重启数据库服务
[root@mysql1 ~]# systemctl restart mysqld.service

差异备份与恢复（最后一次）

差异备份数据：每周一完全备份，周二至周日差异备份
    1. 周一：`xtrabackup --host=127.0.0.1 --user=root --password=456 --backup --target-dir=/mnt/allfull --datadir=/var/lib/mysql` 
    2. 周二：`xtrabackup --host=127.0.0.1 --user=root --password=456 --backup --target-dir=/mnt/day02 --datadir=/var/lib/mysql --incremental-basedir=/mnt/allfull` 
    3. 周三：`xtrabackup --host=127.0.0.1 --user=root --password=456 --backup --target-dir=/mnt/day03 --datadir=/var/lib/mysql --incremental-basedir=/mnt/allfull` 
  	4. 周四至周日操作类似周三
差异恢复数据：
    1. 准备恢复数据：`xtraba**ckup --prepare --apply-log-only --targer-dir=周一完全备份` 
    2. 合并数据：`xtrabackup --prepare --apply-log-only --targer-dir=周一完全备份 incremental-dir=周日差异备份` 
    3. 清空数据库目录
    4. 恢复数据：`xtrabackup --copy-back --target-dir=周一完全备份` 
    5. 递推修改数据库目录所属关系并重启数据库服务：`chown -R mysql:mysql /var/lib/mysql`，`systemctl restart mysqld.service`。

例如：数据库系统root密码456
// 安装percona
mysql服务器（192.168.88.50 和 192.168.88.51）：
[root@mysql ~]# ls percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal.tar.gz
	percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal.tar.gz
[root@mysql ~]# tar -xf percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal.tar.gz
[root@mysql ~]# mv percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal /usr/local/percona
[root@mysql ~]# echo "export $PATH=/usr/local/percona/bin/:$PATH" >> /etc/bashrc
[root@mysql ~]# source /etc/bashrc

// 差异备份：每周一完全备份，周二至周日差异备份
mysql0服务器（192.168.88.50）：
1.周一的完全备份
[root@mysql0 ~]# xtrabackup --host=127.0.0.1 --user=root --password=456 --backup --target-dir=/mnt/allfull --datadir=/var/lib/mysql
2.周二至周日的差异备份
[root@mysql0 ~]# xtrabackup --host=127.0.0.1 --user=root --password=456 --backup --target-dir=/mnt/day02 --datadir=/var/lib/mysql --incremental-basedir=/mnt/allfull
[root@mysql0 ~]# xtrabackup --host=127.0.0.1 --user=root --password=456 --backup --target-dir=/mnt/day03 --datadir=/var/lib/mysql --incremental-basedir=/mnt/allfull
[root@mysql0 ~]# ^day03^day04
[root@mysql0 ~]# ^day04^day05
[root@mysql0 ~]# ^day05^day06
[root@mysql0 ~]# ^day06^day07
    /*
	[root@mysql0 ~]# ls /mnt
		allfull  day02  day03  day04  day05  day06  day07
    [root@mysql0 ~]# cat /mnt/allfull/xtrabackup_checkpoints 
        backup_type = full-backuped
        from_lsn = 0
        to_lsn = 25670875
        last_lsn = 25670875
        flushed_lsn = 25670875
    [root@mysql0 ~]# cat /mnt/day02/xtrabackup_checkpoints 
        backup_type = incremental
        from_lsn = 25670875					// 同incremental-basedir完全备份的to_lsn
        to_lsn = 25670895
        last_lsn = 25670905
        flushed_lsn = 25670895
    [root@mysql0 ~]# cat /mnt/day03/xtrabackup_checkpoints 
        backup_type = incremental
        from_lsn = 25670875					// 同incremental-basedir完全备份的to_lsn
        to_lsn = 25672433
        last_lsn = 25672443
        flushed_lsn = 25672433
    */

// 差异恢复
mysql1服务器（192.168.88.51）：
[root@mysql1 ~]# rsync -av 192.168.88.50:/mnt/allfull  /mnt 		// 拷贝完全备份
[root@mysql1 ~]# rsync -av 192.168.88.50:/mnt/day07  /mnt 			// 拷贝最后一次差异备份
[root@mysql1 ~]# ls /mnt
	allfull  day07
1.准备恢复数据
[root@mysql1 ~]# xtrabackup --prepare --apply-log-only --target-dir=/mnt/allfull
2.合并数据
[root@mysql1 ~]# xtrabackup --prepare --apply-log-only --target-dir=/mnt/allfull --incremental-dir=/mnt/day07
3.清空数据库目录
[root@mysql1 ~]# rm -rf /var/lib/mysql/*
4.恢复数据
[root@mysql1 ~]# xtrabackup --copy-back --target-dir=/allfull		// 会将备份的数据恢复到数据库目录
5.递推修改归属关系
[root@mysql1 ~]# chown -R mysql:mysql /var/lib/mysql
6.重启数据库服务
[root@mysql1 ~]# systemctl restart mysqld.service

日志实时备份 mysqlbinlog

binlog日志的自定义和管理

binlog日志（也叫二进制日志）：是MySQL服务日志文件的一种，保存"除select之外"的所有SQL命令，因此可以通过实时重复日志文件中的SQL命令来达到数据备份的效果。
    + binlog日志是配置mysql主从同步的必要条件。
    + MySQL 8版本中，binlog日志默认是开启的，日志文件存储在 `/var/lib/mysql`
      + `/var/lib/mysql/binlog.index `：日志的索引文件。记录当前所有日志文件的路径。
      + `/var/lib/mysql/binlog.编号`：日志文件。编号六位数。
    + binlog日志文件默认容量1G，超出后会自动创建新日志。可以在配置文件中通过 `max_binlog_size=数字m` 修改。
    + 日志文件没写满时，执行的所有写命令都会保存到当前使用的日志文件里，日志文件写满时会自动创建新的日志文件。

自定义binlog日志（配置文件`/etc/my.cnf.d/mysql-server.cnf` 中 `[mysqld]` 的辐射范围内，需要先关闭selinux）：
    + `server-id=数字`：指定本数据库服务器的编号（1-255），用以区别其他数据库服务器。任意取值，约定同主机位。
    + `log-bin=/目录名/日志名`：开启日志，指定日志文件的父目录与文件名（编号无法干预）
    + `max_binlog_size=数字m`：指定日志文件容量，默认为1G。一般不修改。

管理binlog日志
  创建新日志：
    + 库命令刷新日志，每次刷新创建一个新日志文件：`flush logs;` 
    + 数据库每次重启会创建一个新日志文件。
    + mysqldump完全备份并创建新日志文件（一个库创建一个日志文件）：
      + `mysqldump -uroot -p密码 -B 库名 --flush-logs > /目录/备份文件名.sql` 
      + `mysqldump -uroot -p密码 -A --flush-logs > /目录/备份文件名.sql` 
  查看日志：
    + 查看正在使用的日志：`show master status;` 
    + 查看已有的日志：`show binary logs;` 
    + 查看日志文件内容：`show binlog events in "日志文件名";` 
    + 命令行模式下查看日志：`mysqlbinlog 日志文件路径`
  删除日志：删除日志不会影响数据库中的数据
    + 删除指定日志之前的所有日志：`purge master logs to "日志文件名";` 
    + 删除所有日志：`reset master;` 

mysql0服务器（192.168.88.52）：
mysql> show master status;		# 查看当前使用的日志文件
    +---------------+----------+--------------+------------------+-------------------+
    | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +---------------+----------+--------------+------------------+-------------------+
    | binlog.000001 |      156 |              |                  |                   |
    +---------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

mysql> create database db1;
mysql> create table db1.user(name varchar(6));
mysql> show master status;		# 日志文件会记录select语句之外的所有SQL命令，Position值会变化
    +---------------+----------+--------------+------------------+-------------------+
    | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +---------------+----------+--------------+------------------+-------------------+
    | binlog.000001 |      536 |              |                  |                   |
    +---------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
mysql> system cat /var/lib/mysql/binlog.index	# binlog.index记录当前所有日志文件的路径
	./binlog.000001

[root@mysql0 ~]# vim /etc/my.cnf.d/mysql-server.cnf 	
    [mysqld]					# 在[mysqld]的辐射范围内新增以下两行。
	server-id=50				# 新增该行。给主机编号，也可省略该行。
    log-bin=/mylog/mysql50   	# 新增该行。开启日志，定义日志目录和日志文件名
[root@mysql0 ~]# mkdir /mylog  	
[root@mysql0 ~]# chown mysql /mylog					// mysql需要对日志目录有读写权限
[root@mysql0 ~]# systemctl restart mysqld.service 	// 每次启动服务都会创建一个新的日志文件
[root@mysql0 ~]# ls /mylog/  						// 自动创建日志文件
	mysql50.000001  mysql50.index
[root@mysql0 ~]# cat /mylog/mysql50.index 
	/mylog/mysql50.000001
mysql> show master status;
    +----------------+----------+--------------+------------------+-------------------+
    | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +----------------+----------+--------------+------------------+-------------------+
    | mysql50.000001 |      156 |              |                  |                   |
    +----------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

mysql> show binary logs;
    +----------------+-----------+-----------+
    | Log_name       | File_size | Encrypted |
    +----------------+-----------+-----------+
    | mysql50.000001 |       156 | No        |
    +----------------+-----------+-----------+
    1 row in set (0.00 sec)

mysql> show binlog events in 'mysql50.000001';
    +----------------+-----+----------------+-----------+-------------+-----------------------------------+
    | Log_name       | Pos | Event_type     | Server_id | End_log_pos | Info                              |
    +----------------+-----+----------------+-----------+-------------+-----------------------------------+
    | mysql50.000001 |   4 | Format_desc    |        52 |         125 | Server ver: 8.0.26, Binlog ver: 4 |
    | mysql50.000001 | 125 | Previous_gtids |        52 |         156 |                                   |
    +----------------+-----+----------------+-----------+-------------+-----------------------------------+
    2 rows in set (0.00 sec)
	/*
    Log_name	：日志文件名。
    Pos        	：命令在日志文件中的起始位置。
    Event_type	：事件类型，例如 Query、Table_map、Write_rows 等。
    server-id	：服务器 ID。
    End_log_pos	：命令在文件中的结束位置，以字节为单位。
    Info		：执行命令信息。
    */

使用日志文件恢复数据

mysqlbinlog 日志文件绝对路径 | mysql –uroot -p密码：mysqlbinlog将日志文件中的SQL命令再执行一遍以实现恢复效果。

使用日志文件恢复数据
    `mysqlbinlog [选项] 日志文件绝对路径 | mysql –uroot -p密码`：mysqlbinlog将日志文件中的SQL命令再执行一遍以实现恢复效果。
    常用选项有：
	指定偏移量范围（show查看binlog）：--start-position=xxx --stop-position=xxx
	指定时间范围（mysqlbinlog查看binlog）：--start-datetime="yyyy/mm/dd h:mm:ss" --stop-datetime="yyyy/mm/dd h:mm:ss"
# 准备日志文件
mysql0服务器（192.168.88.50）：
mysql> reset master;
mysql> create database testlog default character set utf8mb4 default collate utf8mb4_general_ci;
mysql> create table testlog.user(name varchar(8));
mysql> insert into testlog.user values('a'),('avc');
mysql> flush logs;
mysql> insert into testlog.user values('bxp');
mysql> select * from testlog.user;
    +------+
    | name |
    +------+
    | a    |
    | avc  |
    | bxp  |
    +------+
    3 rows in set (0.00 sec)
mysql> show binary logs;
    +----------------+-----------+-----------+
    | Log_name       | File_size | Encrypted |
    +----------------+-----------+-----------+
    | mysql50.000001 |      1155 | No        |
    | mysql50.000002 |       434 | No        |
    +----------------+-----------+-----------+
    2 rows in set (0.00 sec)

# 使用日志文件恢复数据
mysql1服务器（192.168.88.51）：
[root@mysql1 ~]# rsync -av 192.168.88.50:/mylog/mysql50.0* /root
[root@mysql1 ~]# ls mysql50.0*
	mysql50.000001  mysql50.000002
[root@mysql1 ~]# mysqlbinlog mysql50.000002 | mysql -p456
	ERROR 1049 (42000) at line 45: Unknown database 'testlog'		// 创建库testlog的SQL命令在mysql50.000001
[root@mysql1 ~]# mysqlbinlog mysql50.000001 | mysql -p456			// 重复日志文件的SQL命令以实现恢复效果
[root@mysql1 ~]# mysql -e 'select * from testlog.user;' -p456
    +------+
    | name |
    +------+
    | a    |
    | avc  |
    +------+
[root@mysql1 ~]# mysqlbinlog mysql50.000002 | mysql -p456	
[root@mysql1 ~]# mysql -e 'select * from testlog.user;' -p456
    +------+
    | name |
    +------+
    | a    |
    | avc  |
    | bxp  |
    +------+

主从同步

MySQL主从同步是数据库复制技术，可以将主服务器数据库的变更操作同步到一台或多台从服务器的数据库。
    + 主数据库服务器：启用binlog日志，创建用户并授权`replication slave`，查看binlog日志的Position。
    + 从数据库服务器：设置server-id，指定主服务器信息，开启两个进程Slave_IO、Slave_SQL
      + 进程Slave_IO：复制主服务器的binlog日志的SQL命令到本机的relay-log文件（中继日志文件）。
      + 进程Slave_SQL：执行本机relay-log文件的SQL语句，实现与主服务器数据一致。
注意：基于binlog日志的主从同步，只是"同步新数据"，对于部署主从同步之前的旧数据，需要先手工备份和恢复。因此，新增从服务器"需要先同步主服务器的旧数据（人工完全备份）"，再设置主从同步。

主从同步结构：一主一从，一主多从，主从从，互为主从

一主一从和一主多从

注意：基于binlog日志的主从同步，只是"同步新数据"，对于部署主从同步之前的旧数据，需要先手工备份和恢复。因此，新增从服务器"需要先同步主服务器的旧数据（人工完全备份）"，再设置主从同步。

一主多从和一主一从的操作一致：
  主数据库服务器：
    1.启用binlog日志，设置server-id
    2.创建用户并授权`grant replication slave on *.* to 用户@主机;`
    3.查看当前使用的binlog日志信息`show master status;`
  从数据库服务器：
	0.同步旧数据
    1.设置server-id（不能同于主服务器）
    2.指定主服务器信息：
    	+ `change master to master_host="主服务器IP地址",master_user="被授权的数据库用户",master_password="密码",master_log_file="日志文件名",master_log_pos=数字;` 
        + 这里的`master_log_file`、`master_log_pos`的值同主服务器的`show master status;`。
    3.启动slave进程（Slave_IO复制日志的SQL语句、Slave_SQL执行SQL语句）
    4.查看slave进程状态，"确保slave进程运行"
    	+ 注意：如果两个slave进程没有成功启动，则stop slave后重新change master再start slave
    	+ 注意：被授权用户如果更改密码，从服务器需要stop slave后重新change master再start slave
主数据库服务器可以通过 `show processlist;` 查看从数据库服务器。

# 主服务器
mysql0主服务器（192.168.88.50）：
1.启用binlog日志，设置server-id
[root@mysql0 ~]# vim /etc/my.cnf.d/mysql-server.cnf 
	 18 server-id=50
 	 19 log-bin=mysql50
[root@mysql0 ~]# systemctl restart mysqld.service 
2.创建用户并授权
mysql> create user repluser@'%' identified by '123';
mysql> grant replication slave on *.* to repluser@'%';
3.查看当前使用的binlog日志信息
mysql> show master status;
    +----------------+----------+--------------+------------------+-------------------+
    | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +----------------+----------+--------------+------------------+-------------------+
    | mysql50.000001 |      667 |              |                  |                   |
    +----------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

# 从服务器（假设主服务器上没有旧数据）
mysql1从服务器（192.168.88.51）：
1.设置server-id（不能同于主服务器）
[root@mysql1 ~]# vim /etc/my.cnf.d/mysql-server.cnf 
	 18 server-id=51
[root@mysql1 ~]# systemctl restart mysqld.service 
2.指定主服务器信息
mysql> change master to master_host='192.168.88.50',master_user='repluser',master_password='123',
    -> master_log_file='mysql50.000001',master_log_pos=667;
3.启动slave进程
mysql> start slave;
4.查看slave进程状态，确保slave进程运行
mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for source to send event
                      Master_Host: 192.168.88.50
                      Master_User: repluser
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql50.000001
              Read_Master_Log_Pos: 667
                   Relay_Log_File: mysql1-relay-bin.000002
                    Relay_Log_Pos: 322
            Relay_Master_Log_File: mysql50.000001
                 Slave_IO_Running: Yes			// 复制主服务器的binlog日志的SQL命令到本机的relay-log文件（中继日志文件）
                Slave_SQL_Running: Yes			// 执行本机relay-log文件的SQL语句，实现与主服务器数据一致。
    ……省略一万字
                    Last_IO_Error: 				// Slave_IO进程的报错信息
                   Last_SQL_Error: 				// Slave_SQL进程的报错信息
    ……省略一万字

此时，对主服务器MySQL数据库做的所有SQL操作会同步到从服务器上。
主数据库服务器（192.168.88.50）可以通过 `show processlist;` 查看从数据库服务器（192.168.88.51）：
[root@mysql0 ~]# mysql -e 'show processlist;' -p456
    +----+-----------------+---------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
    | Id | User            | Host                | db   | Command     | Time | State                                                           | Info             |
    +----+-----------------+---------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
    |  5 | event_scheduler | localhost           | NULL | Daemon      | 1084 | Waiting on empty queue                                          | NULL             |
    |  9 | root            | localhost           | NULL | Query       |    0 | init                                                            | show processlist |
    | 10 | repluser        | 192.168.88.51:44546 | NULL | Binlog Dump |  338 | Source has sent all binlog to replica; waiting for more updates | NULL             |
    +----+-----------------+---------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
    3 rows in set (0.00 sec)

# 从服务器（假设主服务器上有旧数据）
mysql0主服务器（192.168.88.50）：
[root@mysql0 ~]# mysqldump -p456 -A > /root/all.sql
[root@mysql0 ~]# mysql -e 'show master status;' -p456
    +----------------+----------+--------------+------------------+-------------------+
    | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +----------------+----------+--------------+------------------+-------------------+
    | mysql50.000001 |      963 |              |                  |                   |
    +----------------+----------+--------------+------------------+-------------------+

mysql2从服务器（192.168.88.52）：
0.同步旧数据
[root@mysql2 ~]# rsync -av 192.168.88.50:/root/all.sql /root
[root@mysql2 ~]# ls all.sql 
	all.sql
[root@mysql2 ~]# mysql < all.sql 
1.设置server-id（不能同于主服务器）
[root@mysql2 ~]# vim /etc/my.cnf.d/mysql-server.cnf 
	 18 server-id=52
[root@mysql2 ~]# systemctl restart mysqld.service 
2.指定主服务器信息
mysql> change master to master_host='192.168.88.50',master_user='repluser',master_password='123',
    -> master_log_file='mysql50.000001',master_log_pos=667;
3.启动slave进程
mysql> start slave;
4.查看slave进程状态，确保slave进程运行
mysql> show slave status\G
	……省略一万字
                 Slave_IO_Running: Yes	
                Slave_SQL_Running: Yes		
    ……省略一万字

此时，对主服务器MySQL数据库做的所有SQL操作会同步到从服务器上。
主数据库服务器（192.168.88.50）可以通过 `show processlist;` 查看从数据库服务器：
[root@mysql0 ~]# mysql -e 'show processlist;' -p456
+----+-----------------+---------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
| Id | User            | Host                | db   | Command     | Time | State                                                           | Info             |
+----+-----------------+---------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost           | NULL | Daemon      | 2904 | Waiting on empty queue                                          | NULL             |
| 10 | repluser        | 192.168.88.51:44546 | NULL | Binlog Dump | 2158 | Source has sent all binlog to replica; waiting for more updates | NULL             |
| 14 | repluser        | 192.168.88.52:47750 | NULL | Binlog Dump |   81 | Source has sent all binlog to replica; waiting for more updates | NULL             |
| 15 | root            | localhost           | NULL | Query       |    0 | init                                                            | show processlist |
+----+-----------------+---------------------+------+-------------+------+-----------------------------------------------------------------+------------------+

读写分离 mycat 8066

准备：数据库主从同步，中间件启动mycat

# 主从数据库服务器部署主从同步
mysql0主数据库服务器（192.168.88.50）：
[root@mysql0 ~]# yum -y install mysql-server
[root@mysql0 ~]# vim /etc/my.cnf.d/mysql-server.cnf
	 18 server-id=50
 	 19 log-bin=mysql50
[root@mysql0 ~]# systemctl enable mysqld.service --now
[root@mysql0 ~]# mysql -e "create user repl@'%' identified by '123'"
[root@mysql0 ~]# mysql -e "grant replication slave on *.* to repl@'%'"
[root@mysql0 ~]# mysqldump -A > /root/all.sql
[root@mysql0 ~]# mysql -e "show master status"
    +----------------+----------+--------------+------------------+-------------------+
    | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +----------------+----------+--------------+------------------+-------------------+
    | mysql50.000001 |      156 |              |                  |                   |
    +----------------+----------+--------------+------------------+-------------------+

mysql1从数据库服务器（192.168.88.51）：
[root@mysql1 ~]# yum -y install mysql-server
[root@mysql1 ~]# vim /etc/my.cnf.d/mysql-server.cnf
     18 server-id=51
[root@mysql1 ~]# systemctl enable mysqld.service --now
[root@mysql1 ~]# rsync -av root@192.168.88.50:/root/all.sql /root && mysql < /root/all.sql
mysql> change master to master_host='192.168.88.50',master_user='repl',master_password='123',
    -> master_log_file='mysql50.000001',master_log_pos=156;
mysql> start slave;
mysql> show slave status\G
	……省略一万字
                 Slave_IO_Running: Yes	
                Slave_SQL_Running: Yes		
    ……省略一万字

# 主从数据库服务器创建用户并赋权，以供mycat服务使用
[root@mysql ~]# mysql -e "create user cat@'%' identified by '123'"
[root@mysql ~]# mysql -e "grant all on *.* to cat@'%'" -p456

# 中间件服务器部署mycat服务
0. 安装MySQL并创建用户赋权。mycat需要先连接数据库才能启服务，因此先连接本机MySQL启服务后再连接主从数据库服务器部署读写分离
1. 安装jdk。mycat用Java编写的。mycat2匹配Java1.8版本
2. 安装mycat依赖和mycat
3. 配置mycat：指定mycat连接的数据库服务（mycat启动必须先连接数据库）
4. 配置mycat：设置连接mycat服务的用户、密码
5. 启动mycat
此时，客户端可以通过中间件mycat服务进入连接的MySQL数据库：`mysql -hmycatIP -P8066 -umycat用户 -p密码`

中间件mycat服务器（192.168.88.66）：
0. 安装MySQL并创建用户赋权。mycat需要先连接数据库才能启服务，因此先连接本机MySQL启服务后再连接主从数据库服务器部署读写分离
[root@mycat ~]# yum install -y mysql-server > /dev/null	
[root@mycat ~]# systemctl start mysqld.service 	
[root@mycat ~]# mysql -e "create user cat@'%' identified by '123'"
[root@mycat ~]# mysql -e "grant all on *.* to cat@'%'"
1. 安装jdk。mycat用Java编写的。mycat2匹配Java1.8版本
[root@mycat ~]# yum install -y java-1.8.0-openjdk.x86_64
2. 安装mycat依赖和mycat
[root@mycat ~]# ls mycat2*
	mycat2-1.21-release-jar-with-dependencies.jar  mycat2-install-template-1.21.zip
[root@mycat ~]# yum install -y unzip
[root@mycat ~]# unzip mycat2-install-template-1.21.zip
[root@mycat ~]# cp mycat2-1.21-release-jar-with-dependencies.jar mycat/lib/	
[root@mycat ~]# mv mycat/ /usr/local/
[root@mycat ~]# chmod -R 777 /usr/local/mycat/
[root@mycat ~]# cd /usr/local/mycat/
3. 配置mycat：指定mycat连接的数据库服务（mycat启动必须先连接数据库）
[root@mycat mycat]# cat conf/datasources/prototypeDs.json
    {
            "dbType":"mysql",
            "idleTimeout":60000,
            "initSqls":[],
            "initSqlsGetConnection":true,
            "instanceType":"READ_WRITE",
            "maxCon":1000,
            "maxConnectTimeout":3000,
            "maxRetryCount":5,
            "minCon":1,
            "name":"prototypeDs",
            "password":"123", 						// 修改该行。指定数据库用户密码
            "type":"JDBC",
            "url":"jdbc:mysql://localhost:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",	// 指定连接的数据库
            "user":"cat", 							// 修改该行。指定数据库用户
            "weight":0
    }
4. 配置mycat：设置连接mycat服务的用户、密码
[root@mycat mycat]# cat conf/users/root.user.json
    {^M
            "dialect":"mysql",^M					// 数据库使用的语言
            "ip":null,^M							// 客户端的IP地址，null为不限制，所有客户端都可以访问
            "password":"123456",^M					// 修改该行。自定义服务密码`123456`
            "transactionType":"proxy",^M
            "username":"mycat"^M					// 修改该行。自定义服务用户名`mycat`
    }
5. 启动mycat
[root@mycat mycat]# bin/mycat help
	Usage: /usr/local/mycat/bin/mycat { console | start | stop | restart | status | dump }
[root@mycat mycat]# bin/mycat start
[root@mycat mycat]# bin/mycat status
	mycat2 is running (4858).
[root@mycat mycat]# ss -ntulp | grep :8066
	tcp   LISTEN 0      128                     *:8066             *:*    users:(("java",pid=4860,fd=71))  
[root@mycat mycat]# ls logs/
	mycat.pid  wrapper.log（日志文件，可以存储报错信息）

此时，客户端可以通过中间件mycat服务进入连接的MySQL数据库：`mysql -h192.168.88.66 -P8066 -umycat -p123456`
[root@client ~]# mysql -h192.168.88.66 -P8066 -umycat -p123456	// 通过mycat服务进入连接的数据库
mysql> show databases;											// 与直接进入数据库不同，mycat服务只有三个库
    +--------------------+
    | `Database`         |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    +--------------------+
    3 rows in set (0.08 sec)
[root@client ~]# mysql -h192.168.88.66 -ucat -p123				// 直接进入数据库
mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mycat              |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)

mycat部署读写分离

中间件软件mycat（使用Java编写）部署数据库读写分离服务：
0.启动mycat
   + mycat软件一定要连接某个数据库才能启服务，因此先连接自身数据库启服务，再连接主从服务器部署读写分离。
   + 连接mycat服务：`mysql -h中间件服务器IP地址 -P8066 -u自定义mycat服务用户名 -p密码` 
1.mycat服务添加数据库服务的数据源："注意要连接mycat服务进行配置"，每个数据库服务添加为一个数据源
   + /*+ mycat:createdatasource{
     "name":"自定义数据源名",
     "url":"jdbc:mysql://数据库服务器IP地址:3306",
     "user":"数据库用户",
     "password":"用户密码"
     }*/;
     + 该自定义数据源名只对mycat服务自身有效
     + 注意主从数据库服务要创建该数据库用户并赋权
   + 查看数据源信息：
     + 库命令：`/*+ mycat:showdatasources{}*/ \G` 
     + 文件：`less mycat目录/conf/datasources/自定义数据源名.datasource.json` 
3.mycat服务创建集群："注意要连接mycat服务进行配置"
   + /*! mycat:createcluster{
     "name":"自定义集群名",
     "masters":["主服务器的自定义数据源名m","主服务器的自定义数据源名n"],
     "replicas":["从服务器的自定义数据源名x","从服务器的自定义数据源名y"]
     }*/;
	 + 这里的数据源名是createdatasource中的name
   + 查看集群信息：
     + 库命令：`/*+ mycat:showclusters{}*/ \G` 
     + 文件：`less mycat目录/conf/clusters/自定义集群名.cluster.json` 
4.mycat服务创建库并连接集群
   + `vim conf/schemas/库名.schema.json`，增加`"targetName":"自定义集群名",`
     + 注意："只有连接了集群后，对该库的操作才是对集群的库的操作，否则是对mycat服务器本身的库操作"。
5.mycat服务配置数据源，分配数据库服务器的服务："注意要连接mycat服务进行配置"
   + `vim mycat目录/conf/datasources/自定义数据源名.datasource.json`，修改`"instanceType":"提供服务",` 
     + 提供服务有：读写服务`READ_WRITE`、读服务`READ`、写服务`WRITE` 
     + 主数据库服务器提供写服务（主不能同步从，不能让从提供写服务），从数据库服务器提供读服务（减少主的负担）。
6.mycat服务修改读策略（修改集群处理读访问的分配方式）：
   + `vim mycat安装目录/conf/clusters/自定义集群名.cluster.json`，修改`"readBalanceType":"BALANCE_ALL_READ",` 
     + `BALANCE_ALL`（获取集群中所有数据源）、`BANLANCE_ALL_READ`（获取集群中所有读服务数据源）、`BALANCE_READ_WRITE`（获取集群中所有读写服务数据源，但读服务数据源优先）、`BALANCE_NONE`（获取集群中写服务数据源）

7.重启mycat服务


# 中间件服务器部署读写分离
中间件mycat服务器（192.168.88.66）：
1.mycat服务添加数据库服务的数据源。注意：必须是通过mycat服务进入数据库部署
[root@mycat mycat]# mysql -h127.0.0.1 -P8066 -umycat -p123456
mysql> /*+ mycat:createdatasource{"name":"mysql50","url":"jdbc:mysql://192.168.88.50:3306","user":"cat","password":"123"}*/;
mysql> /*+ mycat:createdatasource{"name":"mysql51","url":"jdbc:mysql://192.168.88.51:3306","user":"cat","password":"123"}*/;
mysql> /*+ mycat:showdatasources{}*/ \G			// 库命令方式查看
	……省略一万字
	3 rows in set (0.01 sec)
[root@mycat mycat]# ls conf/datasources/		// 文件方式查看
	mysql50.datasource.json  mysql51.datasource.json  prototypeDs.datasource.json

2.mycat服务创建集群。注意：必须是通过mycat服务进入数据库部署
[root@mycat mycat]# mysql -h127.0.0.1 -P8066 -umycat -p123456
mysql> /*! mycat:createcluster{"name":"clus0","masters":["mysql50"],"replicas":["mysql51"]}*/;
mysql> /*+ mycat:showclusters{}*/ \G			// 库命令方式查看
	……省略一万字
	2 rows in set (0.01 sec)
[root@mycat mycat]# ls conf/clusters/			// 文件方式查看
	clus0.cluster.json  prototype.cluster.json

4.mycat服务创建库并连接集群
[root@mycat mycat]# mysql -h127.0.0.1 -P8066 -umycat -p123456
mysql> create database testcat;
[root@mycat mycat]# vim conf/schemas/testcat.schema.json 
      6         "schemaName":"testcat",
      7         "targetName":"clus0",						// 增加该行。指定库testcat使用该集群的数据库服务器
      8         "shardingTables":{},

5.mycat服务配置数据源，分配数据库服务器的服务
[root@mycat mycat]# vim +6 conf/datasources/mysql50.datasource.json 
	  6         "instanceType":"WRITE",						// 修改该行。READ_WRITE修改为WRITE。主服务器仅提供写访问
[root@mycat mycat]# vim +6 conf/datasources/mysql51.datasource.json 
	  6         "instanceType":"READ", 						// 修改该行。READ_WRITE修改为READ。从服务器仅提供读访问

6.mycat服务修改读策略（修改集群处理读访问的分配方式）
[root@mycat mycat]# vim +13 conf/clusters/clus0.cluster.json 
	 13         "readBalanceType":"BALANCE_ALL_READ",		// 修改该行。让读访问分配给读服务的数据库服务器（"instanceType":"READ"）

7.重启服务（使conf下所有配置生效）。注意主从数据库服务器要创建数据库用户cat并赋权
[root@mycat mycat]# bin/mycat restart

此时，客户端可以使用mycat服务：`mysql -h192.168.88.66 -P8066 -umycat -p123456`来访问主从数据库服务器的testcat库
1.访问的是集群服务器的数据库testcat库，而不是mycat服务器本身的数据库testcat库
	验证：客户端使用mycat服务，进入数据库对testcat库进行增改删操作，发现主从服务器的testcat库有修改，而mycat服务器的testcat库无修改。
2.写访问都是对主服务器的数据库操作，读访问都是对从服务器的数据库操作
	验证：在从服务器testcat库增加一条记录，客户端使用mycat服务可以查看到该条记录，证明是读取从服务器数据库。
		客户端使用mycat服务增加一条记录，主从服务器testcat库均可以查看到该条记录，证明是写入主服务器数据库。
3.注意：通过mycat服务仅可操作主从数据库服务器的testcat库，其它库由于没有连接集群，所以是对mycat服务器本身的数据库操作

存储引擎

MySQL支持多种存储引擎，每种存储引擎都具有不同的特性、优势和适用场景。以下是MySQL中常见的存储引擎：
    InnoDB: InnoDB是MySQL 8的默认存储引擎，它提供了'事务处理'和行级锁定的支持，适用于大多数应用程序，尤其是需要高并发和高可靠性的事务型应用。
    MyISAM: MyISAM是MySQL原始的存储引擎，它不支持事务处理和行级锁定，但适合用于'读密集'的应用，例如数据仓库、日志系统等。
    MEMORY: MEMORY存储引擎将表中的数据存储在内存中，因此读写速度非常快，但数据会在数据库重启时丢失。适用于临时数据存储和高性能的缓存。
    NDB Cluster: NDB Cluster是一个高可用和高性能的集群存储引擎，适用于支持分布式数据库系统和实时应用。
    ARCHIVE: ARCHIVE存储引擎用于存储大量归档数据，对数据的写入很慢，但占用的磁盘空间很小。
    CSV: CSV存储引擎将数据存储在逗号分隔值（CSV）格式文件中，适用于导入和导出数据。
除了上述存储引擎，还有其他一些存储引擎，如Federated、Blackhole等。选择合适的存储引擎取决于应用程序的需求，例如性能要求、数据完整性要求、事务处理需求等。在设计数据库时，可以根据具体情况选择最适合的存储引擎来提高数据库的效率和性能。

事务处理是指将一系列数据库操作（包括增加、修改、删除数据等）视为一个不可分割的工作单元，要么全部成功完成，要么全部失败回滚的过程。在数据库中，事务通常应满足四个原则（ACID）：
    原子性（Atomicity）：事务中的所有操作要么全部执行成功，要么全部失败回滚，不允许部分操作成功部分失败。
    一致性（Consistency）：事务执行前后，数据库的完整性约束不会发生破坏，数据库状态从一个一致状态转换到另一个一致状态。
    隔离性（Isolation）：多个事务并发执行时，每个事务的操作都应该与其他事务的操作相互隔离，防止相互干扰，确保数据的一致性。
    持久性（Durability）：一旦事务提交成功，其对数据库的修改应该持久保存，即使系统出现故障也不会丢失。
通过事务处理，可以确保数据库操作的一致性和可靠性，防止数据异常和不完整性。在应用程序中，通常会通过事务来确保多个数据库操作的原子性，保证数据的完整性和稳定性。

binlog

不同格式的binlog日志在 MySQL数据库中都有各自的优缺点，主要取决于数据库的使用场景和需求。
    Row 格式（默认）： 
        优点：
            更为精确：记录每一行数据的变更情况，可以准确地表达数据变更。
            安全性高：对于主从数据一致性较为重要的情况，使用 Row 格式能够确保数据的同步性。 
        缺点：
            产生较大的 binlog 日志量：记录每一行数据的变更情况可能会导致 binlog 日志过大，增加存储和传输成本。
    Statement 格式： 
        优点：
            效率高：由于直接记录 SQL 语句，所以在执行复杂 SQL 语句时，可以减少记录数据行的开销。 
        缺点：
            可能出现不确定性：某些 SQL 语句可能会导致主从数据不一致问题，例如使用了不确定函数。
            可能会影响备份和恢复：如果在备份和恢复时数据库中大量使用了存储函数或触发器，可能会出现问题。

    Mixed 格式： 
        优点：
            综合了 Statement 和 Row 格式的优点：既可以节省存储空间和传输成本，又可以保证数据的准确性。
            高灵活性：可以根据具体的操作来选择使用不同的格式记录。 
        缺点：
            配置相对较复杂：需要根据具体的需求来进行配置，可能增加了一定的管理成本。
在选择 binlog 格式时，需要根据数据库的使用情况和需求来进行权衡，以达到最优的性能和安全性。通常来说，如果对数据的准确性要求比较高，可以选择 Row 格式；如果对性能要求比较高，可以考虑使用 Statement 格式；如果需要兼顾性能和准确性，可以尝试使用 Mixed 格式。

可以使用以下命令查看当前 binlog 格式：SHOW VARIABLES LIKE 'binlog_format'
要配置 MySQL 的 binlog 格式，可以通过修改 MySQL 配置文件中的参数来实现。下面是配置 binlog 格式的步骤：
1. 打开 MySQL 配置文件 my.cnf（通常位于 /etc/mysql/my.cnf 或 /etc/my.cnf）。
2. 在配置文件中找到 [mysqld] 区域，这是用来配置 MySQL 服务器参数的区域。添加以下参数来配置不同的 binlog 格式：
    配置为 ROW 格式：binlog_format = ROW
    配置为 STATEMENT 格式：binlog_format = STATEMENT
    配置为 MIXED 格式：binlog_format = MIXED
保存配置文件并重新启动 MySQL 服务，使配置生效：sudo systemctl restart mysql

通过以上步骤就可以配置 MySQL 的 binlog 格式。请注意，更改 binlog 格式可能会影响 MySQL 的性能和复制过程，因此需要在实际生产环境中慎重考虑和测试。如果有其他特定需求或情况，可以根据具体情况进行相应的配置调整。

innobackupex

Innobackupex 是 Percona 提供的用于备份 MySQL 数据库的工具，主要用于备份 InnoDB 存储引擎的数据。Percona XtraBackup 是基于 Innobackupex 的，Innobackupex 同样是热备不锁表，但MySQL 8 不再支持 Innobackupex 这个工具。

完全备份
	innobackupex -u用户名 -p密码 备份目录路径 --no-timestamp	# 注意备份目录无需事先创建，--no-timestamp表示无需根据时间戳创建子目录
完全恢复
    innobackupex --apply-log 备份目录路径		# 准备恢复数据
    systemctl stop mysqld.service
    rm -rf /var/lib/mysql/*
    innobackupex --copy-back 备份目录路径		# 恢复数据
    chown -R mysql:mysql /var/lib/mysql
    systemctl start mysqld.service

恢复单张表的数据（该表数据被清空但表本身不能被删除，即保留表字段：delete * from 库名.表名）
	1.删除表空间（/var/lib/mysql/库名/表名.ibd，不能直接删除，使用SQL语句删除）
		alter table 库名.表名 discard tablespace;
	2.导出表信息（将备份目录下的表信息导出）
		innobackupex --apply-log --export 备份目录路径	# 生成`表名.cfg`和`表名.exp`文件
	3.拷贝表信息文件并修改属主属组
		cp -a 备份目录路径/库名/表名.{cfg,exp,ibd}  /var/lib/mysql/库名
		chown mysql:mysql /var/lib/mysql/库名/表名.*
	4.导入表空间
		alter table 库名.表名 import tablespace;
	5.清除表信息文件
		rm -f /var/lib/mysql/库名/表名.{cfg,exp}

MySQL多实例

MySQL多实例
	1. 安装依赖libaio，创建用户mysql
	2. 解压mysql源码包
	3. 删除配置文件/etc/my.cnf
	4. 创建配置文件/etc/my.cnf，编写多实例
	
	
	5. 启动实例：`mysql安装目录/bin/mysqld_multi start 数字`
	6. 连接该实例的数据库服务后修改root密码
		`mysql -uroot -p'密码' -S socket文件路径` 或者 `mysql -uroot -p'密码' -P端口 -hIP`
    停止实例：`mysql安装目录/bin/mysqld_multi --user=root --password='密码' stop 数字`
		

1. 安装依赖libaio，创建用户mysql
]# yum install -y libaio
]# useradd mysql
2. 解压mysql源码包
]# tar -xf mysql源码包
]# mv 源码包安装目录 /usr/local/mysql
3. 删除配置文件/etc/my.cnf
]# rm -f /etc/my.cnf
4. 创建配置文件，编写多实例
]# cat > /etc/my.cnf << EOF
[mysql_multi]                                    # 管理mysql实例的配置
user=root                                        # 系统root
mysqld=/usr/local/mysql/bin/mysqld_safe 
mysqladmin=/usr/local/mysql/bin/mysqladmin
[mysqld1]                   # mysql实例的名称
datadir=/dir1               # 数据的存储目录
port=3307                   # 1024-65535
pid-file=/dir1/mysqld1.pid  # 存放pid号的文件
log-error=/dir1/mysqld1.err # 日志文件
socket=/dir1/mysqld1.sock   # socket文件，通过127访问本机服务时是通过socket文件连接。文件随服务启止而创建消灭
[mysqld2]
datadir=/dir2
port=3308                   
pid-file=/dir2/mysqld2.pid
log-error=/dir2/mysqld2.err
socket=/dir2/mysqld2.sock 
EOF
5. 启动实例
]# /usr/local/mysql/bin/mysqld_multi start 1         # 1代表第一个实例
	最后一行信息：数据库root会随机生成一个密码，该密码只能用于初始登陆，无其它权限
]# netstat -ntulp | grep 3307
6. 连接该实例的数据库服务后修改root密码
]# mysql -uroot -p'随机密码' -S /dir1/mysqld1.sock
mysql> alter user root@localhost identified by '1234.com';
mysql> exit
]# mysql -uroot -p'1234.com' -S /dir1/mysqld1.sock

7. 启动其他实例并修改root密码
]# /usr/local/mysql/bin/mysqld_multi start 2         # 2代表第二个实例
	最后一行信息：数据库root会随机生成一个密码，该密码只能用于初始登陆，无其它权限
]# netstat -ntulp | grep 3308
]# mysql -uroot -p'随机密码' -S /dir2/mysqld2.sock
mysql> alter user root@localhost identified by '1234.com';
mysql> exit
]# mysql -uroot -p'1234.com' -S /dir2/mysqld2.sock

存储过程procedure

MySQL 中的存储过程和函数在概念和用法上有一些区别，主要包括以下几点：
    返回值类型：
        存储过程（Stored Procedure）：可以返回多个结果集（使用 SELECT 语句），也可以返回 OUT 或 INOUT 参数指定的值。
        函数（Function）：只能返回一个单一的值，不支持返回多个结果集。
    使用方式：
        存储过程：可以被调用并执行，通常用于包含一系列 SQL 语句来完成某种操作或逻辑。存储过程可以包含各种 SQL 语句、控制流结构、条件判断等。
        函数：用于具体的计算或操作，通常用于返回一个单一的值，而不包含过于复杂的逻辑。
    事务支持：
        存储过程：可以包含事务控制语句（BEGIN、COMMIT、ROLLBACK），从而支持事务控制和事务管理。
        函数：通常不支持事务控制语句，因为函数在执行过程中不能影响数据库事务状态。
    调用方式：
        存储过程：使用 'CALL语句调用存储过程'，并'可以传递参数'。
        函数：使用 'SELECT语句调用函数'，并获取函数返回的值。
总的来说，存储过程更适合用于一系列操作的执行，尤其是需要包含复杂业务逻辑、多条SQL语句执行等情况；而函数更适合用于计算或操作，并返回单一的结果值。


创建存储过程：`create procedure 存储过程名(形参列表) BEGIN SQL语句 END 终止符`
调用存储过程：`call 存储过程名(实参列表);`
删除存储过程: `drop procedure 存储过程名;`
如果创建存储过程的SQL语句中使用了分号，可以先使用`delimiter 终止符`来定义终止符

变量的使用

数据库变量：
	系统变量：'global'全局变量（值对所有会话一致），'session'会话变量（值根据不同会话而可能不同，只对当前会话有效）
	自定义变量：'@'用户变量（只对当前连接有效），局部变量（只在存储过程执行时有效，begin和end之间使用'declare'定义的变量）
# 全局变量和会话变量
mysql> show global或session variables;
mysql> show global或session variables like '%字符串%';		# _单个字符，%任意个字符
mysql> select @@变量名;									# 查看该变量的值
mysql> set global或session 变量名 = 变量值;				  # 修改全局变量或会话变量的值

# 定义用户变量
mysql> set @age=19,@name='字符串';
mysql> select @age,@name;
mysql> select count(name)，min(uid) into @x,@min from test.user;
mysql> select @x,@min;

# 定义局部变量
# `DELIMITER //`来指定新的结束符为//，`DELIMITER ;`将结束符重新设置为默认的分号。
# 使用 CREATE PROCEDURE 在test库中定义了一个名为 my_procedure 的存储过程
mysql> DELIMITER //
mysql> CREATE PROCEDURE test.my_procedure()
	-> BEGIN
	->  	DECLARE my_variable INT default 9;
	-> 		SET my_variable = 10;
	-> 		SELECT my_variable;
	-> END//
mysql > DELIMITER ;
mysql > CALL my_procedure();

参数的使用

存储过程的参数类型
    in：in类型的参数负责把数据传递给存储过程
    out：接收或保存存储过程的处理结果
    inout：既in又out

create procedure 名(参数1,参数n)
参数定义的语法格式：`参数类型 变量名 数据类型`

# in类型
# 根据部门编号统计部门的总人数
mysql> select dept_id,count(name) from test.employees where dept_id=3 group by dept_id;
mysql> delimter //
mysql> create procedure test.pnum(in dept_num int)
	-> begin
	->   select dept_id,count(name) from test.employees where dept_id=dept_num group by dept_id;
	-> end //
mysql> delimter ;
mysql> call test.pnum(5);

# out类型
# 根据员工姓名获取员工的邮箱
mysql> select email from test.employees where name='tom';
mysql> delimter //
mysql> create procedure test.pnum(in emp_name varchar(10),out emp_email varchar(50))
	-> begin
	->   select email into emp_email from test.employees where name=emp_name;
	-> end //
mysql> delimter ;
mysql> call test.pmail('tom',@x);
mysql> select @x;

# inout类型
# 注意，inout类型的实参必须是一个变量，否则无法接收存储过程的结果因而报错
mysql> delimter //
mysql> create procedure test.add(inout i int)
	-> begin
	->   set i = i + 100;
	-> end //
mysql> delimter ;
mysql> set @x=8
mysql> call test.add(@x);
mysql> select @x;

流程控制

流程控制：
    1. if判断结构
        if 判断条件1 then 代码;
        elseif 判断条件2 then 代码;
        elseif 判断条件n then 代码;
        else 代码;
        end if;
    2. case分支结构
    	case 变量
    	when 值1 then 代码;
    	when 值2 then 代码;
    	when 值n then 代码;
        else 代码;
        end case;
    3. while循环
    	while 判断条件 do 代码;
    	end while;
    4. repeat循环（代码至少执行一次）
    	repeat 代码;
    	until 判断条件
    	end repeat;
    5. loop死循环
    	loop 代码;
    	end loop;
    6. 循环控制命令
    6.1.'leave'结束循环，相当于shell中的'break'，以loop循环举例
            名称: LOOP
                代码;
                IF 条件判断 THEN
                    LEAVE 名称;
                END IF;
            END LOOP 名称;
    6.2.'iterate'跳过本次循环，相当于shell中的'continue'，以while循环举例，跳过3
            BEGIN
            	DECLARE j int default 1;
                名称:while j <= i do
					if j = 3 then
						set j = j + 1;
						iterate 名称;
					end if;
					select j;
					set j = j + 1;
				end while 名称;
            END;

# if判断结构
# 根据部门编号输出部门类型
mysql> select dept_name from test.departments where dept_id=2;
mysql> delimiter //
mysql> create procedure test.detype(in id int,out dept_type varchar(5))
	-> begin
	->     declare type varchar(5);
	->     select dept_name into type from test.departments where dept_id=id;
	->     if type='运维部' then set dept_type='技术部门';
	->     elseif type='开发部' then set dept_type='技术部门';
	->     elseif type='测试部' then set dept_type='技术部门';
	->     else
	->         set dept_type='非技术部门';
	->     end if;
	-> end
mysql> delimiter ;
mysql> call test.detype(1,@t);
mysql> select @t;

# case分支结构
mysql> delimiter //
mysql> create procedure test.detype(in id int,out dept_type varchar(5))
	-> begin
	->     declare type varchar(5);
	->     select dept_name into type from test.departments where dept_id=id;
	->     case type
	->     when '运维部' then set dept_type='技术部门';
	->     when '开发部' then set dept_type='技术部门';
	->     when '测试部' then set dept_type='技术部门';
	->     else
	->         set dept_type='非技术部门';
	->     end case;
	-> end
mysql> delimiter ;
mysql> call test.detype(1,@t);
mysql> select @t;

# while循环结构
# 往表中增加指定行数
mysql> delimiter //
mysql> create procedure test.whil(in i int)
	-> begin
	-> 	declare j int default 1;
	-> 	while j < i do
	-> 		insert into test.department(dept_name) values('hr');
	-> 		set j=j+1;
	-> 	end while;
	-> end
mysql> delimiter ;
mysql> call test.whil(1);
mysql> select count(dept_name) from test.department where dept_name='hr';	# 0条
mysql> call test.whil(5);
mysql> select count(dept_name) from test.department where dept_name='hr';	# 4条

# repeat循环结构
# 往表中增加指定行数
mysql> delimiter //
mysql> create procedure test.repea(in i int)
	-> begin
	-> 	declare j int default 1;
	-> 	repeat
	-> 		insert into test.department(dept_name) values('hr');
	-> 		set j=j+1;
	-> 		util j >= i
	-> 	end repeat;
	-> end
mysql> delimiter ;
mysql> call test.repea(1);
mysql> select count(dept_name) from test.department where dept_name='hr';	# 1条
mysql> call test.repea(5);
mysql> select count(dept_name) from test.department where dept_name='hr';	# 4条

# loop死循环
mysql> delimiter //
mysql> create procedure test.loo()
	-> begin
	-> 	declare i int default 1;
	-> 	loop
	-> 		select sleep(1),i;
	-> 	end loop;
	-> end
mysql> delimiter ;
mysql> call test.loo();
注意，此时Ctrl+C只能杀死一次命令，无法终止死循环，需另外建立会话连接数据库，查询loop进程id号并杀死它
新mysql> show processlist;
新mysql> kill loop循环的id号;

MySQL优化

影响处理速度的因素：
	硬件优化：硬件配置低，网络带宽窄，存储架构不合理
	软件优化：复杂的查询语句，服务运行参数设置不合理

加快查询：
    1.慢查询日志找出复杂的查询语句，优化查询语句
    2.查询缓存
    3.索引缓存（myisam存储引擎）
    4.设置缓存表
    5.设置并发连接数
    6.设置连接超时数，避免恶意连接
    7.设置缓存保存的最大线程数，加快响应速度

慢查询日志

针对复杂的查询语句，可以启用慢查询日志排查查询命令，再进行优化

慢查询日志作用：保存耗时较长的查询命令
    slow-query-log：启用慢查询日志
    long-query-time：该时间内没有返回查询结果则将查询命令保存到慢查询日志

]# vim /etc/my.cnf
[mysqld]
slow-query-log
long-query-time 0.3		# 0.3s内没有返回查询结果则将查询命令保存到慢查询日志
]# systemctl restart mysqld.service
]# ls /var/lib/mysql/主机名-slow.log		# 慢查询日志
]# mysqldumpslow 慢查询日志路径 > a.txt	# 统计日志信息
a.txt中记录有超时的查询命令，可以进行针对性的优化

查询缓存 query_cache

mysql> show variables like '%query_cache%';
    +------------------------------+----------+
    | Variable_name                | Value    |
    +------------------------------+----------+
	| have_query_cache             | YES      |   # 是否开启查询缓存功能
    | query_cache_limit            | 1048576  |
    | query_cache_min_res_unit     | 4096     |
    | query_cache_size             | 1048576  |   # 查询缓存的大小（占用物理内存），单位字节，这里是1M
    | query_cache_wlock_invalidate | OFF      |
    | query_cache_type             | DEMAND   |   # 是否允许将查询数据放入查询缓存，OFF禁用，ON启用，DEMAND如果查询包含SQL_CACHE命令则缓存、否则不缓存
    +------------------------------+----------+
 
]# vim /etc/my.cnf
[mysqld]
have_query_cache yes			# 开启查询缓存功能
query_cache_size 10485760       # 10M
query_cache_type on				# 允许将查询数据放入查询缓存
]# systemctl restart mysqld.service
mysql> show status like '%qcache%'
    | Variable_name         | Value   |
    |-----------------------|---------|
    | Qcache_free_blocks    | 10      |
    | Qcache_free_memory    | 1024    |
    | Qcache_hits           | 500     |		# 在缓存里查找结果的命中次数，命中意思是找到
    | Qcache_inserts        | 100     |     # 在硬盘里查找结果的命中次数
    | Qcache_lowmem_prunes   | 50     |
    | Qcache_not_cached      | 200     |
如果第一次查找，则Qcache_inserts+1，之后每次查找，Qcache_hits+1

mysql> show status like 'com_select';
    | Variable_name | Value |
    |---------------|-------|
    | Com_select    | 1000  |		# 启服务起执行SELECT查询的次数，无论是否命中

查询缓存命中率=（Qcache_hits/Com_select）*100%，较高的查询缓存命中率可以减少数据库服务器的负载，提高查询性能
如果物理内存有限，则不写入查询缓存，而另外部署内存数据库

索引缓存 key_buffer_size

验证查询时有无使用索引：`expalin select语句` ，可以看到查找时查找了多少行信息

# 普通索引 index()
新表创建索引：`create table 库名.表名(表字段1 数据类型,表字段2 数据类型,index(表字段1));`：对表字段1创建索引。
	索引标志MUL：`desc 库名.表名;` 
	新表创建索引的索引名同表字段。
旧表添加索引：`create index 索引名 on 库名.表名(表字段);`。一般索引名同表字段名。
删除索引：`drop index 索引名 on 库名.表名;` 
查看所有索引的详细信息：`show index from 库名.表名 \G` 

读操作比较多的表，建议使用myisam存储引擎，每个表会存储为三个表文件：
	表名.frm：存储表字段，desc 表名
	表名.MYI：存储索引，show index from 表名
	表名.MYD：存储表数据，select * from 表名
索引缓存：划分物理内存空间，存放索引信息，即存放`表名.myi`文件中的数据

mysql> show variables like '%key_buffer%’;
    | Variable_name   | Value  |
    |---------------  |------- |
    | key_buffer_size | 8388608|	# 索引缓存空间，8M

]# vim /etc/my.cnf
[mysqld]
key_buffer_size=83886080	# 80M
]# systemctl restart mysqld.service
mysql> alter table 库名.表名 engine=myisam;

mysql> show variables like '%key_read%’;
    | Variable_name   | Value  |
    |---------------  |------- |
    | Key_read_requests | 6   |	   # 索引请求总数
    | Key_read        | 3     |	   # 磁盘IO数（第一次访问磁盘的MYI文件，数值+1，后续访问内存中的MYI文件，数值不变）
第一次查询，Key_read+1，Key_read_requests增加若干（因为查找可能涉及其他表的索引），再次查询，Key_read不变，Key_read_requests不一定

Key_read只记录查找的目标表中的索引，只记录磁盘的次数。Key_read_requests记录查找时所有涉及的索引，包括其他表，包括磁盘和内存的次数。
Key_read/Key_read_requests，越低越好，表明索引缓存的作用越大（空间越大作用越大，但过大也占用高内存）

设置缓存表 table_open_cache

每次查询表数据：打开表——》查找数据——》关闭表
如果查询表之后不立即关闭，下次查询可以减少打开操作提高读取效率，因此可以设置缓存表的数量（最多同时打开多少张表不关）

mysql> show variables like 'table_open_cache’;
    | Variable_name   | Value  |
    |---------------  |------- |
    | table_open_cache | 2000   |	# 可以同时打开多少张表不关

mysql> show variables like 'open%tables’;
    | Variable_name   | Value  |
    |---------------  |------- |
    | open_tables     | 63  |	   # 当前内存同时打开多少张表
    | opend_tables    | 70   |	   # 启服务起打开过多少张表

open_tables < table_open_cache 并且opend_tables不大

设置并发连接数 max_connections


mysql> show variables like 'max_connections’;
    | Variable_name   | Value  |
    |---------------  |------- |
    | max_connections | 151    |	# 允许的最大并发连接数

mysql> show variables like 'max_used_connections’;
    | Variable_name           | Value  |
    |---------------          |------- |
    | Max_used_connections     | 63  |	   # 当前最大的连接数

Max_used_connections / max_connections * 100% < 85%

连接超时 wait_timeout

配置MySQL的wait_timeout参数来自动关闭长时间不活动的连接，可以按照以下步骤进行操作：
    登录MySQL数据库管理系统，可以使用命令行登录或者其他可视化工具如MySQL Workbench。
    执行以下SQL命令来查看当前的wait_timeout参数设置：
        SHOW VARIABLES LIKE 'wait_timeout';
    这样可以查看当前的wait_timeout参数的值。
    如果需要修改wait_timeout参数的值，可以通过以下命令进行设置，其中将等待时间设置为300秒（5分钟）：
        SET GLOBAL wait_timeout=300;
    或者修改my.cnf配置文件（Windows系统可能是my.ini）中的配置，在文件中加入以下配置：
        wait_timeout=300
    然后重启MySQL数据库服务，以使新的wait_timeout参数生效。
    这样设置之后，如果某个连接在指定的时间内没有活动，MySQL会自动关闭这个连接，从而释放资源。这有助于避免因为长时间占用连接资源而导致数据库性能下降的情况发生。

在MySQL中，有几个flush命令可以用于不同的目的：
    FLUSH PRIVILEGES;：重新加载授权表，更新授权数据，使最新的更改生效，比如创建新用户或者修改用户权限。
    FLUSH TABLES;：关闭所有打开的表并将对表所做的所有更改写入磁盘，刷新所有表的缓存。这也会释放表缓存，强制所有表中的数据从内存写入磁盘。
    FLUSH TABLES WITH READ LOCK;：获取一个全局只读锁，以确保其他会话无法写入表，适用于备份或复制操作。
    FLUSH LOGS;：关闭并重新打开所有日志文件，即使日志文件循环也会触发。
    FLUSH STATUS;：重置所有服务器状态变量，使其返回初始值。
    FLUSH QUERY CACHE;：清空查询缓存，使查询缓存无效。
    这些flush命令的作用是刷新并使得相应的配置或者状态更改生效。

在MySQL中，可以使用以下几种方法来查询连接数等信息：
    查询当前连接数：可以使用以下SQL语句查询当前连接数。
    SHOW STATUS LIKE 'Threads_connected';
    查询总连接数：可以使用以下SQL语句查询MySQL服务器开始运行以来的总连接次数。
    SHOW STATUS LIKE 'Connections';
    查询当前活动连接信息：可以使用以下SQL语句查询当前活动连接的详细信息。
    SHOW PROCESSLIST;
    查询当前等待连接信息：可以使用以下SQL语句查询当前正在等待连接的详细信息。
    SHOW STATUS LIKE 'Threads_connected';

缓存重复线程 thread_cache_size

一般连接断开后，会杀死对应的线程，可以不销毁处理完访问的线程，让其继续在内存中运行，等待客户端连接，加快响应的时间
每一个连接会占用一个线程，最大并发连接max_connections，缓存中最大线程数thread_cache_size

mysql> show variables like 'thread_cache_size’;
    | Variable_name   | Value  |
    |---------------  |------- |
    | thread_cache_size | 9    |	# 缓存中允许保留的最大线程数

mysql> show variables like 'threads_%’;
    | Variable_name           | Value  |
    |---------------          |------- |
    | Threads_cached     		| 2  |	   # 缓存中保存的线程数
    | Threads_connected     	| 1 |	   # 当前正连接的线程数
    | Threads_created   		| 3  |	   # 创建过的线程数
    | Threads_running    		| 1  |	   # 活跃的线程数

当连接断开，Threads_cached的最大值为thread_cache_size，Threads_connected <= thread_cache_size时响应速度最快