MySQL架構的優化

Panda_XiaoXi發表於2019-03-01
MySql架構優化

  • mysql的複製：

      實現在不同伺服器上的分佈：
  	利用二進位制日誌增量進行；
  	不需要太多的頻寬，但是使用基於行的複製在進行大批量的更改時，會對頻寬帶來一定的壓力，特別是跨IDC環境下進行的複製；	
  	應該分批進行
  實現資料讀取的負載均衡，需要其他元件配合使用
  增加資料的安全性
  實現資料庫高可用和故障切換
  實現資料庫線上升級
複製程式碼

  • mysql的二進位制日誌：記錄了所有對MySQL資料庫的資料增刪查改和對錶和資料庫的修改

  • binlog命令列的工具進行檢視

    二進位制日誌格式：

      基於段的日誌格式： binlog_format=STATEMENT

  基於行的日誌格式： binlog_format=ROW
  	binlog_row_image=[FULL|MINIMAL|NOBLOB]

  mysqlbinlog -vv 日誌檔名稱；
  
  混合日誌格式：binlog_format=MIXED
  	根據sql語句由系統決定使用基於段還是基於行的日誌格式；
  	資料量的大小由所執行的sql語句決定
複製程式碼

    建議使用binlog_format=MIXED 或者 binlog_format=ROW並設定binlog_row_image=MINIMAL

  • 基於SQL語句的複製(SBR)

    優點：
    生成的日誌量少，節約網路傳輸IO
    並不強制要求主從資料庫的表定義完全相同
    相比於基於行的複製方式更為靈活

    缺點：
    對於非確定性事件，無法保證主從資料的一致性
    對於儲存過程，觸發器，自定義函式進行的修改也可以造成資料不一致
    相對於基於行的複製方式在從上執行時需要更多的行鎖

  • 基於行的複製（PBR）

    優點：

      可以應用於任何SQL的複製包括非確定確定函式，儲存過程等；
  可以減少資料庫鎖的使用
複製程式碼

    缺點：

      要求主從資料庫的表的結構相同，負責可能會中斷複製；
  無法在從伺服器上單獨執行觸發器
複製程式碼

  • mysql複製的工作方式

mysql複製的工作方式
  1. 主伺服器將變更寫入到二進位制檔案中
  2. 從伺服器讀取主伺服器的二進位制日誌變更寫入到relay_log中
  3. 在從伺服器上重放rely_log中的日誌
基於日誌點的複製配置的步驟：
1.在主DB伺服器上建立複製賬號
	
	create user `repl` @ `IP段` identified by `password`;
	grant replication slave on *.* to `repl` @ `IP段`;//授權
	
2.配置主庫伺服器

	bin_log=mysql-bin
	server_id = 100

3.配置資料庫從伺服器
	
	bin_log = mysql-bin
	server_id = 101
	relay_log = mysql-relay-bin
	log_slave_update = on[可選]
	read_only = on[可選]

4.初始化從伺服器資料
	
	mysqldump --master-data=2 -single-transaction
	xtrabackup --slave-info[對innodb效能不進行鎖表]

5.啟動複製鏈路
	
	change master to master_host = `主伺服器的ip地址` ，
		master_user = `repl`，
		master_password = `password`，
		master_log_file = `mysql_log_file_name`，
		master_log_pos = 4; 
實際的配置：

	1.配置主庫：
		create user repl@`192.168.25.%` identified by `123456`;
		grant replication slave on *.* to repl@`192.168.25.%`;
	
	2.修改主從配置檔案

	3.主資料庫備份：mysqldump --single-transcation --master-data --triggers --			routines --all-databases >> all.sql

	4.將主庫的生成的備份檔案傳遞到從伺服器上：scp all.sql root@192.168.15.130:/root

	5.在從伺服器上執行該sql檔案： mysql -uroot -p < all.sql

	6.在從伺服器上配置資料鏈路：change master to master_host=`192.168.25.33`,
							master_user=`repl`,
							master_password=`123456`,
							master_log_file=`mysql-bin.0000003`,
							mater_log_pos=1829; 
	
	7.在從伺服器上啟動複製鏈路：start slave;
