MySQL主從複製讀寫分離

l文峰發表於2020-12-28
MySql

MySQL主從複製與讀寫分離

MySQL使用主從複製與讀寫分離的原因

1、在企業應用中，成熟的業務通常資料量都比較大
2、單臺MySQL在安全性、高可用性和高併發方面都無法滿足實際的需求
（myisam基於表級鎖定；innodb基於行級鎖定 --儲存引擎不能滿足併發讀、寫的需求）
3、配置多臺主從資料庫伺服器以實現讀寫分離

MySQL主從複製原理

MySQL的複製型別

基於語句的複製
基於行的複製
混合型別的複製（語句、行、日誌檔案）

MySQL讀寫分離原理

1、只在主伺服器上寫，只在從伺服器上讀
2、主資料庫處理事務性查詢，從資料庫處理select查詢
3、資料庫複製用於將事務性查詢的變更同步到叢集中的從資料庫
4、複製的基本過程如下：

  • Master將使用者對資料庫更新的操作以二進位制格式儲存到Binary Log日誌檔案中
  • Slave上面的IO程式連線上Master，並請求從指定日誌檔案的指定位置（或者從最開始的日誌）之後的日誌內容
  • Master接收來自Slave的IO程式的請求後，通過負責複製的IO程式根據請求資訊讀取制定日誌指定位置之後的日誌資訊，返回給Slave的IO程式。返回資訊中除了日誌所包含的資訊之外，還包括本次返回的資訊已經到Master端的bin-log檔案的名稱以及bin-log的位置
  • Slave的IO程式接收到資訊後，將接收到的日誌內容依次新增到Slave端的relay-log檔案的最末端，並將讀取到的Master端的bin-log的檔名和位置記錄到master-info檔案中，以便在下一次讀取的時候能夠清除的告訴Master“我需要從某個bin-log的哪個位置開始往後的日誌內容，請發給我”
  • SlaveSql程式檢測到relay-log中新增了內容後，會馬上解析relay-log的內容稱為在Master端真實執行時候的那些可執行的內容，並在自身執行

MySQL主從複製讀寫分離步驟

環境

主庫
20.0.0.11
從庫
20.0.0.12
20.0.0.13
amoeba
20.0.0.15
客戶機
20.0.0.16

時間同步 (所有裝置時間保持一致)

主從複製

主庫

[root@server1 ~]# vi /etc/my.cnf #資料庫配置檔案
server-id = 1 #伺服器id，每個伺服器不能相同
log_bin=master-bin #主伺服器日誌檔案
log_slave_updates=true #允許中繼日誌讀取主伺服器的二進位制日誌

[root@server1 ~]# systemctl restart mysqld.service 
[root@server1 ~]# ls /usr/local/mysql/data/ #檢視生成的二進位制日誌檔案
auto.cnf        ib_logfile1        master-bin.index
ib_buffer_pool  ibtmp1             mysql
ibdata1         lcx                performance_schema
ib_logfile0     master-bin.000001 #日誌檔案 sys

[root@server1 ~]# mysql -uroot -p123456

mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by '123456'; #為所有伺服器所有庫授權
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges; #更新許可權
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'myslave'@'20.0.0.%'; 
+--------------------------------------------------------+
| Grants for myslave@20.0.0.%                            |
+--------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'20.0.0.%' |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> mysql> show master status; #檢視主的狀態，記下position的值(同步的位置)
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |     1119 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

從庫（20.0.0.12、20.0.0.13）

[root@server1 ~]# vi /etc/my.cnf #修改從伺服器主配置檔案引數
server-id = 11 #三臺資料庫id不同
relay_log=relay-log-bin #從主伺服器上同步日誌檔案記錄到本地中繼日誌
relay_log_index=slave-relay-bin.index #定義中繼日誌的索引

[root@server1 ~]# systemctl restart mysqld.service 

