對MySQL做系統(tǒng)的復(fù)習(xí)。

用戶管理

數(shù)據(jù)庫賬號

• 用戶名@可訪問列表

  • 訪問列表種類

    • %--> 代表可以從所有外部主機(jī)訪問
    • 192.169.1.%--> 代表可以從192.168.1網(wǎng)段訪問
    • localhost --> 代表只能本地訪問

• 創(chuàng)建數(shù)據(jù)庫用戶

  • CREATE USER 建立用戶 具體語法信息可以使用 \h create user 查看，部分語法如圖

p1.png

• 常用權(quán)限 使用 show privileges;查看當(dāng)前MYSQL版本的權(quán)限列表。

p2.png

• 用戶授權(quán)
• 遵循最小權(quán)限原則
• 使用 Grant 命令對用戶授權(quán)
  • grant 權(quán)限語句 on 庫名.表名 to user@ip;
    • 例子：比如給用戶test01 賦權(quán)限給test數(shù)據(jù)庫。(grant create to test.* on test01@ip identified by ‘密碼’ )
• 使用revoke命令收回用戶的權(quán)限
  • revoke 權(quán)限 on 庫名.表名 from user@ip;
    • 例子: 比如給用戶test01在庫數(shù)據(jù)庫test中取消CREATE權(quán)限。（revoke create on test.* from test01@'localhost' ）
  • 刷新權(quán)限 flush privileges

服務(wù)器配置

SQL_MODE

set [session/global/persist] sql_mode = 'XXXX';

主要分為兩大類

• 寬松模式：SQL_MODE = 'ANSI';
• 傳統(tǒng)模式: SQL_MODE = 'TRADITIONAL';

系統(tǒng)配置

set session 系統(tǒng)變量名 = 'xxxxx';

set global 系統(tǒng)變量名 = 'xxxxx';

set persist 系統(tǒng)變量名 = 'xxxxx' (MYSQL 8)

常用性能參數(shù)

p3.png

p4.png

p5.png

日志以及使用場景

常用日志

錯誤日志（error_log）

• 分析排除MYSQL運(yùn)行錯誤：異常重啟，啟動失敗，主從同步異常等。
• 記錄未經(jīng)授權(quán)的訪問。

錯誤日志參數(shù)配置

• log_error = 'xxxxxxxxxxxx' 錯誤日志輸出路徑
• log_error_verbosity = [1,2,3] 日志輸出級別

p6.png

• log_error_services = [服務(wù)組件1;服務(wù)組件2]

p7.png

查看錯誤日志路徑:select @@log_error;

查看錯誤日志級別:select @@log_error_verbosity;

查看日志服務(wù)組件:select @@log_error_services;

常規(guī)日志(general_log)：必要時打開，要及時關(guān)閉

• 分析客戶端發(fā)送到MySQL的實際請求。（比如 從客戶端連接開始，改變數(shù)據(jù)庫中的信息，到客戶端斷開連接的整個過程）

常規(guī)日志(general_log)的參數(shù)配置

• general_log = [ON|OFF] 常規(guī)日志開關(guān)閉 默認(rèn)OFF
• general_log_file = 'xxxxxxxxxxxxxx' 常規(guī)日志的存儲路徑
• log_output = [FILE|TABLE|NONE] 日志輸出形式，如果為TABLE 默認(rèn)保存在 GENERAL_LOG表中。

查看常規(guī)日志開啟狀態(tài):select @@general_log

查看常規(guī)日志文件路徑：select @@general_log_file

查看常規(guī)日志輸出形式：select @@log_output

慢查日志(slow_query_log) :解決性能問題

• 將執(zhí)行成功并符合條件的查詢錄到日志中。
• 找到需要優(yōu)化的SQL。

慢查日志(slow_query_log) 的參數(shù)配置

• slow_query_log = [ON|OFF] 慢查日志開關(guān) 默認(rèn)OFF
• slow_query_log_file = 'xxxxxxxxxx' 慢查日志位置
• long_query_time = xxx（以秒為單位，可以記錄到微妙 可以6位小數(shù)） 當(dāng)SQL執(zhí)行時間超過這個值時，就會被記錄到慢查日志中，如果想記錄所有SQL 則設(shè)置為 0
• log_queries_not_using_indexes = [ON | OFF ] 將所有沒有使用索引的SQL記錄到日志中 默認(rèn)OFF
• log_slow_admin_statements = [ON | OFF ] 記錄操作的管理命令 比如 alter table ,create index 等 默認(rèn)為OFF

查詢SQL執(zhí)行時間閾值:show variables like 'long_query_time';

二進(jìn)制日志（binary_log）

• 記錄所有對數(shù)據(jù)庫中數(shù)據(jù)的修改 (insert update delete等)。
• 由于記錄的所有數(shù)據(jù)庫的修改操作可以基于時間點(diǎn)的備份和恢復(fù)。
• 主從復(fù)制

二進(jìn)制日志（binary_log）常用配置

• log-bin [=base_name] 是否啟用二進(jìn)制日志,base_name是存儲的文件目錄以及前綴名， 靜態(tài)配置，只能在配置文件中修改 。

p8.png

• binlog_format = [ROW | STATEMENT | MIXED] 日志記錄方式

• binlog_row_image = [FULL | MINIMAL | NOBLOB ] 針對 ROW方式記錄的記錄方式

• binlog_row_query_log_events = [ON | OFF ] 開啟針對ROW日志記錄方式時，記錄當(dāng)前執(zhí)行SQL。