基於日誌點的複製優點：
	是mysql最早的複製技術，bug較少
	對於sql查詢沒有任何限制
	故障處理比較容易
基於日誌點的複製缺點：
	故障轉移時重新獲取新主的日誌點的資訊比較困難
複製程式碼
基於GTID的複製配置的步驟：
	1.在主DB伺服器上簡歷複製賬號
	
		create user `repl` @ `IP段` identified by `password`;
		grant replication slave on *.* to `repl` @ `IP段`;//授權
	
	2.配置主庫伺服器

		bin_log=mysql-bin
		server_id = 100
		gtid_mode = on
		
		enforce-gtid-constistency
			不支援以下操作：create table ... select
						  在事務中使用create temporary建立臨時表
						  使用關聯更新事務表和非事務表
		log-slave-updates = on[5.7版本之前加]

	3.配置資料庫從伺服器
	
		server_id = 101
		relay_log = mysql-relay-bin
		gtid_mode=on
		enforce-gtid-constistency
		read_only = on[可選]
		master_info_repository=table
		relay_log_info_repository=table
	
	4.初始化從伺服器資料
	
		mysqldump --master-data=2 -single-transaction
		xtrabackup --slave-info

	5.啟動複製鏈路
		
		change master to master_host = `主伺服器的ip地址` ，
			master_user = `repl`，
			master_password = `password`，
			master_auto_position=1;

基於gtid的複製的優點：
	可以很方便的進行故障的轉移
	從庫不會丟失主庫上的任何修改

基於gtid的複製的缺點：
	故障處理比較複雜
	對執行的SQL有一定的限制
複製程式碼

  • 如何選擇複製模式

    1. 所使用的MySQL版本
    2. 複製的架構及主從切換的方式
    3. 所使用的高可用管理元件
    4. 對應用的支援程度

  • MySQL複製拓撲結構

    MySQL5.7之前，一個從庫只能有一個主庫

    MySQL5.7之後支援一從多主架構

常見的拓撲的設計及設計的注意事項:

  • 一主多從的複製拓撲
一主多從的複製拓撲
優點：配置簡單
	 可以用多個從庫分擔讀負載

用途：為不同業務使用不同的從庫
	 將一臺從庫放到遠端的IDC中，用作災備恢復
	 分擔主庫的讀負載
複製程式碼
  • 主-主複製拓撲
主-主複製拓撲
缺點：
	產生資料衝突而造成複製鏈路的中斷
	耗費大量的時間
	造成資料的丟失

主主模式下的主主複製的配置的注意事項：
	兩個主中所操作的表最好能夠分開
	使用下面兩個引數自增ID的生成
		auto_increment_increment = 2
		auto_increment_offset = 1|2
複製程式碼
  • 主-備複製的拓撲
主-備複製的拓撲
特點：
	只有一臺主伺服器對外提供服務
	一臺伺服器處於只讀狀態並且只作為熱備使用
	在對外提供的主庫出現故障或是計劃性的維護時才會進行切換,使原來的備庫成為主庫，而原來的主庫會成為新的備庫並處理只讀或下線狀態，待維護完成後重新上線

主備模式下的主主複製的配置的注意事項：
	確保兩臺伺服器上的初始化的初始資料相同
	確保兩臺伺服器上已經啟動binlog並且有不同的server_id
	在兩臺伺服器上啟用log_slave_updates引數
	在初始的備庫上啟用read_only
複製程式碼
  • 帶從伺服器的主主複製的拓撲
帶從伺服器的主主複製的拓撲

  • 級聯複製的拓撲

    將一個主伺服器配置一個從伺服器通過從伺服器來分發進行復制

    mysql級聯複製

