MySQL主從複製與讀寫分離

標籤：none   增加   profile   error:   測試   訪問   java   cin   之間   

MySQL主從複製與讀寫分離實驗介紹

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

MySQL主從複製原理

1、在每個事務更新資料前，Master伺服器在二進位日誌中記錄這些改變。寫入二進位檔案完成後，Master伺服器通知儲存引擎提交事務。

2、Slave伺服器將Master的Binary log複製到其中繼日誌（Relay log）。首先Slave開始一個背景工作執行緒——I/O線程，I/O線程在Master上開啟一個普通的串連，然後開始Binary log dump process。Binary log dump process從Master的二進位日誌中讀取時間，如果已經跟上Master，它會睡眠等待Master產生新的事件。I/O線程將這些事件寫入中繼日誌中。

3、SQL slave thread（SQL從線程）處理最後一步。SQL線程從中繼日誌中讀取事件，並重放其中的事件從而更新Slave的資料，使其與Master中的資料一致。只要該線程與I/O線程保持一致，中繼日誌通常會位於系統的緩衝中，所以中繼日誌開銷很小。

MySQL讀寫分離原理

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

基於中間代理層實現：代理一般位於用戶端和服務端之間，Proxy 伺服器接到用戶端請求通過判斷轉寄到後端資料庫，這部分通過Amoeba實現。

案例環境

如所示

實驗過程搭建MySQL主從複製

1、建立時間同步環境，在主節點上搭建時間原始伺服器。

[[email protected] ~]# yum install ntp -y[[email protected] ~]# vim /etc/ntp.conf                  #在設定檔最後加兩行server 127.127.58.0                                  #設定本地時鐘源fudge 127.127.58.0 stratum 8                         #設定時間層級為8（限制在15內）[[email protected] ~]# service ntpd restart               #重啟服務[[email protected] ~]# systemctl stop firewalld.service   #關閉防火牆[[email protected] ~]# setenforce 0                       #關閉增強安全功能

2、在兩台從節點上分別進行時間同步。

[[email protected] ~]# yum install ntp -y[[email protected] ~]# /usr/sbin/ntpdate 192.168.58.131      #和時間原始伺服器同步10 Jul 11:07:16 ntpdate[28695]: the NTP socket is in use, exiting[[email protected] ~]# systemctl stop firewalld.service   #關閉防火牆[[email protected] ~]# setenforce 0                       #關閉增強安全功能

3、安裝MySQL，這步在前面講過，就省略掉了。

4、配置MySQL Master主伺服器。

[[email protected] ~]# vim /etc/my.cnfserver-id = 11                                           #修改server-id，注意三台伺服器id不能重複log-bin=master-bin                                       #修改主伺服器記錄檔log-slave-updates=true                                   #增加開啟主從同步功能[[email protected] ~]# systemctl restart mysqld.service       #重啟MySQL伺服器

5、登入MySQL服務，給從伺服器授權。

[[email protected] ~]# mysql -u root -pEnter password: mysql> GRANT REPLICATION SLAVE ON *.* TO ‘myslave‘@‘192.168.58.%‘ IDENTIFIED BY ‘123456‘;    #授予192.168.58.0網段的主機分別以myslave，123456為使用者名稱，密碼的使用者REPLICATION SLAVE許可權。mysql> FLUSH PRIVILEGES;   #重新整理使用權限設定mysql> show master status; #查看主伺服器狀態，file,position兩個值很重要，後面要用到。+-------------------+----------+--------------+------------------+-------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+-------------------+| master-bin.000002 |     2614 |              |                  |                   |+-------------------+----------+--------------+------------------+-------------------+

6、配置MySQL Slave從伺服器,在兩台從伺服器上都執行相同的操作，只有server-id不同

