MySQL 資料庫的主從複製與讀寫分離

標籤：abs   修改   def   bst   read   oss   slaves   sbin   4行   

在實際生產環境中，如果對資料庫的讀和寫都在同一個資料庫伺服器中操作，無論是安全性、高可用性，還是高並發等各個方面都是完全不能滿足實際需求的，因此，一般來說都是通過主從複製（Master-Slave）的方式來同步資料，再通過讀寫分離來提升資料庫的並發負載能力這樣的方案來進行部署與實施。

MySQL 的主從複製和讀寫分離兩者有著緊密關聯，首先要部署主從複製，才能在此基礎上進行資料的讀寫分離。

• MySQL 主從複製的複製類型
  1) 基於語句的資料。在主伺服器上執行的 SQL 陳述式，在從伺服器上執行同樣的語句。 MySQL 預設採用基於語句的複製，效率比較高。
  2) 基於行的複製。把改變的內容複寫過去，而不是把命令從伺服器上執行一遍。
  3) 混合類型的複製。預設採用基於語句的複製，一旦發現基於語句無法精確複製時，採用基於行的複製。

• MySQL 讀寫分離原理
  簡單來說，讀寫分離就是只在主伺服器上寫，只在從伺服器上讀。基本原理是讓主要資料庫處理事務性查詢，而從資料庫處理 select 查詢。資料庫複寫被用來把事務性查詢導致的變更同步到群集中的從伺服器。

部署環境

主機	作業系統	IP地址	主要軟體
Master	CentOS 7.4 x86_64	192.168.100.200	mysql-5.7.17.tar.gz 、ntp 、boost_1_59_0.tar.gz
Slave1	CentOS 7.4 x86_64	192.168.100.201	mysql-5.7.17.tar.gz 、ntp 、boost_1_59_0.tar.gz
Slave2	CentOS 7.4 x86_64	192.168.200.202	mysql-5.7.17.tar.gz 、ntp 、boost_1_59_0.tar.gz
Amoeba	CentOS 7.4 x86_64	192.168.200.203	mysql-5.7.17.tar.gz 、ntp 、boost_1_59_0.tar.gz 、amoeba-mysql-binary-2.2.0.tar.gz 、jdk-6u14-linux-x64.bin
Client	CentOS 7.4 x86_64	192.168.200.204	mysql-5.7.17.tar.gz

搭建 MySQL 主從複製

• 建立時間同步環境，在主節點上搭建時間同步伺服器

  systemctl stop firewalld.servicesetenforce 0yum -y install ntp    #一般系統內建，沒有的話yum安裝vim /etc/ntp.conf    #添加下面的行server 127.127.100.0    #本地為時鐘源fudge 127.127.100.0 stratum 8    #設定時間層級為8（一般限制15內）systemctl restart ntpd    #重啟服務

• 在從伺服器上進行時間同步

  systemctl stop firewalld.servicesetenforce 0yum -y install ntpdate/usr/sbin/ntpdate 192.168.100.200     #進行時間同步

• 在4台主機上安裝 MySQL資料庫
  這邊選擇的是5.7版本，直接進行簡單的編譯安裝即可。

• 配置 Master 主伺服器

  # vim /etc/my.cnf     server-id       = 11           #修改     log-bin=master-bin        #主伺服器記錄檔     log-slave-updates=true      #從伺服器更新二進位日誌# systemctl restart mysqld.service# mysql -u root -p123456  #登入Mysql 給從伺服器授權mysql> GRANT REPLICATION SLAVE ON *.* TO ‘myslave‘@‘192.168.100.%‘ IDENTIFIED BY ‘123456‘;mysql> FLUSH PRIVILEGES;mysql> show master status;

• 配置 Slave 從伺服器(Slave1、Slave2配置相同)

  # vim /etc/my.cnfserver-id       = 22        #另一台從伺服器也是22(不能和 Master 伺服器相同)relay-log=relay-log-bin         #從主伺服器上同步處理記錄檔案記錄到本地relay-log-index=slave-relay-bin.index      #定義relay-log的位置和名稱# systemctl restart mysqld.service

• 登入 Slave 伺服器，按 Master 伺服器結果更改下面命令中 master_log_file 和 master_log_pos 的參數

  # mysql -u root -p123456mysql> change master to master_host=‘192.168.100.200‘,master_user=‘myslave‘,master_password=‘123456‘,master_log_file=‘master-bin.000001‘,master_log_pos=604;mysql> start slave;      #啟動同步mysql> show slave status\G;         #查看狀態

• 驗證主從複製效果
  在 Master 伺服器上建立一個 test 表，從伺服器上查看。

