一、讀寫分離概述

讀寫分離，簡單來說是把對數(shù)據(jù)庫的讀和寫操作分開，當應(yīng)用程序發(fā)起讀操作時分配給讀庫，當應(yīng)用程序發(fā)起寫操作時分配給寫庫，從而達到減輕單臺數(shù)據(jù)庫服務(wù)器的壓力。

實現(xiàn)讀寫分離的工具使用mycat就可以，當然還有其它的，mycat不僅可以支持MySQL，也支持oracle和SQL server。

讀寫分離邏輯圖

讀寫分離是基于MySQL 的主從復(fù)制的，所以在實現(xiàn)讀寫分離前，我們先回顧下mysql主從復(fù)制。mysql主從復(fù)制首先它是基于二進制日志實現(xiàn)的，這個二進制binlong包含了DDL和DML語句。實現(xiàn)原理是這樣的: 當主庫執(zhí)行了DDL和DML語句，會將數(shù)據(jù)的變更記錄到binlog日志文件里，從庫中的IO線程負責(zé)去讀取主庫的binlog，然后記錄到自己的中繼日志relay log中，從庫的SQL線程讀取中繼日志relay log 重新執(zhí)行到從庫中。

一主一從環(huán)境環(huán)境準備，如圖示:

二、配置一主一從讀寫分離

通過mycat來控制后臺數(shù)據(jù)庫的讀寫分離和負載均衡，由schema.xml文件中的datahost標簽里的balance屬性來控制。

balance屬性值含義:

0: 表示不開啟讀寫分離機制，所有讀操作都發(fā)送到當前可用的writeHost 上。

1: 表示全都的readHost與備用的writeHost都參與select語句的負載均衡(針對雙主環(huán)境)

2: 所有的讀寫操作都隨機在writeHost、readHost上分發(fā)。

3: 所有的讀請求隨機分發(fā)到writeHost對應(yīng)的readHost上執(zhí)行，writeHost不負責(zé)讀壓力。

配置過程修改schema.xml 添加邏輯庫及讀寫分離dataHost標簽，

schme.xml

然后修改server.xml 運行用戶訪問讀寫分離邏輯庫DB_TBSHARE_RW.

server.xml

ok,配置好后重啟mycat進行測試。

[root@db-master /usr/local/mycat/conf]# ../bin/mycat start

[root@db-master /usr/local/mycat/conf]# cat ../logs/wrApper.log
STATUS | wrapper | 2022/04/24 20:57:01 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/04/24 20:57:02 | Launching a JVM...
INFO | jvm 1 | 2022/04/24 20:57:03 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/04/24 20:57:03 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/04/24 20:57:03 |
INFO | jvm 1 | 2022/04/24 20:57:10 | MyCAT Server startup successfully. see logs in logs/mycat.log

[root@db-master /usr/local/mycat/conf]# mysql -uroot -h192.168.128.100 -p123456 -P8066
。。。
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show databases;
+---------------+
| DATABASE      |
+---------------+
| DB_TBSHARE    |
| DB_TBSHARE_RW |
| SHOPING       |
+---------------+
3 rows in set (0.02 sec)

mysql> use DB_TBSHARE_RW;
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_db_tbshare |
+----------------------+
| tb_user              |
+----------------------+
1 row in set (0.02 sec)

mysql> 

插入4條數(shù)據(jù)，然后select，此時我們分辨不出查詢是主庫的還是從庫的，懵逼了

mysql> insert into tb_user values(1,'鳳雛','男');
Query OK, 1 row affected (0.02 sec)
mysql> insert into tb_user values(2,'落鳳坡','男');
Query OK, 1 row affected (0.02 sec)
mysql> insert into tb_user values(3,'小喬','女');
Query OK, 1 row affected (0.02 sec)
mysql> insert into tb_user values(4,'風(fēng)二娘','女');
Query OK, 1 row affected (0.10 sec)

mysql> select * from tb_user;
+----+-----------+------+
| id | username | sex |
+----+-----------+------+
| 1 | 鳳雛 | 男 |
| 2 | 落鳳坡 | 男 |
| 3 | 小喬 | 女 |
| 4 | 風(fēng)二娘 | 女 |
+----+-----------+------+
4 rows in set (0.01 sec)

辦法來了，我們悄悄的把從庫的小喬改成大橋試試, 因為從庫的更新不可能同步到主庫對吧，條友們，然后才select驗證，如下圖示，讀寫分離正常。

由于這一主一從存在單點故障，當主庫掛了，通過mycat查詢是沒問題的，但是執(zhí)行DML和DDL會提示連接斷開，導(dǎo)致大家熬夜加班。

三、主主復(fù)制，雙主雙從架構(gòu)實現(xiàn)讀寫分離

