{"id":2517,"date":"2019-10-08T17:55:35","date_gmt":"2019-10-08T17:55:35","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=2517"},"modified":"2024-01-27T17:28:30","modified_gmt":"2024-01-27T17:28:30","slug":"proxysql-configuration-file-startup-process-explained","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/proxysql-configuration-file-startup-process-explained","title":{"rendered":"ProxySQL Configuration &#038; Startup Process explained"},"content":{"rendered":"\n<p>When learning ProxySQL, we have seen some confusion around the configuration and especially around the usage of the configuration file. In this post, we will explain how ProxySQL treats its internal configuration and its configuration file.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">ProxySQL configuration<\/h2>\n\n\n\n<p>For the initial startup, the configuration is loaded from the&nbsp;\/etc\/proxysql.cnf&nbsp;file. At this time, the configuration file will be parsed and the settings will be stored in the embedded SQLite database. From this point onwards, the settings will be accessed from the SQLite database.<\/p>\n\n\n\n<p>However, there are four configuration settings which are always parsed from the config file, even when the SQLite database is present. This places a requirement for the configuration file to always be in the place, if missing, ProxySQL terminates.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>datadir:<\/strong>&nbsp;The path of ProxySQL datadir which stores database file, logs.<\/li>\n\n\n\n<li><strong>restart_on_missing_heartbeats (new in 1.4.4):<\/strong>&nbsp;If MySQL threads miss restart_on_missing_heartbeats (number of) heartbeats, proxysql will raise a SIGABRT signal and restart. Default is&nbsp;10.<\/li>\n\n\n\n<li><strong>execute_on_exit_failure (new in 1.4.4):<\/strong>&nbsp;If set, ProxySQL\u2019s parent process will execute the defined script every time ProxySQL crashes. It is recommended to use this setting to generate an alert or log the event.&nbsp;<\/li>\n\n\n\n<li><strong>errorlog (new in 2.0.0):<\/strong>&nbsp;The file to be used as error-log. Defaults to&nbsp;<em>${datadir}<\/em>\/proxysql.log<\/li>\n<\/ul>\n\n\n\n<!--more-->\n\n\n\n<p>ProxySQL also offers some flags to force reloading of the configuration file.<\/p>\n\n\n\n<p>The first option is to run the proxysql \u2013initial command which will recreate the SQLite database and reprovision if with the data from the configuration from file.<br>The second startup option is to use proxysql \u2013reload, which will merge both the config file (\/etc\/proxysql.cnf) and SQLite database values to bring up the proxysql node.<\/p>\n\n\n\n<p>To initiate, ProxySQL will look to parse the configuration file, load contents to an in-memory database, persist it on disk and, finally, propagate to runtime level to bring it to the effect. Although if this is not an initial run and we already have database file (sqlite), then proxysql won\u2019t really parse the configuration file and load data straight from the disk db.<\/p>\n\n\n\n<p>For loading an updated configuration option from configuration files, we may use the following command on proxysql admin prompt.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><em>LOAD &lt;XYZ&gt; FROM CONFIG FILE<\/em><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">ProxySQL Startup Process<\/h2>\n\n\n\n<p>Consider the following image depicting the proxysql startup process:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" width=\"1024\" height=\"700\" src=\"http:\/\/kedar.nitty-witty.com\/wp-content\/uploads\/2019\/09\/ProxySQL-Startup-Process-1024x700.png\" alt=\"\" class=\"wp-image-2518\" srcset=\"https:\/\/kedar.nitty-witty.com\/blog\/wp-content\/uploads\/2019\/09\/ProxySQL-Startup-Process-1024x700.png 1024w, https:\/\/kedar.nitty-witty.com\/blog\/wp-content\/uploads\/2019\/09\/ProxySQL-Startup-Process-300x205.png 300w, https:\/\/kedar.nitty-witty.com\/blog\/wp-content\/uploads\/2019\/09\/ProxySQL-Startup-Process-768x525.png 768w, https:\/\/kedar.nitty-witty.com\/blog\/wp-content\/uploads\/2019\/09\/ProxySQL-Startup-Process.png 2468w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">The ProxySQL Configuration Layers<\/h2>\n\n\n\n<p>Lets now take a look at an important aspect of configuration. The configuration loaded from&nbsp;proxysql.cnf&nbsp;file or added from admin module flows through the ProxySQL configuration system. The ProxySQL configuration system is made up of 4 layers:&nbsp;<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>CONFIG:<\/strong>&nbsp;<em>This is the initial layer representing the configuration file (proxysql.cnf) from which the configuration is loaded to MEMORY.&nbsp;<\/em><\/li>\n\n\n\n<li><strong>MEMORY:&nbsp;<\/strong><em>This layer represents the in-memory database maintained by ProxySQL. (This is not the running configuration!)<\/em><\/li>\n\n\n\n<li><strong>DISK:&nbsp;<\/strong><em>This layer persists the configuration on disk. ProxySQL uses an SQLite database file to store its configuration.<\/em><\/li>\n\n\n\n<li><strong>RUNTIME:<\/strong>&nbsp;T<em>he configuration can be loaded to RUNTIME layer which is the configuration used by ProxySQL actively<\/em>.<\/li>\n<\/ol>\n\n\n\n<p>The following is the image of the configuration flow commands between layers.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" width=\"1024\" height=\"693\" src=\"http:\/\/kedar.nitty-witty.com\/wp-content\/uploads\/2019\/09\/ProxySQL-Layers-1024x693.png\" alt=\"\" class=\"wp-image-2519\" srcset=\"https:\/\/kedar.nitty-witty.com\/blog\/wp-content\/uploads\/2019\/09\/ProxySQL-Layers-1024x693.png 1024w, https:\/\/kedar.nitty-witty.com\/blog\/wp-content\/uploads\/2019\/09\/ProxySQL-Layers-300x203.png 300w, https:\/\/kedar.nitty-witty.com\/blog\/wp-content\/uploads\/2019\/09\/ProxySQL-Layers-768x520.png 768w, https:\/\/kedar.nitty-witty.com\/blog\/wp-content\/uploads\/2019\/09\/ProxySQL-Layers.png 2025w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><strong>Remember:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><em>Until the configuration is in RUNTIME layer, it won\u2019t go into effect.<\/em><\/li>\n\n\n\n<li><em>Until you move the configuration changes to DISK, the changes won\u2019t persist proxysql restarts\/reload.<\/em><\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">ProxySQL Initial Configuration<\/h2>\n\n\n\n<p>Now let\u2019s explore the configuration options. Every proxysql setup comes with a default configuration file with default credentials. It is recommended to change at least the default admin credentials before starting the proxysql service in production.<\/p>\n\n\n\n<p>The default proxysql configuration file can be found on its repo: https:\/\/github.com\/sysown\/proxysql\/blob\/v2.0.7\/etc\/proxysql.cnf<br>The default proxysql configuration file (\/etc\/proxysql.cnf) starts with two important parameters: datadir and errorlog. These are pretty much self-explanatory.<br>The contents of datadir include the ProxySQL\u2019s database, error log and stats database.<\/p>\n\n\n\n<p><strong>\u2013 admin_variables<\/strong><br>Two important settings here are admin_credentials and mysql_ifaces.<\/p>\n\n\n\n<p>1. admin_credentials are the semi-colon separated user:password pairs that can administer the ProxySQL.<br>Eg: admin_credentils=\u201dadmin_user1:admin_pass1; admin_user2;admin_pass2\u201d<\/p>\n\n\n\n<p>Note: ProxySQL supports encrypted passwords even in the configuration file using the mysql_native_password encryption<\/p>\n\n\n\n<p>2. mysql_ifaces specifies the list of host:port entries or socket on which the admin interface accept connections.<\/p>\n\n\n\n<p><strong>\u2013 mysql_variables<\/strong><\/p>\n\n\n\n<p>Among other variables, the following are the noteworthy variables which I would look at changing:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Interfaces: This interface is for the incoming MySQL connection requests. It consists of semi-colon separated list of host:port entries or sockets.<\/li>\n\n\n\n<li>Monitor_username \/ monitor_password are the variables specifying a monitoring user with at-least USAGE privileges to connect, ping and test MySQL read_only status. You may need a REPLICATION CLIENT privilege for this user if replication lag needs to be monitored.<\/li>\n<\/ul>\n\n\n\n<p>The following sections are largely handled from the admin interface rather than the configuration file.<\/p>\n\n\n\n<p><strong>\u2013 mysql_servers<\/strong><br><strong>\u2013 mysql_users<\/strong><br><strong>\u2013 mysql_query_rules<\/strong><br><strong>\u2013 scheduler<\/strong><br><strong>\u2013 mysql_replication_hostgroups<\/strong><br><strong>\u2013 mysql_galera_hostgroups (from ProxySQL 2.x)<\/strong><br><strong>\u2013 mysql_group_replication_hostgroups (from ProxySQL 2.x)<\/strong><br><strong>\u2013 proxysql_servers (from ProxySQL 1.4.x)<\/strong><\/p>\n\n\n\n<p>Covering variables is beyond the scope of this post. However, ProxySQL documentation is well-maintained, so have a look at it for more details on the configuration variables.<\/p>\n\n\n\n<p>I have a very interesting use-case for ProxySQL I wanted to share with you. It is about setting-up <a href=\"http:\/\/kedar.nitty-witty.com\/how-to-replicate-without-binary-logs-in-mysql?preview_id=2968&amp;preview_nonce=d114ab23c2&amp;preview=true\" target=\"_blank\" rel=\"noopener\" title=\"replication in MySQL without binary log\">replication in MySQL without binary log<\/a>. <\/p>\n\n\n\n<p><strong>Note: <\/strong>I originally wrote this at <a href=\"https:\/\/blog.pythian.com\/proxysql-configuration-file-startup-process-explained\/\" rel=\"nofollow\" title=\"\">https:\/\/blog.pythian.com\/proxysql-configuration-file-startup-process-explained\/<\/a><\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"This post covers Proxysql start up process, Layers and configuration file.\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,377,866],"tags":[427,479,478,480,481],"class_list":{"0":"post-2517","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-articles","8":"category-proxysql","9":"tag-mysql","10":"tag-proxysql","11":"tag-proxysql-configuration","12":"tag-proxysql-layers","13":"tag-proxysql-startup"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2517","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=2517"}],"version-history":[{"count":5,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2517\/revisions"}],"predecessor-version":[{"id":3048,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2517\/revisions\/3048"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=2517"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=2517"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=2517"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}