誰再說學不會 MySQL 資料庫，就把這個給他扔過去！

大家好，我是民工哥。

又是新的一年奮鬥路的開啟，相信有不少人農曆新年之後，肯定會有所變動（跳槽加薪少不了）。所以，我把往期推送過的MySQL技術文章做了一個相關的整理，基礎不好的可以從最基礎的學習一遍，提高的也可以從中再提取深入一下。

碼字不易，如有幫助，請隨手點在看與轉發朋友圈支援一下民工哥，關注我，一起學習更多的IT技術知識，共同進步。

資料庫是什麼

資料庫管理系統，簡稱為DBMS（Database Management System），是用來儲存資料的管理系統。

DBMS 的重要性

無法多人共享資料
無法提供操作大量資料所需的格式
實現讀取自動化需要程式設計技術能力
無法應對突發事故

DBMS 的種類

層次性資料庫
- 最古老的資料庫之一，因為突出的缺點，所以很少使用了
關係型資料庫
- 採用行列二維表結構來管理資料庫，類似Excel的結構，使用專用的SQL語言對資料進行控制。
關聯式資料庫管理系統的常見種類
- Oracle ==> 甲骨文
- SQL Servce ==> 微軟
- DB2 ==> IBM
- PostgreSQL ==> 開源
- MySQL ==> 開源
物件導向的資料庫
- XML資料庫
- 鍵值儲存系統
- DB2
- Redis
- MongoDB

SQL 語句及其種類

DDL（資料定義語言）
- create ==> 建立資料庫或者表等物件
- drop ==> 刪除資料庫或者表等物件
- alter ==> 修改資料庫或者表等物件的結構
DML（資料操作語言）
- select ==> 查詢表中資料
- insert ==> 向表中插入資料
- update ==> 更新表中資料
- delete ==> 刪除表中資料
DCL（資料控制語言）
- commit ==> 決定對資料庫中的資料進行變更
- rollback ==> 取消對資料庫中的資料進行變更
- grant ==> 賦予使用者操作許可權
- revoke ==> 取消使用者的操作許可權

SQL 的基本書寫規則

SQL 語句要以；結尾
關鍵字不區分大小寫，但是表中資料區分大小寫
關鍵字大寫
表名的首字母大寫
列明等小寫
常數的書寫方式是固定的
遇到字串、日期等型別需要用到''
單詞間需要使用空格分割
命名規則
資料庫和表的名稱可以使用英文、資料以及下劃線
名稱必須以英文作為開頭
名稱不能重複
掌握 SQL 這些核心知識點，出去吹牛逼再也不擔心了

資料型別

integer
- 數字型，但是不能存放小數
char
- 定長字串型別，指定最大長度，不足使用空格填充
varchar
- 可變長度字串型別，指定最大長度，但是不足不填充
data
- 儲存日期，年/月/日

以上內容是對通用資料庫以及sql語句相關的知識點介紹，本文不做過多的贅述，本文主要針對關係型資料庫：MySQL 來進行各方面的知識點總結。

MySQL 資料庫簡介

MySQL 是最流行的關係型資料庫管理系統，在 WEB 應用方面 MySQL 是最好的 RDBMS(Relational Database Management System：關聯式資料庫管理系統)應用軟體之一。

MySQL 是一個關係型資料庫管理系統，由瑞典 MySQL AB 公司開發，目前屬於 Oracle 公司。MySQL 是一種關聯資料庫管理系統，關聯資料庫將資料儲存在不同的表中，而不是將所有資料放在一個大倉庫內，這樣就增加了速度並提高了靈活性。

MySQL 是開源的，目前隸屬於 Oracle 旗下產品。
MySQL 支援大型的資料庫。可以處理擁有上千萬條記錄的大型資料庫。
MySQL 使用標準的 SQL 資料語言形式。
MySQL 可以執行於多個系統上，並且支援多種語言。這些程式語言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
MySQL 對PHP有很好的支援，PHP 是目前最流行的 Web 開發語言。
MySQL 支援大型資料庫，支援 5000 萬條記錄的資料倉儲，32 位系統表檔案最大可支援 4GB，64 位系統支援最大的表檔案為8TB。
MySQL 是可以定製的，採用了 GPL 協議，你可以修改原始碼來開發自己的 MySQL 系統。

在日常工作與學習中，無論是開發、運維、還是測試，對於資料庫的學習是不可避免的，同時也是日常工作的必備技術之一。在網際網路公司，開源產品線比較多，網際網路企業所用的資料庫佔比較重的還是MySQL。

更多關於MySQL資料庫的介紹，有興趣的讀者可以參考官方網站的文件和這篇文章：可能是全網最好的MySQL重要知識點，關於MySQL架構的介紹可以參考：MySQL 架構總覽->查詢執行流程->SQL 解析順序

