DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Orchestrator and ProxySQL

Learn how can you use Orchestrator and ProxySQL together and a few tips for your configuration and failover prep.

Tibor Korocz user avatar by
Tibor Korocz
·
Nov. 11, 16 · Tutorial
Like (1)
Save
Tweet
Share
4.04K Views

Join the DZone community and get the full member experience.

Join For Free

In this blog post, I am going to show you how can you use Orchestrator and ProxySQL together.

In my previous blog post, I showed how to use bash scripts and move virtual IPs with Orchestrator. As in that post, I assume you already have Orchestrator working. If not, you can find the installation steps here.

In the case of a failover, Orchestrator changes the MySQL topology and promotes a new master. But who lets the application know about this change? This is where ProxySQL helps us.

ProxySQL

You can find the ProxySQL install steps here. In our test, we use the following topology:

screen-shot-2016-11-01-at-14-27-09

For this topology we need the next rules in “ProxySQL”:

INSERT INTO mysql_servers(hostname,hostgroup_id,port,weight,max_replication_lag)VALUES('192.168.56.107',601,3306,1000,10);

INSERT INTO mysql_servers(hostname,hostgroup_id,port,weight,max_replication_lag)VALUES('192.168.56.106',601,3306,1000,10);

INSERT INTO mysql_servers(hostname,hostgroup_id,port,weight,max_replication_lag)VALUES('192.168.56.105',601,3306,1000,0);

INSERT INTO mysql_servers(hostname,hostgroup_id,port,weight,max_replication_lag)VALUES('192.168.56.105',600,3306,1000,0);

INSERT INTO mysql_replication_hostgroups VALUES(600,601,'');

LOAD MYSQL SERVERS TORUNTIME;SAVE MYSQL SERVERS TODISK;

insert into mysql_query_rules(username,destination_hostgroup,active)values('testuser_w',600,1);

insert into mysql_query_rules(username,destination_hostgroup,active)values('testuser_r',601,1);

insert into mysql_query_rules(username,destination_hostgroup,active,retries,match_digest)values('testuser_rw',601,1,3,'^SELECT');

LOAD MYSQL QUERY RULES TORUNTIME;SAVE MYSQL QUERY RULES TODISK;

insert into mysql_users(username,password,active,default_hostgroup,default_schema,transaction_persistent)values('testuser_w','Testpass1.',1,600,'test',1);

insert into mysql_users(username,password,active,default_hostgroup,default_schema,transaction_persistent)values('testuser_r','Testpass1.',1,601,'test',1);

insert into mysql_users(username,password,active,default_hostgroup,default_schema,transaction_persistent)values('testuser_rw','Testpass1.',1,600,'test',1);

LOAD MYSQL USERS TORUNTIME;SAVE MYSQL USERS TODISK;


See the connection pool:
mysql>select*fromstats_mysql_connection_poolwherehostgroupbetween600and601order byhostgroup,srv_hostdesc;

+-----------+----------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+

|hostgroup|srv_host|srv_port|status|ConnUsed|ConnFree|ConnOK|ConnERR|Queries|Bytes_data_sent|Bytes_data_recv|Latency_ms|

+-----------+----------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+

|600|192.168.56.105|3306|ONLINE|4|0|4|0|2833|224351|0|3242|

|601|192.168.56.107|3306|ONLINE|1|1|11|0|275443|11785750|766914785|431|

|601|192.168.56.106|3306|ONLINE|1|1|10|0|262509|11182777|712120599|1343|

|601|192.168.56.105|3306|ONLINE|1|1|2|0|40598|1733059|111830195|3242|

+-----------+----------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+

4rowsinset(0.00sec)

It shows us “192.168.57.105” is in “hostgroup” 600, which means that server is the master.

Choosing the New Master

ProxySQL does not know what the topology looks like, which is really important. ProxySQL is monitoring the “read_only” variables on the MySQL servers, and the server where read_only=off is going to get the writes. If the old master went down and we changed our topology, we have to change the read_only variables on the new master. Of course, applications like MHA or Orchestrator can do that for us.

We have two possibilities here: the master went down, or we want to promote a new master.

Master Is Down

If the master goes down, Orchestrator is going to change the topology and set the read_only=OFF on the promoted master. ProxySQL is going to realize the master went down and send the write traffic to the server where read_only=OFF.

Let’s do a test. After we stopped MySQL on “192.168.56.105”, Orchestrator promoted “192.168.56.106” as the new master. ProxySQL is using it now as a master:

mysql>select*fromstats_mysql_connection_poolwherehostgroupbetween600and601order byhostgroup,srv_hostdesc;

+-----------+----------------+----------+---------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+

|hostgroup|srv_host|srv_port|status|ConnUsed|ConnFree|ConnOK|ConnERR|Queries|Bytes_data_sent|Bytes_data_recv|Latency_ms|

+-----------+----------------+----------+---------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+

