MySQL-體系結(jié)構(gòu)和管理

1、MySQL的C/S模型

1>Server：mysqld
2>Client：

支持兩種協(xié)議：

socket：僅本地連接使用

tcp/ip：應(yīng)用連接使用（用的比較多）

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

2、實例

mysqld----->master thread-------> 干活的線程+預(yù)分配的內(nèi)存結(jié)構(gòu)
boss              經(jīng)理                           員工               辦公室

3泳梆、MySQL的程序結(jié)構(gòu)

3.1 MySQL的專用管理和操作命令SQL語句

分為4大類：
DDL：數(shù)據(jù)定義語言
DCL：數(shù)據(jù)控制語言
DML：數(shù)據(jù)操作語言
DQL：數(shù)據(jù)查詢語言

3.2 SQL語句的執(zhí)行過程

1>連接層

提供連接協(xié)議（在Linux中提供兩種連接協(xié)議socket和tcp/ip）
驗證user、password优妙、host、port等
提供連接線程
- 查看連接線程：mysql> show [full] processlist;
- 功能：負(fù)責(zé)接受SQL語句和返回結(jié)果

2>SQL層（重點）

語法檢查和SQL_MODE檢查
語義檢查（判斷SQL語句的類型）和權(quán)限檢查
解析預(yù)處理卡辰，生成解析樹（執(zhí)行計劃樹）
優(yōu)化器會根據(jù)自帶算法，選擇最優(yōu)的方案（代價模型算法）-----不需要人為干預(yù)
- 代價九妈？cpu、io萌朱、mem
選擇最優(yōu)的方案進行執(zhí)行SQL
提供查詢緩存（默認(rèn)是關(guān)閉的），會使用redis晶疼、tair替代查詢緩存功能
提供日志記錄，默認(rèn)是關(guān)閉的

3>存儲引擎層（簡單介紹）

相當(dāng)于文件系統(tǒng)冒晰，將數(shù)據(jù)取出，在交給SQL層結(jié)構(gòu)化成表壶运，返回給用戶

4、MYSQL的邏輯結(jié)構(gòu)（重要）

1>庫（DATABASE,SCHEMA）

相當(dāng)于Linux的目錄
由庫名字和庫屬性組成

2>表（TABLE,SEGMENT）

相當(dāng)于Linux下的目錄
由表名蒋情、表屬性、表的數(shù)據(jù)行（又稱為row棵癣、記錄）、列（又稱為字段）組成

3>用戶（USER）

由用戶名狈谊、白名單（主機范圍）組成

4>OTHER（省略）

5、MYSQL的物理結(jié)構(gòu)（重要）

1>庫（DATABASE,SCHEMA）

物理存儲就是Linux中的目錄

2>表（TABLE,SEGMENT）

通過不同的引擎分為：

MyISAM（一種引擎）的表：
    *.MYI：索引相關(guān)信息
    *.MYD：存儲數(shù)據(jù)行
    *.frm：列的信息
InnoDB(默認(rèn)的存儲引擎)的表：
    *.frm：列的信息
    *.ibd：數(shù)據(jù)行和索引（IOT）

5.1河劝、InnoDB表底層存儲結(jié)構(gòu)引入（擴展）（重要）

段（SEGMENT）:一個非分區(qū)表就是一個段
區(qū)（EXTENT）：連續(xù)的64個page，固定大小1M
頁（PAGE）：MySQL最小的IO單元牌里，默認(rèn)16KB

6、MySQL基礎(chǔ)管理

6.1 用戶

1>用戶的作用

登錄MySQL
管理MySQL的邏輯對象

2>用戶的定義

用戶名
白名單（主機列表）牡辽，即可被允許的主機IP

用戶定義的幾種方式，如定義wordpress

wordpress@'10.0.0.1'
wordpress@'localhost'
wordpress@'10.0.0.%'
wordpress@'10.0.0.5%'
wordpress@'10.0.0.0/255.255.254.0'
wordpress@'oldguo.com'
wordpress@'db01'
wordpress@'%'

