資料庫讀寫分離Master-Slave

濤姐濤哥發表於2019-07-02

資料庫讀寫分離Master-Slave

一個平臺或系統隨著時間的推移和使用者量的增多，資料庫操作往往會變慢，這時我們需要一些有效的優化手段來提高資料庫的執行速度；如SQL優化、表結構優化、索引優化、引擎優化和讀寫分離優化等手段。

1、SQL優化(簡單列幾點)：

儘量避免用SELECT*；
只查詢一條記錄時使用limit1；
使用連線查詢代替子查詢；
儘量使用一些能通過索引查詢的關鍵字。

2、表結構優化：

儘量使用數字型別欄位，提高對比效率；
長度不變且對查詢速度要求高的資料可以考慮使用char，否則使用varchar；
表中欄位過多時可以適當的進行垂直分割，將部分欄位移動到另一張表；
表中資料量大可以適當的進行水平分割，將部分資料移動到另一張表。

3、索引優化：

對查詢頻率高的欄位適當建立索引，提高效率。(在經常用到的欄位上適合建立索引)

4、引擎優化：

選擇合適的引擎提高資料庫效能，如InnoDB和MyISAM，InnoDB和MyISAM是許多人在使用MySQL時最常用的兩個表型別，這兩個表型別各有優劣，視具體應用而定。基本的差別為：MyISAM型別不支援事務處理等高階處理，而InnoDB型別支援。MyISAM型別的表強調的是效能，其執行數度比InnoDB型別更快，但是不提供事務支援，而InnoDB提供事務支援已經外部鍵等高階資料庫功能。
InnoDB：
支援事務處理，支援外來鍵，支援崩潰修復能力和併發控制。如果需要對事務的完整性要求比較高（比如銀行），要求實現併發控制（比如售票），那選擇InnoDB有很大的優勢。如果需要頻繁的更新、刪除操作的資料庫，也可以選擇InnoDB，因為支援事務的提交（commit）和回滾（rollback）。
MyISAM：
插入資料快，空間和記憶體使用比較低。如果表主要是用於插入新記錄和讀出記錄，那麼選擇MyISAM能實現處理高效率。如果應用的完整性、併發性要求比較低，也可以使用。
MEMORY：
所有的資料都在記憶體中，資料的處理速度快，但是安全性不高。如果需要很快的讀寫速度，對資料的安全性要求較低，可以選擇MEMOEY。它對錶的大小有要求，不能建立太大的表。所以，這類資料庫只使用在相對較小的資料庫表。
注意：
同一個資料庫也可以使用多種儲存引擎的表，如果一個表要求比較高的事務處理，可以選擇InnoDB；這個資料庫中可以將查詢要求比較高的表選擇MyISAM儲存；如果該資料庫需要一個用於查詢的臨時表，可以選擇MEMORY儲存引擎。

5、讀寫分離優化：

隨著使用者量的增多，資料庫操作往往會成為一個系統的瓶頸所在，但一般的系統“讀”的壓力遠遠大於“寫”，So我們可以通過實現資料庫讀寫分離-主從複製來提高系統的效能。

主從設計思路：

通過設定主從資料庫實現讀寫分離，主資料庫負責“寫操作”，從資料庫負責“讀操作”，根據壓力情況，從資料庫可以部署多個提高“讀”的速度，藉此來提高系統總體的效能。當然，我們可以根據專案等需要配置多個從庫。

如上圖所示，讀寫分離的實現，主要是解決主從資料庫資料同步的問題，在主資料庫寫入資料後要保證從資料庫的資料也要更新。主伺服器master記錄資料庫操作日誌到Binary log，從伺服器開啟i/o執行緒將二進位制日誌記錄的操作同步到relay log（存在從伺服器的快取中），另外sql執行緒將relay log日誌記錄的操作在從伺服器執行。

Master-Slave具體步驟

準備工作，在這之前需要準備兩個伺服器，分別在上面安裝Mysql資料庫，一個作為Master，另外一個是Slave，當然需要多個從庫可以自己搞多個Slave。如下圖：

兩個虛擬機器伺服器：

分別安裝Mysql資料庫：

1.主mysql資料庫配置檔案修改

[root@tjt03 ~]# vim /etc/my.cnf

在主伺服器master上配置開啟Binary log，主要是在[mysqld]下面新增：

server-id=132
log-bin=master-bin      //[必須]啟用二進位制日誌
log-bin-index=master-bin.index     //[必須]伺服器唯一ID，預設是1，一般取IP最後一段

配置修改好後重主庫Mysql：

[root@tjt03 ~]# sudo service mysqld stop
Stopping mysqld (via systemctl):                           [  OK  ]
[root@tjt03 ~]# sudo service mysqld start
Starting mysqld (via systemctl):                           [  OK  ]
[root@tjt03 ~]#

在主資料庫檢查配置效果：

mysql> SHOW MASTER STATUS;

可以看到下圖表示配置沒問題,這裡面的File名:master-bin.000001 我們接下來在從資料庫的配置會使用：

2.從mysql資料庫配置檔案修改

[root@tjt02 mysql]# vim /etc/my.cnf

在從伺服器slave上的[mysqld]下面新增：

server-id=131    //這裡面的server-id 一定要和主庫的不同
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

配置修改好後重從庫Mysql：

[root@tjt02 mysql]# service mysqld stop
Stopping mysqld (via systemctl):                           [  OK  ]
[root@tjt02 mysql]# service mysqld start
Starting mysqld (via systemctl):                           [  OK  ]
[root@tjt02 mysql]#

3、配置連個資料庫的關聯

首先我們先建立一個操作主從同步的資料庫使用者，切換到主資料庫執行：

mysql> GRANT REPLICATION SLAVE ON *.* to 'tjt'@'%' identified by 'TANjintao@520';

這個配置的含義就是建立了一個資料庫使用者tjt，密碼是TANjintao@520, 在從伺服器使用tjt這個賬號和主伺服器連線的時候，就賦予其REPLICATION SLAVE的許可權， *.* 表面這個許可權是針對主庫的所有表的，“%”表示所有客戶端都可能連，只要帳號，密碼正確，此處可用具體客戶端IP代替，如192.168.145.226，加強安全。

進入從資料庫執行授權Slave：

mysql> change master to master_host='172.16.114.132',master_port=3306,master_user='tjt',master_password='TANjintao@520',master_log_file='master-bin.000001',master_log_pos=0;

上述步驟執行完畢後執行start slave啟動配置：

mysql> start slave;

停止主從同步的命令為：

mysql> stop slave;

檢視狀態命令,\G表示換行檢視:

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.114.132
                  Master_User: tjt
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 870
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 1085
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: