MySQL主從復(fù)制與讀寫(xiě)分離原理及用法詳解

本文實(shí)例講述了MySQL主從復(fù)制與讀寫(xiě)分離原理及用法。分享給大家供大家參考，具體如下：

主從復(fù)制

概念

影響MySQL-A數(shù)據(jù)庫(kù)的操作，在數(shù)據(jù)庫(kù)執(zhí)行后，都會(huì)寫(xiě)入本地的日志系統(tǒng)A中。
假設(shè)，實(shí)時(shí)的將變化了的日志系統(tǒng)中的數(shù)據(jù)庫(kù)事件操作，在MYSQL-A的3306端口，通過(guò)網(wǎng)絡(luò)發(fā)給MYSQL-B。
MYSQL-B收到后，寫(xiě)入本地日志系統(tǒng)B，然后一條條的將數(shù)據(jù)庫(kù)事件在數(shù)據(jù)庫(kù)中完成。
那么，MYSQL-A的變化，MYSQL-B也會(huì)變化，這樣就是所謂的MYSQL的復(fù)制，即MYSQL replication。
在上面的模型中，MYSQL-A就是主服務(wù)器，即master，MYSQL-B就是從服務(wù)器，即slave。
日志系統(tǒng)A，其實(shí)它是MYSQL的日志類型中的二進(jìn)制日志，也就是專門(mén)用來(lái)保存修改數(shù)據(jù)庫(kù)表的所有動(dòng)作，即bin log。【注意MYSQL會(huì)在執(zhí)行語(yǔ)句之后，釋放鎖之前，寫(xiě)入二進(jìn)制日志，確保事務(wù)安全】
日志系統(tǒng)B，并不是二進(jìn)制日志，由于它是從MYSQL-A的二進(jìn)制日志復(fù)制過(guò)來(lái)的，并不是自己的數(shù)據(jù)庫(kù)變化產(chǎn)生的，有點(diǎn)接力的感覺(jué)，稱為中繼日志，即relay log。
可以發(fā)現(xiàn)，通過(guò)上面的機(jī)制，可以保證MYSQL-A和MYSQL-B的數(shù)據(jù)庫(kù)數(shù)據(jù)一致，但是時(shí)間上肯定有延遲，即MYSQL-B的數(shù)據(jù)是滯后的。
【即便不考慮什么網(wǎng)絡(luò)的因素，MYSQL-A的數(shù)據(jù)庫(kù)操作是可以并發(fā)的執(zhí)行的，但是MYSQL-B只能從relay log中讀一條，執(zhí)行下。因此MYSQL-A的寫(xiě)操作很頻繁，MYSQL-B很可能跟不上。】

解決問(wèn)題

數(shù)據(jù)如何不被丟失
備份
讀寫(xiě)分離
數(shù)據(jù)庫(kù)負(fù)載均衡
高可用

環(huán)境搭建

1.準(zhǔn)備環(huán)境
兩臺(tái)windows操作系統(tǒng) ip分別為: 172.27.185.1(主)、172.27.185.2(從)
2.連接到主服務(wù)(172.27.185.1)服務(wù)器上，給從節(jié)點(diǎn)分配賬號(hào)權(quán)限。
GRANT REPLICATION SLAVE ON . TO ‘root'@‘172.27.185.2' IDENTIFIED BY ‘root';
3.在主服務(wù)my.ini文件新增

server-id=200
log-bin=mysql-bin
relay-log=relay-bin
relay-log-index=relay-bin-index

重啟mysql服務(wù)
4.在從服務(wù)my.ini文件新增

server-id = 210
replicate-do-db =itmayiedu #需要同步數(shù)據(jù)庫(kù)

重啟mysql服務(wù)
5.從服務(wù)同步主數(shù)據(jù)庫(kù)

stop slave;
change
master to master_host='172.27.185.1',master_user='root',master_password='root';
start slave;
show slave status;

注意事項(xiàng)
①一定要在同一個(gè)局域網(wǎng)中
②使用360WiFi創(chuàng)建局域網(wǎng)
③最好把防火墻全部關(guān)閉掉

什么是讀寫(xiě)分離

在數(shù)據(jù)庫(kù)集群架構(gòu)中，讓主庫(kù)負(fù)責(zé)處理事務(wù)性查詢，而從庫(kù)只負(fù)責(zé)處理select查詢，讓兩者分工明確達(dá)到提高數(shù)據(jù)庫(kù)整體讀寫(xiě)性能。當(dāng)然，主數(shù)據(jù)庫(kù)另外一個(gè)功能就是負(fù)責(zé)將事務(wù)性查詢導(dǎo)致的數(shù)據(jù)變更同步到從庫(kù)中，也就是寫(xiě)操作。

