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

本文目錄一覽：

1、pos機(jī)讀寫技術(shù)

pos機(jī)讀寫技術(shù)

一、讀寫分離概述

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

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

讀寫分離邏輯圖

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

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

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

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

balance屬性值含義:

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

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

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

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

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

schme.xml

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

server.xml

ok,配置好后重啟mycat進(jìn)行測試。

[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，此時(shí)我們分辨不出查詢是主庫的還是從庫的，懵逼了[捂臉]

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)

辦法來了，我們悄悄的把從庫的小喬改成大橋試試, 因?yàn)閺膸斓母虏豢赡芡降街鲙鞂?duì)吧，條友們，然后才select驗(yàn)證，如下圖示，讀寫分離正常。

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

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

主節(jié)點(diǎn)master1用于處理所有的寫請求，它的從節(jié)點(diǎn)slave1和另外一臺(tái)主節(jié)點(diǎn)master2及slave2從節(jié)點(diǎn)負(fù)責(zé)所有的讀請求。當(dāng)master1主機(jī)節(jié)點(diǎn)宕機(jī)后，master2主節(jié)點(diǎn)負(fù)責(zé)寫請求，它倆互為備機(jī)，如圖示:

實(shí)現(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 #強(qiáng)制刷新從庫二進(jìn)制日志，如果有更新的話

M2的my.cnf文件添加

server_id=33062

binlog-do-db=db01

binlog-do-db=db02

binlog-do-db=db03

log-slave-updates=1

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

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ù)

接著配置兩臺(tái)從庫管理主庫，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;

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

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

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

是沒有問題的

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

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

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語句的負(fù)載均衡，換句話說，就是當(dāng)雙主雙從模式M1->S1,M2->S2互為主備，正常情況下，M2、S1、S2都參與select語句的負(fù)載均衡。

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

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

switchType: -1 不自動(dòng)切換 ； 1 自動(dòng)切換

配置好后，重啟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é)果到底是哪臺(tái)節(jié)點(diǎn)上的，我們先把S1 和S2 上的數(shù)據(jù)進(jìn)行標(biāo)識(shí)。

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

我們切換會(huì)mycat進(jìn)行查詢

mysql> select * from tb_user;+----+---------+------+| id | name | sex |+----+---------+------+| 1 | Jack-S1 | 1 | # 從節(jié)點(diǎn) 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é)點(diǎn) 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é)點(diǎn)M2，因?yàn)镸1負(fù)責(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個(gè)節(jié)點(diǎn)同步情況

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é)點(diǎn)宕機(jī)，看看會(huì)不會(huì)切換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é)廢了嗎。

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