MySQL 安裝

MySQL 8正式版8.0.11已釋出，官方表示MySQL8要比MySQL 5.7快2倍，還帶來了大量的改進和更快的效能！到底誰最牛呢？請看：MySQL 5.7 vs 8.0，哪個效能更牛？

詳細的安裝步驟請參閱：CentOS 下 MySQL 8.0 安裝部署，超詳細！，介紹幾個 8.0 在關聯式資料庫方面的主要新特性：MySQL 8.0 的 5 個新特性，太實用了！

MySQL基礎入門操作

Windows服務

-- 啟動MySQL
net start mysql

-- 建立Windows服務
sc create mysql binPath= mysqld_bin_path(注意：等號與值之間有空格)

連線與斷開伺服器

mysql -h 地址 -P 埠 -u 使用者名稱 -p 密碼

SHOW PROCESSLIST -- 顯示哪些執行緒正在執行
SHOW VARIABLES -- 顯示系統變數資訊

資料庫操作

-- 檢視當前資料庫
SELECT DATABASE();

-- 顯示當前時間、使用者名稱、資料庫版本
SELECT now(), user(), version();

-- 建立庫
CREATE DATABASE[ IF NOT EXISTS] 資料庫名 資料庫選項
    資料庫選項：
        CHARACTER SET charset_name
        COLLATE collation_name

-- 檢視已有庫
    SHOW DATABASES[ LIKE 'PATTERN']

-- 檢視當前庫資訊
    SHOW CREATE DATABASE 資料庫名

-- 修改庫的選項資訊
    ALTER DATABASE 庫名 選項資訊

-- 刪除庫
    DROP DATABASE[ IF EXISTS] 資料庫名
        同時刪除該資料庫相關的目錄及其目錄內容

表的操作

-- 建立表
CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [庫名.]表名 ( 表的結構定義 )[ 表選項]
每個欄位必須有資料型別
最後一個欄位後不能有逗號
TEMPORARY 臨時表，會話結束時表自動消失
對於欄位的定義：
   欄位名 資料型別 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
-- 表選項
  -- 字符集
  CHARSET = charset_name
  如果表沒有設定，則使用資料庫字符集
  -- 儲存引擎
  ENGINE = engine_name
  表在管理資料時採用的不同的資料結構，結構不同會導致處理方式、提供的特性操作等不同
  常見的引擎：InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive
  不同的引擎在儲存表的結構和資料時採用不同的方式
  MyISAM表檔案含義：.frm表定義，.MYD表資料，.MYI表索引
  InnoDB表檔案含義：.frm表定義，表空間資料和日誌檔案
  SHOW ENGINES -- 顯示儲存引擎的狀態資訊
  SHOW ENGINE 引擎名 {LOGS|STATUS} -- 顯示儲存引擎的日誌或狀態資訊
    -- 自增起始數
        AUTO_INCREMENT = 行數
    -- 資料檔案目錄
        DATA DIRECTORY = '目錄'
    -- 索引檔案目錄
        INDEX DIRECTORY = '目錄'
    -- 表註釋
        COMMENT = 'string'
    -- 分割槽選項
        PARTITION BY ... (詳細見手冊)

-- 檢視所有表
SHOW TABLES[ LIKE 'pattern']
SHOW TABLES FROM 表名

-- 檢視錶機構
SHOW CREATE TABLE 表名 （資訊更詳細）
DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 [LIKE 'PATTERN']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

-- 修改表
   -- 修改表本身的選項
    ALTER TABLE 表名 表的選項
    eg: ALTER TABLE 表名 ENGINE=MYISAM;
    -- 對錶進行重新命名
    RENAME TABLE 原表名 TO 新表名
    RENAME TABLE 原表名 TO 庫名.表名 （可將表移動到另一個資料庫）
    -- RENAME可以交換兩個表名
    -- 修改表的欄位機構（13.1.2. ALTER TABLE語法）
       ALTER TABLE 表名 操作名
       -- 操作名
          ADD[ COLUMN] 欄位定義       -- 增加欄位
            AFTER 欄位名          -- 表示增加在該欄位名後面
            FIRST               -- 表示增加在第一個
            ADD PRIMARY KEY(欄位名)   -- 建立主鍵
            ADD UNIQUE [索引名] (欄位名)-- 建立唯一索引
            ADD INDEX [索引名] (欄位名) -- 建立普通索引
            DROP[ COLUMN] 欄位名      -- 刪除欄位
            MODIFY[ COLUMN] 欄位名 欄位屬性     -- 支援對欄位屬性進行修改，不能修改欄位名(所有原有屬性也需寫上)
            CHANGE[ COLUMN] 原欄位名 新欄位名 欄位屬性      -- 支援對欄位名修改
            DROP PRIMARY KEY    -- 刪除主鍵(刪除主鍵前需刪除其AUTO_INCREMENT屬性)
            DROP INDEX 索引名 -- 刪除索引
            DROP FOREIGN KEY 外來鍵    -- 刪除外來鍵
