共计 7143 个字符,预计需要花费 18 分钟才能阅读完成。
本篇文章给大家分享的是有关怎么进行 MySQL 的执行过程分析,丸趣 TV 小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着丸趣 TV 小编一起来看看吧。
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.tar
2、查看系统是否自带 mariadb
rpm -qa | grep mariadb
3、将查出的 mariadb 进行卸载
rpm -e --nodeps mariadb-libs-5.5.64-1.el7.x86_64
4、把刚刚下载的 mysql tar 解压
tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
5、在解压目录安装如下 4 个 mysql 核心包
rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
6、安装到 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_64
7、安装缺少的依赖
yum -y install libaio
8、再次安装 server
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
9、启动 mysql 服务
service mysqld start
10、查看 v5.7 版本的默认登录密码
grep password /var/log/mysqld.log
11、登录到 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);
基础知识回顾
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;
存储引擎说明 MyISAM 高速查询引擎,不支持事物 InnoDBv5.5 以后是 MySQL 的默认引擎 Archive 数据压缩存储引擎,便于数据归档 Memory 内存存储引擎对比 MyISAM 和 InnoDB
对比 InnoDB—————————– 存储文件的形式.frm 表定义文件,.ibd 存放数据和索引的锁表、页、行事务支持 CRUD 可同时读、写
# 查询表的引擎
```sql
show 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.log
log_warnings=2
log_warnings= 0| 1| 20 关闭 1 开启 -default 1 失败的连接,拒绝访问的错误也会记录
查询日志
查询日志会将所有数据库的操作都会记录(general log 通用日志)
消耗 I /O,默认不开
show variables like %general_log%
log_output=FILE
FILE、TABLE、FILE,TABLE、NONE
慢查询日志
show variables like %slow%
[mysqld]
slow_query_log=ON
slow_launch_time=3
slow_query_log_file=/usr/local/slow.log
chown -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: 3
use 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: 3
SET 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.log
Count: 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_user
Count: 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-bin
binlog_format=statement
# binlog 日志切割的大小
max_binlog_size=500m
# binlog 过期清理时间
expire_logs_days=3
[mysqld]
server-id=213
mysql 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 的执行过程分析,丸趣 TV 小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注丸趣 TV 行业资讯频道。