0）環(huán)境
# MySQL版本
mysql-5.7.20
# IP地址
10.0.0.51 172.16.1.51
1）體系結(jié)構(gòu)
CS模型介紹(客戶端/服務(wù)端)
#TCP/IP方式（遠(yuǎn)程）
mysql -uroot -p123 -h 10.0.0.51 -P3306
#Socket方式（套接字、本地登錄、localhost）
mysql -uroot -p123 -S /tmp/mysql.sock
實(shí)例介紹
實(shí)例=mysqld后臺(tái)守護(hù)進(jìn)程+Master Thread+干活的Thread+預(yù)分配的內(nèi)存
公司=老板+經(jīng)理+員工+辦公室
mysqld程序運(yùn)行原理
層次結(jié)構(gòu)
2）一條SQL語句的執(zhí)行過程
-
命令
#查看MySQL用戶信息
>select user,host from mysql.user;
層次介紹
連接層
連接層
1）提供連接協(xié)議：TCP/IP、Socket
2）提供驗(yàn)證：用戶、密碼、IP、Socket
3）提供專業(yè)連接線程：接收用戶SQL，返回結(jié)果
#查看連接線程語句
> show processlist;
SQL層（重點(diǎn)）
SQL層
1）接收上層傳送SQL語句
2）語法驗(yàn)證模塊：驗(yàn)證SQL語句語法
3）權(quán)限檢查（用戶對(duì)庫表的權(quán)限）
4）語義檢查（判斷語句類型）
DDL：數(shù)據(jù)定義語言
DCL：數(shù)據(jù)控制語言
DML：數(shù)據(jù)操作語言
DQL：數(shù)據(jù)查詢語言 #MySQL查詢語句類型
5）解析器：進(jìn)行SQL的預(yù)處理，產(chǎn)生執(zhí)行計(jì)劃
6）優(yōu)化器：根據(jù)解析器執(zhí)行計(jì)劃進(jìn)行判斷，選擇最優(yōu)的執(zhí)行計(jì)劃
代價(jià)模型：根據(jù)資源耗損進(jìn)行評(píng)估 (CPU IO 內(nèi)存 )
7）執(zhí)行器：根據(jù)最優(yōu)執(zhí)行計(jì)劃，執(zhí)行SQL語句，產(chǎn)生執(zhí)行結(jié)果
執(zhí)行結(jié)果：數(shù)據(jù)在磁盤的XXX位置
8）提供查詢緩存，提高效率。（默認(rèn)關(guān)閉，可使用"Redis"或"Tair"替代查詢緩存）
9）提供日志記錄（日志管理）：Binlog(默認(rèn)關(guān)閉)
存儲(chǔ)引擎層（類似于Linux文件系統(tǒng)）
負(fù)責(zé)根據(jù)SQL語句執(zhí)行結(jié)果，調(diào)取磁盤數(shù)據(jù)。(16進(jìn)制)
將16進(jìn)制的磁盤數(shù)據(jù)，交由SQL層轉(zhuǎn)化為表，由連接層的專用線程返回給用戶。
MySQL出現(xiàn)故障或性能問題80%以上是人為的。
——《oldguo》
3）邏輯結(jié)構(gòu)
數(shù)據(jù)庫
存放數(shù)據(jù)表
庫名、庫權(quán)限、庫數(shù)據(jù)類型
數(shù)據(jù)表（二維表）
列：字段
行：記錄
MySQL為了使存入的數(shù)據(jù)準(zhǔn)確、規(guī)范、有意義，增強(qiáng)了傳統(tǒng)二維表的功能。
表屬性：權(quán)限、字符集、存儲(chǔ)引擎
列屬性：數(shù)據(jù)類型、約束、其他（默認(rèn)值、自增長、注釋）
物理存儲(chǔ)結(jié)構(gòu)引入
庫的物理存儲(chǔ)結(jié)構(gòu)
用文件系統(tǒng)的目錄來存儲(chǔ)
位置：/data/mysql/data
表的物理存儲(chǔ)結(jié)構(gòu)
MyISAM（默認(rèn)存儲(chǔ)引擎）表
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ù)行+索引
表的頁、區(qū)、段（了解）
- 頁：最小的存儲(chǔ)單元 16KB
- 區(qū)：1個(gè)或多個(gè)連續(xù)的頁
- 段：1個(gè)或多個(gè)連續(xù)的區(qū)，一個(gè)表就是一個(gè)段
4）基礎(chǔ)管理
用戶、權(quán)限管理
- 用戶
作用：登陸 管理數(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;
#查詢表結(jié)構(gòu)
>desc mysql.user;
- 改
#'更改用戶密碼
>alter user inanhan@'10.0.0.%' identified by '456';
- 刪
#'刪除用戶
>drop user inanhan@'10.0.0.%';
權(quán)限
- 權(quán)限介紹
SELECT #允許從表中查看數(shù)據(jù)
INSERT #允許在表里插入數(shù)據(jù)
UPDATE #允許修改表中的數(shù)據(jù)的權(quán)限
DELETE #刪除行數(shù)據(jù)
CREATE #允許創(chuàng)建新的數(shù)據(jù)庫和表的權(quán)限
DROP #刪除數(shù)據(jù)庫與表
RELOAD #允許刷新權(quán)限(FLUSH命令)
SHUTDOWN #允許關(guān)閉數(shù)據(jù)庫實(shí)例
PROCESS #允許查看數(shù)據(jù)庫進(jìn)程
FILE #允許用戶在MySQL進(jìn)行讀寫文件磁盤操作
REFERENCES #允許創(chuàng)建外鍵（5.7.6版本之后引入）
INDEX #允許創(chuàng)建和刪除索引
ALTER #允許修改表結(jié)構(gòu)的權(quán)限，但必須要求有CREATE和INSERT權(quán)限配合
SHOW DATABASES #查看所有的數(shù)據(jù)庫名
SUPER #允許執(zhí)行一系列數(shù)據(jù)庫管理命令，包括kill強(qiáng)制關(guān)閉某個(gè)連接
CREATE TEMPORARY TABLES #允許創(chuàng)建臨時(shí)表的權(quán)限
LOCK TABLES #允許對(duì)擁有select權(quán)限的表進(jìn)行鎖定，以防止其他鏈接對(duì)此表讀或?qū)?EXECUTE #允許執(zhí)行存儲(chǔ)過程和函數(shù)的權(quán)限
REPLICATION SLAVE #允許Slave主機(jī)通過此用戶連接Master以便建立主從復(fù)制關(guān)系
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 #允許修改或者刪除存儲(chǔ)過程、函數(shù)的權(quán)限
CREATE USER #允許創(chuàng)建用戶
EVENT #允許查詢，創(chuàng)建，修改，刪除MySQL事件
TRIGGER #允許創(chuàng)建，刪除，執(zhí)行，顯示觸發(fā)器的權(quán)限
CREATE TABLESPACE #允許創(chuàng)建表空間
-------------------------------------------------------------------------------------
ALL #以上所有權(quán)限，普通管理員權(quán)限
with grant option #超級(jí)管理員功能，為其他用戶授權(quán)
應(yīng)用用戶權(quán)限 #SELECT，INSERT，UPDATE，DELETE
- 授權(quán)對(duì)象
*.* --->chmod 755 -R / ---->針對(duì)管理員
inanhan.* --->chmod 755 -R /inanhan ---->應(yīng)用用戶
inanhan.t1 --->chmod 755 -R /inanhan/t1
基本權(quán)限管理操作
- 授權(quán)命令寫法
grant 權(quán)限 on 對(duì)象 to 用戶 identified '密碼'；
- 需求01：Windows系統(tǒng)使用Navicat登錄到Linux中的MySQL，管理員用戶。
#'授權(quán)命令
> grant all on *.* to root@'10.0.0.%' identified by '123';
- 需求02：創(chuàng)建一個(gè)應(yīng)用用戶 app 用戶，能從Windows上登錄MySQL，并且可以操作app庫。
#'授權(quán)命令
> grant select ,update,insert,delete on app.* to app@'10.0.0.%' identified by '123';
- 開發(fā)人員用戶授權(quán)流程
權(quán)限
操作對(duì)象（庫與表）
登陸IP地址
密碼要求
- 8.0版本grant命令新特性
#用戶創(chuàng)建與授權(quán)分開
#不支持自動(dòng)創(chuàng)建用戶與更改密碼
- 查看授權(quán)信息
>show grants for app@'10.0.0.%'; #查看app用戶授權(quán)信息
- 回收權(quán)限
>revoke delete on app.* from app@'10.0.0.%'; #回收app用戶delete權(quán)限
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í)行命令
< #導(dǎo)入SQL腳本
#免交互查看MySQL數(shù)據(jù)庫用戶信息
mysql -uroot -p -e "select user,host from mysql.user;"
Enter password:
#導(dǎo)入SQL語句到MySQL數(shù)據(jù)庫
mysql -uroot -p <world.sql
Enter password:
多種啟動(dòng)方式介紹
啟動(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ù)庫。
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
- 默認(rèn)情況下，MySQL啟動(dòng)時(shí)，會(huì)依次讀取以上配置文件，如果有重復(fù)選項(xiàng)，會(huì)以最后一個(gè)文件設(shè)置的為準(zhǔn)。
- 若加入--defaults-file=xxxx時(shí)，則直接讀取指定文件。
初始化配置書寫格式
[標(biāo)簽]
配置項(xiàng)=XXX
...
#標(biāo)簽類型：服務(wù)端、客戶端
服務(wù)端
[mysqld]
[mysqld_safe]
客戶端
[mysql]
[mysqldump]
[client]
#實(shí)例
[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）多實(shí)例的應(yīng)用
準(zhǔn)備環(huán)境
#創(chuàng)建目錄
mkdir -p /data/330{7,8,9}/data
#準(zhǔn)備配置文件
#-----------------------實(shí)例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
#-----------------------實(shí)例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
#-----------------------實(shí)例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
#-------------------------------實(shí)例01
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/data/mysql
#-------------------------------實(shí)例02
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/data/mysql
#-------------------------------實(shí)例03
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/data/mysql
systemd管理多實(shí)例
#進(jìn)入system目錄
cd /etc/systemd/system
=====================================
#拷貝服務(wù)啟動(dòng)腳本
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service
=====================================
#更改配置文件倒數(shù)第二行內(nèi)容
#---------------------------實(shí)例01
vim mysqld3307.service
ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
#---------------------------實(shí)例02
vim mysqld3308.service
ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
#---------------------------實(shí)例03
vim mysqld3309.service
ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
授權(quán)
#授權(quán)MySQL文件目錄
chown -R mysql.mysql /data/*
啟動(dòng)實(shí)例
# 如有必要，可設(shè)置開機(jī)自啟動(dòng)
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service
驗(yàn)證多實(shí)例
- 端口檢測(cè)
#檢測(cè)端口（3306配置文件已改名，所以未啟動(dòng)）
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
檢測(cè)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密碼
#關(guān)閉數(shù)據(jù)庫
systemctl stop mysqld
#跳過授權(quán)啟動(dòng)
mysql_safe --skip-grant-tables --skip-networking &
--skip-grant-tables : 連接層關(guān)閉驗(yàn)證模塊，所有驗(yàn)證表不加載。
--skip-networking ：連接層關(guān)閉TCP/IP協(xié)議，禁止遠(yuǎn)程訪問。
#無密碼登陸
mysql -uroot -p 回車
> flush privileges;
> alter user root@'localhost' identified by '456';
#殺掉MySQL進(jìn)程
pkill mysqld
#啟動(dòng)MySQL
systemctl start mysqld
