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

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

image

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

1.2 實例介紹

實例=mysqld后臺守護進(jìn)程+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í)行前,進(jìn)行預(yù)處理汁展，生成解析樹(執(zhí)行計劃),說白了就是生成多種執(zhí)行方案.（6）優(yōu)化器：根據(jù)解析器得出的多種執(zhí)行計劃，進(jìn)行判斷糙置，選擇最優(yōu)的執(zhí)行計劃? ? ? ? 代價模型：資源（CPU IO MEM）的耗損評估性能好壞（7）執(zhí)行器：根據(jù)最優(yōu)執(zhí)行計劃云茸，執(zhí)行SQL語句是目，產(chǎn)生執(zhí)行結(jié)果執(zhí)行結(jié)果：在磁盤的xxxx位置上（8）提供查詢緩存（默認(rèn)是沒開啟的）谤饭，會使用redis tair替代查詢緩存功能（9）提供日志記錄（日志管理章節(jié)）：binlog，默認(rèn)是沒開啟的懊纳。

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

負(fù)責(zé)根據(jù)SQL層執(zhí)行的結(jié)果揉抵，從磁盤上拿數(shù)據(jù)。將16進(jìn)制的磁盤數(shù)據(jù)嗤疯，交由SQL結(jié)構(gòu)化化成表冤今，連接層的專用線程返回給用戶。

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

need-to-insert-img

image.png

以上圖片由五行哥提供

1.4.1 庫：

庫名茂缚，庫屬性

1.4.2 表

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

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

need-to-insert-img

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.MYIInnoDB(默認(rèn)的存儲引擎)的表：-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.ibdtime_zone.frm：存儲列相關(guān)信息time_zone.ibd：數(shù)據(jù)行+索引

1.5.3 表的段戏罢、區(qū)、頁（16k）（了解）

頁：最小的存儲單元脚囊，默認(rèn)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_stringmysql> 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 TABLESPACEALL:以上所有權(quán)限衬以，一般是普通管理員擁有的withgrant 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>grantselect,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.*fromapp@'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? ? ? ? ? ? ? ? ? 密碼-hIP-P端口-Ssocket文件-e? ? ? ? ? ? ? ? ? 免交互執(zhí)行命令<導(dǎo)入SQL腳本[root@db01~]# mysql -uroot -p -h 10.0.0.51 -P3306Enterpassword:mysql>select@@socket;+-----------------+|@@socket|+-----------------+|/tmp/mysql.sock|[root@db01~]# mysql -uroot -p -S /tmp/mysql.sockEnterpassword:[root@db01~]# mysql -uroot -p -e "select user,host from mysql.user;"Enterpassword:+---------------+-----------+|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.sqlEnterpassword:[root@db01~]#

2.3 多種啟動方式介紹

image

提示：

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

2.4 初始化配置

2.4.0 作用

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

2.4.1 初始化配置的方法

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

2.4.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注:默認(rèn)情況下，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=mysqlbasedir=/app/mysqldatadir=/data/mysqlsocket=/tmp/mysql.sockserver_id=6port=3306log_error=/data/mysql/mysql.log[mysql]socket=/tmp/mysql.sockprompt=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/mysqldatadir=/data/3307/datasocket=/data/3307/mysql.socklog_error=/data/3307/mysql.logport=3307server_id=7log_bin=/data/3307/mysql-binEOFcat>/data/3308/my.cnf<<EOF[mysqld]basedir=/app/mysqldatadir=/data/3308/datasocket=/data/3308/mysql.socklog_error=/data/3308/mysql.logport=3308server_id=8log_bin=/data/3308/mysql-binEOFcat>/data/3309/my.cnf<<EOF[mysqld]basedir=/app/mysqldatadir=/data/3309/datasocket=/data/3309/mysql.socklog_error=/data/3309/mysql.logport=3309server_id=9log_bin=/data/3309/mysql-binEOF

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

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

2.5.4 systemd管理多實例

cd/etc/systemd/systemcp mysqld.service mysqld3307.servicecp mysqld.service mysqld3308.servicecp mysqld.service mysqld3309.servicevim mysqld3307.serviceExecStart=/app/mysql/bin/mysqld--defaults-file=/data/3307/my.cnfvim mysqld3308.serviceExecStart=/app/mysql/bin/mysqld--defaults-file=/data/3308/my.cnfvim mysqld3309.serviceExecStart=/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.servicesystemctl start mysqld3308.servicesystemctl start mysqld3309.service

2.5.7 驗證多實例

netstat-lnp|grep330mysql-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"

作者：MySQL_oldguo

鏈接：http://www.reibang.com/p/e872bc12f583

來源：簡書

著作權(quán)歸作者所有轩猩。商業(yè)轉(zhuǎn)載請聯(lián)系作者獲得授權(quán)卷扮，非商業(yè)轉(zhuǎn)載請注明出處。