[[email protected] ~]# vim /etc/my.cnfserver-id       = 22                        #設定server-id，三台伺服器不能一樣relay-log=relay-log-bin                     #從主伺服器上同步處理記錄檔案記錄到本地relay-log-index=slave-relay-bin.index       #定義relay-log的位置和名稱[[email protected] ~]# service mysqld restart[[email protected] ~]# mysql -u root -pEnter password: mysql> change master to master_host=‘192.168.58.131‘,master_user=‘myslave‘,master_password=‘123456‘‘,master_log_file=‘master-bin.000002‘,master_log_pos=2614;   #這條命令就是用來指定主伺服器，master_log_file和master_log_pos參數和上面對應。mysql> start slave;    #開啟同步mysql> show slave status\G; #查看slave狀態，確保 Slave_IO_Running，Slave_SQL_Running都是yex*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.58.131                  Master_User: myslave                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: master-bin.000002          Read_Master_Log_Pos: 2614               Relay_Log_File: relay-log-bin.000005                Relay_Log_Pos: 1955        Relay_Master_Log_File: master-bin.000002             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: 2614              Relay_Log_Space: 2535              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

7、驗證主從同步效果。通過在主伺服器上建立一個新資料庫，然後查看是否同步成功來判斷。

主伺服器中的資料庫

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || school             || sys                || test1              |+--------------------+6 rows in set (0.23 sec)

從伺服器中的資料庫

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || school             || sys                || test1              || wq                 || yxxx               |+--------------------+8 rows in set (0.31 sec)

下面，在在主伺服器上建立一個新的資料庫test02，進而查看是否同步成功。

