本文源碼：GitHub·點(diǎn)這里 || GitEE·點(diǎn)這里

一、MySQL用戶(hù)

1、基礎(chǔ)描述

在數(shù)據(jù)庫(kù)的使用過(guò)程中，用戶(hù)作為訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)的鑒權(quán)因素，起到非常重要的作用，安裝MySQL時(shí)會(huì)自動(dòng)生成一個(gè)root用戶(hù)，作為數(shù)據(jù)庫(kù)管理員，擁有所有權(quán)限。在多用戶(hù)的應(yīng)用場(chǎng)景下，可能需要給不同的用戶(hù)分配不同的權(quán)限，用來(lái)提升系統(tǒng)的穩(wěn)定性，比如常見(jiàn)：報(bào)表庫(kù)只提供讀權(quán)限，或者開(kāi)放給第三方的庫(kù)，也只提供可讀用戶(hù)。

2、用戶(hù)管理

基本描述

MySQL將用戶(hù)信息存儲(chǔ)在系統(tǒng)數(shù)據(jù)庫(kù)mysql的user表中。根據(jù)用戶(hù)名密碼和客戶(hù)端主機(jī)來(lái)定義帳戶(hù)。

用戶(hù)密碼：基本驗(yàn)證操作 ；

客戶(hù)端IP：類(lèi)似黑白名單的限制，支持通配符表達(dá)式 ；

SELECT t.`Host`,t.`User`,t.authentication_string 
FROM mysql.`user` t ;

添加用戶(hù)

可以對(duì)user表進(jìn)行增刪改查一系列操作，進(jìn)而添加用戶(hù)，不同的用戶(hù)就會(huì)涉及到不同的操作權(quán)限，這就是另外一個(gè)問(wèn)題：用戶(hù)的權(quán)限管理。

這里添加一個(gè)user01用戶(hù)，作為權(quán)限模塊的測(cè)試用戶(hù)，權(quán)限先給和root用戶(hù)一樣的權(quán)限。

INSERT INTO `mysql`.`user`(`Host`, `User`, `authentication_string`) 
VALUES ('%', 'user01', '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9');
FLUSH PRIVILEGES ;

注意：這里host賦值%，就是代表所有IP可以通過(guò)user01用戶(hù)連接MySQL服務(wù)器。修改系統(tǒng)表之后需要執(zhí)行一次刷新操作。

二、訪(fǎng)問(wèn)鑒權(quán)

1、權(quán)限控制

MySQL數(shù)據(jù)庫(kù)系統(tǒng)中，權(quán)限分配涉及到如下幾張核心表：user、db、table_pric、columns_priv。在權(quán)限認(rèn)證時(shí)候遵守該順序逐步驗(yàn)證。

• 權(quán)限表描述

user表：存儲(chǔ)用戶(hù)和用戶(hù)全局權(quán)限，也是MySQL鑒權(quán)流程首當(dāng)其沖的表 ；

db表：保存數(shù)據(jù)庫(kù)權(quán)限 ；

tables_priv表：存儲(chǔ)表權(quán)限，面向一個(gè)特定表中的和其中所有列；

columns_priv表：存儲(chǔ)列權(quán)限，面向一個(gè)特定表中的單一列；

注意：權(quán)限表的管理，不止上述描述的幾個(gè)，但是人生苦短，把這幾個(gè)理順了，其他表也應(yīng)該可以順藤摸瓜找過(guò)去。

• user表結(jié)構(gòu)

這里處理包含用戶(hù)的連接信息，還有很多權(quán)限點(diǎn)認(rèn)證。

CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  ... //此處省略很多
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';

注意：注釋說(shuō)明,用戶(hù)和全局權(quán)限管理。

• 權(quán)限點(diǎn)描述

對(duì)一般開(kāi)發(fā)流程而言，知道如下幾個(gè)權(quán)限點(diǎn)管理即可，道理同上，到需要使用的時(shí)候再去熟悉，不差時(shí)。

SELECT: 查詢(xún)表中的記錄 ；
INSERT: 向表中寫(xiě)入新數(shù)據(jù) ；
UPDATE: 更新表數(shù)據(jù)；
DELETE: 刪除表的記錄 ；
CREATE: 創(chuàng)建數(shù)據(jù)庫(kù)和表 ；
DROP: 刪除數(shù)據(jù)庫(kù)和表 ；

絮叨一句：工作幾年之后，你最喜歡寫(xiě)的程序邏輯是什么？反正我就想寫(xiě)寫(xiě)簡(jiǎn)單的增刪改查操作。

2、鑒權(quán)流程

首先驗(yàn)證user表，其次db表，然后table表，再然后column表；

基于范圍逐級(jí)縮小，權(quán)限不斷的細(xì)化。

• 測(cè)試user01用戶(hù)權(quán)限

