文章目錄

• 一、MySQL主從復制與讀寫分離
• • 1.1 主從分離和讀寫分離的相關概述
  • • 1）主從復制與讀寫分離、
    • 2）為什么要讀寫分離
    • 3）什么時候要讀寫分離
    • 4）讀寫分離的原理
    • 5）mysql支持的復制類型
    • 6）主從復制的工作過程（主寫從復制）
    • 7）MySOL主從復制延遲
    • 8）MySQL 讀寫分離原理
    • 9）目前較為常見的 MySQL
• 二、MySQL主從復制與讀寫分離
• • 2.1 主從復制的步驟（理論）
  • 2.2 主從復制的實驗操作步驟（實操）
  • • 第一步：主服務器時間同步設置
    • 第二步：從服務器時間同步設置
    • 第三步：主服務器的mysql設置
    • 第四步：從服務器的mysql設置
    • 第五步：測試階段
  • 2.3 讀寫分離的步驟（理論）
  • 2.4 讀寫分離的實驗操作步驟（實操）
  • • 第一步： Amoeba服務器設置
    • 第二步： 在Master、slave1和slave2服務器開啟amoeba訪問
    • 第三步： Amoeba服務器設置amoeba服務
    • 第四步： 使用客戶機進行驗證（沒安裝mysql，這里我用的是mariadb，也是mysql的衍生體）

一、MySQL主從復制與讀寫分離

1.1 主從分離和讀寫分離的相關概述

1）主從復制與讀寫分離、

在實質際的生產環境中，對數據庫的讀和寫都在同一個數據庫服務器中，是不能滿足實院需求的。無論是在安全性、高可用性還是高并發等各個方面都是完全不能滿足實際需求的。因此，通過主從復制的方式來同步數據，再通過讀寫分離來提升數據庫的并發負載能力。有點類似于rsync，但是不同的是rsync是對磁盤文件做備份，而mysql主從復制是對數據庫中的數據、語句做備份。

2）為什么要讀寫分離

因為數據庫的"寫"（寫10000條數據可能要3分鐘）操作是比較耗時的。
但是數據庫的"讀" （讀10000條數據可能只要5秒鐘）
所以讀寫分離，解決的是，數據庫的寫入，景響了查詢的效率。

3）什么時候要讀寫分離

數據庫不一定要讀寫分離，如果程序使用數據庫較多時，而更新少，查詢多的情況下會考慮使用。利用數據庫主從同步，再通過讀寫分離可以分擔數據庫壓力，提高性能。

4）讀寫分離的原理

讀寫分離基本的原理是讓主數據庫處理事務性增、改、刪操作（INSERT、UPDATE、DELETE），而從數據庫處理SELECT香詢操作。數據庫復制被用來把事務性操作導致的變更同步到集群中的從數據庫。

5）mysql支持的復制類型

STATEMENT∶基于語句的復制。在服務器上執行sgl語句，在從服務器上執行同樣的語句，mysgl默認采用基于語句的復制，執行效率高。
ROW∶ 基于行的復制。把改變的內容復制過去， 而不是把命令在從服務器上執行一遍。
MIXED∶混合類型的復制。黑默認采用基于語句的復制，一旦發現基于語句無法精確復制時，就會采用基于行的復制。

6）主從復制的工作過程（主寫從復制）

在每個事務更新數據完成之前，Master在二進制日志(Binary log)記錄這些改變。寫入二進制日志完成后，Master通知存儲引擎提交事務。
Slave將Master復制到其中繼日志(Relay log) 。首先slave開始一個工作線程(I/O)，I/0線程在Master上打開一個普通的連接，然后開始Binlog dump process。Binlogdumpprocess從Master的二進制日志中讀取事件，如果已經跟上Master,它會睡眠并等待Master產生新的事件，I/O線程將這些事件寫入中繼日志（緩存中）。
SQL slave thread (SQL從線程)處理該過程的最后一步，SQL線程從中繼日志讀取事件，并重放其中的事件而更新Slave數據，使其與Master 中的數據一致，只要該線程與I/O 線程保持一致，中繼日志通常會位于OS（系統）緩存中，所以中繼日志的開銷很小。復制過程有一個很重要的限制，即復制在 Slave 上 是串行化的，也就是說Master 上的并行更新操作不能在Slave上并行操作。

