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

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

TCP/IP方式（遠(yuǎn)程埂淮、本地）：

mysql -uroot -poldboy123 -h 10.0.0.51 -P3306

Socket方式(僅本地)：

mysql -uroot -poldboy123 -S /tmp/mysql.sock

1.2 實(shí)例介紹

實(shí)例=mysqld后臺(tái)守護(hù)進(jìn)程+Master Thread +干活的Thread+預(yù)分配的內(nèi)存

公司=老板+經(jīng)理+員工+辦公室

1.3 mysqld程序運(yùn)行原理

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

mysql出現(xiàn)故障或性能問題80%以上是人為的捕传。

? ????????????????????????????????????????????????????????????????????? ---oldguo

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

1.3.2.1 連接層

（1）提供連接協(xié)議：TCP/IP 歉眷、SOCKET

（2）提供驗(yàn)證：用戶岁疼、密碼棒搜，IP仆嗦，SOCKET

（3）提供專用連接線程：接收用戶SQL枝恋，返回結(jié)果

通過以下語句可以查看到連接線程基本情況

mysql> show processlist;

1.3.2.2 SQL層 （重點(diǎn)）

（1）接收上層傳送的SQL語句

（2）語法驗(yàn)證模塊：驗(yàn)證語句語法

（3）語義檢查：判斷SQL語句的類型

DDL ：數(shù)據(jù)定義語言

DCL ：數(shù)據(jù)控制語言

DML ：數(shù)據(jù)操作語言

DQL：數(shù)據(jù)查詢語言

（4）權(quán)限檢查：用戶對(duì)庫表有沒有權(quán)限

（5）解析器：進(jìn)行SQL的預(yù)處理每币，產(chǎn)生執(zhí)行計(jì)劃

（6）優(yōu)化器：根據(jù)解析器得出的多種執(zhí)行計(jì)劃斑粱，進(jìn)行判斷，選擇最優(yōu)的執(zhí)行計(jì)劃

? ? ? ? 代價(jià)模型：資源（CPU IO MEM）的耗損評(píng)估性能好壞

（7）執(zhí)行器：根據(jù)最優(yōu)執(zhí)行計(jì)劃脯爪，執(zhí)行SQL語句则北，產(chǎn)生執(zhí)行結(jié)果

執(zhí)行結(jié)果：在磁盤的xxxx位置上

（8）提供查詢緩存（默認(rèn)是沒開啟的），會(huì)使用redis tair替代查詢緩存功能

（9）提供日志記錄（日志管理章節(jié)）：binlog痕慢，默認(rèn)是沒開啟的尚揣。

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

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

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

1.4.1 庫：庫名方篮，庫權(quán)限名秀，庫數(shù)據(jù)類型等

1.4.2 表（二維表）

列：字段

行：記錄

MySQL為了將來數(shù)據(jù)的準(zhǔn)確、規(guī)范藕溅、有意義匕得，對(duì)于傳統(tǒng)認(rèn)知的二維表添加了更多的功能

表屬性：權(quán)限，存儲(chǔ)引擎巾表，字符集

列屬性：數(shù)據(jù)類型汁掠、約束、其他屬性（默認(rèn)值集币，自增長(zhǎng)考阱，注釋）

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

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

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

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

MyISAM（一種引擎）的表：

user.frm：列的相關(guān)信息

user.MYD：數(shù)據(jù)行

user.MYI：索引信息

==========================

InnoDB(默認(rèn)的存儲(chǔ)引擎)的表：

?time_zone.frm：存儲(chǔ)列相關(guān)信息

?time_zone.ibd：數(shù)據(jù)行+索引

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

頁：最小的存儲(chǔ)單元

區(qū)：1個(gè)或多個(gè)連續(xù)的頁

段：一個(gè)表就是一個(gè)段乞榨，包含一個(gè)或多個(gè)區(qū)

==================================================

2. 基礎(chǔ)管理

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

2.1.1 用戶

作用：登錄当娱，管理數(shù)據(jù)庫對(duì)象

定義：用戶@'白名單'

白名單：允許哪些IP能夠登錄數(shù)據(jù)庫

支持的方式：

oldboy@'%'? ? ? ? ? ? ? ? ? ? ?：所有地址

oldboy@'10.0.0.%'? ? ? ? ? ?:10.0.0.0/24 網(wǎng)段的地址

oldboy@'localhost'? ? ? ? ? ?:本地主機(jī)

oldboy@'10.0.0.5%'? ? ? ? ?：10.0.0.50~10.0.0.59

oldboy@'10.0.0.0/255.255.254.0'? ：子網(wǎng)掩碼不是24位的

管理操作：

增：

mysql> create user oldboy@'10.0.0.%' identified by '123';

查：

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)限

介紹

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：超級(jí)管理員才具備的，給別的用戶授權(quán)的功能

應(yīng)用用戶：SELECT,INSERT, UPDATE, DELETE

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

grant? ? ?權(quán)限? ? ?on? ? ? 對(duì)象? ? ?to? ? ?用戶? ? ? identified by? ? ? '密碼'趾访；

需求例子：windows機(jī)器的navicat登錄到linux中的MySQL态秧，管理員用戶。

grant? ?all? ? on? ? ? 對(duì)象? ? ?to? ? ?用戶? ? ? identified by? ? ? '密碼'扼鞋；

?對(duì)象：

*.*? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?--->chmod 755 -R /? ? ? ? ? ? ? ? ? ----》針對(duì)管理員

oldboy.*? ? ? ? ? ? ? ? ? ? ? ? ?chmod 755 -R /oldboy? ? ? ? ? ? ----->應(yīng)用用戶

