誰再說學不會 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資料庫入門———常用基礎命令
1047 行 MySQL 詳細學習筆記（值得學習與收藏）
MySQL基礎入門之常用命令介紹

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開發規范與使用技巧總結

MySQL 高頻企業面試題

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

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

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

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

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

以下內容主要受眾為開發人員,所以不涉及到MySQL的服務部署等操作,且內容較多,大家準備好耐心和瓜子礦泉水.

前一陣系統的學習了一下MySQL,也有一些實際操作經驗,偶然看到一篇和MySQL相關的面試文章,發現其中的一些問題自己也回答不好,雖然知識點大部分都知道,但是無法將知識串聯起來.

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

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

MySQL 高頻面試題，都在這了
史上最全的大廠Mysql面試題在這里
MySQL 資料庫面試題（2021最新版）

MySQL用戶行為安全

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

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

歡迎大家關注我的微信公眾號民工哥技術之路（jishuroad）共同學習與交流

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/413161.html

標籤：MySQL

上一篇：【SQL實戰】期末考試，如何統計學生成績

下一篇：盤點 | 常用 PG 資料恢復方案概覽【建議收藏】