7）MySOL主從復制延遲

master服務器高并發，形成大量事務
網絡延遲
主從硬件設備導致 cpu主頻、內存io、硬盤io
本來就不是同步復制、而是異步復制
從庫優化Mysql參數。比如增大innodb_buffer_pool_size，讓更多操作在Mysgl內存中完成，減少磁盤操作。從庫使用高性能主機。包括cpu強悍、內存加大。避免使用虛擬云主機，使用物理主機，這樣提升了i/o方面性。從庫使用SSD磁盤
網絡優化，避免跨機房實現同步

8）MySQL 讀寫分離原理

讀寫分離就是只在主服務器上寫，只在從服務器上讀。基本的原理是讓主數據庫處理事務性查詢，而從數據庫處理select查詢。數據庫復制被用來把主數據庫上事務性查詢導致的變更同步到集群中的從數據庫。

9）目前較為常見的 MySQL

讀寫分離分為以下兩種∶
（1）基于程序代碼內部實現
在代碼中根據 select、insert，進行路由分類，這類方法也是目前生產環境應用最廣泛的。
優點是性能較好，因為在程序代碼中實現，不需要增加額外的設備為硬件開支;
缺點是需要開發人員來實現，運維人員無從下手。
但是并不是所有的應用都適合在程序代碼中實現讀寫分離，像一些大型復雜的Java應用，如果在程序代碼中實現讀寫分離對代碼改動就較大。

（2）基于中間代理層實現
代理一般位于客戶端和服務器之間， 代理服務器接到客戶端請求后通過判斷后轉發到后端數據庫， 有以下代表性程序。

①MySQL-Proxy。 MySQL-Proxy 為 MySOL開源項目， 通過其自帶的 lua 腳本進行SOL 判斷。
②Atlas。是由奇虎360的Web平臺部基礎架構團隊開發維護的一個基于MySQL協議的數據中間層項目。它是在mysql-proxy0.8.2版本的基礎上，對其進行了優化， 增加了一些新的功能特性。360內部使用Atlas運行的mysql業務，每天承載的讀寫請求數達幾十億條。支持事物以 及存儲過程。
③Amoeba。 由陳思儒開發，作者曾就職于阿里巴巴。該程序由Jaya語言進行開發，阿里巴巴將其用于生產環境。但是它不支持事務和存儲過程。

二、MySQL主從復制與讀寫分離

2.1 主從復制的步驟（理論）

-----主服務器設置--------

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

##主從服務器時間同步設置##
vim /etc/ntp.conf

##末行添加
server 127.127.153.0 #設置本地是時鐘源，注意修改網段
fudge 127.127.153.0 stratum 8 #設置時間層級為8(限制在15內)

-----從服務器設置--------

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

##從服務器設置時間同步
yum install -y ntp ntpdate

service ntpd start

crontab -e #創建定時任務如下
*/30 * * * * /usr/sbin/ntpdate 192.168.153.50

-----主服務器MySQL設置--------

vim /etc/my.cnf
##添加以下配置
server-id = 11 #修改服務器id為11
log-bin=master-bin #添加，主服務器開啟二進制日志
binlog_format = MIXED #添加，mysql支持的復制類型為MIXED
log-slave-updates=true #添加，允許從服務器更新二進制日志

systemctl restart mysqld.service #重啟服務器

mysql -uroot -pabc123