3>用戶的操作管理

（1）創(chuàng)建用戶

mysql> create user oldgril@'10.0.0.%';   #創(chuàng)建用戶态辛，不設(shè)置密碼
Query OK, 0 rows affected (0.00 sec)

mysql> create user old@'10.0.0.%' identified by '123456';   #創(chuàng)建用戶的同時設(shè)置密碼
Query OK, 0 rows affected (0.00 sec)

（2）查詢用戶

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| oldgril       | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

（3）修改用戶密碼

mysql> alter user oldgril@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

（4）刪除用戶

mysql> drop user old@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

注意：
(1)8.0以前,以上命令可以忽略,grant可以自動創(chuàng)建用戶并授權(quán).
(2)8.0以后必須先建用戶后授權(quán),grant只做授權(quán)功能.

6.2 權(quán)限

1>權(quán)限作用

開啟用戶的管理對象的能力

2>權(quán)限定義（8.0以前）

按命令進行定義挺尿，例如select、update票髓、insert铣耘、drop、create……

3>授權(quán)范圍

*.*：全庫級別蜗细，一般是管理員
wordpress.*：單庫級別怒详，一般是應(yīng)用或開發(fā)用戶（常用）
wordpress.t1：單表級別，很少使用

4>授權(quán)管理命令

grant  all  on *.*  to oldguo@'10.0.0.%' identified by '123';
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 : 普通管理員
    ALL+ with grant option 超級管理員 .

5>授權(quán)管理生產(chǎn)需求

(1) 授權(quán)一個管理員用戶admin ,密碼admin,能夠通過10網(wǎng)段管理數(shù)據(jù)庫

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

(2)開放一個wordpress應(yīng)用的用戶昆烁，密碼123缎岗，nginx服務(wù)器使172.16.1.%網(wǎng)段

分析：應(yīng)用用戶需要什么權(quán)限静尼？select,insert,update,delete

mysql> grant select,insert,update,delete on *.* to wordpress@'172.16.1.%' identified by '123'

(3) 中小公司,開發(fā)人員leader,需要開發(fā)用戶dev通過10網(wǎng)段對wordpress進行開發(fā)和管理

分析：用戶需要什么權(quán)限传泊？
這里給了最大權(quán)限，但到底什么權(quán)限眷细，根據(jù)實際情況來定

mysql> grant SELECT,INSERT, UPDATE, DELETE,CREATE,ALTER,CREATE VIEW,SHOW VIEW,CREATE ROUTINE, ALTER ROUTINE,EVENT, TRIGGER on  wordpress.* to dev@'10.0.0.%' identified by '123';

6>權(quán)限回收

（1）查看用戶有哪些權(quán)限？

mysql> show grants for wordpress@'172.16.1.%';
+-----------------------------------------------------------------------------------+
| Grants for wordpress@172.16.1.%                                                   |
+-----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpress'@'172.16.1.%'                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `wordpress`.* TO 'wordpress'@'172.16.1.%' |
+-----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

（2）回收用戶的delete,update權(quán)限

mysql> revoke delete,update on wordpress.* from wordpress@'172.16.1.%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for wordpress@'172.16.1.%';
+-------------------------------------------------------------------+
| Grants for wordpress@172.16.1.%                                   |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpress'@'172.16.1.%'                    |
| GRANT SELECT, INSERT ON `wordpress`.* TO 'wordpress'@'172.16.1.%' |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

7>本地管理員用戶密碼忘記（面試題）

（1）關(guān)閉數(shù)據(jù)庫

/etc/init.d/mysqld stop

（2）將數(shù)據(jù)庫啟動到無驗證模式

mysqld_safe --skip-grant-tables --skip-networking &
參數(shù)說明：
    --skip-grant-tables：跳過授權(quán)表
    --skip-networking：跳過TCP/IP