讀寫(xiě)分離的好處

1）分?jǐn)偡?wù)器壓力，提高機(jī)器的系統(tǒng)處理效率
讀寫(xiě)分離適用于讀遠(yuǎn)比寫(xiě)的場(chǎng)景，如果有一臺(tái)服務(wù)器，當(dāng)select很多時(shí)，update和delete會(huì)被這些select訪問(wèn)中的數(shù)據(jù)堵塞，等待select結(jié)束，并發(fā)性能并不高，而主從只負(fù)責(zé)各自的寫(xiě)和讀，極大程度的緩解X鎖和S鎖爭(zhēng)用；
假如我們有1主3從，不考慮上述1中提到的從庫(kù)單方面設(shè)置，假設(shè)現(xiàn)在1分鐘內(nèi)有10條寫(xiě)入，150條讀取。那么，1主3從相當(dāng)于共計(jì)40條寫(xiě)入，而讀取總數(shù)沒(méi)變，因此平均下來(lái)每臺(tái)服務(wù)器承擔(dān)了10條寫(xiě)入和50條讀?。ㄖ鲙?kù)不承擔(dān)讀取操作）。因此，雖然寫(xiě)入沒(méi)變，但是讀取大大分?jǐn)偭?，提高了系統(tǒng)性能。另外，當(dāng)讀取被分?jǐn)偤?，又間接提高了寫(xiě)入的性能。所以，總體性能提高了，說(shuō)白了就是拿機(jī)器和帶寬換性能；
2）增加冗余，提高服務(wù)可用性，當(dāng)一臺(tái)數(shù)據(jù)庫(kù)服務(wù)器宕機(jī)后可以調(diào)整另外一臺(tái)從庫(kù)以最快速度恢復(fù)服務(wù)

主從復(fù)制原理

依賴于二進(jìn)制日志，binary-log.
二進(jìn)制日志中記錄引起數(shù)據(jù)庫(kù)發(fā)生改變的語(yǔ)句
Insert 、delete、update、create table

Scale-up與Scale-out區(qū)別

Scale Out是指Application可以在水平方向上擴(kuò)展。一般對(duì)數(shù)據(jù)中心的應(yīng)用而言，Scale out指的是當(dāng)添加更多的機(jī)器時(shí)，應(yīng)用仍然可以很好的利用這些機(jī)器的資源來(lái)提升自己的效率從而達(dá)到很好的擴(kuò)展性。
Scale Up是指Application可以在垂直方向上擴(kuò)展。一般對(duì)單臺(tái)機(jī)器而言，Scale Up值得是當(dāng)某個(gè)計(jì)算節(jié)點(diǎn)（機(jī)器）添加更多的CPU Cores，存儲(chǔ)設(shè)備，使用更大的內(nèi)存時(shí)，應(yīng)用可以很充分的利用這些資源來(lái)提升自己的效率從而達(dá)到很好的擴(kuò)展性。

MyCat

什么是 Mycat

是一個(gè)開(kāi)源的分布式數(shù)據(jù)庫(kù)系統(tǒng)，但是因?yàn)閿?shù)據(jù)庫(kù)一般都有自己的數(shù)據(jù)庫(kù)引擎，而Mycat并沒(méi)有屬于自己的獨(dú)有數(shù)據(jù)庫(kù)引擎，所有嚴(yán)格意義上說(shuō)并不能算是一個(gè)完整的數(shù)據(jù)庫(kù)系統(tǒng)，只能說(shuō)是一個(gè)在應(yīng)用和數(shù)據(jù)庫(kù)之間起橋梁作用的中間件。
在Mycat中間件出現(xiàn)之前，MySQL主從復(fù)制集群，如果要實(shí)現(xiàn)讀寫(xiě)分離，一般是在程序段實(shí)現(xiàn)，這樣就帶來(lái)了一個(gè)問(wèn)題，即數(shù)據(jù)段和程序的耦合度太高，如果數(shù)據(jù)庫(kù)的地址發(fā)生了改變，那么我的程序也要進(jìn)行相應(yīng)的修改，如果數(shù)據(jù)庫(kù)不小心掛掉了，則同時(shí)也意味著程序的不可用，而對(duì)于很多應(yīng)用來(lái)說(shuō)，并不能接受；

