{"id":1474,"date":"2021-07-06T08:03:24","date_gmt":"2021-07-06T06:03:24","guid":{"rendered":"https:\/\/www.netexpertise.eu\/en\/?p=1474"},"modified":"2021-09-21T08:34:44","modified_gmt":"2021-09-21T06:34:44","slug":"proxysql-helm-chart-load-rules-from-sql-query-set","status":"publish","type":"post","link":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/proxysql-helm-chart-load-rules-from-sql-query-set.html","title":{"rendered":"ProxySQL Helm Chart ⎈ Load Rules from SQL Query Set"},"content":{"rendered":"\n

ProxySQL<\/a> is a powerful tool that relays traffic to multiple Mysql backends. Configuration can be set in proxysql.cnf that is loaded when the daemon starts. This is really nice from a Docker perspective. You change the file and redeploy with the new settings. Now, I need a ProxySQL Helm chart to deploy on my Kubernetes cluster.<\/p>\n\n\n\n


Official ProxySQL Helm Chart<\/h2>\n\n\n\n

I tried to use the ProxySQL team helm chart but quickly ran into some issues.
The docker hub image is a bit outdated. The Helm chart does not seem to be maintained anymore while they had done a great job.

The configuration lies in proxysql.cnf. It’d be nice the file content would load from Values.yaml, making it dynamic for each deployment.
My setup uses mainly mysql_query_rules_fast_routing<\/strong> but here’s the problem: it cannot be configured through proxysql.cnf and ProxySQL developers made it clear they will not<\/a> change this.<\/p>\n\n\n\n


Docker Image with Mysql Client<\/h2>\n\n\n\n

ProxySQL docker<\/a> comes without Mysql client probably to make the image size smaller. You will need it though to inject SQL queries locally when the pod starts up. The dockerfile is really easy but it forces you to maintain your own image.<\/p>\n\n\n\n

FROM proxysql\/proxysql:2.2.0\n\nRUN apt update && apt -y install mysql-client && apt clean all\n\nENTRYPOINT [\"proxysql\", \"-f\", \"-D\", \"\/var\/lib\/proxysql\"]<\/code><\/pre>\n\n\n\n
I made an image directly available on Dockerhub<\/a>. It is referenced in the helm chart but you can build your own if you want to host it on a private repository.<\/p>\n\n\n\n

mysql_query_rules_fast_routing Rules<\/h2>\n\n\n\n
I decided to make some changes on the Helm template so it can read SQL rules from the Value file, and load them into ProxySQL dynamically once the pod is up and running.

<\/p>\n\n\n\n
sqlqueries: |\n   --\n   SET mysql-have_ssl='true';\n   LOAD MYSQL VARIABLES TO RUNTIME;\n   SET admin-web_enabled='true';\n   LOAD ADMIN VARIABLES TO RUNTIME;\n   --\n   INSERT INTO mysql_servers (hostgroup_id,hostname) VALUES (0,'db1.domain.lan');\n   INSERT INTO mysql_servers (hostgroup_id,hostname) VALUES (1,'db2.domain.lan');\n   --\n   INSERT INTO mysql_users (username,password,use_ssl,default_hostgroup) VALUES ('proxy','*9EF51D21B4A3E7BC7A58925308F229CF4AEEC9E1',1,0);\n   --\n   INSERT INTO mysql_query_rules_fast_routing (username,schemaname,destination_hostgroup, comment) VALUES ('proxy','schema1',0,'');\n   INSERT INTO mysql_query_rules_fast_routing (username,schemaname,destination_hostgroup, comment) VALUES ('proxy','schema2',1,'');\n   --\n   LOAD MYSQL USERS TO RUNTIME;\n   LOAD MYSQL SERVERS TO RUNTIME;\n   LOAD MYSQL QUERY RULES TO RUNTIME;<\/code><\/pre>\n\n\n\n

SQL queries are added to the Values.yaml file, loaded into the configmap and mounted as a file in \/docker-entrypoint-initdb.d\/sqlqueries. We can then inject SQL queries once ProxySQL daemon is up and running. It is achieved with a poststart command that is launched right after the docker entrypoint’s been executed.

<\/p>\n\n\n\n
lifecycle:\n   postStart:\n     exec:\n       command: [\"\/bin\/sh\", \"-c\", \"sleep 1 && mysql --show-warnings -uadmin -padmin -h127.0.0.1 -P{{ .Values.pod.adminPort }} < \/docker-entrypoint-initdb.d\/sqlqueries\"]<\/code><\/pre>\n\n\n\n

Using the Helm Chart<\/h2>\n\n\n\n
The helm chart is available on github<\/a>. Clone the repo, edit your own Values file based on example.yaml and deploy the Helm chart<\/p>\n\n\n\n
helm install -n my-release -f myconf.yaml .\/proxysql<\/code><\/pre>\n\n\n\n

Read this post<\/a> if you want to enable and reach the stats server through Kubernetes ingress rules.<\/p>\n\n\n\n

Bring your Own Self Signed Cert<\/h2>\n\n\n\n
Proxysql 2.3.0 is now capable of reloading the SSL\/TLS certificate on the fly.
Before that, you’d have to build your own proxysql container to embed the CA and Mysql certificate, making it static.
With this new feature, you just have to create a Kubernetes TLS secret with a ca.crt, tls.cert and key.crt that will be mounted by the ProxySQL Helm chart<\/a>.

Uncomment the matching set of commands that will replace ProxySQL auto-generated certs with the Mysql SYSTEM instruction.<\/p>\n","protected":false},"excerpt":{"rendered":"
ProxySQL is a powerful tool that relays traffic to multiple Mysql backends. Configuration can be set in proxysql.cnf that is loaded when the daemon starts. This is really nice from a Docker perspective. You change the file and redeploy with the new settings. Now, I need a ProxySQL Helm chart to deploy on my Kubernetes […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[423,424,4],"tags":[447,443,385,446],"yoast_head":"\nNetexpertise - ProxySQL Helm Chart ⎈ Load Rules from SQL Query Set<\/title>\n<meta name=\"description\" content=\"ProxySQL Helm Chart that loads configuration from SQL Query Set. Define rules in Values file and deploy on Kubernetes\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/proxysql-helm-chart-load-rules-from-sql-query-set.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Netexpertise - ProxySQL Helm Chart ⎈ Load Rules from SQL Query Set\" \/>\n<meta property=\"og:description\" content=\"ProxySQL Helm Chart that loads configuration from SQL Query Set. Define rules in Values file and deploy on Kubernetes\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/proxysql-helm-chart-load-rules-from-sql-query-set.html\" \/>\n<meta property=\"og:site_name\" content=\"Netexpertise\" \/>\n<meta property=\"article:published_time\" content=\"2021-07-06T06:03:24+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-09-21T06:34:44+00:00\" \/>\n<meta name=\"author\" content=\"dave\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@netexpertise\" \/>\n<meta name=\"twitter:site\" content=\"@netexpertise\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/proxysql-helm-chart-load-rules-from-sql-query-set.html\",\"url\":\"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/proxysql-helm-chart-load-rules-from-sql-query-set.html\",\"name\":\"Netexpertise - ProxySQL Helm Chart ⎈ Load Rules from SQL Query Set\",\"isPartOf\":{\"@id\":\"https:\/\/www.netexpertise.eu\/en\/#website\"},\"datePublished\":\"2021-07-06T06:03:24+00:00\",\"dateModified\":\"2021-09-21T06:34:44+00:00\",\"author\":{\"@id\":\"https:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa\"},\"description\":\"ProxySQL Helm Chart that loads configuration from SQL Query Set. Define rules in Values file and deploy on Kubernetes\",\"breadcrumb\":{\"@id\":\"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/proxysql-helm-chart-load-rules-from-sql-query-set.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/proxysql-helm-chart-load-rules-from-sql-query-set.html\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/proxysql-helm-chart-load-rules-from-sql-query-set.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.netexpertise.eu\/en\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"ProxySQL Helm Chart ⎈ Load Rules from SQL Query Set\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.netexpertise.eu\/en\/#website\",\"url\":\"https:\/\/www.netexpertise.eu\/en\/\",\"name\":\"Netexpertise\",\"description\":\"Systems \/ Networks \/ DevOps\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.netexpertise.eu\/en\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa\",\"name\":\"dave\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g\",\"contentUrl\":\"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g\",\"caption\":\"dave\"},\"sameAs\":[\"http:\/\/www.netexpertise.eu\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Netexpertise - ProxySQL Helm Chart ⎈ Load Rules from SQL Query Set","description":"ProxySQL Helm Chart that loads configuration from SQL Query Set. Define rules in Values file and deploy on Kubernetes","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/proxysql-helm-chart-load-rules-from-sql-query-set.html","og_locale":"en_US","og_type":"article","og_title":"Netexpertise - ProxySQL Helm Chart ⎈ Load Rules from SQL Query Set","og_description":"ProxySQL Helm Chart that loads configuration from SQL Query Set. Define rules in Values file and deploy on Kubernetes","og_url":"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/proxysql-helm-chart-load-rules-from-sql-query-set.html","og_site_name":"Netexpertise","article_published_time":"2021-07-06T06:03:24+00:00","article_modified_time":"2021-09-21T06:34:44+00:00","author":"dave","twitter_card":"summary_large_image","twitter_creator":"@netexpertise","twitter_site":"@netexpertise","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/proxysql-helm-chart-load-rules-from-sql-query-set.html","url":"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/proxysql-helm-chart-load-rules-from-sql-query-set.html","name":"Netexpertise - ProxySQL Helm Chart ⎈ Load Rules from SQL Query Set","isPartOf":{"@id":"https:\/\/www.netexpertise.eu\/en\/#website"},"datePublished":"2021-07-06T06:03:24+00:00","dateModified":"2021-09-21T06:34:44+00:00","author":{"@id":"https:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa"},"description":"ProxySQL Helm Chart that loads configuration from SQL Query Set. Define rules in Values file and deploy on Kubernetes","breadcrumb":{"@id":"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/proxysql-helm-chart-load-rules-from-sql-query-set.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.netexpertise.eu\/en\/database\/mysql\/proxysql-helm-chart-load-rules-from-sql-query-set.html"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/proxysql-helm-chart-load-rules-from-sql-query-set.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.netexpertise.eu\/en"},{"@type":"ListItem","position":2,"name":"ProxySQL Helm Chart ⎈ Load Rules from SQL Query Set"}]},{"@type":"WebSite","@id":"https:\/\/www.netexpertise.eu\/en\/#website","url":"https:\/\/www.netexpertise.eu\/en\/","name":"Netexpertise","description":"Systems \/ Networks \/ DevOps","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.netexpertise.eu\/en\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa","name":"dave","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/image\/","url":"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g","contentUrl":"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g","caption":"dave"},"sameAs":["http:\/\/www.netexpertise.eu"]}]}},"_links":{"self":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/posts\/1474"}],"collection":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/comments?post=1474"}],"version-history":[{"count":0,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/posts\/1474\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/media?parent=1474"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/categories?post=1474"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/tags?post=1474"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}