-- 刪除表
    DROP TABLE[ IF EXISTS] 表名 ...

-- 清空表資料
    TRUNCATE [TABLE] 表名

-- 複製表結構
    CREATE TABLE 表名 LIKE 要複製的表名

-- 複製表結構和資料
    CREATE TABLE 表名 [AS] SELECT * FROM 要複製的表名

-- 檢查表是否有錯誤
    CHECK TABLE tbl_name [, tbl_name] ... [option] ...

-- 優化表
   OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

-- 修復表
   REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]

-- 分析表
   ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

更多相關的操作基礎知識點請參閱以下文章：

MySQL 多例項配置

MySQL資料庫入門——多例項配置

MySQL 主從同步複製

複製概述

Mysql內建的複製功能是構建大型，高效能應用程式的基礎。將Mysql的資料分佈到多個系統上去，這種分佈的機制，是通過將Mysql的某一臺主機的資料複製到其它主機（slaves）上，並重新執行一遍來實現的。複製過程中一個伺服器充當主伺服器，而一個或多個其它伺服器充當從伺服器。主伺服器將更新寫入二進位制日誌檔案，並維護檔案的一個索引以跟蹤日誌迴圈。這些日誌可以記錄傳送到從伺服器的更新。當一個從伺服器連線主伺服器時，它通知主伺服器從伺服器在日誌中讀取的最後一次成功更新的位置。從伺服器接收從那時起發生的任何更新，然後封鎖並等待主伺服器通知新的更新。

請注意當你進行復制時，所有對複製中的表的更新必須在主伺服器上進行。否則，你必須要小心，以避免使用者對主伺服器上的表進行的更新與對從伺服器上的表所進行的更新之間的衝突。
mysql支援的複製型別：

L預設採用基於語句的複製，效率比較高。一旦發現沒法精確複製時，會自動選著基於行的複製。
l5.0開始支援
採用基於行的複製。

複製解決的問題

MySQL複製技術有以下一些特點：

資料分佈 (Data distribution )
負載平衡(load balancing)
備份(Backups)
高可用性和容錯行 High availability and failover

複製如何工作

整體上來說，複製有3個步驟：

master將改變記錄到二進位制日誌(binary log)中（這些記錄叫做二進位制日誌事件，binary log events）；
slave將master的binary log events拷貝到它的中繼日誌(relay log)；
slave重做中繼日誌中的事件，將改變反映它自己的資料。

更多相關的更深入的介紹參考：Mysql主從架構的複製原理及配置詳解

MySQL 複製有兩種方法：

傳統方式：基於主庫的bin-log將日誌事件和事件位置複製到從庫，從庫再加以應用來達到主從同步的目的。
Gtid方式：global transaction identifiers是基於事務來複制資料，因此也就不依賴日誌檔案位置，同時又能更好的保證主從庫資料一致性。
MySQL資料庫主從同步實戰過程
基於 Gtid 的 MySQL 主從同步實踐
MySQL 主從同步架構中你不知道的“坑”（上）
MySQL 主從同步架構中你不知道的“坑”（下）

MySQL複製有多種型別：

非同步複製：一個主庫，一個或多個從庫，資料非同步同步到從庫。
同步複製：在MySQL Cluster中特有的複製方式。
半同步複製：在非同步複製的基礎上，確保任何一個主庫上的事務在提交之前至少有一個從庫已經收到該事務並日志記錄下來。
延遲複製：在非同步複製的基礎上，人為設定主庫和從庫的資料同步延遲時間，即保證資料延遲至少是這個引數。

MySQL主從複製延遲解決方案：高可用資料庫主從複製延時的解決方案

MySQL 資料備份與恢復

資料備份多種方式：