GRANT REPLICATION SLAVE ON . TO ‘myslave’@‘192.168.153.%’ IDENTIFIED BY ‘123456’; #添加授權
SHOW master status; #查看主服務狀態，如下代表正確
±------------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±------------------±---------±-------------±-----------------±------------------+
| master-bin.000001 | 604 | | | |
±------------------±---------±-------------±-----------------±------------------+
1 row in set (0.01 sec)
#File列顯示日志名，Position 列顯示偏移量

-----從服務器MySQL設置--------

vim /etc/my.cnf
server-id = 22 #修改，注意id與Master的不同，兩個Slave的id也要不同
relay-log=relay-log-bin #添加，開啟中繼日志，從主服務器上同步日志文件記錄到本地
relay-log-index=slave-relay-bin.index #添加，定義中繼日志文件的位置和名稱

##配置同步，注意master_log_file和master_log_pos的值要與Master查詢的一致
change master to master_host=‘192.168.153.50’ , master_user=‘myslave’,master_password=‘123456’,master_log_file=‘master-bin.000001’,master_log_pos=604;

start slave; #啟動同步，如有報錯執行reset slave;
show slave status\G #查看Slave狀態
#確保下面IO和SQL線程都是Yes，代表同步正常。
Slave_IO_Running: Yes #負責與主機的io通信
Slave_SQL_Running: Yes #負責自己的slave mysql進程

#一-般Slave_IO_Running: No的可能性:
1、網絡不通
2、my. cnf配置有問題
3、密碼、file文件名、pos偏移量不對
4、防火墻沒有關閉

2.2 主從復制的實驗操作步驟（實操）

實驗環境：
主服務器：192.168.153.50 從服務器192.168.153.60 ，192.168.153.70

第一步：主服務器時間同步設置

1）關閉防火墻和SElinux然后安裝ntp服務

2）修改配置文件

3）開啟ntp

第二步：從服務器時間同步設置

1）關閉防火墻和SElinux

2）安裝ntp和ntpdate

3）添加定時任務

第三步：主服務器的mysql設置

1）主服務器my.cnf配置文件修改

2）重啟mysql并給從服務器添加授權

3）查看主服務器狀態

第四步：從服務器的mysql設置

1）修改my.cnf配置文件

2）重啟mysql并設置同步配置

3）查看slave狀態

第五步：測試階段

1）在主服務上新建TEST庫

2）在從服務器上直接查看所有庫

3）在主服務器上創建test表，添加數據

4）在從服務器上查看主服務器創建的test表，添加的數據

2.3 讀寫分離的步驟（理論）

#實驗環境
Master CentOS7 192.168.153.50
Amoeba CentOS7 192.168.153.80
Slave1 CentOS7 192.168.153.60
Slave2 CentOS7 192.168.153.70
客戶端 CentOS7 192.168.153.40

-----Amoeba服務器設置--------

cd /opt/
cp jdk-6u14-linux-x64.bin /usr/local/

cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
按空格到最后一行，按yes，然后再按enter

mv jdk1.6.0_14/ /usr/local/jdk1.6

vim /etc/profile
## 末行添加下列配置
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAV{A}_{H}OME/jre/libexportPATH=$JAVA_HOME/lib:$JAV{A}_{H}OME/jre/bin/:$PATH:$HOME/binexportAMOEB{A}_{H}OME=/usr/local/amoebaexportPATH=$PATH:$AMOEBA_HOME/bin

source /etc/profile
java -version
顯示：java version “1.6.0_14” 代表成功

## 安裝 Amoeba軟件
mkdir /usr/local/amoeba
tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod -R 755 /usr/local/amoeba/
/usr/local/amoeba/bin/amoeba
顯示：amoeba start|stop 說明安裝成功

---- 配置 Amoeba讀寫分離，兩個 Slave 讀負載均衡----

## 在Master、Slave1、Slave2 的mysql上開放權限給 Amoeba 訪問
mysql -uroot -pabc123
grant all on . to test@‘192.168.80.%’ identified by ‘123456’;