[root@server1 ~]# mysql -uroot -p #登陸MySQL從伺服器

mysql> change master to master_host='20.0.0.11',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=1119; #新增要同步資料的master伺服器的賬號、二進位制日誌檔案、開始同步的位置（position）
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: 20.0.0.11
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 1119
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes #此項需為yes
            Slave_SQL_Running: Yes #此項需為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: 1119
              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: af0fbd40-464c-11eb-a010-000c29463cae
             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)

讀寫分離（amoeba伺服器）

安裝jdk環境（需要java環境）

[root@amoeba ~]# tar zxvf jdk-8u91-linux-x64.tar.gz

[root@amoeba ~]# mv jdk1.8.0_91/ /usr/local/java

[root@amoeba ~]# vi /etc/profile.d/java.sh #環境檔案最後新增
export JAVA_HOME=/usr/local/java #設定java根目錄
export PATH=$PATH:$JAVA_HOME/bin #在PATH環境變數中新增JAVA根目錄下的bin子目錄

[root@server1 ~]# source /etc/profile.d/java.sh 
[root@server1 ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/java/bin
[root@server1 ~]# java -version 
openjdk version "1.8.0_131"
OpenJDK Runtime Environment (build 1.8.0_131-b12)
OpenJDK 64-Bit Server VM (build 25.131-b12, mixed mode)

下載amoeba

wget https://nchc.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip

部署amoeba

[root@server1 ~]# unzip amoeba-mysql-3.0.5-RC-distribution.zip 

[root@server1 ~]# mv amoeba-mysql-3.0.5-RC/ /usr/local/amoeba
[root@server1 ~]# chmod -R 755 /usr/local/amoeba/

[root@server1 ~]# vim /usr/local/amoeba/jvm.properties 
32	#JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize    =16m -XX:MaxPermSize=96m"
33	JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"

在三臺MySQL上給amoeba授權

mysql> grant all on *.* to 'test'@'20.0.0.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

修改amoeba主配置檔案

[root@amoeba ~]# vi /usr/local/amoeba/conf/amoeba.xml 
28	<property name="user">amoeba</property> #修改root為amoeba
29 
30	<property name="password">123456</property> #新增資料庫登陸密碼

83                 <property name="defaultPool">master</property> #修改multiPool為master
84 
85                 <!-- #註釋，需刪除
86                 <property name="writePool">master</property> #主庫寫許可權
87                 <property name="readPool">slaves</property> #從庫讀許可權
88                 --> #註釋，需刪除

修改dbServers.xml 檔案

[root@server1 ~]# vi /usr/local/amoeba/conf/dbServers.xml
 22                         <!-- mysql schema --> #註釋行
 23                         <property name="schema">mysql</property> #MySQL5.7版本沒有預設的test庫，所以修改為mysql（5.7預設的資料庫）
 24 
 25                         <!-- mysql user --> #註釋行
 26                         <property name="user">test</property>
 27                         <!-- mysql password --> #註釋行
 28                         <property name="password">123456</property>

#配置三臺MySQL伺服器主機名和地址
 43         <dbServer name="master"  parent="abstractServer"> #修改server1為master
 44                 <factoryConfig>
 45                         <!-- mysql ip -->
 46                         <property name="ipAddress">20.0.0.11</property> #修改為主庫master的IP地址
 47                 </factoryConfig>
 48         </dbServer>
 49 
 50         <dbServer name="slave1"  parent="abstractServer"> #修改為slave1
 51                 <factoryConfig>
 52                         <!-- mysql ip -->
 53                         <property name="ipAddress">20.0.0.12</property> #修改為從庫1的IP
 54                 </factoryConfig>

#新增下面slave2模組
             <dbServer name="slave2"  parent="abstractServer"> 
                   <factoryConfig>
                           <!-- mysql ip -->
                            <property name="ipAddress">20.0.0.13</property>
                    </factoryConfig>

 57         <dbServer name="slaves" virtual="true">
 58                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
 59                         <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
 60                         <property name="loadbalance">1</property>
 61 
 62                         <!-- Separated by commas,such as: server1,server2,server1 -->
 63                         <property name="poolNames">slave1,slave2</property> #修改為slave1，slave2
 64                 </poolConfig>
 65         </dbServer>

[root@server1 ~]# /usr/local/amoeba/bin/launcher #啟動anoeba
………………
 2020-12-28 22:50:03 [INFO] Project Name=Amoeba-MySQL, PID=15094 , System shutdown ....
^C
#強行結束

[root@server1 ~]# netstat -anpt | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      15051/java

效果驗證

客戶機

[root@server1 ~]# yum -y install mariadb*

[root@server1 ~]# mysql -uamoeba -p123456 -h 20.0.0.15 -P8066 #登入到amoeba伺服器
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 201047493
Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]>