引入Mycat中間件能很好地對(duì)程序和數(shù)據(jù)庫(kù)進(jìn)行解耦，這樣，程序只需關(guān)注數(shù)據(jù)庫(kù)中間件的地址，而無(wú)需知曉底層數(shù)據(jù)庫(kù)是如何提供服務(wù)的，大量的通用數(shù)據(jù)聚合、事務(wù)、數(shù)據(jù)源切換等工作都由中間件來(lái)處理；
Mycat中間件的原理是對(duì)數(shù)據(jù)進(jìn)行分片處理，從原有的一個(gè)庫(kù)，被切分為多個(gè)分片數(shù)據(jù)庫(kù)，所有的分片數(shù)據(jù)庫(kù)集群構(gòu)成完成的數(shù)據(jù)庫(kù)存儲(chǔ)，有點(diǎn)類似磁盤(pán)陣列中的RAID0.

Mycat安裝

創(chuàng)建表結(jié)構(gòu)

CREATE DATABASE IF NOT EXISTS `weibo_simple`;
-- ------------------------------------
-- Table structure for `t_users` 用戶表
-- ------------------------------------
DROP TABLE IF EXISTS `t_users`;
CREATE TABLE `t_users` (
 `user_id` varchar(64) NOT NULL COMMENT '注冊(cè)用戶ID',
 `user_email` varchar(64) NOT NULL COMMENT '注冊(cè)用戶郵箱',
 `user_password` varchar(64) NOT NULL COMMENT '注冊(cè)用戶密碼',
 `user_nikename` varchar(64) NOT NULL COMMENT '注冊(cè)用戶昵稱',
 `user_creatime` datetime NOT NULL COMMENT '注冊(cè)時(shí)間',
 `user_status` tinyint(1) NOT NULL COMMENT '驗(yàn)證狀態(tài) 1：已驗(yàn)證 0：未驗(yàn)證',
 `user_deleteflag` tinyint(1) NOT NULL COMMENT '刪除標(biāo)記 1：已刪除 0：未刪除',
 PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- -------------------------------------
-- Table structure for `t_message`微博表
-- -------------------------------------
DROP TABLE IF EXISTS `t_message`;
CREATE TABLE `t_message` (
 `messages_id` varchar(64) NOT NULL COMMENT '微博ID',
 `user_id` varchar(64) NOT NULL COMMENT '發(fā)表用戶',
 `messages_info` varchar(255) DEFAULT NULL COMMENT '微博內(nèi)容',
 `messages_time` datetime DEFAULT NULL COMMENT '發(fā)布時(shí)間',
 `messages_commentnum` int(12) DEFAULT NULL COMMENT '評(píng)論次數(shù)',
 `message_deleteflag` tinyint(1) NOT NULL COMMENT '刪除標(biāo)記 1：已刪除 0：未刪除',
 `message_viewnum` int(12) DEFAULT NULL COMMENT '被瀏覽量',
 PRIMARY KEY (`messages_id`),
 KEY `user_id` (`user_id`),
 CONSTRAINT `t_message_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

配置server.xml

	<!-- 添加user -->
  <user name="mycat">
  <property name="password">mycat</property>
  <property name="schemas">mycat</property>
  </user>
	
	<!-- 添加user -->
  <user name="mycat_red">
  <property name="password">mycat_red</property>
  <property name="schemas">mycat</property>
	<property name="readOnly">true</property>
  </user>

配置schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
  <!-- 與server.xml中user的schemas名一致 -->
  <schema name="mycat" checkSQLschema="true" sqlMaxLimit="100">
    <table name="t_users" primaryKey="user_id" dataNode="dn1" rule="rule1"/>
   
    <table name="t_message" type="global" primaryKey="messages_id" dataNode="dn1" />
  </schema>
<dataNode name="dn1" dataHost="jdbchost" database="weibo_simple


" />
  
  <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="1"
        writeType="0" dbType="mysql" dbDriver="native" switchType="1"
        slaveThreshold="100">
     <heartbeat>select user()</heartbeat> 
    <writeHost host="hostMaster" url="172.27.185.1:3306" user="root" password="root">
    </writeHost>
    <writeHost host="hostSlave" url="172.27.185.2:3306" user="root" password="root"/>
  </dataHost>
  
</mycat:schema>

配置rule.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://org.opencloudb/">
	 <tableRule name="rule1">
    <rule>
      <columns>user_id</columns>
      <algorithm>func1</algorithm>
    </rule>
  </tableRule>
  <function name="func1" class="org.opencloudb.route.function.AutoPartitionByLong">
 	<property name="mapFile">autopartition-long.txt</property>
  </function>
</mycat:rule>

為了更好地定位錯(cuò)誤，修改log4j.xml

雙擊startup_nowrap.bat開(kāi)始啟動(dòng)

常見(jiàn)問(wèn)題
SHOW MASTER STATUS 如果為，則在my.ini文件中添加一行
log-bin=mysql-bin

給賬號(hào)分配權(quán)限
grant all privileges on . to ‘root'@‘172.27.185.1' identified by ‘root';

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過(guò)程技巧大全》及《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》

希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。