如果您對數(shù)據(jù)庫感興趣，可以添加 DBA解決方案QQ群：895979329

1. 體系結(jié)構(gòu)

1.1 C/S(客戶端/服務(wù)端)模型介紹

image

TCP/IP方式（遠程、本地）：
mysql -uroot -poldboy123 -h 10.0.0.51 -P3306
Socket方式(僅本地)：
mysql -uroot -poldboy123 -S /tmp/mysql.sock

1.2 實例介紹

實例=mysqld后臺守護進程+Master Thread +干活的Thread+預(yù)分配的內(nèi)存
公司=老板+經(jīng)理+員工+辦公室

1.3 mysqld程序運行原理

1.3.1 mysqld程序結(jié)構(gòu)

image

1.3.2 一條SQL語句的執(zhí)行過程

1.3.2.1 連接層
（1）提供連接協(xié)議：TCP/IP 伴找、SOCKET
（2）提供驗證：用戶蝎抽、密碼堂湖，IP喊巍，SOCKET
（3）提供專用連接線程：接收用戶SQL鼎姊，返回結(jié)果
通過以下語句可以查看到連接線程基本情況
mysql> show processlist;

1.3.2.2 SQL層（重點）

（1）接收上層傳送的SQL語句
（2）語法驗證模塊：驗證語句語法,是否滿足SQL_MODE
（3）語義檢查：判斷SQL語句的類型
DDL ：數(shù)據(jù)定義語言
DCL ：數(shù)據(jù)控制語言
DML ：數(shù)據(jù)操作語言
DQL： 數(shù)據(jù)查詢語言
...
（4）權(quán)限檢查：用戶對庫表有沒有權(quán)限
（5）解析器：對語句執(zhí)行前,進行預(yù)處理骡和，生成解析樹(執(zhí)行計劃),說白了就是生成多種執(zhí)行方案.
（6）優(yōu)化器：根據(jù)解析器得出的多種執(zhí)行計劃，進行判斷相寇，選擇最優(yōu)的執(zhí)行計劃
        代價模型：資源（CPU IO MEM）的耗損評估性能好壞
（7）執(zhí)行器：根據(jù)最優(yōu)執(zhí)行計劃慰于，執(zhí)行SQL語句，產(chǎn)生執(zhí)行結(jié)果
執(zhí)行結(jié)果：在磁盤的xxxx位置上
（8）提供查詢緩存（默認是沒開啟的）唤衫，會使用redis tair替代查詢緩存功能
（9）提供日志記錄（日志管理章節(jié)）：binlog婆赠，默認是沒開啟的。

1.3.2.3 存儲引擎層（類似于Linux中的文件系統(tǒng)）

負責(zé)根據(jù)SQL層執(zhí)行的結(jié)果佳励，從磁盤上拿數(shù)據(jù)休里。
將16進制的磁盤數(shù)據(jù)，交由SQL結(jié)構(gòu)化化成表赃承，
連接層的專用線程返回給用戶妙黍。

1.4 邏輯結(jié)構(gòu)

image.png

以上圖片由五行哥提供

1.4.1 庫：

庫名，庫屬性

1.4.2 表

表名
屬性
列:列名(字段),列屬性(數(shù)據(jù)類型,約束等)
數(shù)據(jù)行(記錄)

1.5 物理存儲結(jié)構(gòu)引入

image.png

以上圖片由五行哥提供

1.5.1 庫的物理存儲結(jié)構(gòu)

用文件系統(tǒng)的目錄來存儲

1.5.2 表的物理存儲結(jié)構(gòu)

MyISAM（一種引擎）的表：
-rw-r----- 1 mysql mysql   10816 Apr 18 11:37 user.frm
-rw-r----- 1 mysql mysql     396 Apr 18 12:20  user.MYD
-rw-r----- 1 mysql mysql    4096 Apr 18 14:48 user.MYI

InnoDB(默認的存儲引擎)的表：
-rw-r----- 1 mysql mysql    8636 Apr 18 11:37 time_zone.frm
-rw-r----- 1 mysql mysql   98304 Apr 18 11:37 time_zone.ibd
time_zone.frm：存儲列相關(guān)信息
time_zone.ibd：數(shù)據(jù)行+索引