權(quán)限查詢(xún)

首先查看user01用戶(hù)的查詢(xún)權(quán)限。此時(shí)該用戶(hù)是具有select權(quán)限的。

SELECT t.`Host`,t.`User`,t.Select_priv 
FROM mysql.`user` t WHERE t.`User`='user01' ;

基于客戶(hù)端工具，使用user01用戶(hù)登錄MySQL服務(wù)器，可以正常使用查詢(xún)權(quán)限。

禁用查詢(xún)權(quán)限點(diǎn)

UPDATE `mysql`.`user` SET `Select_priv` = 'N' WHERE `User` = 'user01';
FLUSH PRIVILEGES ;

權(quán)限驗(yàn)證

使用user01登錄的客戶(hù)端，不能查詢(xún)表數(shù)據(jù)，說(shuō)明權(quán)限管理起到作用了。

三、日志記錄系統(tǒng)

1、日志配置查看

基于該語(yǔ)句查看日志相關(guān)配置，例如日志地址，是否開(kāi)啟關(guān)閉，日志緩存大小，相關(guān)配置信息。

SHOW GLOBAL VARIABLES LIKE '%log%';

正常停止MySQL服務(wù)器，可以通過(guò)my.cnf更改相關(guān)配置。Linux下配置文件一般在/etc/my.cnf中。

2、InnoDB事務(wù)日志

InnoDB的事務(wù)日志包括Redo-log和Undo-log兩種，這個(gè)日志的描述在MySQL5.7官方文檔的InnoDB存儲(chǔ)引擎-磁盤(pán)結(jié)構(gòu)模塊下面。

• Redo-log

重做日志：基于磁盤(pán)的數(shù)據(jù)結(jié)構(gòu)，記錄事務(wù)性操作崩潰期間沒(méi)有正常寫(xiě)入庫(kù)的數(shù)據(jù)，重做：處理日志中沒(méi)有正常寫(xiě)入的數(shù)據(jù)記錄，完成數(shù)據(jù)入庫(kù)。

• Undo-log

回滾日志：提供回滾操作和多個(gè)行版本控制MVCC，事務(wù)提交時(shí)，會(huì)記錄Undo-log，當(dāng)事務(wù)失敗或執(zhí)行回滾，就需要通過(guò)Undo-log進(jìn)行回滾。思維跳躍一層：當(dāng)寫(xiě)入數(shù)據(jù)時(shí)，日志記錄應(yīng)該是新增標(biāo)記，要執(zhí)行的記錄是刪除這條數(shù)據(jù)操作，刪除數(shù)據(jù)，過(guò)程應(yīng)該相反，要記錄的是刪除的這條數(shù)據(jù)的寫(xiě)入操作。

2、錯(cuò)誤日志

在MySQL的配置文件中，log_error是強(qiáng)制開(kāi)啟的，且沒(méi)有關(guān)閉開(kāi)關(guān)，用來(lái)記錄mysql服務(wù)器每次啟動(dòng)和關(guān)閉時(shí)的詳細(xì)信息，以及運(yùn)行過(guò)程中出現(xiàn)的的嚴(yán)重警告信息和錯(cuò)誤信息等，Linux下配置如下:

log-error=/var/log/mysqld.log

錯(cuò)誤日志包含mysqld啟動(dòng)和關(guān)閉時(shí)間的記錄。它還包含診斷消息，例如錯(cuò)誤，警告和注釋?zhuān)鼈冊(cè)诜?wù)器啟動(dòng)和關(guān)閉期間以及服務(wù)器運(yùn)行期間發(fā)生。例如，如果mysqld注意到需要自動(dòng)檢查或修復(fù)表，它將向錯(cuò)誤日志中寫(xiě)入一條消息。

4、二進(jìn)制日志

Binary-Log，主要用來(lái)記錄數(shù)據(jù)庫(kù)更改，例如表創(chuàng)建操作或表數(shù)據(jù)更改的事件，對(duì)于主從復(fù)制流程，主庫(kù)服務(wù)器上的二進(jìn)制日志發(fā)送到從庫(kù)服務(wù)器，從服務(wù)器執(zhí)行這些事件，保證主從服務(wù)器的數(shù)據(jù)同步。

log_bin  OFF

MySQL5.6版本下，該日志默認(rèn)是關(guān)閉的。

SHOW GLOBAL VARIABLES LIKE '%long_query_time%';

開(kāi)啟慢查詢(xún)?nèi)罩?，通過(guò)對(duì)該時(shí)間的調(diào)整，可以記錄性能差的SQL語(yǔ)句，進(jìn)行分析優(yōu)化，對(duì)系統(tǒng)性能的提升十分有幫助。

四、源代碼地址

GitHub·地址
https://github.com/cicadasmile/mysql-data-base
GitEE·地址
https:///cicadasmile/mysql-data-base