MySQL主從復(fù)制與讀寫(xiě)分離（原理適用場(chǎng)景+實(shí)踐操作）

MySQL主從復(fù)制與讀寫(xiě)分離

一、前言

? 前面我們已經(jīng)對(duì)MySQL數(shù)據(jù)庫(kù)的安裝、命令、備份、索引、事務(wù)以及存儲(chǔ)引擎等各個(gè)方面有了初步的理解，而今天我們將從宏觀的角度來(lái)理解什么是MySQL數(shù)據(jù)庫(kù)的主從復(fù)制與讀寫(xiě)分離。

十余年的和縣網(wǎng)站建設(shè)經(jīng)驗(yàn)，針對(duì)設(shè)計(jì)、前端、開(kāi)發(fā)、售后、文案、推廣等六對(duì)一服務(wù)，響應(yīng)快，48小時(shí)及時(shí)工作處理。營(yíng)銷(xiāo)型網(wǎng)站建設(shè)的優(yōu)勢(shì)是能夠根據(jù)用戶(hù)設(shè)備顯示端的尺寸不同，自動(dòng)調(diào)整和縣建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無(wú)論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計(jì)，從而大程度地提升瀏覽體驗(yàn)。創(chuàng)新互聯(lián)從事“和縣網(wǎng)站設(shè)計(jì)”,“和縣網(wǎng)站推廣”以來(lái)，每個(gè)客戶(hù)項(xiàng)目都認(rèn)真落實(shí)執(zhí)行。

二、場(chǎng)景描述

? 在實(shí)際的生產(chǎn)環(huán)境中，如果對(duì)MySQL數(shù)據(jù)庫(kù)的讀與寫(xiě)都在一臺(tái)服務(wù)器上進(jìn)行操作，無(wú)論是在安全性，高可用性、還是高并發(fā)性等諸多方面都是無(wú)法滿(mǎn)足的；而這就需要對(duì)MySQL數(shù)據(jù)庫(kù)進(jìn)行主從復(fù)制與讀寫(xiě)分離。

? 我們使用一個(gè)架構(gòu)圖來(lái)引出MySQL數(shù)據(jù)庫(kù)主從復(fù)制的原理以及其作用，請(qǐng)看下圖：

服務(wù)器作用：

MySQL主服務(wù)器：負(fù)責(zé)數(shù)據(jù)的寫(xiě)入；

MySQL從服務(wù)器：同步主服務(wù)器的數(shù)據(jù)并進(jìn)行數(shù)據(jù)的輪循讀??；

? 那么這三臺(tái)具備相同服務(wù)的MySQL服務(wù)器就稱(chēng)為“MySQL群集”。我們可以從上圖中看出，這樣的安排實(shí)現(xiàn)數(shù)據(jù)同步的基礎(chǔ)上，實(shí)現(xiàn)數(shù)據(jù)的讀寫(xiě)分離，從而在保證數(shù)據(jù)的可靠性的同時(shí)也大大減輕了主服務(wù)器的壓力。

? 下面我們對(duì)MySQL的主從復(fù)制和讀寫(xiě)分離進(jìn)行逐一介紹并給出配置實(shí)例。

三、MySQL主從復(fù)制原理

? MySQL主從復(fù)制與讀寫(xiě)分離有著緊密的聯(lián)系，可以這么說(shuō)，MySQL的讀寫(xiě)分離的實(shí)現(xiàn)需要基于主從復(fù)制的基礎(chǔ)之上。

3.1MySQL支持的復(fù)制類(lèi)型

1. 基于語(yǔ)句的復(fù)制；——基于SQL語(yǔ)言命令的復(fù)制形式，使用SQL命令執(zhí)行復(fù)制，效率高
2. 基于行的復(fù)制；——復(fù)制數(shù)據(jù)庫(kù)變化的內(nèi)容，不是執(zhí)行命令
3. 混合類(lèi)型的復(fù)制；——默認(rèn)采用語(yǔ)句類(lèi)型的復(fù)制，如果發(fā)現(xiàn)有不確定問(wèn)題等其他原因造成無(wú)法復(fù)制的情況再進(jìn)行基于行的復(fù)制