1.5.3 表的段瞧剖、區(qū)拭嫁、頁（16k）（了解）

頁：最小的存儲單元，默認16k
區(qū)：64個連續(xù)的頁抓于，共1M
段：一個表就是一個段做粤，包含一個或多個區(qū)

2. 基礎(chǔ)管理

2.1 用戶、權(quán)限管理

2.1.1 用戶

作用：

登錄捉撮，管理數(shù)據(jù)庫邏輯對象

定義：

用戶名@'白名單'
白名單支持的方式怕品？
wordpress@'10.0.0.%'    
wordpress@'%'
wordpress@'10.0.0.200'
wordpress@'localhost'
wordpress@'db02'
wordpress@'10.0.0.5%'
wordpress@'10.0.0.0/255.255.254.0'

管理操作：

增：
mysql> create user oldboy@'10.0.0.%' identified by '123';
查：
mysql> desc mysql.user;    ---->  authentication_string
mysql> select user ,host ,authentication_string from mysql.user
改:
mysql> alter user oldboy@'10.0.0.%' identified by '456';
刪：
mysql> drop user oldboy@'10.0.0.%';

2.1.2 權(quán)限

權(quán)限管理操作：

mysql> grant all on wordpress.* to wordpress@'10.0.0.%' identified  by '123';

常用權(quán)限介紹:

ALL:
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
ALL : 以上所有權(quán)限，一般是普通管理員擁有的
with grant option：超級管理員才具備的巾遭，給別的用戶授權(quán)的功能

權(quán)限作用范圍:

*.*                  ---->管理員用戶
wordpress.*          ---->開發(fā)和應(yīng)用用戶
wordpress.t1

需求1：windows機器的navicat登錄到linux中的MySQL肉康，管理員用戶。

mysql> grant all on *.* to root@'10.0.0.%' identified by '123';

需求2：創(chuàng)建一個應(yīng)用用戶app用戶灼舍，能從windows上登錄mysql迎罗，并能操作app庫

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

2.1.3 開發(fā)人員用戶授權(quán)流程

1.權(quán)限
2.對誰操作
3.你從哪來
4.密碼要求

2.1.4 提示：8.0在grant命令添加新特性

建用戶和授權(quán)分開了
grant 不再支持自動創(chuàng)建用戶了，不支持改密碼
授權(quán)之前片仿，必須要提前創(chuàng)建用戶。

2.1.5 查看授權(quán)

mysql> show grants for app@'10.0.0.%';

2.1.6 回收權(quán)限

revoke  delete on app.*  from app@'10.0.0.%'尤辱；

2.1.7 本地管理員用戶密碼忘記.