主節(jié)點master1用于處理所有的寫請求，它的從節(jié)點slave1和另外一臺主節(jié)點master2及slave2從節(jié)點負責(zé)所有的讀請求。當master1主機節(jié)點宕機后，master2主節(jié)點負責(zé)寫請求，它倆互為備機，如圖示:

實現(xiàn)過程

1、搭建雙主

修改M1的 my.cnf 添加主要參數(shù)

server_id=33061

binlog-do-db=db01

binlog-do-db=db02

binlog-do-db=db03

log-slave-updates=1 #強制刷新從庫二進制日志，如果有更新的話

M2的my.cnf文件添加

server_id=33062

binlog-do-db=db01

binlog-do-db=db02

binlog-do-db=db03

log-slave-updates=1

然后在兩個主庫中創(chuàng)建復(fù)制賬號

grant replication slave on *.* to repl@'192.168.128.%' identified by '123456';

flush privileges;

show master status;

從庫配置，修改server_id 即可

server_id=33071 # S1

server_id=33072 # S2

重啟服務(wù)

接著配置兩臺從庫管理主庫，S1關(guān)聯(lián)M1

change master to master_host='192.168.128.100',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;

start slave && show slave status;

S2管理M2

change master to master_host='192.168.128.101',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;

start slave; && show slave status; # 如果有故障就reset slave all

主庫M1和M2相互復(fù)制

M1: change master to master_host='192.168.128.101',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;

start slave && show slave status;

M2: change master to master_host='192.168.128.100',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;

start slave && show slave status;

測試，分別在兩臺M1、M2上執(zhí)行DDL、DML語句，查看涉及到的數(shù)據(jù)庫服務(wù)器的數(shù)據(jù)同步情況。

M1 上創(chuàng)建db01, 如圖db01全部同步完成。

在M2上創(chuàng)建表并插入數(shù)據(jù)觀察同步情況

是沒有問題的

主從主主復(fù)制已實現(xiàn)，接下來實現(xiàn)雙主雙從讀寫分離。

Mycat控制后臺數(shù)據(jù)庫的讀寫分離和負載均衡是由schema.xml文件中的datahost標簽里的balance屬性控制的，通過writeType及switchType來完成失敗自動切換。

schema.xml 配置

<schema name="DB_TBSHARE_RW2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7">
                <!--此處可以不用配邏輯表-->
</schema>

<dataNode name="dn7" dataHost="dbhost7" database="db01" />

<dataHost name="dbhost7" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!--M1 S1-->
<writeHost host="master1" url="192.168.128.100:3306" user="root" password="123456">
<readHost host="slave1" url="192.168.128.101:3307" user="root" password="123456" />
</writeHost>
<!--M2 S2-->
<writeHost host="master2" url="192.168.128.101:3306" user="root" password="123456">
<readHost host="slave2" url="192.168.128.101:3307" user="root" password="123456" />
</writeHost>
</dataHost>

balance="1", 表示全部的readHost與備用的writeHost參與select語句的負載均衡，換句話說，就是當雙主雙從模式M1->S1,M2->S2互為主備，正常情況下，M2、S1、S2都參與select語句的負載均衡。

writeType: 0 寫操作都轉(zhuǎn)發(fā)到第一臺writehost、writehost1宕機會切換到writehost2上

1 所有的寫操作都隨機發(fā)送到配置的writehost上。

switchType: -1 不自動切換 ； 1 自動切換

配置好后，重啟mycat。 ./bin/mycat restart

[root@db-master ~]# mysql -uroot -h192.168.128.100 -p123456 -P8066
.......
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+----------------+
| DATABASE |
+----------------+
| DB_TBSHARE |
| DB_TBSHARE_RW2 |
| SHOPING |
+----------------+
3 rows in set (0.02 sec)

mysql> use DB_TBSHARE_RW2;
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_db01 |
+----------------+
| tb_user |
+----------------+
1 row in set (0.02 sec)
mysql>

  mysql> select * from tb_user;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | Jack | 1    |
|  2 | Tony | 1    |
|  3 | mack | 2    |
|  4 | Lucy | 2    |
|  5 | Mely | 2    |
+----+------+------+
5 rows in set (1.86 sec)

為了區(qū)分查詢結(jié)果到底是哪臺節(jié)點上的，我們先把S1 和S2 上的數(shù)據(jù)進行標識。

mysql> update tb_user set name='Jack-S1' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tb_user;
+----+---------+------+
| id | name | sex |
+----+---------+------+
| 1 | Jack-S1 | 1 |                     # 查詢結(jié)果為Jack-S1 |說明來自S1
。。。。

mysql> update tb_user set name='Jack-S2' where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb_user;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | Jack-S2 | 1    |                   # 查詢結(jié)果為Jack-S2 |說明來自S2
。。。。。

我們切換會mycat進行查詢