3.2MySQL復(fù)制的工作過(guò)程

我們通過(guò)下圖來(lái)理解MySQL主、從服務(wù)器是如何進(jìn)行復(fù)制同步的過(guò)程的。

1）首先，MySQL主服務(wù)器在更新數(shù)據(jù)庫(kù)或其他進(jìn)行數(shù)據(jù)庫(kù)相關(guān)操作時(shí)，會(huì)在二進(jìn)制日志文件中記錄這些改變（這我們?cè)谇懊娴脑隽總浞菖c恢復(fù)的文章中進(jìn)行了講述，log-bin的設(shè)置以及如何使用mysqladmin命令刷新該日志。）當(dāng)寫(xiě)入日志完成后，主服務(wù)器會(huì)告知存儲(chǔ)引擎提交事務(wù)；

2）MySQL從服務(wù)器會(huì)將主服務(wù)器的二進(jìn)制日志文件（Binary log）復(fù)制到其中繼日志（Relay log）中。中繼日志通常存放在系統(tǒng)緩存中，因此中繼日志的開(kāi)銷(xiāo)很小；

3）從服務(wù)器通過(guò)自身線(xiàn)程從中繼日志中讀取事件，更新自身的日志文件使其與主服務(wù)器中的數(shù)據(jù)一致。

ps:復(fù)制過(guò)程中有一個(gè)很重要的限制，即在從服務(wù)器上復(fù)制是串行化的，這就表明主服務(wù)器上的并行更新操作不能在從服務(wù)器上并行操作。

四、MySQL主從復(fù)制配置流程與實(shí)際操作

4.1主從復(fù)制所需環(huán)境

所需設(shè)備（我們?cè)谔摂M機(jī)上模擬進(jìn)行配置）清單：

Centos7一臺(tái)：作為主服務(wù)器——master，ip地址為192.168.68.133

Centos7兩臺(tái)：作為從服務(wù)器——slave1、slave2IP地址分別為192.168.68.129、192.168.68.132

所需安裝服務(wù)：

安裝并且配置ntp服務(wù)、都安裝了MySQL5.7版本的數(shù)據(jù)庫(kù)

4.2主從復(fù)制具體流程步驟

4.2.1準(zhǔn)備工作

? 首先，我們聯(lián)想一下增量備份的恢復(fù)操作，我們都是依賴(lài)于data目錄下的二進(jìn)制日志文件，通過(guò)兩種方法實(shí)現(xiàn)的，其中一種就是根據(jù)時(shí)間節(jié)點(diǎn)進(jìn)行備份恢復(fù)操作的。那么我們需要進(jìn)行MySQL主從服務(wù)器復(fù)制，就需要先同步所有MySQL服務(wù)器的系統(tǒng)時(shí)間。

4.2.1.1主服務(wù)器準(zhǔn)備工作的配置

1）服務(wù)器名稱(chēng)設(shè)置以區(qū)別

hostnamectl set-hostname master
su

2）安裝配置ntp服務(wù)

yum install ntp -y 
#修改ntp服務(wù)配置文件，添加下面的兩句
vim /etc/ntp.conf

server 127.127.68.0         #服務(wù)器本地網(wǎng)段，127.127表示的就是192.168
fudge 127.127.68.0 stratum 8 #時(shí)區(qū)設(shè)置（東八區(qū)）
#保存退出
systemctl start ntpd
#可以使用netstat -nutp | grep ntpd 命令查看服務(wù)開(kāi)啟狀態(tài)

3）關(guān)閉防火墻和SELinux功能

systemctl stop firewalld
setenforce 0

4.2.1.2兩臺(tái)從服務(wù)器的準(zhǔn)備工作的配置（二者步驟一致）

1）服務(wù)器名稱(chēng)設(shè)置

hostnamectl set-hostname slave1（slave2）
su

2）兩臺(tái)從服務(wù)器上安裝ntp和ntpdate服務(wù)并開(kāi)啟服務(wù)

