MySQL进阶

一、MySQL的企业版本选择

1.MySQL 同源分支

Oracle
Percona
MariaDB

2.MySQL 版本选择

5.6 ：5.6.40 + , GA 6-12之间，202010
5.7 ：5.7.20+
8.0 : 8.0.18+

MySQL官网下载：https://downloads.mysql.com/archives/community/

二、MySQL 二进制安装

1. 创建相关目录

   [root@node01 ~]# mkdir -p /data/app
   [root@node01 ~]# mkdir -p /data/3306/data

2. 创建用户

   [root@node01 data]# useradd mysql

3. 关闭防火墙

   [root@node01 data]# iptables - L #显示出当前使用的 iptables 规则
   [root@node01 data]# systemctl stop firewalld #关闭防火墙
   [root@node01 data]# systemctl disable firewalld #设置开机禁用防火墙
   [root@node01 data]# getenforce #查看当前SELinux的运行模式
   Disabled
   [root@node01 data]# setenforce 0 #设置SELinux 成为permissive模式 临时关闭selinux的
   setenforce: SELinux is disabled
   [root@node01 data]# cat /etc/selinux/config
   SELINUX=disabled

• Enforcing：强制模式。代表SELinux在运行中，且已经开始限制domain/type之间的验证关系
• Permissive：宽容模式。代表SELinux在运行中，不过不会限制domain/type之间的验证关系，即使验证不正确，进程仍可以对文件进行操作。不过如果验证不正确会发出警告
• Disabled：关闭模式。SELinux并没有实际运行

删除无关的软件包

[root@node01 data]# rpm -qa|grep mariadb
[root@node01 data]# yum remove mariadb-*
[root@node01 data]# rm -rf /etc/my.cnf* #删除残留的配置文件

4.上传软件至/data/app目录下，并解压，做软连接

[root@node01 ~]# cd /data/app/

[root@node01 ~]# rz -E

没有rz命令使用yum install lrzsz安装

tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz

ln -s mysql-5.7.30-linux-glibc2.12-x86_64 mysql

cd bin/