物理備份是指通過拷貝資料庫檔案的方式完成備份，這種備份方式適用於資料庫很大，資料重要且需要快速恢復的資料庫
邏輯備份是指通過備份資料庫的邏輯結構（create database/table語句)和資料內容（insert語句或者文字檔案）的方式完成備份。這種備份方式適用於資料庫不是很大，或者你需要對匯出的檔案做一定的修改，又或者是希望在另外的不同型別伺服器上重新建立此資料庫的情況
通常情況下物理備份的速度要快於邏輯備份，另外物理備份的備份和恢復粒度範圍為整個資料庫或者是單個檔案。對單表是否有恢復能力取決於儲存引擎，比如在MyISAM儲存引擎下每個表對應了獨立的檔案，可以單獨恢復；但對於InnoDB儲存引擎表來說，可能每個表示對應了獨立的檔案，也可能表使用了共享資料檔案
物理備份通常要求在資料庫關閉的情況下執行，但如果是在資料庫執行情況下執行，則要求備份期間資料庫不能修改
邏輯備份的速度要慢於物理備份，是因為邏輯備份需要訪問資料庫並將內容轉化成邏輯備份需要的格式；通常輸出的備份檔案大小也要比物理備份大；另外邏輯備份也不包含資料庫的配置檔案和日誌檔案內容；備份和恢復的粒度可以是所有資料庫，也可以是單個資料庫，也可以是單個表；邏輯備份需要再資料庫執行的狀態下執行；它的執行工具可以是mysqldump或者是select … into outfile兩種方式
生產資料庫備份方案：高逼格企業級MySQL資料庫備份方案
MySQL資料庫物理備份方式：Xtrabackup實現資料的備份與恢復
MySQL 定時備份：MySQL 資料庫定時備份的幾種方式（非常全面）

MySQL 高可用架構設計與實戰

先來了解一下MySQL高可用架構簡介：淺談MySQL叢集高可用架構

MySQL高可用方案：MySQL 同步複製及高可用方案總結

官方也提供一種高可用方案：官方工具｜MySQL Router 高可用原理與實戰

MHA

MHA（Master High Availability）目前在MySQL高可用方面是一個相對成熟的解決方案，該軟體由兩部分組成：MHA Manager（管理節點）和MHA Node（資料節點。
MHA Manager: 可以單獨部署在一臺獨立的機器上管理多個master-slave叢集，也可以部署在一臺slave節點上。
MHA Node: 行在每臺MySQL伺服器上。
MHA Manager會定時探測叢集中的master節點，當master出現故障時，它可以自動將最新資料的slave提升為新的master，然後將所有其他的slave重新指向新的master。整個故障轉移過程對應用程式完全透明。

MHA高可用方案實戰：MySQL叢集高可用架構之MHA

MGR

Mysql Group Replication(MGR)是從5.7.17版本開始釋出的一個全新的高可用和高擴張的MySQL叢集服務。
高一致性，基於原生複製及paxos協議的組複製技術，以外掛方式提供一致資料安全保證；
高容錯性，大多數服務正常就可繼續工作，自動不同節點檢測資源徵用衝突，按順序優先處理，內建動防腦裂機制；
高擴充套件性，自動新增移除節點，並更新組資訊；
高靈活性，單主模式和多主模式。單主模式自動選主，所有更新操作在主進行；多主模式，所有server同時更新。

MySQL 資料庫讀寫分離高可用

海量資料的儲存和訪問成為了系統設計的瓶頸問題，日益增長的業務資料，無疑對資料庫造成了相當大的負載，同時對於系統的穩定性和擴充套件性提出很高的要求。隨著時間和業務的發展，資料庫中的表會越來越多，表中的資料量也會越來越大，相應地，資料操作的開銷也會越來越大；另外，無論怎樣升級硬體資源，單臺伺服器的資源（CPU、磁碟、記憶體、網路IO、事務數、連線數）總是有限的，最終資料庫所能承載的資料量、資料處理能力都將遭遇瓶頸。分表、分庫和讀寫分離可以有效地減小單臺資料庫的壓力。

MySQL讀寫分離高可用架構實戰案例：

ProxySQL+Mysql實現資料庫讀寫分離實戰

Mysql+Mycat實現資料庫主從同步與讀寫分離

MySQL效能優化

史上最全的MySQL高效能優化實戰總結！

MySQL索引原理：MySQL 的索引是什麼？怎麼優化？