yum install ntp ntpdate -y
systemctl start ntpd

3）兩臺(tái)從服務(wù)器上關(guān)閉防火墻和SELinux功能

systemctl stop firewalld
setenforce 0

4）使用ntpdate命令進(jìn)行時(shí)間同步

[root@slave1 ~]# /usr/sbin/ntpdate 192.168.68.133
 9 Jan 15:35:13 ntpdate[67450]: the NTP socket is in use, exiting

4.2.2MySQL服務(wù)的配置

4.2.2.1主服務(wù)器上MySQL的修改與配置

1）修改mysql的主配置文件并重啟mysql服務(wù)

vim /etc/my.cnf
#配置如下：
log-bin = master-bin  #二進(jìn)制日志文件 master-bin可以自己設(shè)置
server-id = 1         #服務(wù)器的id號(hào)，用于區(qū)別
log-slave-updates=true #開(kāi)啟從服務(wù)器更新日志功能（結(jié)合復(fù)制流程連接）
systemctl restart mysqld.service

2）進(jìn)入數(shù)據(jù)庫(kù)進(jìn)行權(quán)限設(shè)置（授權(quán)）與刷新

[root@lokott ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant replication slave on *.* to 'myslave'@'192.168.68.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;#刷新系統(tǒng)權(quán)限表
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      603 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

注意核心命令：授權(quán)命令的解釋?zhuān)€有以上位置Position 記錄數(shù)據(jù)603

命令格式：

grant replication slave on *.* to 'myslave'@'192.168.68.%' identified by '123456';
#grant ——授權(quán)命令   replication slave  從服務(wù)器復(fù)制操作     *.*表示的是所有數(shù)據(jù)庫(kù)所有表

其具體含義是：賦予192.168.68.0網(wǎng)段的主機(jī)（服務(wù)器）復(fù)制（同步）主服務(wù)器的所有數(shù)據(jù)庫(kù)數(shù)據(jù)；

4.2.2.2從服務(wù)器上MySQL的修改與配置

1）修改主配置文件并重啟服務(wù)

vim /etc/my.cnf
#配置如下
log-bin = mysql-bin
server-id = 2
relay-log = relay-log-bin     #中繼日志
relay-log-index = slave-relay-log-bin.index #索引文件

#保存退出重啟
systemctl restart mysqld.service

2)進(jìn)入數(shù)據(jù)庫(kù)配置同步

[root@slave1 ~]# mysql -uroot -p
...#省略部分內(nèi)容
mysql> change master to master_host='192.168.68.133',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.68.133
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 603
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
 ...#省略部分內(nèi)容 上面的兩行表示從服務(wù)器給出的IO線(xiàn)程和數(shù)據(jù)庫(kù)SQL語(yǔ)句都在運(yùn)行狀態(tài)
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

4.2.3進(jìn)行主從復(fù)制的測(cè)試與驗(yàn)證

在主服務(wù)器上創(chuàng)建一個(gè)新的數(shù)據(jù)庫(kù)，新建表和數(shù)據(jù)；在服務(wù)器上進(jìn)行查看；

主服務(wù)器：

create database test;
Query OK, 1 row affected (0.00 sec)

從服務(wù)上slave1查詢(xún)：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> exit
Bye
[root@slave1 ~]# 

從服務(wù)器slave2上查詢(xún)：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> exit
Bye
[root@slave2 ~]# 

4.3主從復(fù)制小結(jié)

? 該配置實(shí)現(xiàn)的是從服務(wù)器通過(guò)主服務(wù)器給予的權(quán)限進(jìn)行復(fù)制操作，我們需要通過(guò)實(shí)驗(yàn)配置理解上述的主從復(fù)制的原理以及其復(fù)制的過(guò)程。只有在理解掌握了如何進(jìn)行MySQL數(shù)據(jù)庫(kù)的主從復(fù)制，才能理解和進(jìn)行MySQL讀寫(xiě)分離的配置操作。

五、MySQL讀寫(xiě)分離原理

