0）環(huán)境
# MySQL版本
mysql-5.7.20
# IP地址
10.0.0.51 172.16.1.51
1）體系結構
CS模型介紹(客戶端/服務端)
#TCP/IP方式（遠程）
mysql -uroot -p123 -h 10.0.0.51 -P3306
#Socket方式（套接字署隘、本地登錄、localhost）
mysql -uroot -p123 -S /tmp/mysql.sock
實例介紹
實例=mysqld后臺守護進程+Master Thread+干活的Thread+預分配的內存
公司=老板+經(jīng)理+員工+辦公室
mysqld程序運行原理
層次結構
2）一條SQL語句的執(zhí)行過程
-
命令
#查看MySQL用戶信息
>select user,host from mysql.user;
層次介紹
連接層
連接層
1）提供連接協(xié)議：TCP/IP亦歉、Socket
2）提供驗證：用戶肴楷、密碼砂客、IP鞠值、Socket
3）提供專業(yè)連接線程：接收用戶SQL彤恶，返回結果
#查看連接線程語句
> show processlist;
SQL層（重點）
SQL層
1）接收上層傳送SQL語句
2）語法驗證模塊：驗證SQL語句語法
3）權限檢查（用戶對庫表的權限）
4）語義檢查（判斷語句類型）
DDL：數(shù)據(jù)定義語言
DCL：數(shù)據(jù)控制語言
DML：數(shù)據(jù)操作語言
DQL：數(shù)據(jù)查詢語言 #MySQL查詢語句類型
5）解析器：進行SQL的預處理声离，產(chǎn)生執(zhí)行計劃
6）優(yōu)化器：根據(jù)解析器執(zhí)行計劃進行判斷芒炼，選擇最優(yōu)的執(zhí)行計劃
代價模型：根據(jù)資源耗損進行評估 (CPU IO 內存 )
7）執(zhí)行器：根據(jù)最優(yōu)執(zhí)行計劃术徊，執(zhí)行SQL語句子寓，產(chǎn)生執(zhí)行結果
執(zhí)行結果：數(shù)據(jù)在磁盤的XXX位置
8）提供查詢緩存别瞭，提高效率。（默認關閉嗤瞎，可使用"Redis"或"Tair"替代查詢緩存）
9）提供日志記錄（日志管理）：Binlog(默認關閉)
存儲引擎層（類似于Linux文件系統(tǒng)）
負責根據(jù)SQL語句執(zhí)行結果虹菲，調取磁盤數(shù)據(jù)。(16進制)
將16進制的磁盤數(shù)據(jù)霎褐，交由SQL層轉化為表冻璃，由連接層的專用線程返回給用戶娘纷。
MySQL出現(xiàn)故障或性能問題80%以上是人為的。
——《oldguo》
3）邏輯結構
數(shù)據(jù)庫
存放數(shù)據(jù)表
庫名嬉探、庫權限、庫數(shù)據(jù)類型
數(shù)據(jù)表（二維表）
列：字段
行：記錄
MySQL為了使存入的數(shù)據(jù)準確胎围、規(guī)范、有意義福荸，增強了傳統(tǒng)二維表的功能。
表屬性：權限台夺、字符集、存儲引擎
列屬性：數(shù)據(jù)類型滚朵、約束、其他（默認值、自增長吞杭、注釋）
物理存儲結構引入
庫的物理存儲結構
用文件系統(tǒng)的目錄來存儲
位置：/data/mysql/data
表的物理存儲結構
MyISAM（默認存儲引擎）表
user.frm：列的相關信息
user.MYD：數(shù)據(jù)行
user.MYI:索引信息
InnoDB（默認存儲引擎）表
time_zone.frm ：存儲列相關信息
time_zone.ibd：數(shù)據(jù)行+索引
表的頁、區(qū)童擎、段（了解）
- 頁：最小的存儲單元 16KB
- 區(qū)：1個或多個連續(xù)的頁
- 段：1個或多個連續(xù)的區(qū)，一個表就是一個段
4）基礎管理
用戶、權限管理
- 用戶
作用：登陸 管理數(shù)據(jù)庫 - 定義
用戶@'白名單'
inanhan@'localhost'
- 白名單：允許登陸的IP地址段
- 支持方式
inanhan@'%' #所有地址
inanhan@'10.0.0.%' #10.0.0.0/24網(wǎng)段
inanhan@'localhost' #本地登陸
inanhan@'10.0.0.5%' #10.0.0.50~10.0.0.59
inanhan@'10.0.0.0/255.255.254.0' #子網(wǎng)劃分：10.0.0.0/23
基本用戶管理操作
- 增
#'創(chuàng)建用戶
>create user inanhan@'10.0.0.%' identified by '123';
- 查
#查MySQL用戶名、登陸白名單、密碼
>select user,host,authentication_string from mysql.user;
#查詢表結構
>desc mysql.user;
- 改
#'更改用戶密碼
>alter user inanhan@'10.0.0.%' identified by '456';
- 刪
#'刪除用戶
>drop user inanhan@'10.0.0.%';
權限
- 權限介紹
SELECT #允許從表中查看數(shù)據(jù)
INSERT #允許在表里插入數(shù)據(jù)
UPDATE #允許修改表中的數(shù)據(jù)的權限
DELETE #刪除行數(shù)據(jù)
CREATE #允許創(chuàng)建新的數(shù)據(jù)庫和表的權限
DROP #刪除數(shù)據(jù)庫與表
RELOAD #允許刷新權限(FLUSH命令)
SHUTDOWN #允許關閉數(shù)據(jù)庫實例
PROCESS #允許查看數(shù)據(jù)庫進程
FILE #允許用戶在MySQL進行讀寫文件磁盤操作
REFERENCES #允許創(chuàng)建外鍵（5.7.6版本之后引入）
INDEX #允許創(chuàng)建和刪除索引
ALTER #允許修改表結構的權限羽德，但必須要求有CREATE和INSERT權限配合
SHOW DATABASES #查看所有的數(shù)據(jù)庫名
SUPER #允許執(zhí)行一系列數(shù)據(jù)庫管理命令章蚣，包括kill強制關閉某個連接
CREATE TEMPORARY TABLES #允許創(chuàng)建臨時表的權限
LOCK TABLES #允許對擁有select權限的表進行鎖定，以防止其他鏈接對此表讀或寫
EXECUTE #允許執(zhí)行存儲過程和函數(shù)的權限
REPLICATION SLAVE #允許Slave主機通過此用戶連接Master以便建立主從復制關系
REPLICATION CLIENT #允許執(zhí)行show master status,show slave status,show binary logs命令
CREATE VIEW #查看視圖創(chuàng)建的語句：mysqladmin processlist, show engine
SHOW VIEW #代表通過執(zhí)行show create view命令查看視圖創(chuàng)建的語句
CREATE ROUTINE #允許創(chuàng)建procedure,function
ALTER ROUTINE #允許修改或者刪除存儲過程、函數(shù)的權限
CREATE USER #允許創(chuàng)建用戶
EVENT #允許查詢蓬豁，創(chuàng)建，修改，刪除MySQL事件
TRIGGER #允許創(chuàng)建质礼，刪除，執(zhí)行，顯示觸發(fā)器的權限
CREATE TABLESPACE #允許創(chuàng)建表空間
-------------------------------------------------------------------------------------
ALL #以上所有權限刽宪，普通管理員權限
with grant option #超級管理員功能毁欣，為其他用戶授權
應用用戶權限 #SELECT饭耳，INSERT，UPDATE新蟆，DELETE
- 授權對象
*.* --->chmod 755 -R / ---->針對管理員
inanhan.* --->chmod 755 -R /inanhan ---->應用用戶
inanhan.t1 --->chmod 755 -R /inanhan/t1
基本權限管理操作
- 授權命令寫法
grant 權限 on 對象 to 用戶 identified '密碼'饶囚；
- 需求01：Windows系統(tǒng)使用Navicat登錄到Linux中的MySQL莫鸭，管理員用戶卿拴。
#'授權命令
> grant all on *.* to root@'10.0.0.%' identified by '123';
- 需求02：創(chuàng)建一個應用用戶 app 用戶文狱，能從Windows上登錄MySQL壕曼，并且可以操作app庫摹蘑。
#'授權命令
> grant select ,update,insert,delete on app.* to app@'10.0.0.%' identified by '123';
- 開發(fā)人員用戶授權流程
權限
操作對象（庫與表）
登陸IP地址
密碼要求
- 8.0版本grant命令新特性
#用戶創(chuàng)建與授權分開
#不支持自動創(chuàng)建用戶與更改密碼
- 查看授權信息
>show grants for app@'10.0.0.%'; #查看app用戶授權信息
- 回收權限
>revoke delete on app.* from app@'10.0.0.%'; #回收app用戶delete權限
5）連接管理
登陸方式
- 登陸方式01：TCP/IP
#連接10.0.0.51數(shù)據(jù)庫（密碼隱藏輸入）
mysql -uroot -p -h 10.0.0.51 -P3306
Enter password:
- 登陸方式02：Socket
#通過本地socket登陸MySQL
mysql -uroot -p -S /tmp/mysql.sock
Enter password:
#查看登陸socket路徑
> select @@socket;
+-----------------+
| @@socket |
+-----------------+
| /tmp/mysql.sock |
mysql命令常用參數(shù)
-u #用戶
-p #密碼
-h #IP
-P #端口
-S #socket文件路徑
-e #免交互執(zhí)行命令
< #導入SQL腳本
#免交互查看MySQL數(shù)據(jù)庫用戶信息
mysql -uroot -p -e "select user,host from mysql.user;"
Enter password:
#導入SQL語句到MySQL數(shù)據(jù)庫
mysql -uroot -p <world.sql
Enter password:
多種啟動方式介紹
啟動方式
-
提示
以上多種方式泵三，都可以單獨啟動MySQL服務
mysqld_safe和mysqld一般是在臨時維護時使用。
從CentOS 7系統(tǒng)開始，支持systemd直接調用mysqld的方式進行啟動數(shù)據(jù)庫瞳抓。
6）初始化配置
初始化配置文件
#查看初始化配置文件
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
- 默認情況下翠桦，MySQL啟動時丛晌，會依次讀取以上配置文件蜕窿，如果有重復選項，會以最后一個文件設置的為準荧恍。
- 若加入--defaults-file=xxxx時，則直接讀取指定文件。
初始化配置書寫格式
[標簽]
配置項=XXX
...
#標簽類型：服務端虹蓄、客戶端
服務端
[mysqld]
[mysqld_safe]
客戶端
[mysql]
[mysqldump]
[client]
#實例
[mysqld]
user=mysql
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/error.log
[mysql]
socket=/tmp/mysql.sock
prompt=Master [\\d]>
6）多實例的應用
準備環(huán)境
#創(chuàng)建目錄
mkdir -p /data/330{7,8,9}/data
#準備配置文件
#-----------------------實例01（3307）
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
#-----------------------實例02（3308）
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
#-----------------------實例03（3309）
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
初始化配置
#更改初始數(shù)據(jù)庫配置文件名
mv /etc/my.cnf /etc/my.cnf.bak
#-------------------------------實例01
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/data/mysql
#-------------------------------實例02
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/data/mysql
#-------------------------------實例03
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/data/mysql
systemd管理多實例
#進入system目錄
cd /etc/systemd/system
=====================================
#拷貝服務啟動腳本
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service
=====================================
#更改配置文件倒數(shù)第二行內容
#---------------------------實例01
vim mysqld3307.service
ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
#---------------------------實例02
vim mysqld3308.service
ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
#---------------------------實例03
vim mysqld3309.service
ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
授權
#授權MySQL文件目錄
chown -R mysql.mysql /data/*
啟動實例
# 如有必要罪佳，可設置開機自啟動
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service
驗證多實例
- 端口檢測
#檢測端口（3306配置文件已改名酌毡，所以未啟動）
netstat -lnp|grep 330*
tcp6 0 0 :::3307 :::* LISTEN 2932/mysqld
tcp6 0 0 :::3308 :::* LISTEN 2939/mysqld
tcp6 0 0 :::3309 :::* LISTEN 2946/mysqld
檢測SERVER ID
#免交互查看SERVER ID
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'"
登陸數(shù)據(jù)庫
mysql -S /data/3307/mysql.sock #3307
mysql -S /data/3308/mysql.sock #3308
mysql -S /data/3309/mysql.sock #3309
0.0)數(shù)據(jù)庫忘記root密碼
#關閉數(shù)據(jù)庫
systemctl stop mysqld
#跳過授權啟動
mysql_safe --skip-grant-tables --skip-networking &
--skip-grant-tables : 連接層關閉驗證模塊，所有驗證表不加載勋眯。
--skip-networking ：連接層關閉TCP/IP協(xié)議婴梧，禁止遠程訪問。
#無密碼登陸
mysql -uroot -p 回車
> flush privileges;
> alter user root@'localhost' identified by '456';
#殺掉MySQL進程
pkill mysqld
#啟動MySQL
systemctl start mysqld