[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
mysql> flush privileges;
mysql>  alter user root@'localhost' identified by '123456';
[root@db01 ~]# pkill mysqld
[root@db01 ~]# systemctl start  mysqld

2.2 連接管理

2.2.1 自帶客戶端命令

mysql 常用參數(shù)：

-u                   用戶
-p                   密碼
-h                   IP
-P                   端口
-S                   socket文件
-e                   免交互執(zhí)行命令
<                    導(dǎo)入SQL腳本

[root@db01 ~]# mysql -uroot -p -h 10.0.0.51 -P3306
Enter password:
mysql> select @@socket;
+-----------------+
| @@socket        |
+-----------------+
| /tmp/mysql.sock |
[root@db01 ~]# mysql -uroot -p -S /tmp/mysql.sock
Enter password:
[root@db01 ~]# mysql -uroot -p -e "select user,host from mysql.user;"
Enter password:
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| abc          | 10.0.0.%  |
| app          | 10.0.0.%  |
| root          | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys    | localhost |
| root          | localhost |
+---------------+-----------+
[root@db01 ~]#
[root@db01 ~]# mysql -uroot -p <world.sql
Enter password:
[root@db01 ~]#

2.3 多種啟動方式介紹

image

提示：

以上多種方式砂豌，都可以單獨啟動MySQL服務(wù)
mysqld_safe和mysqld一般是在臨時維護時使用厢岂。
另外，從Centos 7系統(tǒng)開始阳距，支持systemd直接調(diào)用mysqld的方式進行啟動數(shù)據(jù)庫

2.4 初始化配置

2.4.0 作用

控制MySQL的啟動
影響到客戶端的連接

2.4.1 初始化配置的方法

預(yù)編譯
**配置文件(所有啟動方式)**
命令行參數(shù) (僅限于 mysqld_safe mysqld)

2.4.2 初始配置文件

初始化配置文件的默認讀取路徑

[root@db01 ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
注:
默認情況下塔粒，MySQL啟動時，會依次讀取以上配置文件筐摘，如果有重復(fù)選項卒茬，會以最后一個文件設(shè)置的為準(zhǔn)。
但是咖熟，如果啟動時加入了--defaults-file=xxxx時圃酵，以上的所有文件都不會讀取.

配置文件的書寫方式:

[標(biāo)簽]
配置項=xxxx

標(biāo)簽類型：服務(wù)端、客戶端
服務(wù)器端標(biāo)簽：
[mysqld]
[mysqld_safe]
[server]

客戶端標(biāo)簽：
[mysql]
[mysqldump]
[client]

配置文件的示例展示：
[root@db01 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/mysql.log
[mysql]
socket=/tmp/mysql.sock
prompt=Master [\\d]>

2.5 多實例的應(yīng)用

2.5.1 準(zhǔn)備多個目錄

mkdir -p /data/330{7,8,9}/data

2.5.2 準(zhǔn)備配置文件

cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/app/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF

cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/app/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF

cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/app/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF

2.5.3 初始化三套數(shù)據(jù)

mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/app/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/app/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/app/mysql

2.5.4 systemd管理多實例

cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service

vim mysqld3307.service
ExecStart=/app/mysql/bin/mysqld  --defaults-file=/data/3307/my.cnf
vim mysqld3308.service
ExecStart=/app/mysql/bin/mysqld  --defaults-file=/data/3308/my.cnf
vim mysqld3309.service
ExecStart=/app/mysql/bin/mysqld  --defaults-file=/data/3309/my.cnf

2.5.5 授權(quán)

chown -R mysql.mysql /data/*

2.5.6 啟動

systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service

2.5.7 驗證多實例

netstat -lnp|grep 330
mysql -S /data/3307/mysql.sock -e "select @@server_id"
mysql -S /data/3308/mysql.sock -e "select @@server_id"
mysql -S /data/3309/mysql.sock -e "select @@server_id"

Oldguo-標(biāo)桿班級-MySQL-lesson02-體系結(jié)構(gòu)與管理

Oldguo-標(biāo)桿班級-MySQL-lesson02-體系結(jié)構(gòu)與管理

1. 體系結(jié)構(gòu)

1.1 C/S(客戶端/服務(wù)端)模型介紹

1.2 實例介紹

1.3 mysqld程序運行原理

1.3.1 mysqld程序結(jié)構(gòu)

1.3.2 一條SQL語句的執(zhí)行過程

1.4 邏輯結(jié)構(gòu)

1.4.1 庫：

1.4.2 表

1.5 物理存儲結(jié)構(gòu)引入

1.5.1 庫的物理存儲結(jié)構(gòu)

1.5.2 表的物理存儲結(jié)構(gòu)

1.5.3 表的段瞧剖、區(qū)拭嫁、頁（16k）（了解）

2. 基礎(chǔ)管理

2.1 用戶、權(quán)限管理

2.1.1 用戶

2.1.2 權(quán)限

2.1.3 開發(fā)人員用戶授權(quán)流程

2.1.4 提示：8.0在grant命令添加新特性

2.1.5 查看授權(quán)

2.1.6 回收權(quán)限

2.1.7 本地管理員用戶密碼忘記.

2.2 連接管理

2.2.1 自帶客戶端命令

2.3 多種啟動方式介紹

2.4 初始化配置

2.4.0 作用

2.4.1 初始化配置的方法

2.4.2 初始配置文件

2.5 多實例的應(yīng)用

2.5.1 準(zhǔn)備多個目錄

2.5.2 準(zhǔn)備配置文件

2.5.3 初始化三套數(shù)據(jù)

2.5.4 systemd管理多實例

2.5.5 授權(quán)

2.5.6 啟動

2.5.7 驗證多實例