当前位置:网站首页>Technology sharing | introduction to proxysql binlog reader component (Part 1)
Technology sharing | introduction to proxysql binlog reader component (Part 1)
2022-07-22 00:57:00 【ActionTech】
author : Yang Taotao
Senior database expert , Specialized research MySQL More than ten years . Good at MySQL、PostgreSQL、MongoDB And other open source database related backup recovery 、SQL tuning 、 Monitoring operation and maintenance 、 High availability architecture design, etc . At present, I work in akerson , For major operators and bank financial enterprises MySQL Related technical support 、MySQL Related courses, training, etc .
In this paper, the source : Original contribution
* Produced by aikesheng open source community , Original content is not allowed to be used without authorization , For reprint, please contact the editor and indicate the source .
I wrote an article before :《ProxySQL collocation MySQL HA( Next )》. The article introduces ProxySQL Backend host metadata table mysql_server The meaning of each field , There is a field named gtid_port . This field is ProxySQL Binlog Reader ( Not yet MySQL 8.0 edition ) The port that the component needs to listen on , ProxySQL You need to connect this port to judge the master and slave GTID Whether the transaction number is consistent , Today, let me briefly introduce this component .
You can put ProxySQL Binlog Reader Component as a lightweight MySQL client , Use it to detect in real time MySQL The master-slave replicates the of each slave instance in the architecture GTID Replay the results .ProxySQL Binlog Reader 、ProxySQL 、MySQL The relationship between the three is shown in the figure below :ProxySQL Read ProxySQL Binlog Reader Output GTID To judge MySQL Whether the data of master database and slave database are consistent .
ProxySQL Binlog Reader The background of component birth is as follows :
1. The front-end application requests to enter MySQL Before reading and writing separation .
2. How to ensure the logic of read-write separation ? There are roughly three strategies :
(1). The request is uniformly distributed to the master
(2). Send the transaction block to the master
(3). Read requests are uniformly distributed from
For these three strategies , Especially the last one , It is possible to read out dated data from the Library .MySQL In terms of data transmission principle, master-slave replication , The delay of data playback cannot be avoided from the Library , The various optimization strategies we customized are nothing more than trying to reduce the delay time , Guarantee timeliness . MySQL Since the release GTID After function , For such problems , The solution becomes much easier .
For example, you can read the latest GTID Number 、 Read the latest playback from the Library GTID Number , Compare consistency to calculate the timeliness of delay , Thus, the front-end traffic can be distributed more accurately . This is either analyzed according to the traditional method show slave status Write the corresponding script for the result of 、 Or read variables manually gtid_executed Value to judge the latest GTID Playback number 、 Or it is the opening parameter of each instance session_track_gtids Take the initiative to return the latest to the client GTID Number , The client uses this number to determine whether the transaction has been played back . In this context , Based on efficiency and scalability ,ProxySQL Binlog Reader Component simulation MySQL Slave Library , Pull each in real time MySQL Example of GTID Number ( Just pull GTID Number , Very resource efficient ),ProxySQL The process reads as a client ProxySQL Binlog Reader The latest information obtained by the component GTID Number to determine whether the data of the slave database is consistent with that of the master database , So as to avoid reading obsolete data .
Let me briefly demonstrate how to use this component .
Three examples (MySQL Version is 5.7.34, All ports are 5734):
- ProxySQL host : 192.168.2.111
- Main library :192.168.2.120
- Slave Library ,192.168.2.121
ProxySQL Default management port 6032, Traffic port 6033, Configuration table mysql_servers Field gtid_port Set to 57341.
<mysql:admin:5.5.30>select hostgroup_id, hostname,port,status,gtid_port from mysql_servers;
+--------------+---------------+------+--------+-----------+
| hostgroup_id | hostname | port | status | gtid_port |
+--------------+---------------+------+--------+-----------+
| 1 | 192.168.2.120 | 5734 | ONLINE | 57341 |
| 2 | 192.168.2.121 | 5734 | ONLINE | 57341 |
+--------------+---------------+------+--------+-----------+
2 rows in set (0.00 sec)
In order to distinguish from normal users , Give to alone ProxySQL Binlog reader Component assignment user :
<mysql: ytt:5.7.34-log> create user binlog_reader identified by 'read';
Query OK, 0 rows affected (0.01 sec)
<mysql: ytt:5.7.34-log> grant replication client, replication slave on *.* to binlog_reader;
Query OK, 0 rows affected (0.00 sec)
At every MySQL Start one on each instance ProxySQL Binlog Reader Monitoring process :-l The port specified by the option needs to match ProxySQL Ports set in the system table , That is to say mysql_servers Tabular gtid_port field value .
# Lord :
[email protected]:/tmp# proxysql_binlog_reader -ubinlog_reader -pread -P5734 -h 192.168.2.120 -l57341 -L /tmp/proxysql_mysqlbinlog_reader.log
# from :
[email protected]:/tmp# proxysql_binlog_reader -ubinlog_reader -pread -P5734 -h 192.168.2.121 -l57341 -L /tmp/proxysql_mysqlbinlog_reader.log
Check the main database log for errors : Start up normal , Read start GTID Number , Read continuously after binlog .
[email protected]:/tmp# tail -f proxysql_mysqlbinlog_reader.log
Starting ProxySQL MySQL Binlog
Sucessfully started
Angel process started ProxySQL MySQL Binlog process 28841
2022-07-18 15:11:04 [INFO] proxysql_binlog_reader version 2.0-3-gd8e0140
2022-07-18 15:11:04 [INFO] Initializing client...
2022-07-18 15:11:04 [INFO] Last executed GTID: '00005734-0000-0000-0000-000000005734:1-2'
2022-07-18 15:11:04 [INFO] Reading binlogs...
The slave library is also started normally :
[email protected]:/tmp# tail -f proxysql_mysqlbinlog_reader.log
Starting ProxySQL MySQL Binlog
Sucessfully started
Angel process started ProxySQL MySQL Binlog process 22710
2022-07-18 15:11:59 [INFO] proxysql_binlog_reader version 2.0-3-gd8e0140
2022-07-18 15:11:59 [INFO] Initializing client...
2022-07-18 15:11:59 [INFO] Last executed GTID: '00005734-0000-0000-0000-000000005734:1-2'
2022-07-18 15:11:59 [INFO] Reading binlogs...
If the data transmission between master and slave is abnormal , stay ProxySQL Binlog Reader There will also be corresponding information output in the log of the component .
see MySQL Conversation list , There are two more master libraries and two more slave libraries DUMP Thread transfers data to ProxySQL Binlog Reader Components :
# Lord :
Id: 125
User: binlog_reader
Host: 192.168.2.120:54668
db: NULL
Command: Binlog Dump GTID
Time: 314
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
# from :
Id: 1426
User: binlog_reader
Host: 192.168.2.121:54992
db: NULL
Command: Binlog Dump GTID
Time: 165
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
Here I briefly introduce ProxySQL Binlog Reader The background of the component 、 Usage, etc , Please pay attention to the next article for more details .
边栏推荐
- Comprehensive experiment of mGRE and OSPF
- Data visualization Chapter 5
- Script loads data into JSON file
- ROS multi coordinate transformation
- Dataframe counts the number of repetitions
- Read and understand | how data center supports enterprise digital operation
- 面试官:你确定 Redis 是单线程的进程吗?
- Oracle VM VirtualBox NAT network Ping failed
- 一个程序员的水平能差到什么程度?
- 数字时代下,企业运营管理的“数智”变革
猜你喜欢
随机推荐
HCIA NAT experiment report 7.14
从装配式建筑流行看云原生技术中台价值 (二)
策略中心——企业建设数智运营平台的大脑
用j s编写红绿灯
[AR Foundation] AR Foundation基礎
元数据驱动下的业务创新,构建企业竞争新优势
牛客刷题——剑指offer
技术分享 | ProxySQL Binlog Reader 组件介绍(上篇)
三相差分编码器转成脉冲信号或集电极开路转换模块
mysql dense_ Rank(), rank() function
Oracle VM VirtualBox NAT network Ping failed
解决Visio封闭图案不能填充问题
整数二分、浮点二分以及STL中的二分函数(lower_bound(),upper_bound())
In the digital era, the "digital intelligence" transformation of enterprise operation and management
Successfully install pyinstaller (solve the problem of PIP install pyinstaller installation failure)
剑指offer_知识迁移能力
06.Octave的介绍、安装与简单使用
【HCIP持续更新】DHCP安全威胁
Win10 display auto repair fails to boot normally
国际会计准则IAS 和 IFRS