顧名思義，B-tree索引使用B-tree的資料結構儲存資料，不同的儲存引擎以不同的方式使用B-Tree索引，比如MyISAM使用字首壓縮技術使得索引空間更小，而InnoDB則按照原資料格式儲存，且MyISAM索引在索引中記錄了對應資料的物理位置，而InnoDB則在索引中記錄了對應的主鍵數值。B-Tree通常意味著所有的值都是按順序儲存，並且每個葉子頁到根的距離相同。
B-Tree索引驅使儲存引擎不再通過全表掃描獲取資料，而是從索引的根節點開始查詢，在根節點和中間節點都存放了指向下層節點的指標，通過比較節點頁的值和要查詢值可以找到合適的指標進入下層子節點，直到最下層的葉子節點，最終的結果就是要麼找到對應的值，要麼找不到對應的值。整個B-tree樹的深度和表的大小直接相關。
全鍵值匹配：和索引中的所有列都進行匹配，比如查詢姓名為zhang san，出生於1982-1-1的人
匹配最左字首：和索引中的最左邊的列進行匹配，比如查詢所有姓為zhang的人
匹配列字首：匹配索引最左邊列的開頭部分，比如查詢所有以z開頭的姓名的人
匹配範圍值：匹配索引列的範圍區域值，比如查詢姓在li和wang之間的人
精確匹配左邊列並範圍匹配右邊的列：比如查詢所有姓為Zhang，且名字以K開頭的人
只訪問索引的查詢：查詢結果完全可以通過索引獲得，也叫做覆蓋索引，比如查詢所有姓為zhang的人的姓名
MySQL 常用30種SQL查詢語句優化方法|
MySQL太慢？試試這些診斷思路和工具
MySQL 效能優化的 9 種姿勢，面試再也不怕了！

MySQL表分割槽介紹：一文徹底搞懂MySQL分割槽

可以允許在⼀個表⾥儲存更多的資料，突破磁碟限制或者⽂件系統限制。
對於從表⾥將過期或歷史的資料移除在表分割槽很容易實現，只要將對應的分割槽移除即可
對某些查詢和修改語句來說，可以⾃動將資料範圍縮⼩到⼀個或⼏個表分割槽上，優化語句執⾏效率。⽽且可以通過顯示指定表分割槽來執⾏語句，⽐如 select * from temp partition(p1,p2) where store\_id < 5;
表分割槽是將⼀個表的資料按照⼀定的規則⽔平劃分為不同的邏輯塊，並分別進⾏物理儲存，這個規則就叫做分割槽函式，可以有不同的分割槽規則。
MySQL5.7版本可以通過show plugins語句檢視當前MySQL是否⽀持表分割槽功能。
MySQL8.0版本移除了show plugins⾥對partition的顯示，但社群版本的表分割槽功能是預設開啟的。
但當表中含有主鍵或唯⼀鍵時，則每個被⽤作分割槽函式的欄位必須是表中唯⼀鍵和主鍵的全部或⼀部分，否則就⽆法建立分割槽表。

MySQL分庫分表

能不分就不分，1000萬以內的表，不建議分片，通過合適的索引，讀寫分離等方式，可以很好的解決效能問題。
分片數量儘量少，分片儘量均勻分佈在多個DataHost上，因為一個查詢SQL跨分片越多，則總體效能越差，雖然要好於所有資料在一個分片的結果，只在必要的時候進行擴容，增加分片數量。
分片規則需要慎重選擇，分片規則的選擇，需要考慮資料的增長模式，資料的訪問模式，分片關聯性問題，以及分片擴容問題，最近的分片策略為範圍分片，列舉分片，一致性Hash分片，這幾種分片都有利於擴容。
儘量不要在一個事務中的SQL跨越多個分片，分散式事務一直是個不好處理的問題。
查詢條件儘量優化，儘量避免Select * 的方式，大量資料結果集下，會消耗大量頻寬和CPU資源，查詢儘量避免返回大量結果集，並且儘量為頻繁使用的查詢語句建立索引。

資料庫分庫分表概述：資料庫分庫分表，何時分？怎樣分？

Mysql分庫分表方案：MySQL 分庫分表方案，總結的非常好！

Mysql分庫分表的思路：解救 DBA—資料庫分庫分表思路及案例分析

MySQL效能監控

MySQL效能監控的指標大體可以分為以下4大類：

查詢吞吐量
查詢延遲與錯誤
客戶端連線與錯誤
緩衝池利用率

對於MySQL效能監控，官方也提供了相關的服務外掛：MySQL-Percona，下面簡單介紹一下外掛的安裝

[root@db01 ~]# yum -y install php php-mysql
[root@db01 ~]# wget https://www.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.8/binary/redhat/7/x86_64/percona-zabbix-templates-1.1.8-1.noarch.rpm
[root@db01 ~]# rpm -ivh percona-zabbix-templates-1.1.8-1.noarch.rpm
warning: percona-zabbix-templates-1.1.8-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ################################# [100%]
Updating / installing...
   1:percona-zabbix-templates-1.1.8-1 ################################# [100%]

Scripts are installed to /var/lib/zabbix/percona/scripts
Templates are installed to /var/lib/zabbix/percona/templates

最後，可以配合其它監控工具來實現對MySQL的效能監控。

MySQL伺服器配置外掛：

修改php指令碼連線MySQL的monitor@localhost使用者

修改MySQL的sock檔案路徑