MySQL複製效能的優化

  • 影響主從延遲的因素
  1. 主庫寫入二進位制日誌的時間 –> 控制主庫中執行事務的大小，分隔大事務
  2. 二進位制日誌傳輸時間 –> 使用mixed日誌格式，設定set binlog_row_image=minimal;
  3. 預設情況下只有一個SQL執行緒，主上併發修改在從上變成了序列化 –> 使用多執行緒複製

  • 如何在MySQL5.7中配置資料庫的多執行緒的複製[MYSQL5.6中引入多執行緒的複製,在MySQL5.7中可以按照邏輯時鐘的方式來分配SQL執行緒]：

    1.stop slave //停止主從複製
    2.set global slave_parallel_type=`logical_clock`; //設定分配執行緒的方式
    3.set global slave_parallel_workers=4; //設定4個複製執行緒
    4.start slave;

MySQL複製常見的問題

  • 由於資料的損壞或者丟失所引起的主從複製錯誤

    主庫或者從庫的意外當機所引起的錯誤 –> 跳過二進位制日誌的事件/注入空事務的方式先恢復中斷的複製鏈路，再使用其他的方法來對比主從伺服器上的資料

    主庫上的二進位制日誌損壞 –> 通過change master命令來重新指定

    備庫上的中繼日誌損壞

    在從庫上進行資料修改造成的主從複製的錯誤

    不唯一的server_id或者server_uuid(server_uuid是記錄在資料目錄中的auto.cnf檔案中，一旦存在mysql不會生成server_uuid)

    max_allow_packet設定引起的主從複製錯誤

MySQL主從複製無法解決的問題

  • 無法分擔主資料庫的寫負載
  • 無法自動進行故障轉移及主從切換
  • 不提供讀寫分離的功能

利用主從複製搭建高可用的架構

  • 高可用性指的是通過儘量縮短因日常維護操作（計劃）和突發的系統崩潰（非計劃）所導致的體積世家你，以提高系統和應用的可用性。

  • 實現高可用

    》避免導致系統不可用的因素，減少系統不可用的時間[伺服器磁碟空間耗盡、效能低下的SQL、表結構和索引沒有優化、主從資料不一致、人為的操作失誤]

    》建立完善的監控及報警系統

    》對備份資料進行恢復測試

    》正確配置資料庫環境

    》對不需要的資料進行歸檔和清理

    》增加系統冗餘，保證發生系統不可用時可以儘快的恢復[避免存在單點故障、主從切換及故障轉移]

      利用sun共享儲存或者drdb磁碟複製解決MySQL單點故障
  利用多寫叢集或者NDB叢集來解決MySQL單點的故障
  利用MySQL的複製來解決MySQL的單點登入的問題
複製程式碼

MySQL資料庫的MMM架構

在主庫出現當機時進行故障轉移並自動配置其他從對新主的複製

提供了主，寫虛擬ip，在主從伺服器出現問題時可以自動遷移虛擬ip

MySQL資料庫的MMM架構
  • MMM部署所需資源
名稱資源 數量 說明
主DB伺服器 2 用於主備模式的主主複製配置
從DB伺服器 0-N 可以配置0臺或多臺從伺服器，但建議不要太多
監控伺服器 1 用於監控mysql監控叢集
IP地址 2*(n+1) n為MySQL伺服器的數量
監控使用者 1 用於監控資料庫狀態的MySQL使用者(replication client)
代理使用者 1 用於MMM代理的MySQL使用者(super,replication client,process)
複製使用者 1 用於配置MySQL複製的MySQL使用者(replication slave)
MMM架構的部署步驟

  1. 配置主主複製及主從同步叢集

  2. 安裝主從節點所需要的支援包（per依賴包）

  3. 安裝及配置MMM工具集

  4. 執行MMM監控

  5. 測試

MySQL高可用架構之MMM架構的詳細配置

MMM叢集的優缺點：

》MMM叢集的優點：

  1. 使用Perl指令碼語言開發及完全開源
  2. 提供了讀寫VIP(虛擬IP),使伺服器角色的變更對前端應用透明
  3. MMM提供了從伺服器的延遲監控
  4. MMM提供了主資料庫故障轉移後從伺服器對新主的重新同步的功能
  5. 很容易對發生故障的主資料庫重新上線
  6. MMM提供了從伺服器的延遲監控

