網(wǎng)上有很多關于pos機讀寫技術,MySQL 讀寫分離的知識，也有很多人為大家解答關于pos機讀寫技術的問題，今天pos機之家(www.dprolou.com)為大家整理了關于這方面的知識，讓我們一起來看下吧!

本文目錄一覽：

1、pos機讀寫技術

pos機讀寫技術

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

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

讀寫分離邏輯圖

讀寫分離是基于MySQL 的主從復制的，所以在實現(xiàn)讀寫分離前，我們先回顧下mySQL主從復制。mysql主從復制首先它是基于二進制日志實現(xiàn)的，這個二進制binlong包含了DDL和DML語句。實現(xiàn)原理是這樣的: 當主庫執(zhí)行了DDL和DML語句，會將數(shù)據(jù)的變更記錄到binlog日志文件里，從庫中的IO線程負責去讀取主庫的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對應的readHost上執(zhí)行，writeHost不負責讀壓力。

配置過程修改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.logSTATUS | wrapper | 2022/04/24 20:57:01 | --> Wrapper Started as DaemonSTATUS | 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.orgINFO | 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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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,'風二娘','女');Query OK, 1 row affected (0.10 sec)mysql> select * from tb_user;+----+-----------+------+| id | username | sex |+----+-----------+------+| 1 | 鳳雛 | 男 || 2 | 落鳳坡 | 男 || 3 | 小喬 | 女 || 4 | 風二娘 | 女 |+----+-----------+------+4 rows in set (0.01 sec)

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

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

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

實現(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)建復制賬號

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

重啟服務

接著配置兩臺從庫管理主庫，S1關聯(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相互復制

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ù)庫服務器的數(shù)據(jù)同步情況。

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

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

是沒有問題的

主從主主復制已實現(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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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é)點上的，我們先把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: 0mysql> select * from tb_user;+----+---------+------+| id | name | sex |+----+---------+------+| 1 | Jack-S1 | 1 | # 查詢結果為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: 0mysql> select * from tb_user;+----+---------+------+| id | name | sex |+----+---------+------+| 1 | Jack-S2 | 1 | # 查詢結果為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負責寫操作，我們配了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: 0mysql> 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，讀寫分離講完啦，你學廢了嗎。

以上就是關于pos機讀寫技術,MySQL 讀寫分離的知識，后面我們會繼續(xù)為大家整理關于pos機讀寫技術的知識，希望能夠幫助到大家！