怎么进行MySQL的执行过程分析

本篇文章给大家分享的是有关怎么进行MySQL的执行过程分析，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

1. MySQL的执行过程分析

1.1. MySQL 5.7安装步骤

1、下载rpm包wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar# 如果提示需要账号密码，就用这个方式下载# wget --http-user=youremail@email.com --http-passwd=yourpassword https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar2、查看系统是否自带mariadbrpm -qa | grep mariadb3、将查出的mariadb进行卸载rpm -e --nodeps mariadb-libs-5.5.64-1.el7.x86_644、把刚刚下载的mysql tar解压tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar5、在解压目录安装如下4个mysql核心包rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpmrpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpmrpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpmrpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm6、安装到server时缺少依赖报错error: Failed dependencies:        libaio.so.1()(64bit) is needed by mysql-community-server-5.7.28-1.el7.x86_64        libaio.so.1(LIBAIO_0.1)(64bit) is needed by mysql-community-server-5.7.28-1.el7.x86_64        libaio.so.1(LIBAIO_0.4)(64bit) is needed by mysql-community-server-5.7.28-1.el7.x86_647、安装缺少的依赖yum -y install libaio8、再次安装serverrpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm9、启动mysql服务service mysqld start10、查看v5.7版本的默认登录密码grep password /var/log/mysqld.log11、登录到mysql命令行，修改默认密码ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';12、会告诉你密码不符合规范，可以修改校验等级和长度后再次执行上面的语句set global validate_password_policy=LOW;set global validate_password_length=6; 13、账户授权use mysql;select host,user from user;# 所有ip都可以访问数据库grant all privileges on *.* to gavin@'%' identified by '123456';# 只有内网网段ip才可访问，并授权账号可以授权其他人# grant all privileges on *.* to gavin@'192.168.%' identified by '123456' with grant option;flush privileges;

1.2. 学习的基础数据导入

create database icoding_admin;DROP TABLE IF EXISTS `ad_role`;CREATE TABLE `ad_role` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  `role_name` varchar(50) NOT NULL DEFAULT '',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `ad_role` (`id`, `role_name`)VALUES        (1,'vip1'),        (2,'vip2'),        (3,'vip3');        DROP TABLE IF EXISTS `ad_user`;CREATE TABLE `ad_user` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  `username` varchar(50) NOT NULL DEFAULT '',  `password` varchar(50) NOT NULL DEFAULT '',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `ad_user` (`id`, `username`, `password`)VALUES        (1,'arry','123456'),        (2,'gavin','1234567'),        (3,'coding','123456');        DROP TABLE IF EXISTS `ad_user_role`;CREATE TABLE `ad_user_role` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  `user_id` int(11) NOT NULL,  `role_id` int(11) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `ad_user_role` (`id`, `user_id`, `role_id`)VALUES        (1,1,1),        (2,1,2),        (3,1,3),        (4,2,2),        (5,3,3),        (7,2,3);

3. 基础知识回顾

• where条件解析顺序

• MySQL：自左向右

• Oralce：自右向左

• SQL执行顺序

• FROM

• ON

• JOIN

• WHERE

• GROUP BY

• HAVING

• SELECT

• DISTINCT

• ORDER BY

• LIMIT

• 全文索引

• 只有在MyISAM的引擎才可以用，只能使用在CHAR、VARCHAR、TEXT字段使用使用

• MySQL中SQL执行的过程-MySQL 5.7

• 连接器

• 查询缓存

• 分析器（词法、语法）

• 优化器

• 执行器

声明使用查询缓存，但是不建议这样使用

select SQL_CACHE * from ad_user;

注意：MySQL 8.0 把查询缓存这个模块去掉了

MySQL数据引擎

MySQL支持的数据引擎

mysql> show engines;

# 查询表的引擎```sqlshow table status like '%ad_user%' \G;

MySQL数据库数据存储的位置

cd /var/lib/mysql

这个目录下存放的是数据库对应的各个数据库的数据文件

MySQL配置文件默认路径

vi /etc/my.cnf

2. MySQL内部的日志类型作用及分析 MySQL常用的日志有下面几个

• 错误日志

show variables like '%log_error%';log_error=/var/log/mysqld.loglog_warnings=2

log_warnings= 0| 1| 2

0 关闭

1 开启-default

>1 失败的连接，拒绝访问的错误也会记录

• 查询日志

查询日志会将所有数据库的操作都会记录（general log 通用日志）

消耗I/O，默认不开

show variables like '%general_log%';log_output=FILEFILE、TABLE、FILE,TABLE、NONE

慢查询日志

show variables like '%slow%';[mysqld]slow_query_log=ONslow_launch_time=3slow_query_log_file=/usr/local/slow.logchown -R mysql:mysql /usr/local/select sleep(3),user from user;