搭建 MySQL 資料庫的讀寫分離

Amoeba（變形蟲）項目開源架構於2008年發布了一款 Amoeba for MySQL 軟體。這個軟體致力於 MySQL 的分散式資料庫前端代理層，它主要為應用程式層訪問 MySQL 時充當 SQL 路由，並具有負載平衡、高可用性、SQL 過濾、讀寫分離、可路由相關到目標資料庫、可並發請求多台資料庫。通過 Amoeba 已在很多企業的生產線上使用，其版本可在官網下載。

• 在 Amoeba 伺服器上安裝 Java 環境

  systemctl stop firewalld.servicesetenforce 0cp jdk-6u14-linux-x64.bin /usr/local/cd /usr/local./jdk-6u14-linux-x64.bin   #斷行符號，輸入"yes"，斷行符號mv jdk1.6.0_14/ /usr/local/jdk1.6

• 修改環境變數

  # vim /etc/profile    #在檔案末尾添加下面的行export JAVA_HOME=/usr/local/jdk1.6export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/libexport PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/binexport AMOEBA_HOME=/usr/local/amoebaexport PATH=$PATH:$AMOEBA_HOME/bin# source /etc/profile    #重新整理環境變數

• 安裝並配置 Amoeba 軟體

  # mkdir /usr/local/amoeba# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/# chmod -R 755 /usr/local/amoeba/# /usr/local/amoeba/bin/amoebaamoeba start|stop    #顯示此內容說明 Amoeba 安裝成功

• 在三台主從 MySQL 伺服器上開放許可權給 Amoeba 訪問(三台伺服器都要添加)

  grant all on *.* to [email protected]‘192.168.100.%‘ identified by ‘123456‘;

• 在 Amoeba 伺服器上編輯 amoeba.xml 設定檔

  # vim /usr/local/amoeba/conf/amoeba.xml--30行--<property name="user">amoeba</property>    #使用者訪問amoeba伺服器的身分識別驗證--32行-- <property name="password">123456</property>--115行--<property name="defaultPool">master</property>    #預設許可權--117-去掉注釋-<property name="writePool">master</property>      #賦予master寫入權限<property name="readPool">slaves</property>        #賦予slaves讀許可權

  # vi conf/dbServers.xml-23行注釋掉  作用：預設進入test庫 以防mysql中沒有test庫時，會報錯<!-- <property name="schema">test</property> -->--26--29--去掉注釋--    #賦予amoeba使用者權限<property name="user">test</property><property name="password">123456</property>-----42-主伺服器地址---<dbServer name="master"  parent="abstractServer"><property name="ipAddress">192.168.100.200</property>--52-從伺服器主機名稱-<dbServer name="slave1"  parent="abstractServer">--55-從伺服器1地址-<property name="ipAddress">192.168.100.201</property>--（這裡上面6行複製修改）從伺服器2地址<dbServer name="slave2"  parent="abstractServer"><property name="ipAddress">192.168.100.202</property>--64行--<dbServer name="slaves" virtual="true">--70行--<property name="poolNames">slave1,slave2</property>

• 開啟服務

  /usr/local/amoeba/bin/amoeba start&netstat -anpt | grep java

• 測試讀寫分離

• 客戶機

  systemctl stop firewalld.servicesetenforce 0mysql -u amoeba -p123456 -h 192.168.100.203 -P8066    #使用amoeba遠程登入MySQL

• Master 伺服器

  mysql -u root -pmysql> create database wang;mysql> use wzn;mysql> create table wzn (id int(10),name varchar(10),address varchar(20));

• Slave 伺服器

  mysql -u root -pmysql> stop slave;mysql> use wang;mysql> use wzn;# Slave1 伺服器mysql> insert into zang values(‘2‘,‘wzn‘,‘this_is_slave1‘);# Slave2 伺服器mysq> insert into zang values(‘3‘,‘wzn‘,‘this_is_slave2‘);

• 此時我們通過客戶機訪問,使用select * from wzn;可以看到在兩台 Slave 伺服器上分別建立的資料。

• 我們在用戶端上插入一條資料，此時查看錶 wzn ，是看不到剛寫的資料的，因為資料是寫在 Master 伺服器上，此時同步沒有開啟，但是我們登入 Master資料庫可以看到資料寫入成功。嘗試開啟同步，我們可以看到，資料同步到 Slave 資料庫之後，我們可以看到建立的資料了。
  由此可以證實：資料的寫入是在 Master 伺服器上；資料的讀取是在 Slave 伺服器上。

MySQL 資料庫的主從複製與讀寫分離