1.主要內容

1)mysql的架構,鎖,最佳實踐,事務

2)mysql的命令行,主從復制,索引,數據備份/恢復

3)mysql的集群,mysql的負載均衡,監控,mysql proxy

4) mysql的MMM架構,mysql 的源碼,DBA

MySQL-從刪除到跑路

2.具體內容

2.1)【架構】：

【架構圖】：

MySQL-MySQL的架構圖

【概述】：Mysql是由SQL接口,解析器,優化器,緩存,存儲引擎組成的

2.2)【鎖】：

【概念】：為了保證數據的一致完整性,任何一個數據庫都存在鎖定機制.鎖定機制的優劣直接應想到一個數據庫系統的并發處理能力和性能,所以鎖定機制的實現也就成為了各種數據庫的核心技術之一.

【分類】：MySQL各存儲引擎使用了三種類型(級別)的鎖定機制：行級鎖定,頁級鎖定和表級鎖定

1)行級鎖定：鎖定對象的顆粒度很小,消耗最大,行級鎖定也最容易發生死鎖,但是并發好

2)表級鎖定：死鎖情況少,但是并發不好,

3)頁級鎖定：介于前兩者之間.

【代碼】：

1)鎖定 LOCK TABLES tbl_name [AS alias]

2)解鎖 UNLOCK TABLES

2.3)【最佳實踐】：

【為查詢緩存優化你的查詢】：

【mysql強制索引和禁止某個索引】：

1) mysql強制使用索引:force index(索引名或者主鍵PRI)：select * from table force index(PRI,ziduan1_index) limit 2;(強制使用索引”PRI和ziduan1_index”)

2) mysql禁止某個索引：ignore index(索引名或者主鍵PRI)：select * from table ignore index(PRI,ziduan1_index) limit 2;(禁止使用索引”PRI,ziduan1_index”)

2.4)【事務】：

【概念】：事務是指邏輯上的一組操作,組成這組操作的各個單元,要不全成功要不全失敗.其特點是ACID(原子性,一致性,隔離性,持久性)

【實現】：

1)事務開啟：START TRANSACTION; 或者 BEGIN;

2)事務提交：COMMIT;

3)事務回滾：ROLLBACK;

2.5)【mysql 命令行】：

【命令行執行mysql】:使用mysql的-e參數可以執行各種sql的(創建,刪除,增,刪,改、查)等各種操作

例如：#創建數據庫

create_db_sql="create database IF NOT EXISTS ${DBNAME}”

mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"

2.6)【主從復制】：

【一主多從】：在一主多從的數據庫體系中,多個從服務器采用異步的方式更新主數據庫的變化,業務服務器在執行寫或者相關修改數據庫的操作是在主服務器上進行的,讀操作則是在各從服務器上進行

MySQL-MySQL的主從復制

【原理】 ：MySQL之間數據復制的基礎是二進制日志文件(binary log file).一臺MySQL數據庫一旦啟用二進制日志后,其作為master,它的數據庫中所有操作都會以“事件”的方式記錄在二進制日志中,其他數據庫作為slave通過一個I/O線程與主服務器保持通信,并監控master的二進制日志文件的變化,如果發現master二進制日志文件發生變化,則會把變化復制到自己的中繼日志中,然后slave的一個SQL線程會把相關的“事件”執行到自己的數據庫中,以此實現從數據庫和主數據庫的一致性,也就實現了主從復制.

2.7)【索引】：

【概述】：索引對查詢的速度有著至關重要的影響,理解索引也是進行數據庫性能調優的起點

【種類】：普通索引,唯一索引,全文索引,單列/多列索引,組合索引(最左前綴)

【B+樹】：

MySQL-索引B+樹結構

1)詳解B+樹：淺藍色的塊我們稱之為一個磁盤塊,可以看到每個磁盤塊包含幾個數據項(深藍色所示)和指針(黃色所示),如磁盤塊1包含數據項17和35,包含指針P1、P2、P3,P1表示小于17的磁盤塊,P2表示在17和35之間的磁盤塊,P3表示大于35的磁盤塊.真實的數據存在于葉子節點即3、5、9、10、13、15、28、29、36、60、75、79、90、99.非葉子節點只不存儲真實的數據,只存儲指引搜索方向的數據項,如17、35并不真實存在于數據表中.

2)查找B+樹：如果要查找數據項29,那么首先會把磁盤塊1由磁盤加載到內存,此時發生一次IO,在內存中用二分查找確定29在17和35之間,鎖定磁盤塊1的P2指針,內存時間因為非常短(相比磁盤的IO)可以忽略不計,通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內存,發生第二次IO,29在26和30之間,鎖定磁盤塊3的P2指針,通過指針加載磁盤塊8到內存,發生第三次IO,同時內存中做二分查找找到29,結束查詢,總計三次IO.真實的情況是,3層的b+樹可以表示上百萬的數據,如果上百萬的數據查找只需要三次IO,性能提高將是巨大的,如果沒有索引,每個數據項都要發生一次IO,那么總共需要百萬次的IO,顯然成本非常非常高.