chown -R mysql.mysql /data/*

5. 初始化系统库和表

   vim /etc/profile
   export PATH=/data/app/mysql/bin:$PATH #添加mysql环境

source /etc/profile

mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data

--initialize-insecure #初始化数据库指令

--user=mysql #指定MySQL用户

--basedir=/data/app/mysql #指定软件目录

--datadir=/data/3306/data #指定数据存放目录

[root@node01 bin]# cd /data/3306/data/
[root@node01 data]# ll

++++++

初始化: 在5.6和5.7 的区别
5.6 ：
/data/app/mysql/scripts/mysql_install_db .....
5.7 :
不安全：
mysqld --initialize-insecure ...
初始化完成之后：没有密码即可登录
安全：
mysqld --initialize ...
初始化完成之后：自动生成一个12位，4种密码复杂度的临时密码（root@'localhost'）。不能直接使用，第一次登陆时修改掉密码。

++++++

6. 准备启动脚本

cp /data/app/mysql/support-files/mysql.server /etc/init.d/mysqld

7. 准备配置文件

   [root@node01 data]# vim /etc/my.cnf
   [mysqld]
   user=mysql
   basedir=/data/app/mysql
   datadir=/data/3306/data
   socket=/tmp/mysql.sock
   server_id=10
   port=3306
   [mysql]
   socket=/tmp/mysql.sock

8. 启动数据库

   [root@node01 data]# /etc/init.d/mysqld start
   [root@node01 data]# mysql

三、 MySQL 体系结构 及 日常管理

1.MySQL C / S 结构

Client:

Server：

2.MySQL 实例结构

	作用： 提供数据处理功能。
	实例 = mysqld守护进程  +  Master thread+ 干活 threads + 专用内存结构

3.服务端的体系结构

见图

4.用户管理

4.1 作用

登陆数据库
管理数据库对象（库、表）

4.2 定义

用户名@白名单
root@'localhost'
app@'localhost'
app@'%'
app@'10.0.0.%'
app@'10.0.0.5%'
app@'10.%'
app@'10.0.0.0/255.255.254.0'

4.3 用户管理

查询用户

mysql> select user,host ,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

创建用户

mysql> create user xie@'10.0.0.%';
mysql> create user xies@'10.0.0.%' identified by '123';

修改用户

mysql> alter user xie@'10.0.0.%' identified by '123';

删除用户

mysql> drop user xie@'10.0.0.%';

++++++++++++++

说明：
在8.0之前，可以通过grant 既可以授权又可以自动创建用户。
grant all on . to admin@'%' identified by '123';
8.0版本之后,必须要先建立用户，再单独授权。而且不支持identified by字句了。

8.0密码加密插件发生了变化，默认是sha2的加密方式，很多应用连接会不支持。如果需要向下兼容需要将密码插件修改为mysql_native_password插件
mysql> create user test@'localhost' identified with mysql_native_password by '123';
mysql> alter user test@'localhost' identified with mysql_native_password by '123';

++++++++++++++

5.权限管理

5.1 作用

约束用户的操作行为。

5.2 权限的定义

ALL ： 拥有所有权限（除了grant option），一般管理员才会有的。
mysql> show privileges;
Alter
Alter routine
Create
Create routine
Create temporary tables
Create view
Create user
Delete
Drop
Event
Execute
File
Grant option ** *******
Index
Insert
Lock tables
Process
Proxy
References
Reload
Replication client
Replication slave
Select
Show databases
Show view
Shutdown
Super
Trigger
Create tablespace
Update
Usage

5.3 权限管理

授权

grant 权限 on 权限作用范围 to 用户 identified by 密码;

权限： ALL 、 单一权限等。
作用范围：
*.*
test.*
test.t1

mysql> grant all on . to admin@'%' identified by '123';
mysql> grant select,update,delete,insert on oldboy.* to oldboy@'10.0.0.%' identified by '123';

回收

错误方法：
grant select,update,insert on oldboy.* to oldboy@'10.0.0.%' identified by '123';
mysql> show grants for oldboy@'10.0.0.%';
正确方法：
mysql> revoke delete on oldboy.* from 'oldboy'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for oldboy@'10.0.0.%';

5.4 授权表

mysql.user #用户和权限
mysql.db #用户被赋予哪些库权限
mysql.tables_priv #用户被赋予哪些表权限

6.启动关闭

启动：

mysqld &

关闭：

方式一：

mysql -uroot -p'123'

shutdown

方式二：

mysql -uroot -p'123' shutdown

centos6与centos7启动关闭 MySQL数据库区别

centos6中使用sys-v方式

service mysqld start

service mysqld stop

service mysqld restart

centos7中使用systemd方式

systemctl start mysqld

systemctl stop mysqld

systemctl restart mysqld

7.配置文件应用

7.1 配置文件默认读取顺序

[root@node01 ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf ---》 /etc/mysql/my.cnf ---》 /usr/local/mysql/etc/my.cnf ---》 ~/.my.cnf

说明：

1. 如果自定义配置文件位置（例如： /opt/aa.txt），需要以下命令：
   mysqld --defaults-file=/opt/aa.txt &
   mysqld_safe --defaults-file=/opt/aa.txt &

2. 如果是多配置文件，重复选项，以最后一个配置信息为准。

7.2 配置文件结构

[标签] ：
服务器标签 ： 影响到了数据库启动和数据初始化
[mysqld] 、[mysqld_safe] 、[server]
客户端标签 ：影响客户端的连接
[mysql] 、 [mysqldump] 、 [client]

配置

[root@node01 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=10
port=3306
[mysql]
socket=/tmp/mysql.sock

8.连接管理

8.1 自带客户端连接

本地Socket

要求： 提前创建好localhost相关用户
[root@node01 ~]# mysql -uroot -p123 -S /tmp/mysql.sock

远程连接

要求： 提前创建好可以远程登录的用户
[root@node01 ~]# mysql -uadmin -p123 -h 10.0.0.21 -P 3306

8.2 开发工具

要求： 提前创建好可以远程登录的用户
sqlyog navicat workbech

8.3 程序连接

pip3 install pymysql
import ....

9.多实例环境

9.1 作用：

1. 测试环境、开发环境
2. 分布式架构

3307、3308、3309

9.2 准备目录

mkdir -p /data/330{7..9}/data

chown -R mysql.mysql /data/*

9.3 配置文件

vim /data/3307/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3307/data
socket=/tmp/mysql7.sock
server_id=17
port=3307

vim /data/3308/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3308/data
socket=/tmp/mysql8.sock
server_id=18
port=3308

vim /data/3309/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3309/data
socket=/tmp/mysql9.sock
server_id=19
port=3309

9.4 初始化数据

[root@node01 data]# mv /etc/my.cnf /tmp/mysql.cnf

mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3307/data

mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3308/data

mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3309/data

9.5 启动数据库

[root@node01 data]# mysqld_safe --defaults-file=/data/3307/my.cnf &
[root@node01 data]# mysqld_safe --defaults-file=/data/3308/my.cnf &
[root@node01 data]# mysqld_safe --defaults-file=/data/3309/my.cnf &

9.6连接并查看

mysql -S /tmp/mysql7.sock

mysql -S /tmp/mysql8.sock

mysql -S /tmp/mysql9.sock

查看端口

select @@port;

四、SQL基础规范

1.SQL 常用种类

DDL 数据定义语言
DCL 数据控制语言
DML 数据操作语言

2.DDL 开发规范

库 ：
create database
alter database
drop database

表 ：
create table
alter table
drop table
truncate table

3.DDL规范：

1. 建库需要显示添加字符集和校对规则 
   2. 库名要和业务有关。
   3. 库名不要大写字母，数字开头。
   4. 普通用户禁用drop操作
   5. 表名：和业务有关、不要大写字母和数字开头、不要太长
   6. 必须显式设定存储引擎。
   7. 字符集一般建议utf8mb4 
   8. 表要有注释 
   9. 必须要有主键列，建议是数字自增列。
   10. 数据类型： 合适的、简短的、足够的。
   11. 尽量每个列非空。
   12. 每个列要加注释。
   13. 表大做alter语句，业务不繁忙期间做。如果必须做，建议pt-osc 。

4.ML规范：

1. 大的insert事务，要拆分为小事务进行。也可以用load data infile方式进行批量录入。
   2. 导入大量数据时，可以将索引先禁用，导入成功后再创建。
   3. update 必须要加where条件。经常做update操作的列，不建索引。更新范围尽量小。
   4. delete 必须要加where条件。有必要的话，可以使用update替代delete。

五、索引及执行计划

1.作用

一本书中的目录。起到优化查询的作用（selectupdatedelete）

2.支持的种类

Btree -》B树索引
Rtree -》R树索引｛空间索引｝
Fulltext -》全文索引
Hash -》hash索引

3.查找算法

遍历
二叉树
红黑树
Balance tree(平衡多叉树)【B树】

B树结构图

B+树结构图

4.MySQL 索引如何应用BTree

4.1 聚簇索引

前提：
如果有主键列（PK），通过主键构建聚簇索引。
没有主键,选择第一个不为空的唯一键UK（unique key）。
都没有，自动生成DB_ROW_ID（6字节）的隐藏列，生成聚簇索引
一张表只有一个聚簇（区,默认是64个连续page，默认1M）索引。
聚簇索引，采用区的结构，组织存储表中的数据行。MySQL采用的是IOT。

构建过程：

1. 表设计时，一般会设置一个ID主键。
2. 所有数据的录入，都是按照ID顺序，有序的在磁盘的连续（同一个区）数据页上有序存储数据行。
3. 聚簇索引的叶子节点就是原表数据。
4. 枝节点，保存了下层叶子节点的ID的范围+指针，生成枝节点。
5. 根节点，保存了下层枝节点的ID的范围+指针，生成根节点

作用： 通过ID作为查询的条件时，会受到聚簇索引优化，理论上只需要最少三个数据页，即可获取数据行。

4.2 辅助索引

构建过程:

1. 叶子节点： 提取辅助索引列值+ID，按照辅助索引列进行排序，存储到有序的page中。
2. 枝节点： 保存了下层叶子节点，辅助索引列值范围+指针。
3. 根节点： 保存了下层枝节点，辅助索引列值范围+指针。

作用：
通过辅助索引列作为条件查询时，首先扫描辅助索引树，获得ID主键值，然后再回到聚簇索引扫描【回表查询】(尽可能覆盖大部分索引减少回表查询次数)，最终获取想要的数据行。

4.3 辅助索引的种类

单列
联合索引：
最左原则。 idx(a,b,c)

1. 建索引的时候，基数大(重复值少)的列作为最左列
 2. 写查询条件时，必须包含索引左列。
    前缀

唯一

4.4 索引树高度影响因素

1. 数据行。
   分区、归档表、分库分表
2. 大字段。
   前缀索引。ES、MongoDB
3. 数据类型选择不合理

4.5 回表？ 影响？ 减少？

扫描完辅助索引，得到ID，回到聚簇索引查询。
IO增多。
建联合索引，查询时覆盖长度越多越好。

提前压测：
mysqlslap --defaults-file=/etc/my.cnf
--concurrency=100 --iterations=1 --create-schema='test'
--query="select * from test.t100w where k2='780P'" engine=innodb
--number-of-queries=2000 -uroot -p123 -verbose

Benchmark
Running for engine rbose
Average number of seconds to run all queries: 756.860 seconds
Minimum number of seconds to run all queries: 756.860 seconds
Maximum number of seconds to run all queries: 756.860 seconds
Number of clients running queries: 100
Average number of queries per client: 20

mysqlslap是一个mysql官方提供的压力测试工具。

mysqlslap是从MySQL的5.1.4版开始就开始官方提供的压力测试工具。通过模拟多个并发客户端并发访问MySQL来执行压力测试，同时提供了较详细的SQL执行数据性能报告，并且能很好的对比多个存储引擎（MyISAM，InnoDB等）在相同环境下的相同并发压力下的性能差别。

常用参数

-auto-generate-sql, -a 自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力。
--auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)。
--auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持。
--number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认1
--number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1
--number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)
--query=name,-q 使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。
--create-schema 代表自定义的测试库名称，测试的schema，MySQL中schema也就是database。
--commint=N 多少条DML后提交一次。
--compress, -C 如果服务器和客户端支持都压缩，则压缩信息传递。
--concurrency=N, -c N 表示并发量，也就是模拟多少个客户端同时执行select。可指定多个值，以逗号或者--delimiter参数指定的值做为分隔符。例如：--concurrency=100,200,500。
--engine=engine_name, -e engine_name 代表要测试的引擎，可以有多个，用分隔符隔开。例如：--engines=myisam,innodb。
--iterations=N, -i N 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次。
--only-print 只打印测试语句而不实际执行。
--detach=N 执行N条语句后断开重连。
--debug-info, -T 打印内存和CPU的相关信息。
–-defaults-file，配置文件存放位置
–-socket，socket文件位置

说明

测试的过程需要生成测试表，插入测试数据，这个mysqlslap可以自动生成，默认生成一个mysqlslap的schema，如果已经存在则先删除。可以用--only-print来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹。

测试实例

# 单线程测试。测试做了什么。
mysqlslap -a -uroot -p123456
 
# 多线程测试。使用–concurrency来模拟并发连接。
mysqlslap -a -c 100 -uroot -p123456
 
# 迭代测试。用于需要多次执行测试得到平均值。
mysqlslap -a -i 10 -uroot -p123456
 
mysqlslap ---auto-generate-sql-add-autoincrement -a -uroot -p123456
mysqlslap -a --auto-generate-sql-load-type=read -uroot -p123456
mysqlslap -a --auto-generate-secondary-indexes=3 -uroot -p123456
mysqlslap -a --auto-generate-sql-write-number=1000 -uroot -p123456
mysqlslap --create-schema world -q "select count(*) from City" -uroot -p123456
mysqlslap -a -e innodb -uroot -p123456
mysqlslap -a --number-of-queries=10 -uroot -p123456
 
# 测试同时不同的存储引擎的性能进行对比：
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -p123456
 
# 执行一次测试，分别50和100个并发，执行1000次总查询：
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -p123456
 
# 50和100个并发分别得到一次测试结果(Benchmark)，并发数越多，执行完所有查询的时间越长。为了准确起见，可以多迭代测试几次:
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -p123456

5.索引管理

5.1 查询索引

mysql> desc t100w;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
| k1 | char(2) | YES | | NULL | |
| k2 | char(4) | YES | | NULL | |
| dt | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+

PK
MUL
UK

mysql> show index from t100w;
Empty set (0.00 sec)

5.2 创建索引

mysql> alter table t100w add index idx_k2(k2);

[root@node01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 0.302 seconds
Minimum number of seconds to run all queries: 0.302 seconds
Maximum number of seconds to run all queries: 0.302 seconds
Number of clients running queries: 100
Average number of queries per client: 20

mysql> alter table t100w add index idx(k1,k2);
mysql> alter table city add index idx_name(name(10));
mysql> alter table t100w add unique index idx(x);

5.3 删除索引

mysql> alter table t100w drop index idx_k2;

6.MySQL 自优化能力

AHI 自适应hash索引 。
索引 的 索引。内存中的结构。
索引热的内存中的索引页。

Change buffer
针对 insert、update、delete语句不立即更新的辅助索引，临时存放至change buffer中。
如果需要change buffer数据，会自动在内存进行merge(合并)。

ICP 索引下推

把过滤请求，在引擎去拿到数据之前，在引擎层过滤数据，过滤出有用的数据，减少IO操作

7.索引应用规范总结

7.1 建立索引的原则（规范）

(1) 必须要有主键,无关列。
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期，建议用pt-osc
(7) 联合索引最左原则

7.2 不走索引的情况（开发规范）

7.2.1 没有查询条件，或者查询条件没有建立索引
select * from t1 ;
select * from t1 where id=1001 or 1=1;

7.2.2 查询结果集是原表中的大部分数据，应该是15-25%以上。

查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。
MySQL的预读功能有关。

可以通过精确查找范围，达到优化的效果。

7.2.3 索引本身失效，统计信息不真实（过旧）

索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
一般是删除重建

现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select? --->索引失效,统计数据不真实

innodb_index_stats
innodb_table_stats

mysql> ANALYZE TABLE world.city;

7.2.4 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
算术运算
函数运算
子查询

7.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

telnum char(11)

select * from t1 where telnum = '110'; #走索引
select * from t1 where telnum = 110; #不走索引

7.2.6 <> ，not in 不走索引（辅助索引）

7.2.7 like "%_" 百分号在最前面不走

8.执行计划获取和分析

8.1 作用

优化器优化之后得出的执行SQL语句的计划。
可以提前评估，业务语句的性能。
问题语句的分析。

8.2 获取

mysql> desc select * from t100w where k2='780P';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 925074 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

8.3 分析

table ： 执行计划涉及到的表
type ：
ALL : 全表扫描
index ：全索引扫描
range ：索引范围扫描 , < > >= <= between and in or like 'adas%'
ref ：辅助索引常量查询
eq_ref ：非驱动表连接条件是主键、唯一键
const(sytstem) ：聚簇索引或唯一索引常量查询

possible_keys ：
可能会用到的索引
key ：最终选的索引
key_len : 联合索引的应用长度
rows : 此次查询需要扫描的行数（估算值）
Extra : 额外的信息

六、存储引擎

1.介绍

相当于MySQL中内置的文件系统 ，表级别、插件式的功能。

2.种类

InnoDB
CSV
MyISAM
BLACKHOLE
PERFORMANCE_SCHEMA
MRG_MYISAM
ARCHIVE
MEMORY
FEDERATED

3.InnoDB的核心特性

多缓冲区
事务
MVCC
行锁
热备
自动故障恢复

聚簇索引
change buffer
AHI

4.InnoDB的体系结构

4.1 On-disk 结构

4.1.1 宏观

库 ： 目录
表 ：
t1.frm ： 数据字典信息
t1.ibd ： 数据行、索引

系统（共享）表空间：
ibdata1 ： DD 、 DWB 、CB 、UNDO

事务日志： redo log
ib_logfile0
ib_logfile1

临时表空间：
ibtmp1

4.1.2 微观

表空间：
共享表空间： ibdata1 ,存储系统相关数据
独立表空间： xxx.ibd ,存储数据和索引
UNDO表空间： 可独立配置,存储的是回滚日志
TEMP表空间： 存储临时表。

段 segment : 表数据存储的区域 ，由1-N个区构成
区 extent ： 聚簇索引分配存储空间的单元，默认64个连续的数据页,大小1M。
页 page ： IO的最小单元，默认16KB 。

4.2 In-Memory 结构

IBP : 数据缓冲区
缓冲数据和索引

ILB ：日志缓冲区
缓冲redo log

5.事务

5.1 介绍

事务（Transaction），伴随着业务中的，交易类的需要，出现的一种工作机制。

5.2 事务的 ACID 特性

A: atomicity. 原子性
C: consistency. 一致性
I: isolation. 隔离性
D: durability. 持久性

5.3 事务的生命周期管理

事务控制语句：
begin; /start transaction;
DML;
commit;/rollback;

5.4 autocommit 自动提交机制

​ mysql> select @@autocommit;
​ begin; /start transaction;
​ DML;
​ commit;/rollback;

0手动，1自动

5.5 隐式提交和回滚

​ 隐式提交 ： DDL、DCL。。。
​ 隐式回滚 ： 会话关闭、退出、死锁。

5.6 隔离级别(读隔离)

RU ：READ-UNCOMMITTED 读未提交
会出现问题： 脏读、不可重复读、幻读
RC : READ-COMMITTED 读已提交
会出现问题： 不可重复读、幻读
RR : REPETABLE-READ 可重复读
会出现问题：幻读,但是可以通过GAP和Next-lock防止幻读。
SE : SERIALIZABLE 可串行化

set gloabl transaction_isolation='xxx'

vim /etc/my.cnf
transaction_isolation='xxx'

6.事务语句的工作流程

redo

undo

数据页

CR

LSN

7.MVCC

read-view （读视图,快照），主要undo 来实现。
RC : 语句级别的快照（获取） , Begin DML1 DML2
RR : 事务级别的快照（获取） , Begin; DML1 ...... commit;

8.锁

Row Level lock : 基于索引加锁
record lock
GAP
Next-lock = R+G

9.总结

A : UNDO , REDO
C : REDO UNDO DWB
I : tranasaction_isolation lock MVCC
D : redo (WAL) , DWB

CR : REDO UNDO DWB

七、 日志管理

1.一般日志 general_log

1.1 作用：

调试工作、审计工具

1.2. 配置

general_log=1
general_log_file = /data/3306/data/node01.log

mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)

mysql> select @@general_log_file;
+----------------------------+
| @@general_log_file |
+----------------------------+
| /data/3306/data/node01.log |
+----------------------------+
1 row in set (0.00 sec)

1.3. 使用

tail -f /data/3306/data/node01.log

2.错误日志

2.1 作用

记录mysql启动以来所有事件、警告、错误信息。

2.2 配置

mysql> select @@log_error;
+--------------+
| @@log_error |
+--------------+
| ./node01.err |
+--------------+

2.3 使用

2020-07-31T09:36:39.031715Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2020-07-31T09:36:39.031765Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2020-07-31T09:36:39.031779Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-07-31T09:36:39.636977Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-07-31T09:36:39.637050Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-07-31T09:36:39.637063Z 0 [ERROR] Failed to initialize builtin plugins.
2020-07-31T09:36:39.637070Z 0 [ERROR] Aborting

3.二进制日志

3.1 作用

记录mysql工作期间，变更类的语句（DDL DCL DCL）
数据恢复、主从复制

3.2 配置

mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)

mysql> select @@log_bin_basename;
+--------------------+
| @@log_bin_basename |
+--------------------+
| NULL |
+--------------------+

vim /etc/my.cnf
server_id=10
log_bin=/data/3306/binlog/mysql-bin

[root@node01 data]# mkdir -p /data/3306/binlog
[root@node01 data]# chown -R mysql. /data/*

3.3 查询

mysql> show binary logs;
mysql> show master status;
mysql> show binlog events in 'mysql-bin.000010';

查看events：
Pos : 开始位置点（position）
End_log_pos：结束位置点
Info ：具体操作信息

3.4 binlog_format 日志格式

select @@binlog_format;

+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+

注意： 只针对DML（insert、update、delete）
statement 语句模式 update t1 set name='a' where id<100; insert into t1 values(自定义函数)
row 行模式 100行的数据变化。
mixed 混合模式

3.5 截取日志 并 恢复数据

db01 [oldguo]>show master status ;
db01 [oldguo]>show binlog events in 'mysql-bin.000003';
[root@db01 ~]# mysqlbinlog --skip-gtids --start-position=273 --stop-position=944 /data/3306/binlog/mysql-bin.000003 >/tmp/bin.sql

db01 [(none)]>set sql_log_bin=0;
db01 [(none)]>source /tmp/bin.sql;
db01 [oldguo]>set sql_log_bin=1;

1亿行 表。 误删除。 delete 100行。

3.6 binlog2sql 截取binlog单表的日志:

功能

1. 友好的展示或管理binlog
2. 快速DML闪回（通过日志翻转方式）。

安装配置binlog2sql

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql

yum install python3
pip3 install -r requirements.txt
pip3 show pymysql
pip3 install --upgrade PyMySQL

注意： PyMySQL版本，可能兼容性有问题。经测试.0.9.3版本是可用的。0.10.0版本会有问题。

解析日志事件SQL
[root@db01 binlog2sql]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d mdb -t oldguo --start-file='mysql-bin.000002'
USE b'test';
create database test;
USE b'test';
create table t1 (id int);
INSERT INTO test.t1(id) VALUES (1); #start 649 end 822 time 2020-06-13 19:58:40
INSERT INTO test.t1(id) VALUES (2); #start 649 end 822 time 2020-06-13 19:58:40
INSERT INTO test.t1(id) VALUES (3); #start 649 end 822 time 2020-06-13 19:58:40
UPDATE test.t1 SET id=10 WHERE id=1 LIMIT 1; #start 932 end 1110 time 2020-06-13 19:58:56
DELETE FROM test.t1 WHERE id=3 LIMIT 1; #start 932 end 1198 time 2020-06-13 19:59:05
[root@db01 binlog2sql]#

只解析delete类型操作

[root@db01 binlog2sql]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d test -t t1 --start-file='mysql-bin.000003' --sql-type=delete

生成指定事件回滚语句
[root@db01 binlog2sql]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d test -t t1 --start-file='mysql-bin.000003' --sql-type=delete --start-position=932 --stop-position=1198 -B

[root@db01 binlog2sql]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d test -t t1 --start-file='mysql-bin.000003' --sql-type=delete --start-position=932 --stop-position=1198 -B>/tmp/flashback.sql

4.慢日志

4.1 作用

记录运行较慢的语句。

4.2 配置方法

开关

db01 [(none)]>select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 0 |
+------------------+

存放位置

db01 [(none)]>select @@slow_query_log_file;
+-------------------------------+
| @@slow_query_log_file |
+-------------------------------+
| /data/3306/data/db01-slow.log |
+-------------------------------+

db01 [(none)]>select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
1 row in set (0.00 sec)

db01 [(none)]>select @@log_queries_not_using_indexes;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
| 0 |
+---------------------------------+

db01 [(none)]>set global slow_query_log=1;
db01 [(none)]>set global long_query_time=0.01;
db01 [(none)]>set global log_queries_not_using_indexes=1;

4.3 模拟慢语句

[root@db01 data]# mysqldumpslow -s c -t 3 db01-slow.log
Reading mysql slow query log from db01-slow.log
Count: 7 Time=0.00s (0s) Lock=0.00s (0s) Rows=10.0 (70), root[root]@localhost
select * from t100w limit N ,N
Count: 5 Time=1.58s (7s) Lock=0.00s (0s) Rows=1296.0 (6480), root[root]@localhost
select count() from t100w group by k2 order by count() desc
Count: 4 Time=0.82s (3s) Lock=0.00s (0s) Rows=8.0 (32), root[root]@localhost
select * from t100w where k1 = 'S' and k2='S'

八、 备份恢复--mysqldump 逻辑备份工具应用

1.连接参数

-uroot
-p123
-h 10.0.0.51
-P 3306

2.全备 -A

[root@db01 ~]# mysqldump -uroot -p123 -h 10.0.0.51 -P 3306 -A >/data/full.sql

3.单库或多库 -B

[root@db01 ~]# mysqldump -uroot -p123 -h 10.0.0.51 -P 3306 -B test oldguo >/data/db.sql

4.单表或多表

[root@db01 ~]# mysqldump -uroot -p123 -h 10.0.0.51 -P 3306 world city country >/data/tb.sql

5.--master-data=2

记录binlog位置点。
自动加锁（FTWRL）。
mysqldump -uroot -p123 -h 10.0.0.51 -P 3306 -A --master-data=2 >/data/full.sql

6.--single-transaction

对InnoDB表进行快照备份。
mysqldump -uroot -p123 -h 10.0.0.51 -P 3306 -A --single-transaction --master-data=2 >/data/full.sql

7.-R -E --triggers

mysqldump -uroot -p123 -h 10.0.0.51 -P 3306 -A --single-transaction --master-data=2 -R -E --triggers >/data/full.sql

8.恢复数据

db01 [world]>source /data/full.sql

九、 主从复制

1.介绍

两台或两台以上MySQL实例，通过binlog日志，同步数据。

2.主从复制前提（搭建过程）

① 两台以上的MySQL实例（同版本）
② server_id 和 server_uuid 要不同
mysql -e "select @@server_id;select @@server_uuid;"
③ 主库开启binlog日志，创建专用的复制用户
[root@db02 app]# mysql -e "select @@log_bin;"
[root@db01 data]# mysql -e "grant replication slave on . to repl@'10.0.0.%' identified by '123'"

④ 备份主库数据，恢复到从库
mysqldump -uroot -p123 -h 10.0.0.51 -P 3306 -A --single-transaction --master-data=2 -R -E --triggers >/data/full.sql

⑤ 启用主从复制
[root@db02 app]# grep "-- CHANGE" /data/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=21795459;

CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=21795459,
MASTER_CONNECT_RETRY=10;

start slave;

⑥ 检查主从状态

db02 [(none)]>show slave status G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 21795459
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.....

3.主从复制工作原理

3.1 线程

主库： binlog_dump_thread
接收从库请求。
binlog日志的投递。

从库：
SLAVE_IO_THREAD :
请求binlog
接收binlog
存储binlog

​ SLAVE_SQL_THREAD：
​ 回放日志。

3.2 文件

​ 主库： binlog日志文件
​ 从库：
​ master.info : 记录主库的连接信息，复制的位置点。
​ relay-log.info：回放到的位置点信息。
​ relaylog ：从库存储binlog的文件。

3.3 主从复制原理

4.主从复制监控

主库连接信息
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3306
Connect_Retry: 10

获取到主库的binlog位置点
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 21795624

回放到的日志点
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 485
Relay_Master_Log_File: mysql-bin.000002
Exec_Master_Log_Pos: 21795624

主从延时的秒数
Seconds_Behind_Master: 0

主从故障监控
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

过滤复制相关信息
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:

延时从库状态
SQL_Delay: 0
SQL_Remaining_Delay: NULL

gtid复制相关信息
Retrieved_Gtid_Set: c921924c-d527-11ea-898a-000c29ea9d83:76
Executed_Gtid_Set: 675fe60f-d3a5-11ea-9163-000c29f2d9fe:1-2

5.主从复制的架构演变

5.1 基础架构演变

1主1从
1主多从
级联主从
双主

5.2 高级架构演变

​ 读写分离 ： ProxySQL（6033）
​ 高可用 ： MHA
​ 分布式 ： mycat
​

NoSQL - Redis 缓存技术

1、监控
2、自动选主，切换（6381 slaveof no one）
   采用的是raft分布式一致性协议进行选主：数据节接近主，可以和大部分节点联系，少数服从多数。
3、重构主从管理
4、应用透明 
5、自动处理故障节点


sentinel搭建过程
mkdir /data/26380

cd /data/26380

vim sentinel.conf
port 26380
dir "/data/26380"
sentinel monitor mymaster 127.0.0.1 6380 1
sentinel down-after-milliseconds mymaster 5000
sentinel auth-pass mymaster 123 

启动：
[root@db01 26380]# redis-sentinel /data/26380/sentinel.conf  &>/tmp/sentinel.log &

==============================