怎么進行MySQL的執行過程分析

151次閱讀

共計 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 行業資訊頻道。

正文完