|600|192.168.56.106|3306|ONLINE|0|0|0|0|0|0|0|790|

|601|192.168.56.107|3306|ONLINE|0|0|13|0|277953|11894400|774312665|445|

|601|192.168.56.106|3306|ONLINE|0|0|10|0|265056|11290802|718935768|790|

|601|192.168.56.105|3306|SHUNNED|0|0|2|0|42961|1833016|117959313|355|

+-----------+----------------+----------+---------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+

4rowsinset(0.00sec)


This happens quickly and does not require any application, VIP or DNS modification.

Promoting a New Master

When we perform a graceful-master-takeover with Orchestrator, it promotes a slave as a new master, removes the old master from the replicaset and sets read_only=ON.

From Orchestrator’s point of view, this is great. It promoted a slave as a new master, and old master is not part of the replicaset anymore. But as I mentioned earlier, ProxySQL does not know what the replicaset looks like.

It only knows we changed the read_only variables on some servers. It is going to send reads to the old master, but it does not have up-to-date data anymore. This is not good at all.

We have two options to avoid this.

Remove a Master From a Read Hostgroup

If the master is not part of the read hostgroup, ProxySQL won’t send any traffic there after we promote a new master. But in this case, if we lose the slaves, ProxySQL cannot redirect the reads to the master. If we have a lot of slaves, and the replication stopped on the saves because of an error or mistake, the master probably won’t be able to handle all the read traffic. But if we only have a few slaves, it would be good if the master can also handle reads if there is an issue on the slaves.

Using a Scheduler

In this great blog post from Marco Tusa, we can see that ProxySQL can use “Schedulers”. We can use the same idea here as well. I wrote a script based on Marco’s that can recognize if the old master is no longer a part of the replicaset.

The script checks the followings:

  • read_only=ON – the server is read-only (on the slave servers, this has to be ON)
  • repl_lag  is NULL – on the master, this should be NULL (if the seconds_behind_master is not defined, ProxySQL will report repl_lag is NULL)

If the read_only=ON, it means the server is not the master at the moment. But if the repl_lag is NULL, it means the server is not replicating from anywhere, and it probably was a master. It has to be removed from the Hostgroup.

Adding a Scheduler

INSERTINTOscheduler(id,interval_ms,filename,arg1)values(10,2000,"/var/lib/proxysql/server_monitor.pl","-u=admin -p=admin -h=127.0.0.1 -G=601 -P=6032 --debug=0  --log=/var/lib/proxysql/server_check");

LOADSCHEDULERTORUNTIME;SAVESCHEDULERTODISK;

The script has parameters like username, password or port. But we also have to define the read Hostgroup (-G).

Let’s see what happens with ProsySQL after we run the command orchestrator-cgraceful-master-takeover-irep1-drep2 :

mysql>select*from stats_mysql_connection_pool where hostgroup between600and601order by hostgroup,srv_host desc;

+-----------+----------------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+

|hostgroup|srv_host|srv_port|status|ConnUsed|ConnFree|ConnOK|ConnERR|Queries|Bytes_data_sent|Bytes_data_recv|Latency_ms|

+-----------+----------------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+

|600|192.168.56.106|3306|ONLINE|0|0|0|0|0|0|0|504|

|601|192.168.56.107|3306|ONLINE|0|2|2|0|6784|238075|2175559|454|

|601|192.168.56.106|3306|ONLINE|0|0|2|0|6761|237409|2147005|504|

|601|192.168.56.105|3306|OFFLINE_HARD|0|0|2|0|6170|216001|0|435|

+-----------+----------------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+

4rows inset(0.00sec)


As we can see, the status changed to OFFLINE_HARD:
mysql>select*from mysql_servers;

+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

|hostgroup_id|hostname|port|status|weight|compression|max_connections|max_replication_lag|use_ssl|max_latency_ms|comment|

+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

|601|192.168.56.107|3306|ONLINE|1000|0|1000|10|0|0||

|601|192.168.56.106|3306|ONLINE|1000|0|1000|10|0|0||

|9601|192.168.56.105|3306|ONLINE|1000|0|1000|0|0|0||

|600|192.168.56.106|3306|ONLINE|1000|0|1000|10|0|0||

+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

4rows inset(0.00sec)


This is because we changed the “hostgroup_id” to 9601. This is what we wanted so that the old master won’t get more traffic.

Conclusion

Because ProxySQL redirects the traffic based on the read_only  variables, it is important to start the servers with read_only=ON (even on the master). In that case, we can avoid getting writes on many servers at the same time.

If we want to use graceful-master-takeover with Orchestrator, we have to use a scheduler that can remove the old master from the read Hostgroup.

master

Published at DZone with permission of Tibor Korocz, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Upgrade Guide To Spring Data Elasticsearch 5.0
  • How Observability Is Redefining Developer Roles
  • Why It Is Important To Have an Ownership as a DevOps Engineer
  • Mind Map Reuse in Software Groups

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: