怎么進(jìn)行MySQL的執(zhí)行過程分析

本篇文章給大家分享的是有關(guān)怎么進(jìn)行MySQL的執(zhí)行過程分析，小編覺得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

1. MySQL的執(zhí)行過程分析

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

# 如果提示需要賬號(hào)密碼，就用這個(gè)方式下載
# 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、查看系統(tǒng)是否自帶mariadb
rpm -qa | grep mariadb

3、將查出的mariadb進(jìn)行卸載
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個(gè)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時(shí)缺少依賴報(bào)錯(cuò)
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、啟動(dòng)mysql服務(wù)
service mysqld start

10、查看v5.7版本的默認(rèn)登錄密碼
grep password /var/log/mysqld.log

11、登錄到mysql命令行，修改默認(rèn)密碼
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

12、會(huì)告訴你密碼不符合規(guī)范，可以修改校驗(yàn)等級(jí)和長(zhǎng)度后再次執(zhí)行上面的語(yǔ)句
set global validate_password_policy=LOW;
set global validate_password_length=6; 

13、賬戶授權(quán)
use mysql;
select host,user from user;
# 所有ip都可以訪問數(shù)據(jù)庫(kù)
grant all privileges on *.* to gavin@'%' identified by '123456';
# 只有內(nèi)網(wǎng)網(wǎng)段ip才可訪問，并授權(quán)賬號(hào)可以授權(quán)其他人
# grant all privileges on *.* to gavin@'192.168.%' identified by '123456' with grant option;
flush privileges;

1.2. 學(xué)習(xí)的基礎(chǔ)數(shù)據(jù)導(dǎo)入

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. 基礎(chǔ)知識(shí)回顧

• where條件解析順序

• MySQL：自左向右

• Oralce：自右向左

• SQL執(zhí)行順序

• FROM

• ON

• JOIN

• WHERE

• GROUP BY

• HAVING

• SELECT

• DISTINCT

• ORDER BY

• LIMIT

• 全文索引

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

• MySQL中SQL執(zhí)行的過程-MySQL 5.7

• 連接器

• 查詢緩存

• 分析器（詞法、語(yǔ)法）

• 優(yōu)化器

• 執(zhí)行器

聲明使用查詢緩存，但是不建議這樣使用

select SQL_CACHE * from ad_user;

注意：MySQL 8.0 把查詢緩存這個(gè)模塊去掉了

MySQL數(shù)據(jù)引擎

MySQL支持的數(shù)據(jù)引擎

mysql> show engines;

# 查詢表的引擎

```sql
show table status like '%ad_user%' \G;

MySQL數(shù)據(jù)庫(kù)數(shù)據(jù)存儲(chǔ)的位置

cd /var/lib/mysql

這個(gè)目錄下存放的是數(shù)據(jù)庫(kù)對(duì)應(yīng)的各個(gè)數(shù)據(jù)庫(kù)的數(shù)據(jù)文件

MySQL配置文件默認(rèn)路徑

vi /etc/my.cnf

2. MySQL內(nèi)部的日志類型作用及分析 MySQL常用的日志有下面幾個(gè)

• 錯(cuò)誤日志

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

log_warnings= 0| 1| 2

0 關(guān)閉

1 開啟-default

>1 失敗的連接，拒絕訪問的錯(cuò)誤也會(huì)記錄

• 查詢?nèi)罩?/p>

查詢?nèi)罩緯?huì)將所有數(shù)據(jù)庫(kù)的操作都會(huì)記錄（general log 通用日志）

消耗I/O，默認(rèn)不開

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

慢查詢?nèi)罩?/p>

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;

直接查看慢查詢?nèi)罩?/p>

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提供的慢查詢命令來查看

# 根據(jù)時(shí)間降序
mysqldumpslow -s -t /var/lib/mysql/DB213-slow.log
# 根據(jù)記錄數(shù)降序
mysqldumpslow -s -r /var/lib/mysql/DB213-slow.log
# 根據(jù)執(zhí)行次數(shù)降序
mysqldumpslow -s -C /var/lib/mysql/DB213-slow.log
# 幫助手冊(cè)
mysqldumpslow --help

內(nèi)容

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

• 二進(jìn)制日志：binlog

這個(gè)是數(shù)據(jù)庫(kù)中最重要的日志，會(huì)記錄所有DML，不會(huì)記錄select

• 事務(wù)日志

• 中繼日志：reply log

3. MySQL數(shù)據(jù)備份恢復(fù)以及執(zhí)行優(yōu)化

3.1. Binlog是有三種模式的 statement

• 純粹的記錄DML的語(yǔ)句

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

# statement
# MySQL會(huì)自動(dòng)生成一個(gè)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過期清理時(shí)間
expire_logs_days=3

[mysqld]
server-id=213

mysql> show binlog events in 'mysql-bin.000001';

row（v5.7版本默認(rèn)是row模式）

• 過去的歷史值和現(xiàn)在的新值

row模式的日志查詢

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

查詢的結(jié)果

### 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 */

怎么快速找到誤操作的語(yǔ)句

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模式的數(shù)據(jù)，得到時(shí)間區(qū)間

作業(yè)：自己設(shè)置一下statement和row模式mixed模式的binlog記錄，自己查看一下相關(guān)內(nèi)容

如果進(jìn)行大批量的數(shù)據(jù)操作，這個(gè)時(shí)候數(shù)據(jù)庫(kù)是安全，不讓MySQL記錄

mysql> set sql_log_bin=0; #臨時(shí)關(guān)閉binlog

mixed

混合模式

statement：95%

3.2. 數(shù)據(jù)備份 備份的場(chǎng)景和分析

• 全量備份

• 差異備份

• 增量備份

• 時(shí)間點(diǎn)備份

備份類型

• 熱備：熱備是不能通過簡(jiǎn)單的copy命令

• 溫備：只能進(jìn)行讀操作

• 冷備

• 物理備份：copy文件

• 邏輯備份

常用備份工具

• mysqldump

• Percona提供的xtrabackup

 mysqldump --help
# --master-data 0(不記錄position) 1(記錄position位置) 2(記錄position位置并注釋該條)
# routines 存儲(chǔ)過程
# triggers 觸發(fā)器
# 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

場(chǎng)景：小明200積分，12備份，積分表有200w數(shù)據(jù)，數(shù)據(jù)庫(kù)有300張表

以上就是怎么進(jìn)行MySQL的執(zhí)行過程分析，小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見到或用到的。希望你能通過這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注億速云行業(yè)資訊頻道。