2.8)【數據備份/恢復】：

【數據備份】：

1)使用mysqldump命令備份mysqldump -u username -p --databases dbname2 dbname2 > Backup.sql

2)直接復制整個數據庫目錄 ： 需要停止服務,不支持InnoDB引擎

3)熱備份：使用mysqlhotcopy工具快速備份,不用停止服務. mysqlhotcopy [option] dbname1 dbname2 backupDir/

【數據還原】：

1)還原使用mysqldump命令備份的數據庫： mysql -u root -p < C:\backup.sql

2)還原直接復制目錄的備份： 必須保證兩個MySQL數據庫的版本號是相同的.MyISAM類型的表有效,對于InnoDB類型的表不可用

2.9)【mysql的集群】：

【概念】：MySQL 集群(MySQL Cluster )是mysql適合于分布式計算環境的高實用、高冗余版本.它采用了NDB Cluster 存儲引擎,允許在1個 Cluster 中運行多個MySQL服務器.在MyQL 5.0及以上的二進制版本中、以及與最新的Linux版本兼容的RPM中提供了該存儲引擎.

MySQL-MySQL集群架構圖

【分類】：分為同步集群和異步集群.

1)同步集群：(mysql cluster)

結構：(data + sql + mgm節點)

特點：

1. 1) 內存級別的,對硬件要求較低,但是對內存要求較大.換算比例為：1：1.1；

1.2) 數據同時放在幾臺服務器上,冗余較好；

1.3) 速度一般；

1.4) 建表需要聲明為engine=ndbcluster

1.5) 擴展性強；

1.6) 可以實現高可用性和負載均衡,實現對大型應用的支持；

1.7) 必須是特定的mysql版本,如：已經編譯好的max版本；

1.8) 配置和管理方便,不會丟失數據

2)異步集群(mysql replication)

結構：(master + slave)

特點：

2.1) 主從數據庫異步數據；

2.2) 數據放在幾臺服務器上,冗余一般；

2.3) 速度較快；

2.4) 擴展性差；

2.5) 無法實現高可用性和負載均衡(只能在程序級別實現讀寫分離,減輕對主數據庫的壓力)；

2.6) 配置和管理較差,可能會丟失數據

2.10)【mysql的負載均衡】：

【定義】： 通過director,將用戶的請求分發到real server服務器上,然后返回給用戶.負載均衡部署靈活、能夠滿足各種需求.

MySQL-LVS+Keepalived實現mysql負載均衡

【實現方式】：

硬件：BIG/IP、Cisco、IBM(昂貴)

軟件：LVS(免費)

LVS系統將用戶的請求的數據包在數據層和網絡層進行了封裝和轉發,由三種方式滿足各種需求.

1) DR：直接路由

2) Tuning：tcp/ip隧道

3) NAT：網絡地址轉換

2.11)【mysql的監控】：

【監控內容】：監控數據庫的日常會話情況.碎片、剩余表空間監控,及時了解表空間的擴展情況、以及剩余空間分布情況

【代碼實現】：https://github.com/wozhuzaisi/shell-work/blob/master/mysqlmonitor.sh

2.12)【mysql proxy】：

【定義】：MySQL Proxy是一個處于你的client端和MySQL server端之間的簡單程序,它可以監測、分析或改變它們的通信.它使用靈活,沒有限制,常見的用途包括：負載平衡,故障、查詢分析,查詢過濾和修改等等

【實現讀寫分離】：

1)安裝并配置mysql proxy.

2)測試：主服務器mysql> grant all on *.* to'lin3615'@'192.168.179.142'identified by'123456’;

2.14)【mysql MMM架構】：

【概述】：是一套支持雙主故障切換和雙主日常管理的腳本程序.由于MMM無法完全的保證數據一致性,所以MMM適用于對數據的一致性要求不是很高,但是又想最大程度的保證業務可用性的場景.對于那些對數據的一致性要求很高的業務,非常不建議采用MMM這種高可用架構.

MySQL-MMM架構圖

2.15)【mysql 源碼】：

【內容】：MySQL是由C++寫的,入口函數main()直接調用了mysqld_main().Server啟動的代碼都在這里,直到最后的 handle_connections_sockets().

MySQL-MySQL的源碼第一書

【資料】：

下載pdf:《深入MySQL源碼》

官網手冊: https://dev.mysql.com/doc/internals/en/

閱讀源碼：https://zhuanlan.zhihu.com/p/26118810

書籍：《深入理解MySQL核心技術》

2.16)【DBA】：

【職責】：

MySQL-DBA思維導圖

1)數據庫安裝；

2)數據庫配置和管理；

3)權限設置和安全管理；

4)監控和性能調節；

5)備份和恢復；

6)解決一般的問題；

MySQL-DBA的一天