主庫
新建庫、表

mysql> create database abc;
Query OK, 1 row affected (0.00 sec)

mysql> create table aaa(id int(2), name char(32), age int(3));
Query OK, 0 rows affected (0.01 sec)

從庫1

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                | #可以看到新建的庫
| mysql              |
| performance_schema |
| sys                |
| test2              |
+--------------------+
6 rows in set (0.00 sec)

mysql> use abc
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_abc |
+---------------+
| aaa           | #新建的表
+---------------+
1 row in set (0.00 sec)

從庫2

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

mysql> use abc
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_abc |
+---------------+
| aaa           |
+---------------+
1 row in set (0.00 sec)

從庫1關閉服務

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

主庫新增資料

mysql> insert into aaa values(1,'zhangsan',19);
Query OK, 1 row affected (0.00 sec)

從庫1檢視資料為空

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

從庫2正常複製資料

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

從庫1開啟服務後，自動同步資料

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

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

關閉兩臺從庫服務

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

向從庫中寫入資料

#從庫1
mysql> insert into aaa values(2,'wangwu',20);
Query OK, 1 row affected (0.00 sec)

mysql> select * from aaa;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   19 |
|    2 | wangwu   |   20 |
+------+----------+------+
2 rows in set (0.00 sec)

#從庫2
mysql> insert into aaa values(3,'chenqi',21);
Query OK, 1 row affected (0.00 sec)

mysql> select * from aaa;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   19 |
|    3 | chenqi   |   21 |
+------+----------+------+
2 rows in set (0.00 sec)

客戶端檢視資料庫

MySQL [abc]> select * from aaa;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   19 |
|    2 | wangwu   |   20 |
+------+----------+------+
2 rows in set (0.00 sec)

MySQL [abc]> select * from aaa;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   19 |
|    3 | chenqi   |   21 |
+------+----------+------+
2 rows in set (0.00 sec)

#發現訪問的資料為輪詢機制

在客戶端上寫入資料

MySQL [abc]> insert into aaa values(5,'zhaosi',22);
Query OK, 1 row affected (0.00 sec)

只有主庫會記錄

mysql> select * from aaa;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   19 |
|    5 | zhaosi   |   22 |
+------+----------+------+
2 rows in set (0.00 sec)

開啟同步

mysql> select * from aaa;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   19 |
|    5 | zhaosi   |   22 |
+------+----------+------+
2 rows in set (0.00 sec)

在開啟同步後，主伺服器上的資料會同步到各從伺服器上中，但從伺服器上的自己增加的資料不會同步，只會本地儲存

客戶端訪問依然保持輪詢

MySQL [abc]> select * from aaa;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   19 |
|    3 | chenqi   |   21 |
|    5 | zhaosi   |   22 |
+------+----------+------+
3 rows in set (0.01 sec)

MySQL [abc]> select * from aaa;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   19 |
|    2 | wangwu   |   20 |
|    5 | zhaosi   |   22 |
+------+----------+------+
3 rows in set (0.00 sec)

相關文章