（3）修改密碼

mysql>flush privileges;
mysql>alter user root@'localhost' identified by '123';

（4）重啟數(shù)據(jù)庫為正常模式

/etc/init.d/mysqld restart

（5）測試新密碼

[root@db01 ~]# mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

6.3 連接管理

1>MySQL自帶命令

（1）mysql參數(shù)

-u：指定用戶名
-p：指定連接mysql服務(wù)器的密碼
-h：指定要連接的ip地址
-P：指定端口號普舆，與-h配合使用校读，可以省略沼侣，但不能單獨使用
-S：可以指定socket文件地熄，實現(xiàn)本地登錄
    如：mysql -uroot -p123 -S  /tmp/mysql.sock
-e：免交互式執(zhí)行mysql里的命令
    如：mysql -uroot -p123 -e 'show databases'
-V：查看mysql命令的版本信息
<：恢復(fù)數(shù)據(jù)使用
    如：mysql -uroot -p123 <world.sql

（2）socket連接：

確認(rèn)socket文件位置：socket=/tmp/mysql.sock
mysql -uroot -p123 -S  /tmp/mysql.sock

注意：本地登錄的用戶，需要提前授權(quán)l(xiāng)ocalhost相關(guān)用戶

（3）TCP/IP連接：

mysql -uoldguo -p  -h10.0.0.51 -P3306

注意：
優(yōu)先級：socket連接和TCP/IP連接都存在時端考，優(yōu)先走TCP/IP連接

2>客戶端工具連接

SQLyog工具：https://sqlyog.en.softonic.com/
navicat for mysql工具：https://www.navicat.com.cn/products/navicat-for-mysql

6.4 MySQL啟動和關(guān)閉方式

1> sys-v

/etc/init.d/mysqld  [start|stop|restart]
service mysqld  [start|stop|restart]

前提是需要把軟件存放目錄下的mysql/support-files/mysql.server 拷貝到/etc/init.d/下

2> systemd

systemctl [start|stop|restart] mysqld

3> mysqld &

只提供了啟動的方法

4> mysqld_safe &

一般用于調(diào)試
    如：mysqld_safe --skip-grant-tables --skip-networking &
注：只提供了啟動的方法

注：萬能的關(guān)閉方法，以上都適用：mysqladmin -uroot -p123456 shutdown

6.5 MySQL初始化配置

1> 提供的方法

預(yù)編譯
初始化配置文件
命令行
注意：優(yōu)先級（命令行---->初始化配置文件---->預(yù)編譯）

2>初始化配置文件的默認(rèn)讀取順序

[root@db01 ~]# mysqld  --help --verbose |grep my.cnf
/etc/my.cnf--->/etc/mysql/my.cnf --->/usr/local/mysql/etc/my.cnf---> ~/.my.cnf

注意：一旦使用--defaults-file扶供，以上的默認(rèn)配置就不會生效了
[root@db01 ~]# mysqld_safe --defaults-file=/opt/oldguo.cnf &

3> 初始化配置文件作用

數(shù)據(jù)庫的啟動
客戶端的登錄

4> 初始化配置文件格式

（1）書寫格式

[標(biāo)簽1]
xxxx=yyyy
[標(biāo)簽2]
xxxx=yyyy
[標(biāo)簽3]
xxxx=yyyy

（2）標(biāo)簽

服務(wù)端
    常用標(biāo)簽
        [mysqld]
        [mysqld_safe]
客戶端
    [mysql]
    [mysqldump]
    [mysqladmin]
    [client]：代表所有的客戶端標(biāo)簽

5> 配置文件模板

[mysqld]
user=mysql
server_id=6   
port=3306
basedir=/application/mysql
datadir=/data/3306/data
socket=/data/3306/mysql.sock
[mysql]
socket=/data/3306/mysql.sock
prompt=wenjuan[\\d]>

未完……