mysql> create database test02;Query OK, 1 row affected (3.86 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || school             || sys                || test02             || test1              |+--------------------+7 rows in set (0.01 sec)

查看從伺服器的資料庫。同步成功！

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || school             || sys                || test02             || test1              || wq                 || yxxx               |+--------------------+9 rows in set (0.15 sec)

搭建MySQL讀寫分離

1、在AmoebaProxy 伺服器上安裝Java環境，因為Amoeba服務是基於Java1.5開發的。

systemctl stop firewalld.service               #關閉防火牆        setenforce 0                                   #關閉增強性安全功能cp jdk-6u14-linux-x64.bin /usr/local/          #將軟體包複製到指定目錄./jdk-6u14-linux-x64.bin                       #執行安裝指令碼mv jdk1.6.0_14/ /usr/local/jdk1.6              #為了方便，修改檔案名稱vim /etc/profile                               #將Java添加到環境變數中export JAVA_HOME=/usr/local/jdk1.6             #將下面幾行插入到檔案中export 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/binsource /etc/profile                            #重新整理檔案，使改動生效

2、安裝並配置Amoeba軟體。

[[email protected] ~]#mkdir /usr/local/amoeba                        #為Amoeba建立工作目錄[[email protected] ~]#tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/   #解壓[[email protected] ~]#chmod -R 755 /usr/local/amoeba/                #修改檔案許可權[[email protected] ~]#/usr/local/amoeba/bin/amoeba   #執行Amoeba服務amoeba start|stop說明amoeba安裝成功

3、配置Amoeba讀寫分離，兩個Slave讀負載平衡。

mysql> grant all on *.* to [email protected]‘192.168.58.%‘ identified by ‘123.com‘;#在三台mysql伺服器上添加許可權開放給amoeba訪問

4、回到Amoeba伺服器上，首先配置amoeba.xml設定檔。

[[email protected] ~]# vim /usr/local/amoeba/conf/amoeba.xml <property name="authenticator">            <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">                        <property name="user">amoeba</property> #用戶端用來登入Amoeba伺服器的使用者名稱                        <property name="password">123456</property> #用戶端用來登入Amoeba伺服器的密碼                        <property name="filter">                            <bean class="com.meidusa.amoeba.server.IPAccessController">                            <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>                            </bean>                        </property>            </bean></property>

                <property name="defaultPool">master</property>#預設伺服器集區                <property name="writePool">master</property>#master伺服器用於寫                <property name="readPool">slaves</property>#slaves伺服器用於讀

配置dbServers.xml檔案

[[email protected] ~]# vim /usr/local/amoeba/conf/dbServers.xml                         <!-- mysql schema                         <property name="schema">test</property>#將這句話注釋掉，否則無法用用戶端通過amoebaProxy 伺服器訪問mysql伺服器集區                        -->                        <!-- mysql user -->                        <property name="user">test</property> #修改為用於登入伺服器集區的使用者名稱                        <property name="password">123.com</property> #修改為用於登入伺服器集區的密碼

 <dbServer name="master"  parent="abstractServer">#配置主伺服器                <factoryConfig>                        <!-- mysql ip -->                        <property name="ipAddress">192.168.58.131</property>#主伺服器的IP                </factoryConfig>        </dbServer>        <dbServer name="slave1"  parent="abstractServer">#從伺服器1的mysql伺服器                <factoryConfig>                        <!-- mysql ip -->                        <property name="ipAddress">192.168.58.144</property> #從伺服器1的mysql伺服器IP                </factoryConfig>        </dbServer>        <dbServer name="slave2"  parent="abstractServer">#從伺服器2的mysql伺服器                <factoryConfig>                        <!-- mysql ip -->                        <property name="ipAddress">192.168.58.145</property> #從伺服器2的mysql伺服器IP                </factoryConfig>        </dbServer>        <dbServer name="slaves" virtual="true">#定義從伺服器集區                <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">                        <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->                        <property name="loadbalance">1</property>                        <!-- Separated by commas,such as: server1,server2,server1 -->                        <property name="poolNames">slave1,slave2</property>#定義從伺服器集區中有兩台伺服器                </poolConfig>        </dbServer>

5、配置好後，啟動Amoeba軟體，其預設連接埠為tcp 8066.

[[email protected] ~]# /usr/local/amoeba/bin/amoeba start &[1] 69919[[email protected] ~]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml2018-07-10 16:53:55,472 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf2018-07-10 16:53:55,925 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.2018-07-10 16:53:55,925 INFO  net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:54818.^C[[email protected] ~]# netstat -ntap | grep java #8066連接埠已經開啟tcp6       0      0 127.0.0.1:54818         :::*                    LISTEN      69919/java          tcp6       0      0 :::8066                 :::*                    LISTEN      69919/java          tcp6       0      0 192.168.58.136:41992    192.168.58.145:3306     ESTABLISHED 69919/java          tcp6       0      0 192.168.58.136:33236    192.168.58.144:3306     ESTABLISHED 69919/java          tcp6       0      0 192.168.58.136:48134    192.168.58.131:3306     ESTABLISHED 69919/java 

6、到client主機上進行測試。

[[email protected] 案頭]# mysql -u amoeba -p123456 -h 192.168.58.136 -P8066mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || school             || sys                || test02             || test1              |+--------------------+7 rows in set (0.22 sec)mysql> 

在Master上建立一個表，同步到各從伺服器。

mysql> use test1;Database changedmysql> create table zang (id int(10),name varchar(10),address varchar(20));

關閉兩台從伺服器的同步功能。

mysql> stop slave;

----在主伺服器上---內容不會同步use test1insert into zang values(‘1‘,‘zhang‘,‘this_is_master‘);----從伺服器1----use test1;insert into zang values(‘2‘,‘zhang‘,‘this_is_slave1‘);----從伺服器2----use test1;insert into zang values(‘3‘,‘zhang‘,‘this_is_slave2‘);

------在用戶端上測試----第一次會向從伺服器1讀資料-第二次會向從伺服器2讀取mysql> select * from test1.zang;------+-------+----------------+| id   | name  | address        |+------+-------+----------------+|    3 | zhang | this_is_slave2 |+------+-------+----------------+3 rows in set (0.03 sec)mysql> select * from test1.zang;+------+-------+----------------+| id   | name  | address        |+------+-------+----------------+|    2 | zhang | this_is_slave1 |+------+-------+----------------+3 rows in set (0.25 sec)------在通過用戶端串連資料庫後寫入的資料只有主伺服器會記錄mysql> insert into zang values(‘5‘,‘zhang‘,‘write_test‘);到主伺服器中查看錶，會發現有兩條記錄，而從伺服器中只有一條記錄，說明實現了讀寫分離+------+-------+----------------+| id   | name  | address        |+------+-------+----------------+|    1 | zhang | this_is_master ||    5 | zhang | write_test     |+------+-------+----------------+3 rows in set (0.01 sec)

MySQL主從複製與讀寫分離