• log_slave_updates = [ON | OFF ] 正常情況下 slave服務(wù)器不會記錄從主服務(wù)器上同步的日志，開啟后會記錄從主同步到slave服務(wù)器時的日志。

• sync_binlog = [1 | 0 ] 1 是每寫一次二進(jìn)制日志，就會像磁盤刷新， 0 是 不會主動刷新至磁盤。

• expire_logs_days = days 設(shè)置日志過期時間，自動清理過期日期

• PURGE BINARY LOGS TO '二進(jìn)制日志文件名' 清理 設(shè)置的 二進(jìn)制日志文件名 之前的所有日志

• PURGE BINARY LOGS BEFORE '2008-04-22 12:12:12'; 清理日期之前的二進(jìn)制日志。

查詢二進(jìn)制日志的行記錄方式的指令: mysqlbinlog --no-defaults -vv --base64-output=DECODE-ROWS 日志文件名

存儲引擎

引擎種類

p10.png

鎖種類

• 共享鎖（Share Lock）：共享鎖又稱讀鎖，是讀取操作創(chuàng)建的鎖。其他用戶可以并發(fā)讀取數(shù)據(jù)，但任何事務(wù)都不能對數(shù)據(jù)進(jìn)行修改（獲取數(shù)據(jù)上的排他鎖），直到已釋放所有共享鎖。

• 排他鎖（Exclusive Lock）：排他鎖又稱寫鎖、獨(dú)占鎖，如果事務(wù)T對數(shù)據(jù)A加上排他鎖后，則其他事務(wù)不能再對A加任何類型的封鎖。獲準(zhǔn)排他鎖的事務(wù)既能讀數(shù)據(jù)，又能修改數(shù)據(jù)。

MyISAM引擎

• 非事務(wù)型存儲引擎
• 以堆表方式存儲
• 使用表級鎖 ： 比如查詢的時候就加共享鎖，修改數(shù)據(jù)的時候就加排他鎖。讀寫操作之間會有相互阻塞的情況。
• 支持Btree索引，空間索引，全文索引.
• 表的數(shù)據(jù)和索引是分開存儲的。

部分命令

1. 修復(fù)表 : repair table tb
2. 壓縮表: myisampack -b -f tb

使用場景

• 讀操作遠(yuǎn)遠(yuǎn)大于寫操作的場景
• 不需要事務(wù)

CSV存儲引擎

• 非事務(wù)型存儲引擎
• 數(shù)據(jù)以CSV格式存儲
• 所有列不能為NULL
• 不支持索引

使用場景

• 作為數(shù)據(jù)交換的中間表使用，可以把EXCEL等表數(shù)據(jù)文件轉(zhuǎn)換成CSV然后賦值到MYSQL 數(shù)據(jù)存放的目錄下。

Archive引擎

• 非事務(wù)型存儲引擎
• 表數(shù)據(jù)使用zlib壓縮
• 只支持insert和 select
• 只允許在自增ID列上建立索引

使用場景

• 日志和數(shù)據(jù)采集類的數(shù)據(jù)應(yīng)用。
• 數(shù)據(jù)歸檔

Memory引擎

• 非事務(wù)型存儲引擎
• 數(shù)據(jù)存儲在內(nèi)存中
• 所有字段長度固定
• 支持Btree索引和Hash索引

使用場景（類似于redis）

• 緩存字典映射表

• 緩存周期性分析數(shù)據(jù)

Innodb引擎

• 事務(wù)型存儲引擎，支持ACID
• 數(shù)據(jù)按主鍵聚集存儲。（主鍵是邏輯存儲）
• 支持行級鎖以及MVCC(多版本并發(fā)控制)
• 支持Btree和自適應(yīng)Hash索引
• 支持全文索引和空間索引

使用場景

• 支持絕大部分的OLTP場景

NDB引擎

• 事務(wù)型存儲引擎，支持ACID
• 數(shù)據(jù)在使用前要從磁盤讀取到內(nèi)存
• 支持行級鎖
• 支持集群
• 支持Ttree索引

使用場景

• 需要數(shù)據(jù)完全同步的高可用場景

Innodb相關(guān)問題

什么情況下，無法在線修改表？

答：增加索引，主鍵，自增列，修改列類型以及表的字符集等。

p11.png

在線DDL存在的問題?

• 有些DDL語句不支持在線修改
• 長時間的DDL操作，影響主從復(fù)制
• 無法對DDL操作進(jìn)行資源限制，會導(dǎo)致磁盤或內(nèi)存暴增。

如何安全的在線DDL？

使用Percona 工具 相關(guān)命令 pt-online-schema-change [OPTIONS] DSN

樣例:

p12.png

Innodb如何實現(xiàn)事務(wù)

數(shù)據(jù)庫事務(wù)(Database Transaction) ，是指作為單個邏輯工作單元執(zhí)行的一系列操作，要么完全地執(zhí)行，要么完全地不執(zhí)行。

共享鎖：如果事務(wù)T對數(shù)據(jù)A加上共享鎖后，則其他事務(wù)只能對A再加共享鎖，不能加排它鎖。獲準(zhǔn)共享鎖的事務(wù)只能讀數(shù)據(jù)，不能修改數(shù)據(jù)。

排他鎖：如果事務(wù)T對數(shù)據(jù)A加上排他鎖后，則其他事務(wù)不能再對A加任任何類型的封鎖。獲準(zhǔn)排他鎖的事務(wù)既能讀數(shù)據(jù)，又能修改數(shù)據(jù)。

p13.png

舉例：

p14.png

MVCC流程

p15.png

;