[root@db01 ~]# sed -i '30c $mysql_user = "monitor";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php
[root@db01 ~]# sed -i '31c $mysql_pass = "123456";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php
[root@db01 ~]# sed -i '33c $mysql_socket = "/tmp/mysql.sock";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php

測試是否可用（可以從MySQL中獲取到監控值）

[root@db01 ~]# /usr/bin/php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host localhost --items gg
gg:12

# 確保當前檔案的 屬主 屬組 是zabbix，否則zabbix監控取值錯誤。
[root@db01 ~]# ll -sh /tmp/localhost-mysql_cacti_stats.txt
4.0K -rw-rw-r-- 1 zabbix zabbix 1.3K Dec 5 17:34 /tmp/localhost-mysql_cacti_stats.txt

移動zabbix-agent配置檔案到 /etc/zabbix/zabbix_agentd.d/目錄

[root@db01 ~]# mv /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.d/
[root@db01 ~]# systemctl restart zabbix-agent.service

匯入並配置Zabbix模板與主機：

預設模板監控時間為 5分鐘（當前測試修改為 30s）同時也要修改Zabbix模板時間

# 如果要修改監控獲取值的時間不但要在zabbix皮膚修改取值時間，bash指令碼也要修改。
[root@db01 scripts]# sed -n '/TIMEFLM/p' /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh
TIMEFLM=`stat -c %Y /tmp/$HOST-mysql_cacti_stats.txt`
if [ `expr $TIMENOW - $TIMEFLM` -gt 300 ]; then   
# 這個 300 代表 300s 同時也要修改。

預設模板版本為2.0.9，無法在4.0版本使用，可以先從3.0版本匯出，然後再匯入4.0版本。

Zabbix自帶模板監控MySQL服務

其實，在實際生產過程中，還是有相關的專業監控資料庫的第三方開源軟體的，民工哥之前也寫過相關的文章，今天發出來供大家參考：強大的開源企業級資料庫監控利器Lepus

MySQL 管理工具

MySQL 是最廣泛使用和流行的開源資料庫之一，圍繞它有許多工具，可以讓設計，建立和管理資料庫的過程變得更加容易和便捷。但是如何選擇最適合自己需求的工具，並不容易。這裡為大家推薦：10款MySQL的GUI工具，它們對開發人員和DBA來說都是不錯的解決方案。

很早之前民工哥就給大家介紹過一款開源的SQL管理工具：自動補全、回滾！介紹一款視覺化 sql 診斷利器。

今天，民工哥再給大家推薦一款SQL稽核利器： MySQL 自動化運維工具 goinception。

視覺化管理工具，大家可以試試這個：介紹一款免費好用的視覺化資料庫管理工具

俗話說工欲善其事，必先利其器，定期對你的MYSQL資料庫進行一個體檢，是保證資料庫安全執行的重要手段，因為，好的工具是使你的工作效率倍增！

今天和大家分享幾個mysql 優化的工具，你可以使用它們對你的mysql進行一個體檢，生成awr報告，讓你從整體上把握你的資料庫的效能情況。

效能優化診斷工具：別小看這幾個工具！關鍵時能幫你快速解決資料庫瓶頸

MySQL 常見錯誤程式碼說明

先給大家看幾個例項的錯誤分析與解決方案。

1.ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysql/mysql.sock'

問題分析：可能是資料庫沒有啟動或者是埠被防火牆禁止。

解決方法：啟動資料庫或者防火牆開放資料庫監聽埠。

2.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

問題分析：密碼不正確或者沒有許可權訪問。

解決方法：

1）修改 my.cnf 主配置檔案，在[mysqld]下新增 skip-grant-tables，重啟資料庫。最後修改密碼命令如下：

mysql> use mysql;
mysql> update user set password=password("123456") where user="root";

再刪除剛剛新增的 skip-grant-tables 引數，再重啟資料庫，使用新密碼即可登入。

2）重新授權，命令如下:

mysql> grant all on *.* to 'root'@'mysql-server' identified by '123456';

3.客戶端報 Too many connections

問題分析：連線數超出 Mysql 的最大連線限制。

解決方法：

1、在 my.cnf 配置檔案裡面增加連線數，然後重啟 MySQL 服務。max_connections = 10000
2、臨時修改最大連線數，重啟後不生效。需要在 my.cnf 裡面修改配置檔案，下次重啟生效。
```
set GLOBAL max_connections=10000;
```
4.Warning: World-writable config file '/etc/my.cnf' is ignored ERROR! MySQL is running but PID file could not be found

問題分析：MySQL 的配置檔案/etc/my.cnf 許可權不對。

解決方法：

chmod 644 /et/my.cnf