mysql> select * from tb_user;
+----+---------+------+
| id | name | sex |
+----+---------+------+
| 1 | Jack-S1 | 1 |                               # 從節(jié)點 S1
| 2 | Tony | 1 |
| 3 | Mack | 2 |
| 4 | Lucy | 2 |
| 5 | Mely | 2 |
+----+---------+------+
5 rows in set (0.00 sec)

mysql> select * from tb_user;
+----+---------+------+
| id | name | sex |
+----+---------+------+
| 1 | Jack-S1 | 1 |                               # 從節(jié)點 S1
| 2 | Tony | 1 |
| 3 | Mack | 2 |
| 4 | Lucy | 2 |
| 5 | Mely | 2 |
+----+---------+------+
5 rows in set (0.01 sec)

mysql> select * from tb_user;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | Jack | 1 |
| 2 | Tony | 1 |                   # 主節(jié)點M2，因為M1負責(zé)寫操作，我們配了balance="1"
| 3 | Mack | 2 |
| 4 | Lucy | 2 |
| 5 | Mely | 2 |
+----+------+------+
5 rows in set (0.03 sec)

mysql> select * from tb_user;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | Jack | 1 |
| 2 | Tony | 1 |
| 3 | Mack | 2 |
| 4 | Lucy | 2 |
| 5 | Mely | 2 |
+----+------+------+
5 rows in set (0.00 sec)

插入一條數(shù)據(jù) 看看4個節(jié)點同步情況

mysql> insert into tb_user values(6,'Baky','2');

Query OK, 1 row affected (0.33 sec)

mysql> update tb_user set name='SZ-馬' where id=2;

update 更新也是沒有問題的。

Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tb_user;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 1 | Jack | 1 |
| 2 | SZ-馬 | 1 |
| 3 | Mack | 2 |
| 4 | Lucy | 2 |
| 5 | Mely | 2 |
| 6 | Baky | 2 |
+----+--------+------+
6 rows in set (0.01 sec)
mysql> select * from tb_user;
+----+---------+------+
| id | name | sex |
+----+---------+------+
| 1 | Jack-S1 | 1 |
| 2 | SZ-馬 | 1 |
| 3 | Mack | 2 |
| 4 | Lucy | 2 |
| 5 | Mely | 2 |
| 6 | Baky | 2 |
+----+---------+------+
6 rows in set (0.02 sec)
mysql> select * from tb_user;
+----+---------+------+
| id | name | sex |
+----+---------+------+
| 1 | Jack-S1 | 1 |
| 2 | SZ-馬 | 1 |
| 3 | Mack | 2 |
| 4 | Lucy | 2 |
| 5 | Mely | 2 |
| 6 | Baky | 2 |
+----+---------+------+
6 rows in set (0.25 sec)

模擬M1節(jié)點宕機，看看會不會切換M2及能否執(zhí)行寫入操作

systemctl stop mysqld

mysql> select * from tb_user;
+----+---------+------+
| id | name | sex |
+----+---------+------+
| 1 | Jack-S1 | 1 |
| 2 | SZ-馬 | 1 |
| 3 | Mack | 2 |
| 4 | Lucy | 2 |
| 5 | Mely | 2 |
| 6 | Baky | 2 |
+----+---------+------+
6 rows in set (0.01 sec)

mysql> select * from tb_user;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | Jack-S2 | 1    |
|  2 | SZ-馬   | 1    |
|  3 | Mack    | 2    |
|  4 | Lucy    | 2    |
|  5 | Mely    | 2    |
|  6 | Baky    | 2    |
  
  mysql> insert into tb_user values(7,'www','1');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_user;
+----+--------+------+
| id | name   | sex  |
+----+--------+------+
|  1 | Jack   | 1    |
|  2 | SZ-馬  | 1    |
|  3 | Mack   | 2    |                # M2
|  4 | Lucy   | 2    |
|  5 | Mely   | 2    |
|  6 | Baky   | 2    |
|  7 | www    | 1    |
+----+--------+------+
7 rows in set (0.00 sec)

mysql> select * from tb_user;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | Jack-S2 | 1    |
|  2 | SZ-馬   | 1    |
|  3 | Mack    | 2    |
|  4 | Lucy    | 2    |
|  5 | Mely    | 2    |
|  6 | Baky    | 2    |
|  7 | www     | 1    |
+----+---------+------+
7 rows in set (0.00 sec)

mysql> select * from tb_user;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | Jack-S1 | 1    |
|  2 | Tony    | 1    |
|  3 | Mack    | 2    |
|  4 | Lucy    | 2    |
|  5 | Mely    | 2    |
|  6 | Baky    | 2    |
+----+---------+------+
6 rows in set (0.00 sec

OK，讀寫分離講完啦，你學(xué)廢了嗎。