贊！7000 字學習筆記，一天搞定 MySQL

来源:https://www.cnblogs.com/youkanyouxiao/archive/2020/07/06/13255411.html
-Advertisement-
Play Games

MySQL資料庫簡介 MySQL近兩年一直穩居第二，隨時有可能超過Oracle計晉升為第一名，因為MySQL的性能一直在被優化，同時安全機制也是逐漸成熟，更重要的是開源免費的。 MySQL是一種關係資料庫管理系統，關係資料庫將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，這樣就增加了速度並 ...


MySQL資料庫簡介

MySQL近兩年一直穩居第二，隨時有可能超過Oracle計晉升為第一名，因為MySQL的性能一直在被優化，同時安全機制也是逐漸成熟，更重要的是開源免費的。

MySQL是一種關係資料庫管理系統，關係資料庫將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，這樣就增加了速度並提高了靈活性。

MySQL所使用的 SQL 語言是用於訪問資料庫的最常用標準化語言。MySQL 軟體採用了雙授權政策，分為社區版和商業版，由於其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，一般中小型網站的開發都選擇 MySQL 作為網站資料庫。

如果不會安裝MySQL請移步：MySQL服務安裝

MySQL InnoDB存儲引擎

  • 存儲引擎InnoDB是目前MySQL版本預設的存儲引擎，也是MySQL推薦使用的存儲引擎，是集高可靠性和高性能於一身的存儲引擎。

  • 在MySQL5.7版本中，除非在配置文件中顯視指定default storage engine或者創建表時顯視使用engine=語句指定其它的存儲引擎，否則預設都是InnoDB。

InnoDB存儲引擎的優勢：

  • DML語句支持事務功能，保證ACID特性

  • 行級鎖的使用保證了高併發的屬性

  • InnoDB對有主鍵的表會依據主鍵優化查詢性能，也稱聚簇索引，將所有數據存儲在聚簇索引上以減少對主鍵查詢的IO消耗

  • 為保證數據的一致性，InnoDB還支持外鍵屬性，確保有外鍵約束的表之間不會有不一致的數據

  • 當伺服器硬體或者軟體故障導致MySQL重啟後，InnoDB會自動識別已經在故障之前提交的數據，並回退所有故障時未提交的數據，最大限度的保護數據不會丟失(crash recovery)

1、事物（Transaction）
2、MVCC（多版本併發控制）
3、行級鎖（Row-level Lock）
4、支持外鍵
5、ACSR（Auto Crrash safe Recovery）自動的故障安全恢復
6、支持熱備份

MySQL複製集群原理與實戰

MySQL複製有兩種方法：

  • 傳統方式：基於主庫的bin-log將日誌事件和事件位置複製到從庫，從庫再加以 應用來達到主從同步的目的。

  • Gtid方式：global transaction identifiers是基於事務來複制數據，因此也就不 依賴日誌文件位置，同時又能更好的保證主從庫數據一致性。

MySQL資料庫主從同步實戰過程

MySQL 主從同步架構中你不知道的“坑”（上）

MySQL 主從同步架構中你不知道的“坑”（下）

數據備份多種方式：

  • 物理備份是指通過拷貝資料庫文件的方式完成備份，這種備份方式適用於資料庫很大，數據重要且需要快速恢復的資料庫

  • 邏輯備份是指通過備份資料庫的邏輯結構（create database/table語句)和數據內容（insert語句或者文本文件）的方式完成備份。這種備份方式適用於資料庫不是很大，或者你需要對導出的文件做一定的修改，又或者是希望在另外的不同類型伺服器上重新建立此資料庫的情況

  • 通常情況下物理備份的速度要快於邏輯備份，另外物理備份的備份和恢復粒度範圍為整個資料庫或者是單個文件。對單表是否有恢復能力取決於存儲引擎，比如在MyISAM存儲引擎下每個表對應了獨立的文件，可以單獨恢復；但對於InnoDB存儲引擎表來說，可能每個表示對應了獨立的文件，也可能表使用了共用數據文件

  • 物理備份通常要求在資料庫關閉的情況下執行，但如果是在資料庫運行情況下執行，則要求備份期間資料庫不能修改

  • 邏輯備份的速度要慢於物理備份，是因為邏輯備份需要訪問資料庫並將內容轉化成邏輯備份需要的格式；通常輸出的備份文件大小也要比物理備份大；另外邏輯備份也不包含資料庫的配置文件和日誌文件內容；備份和恢復的粒度可以是所有資料庫，也可以是單個資料庫，也可以是單個表；邏輯備份需要再資料庫運行的狀態下執行；它的執行工具可以是mysqldump或者是select … into outfile兩種方式

送你一份生產資料庫備份方案：高逼格企業級MySQL資料庫備份方案

MySQL資料庫物理備份方式：Xtrabackup實現數據的備份與恢復

MySQL複製有多種類型：

  • 非同步複製：一個主庫，一個或多個從庫，數據非同步同步到從庫。

  • 同步複製：在MySQL Cluster中特有的複製方式。

  • 半同步複製：在非同步複製的基礎上，確保任何一個主庫上的事務在提交之前至 少有一個從庫已經收到該事務並日誌記錄下來。

  • 延遲複製：在非同步複製的基礎上，人為設定主庫和從庫的數據同步延遲時間， 即保證數據延遲至少是這個參數。

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性能優化

史上最全的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表分區介紹：一文徹底搞懂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資料庫讀寫分離高可用

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

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

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

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

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版本 。

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

MySQL用戶行為安全

  • 假設這麼一個情況，你是某公司mysql-DBA，某日突然公司資料庫中的所有被人為刪了。

  • 儘管有數據備份，但是因服務停止而造成的損失上千萬，現在公司需要查出那個做刪除操作的人。

  • 但是擁有資料庫操作許可權的人很多，如何排查，證據又在哪？

  • 是不是覺得無能為力？

  • mysql本身並沒有操作審計的功能，那是不是意味著遇到這種情況只能自認倒霉呢？

學完了就需要出去練一練，最後給大家一些企業面試題供大家練練手：24 個必須掌握的資料庫面試問題！

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Nginx安裝解決報錯
    安裝包： nginx-1.13.7.tar.gz openssl-1.1.0g.tar.gz pcre-8.41.tar.gz zlib-1.2.11.tar.gz 新建文件夾：nginx 註：我沒有新建nginx的時候，運行make命令的時候，報瞭如下error。新建nginx後沒有報錯 將安裝包 ...
  • 【Linux】（一）美化Linux終端之oh-my-zsh開源項目(Linux終端主題)
    使用開源項目oh-my-zsh，讓你的終端界面炫酷。配置簡單。 1、查看系統是否裝了zsh //查看當前使用的shell echo $SHELL //查看系統是否裝了zsh cat /etc/shells 2、安裝zsh（系統沒有查到zsh，則安裝） yum -y install zsh 或者 su ...
  • 雙埠SRAM如何提高系統的整體性能
    SRAM 以其高速、靜態的優點廣泛應用於各種數字設備中，多被用作不同部件之間的緩衝，尤其在電腦體系架構中扮演著重要的角色，即嵌入到CPU 內部的高速緩存（Cache）。電腦的處理速度在高速增長，為了提供足夠的數據緩存能力，隨著集成電路製造工藝的發展，嵌入式SRAM 的存儲單元的面積也在以約0.5 ...
  • 將vim打造為強大的IDE
    前言：首先需要有 vim python2或3 git 下載:sudo apt-get install xxx 一.安裝插件管理器vim-plug(親測比vundle好用-個人觀點) 1.檢查當前用戶下是否有vim文件夾:~/.vim 沒有,自己創建: mkdir ~/.vim 2.創建相應的文件夾( ...
  • 一文說清 KubeSphere 容器平臺的價值
    KubeSphere 作為雲原生家族 後起之秀，開源近兩年的時間以來收穫了諸多用戶與開發者的認可。本文通過大白話從零詮釋 KubeSphere 的定位與價值，以及不同團隊為什麼會選擇 KubeSphere。 對於企業 KubeSphere 是什麼 KubeSphere 是在 Kubernetes 之 ...
  • Spark-submit常用任務命令參數和說明
    Spark常用任務命令參數和說明 spark-submit \ --name task2018072711591669 \ --master yarn --deploy-mode client \ --jars sparklistener-0.0.3-SNAPSHOT.jar \ --conf sp ...
  • HBase寫入異常RejectedExecutionException
    HBase在大數據量併發寫入時，寫一段時間後HBase監控界面出現告警，寫入程式日誌里頻繁出現異常java.util.concurrent.RejectedExecutionException ...
  • Spark TempView和GlobalTempView的區別
    Spark TempView和GlobalTempView的區別 TempView和GlobalTempView在spark的Dataframe中經常使用，兩者的區別和應用場景有什麼不同。 我們以下麵的例子比較下兩者的不同。 from pyspark.sql import SparkSession ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類