? 讀寫(xiě)分離的原理，簡(jiǎn)單來(lái)說(shuō)就是實(shí)現(xiàn)上圖中，在主服務(wù)器上寫(xiě)數(shù)據(jù)，使用從服務(wù)器輪循讀取數(shù)據(jù)的功能。

? 我們結(jié)合下圖來(lái)理解讀寫(xiě)分離的過(guò)程

基于代理層實(shí)現(xiàn)：代理一般位于客戶(hù)端與服務(wù)器之間，代理服務(wù)器接到客戶(hù)端請(qǐng)求通過(guò)判斷后轉(zhuǎn)發(fā)到后端數(shù)據(jù)庫(kù)，有兩個(gè)代表性程序。

1）MySQL_Proxy：通過(guò)自帶的lua腳本進(jìn)行SQL判斷；

2）Amoeba：Java語(yǔ)言開(kāi)發(fā)，阿里巴巴將其用于生產(chǎn)環(huán)境，不支持事務(wù)與存儲(chǔ)過(guò)程；

六、MySQL讀寫(xiě)分離配置流程與實(shí)際操作

環(huán)境：基于上面主從復(fù)制的流程進(jìn)行后續(xù)陪配置

添加設(shè)備：Centos7兩臺(tái)：其中一臺(tái)作為Amoeba代理服務(wù)器，另一臺(tái)作為客戶(hù)端測(cè)試服務(wù)器。

所需軟件包：在Amoeba代理服務(wù)器上需要安裝jdk和amoeba相關(guān)環(huán)境及應(yīng)用（amoeba使用Java開(kāi)發(fā)）

1）安裝jdk環(huán)境和ameoba環(huán)境

[root@amoeba ~]# cd tar/
[root@amoeba tar]# ls
amoeba-mysql-binary-2.2.0.tar.gz  jdk-6u14-linux-x64.bin 

 cd tar/
 ls
 cp jdk-6u14-linux-x64.bin /usr/local/
 cd /usr/local/
 chmod +x jdk-6u14-linux-x64.bin 
 ./jdk-6u14-linux-x64.bin 
 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:$JAVA_HOME/jre/lib
 export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
 export AMOEBA_HOME=/usr/local/amoeba
 export PATH=$PATH:$AMOEBA_HOME/bin

 source /etc/profile
 mkdir /usr/local/amoeba
 #回到壓縮包目錄下解壓amoeba
 tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba
 cd /usr/local/

2）配置amoeba相關(guān)參數(shù)

配置路徑在/usr/local/amoeba/conf中，配置文件為ameoba.xml,dbServers.xml。前者為amoeba主配置文件（還記得tomcat嗎？），后者是數(shù)據(jù)庫(kù)服務(wù)器的相關(guān)配置文件。

1、更改ameoba.xml

30                                         <property name="user">amoeba</property>
31 
32                                         <property name="password">123456</property>
#user是我們?cè)赾lient端登錄amoeba代理服務(wù)器的身份名稱(chēng)，password是登錄密碼，待會(huì)我們將會(huì)在client端上登錄
15                 <property name="defaultPool">master</property>
116 
117                 <property name="writePool">master</property>
118                 <property name="readPool">slaves</property>
119 
#defaultPool表示默認(rèn)的服務(wù)器 writePool表示指定寫(xiě)服務(wù)器（組），readPool表示指定讀服務(wù)器（組），slaves將在dbServers.xml文件中配置

2、更改dbServers.xml

 26                         <property name="user">test</property>
 27 
 28                         <!--  mysql password -->
 29                         <property name="password">123123</property>

 45        <dbServer name="master"  parent="abstractServer">
 46                 <factoryConfig>
 47                         <!-- mysql ip -->
 48                         <property name="ipAddress">192.168.68.133</property>
 49                 </factoryConfig>
 50         </dbServer>
 51 
 52         <dbServer name="slave1"  parent="abstractServer">
 53                 <factoryConfig>
 54                         <!-- mysql ip -->
 55                         <property name="ipAddress">192.168.68.129</property>
 56                 </factoryConfig>
 57         </dbServer>
 58 
 59         <dbServer name="slave2"  parent="abstractServer">
 60                 <factoryConfig>
 61                         <!-- mysql ip -->
 62                         <property name="ipAddress">192.168.68.132</property>
 63                 </factoryConfig>
 64         </dbServer>
 65 
 66         <dbServer name="slaves" virtual="true">
 67                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
 68                         <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
 69                         <property name="loadbalance">1</property>
 70 
 71                         <!-- Separated by commas,such as: server1,server2,server1 -->
 72                         <property name="poolNames">slave1,slave2</property>
 73                 </poolConfig>
 74         </dbServer>