》MMM叢集的缺點：

  1. 釋出時間比較早不支援MySQL新的複製功能
  2. 沒有讀負載均衡的功能
  3. 在進行主從切換時，容易造成資料丟失
  4. MMM監控服務存在單點故障

MySQL資料庫的MHA架構

監控主資料庫伺服器是否可用

當主DB不可用時，從多個從伺服器中選舉出新的主資料庫伺服器

提供了主從切換和故障轉移功能（MHA可以半同步功能結合起來使用）

MySQL架構的優化

  • MHA如何進行主從切換

    當主資料庫出現故障時，嘗試從出現故障的主資料庫儲存二進位制日誌

    從多個備選從伺服器中選舉出新的備選主伺服器

    在備選主伺服器和其它從伺服器之間同步差異二進位制資料

    應用從原主DB伺服器上儲存的二進位制日誌

    提升備選主DB伺服器為新的主DB伺服器

    遷移叢集中的其它從DB做為新的主DB的從伺服器

  • MHA支援GTID的複製和日誌點的複製

  • MMM不支援GTID的複製

MHA架構的部署步驟

  1. 配置叢集內所有伺服器的SSH的免認證登入

    ssh -keygen

    ssh-copy-id -i /root/.ssh/id_rsa `-p 22 root@192.168.3.100

    ssh-copy-id -i /root/.ssh/id_rsa `-p 22 root@192.168.3.101

    ssh-copy-id -i /root/.ssh/id_rsa `-p 22 root@192.168.3.102

  2. 安裝MHA-node軟體包和MHA-manager軟體包

  3. 安裝MHA的支援包

    監控節點：yum -y install perl-Config-Tiny.noarch perl-Time-HiRes.X86_64 perl-Parallel-ForkManager perl-Log-Dispatch-Perl.noarch per-DB-MySQL ncftp

    資料節點：yum -y install perl -DBD-MySQL ncftp perl-DBI.X86

  4. 建立主從複製叢集

  5. 配置MHA管理節點

  6. 使用master_check_ssh和masterha_check_repl對配置進行檢驗

  7. 啟動並測試MHA服務

MySQL高可用架構之MHA介紹及配置

MHA叢集的優缺點：

》MHA叢集的優點：

  1. 使用Perl指令碼語言開發及完全開源
  2. 可以支援就GTID的複製模式
  3. MHA在進行故障轉移時不容易產生資料丟失
  4. 同一個監控節點可以監控多個叢集

》MMM叢集的缺點：

  1. 需要編寫指令碼或利用第三方工具（keepalive等等）來實現VIP的配置
  2. MHA啟動後只會對主資料庫進行監控
  3. 需要基於SSH免認證配置，存在一定的安全隱患
  4. 沒有提供從伺服器的讀負載均衡的功能

資料庫的讀寫分離（如何在複製叢集的不同角色上，去執行不同的SQL語句）

程式實現的讀寫分離

優點：由開發人員控制什麼樣的查詢再從庫上來執行，因此比較靈活
	 由程式直接連線誒資料庫，所以效能損耗比較少

缺點：增加了開發的工作量，使得程式程式碼更為複雜人為控制，
	 容易出現錯誤
複製程式碼

中介軟體的讀寫分離(mysql-proxy、maxScale)

優點：中介軟體根據查詢語法的分析，自動完成讀寫分離；
	 對程式透明，對已有的程式不用做任何的調整。

缺點：增加了中間層，所以對查詢效率有損耗；
	 對於延遲敏感業務無法在主庫上執行
複製程式碼

實現讀的負載均衡（具有相同角色的資料庫，如何共同分擔相同的負載）

軟體：LVS、Haproxy、MaxScale

硬體：F5

maxScale外掛:

maxScale外掛體系結構
  1. Authentication 認證外掛
  2. Protocal 協議外掛
  3. Router 路由外掛（readconnroute、readwritesplit）
  4. MOnitor 監控外掛
  5. Filter&Logging 日誌和過濾外掛

maxScale實現讀寫分離及負載均衡

相關文章