oldboy.t1? ? ? ? ? ? ? ? ? ? ? ? ?chmod 755 -R? /oldboy/t1

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

需求：創(chuàng)建一個(gè)應(yīng)用用戶app用戶申鱼，能從windows上登錄mysql，并能操作app庫云头。

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

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

1.權(quán)限

2.對(duì)誰操作

3.你從哪來

4.密碼要求

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

建用戶和授權(quán)分開了

grant 不再支持自動(dòng)創(chuàng)建用戶了捐友，不支持改密碼

授權(quán)之前，必須要提前創(chuàng)建用戶溃槐。

查看授權(quán)：

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

回收權(quán)限：

revoke? delete on app.*? from app@'10.0.0.%'匣砖；

2.2 連接管理

2.2.1 自帶客戶端命令

mysql? 常用參數(shù)：

-u? ? ? ? ? ? ? ? ? ?用戶

-p? ? ? ? ? ? ? ? ? ?密碼

-h? ? ? ? ? ? ? ? ? ?IP

-P? ? ? ? ? ? ? ? ? 端口

-S? ? ? ? ? ? ? ? ? socket文件

-e? ? ? ? ? ? ? ? ? ?免交互執(zhí)行命令

<? ? ? ? ? ? ? ? ? ? 導(dǎo)入SQL腳本

登錄方法一——遠(yuǎn)程登錄：

[root@db01 ~]# mysql -uroot -p -h 10.0.0.51 -P3306

Enter password:

登錄方法一——遠(yuǎn)程登錄：

mysql> select @@socket;（查看socket位置）

[root@db01 ~]# mysql -uroot -p -S /tmp/mysql.sock

? ? Enter password:

[root@db01 ~]# mysql -uroot -p -e "select user,host from mysql.user;"（免交護(hù)查詢）

? ? Enter password:

[root@db01 ~]# mysql -uroot -p <world.sql（導(dǎo)入）

Enter password:

2.3 多種啟動(dòng)方式介紹

提示：

以上多種方式，都可以單獨(dú)啟動(dòng)MySQL服務(wù)

mysqld_safe和mysqld一般是在臨時(shí)維護(hù)時(shí)使用昏滴。

另外猴鲫，從Centos 7系統(tǒng)開始，支持systemd直接調(diào)用mysqld的方式進(jìn)行啟動(dòng)數(shù)據(jù)庫

2.4 初始化配置

2.4.1 初始化配置的方法

預(yù)編譯

配置文件********

命令行

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

## 初始化配置文件讀取順序

/etc/my.cnf? ?/etc/mysql/my.cnf? ?/usr/local/mysql/etc/my.cnf? ? ~/.my.cnf?

默認(rèn)情況下谣殊，MySQL啟動(dòng)時(shí)拂共，會(huì)依次讀取以上配置文件，如果有重復(fù)選項(xiàng)姻几，會(huì)以最后一個(gè)文件設(shè)置的為準(zhǔn)宜狐。

但是势告，如果啟動(dòng)時(shí)加入了--defaults-file=xxxx時(shí)，以上的所有文件都不會(huì)讀取

## 配置文件的書寫方式

[標(biāo)簽]

配置項(xiàng)=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=/data/mysql? ? ? ? ? ? ? ? ? # 程序目錄

datadir=/data/mysql/data? ? ? ? ? ?# 數(shù)據(jù)目錄

socket=/tmp/mysql.sock? ? ? ? ? ? # 本地連接指定的socket

server_id=6

port=3306? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #? 指定端口號(hào)

log_error=/data/mysql/mysql.log? #指定錯(cuò)誤日志存放路徑

[mysql]

socket=/tmp/mysql.sock? ? ? ? ? ? ? #默認(rèn)要與服務(wù)器端的socket文件路徑一直

prompt=Master [\\d]>? ? ? ? ? ? ? ? ? ?#顯示路徑（與linux命令行顯示差不多）

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

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

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

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

----------------------------

vim /data/3307/my.cnf

[mysqld]

basedir=/data/mysql

datadir=/data/3307/data

socket=/data/3307/mysql.sock

log_error=/data/3307/mysql.log

port=3307

server_id=7

---------------------------

vim /data/3308/my.cnf

[mysqld]

basedir=/data/mysql

datadir=/data/3308/data

socket=/data/3308/mysql.sock

log_error=/data/3308/mysql.log

port=3308

server_id=8

---------------------------

vim /data/3309/my.cnf

[mysqld]

basedir=/data/mysql

datadir=/data/3309/data

socket=/data/3309/mysql.sock

log_error=/data/3309/mysql.log

port=3309

server_id=9

---------------------------

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

mv /etc/my.cnf /etc/my.cnf.bak

mysqld --initialize-insecure? --user=mysql --datadir=/data/3307/data --basedir=/data/mysql

mysqld --initialize-insecure? --user=mysql --datadir=/data/3308/data --basedir=/data/mysql

mysqld --initialize-insecure? --user=mysql --datadir=/data/3309/data --basedir=/data/mysql

2.5.4 systemd管理多實(shí)例

cd /etc/systemd/system

cp mysqld.service mysqld3307.service

cp mysqld.service mysqld3308.service

cp mysqld.service mysqld3309.service

vim mysqld3307.service

ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf

vim mysqld3308.service

ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf

vim mysqld3309.service

ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf

2.5.5 授權(quán)

chown -R mysql.mysql /data/*

2.5.6 啟動(dòng)

systemctl start mysqld3307.service

systemctl start mysqld3308.service

systemctl start mysqld3309.service

2.5.7 驗(yàn)證多實(shí)例

netstat -lnp|grep 330

mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"

mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"

mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"