墨鬥魚業務MYSQL架構-從無到有

標籤：mysql高可用

項目描述 

    採用MYSQL 複製的方式 作為項目的資料庫底層架構 寫操作在MASTER 讀在SLAVE 上

主庫採用

MYSQL5.6版本

從庫採用 

MYSQL5.7版本 #為了測試MYSQL5.7的版本和舊版本的相容性

INNODB儲存引擎 每張表有自增id做主鍵，RR隔離等級

1. 配置環境描述

    1.1 binlog 格式的選擇

        常用binlog格式 有 STATEMENT,ROW,MIXED

    採用ROW格式作為binlog日誌格式

    ROW格式優點 特殊函數能有效複製 安全 表有自增id做主鍵 複製速度會更快

    如果選擇SRB和MIX複製 可能特殊的函數不能有效複製例如 uuid now 等

650) this.width=650;" src="https://s4.51cto.com/wyfs02/M02/8E/60/wKiom1i-qMDgZqujAAGPvSe1AxA813.png-wh_500x0-wm_3-wmp_4-s_465270151.png" title="QQ圖片20170307203319.png" width="500" height="251" border="0" hspace="0" vspace="0" style="width:500px;height:251px;" alt="wKiom1i-qMDgZqujAAGPvSe1AxA813.png-wh_50" />

    1.2從開啟了 read_onle和super_read_onle 避免從寫入

        read_onle=on && super_read_onle=on #這樣任何賬戶都不能寫入

    1.3採用了GTID複製模式

         GTID簡介

       每一個事務都有一個唯一的編號

            uuid:n uuid是MYSQL唯一識別碼 n是事務id號    

            一個事務對應一個唯一的ID,一個GTID在一個伺服器只執行一次

        gtid serverid:事務id 每個事務id的編號是唯一的 不能重複執行 

        MYSQL5.6.2支援 MYSQL5.6.10完善

      GTID限制

        不支援非事務引擎（從庫報錯 stop slave; start slave; 忽略）

        不支援create table ....select語句複製（主庫直接報錯）

        不許一個sql同時更新一個事務引擎和非事務引擎的表

        在一個複製組中 必須要求統一開啟GTID或者關閉GTID

        開啟GTID需要重啟（5.7不需要）

        開啟GTID後，就不在使用原來的傳統複製方式

        對於create tmporary table 和drop temporary table 語句不支援

        不支援 sql_slave_skip_counter

        個人理解： 

            GTID 有利於主從維護 主從複製 主從一致性的校正 主從切換等 

    雙一 保證資料的一致性 MASTER innodb_flush_log_at_trx_commit=1 sync_binlog=1

        當時的壓力測試滿足了當前業務 主從都開啟了雙一

 配置了 網域名稱指定了master 和slave的主機 

         這樣應用程式就不用去修改了 用Ansible 自動同步host檔案

缺點：

    1.主從故障手動切換 監控要做好 要第一時間去做切換

    2.資料一致性難以保證（1）

中期環境改進

keepalived-1.2.13 

MYSQL5.7 MASTER

MYSQL5.7 MASTER

2.配置描述

    利用keepalived做雙主結構 MYSQL也做雙主 互為主從關係 

        auto_increment_increment=2#自增

        auto_increment_offset=1/2#起始步長

     keepalived:MYSQL 不是在同一個交換器上  就在檢測指令碼裡面寫ping 網關 如果不通返回非0，為了防止腦裂

    在MASTER和SLAVE 開啟 master_info_repository=table relay_log_info_repository=tblae 

主從屬記錄 寫到table裡面 不是寫入到系統的檔案裡面

    開啟了relay_log_recovery 為了防止 relay在執行過程中 SLAVE突然關閉 或者relay log 損壞

缺點：

    1.會造成更新丟失問題 這時候就很難判斷以主庫資料為準還是從庫資料為準

        二台機器同時 update 不會造成堵塞 也不會造成鎖 根據主鍵更新 都會寫入binlog 然後傳遞對方的relay log 並應用 所以造成更新丟失。因為 主從複製 根據主鍵更新  不會校隊其他資料 。如果沒有主鍵 根據二級索引進行更新 利用第一個最長的索引匹配主鍵， 如果都沒有 全表掃描更新。

雙主結構 PXC結構 MGR結構 都會造成這個問題 解決辦法 單台機器進行update。insert或者delete 可以負載分發

    2.如果MASTER傳輸 binlog的時候 沒有安全的傳輸到SLAVE  也會造成資料不一致

3.後期環境改進

proxysql-1.3.4 讀寫分離

keepalived-1.2.13 

MYSQL5.7 MASTER

MYSQL5.7 MASTER

MYSQL5.7 SLAVE.....多個

    配置描述

    1.解決資料一致性難以保證（1） 的問題 

        雙1解決了 客戶提交後 資料庫成功執行後 寫到redo log和binlog 也就是雙階段提交

        採用5.7增強半同步 解決了 MASTER的binlog傳輸到SLAVE上 才會提交 返回用戶端

        雙table 避免檔案沒有及時重新整理 MASTER的file和pos資訊 造成日誌點的丟失

        開啟了relay_log_recovery 為了防止 relay在執行過程中 SLAVE突然關閉 或者relay log 損壞，如果發現沒執行完畢 異常關閉 就重新執行relay log

        row格式 gtid 複製格式

    2.用proxysql 做讀寫分離 MYSQL5.7 MASTER 進行寫的操作 MASTER2 和slave做讀操作

    3.MASTER1和MASTER 雙主 做了增強半同步複製

    4.採用了MYSQL5.7的並行複製 是組提交層級 非5.6 庫提交層級 真正的並行複製

缺點

    1.正在解決 如果節點 掛了後修複 重新添加到proxysql資訊 

    打算用python實現自動化添加節點 更改節點資訊

本文出自 “linux” 部落格，謝絕轉載！

墨鬥魚業務MYSQL架構-從無到有