MySQL 主從複製與讀寫分離 1、什麼是讀寫分離? 讀寫分離,基本的原理是讓主資料庫處理事務性增、改、刪操作(INSERT、UPDATE、DELETE),而從資料庫處理SELECT查詢操作。資料庫複製被用來把事務性操作導致的變更同步到集群中的從資料庫。 2、為什麼要讀寫分離呢? 因為資料庫的“寫” ...
1、什麼是讀寫分離?
讀寫分離,基本的原理是讓主資料庫處理事務性增、改、刪操作(INSERT、UPDATE、DELETE),而從資料庫處理SELECT查詢操作。資料庫複製被用來把事務性操作導致的變更同步到集群中的從資料庫。
2、為什麼要讀寫分離呢?
因為資料庫的“寫”(寫10000條數據可能要3分鐘)操作是比較耗時的。 但是資料庫的“讀”(讀10000條數據可能只要5秒鐘)。 所以讀寫分離,解決的是,資料庫的寫入,影響了查詢的效率。
3、什麼時候要讀寫分離?
資料庫不一定要讀寫分離,如果程式使用資料庫較多時,而更新少,查詢多的情況下會考慮使用。利用資料庫主從同步,再通過讀寫分離可以分擔資料庫壓力,提高性能。
4、主從複製與讀寫分離
在實際的生產環境中,對資料庫的讀和寫都在同一個資料庫伺服器中,是不能滿足實際需求的。無論是在安全性、高可用性還是高併發等各個方面都是完全不能滿足實際需求的。因此,通過主從複製的方式來同步數據,再通過讀寫分離來提升資料庫的併發負載能力。有點類似於rsync,但是不同的是rsync是對磁碟文件做備份,而mysql主從複製是對資料庫中的數據、語句做備份。
5、mysql支持的複製類型
(1)STATEMENT:基於語句的複製。在伺服器上執行sql語句,在從伺服器上執行同樣的語句,mysql預設採用基於語句的複製,執行效率高。 (2)ROW:基於行的複製。把改變的內容複製過去,而不是把命令在從伺服器上執行一遍。 (3)MIXED:混合類型的複製。預設採用基於語句的複製,一旦發現基於語句無法精確複製時,就會採用基於行的複製。
6、主從複製的工作過程 (1)Master節點將數據的改變記錄成二進位日誌(bin log),當Master上的數據發生改變時,則將其改變寫入二進位日誌中。 (2)Slave節點會在一定時間間隔內對Master的二進位日誌進行探測其是否發生改變,如果發生改變,則開始一個I/O線程請求 Master的二進位事件。 (3)同時Master節點為每個I/O線程啟動一個dump線程,用於向其發送二進位事件,並保存至Slave節點本地的中繼日誌(Relay log)中,Slave節點將啟動SQL線程從中繼日誌中讀取二進位日誌,在本地重放,即解析成 sql 語句逐一執行,使得其數據和 Master節點的保持一致,最後I/O線程和SQL線程將進入睡眠狀態,等待下一次被喚醒。
註: ●中繼日誌通常會位於 OS 緩存中,所以中繼日誌的開銷很小。 ●複製過程有一個很重要的限制,即複製在 Slave上是串列化的,也就是說 Master上的並行更新操作不能在 Slave上並行操作。
7、MySQL 讀寫分離原理
讀寫分離就是只在主伺服器上寫,只在從伺服器上讀。基本的原理是讓主資料庫處理事務性操作,而從資料庫處理 select 查詢。資料庫複製被用來把主資料庫上事務性操作導致的變更同步到集群中的從資料庫。
8、目前較為常見的 MySQL 讀寫分離分為以下兩種: 1)基於程式代碼內部實現 在代碼中根據 select、insert 進行路由分類,這類方法也是目前生產環境應用最廣泛的。 優點是性能較好,因為在程式代碼中實現,不需要增加額外的設備為硬體開支;缺點是需要開發人員來實現,運維人員無從下手。 但是並不是所有的應用都適合在程式代碼中實現讀寫分離,像一些大型複雜的Java應用,如果在程式代碼中實現讀寫分離對代碼改動就較大。
2)基於中間代理層實現 代理一般位於客戶端和伺服器之間,代理伺服器接到客戶端請求後通過判斷後轉發到後端資料庫,有以下代表性程式。 (1)MySQL-Proxy。MySQL-Proxy 為 MySQL 開源項目,通過其自帶的 lua 腳本進行SQL 判斷。 (2)Atlas。是由奇虎360的Web平臺部基礎架構團隊開發維護的一個基於MySQL協議的數據中間層項目。它是在mysql-proxy 0.8.2版本的基礎上,對其進行了優化,增加了一些新的功能特性。360內部使用Atlas運行的mysql業務,每天承載的讀寫請求數達幾十億條。支持事物以及存儲過程。 (3)Amoeba。由陳思儒開發,作者曾就職於阿裡巴巴。該程式由Java語言進行開發,阿裡巴巴將其用於生產環境。但是它不支持事務和存儲過程。 (4)Mycat。是一款流行的基於Java語言編寫的資料庫中間件,是一個實現了MySql協議的伺服器,其核心功能是分庫分表。配合資料庫的主從模式還可以實現讀寫分離。
由於使用MySQL Proxy 需要寫大量的Lua腳本,這些Lua並不是現成的,而是需要自己去寫。這對於並不熟悉MySQL Proxy 內置變數和MySQL Protocol 的人來說是非常困難的。 Amoeba是一個非常容易使用、可移植性非常強的軟體。因此它在生產環境中被廣泛應用於資料庫的代理層。
Master 伺服器:192.168.1.200 mysql5.7 Slave1 伺服器:192.168.1.100 mysql5.7 Slave2 伺服器:192.168.1.101 mysql5.7 Amoeba 伺服器:192.168.1.102 jdk1.6、Amoeba 客戶端 伺服器:192.168.1.150 mysql
#關閉防火牆
systemctl stop firewalld
systemctl disable firewalld
setenforce 0
---搭建 MySQL主從複製---
----Mysql主從伺服器時間同步---- ##主伺服器設置##
#安裝時間同步軟體
yum install ntp -y
#或[root@localhost ~]# rpm -q chrony
chrony-3.1-2.el7.centos.x86_64
註意:兩個不能同時開啟chrony,ntp開啟預設123埠
--末尾添加--
vim /etc/ntp.conf
//25行添加
server ntp.aliyum.com iburst
#補充:
server 127.127.80.0 #設置本地是時鐘源,註意修改網段
fudge 127.127.80.0 stratum 8 #設置時間層級為8(限制在15內)
#啟動服務
service ntpd start
#檢查同步狀態
ntpq -p
##從伺服器設置##
yum install ntp ntpdate -y
service ntpd start
/usr/sbin/ntpdate 192.168.1.200 #進行時間同步
crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.1.200
#檢查同步狀態
ntpq -p
----主伺服器的mysql配置-----
vim /etc/my.cnf
server-id=11 #每台伺服器不能一樣唯一
log-bin=mysql-bin #添加,主伺服器開啟二進位日誌
binlog_format=mixed #指定二進位日誌的格式
#選配項
expire_logs_days=7 #設置二進位日誌文件過期時間,預設值為0,表示logs不過期
max_binlog_size=500M #設置二進位日誌限制大小,如果超出給定值,日誌就會發生滾動,預設值是1GB
skip_slave_start=1 #阻止從庫崩潰後自動啟動複製,崩潰後再自動複製可能會導致數據不一致的
#"雙1設置",數據寫入最安全
innodb_flush_logs_at_trx_commit=1 #redo log(事務日誌)的刷盤策略,每次事務提交時MySQL都會把事務日誌緩存區的數據寫入日誌文件中,並且刷新到磁碟中,該模式為系統預設
sync_binlog=1 #在進行每1次事務提交(寫入二進位日誌)以後,Mysql將執行一次fsync的磁碟同步指令,將緩衝區數據刷新到磁碟
#"雙1設置"適合數據安全性要求非常高,而且磁碟IO寫能力足夠支持的業務,比如訂單、交易、充值、支付消費系統。"雙1模式"下,當磁碟IO無法滿足業務需求時,比如11.11活動的壓力。推薦一下性能較快的設置,並使用帶蓄電池後備電源,防止系統斷電異常。
innodb_flush_logs_at_trx_commit=2 #每次事務提交時MySQL都會把日誌緩存區的數據寫入日誌文件中,但是並不會同時刷新到磁碟上。該模式下,MySQL會每秒執行一次刷新磁碟操作
sync_binlog=500 #在進行500次事務提交以後,Mysql將執行一次fsync的磁碟同步指令,將緩衝區數據刷新到磁碟
#給從伺服器授權
systemctl restart mysqld
mysql -u root -pabc123
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.1.%' IDENTIFIED BY '123456'; #給從伺服器授權
#GRANT REPLICATION SLAVE: 授予用戶複製從伺服器的許可權。
ON *.*: 表示許可權適用於所有資料庫中的所有表。
TO 'myslave'@'192.168.1.%': 將許可權授予用戶名為 myslave,並且具有 IP 地址為 192.168.1.% 的主機(該模式表示 192.168.1 網段下的所有主機)。
IDENTIFIED BY '123456': 設置該用戶的密碼為 123456。
FLUSH PRIVILEGES; #重新載入和刷新用戶許可權表。
show master status; #查看當前主伺服器上二進位日誌的狀態信息
SHOW MASTER STATUS;
用於查看當前主伺服器上二進位日誌的狀態信息。
-
File(文件):顯示當前正在寫入的二進位日誌文件的名稱。
-
Position(位置):顯示當前寫入的二進位日誌文件的位置(偏移量)。
-
Binlog_Do_DB:如果配置了
--binlog-do-db
選項,它將顯示正在複製的特定資料庫。如果未配置,它將顯示空值。 -
Binlog_Ignore_DB:如果配置了
--binlog-ignore-db
選項,它將顯示被忽略複製的特定資料庫。如果未配置,它將顯示空值。
//如顯示以下
---從伺服器的mysql配置---
vim /etc/my.cnf
server-id = 22 #修改,註意id與Master的不同,兩個Slave的id也要不同
relay-log=relay-log-bin #開啟中繼日誌,從主伺服器上同步日誌文件記錄到本地
relay-log-index=relay-log-bin.index #定義中繼日誌文件的位置和名稱,一般和relay-log在同一目錄
#選配項
innodb_buffer_pool_size=2048M #用於緩存數據和索引的記憶體大小,讓更多數據讀寫記憶體中完成,減少磁碟操作,可設置為伺服器總可用記憶體的 70-80%
sync_binlog=0 #MySQL不做任何強制性的磁碟刷新指令,而是依賴操作系統來刷新數據到磁碟
innodb_flush_log_at_trx_commit=2 #每次事務log buffer會寫入log file,但一秒一次刷新到磁碟
log-slave-updates=0 #slave 從 master 複製的數據會寫入二進位日誌文件里,從庫做為其他從庫的主庫時設置為 1
relay_log_recovery=1 #當 slave 從庫宕機後,假如 relay-log 損壞了,導致一部分中繼日誌沒有處理,則自動放棄所有未執行的 relay-log, 並且重新從 master 上獲取日誌,這樣就保證了 relay-log 的完整性。預設情況下該功能是關閉的,將 relay_log_recovery 的值設置為 1 時, 可在 slave 從庫上開啟該功能,建議開啟。
#授權
systemctl restart mysqld
mysql -u root -pabc123
CHANGE master to master_host='192.168.1.200',master_user='myslave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=941; #配置同步,註意 master_log_file 和 master_log_pos 的值要與Master查詢的一致
start slave; #啟動同步,如有報錯執行 reset slave;
show slave status\G #查看 Slave 狀態
//確保 IO 和 SQL 線程都是 Yes,代表同步正常。
Slave_IO_Running: Yes #負責與主機的io通信
Slave_SQL_Running: Yes #負責自己的slave mysql進程
-
MASTER_HOST
: 指定主伺服器的 IP 地址或主機名,這裡是'192.168.1.200'
。 -
MASTER_USER
: 指定用於複製的用戶名,這裡是'myslave'
。 -
MASTER_PASSWORD
: 指定用於複製的用戶密碼,這裡是'123123'
。 -
MASTER_LOG_FILE
: 指定要從主伺服器複製的二進位日誌文件名,這裡是'mysql-bin.000005'
。 -
MASTER_LOG_POS
: 指定要從主伺服器複製的二進位日誌位置,這裡是 594。
#一般 Slave_IO_Running: No 的可能性: 1、網路不通 2、my.cnf配置有問題 3、密碼、file文件名、pos偏移量不對 4、防火牆沒有關閉
===MySQL主從複製延遲=== 1、master伺服器高併發,形成大量事務 2、網路延遲 3、主從硬體設備導致 cpu主頻、記憶體io、硬碟io 4、是同步複製、而不是非同步複製 從庫優化Mysql參數。比如增大innodb_buffer_pool_size,讓更多操作在Mysql記憶體中完成,減少磁碟操作。 從庫使用高性能主機。包括cpu強悍、記憶體加大。避免使用虛擬雲主機,使用物理主機,這樣提升了i/o方面性。 從庫使用SSD磁碟 網路優化,避免跨機房實現同步
----驗證主從複製效果---- 主伺服器上進入執行 create database db_test;
去從伺服器上查看 show databases;
----搭建 MySQL讀寫分離---
----Amoeba伺服器配置----
##安裝 Java 環境##
因為 Amoeba 基於是 jdk1.5 開發的,所以官方推薦使用 jdk1.5 或 1.6 版本,高版本不建議使用。
cd /opt/
cp jdk-6u14-linux-x64.bin /usr/local/
cd /usr/local/
chmod +x jdk-6u14-linux-x64
./jdk-6u14-linux-x64.bin
//按enter,按yes
mv jdk1.6.0_14/ /usr/local/jdk1.6
添加環境變數
vim /etc/profile
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=.:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin
source /etc/profile
java -version
##安裝 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/amoeba
//如顯示amoeba start|stop說明安裝成功
##配置 Amoeba讀寫分離,兩個 Slave 讀負載均衡## #先在Master、Slave1、Slave2 的mysql上開放許可權給 Amoeba 訪問
grant all on *.* to test@'192.168.1.%' identified by '123.com';
#再回到amoeba伺服器配置amoeba服務:
cd /usr/local/amoeba/conf/
cp amoeba.xml amoeba.xml.bak
vim amoeba.xml #修改amoeba配置文件
--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>
<property name="readPool">slaves</property>
#修改資料庫配置文件
cp dbServers.xml dbServers.xml.bak
vim dbServers.xml
--23行--註釋掉 作用:預設進入test庫 以防mysql中沒有test庫時,會報錯
<!-- <property name="schema">test</property> -->
--26--修改
<property name="user">test</property>
--28-30--去掉註釋
<property name="password">123.com</property>
--45--修改,設置主伺服器的名Master
<dbServer name="master" parent="abstractServer">
--48--修改,設置主伺服器的地址
<property name="ipAddress">192.168.80.10</property>
--52--修改,設置從伺服器的名slave1
<dbServer name="slave1" parent="abstractServer">
--55--修改,設置從伺服器1的地址
<property name="ipAddress">192.168.80.11</property>
--58--複製上面6行粘貼,設置從伺服器2的名slave2和地址
<dbServer name="slave2" parent="abstractServer">
<property name="ipAddress">192.168.80.12</property>
--65行--修改
<dbServer name="slaves" virtual="true">
--71行--修改
<property name="poolNames">slave1,slave2</property>
/usr/local/amoeba/bin/amoeba start& #啟動Amoeba軟體,按ctrl+c 返回
netstat -anpt | grep java #查看8066埠是否開啟,預設埠為TCP 8066
----測試讀寫分離 ----
客戶機沒有MySQL可以yum安裝
yum install -y mariadb-server mariadb
systemctl start mariadb.service
在客戶端伺服器上測試:
mysql -u amoeba -p123456 -h 192.168.1.102 -P8066
//通過amoeba伺服器代理訪問mysql ,在通過客戶端連接mysql後寫入的數據只有主服務會記錄,然後同步給從--從伺服器
在主伺服器上: use db_test; create table test (id int(10),name varchar(10),address varchar(20));
在兩台從伺服器上:
stop slave; #關閉同步
use db_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','wangwu','this_is_master');
//在客戶端伺服器上:
use db_test;
select * from test; //客戶端會分別向slave1和slave2讀取數據,顯示的只有在兩個從伺服器上添加的數據,沒有在主伺服器上添加的數據
insert into test values('4','qianqi','this_is_client'); //只有主伺服器上有此數據
//在兩個從伺服器上執行 start slave; 即可實現同步在主伺服器上添加的數據
start slave;
總結:
搭建 MySQL主從複製
1.主和備都關閉防火牆和selinux
2.都安裝時間同步軟體
做時間同步
3.主開啟二進位日誌
4.給從伺服器授權
5.從伺服器開啟中繼日誌
指定主伺服器相關驗證
ip或主機名,密碼等
6.測試
搭建 MySQL讀寫分離
安裝jdk環境
Amoeba 基於是 jdk1.5 開發的,所以官方推薦使用 jdk1.5 或 1.6 版本,高版本不建議使用。
安裝Amoeba軟體
配置 Amoeba讀寫分離,兩個 Slave 讀負載均衡
測試讀寫分離