---
title: "Dynamic ProxySQL Query Rules"
description: "Shopify uses ProxySQL to manage application connections to MySQL databases, but applying query rules across thousands of ProxySQL instances is difficult, especially during incidents. ProxySQL rules can reroute, rewrite, or reject regex-matched queries, yet incorrect patterns may cause unintended rejection, rewriting, data corruption, or downtime. To make rule changes safer, Shopify added dry-run support to a ProxySQL fork, using the mysql_query_rules table’s active and log fields; dry-run rules log matched queries without executing their actions. A sidecar polls a global key-value store, synchronizes dynamic rules to each ProxySQL instance, caches rules, supports optional TTLs, and uses exponential backoff for broad logging patterns. The system favors consistency during synchronization failures, while static startup rules cover requirements for full availability."
---

# Dynamic ProxySQL Query Rules

[Shopify](https://yomu.fyi/company/shopify) · 2023-10-18 · Apr 20, 2021

**Type:** Problem & solution

## Summary

Shopify uses ProxySQL to manage application connections to MySQL databases, but applying query rules across thousands of ProxySQL instances is difficult, especially during incidents. ProxySQL rules can reroute, rewrite, or reject regex-matched queries, yet incorrect patterns may cause unintended rejection, rewriting, data corruption, or downtime. To make rule changes safer, Shopify added dry-run support to a ProxySQL fork, using the mysql\_query\_rules table’s active and log fields; dry-run rules log matched queries without executing their actions. A sidecar polls a global key-value store, synchronizes dynamic rules to each ProxySQL instance, caches rules, supports optional TTLs, and uses exponential backoff for broad logging patterns. The system favors consistency during synchronization failures, while static startup rules cover requirements for full availability.

## Context

Shopify needed a safer, scalable way to deploy, modify, and remove ProxySQL query rules across thousands of instances. Query rules are useful during database incidents and experimentation, but incorrect rules can cause excessive rejection or rewriting, data corruption, and downtime. Manual updates across many replicas were time-consuming, and ProxySQL did not provide a dry-run feature.

## Approach / What changed

Shopify added dry-run support to a ProxySQL fork, using the mysql\_query\_rules table’s active and log fields to control whether rules execute or only log matched queries. A web app writes rule changes to a global key-value store, while sidecar containers poll it and synchronize rules to ProxySQL. The sidecars cache rules, support optional TTLs, use exponential log backoff, recover from synchronization failures by rebuilding state, and leave fully available rules static in proxysql.cnf.

## Takeaways

- Dry-run mode logs matched queries and the actions a rule would take without executing those actions, allowing rules to be corrected before activation.
- Sidecar containers synchronize dynamic rules from a global key-value store to ProxySQL instances and handle additions, edits, and deletions.
- When synchronization fails, the system favors consistency by clearing local rule lists and rebuilding them from the key-value store; optional TTLs still remove rules after expiration.

**Tags:** [MySQL](https://yomu.fyi/topic/mysql), [ProxySQL](https://yomu.fyi/topic/proxysql), [Reliability](https://yomu.fyi/topic/reliability)

- Source: [Shopify](https://shopify.engineering/dynamic-proxysql-query-rules)
- Source URL: https://shopify.engineering/dynamic-proxysql-query-rules
- Ingested by Yomu: 2026-08-31T01:09:46.044Z

[Read original post](https://shopify.engineering/dynamic-proxysql-query-rules)