直接查看慢查询日志

Time                 Id Command    Argument# Time: 2020-06-17T13:05:20.509651Z# User[@Host](https://my.oschina.net/u/116016): root[root] @ localhost []  Id:     2# Query_time: 12.000509  Lock_time: 0.000111 Rows_sent: 3  Rows_examined: 3use icoding_admin;SET timestamp=1592399120;select sleep(4),username from ad_user;# Time: 2020-06-17T13:09:14.528655Z# User[@Host](https://my.oschina.net/u/116016): root[root] @ localhost []  Id:     2# Query_time: 12.000488  Lock_time: 0.000097 Rows_sent: 3  Rows_examined: 3SET timestamp=1592399354;select sleep(4),id from ad_user;

可以使用mysql提供的慢查询命令来查看

# 根据时间降序mysqldumpslow -s -t /var/lib/mysql/DB213-slow.log# 根据记录数降序mysqldumpslow -s -r /var/lib/mysql/DB213-slow.log# 根据执行次数降序mysqldumpslow -s -C /var/lib/mysql/DB213-slow.log# 帮助手册mysqldumpslow --help

内容

Reading mysql slow query log from /var/lib/mysql/DB213-slow.logCount: 1  Time=12.00s (12s)  Lock=0.00s (0s)  Rows=3.0 (3), root[root][@localhost](https://my.oschina.net/u/570656)  select sleep(N),id from ad_userCount: 1  Time=12.00s (12s)  Lock=0.00s (0s)  Rows=3.0 (3), root[root][@localhost](https://my.oschina.net/u/570656)  select sleep(N),username from ad_user

• 二进制日志：binlog

这个是数据库中最重要的日志，会记录所有DML，不会记录select

• 事务日志

• 中继日志：reply log

3. MySQL数据备份恢复以及执行优化

3.1. Binlog是有三种模式的 statement

• 纯粹的记录DML的语句

- update ad_user set username='gavin.huang' where id=1;- delete from ad_user where id=1;

# statement# MySQL会自动生成一个mysql-bin-00001.log# chown -R mysql:mysql /usr/local/binlog/log_bin=/usr/local/binlog/mysql-binbinlog_format=statement# binlog日志切割的大小max_binlog_size=500m# binlog过期清理时间expire_logs_days=3[mysqld]server-id=213mysql> show binlog events in 'mysql-bin.000001';

row（v5.7版本默认是row模式）

• 过去的历史值和现在的新值

row模式的日志查询

mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000002

查询的结果

### DELETE FROM `icoding_admin`.`ad_user`### WHERE###   @1=3 /* INT meta=0 nullable=0 is_null=0 */###   @2='coding' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */###   @3='123456' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */### UPDATE `icoding_admin`.`ad_user`### WHERE###   @1=2 /* INT meta=0 nullable=0 is_null=0 */###   @2='gavin' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */###   @3='1234567' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */### SET###   @1=2 /* INT meta=0 nullable=0 is_null=0 */###   @2='gavin.huang' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */###   @3='1234567' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */

怎么快速找到误操作的语句

row模式的定位

mysqlbinlog --base64-output=decode-rows --start-datetime='2020-06-17 22:10' --stop-datetime='2020-06-17 22:25' -vv mysql-bin.000002

mysqlbinlog也可以查询statement模式的数据，得到时间区间

作业：自己设置一下statement和row模式mixed模式的binlog记录，自己查看一下相关内容

如果进行大批量的数据操作，这个时候数据库是安全，不让MySQL记录

mysql> set sql_log_bin=0; #临时关闭binlog

mixed

混合模式

statement：95%

3.2. 数据备份 备份的场景和分析

• 全量备份

• 差异备份

• 增量备份

• 时间点备份

备份类型

• 热备：热备是不能通过简单的copy命令

• 温备：只能进行读操作

• 冷备

• 物理备份：copy文件

• 逻辑备份

常用备份工具

• mysqldump

• Percona提供的xtrabackup

 mysqldump --help# --master-data 0(不记录position) 1(记录position位置) 2(记录position位置并注释该条)# routines 存储过程# triggers 触发器# events 事件# single-transaction# --ignore-table=icoding_admin.ad_user_role --ignore-table=icoding_admin.ad_user# 基于innodb引擎mysqldump -uroot -p123456 -h227.0.0.1 --master-data=2 --routines --triggers --events --single-transaction --databases icoding_admin --ignore-table=icoding_admin.ad_user_role > mydb.sql

为什么用--single-transaction

场景：小明200积分，12备份，积分表有200w数据，数据库有300张表

以上就是怎么进行MySQL的执行过程分析，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注行业资讯频道。