3、開(kāi)啟amoeba（可以在另一個(gè)terminal查看端口netstat -natp | grep 8066）

[root@amoeba bin]# amoeba start &
[1] 121608
[root@amoeba bin]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2020-01-10 08:20:03,413 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf
2020-01-10 08:20:03,566 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.
2020-01-10 08:20:03,567 INFO  net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:39466.

4、客戶(hù)機(jī)client端上安裝mysql數(shù)據(jù)庫(kù)，登錄查看

[root@localhost ~]# mysql -u amoeba -p123456 -h 192.168.68.144 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2023306452
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.01 sec)

#因?yàn)榇藭r(shí)我們還沒(méi)有創(chuàng)建如何數(shù)據(jù)表和數(shù)據(jù)，所以在客戶(hù)端上是查看不到任何數(shù)據(jù)的。

5、我們?cè)谥鞣?wù)器上創(chuàng)建一個(gè)表并且在從服務(wù)器上查看是否存在這個(gè)表，存在則主從復(fù)制正常

主服務(wù)器：

mysql> use test;
Database changed
mysql> create table info (id int(5) not null primary key auto_increment,name varchar(10) not null);
Query OK, 0 rows affected (0.02 sec)

mysql> desc info;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(5)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> select * from info;
Empty set (0.00 sec)

從服務(wù)器：

[root@slave1 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.17-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| info           |
+----------------+
1 row in set (0.00 sec)

6、在client端上查看這個(gè)表

mysql> show tables;
Empty set (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| info           |
+----------------+
1 row in set (0.00 sec)

7、那么我們?nèi)绾悟?yàn)證讀寫(xiě)分離呢？還記得我們?cè)趶姆?wù)器上的start slave命令嗎？我們可以停止主從復(fù)制,使用stop slave即可

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

8、我們?cè)赾lient端插入新的數(shù)據(jù)，分別在主從服務(wù)器上進(jìn)行查看

client端：

mysql> insert into info  (name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql> select * from info;
+----+----------+
| id | name     |
+----+----------+
|  2 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

主服務(wù)器：

mysql> select * from info;
+----+----------+
| id | name     |
+----+----------+
|  2 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

從服務(wù)器：

mysql> select * from info;
Empty set (0.00 sec)

主從服務(wù)器寫(xiě)分離小結(jié)

? 從以上的實(shí)驗(yàn)中，我們可以得出結(jié)論，經(jīng)過(guò)主從復(fù)制與寫(xiě)分離的配置，我們可以將寫(xiě)數(shù)據(jù)的任務(wù)在主服務(wù)器上進(jìn)行，主服務(wù)器寫(xiě)入完成后，由從服務(wù)器經(jīng)過(guò)主從復(fù)制的過(guò)程進(jìn)行（start slave）復(fù)制，因?yàn)樵谏a(chǎn)環(huán)境中我們不能stop slave（停止主從復(fù)制）的！??！。但是為了驗(yàn)證讀寫(xiě)分離原理，我們只能先stop slave了。

? 下面驗(yàn)證讀分離。

我們?cè)趶姆?wù)器上分別寫(xiě)入一些數(shù)據(jù)（兩臺(tái)從服務(wù)器上寫(xiě)的不一樣）

slave1：

mysql> insert into info (name) values('lisi');
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
+----+------+
| id | name |
+----+------+
|  1 | lisi |
+----+------+
1 row in set (0.00 sec)

slave2：