5.InnoDB: Error: page 14178 log sequence number 29455369832 InnoDB: is in the future! Current system log sequence number 29455369832

問題分析：innodb 資料檔案損壞。

解決方法：修改 my.cnf 配置檔案，在[mysqld]下新增 innodb_force_recovery=4, 啟動資料庫後備份資料檔案，然後去掉該引數，利用備份檔案恢復資料。

6.從庫的 Slave_IO_Running 為 NO

問題分析：主庫和從庫的 server-id 值一樣.

解決方法：修改從庫的 server-id 的值，修改為和主庫不一樣，比主庫低。修改完後重啟，再同步即可！

7.從庫的 Slave_IO_Running 為 NO問題

問題分析：造成從庫執行緒為 NO 的原因會有很多，主要原因是主鍵衝突或者主庫刪除或更新資料，從庫找不到記錄，資料被修改導致。通常狀態碼報錯有 1007、1032、1062、1452 等。

解決方法一：

mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;

解決方法二：設定使用者許可權，設定從庫只讀許可權

set global read_only=true;
8.Error initializing relay log position: I/O error reading the header from the binary log

分析問題：從庫的中繼日誌 relay-bin 損壞.
解決方法：手工修復，重新找到同步的 binlog 和 pos 點，然後重新同步即可。

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx;

維護過MySQL的運維或DBA都知道，經常會遇到的一些錯誤資訊中有一些類似10xx的程式碼。

Replicate_Wild_Ignore_Table:
         Last_Errno: 1032
         Last_Error: Could not execute Update_rows event on table xuanzhi.test; Can't find record in 'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000004, end_log_pos 3704

但是，如果不深究或者之前遇到過，還真不太清楚，這些程式碼具體的含義是什麼？這也給我們排錯造成了一定的阻礙。

所以，今天民工哥就把主從同步過程中一些常見的錯誤程式碼，它的具體說明給大家整理出來了:建議收藏備查！MySQL 常見錯誤程式碼說明

MySQL 開發規範與使用技巧

命名規範

1.庫名、表名、欄位名必須使用小寫字母，並採用下劃線分割。
- a)MySQL有配置引數lower_case_table_names，不可動態更改，Linux系統預設為 0，即庫表名以實際情況儲存，大小寫敏感。如果是1，以小寫儲存，大小寫不敏感。如果是2，以實際情況儲存，但以小寫比較。
- b)如果大小寫混合使用，可能存在abc，Abc，ABC等多個表共存，容易導致混亂。
- c)欄位名顯示區分大小寫，但實際使⽤用不區分，即不可以建立兩個名字一樣但大小寫不一樣的欄位。
- d)為了統一規範，庫名、表名、欄位名使用小寫字母。
2.庫名、表名、欄位名禁止超過32個字元。
- 庫名、表名、欄位名支援最多64個字元，但為了統一規範、易於辨識以及減少傳輸量，禁止超過32個字元。
3.使用INNODB儲存引擎。
- INNODB引擎是MySQL5.5版本以後的預設引擘，支援事務、行級鎖，有更好的資料恢復能力、更好的併發效能，同時對多核、大記憶體、SSD等硬體支援更好，支援資料熱備份等，因此INNODB相比MyISAM有明顯優勢。
4.庫名、表名、欄位名禁止使用MySQL保留字。
- 當庫名、表名、欄位名等屬性含有保留字時，SQL語句必須用反引號引用屬性名稱，這將使得SQL語句書寫、SHELL指令碼中變數的轉義等變得⾮非常複雜。
5.禁止使用分割槽表。
- 分割槽表對分割槽鍵有嚴格要求；分割槽表在表變大後，執⾏行DDL、SHARDING、單表恢復等都變得更加困難。因此禁止使用分割槽表，並建議業務端手動SHARDING。
6.建議使用UNSIGNED儲存非負數值。
- 同樣的位元組數，非負儲存的數值範圍更大。如TINYINT有符號為 -128-127，無符號為0-255。
7.建議使用INT UNSIGNED儲存IPV4。
- 用UNSINGED INT儲存IP地址佔用4位元組，CHAR(15)則佔用15位元組。另外，計算機處理整數型別比字串型別快。使用INT UNSIGNED而不是CHAR(15)來儲存IPV4地址，通過MySQL函式inet_ntoa和inet_aton來進行轉化。IPv6地址目前沒有轉化函式，需要使用DECIMAL或兩個BIGINT來儲存。

例如：

SELECT INET_ATON('209.207.224.40'); 3520061480SELECT INET_NTOA(3520061480);
209.207.224.40