---- amoeba服務器配置amoeba服務 -----
cd /usr/local/amoeba/conf/

cp amoeba.xml amoeba.xml.bak #備份配置文件
vim amoeba.xml
## 30行修改 ##
amoeba
## 32行修改 ##
123456
## 115行修改 ##
master
## 117行去掉注釋 ##
master
slaves

cp dbServers.xml dbServers.xml.bak

vim dbServers.xml #修改數據庫配置文件
## 23行注釋掉 ##
<!-- mysql schema
<property name=“schema”>test
–>
## 26行修改 ##
<!-- mysql user -->
test
## 28 - 30 行取消注釋 ##
123456
## 45行修改 ##

## 48行修改主服務器id ##
192.168.80.10
## 52行修改從服務器名slave1 ##

## 55行修改為從服務器slave1的ip ##
192.168.80.20
## 復制58行上面6行粘貼，設置從服務器2的名slave2和地址

192.168.80.30
## 65行修改 ##

## 71行修改 ##
slave1,slave2

/usr/local/amoeba/bin/amoeba start& #執行此程序，按Ctrl + c 返回
netstat -anpt | grep java #查看是否開啟了8066端口
---- 測試階段 -----

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

yum install -y mariadb-server mariadb
mysql -u amoeba -p123456 -h 192.168.153.80 -P8066 #通過amoeba服務器代理訪問mysql ，在通過客戶端連接mysql后寫入的數據只有主服務會記錄，然后同步給從服務器

## 主服務器 ##
use TEST;
create table test(id int(10),name varchar(10),address varchar(20));

## 兩臺從服務器 ##
stop slave; #關閉同步
USE TEST;

## slave1 上
insert into test values(‘1’,‘zhangsan’,‘this_is_slave1’);

## slave2 上
insert into test values(‘2’,‘lisi’,‘this_is_slave2’);

## 主服務器 上
insert into test values(‘3’,‘wanger’,‘this_is_master’);

## 客戶端 上
use TEST;
select * from test; #客戶端會分別向slave1和slave2讀取數據，顯示的只有在兩個從服務器上添加的數據，沒有在主服務器上添加的數據
insert into test values(‘4’,‘趙六’,‘this_is_client’); ##只有主服務器上有此數據

2.4 讀寫分離的實驗操作步驟（實操）

實驗環境準備：
設備 IP地址 安裝的程序
Master服務器 192.168.153.50 ntp
Amoeba服務器 192.168.153.80 jdk-6u14-linux-x64.bin、amoeba-mysql-binary-2.2.0.tar.gz
Slave1服務器 192.168.153.60 ntp 、ntpdate
Slave2服務器 192.168.153.70 ntp 、ntpdate
客戶端 192.168.153.20 ——安裝mariadb

關閉所有設備的防火墻和SElinux

第一步： Amoeba服務器設置

1）執行安裝jdk

2）將jdk1.6.0_14/移動到 /usr/local/并修改名稱為：jdk1.6

3）修改/etc/profile主配置文件

4）查看java版本

5）解壓amoeba

6）賦予權限并運行

第二步： 在Master、slave1和slave2服務器開啟amoeba訪問

第三步： Amoeba服務器設置amoeba服務

1）備份配置文件

2）修改amoeba配置文件

3）修改dbServers.xml配置文件

4）確認端口開啟狀態

第四步： 使用客戶機進行驗證（沒安裝mysql，這里我用的是mariadb，也是mysql的衍生體）

1）在客戶端安裝mariadb及客戶端

2）登陸amoeba查看數據庫

3）主服務器新建test表

4）客戶端創建數據，會同步到所有的數據庫中

5）在主服務器和從服務器進行查看

6）在slave1和slave2上停止slave同步

7）在master、slave1和slave2

8）在客戶端創建數據

9）在master上確定數據的創建

10）在slave1和slave2上查看數據

總結

以上是生活随笔為你收集整理的MySQL(9)主从复制与读写分离的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。