当前位置:网站首页>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):

  1. ProxySQL host : 192.168.2.111
  2. Main library :192.168.2.120
  3. 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 .

原网站

版权声明
本文为[ActionTech]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/203/202207210552183101.html