8.強烈建議使用TINYINT來代替ENUM型別。
- ENUM型別在需要修改或增加列舉值時，需要線上DDL，成本較高；ENUM列值如果含有數字型別，可能會引起預設值混淆。
9.使用VARBINARY儲存大小寫敏感的變長字串或二進位制內容。
- VARBINARY預設區分大小寫，沒有字符集概念，速度快。
10.INT型別固定佔用4位元組儲存
- 例如INT(4)僅代表顯示字元寬度為4位，不代表儲存長度。數值型別括號後面的數字只是表示寬度而跟儲存範圍沒有關係，比如INT(3)預設顯示3位，空格補齊，超出時正常顯示，Python、Java客戶端等不具備這個功能。
11.區分使用DATETIME和TIMESTAMP。
- 儲存年使用YEAR型別。儲存日期使用DATE型別。儲存時間(精確到秒)建議使用TIMESTAMP型別。
- DATETIME和TIMESTAMP都是精確到秒，優先選擇TIMESTAMP，因為TIMESTAMP只有4個位元組，而DATETIME8個位元組。同時TIMESTAMP具有自動賦值以及⾃自動更新的特性。注意：在5.5和之前的版本中，如果一個表中有多個timestamp列，那麼最多隻能有一列能具有自動更新功能。

如何使用TIMESTAMP的自動賦值屬性?

a)自動初始化，而且自動更新：
column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP

b)只是自動初始化：
column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP

c)自動更新，初始化的值為0：
column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

d)初始化的值為0：
column1 TIMESTAMP DEFAULT 0

12.索引欄位均定義為NOT NULL。
- a)對錶的每一行，每個為NULL的列都需要額外的空間來標識。
- b)B樹索引時不會儲存NULL值，所以如果索引欄位可以為NULL，索引效率會下降。
- c)建議用0、特殊值或空串代替NULL值。

詳細的可參閱以下文章

MySQL 高頻企業面試題

學好知識，當然就得去面試，進大廠，拿高薪。但是進入面試之前，必要的準備是必須的，刷題是其中之一。

因此決定搞一個MySQL靈魂100問,試著用回答問題的方式,讓自己對知識點的理解更加深入一點.

此文不會事無鉅細的從select的用法開始講解mysql,主要針對的是開發人員需要知道的一些MySQL的知識點,主要包括索引,事務,優化等方面,以在面試中高頻的問句形式給出答案.

MySQL使用者行為安全

假設這麼一個情況，你是某公司mysql-DBA，某日突然公司資料庫中的所有被人為刪了。
儘管有資料備份，但是因服務停止而造成的損失上千萬，現在公司需要查出那個做刪除操作的人。
但是擁有資料庫操作許可權的人很多，如何排查，證據又在哪？
是不是覺得無能為力？
mysql本身並沒有操作審計的功能，那是不是意味著遇到這種情況只能自認倒黴呢？

民工哥技術之路公眾號不定期更新MySQL技術知識體系，大家可以關注我查閱 MySQL技術專欄學習更多的MySQL知識。

也歡迎點贊、轉發支援，因水平有限，如有錯誤請留言指正，為感！！！

誰再說學不會 MySQL 資料庫，就把這個給他扔過去！

資料庫是什麼

DBMS 的重要性

DBMS 的種類

SQL 語句及其種類

SQL 的基本書寫規則

資料型別

MySQL 資料庫簡介

MySQL 安裝

MySQL基礎入門操作

Windows服務

連線與斷開伺服器

資料庫操作

表的操作

MySQL 多例項配置

MySQL 主從同步複製

複製概述

複製解決的問題

複製如何工作

MySQL 複製有兩種方法：

MySQL複製有多種型別：

MySQL 資料備份與恢復

資料備份多種方式：

MySQL 高可用架構設計與實戰

先來了解一下MySQL高可用架構簡介：淺談MySQL叢集高可用架構

MySQL高可用方案：MySQL 同步複製及高可用方案總結

官方也提供一種高可用方案：官方工具｜MySQL Router 高可用原理與實戰

MHA

MGR

MySQL 資料庫讀寫分離高可用

MySQL效能優化

史上最全的MySQL高效能優化實戰總結！

MySQL索引原理：MySQL 的索引是什麼？怎麼優化？

MySQL表分割槽介紹：一文徹底搞懂MySQL分割槽

MySQL分庫分表

MySQL效能監控

MySQL 管理工具

MySQL 常見錯誤程式碼說明

MySQL 開發規範與使用技巧

命名規範

MySQL 高頻企業面試題

Linux運維必會的100道MySql面試題之（一）

Linux運維必會的100道MySql面試題之（二）

Linux運維必會的100道MySql面試題之（三）

Linux運維必會的100道MySql面試題之（四）

MySQL使用者行為安全

相關文章