mysql> insert into info (name) values('wangwu');
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
+----+--------+
| id | name   |
+----+--------+
|  1 | wangwu |
+----+--------+
1 row in set (0.00 sec)

client 查詢(xún)：（可能會(huì)有問(wèn)題，因?yàn)槭遣捎昧薬uto_increment自增列）

因此我們?cè)趶姆?wù)器上開(kāi)啟復(fù)制功能，在主從服務(wù)器上修改字段，然后在從服務(wù)器上關(guān)閉復(fù)制功能，并且重新寫(xiě)入不同的數(shù)據(jù)，在client端查看。

兩臺(tái)從服務(wù)器：

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

主服務(wù)器：

mysql> alter table info modify id int(5) not null;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc info;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(5)      | NO   | PRI | NULL    |       |
| name  | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> delete from info;
Query OK, 1 row affected (0.00 sec)

從服務(wù)器：

mysql> alter table info modify id int(5) not null;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc info;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(5)      | NO   | PRI | NULL    |       |
| name  | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> delete from info;
Query OK, 1 row affected (0.00 sec)

client ：重新寫(xiě)入數(shù)據(jù)

mysql> insert into info  (id,name) values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)

主服務(wù)器：

mysql> select * from info;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)
#此時(shí)需要重新查看狀態(tài)記錄position，此時(shí)如下：
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |     2955 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

從服務(wù)器：

mysql> change master to master_host='192.168.68.133',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=2955;
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.
mysql> STOP SLAVE IO_THREAD FOR CHANNEL '';
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.68.133',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=2955;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.68.133
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 2955
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2955
              Relay_Log_Space: 526
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 5bb93767-328a-11ea-820a-000c290bd936
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

算了，我們將三個(gè)服務(wù)器上的數(shù)據(jù)表的數(shù)據(jù)都刪了重新進(jìn)行主從復(fù)制吧；按照主從復(fù)制執(zhí)行之后繼續(xù)進(jìn)行讀分離的操作；

關(guān)閉從服務(wù)器主從復(fù)制：

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

從服務(wù)器上寫(xiě)入不同數(shù)據(jù)：

slave1：

mysql> insert into info values(2,'lisi');
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

slave2：

mysql> insert into info values(3,'wangwu');
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  3 | wangwu   |
+----+----------+
2 rows in set (0.00 sec)

client：

mysql> select * from info;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

mysql> select * from info;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  3 | wangwu   |
+----+----------+
2 rows in set (0.00 sec)

讀分離小結(jié)

? MySQL讀分離則是在MySQL的從服務(wù)器上進(jìn)行數(shù)據(jù)讀取操作。其中兩臺(tái)做負(fù)載均衡，輪循讀取，減輕壓力，提高并發(fā)訪(fǎng)問(wèn)。

七、總結(jié)

        本文主要介紹了MySQL主從復(fù)制與讀寫(xiě)分離原理，以及如何配置和驗(yàn)證主從復(fù)制與讀寫(xiě)分離的實(shí)驗(yàn)。
        通過(guò)實(shí)驗(yàn)我們可以得出以下結(jié)論：
        在生產(chǎn)環(huán)境中，我們不會(huì)將從服務(wù)器停止復(fù)制同步功能，并且不會(huì)在從服務(wù)上寫(xiě)數(shù)據(jù)，因?yàn)檫@樣會(huì)導(dǎo)致某些問(wèn)題，如果在從服務(wù)器上寫(xiě)了與主服務(wù)器沖突的數(shù)據(jù)，主服務(wù)器不會(huì)同步從的，主服務(wù)器上與從服務(wù)器所沖突的數(shù)據(jù)不會(huì)同步給從服務(wù)器。

網(wǎng)站標(biāo)題：MySQL主從復(fù)制與讀寫(xiě)分離（原理適用場(chǎng)景+實(shí)踐操作）
文章轉(zhuǎn)載：http://bm7419.com/article36/phocpg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供外貿(mào)網(wǎng)站建設(shè)、品牌網(wǎng)站建設(shè)、定制網(wǎng)站、、品牌網(wǎng)站制作、搜索引擎優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)