MySQL middleware proxysql (3): Multi-layer configuration system

Libraries in 1.ProxySQL

Use Proxysql's admin management interface to connect to Proxysql to see the libraries owned by Proxysql.

mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt 'admin> '

admin> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+

which

• Main library is the main library of Proxysql, is the library that needs to modify the configuration , it is actually a memory database system . Therefore, after modifying the configuration in the main library, you must persist it to disk for permanent storage.
  
• Disk Library is a database of disks that is fully consistent with the in-memory database. When the configuration in the memory database is persisted, it is actually written to disk library. The default path for the disk database is$DATADIR/proxysql.db.
  
• The stats library is a statistical information library. The data in this library is generally populated when the data is retrieved, and it is stored in memory. There is no need to persist because there are no related configuration items.
  
• The Monitor library is a library of monitoring back-end MySQL nodes with only a few log class tables, and the monitoring information collected by the monitoring module is stored in the corresponding log table.
  
• The Stats_history library is a new library in version 1.4.4 that holds historical statistics. The default path is$DATADIR/proxysql_stats.db.

Proxysql internal use of the SQLite3 database, whether in the memory database or disk database, is through the SQLite3 engine for parsing, operation. It may be slightly different from MySQL syntax, but proxysql automatically adjusts for incompatible grammars to maximize the efficiency of MySQL statements.

The main library is described above, only to say that the memory database needs to be persisted to disk library to permanently save the configuration. In fact, after modifying the configuration in the main library, it does not take effect immediately, it also needs to load into the data structure of the runtime to take effect, only the configuration in the runtime data structure is the current valid configuration of Proxysql. Take a look at the "Multilayer configuration system" below.

2.ProxySQL multi-layer configuration system

Proxysql's configuration system is very powerful, it can modify almost all configurations online ( only two variables that need to be restarted to take effectmysql-threadsmysql-stacksize), and online effective, persistent save . This is thanks to the multi-tiered configuration system it employs.

The multilayer configuration system is structured as follows:

+-------------------------+
       |         RUNTIME         |
       +-------------------------+
              /|\          |
               |           |
           [1] |       [2] |
               |          \|/
       +-------------------------+
       |         MEMORY          |
       +-------------------------+ _
              /|\          |      |               |           |                   [3] |       [4] |         \ [5]
               |          \|/                +-------------------------+  +---------------+
       |          DISK           |  |  CONFIG FILE  |
       +-------------------------+  +---------------+

Explain this 3-tier configuration system.

At the bottom is disk Library and config file. Note here that the config file here is the traditional profile, the default is/etc/ Proxysql.cnf,proxysql startup, mainly from disk library read configuration loaded into memory and finally loaded into the runtime to take effect, only a few specific configuration content is loaded from config file, Unless the PROXYSQL runtime environment (or disk library is empty) is initialized for the first time.

The middle layer is memory, which means that the database is actually the main library. All configurations modified through the management interface are saved in the in-memory database (main). When Proxysql restarts or crashes, the data in this memory database is lost, so save to disk library is required.

At the top is runtime, which is the proxysql about the data structure that the thread reads when it runs. In other words, the configuration in this data structure is a configuration that is in effect. Therefore, after modifying the configuration in the main library, it must be load into the runtime data structure to make it effective.

In the multilayer configuration system diagram above, the ordinal numbers of [1], [2], [3], [4], [5] are labeled. Each serial number has two operation directionfrom/to b, actually only the angle of the station is different. Here are the actions for each sequence number:

[1]: Load the configuration from the in-memory database into the RUNTIME data structure
         LOAD XXX FROM MEMORY
         LOAD XXX TO RUNTIME

[2]: Persist the configuration in the RUNTIME data structure to the in-memory database
         SAVE XXX FROM RUNTIME
         SAVE XXX TO MEMORY

[3]: Load the configuration from the disk database into the in-memory database
         LOAD XXX FROM DISK
         LOAD XXX TO MEMORY

[4]: Persist configuration in memory database to disk database
         SAVE XXX FROM MEMORY
         SAVE XXX TO DISK

[5]: Read configuration from traditional configuration file and load it into in-memory database
         LOAD XXX FROM CONFIG

Disk/memory/runtime/config can be abbreviated, as long as it can be identified. For exampleMEMORY, you can abbreviate toMEMruntimerun.

Plus, what's up thereXXX? This indicates what kind of configuration to load/save. The current PROXYSQL supports the following types of support:

• [x] MySQL users
  
• [x] MySQL servers
  
• [x] MySQL variables
  
• [x] MySQL query rules
  
• [x] Admin variables
  
• [X] Scheduler
  
• [] Proxysql_servers: At present, the Proxysql cluster function is still in the experimental stage, so this kind of configuration should not be used.

These can be viewed from the main library or disk library.

admin> show tables from disk;
+------------------------------------+
| tables                             |
+------------------------------------+
| global_variables                   |  # (1)
| mysql_collations                   |  # (N)
| mysql_group_replication_hostgroups |  # (2)
| mysql_query_rules                  |  # (3)
| mysql_query_rules_fast_routing     |  # (4)
| mysql_replication_hostgroups       |  # (5)
| mysql_servers                      |  # (6)
| mysql_users                        |  # (7)
| proxysql_servers                   |  # (8)
| scheduler                          |  # (9)
+------------------------------------+

which

• (1)Contains two classes of variables,amdin-starting with the expression admin variables, withmysql-the beginning of the expression MySQL variables. What type of variable is modified, and what kind of class is represented in the preceding articleXXX.
  
• (2,5,6)The corresponding is MySQL servers.
  
• (3,4)The corresponding is MySQL query rules.
  
• (7)The corresponding MySQL users.
  
• (9)The corresponding scheduler.
  
• (N)It's just a table, saved by the character set and collation supported by Proxysql, it's not modified.
  
• (8)is the Proxysql cluster configuration table, which is still in the experimental phase. If you want to configure the feature, thenload/save proxysql_servers to/from ....

Here are a few examples: note that almost all configurations are modified on the Admin management interface, which is the recommended configuration method.

(1).mysql_serversA backend node was added to the table in Proxysql.

load mysql servers to runtime; # Load into runtime to make the configuration of this node take effect
save mysql servers to disk; # Persist the configuration of this node to the disk database

The above two sentences are equivalent to the following two sentences, but the direction of operation is different (also using abbreviations):

ad mysql servers from mem;
save mysql servers from mem;

(2).mysql_usersadded a user to the Proxysql table for sending and processing SQL statements.

load mysql users to runtime;
save mysql users to disk;

(3). Modify theadmin-variable to begin with.

load admin variables to runtime;
save admin variables to disk;

3. How to load the configuration when starting Proxysql

If the proxysql is just installed, or if the disk database file is empty (not even present), or if the option is used to start proxysql,--initialThese situations are loaded into the in-memory database when Proxysql is started, from the traditional profile config file read configuration. and automatically load to the runtime data structure, save to disk database, this is the process of initializing the proxysql running environment.

If you do not start proxysql for the first time, because a disk database file already exists, Proxysql will read almost all of the configuration from the disk database (even if an item is configured in the traditional configuration file and will not parse), but there are 3 items that must be read from the traditional configuration file . These 3 items are:

• datadir: When Proxysql starts, its data directory must be determined from the configuration file because the disk database files, logs, and other files are stored in the data directory.
  
• restart_on_missing_heartbeats: How many heartbeats the MySQL thread loses, it kills the thread and restarts it. The default value is 10.
  

• execute_on_exit_failure: If the variable is set, the Proxysql parent process will execute the already defined script every time the proxysql crashes. It is recommended to use it to generate some warnings and logs when it crashes. Note that the restart speed of the Proxysql may be only a few milliseconds, so many other monitoring tools may not be able to detect a common fault with the Proxysql, which can be used at this time.

  4. About Legacy configuration Files

The default path for the traditional profile is/etc/proxysql.cnf, or you can use it on the binary proxysql-cor--configto manually specify the configuration file.

An example of the traditional configuration file for Proxysql is as follows: Browse down and almost no need to manually configure PROXYSQL.CNF.

datadir="/var/lib/proxysql"

admin_variables=
{
        admin_credentials="admin:admin"
#       mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
        mysql_ifaces="0.0.0.0:6032"
#       refresh_interval=2000
#       debug=true
}

mysql_variables=
{
        threads=4
        max_connections=2048
        default_query_delay=0
        default_query_timeout=36000000
        have_compress=true
        poll_timeout=2000
#       interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
        interfaces="0.0.0.0:6033"
        default_schema="information_schema"
        stacksize=1048576
        server_version="5.5.30"
        connect_timeout_server=3000
# make sure to configure monitor username and password
        monitor_username="monitor"
        monitor_password="monitor"
        monitor_history=600000
        monitor_connect_interval=60000
        monitor_ping_interval=10000
        monitor_read_only_interval=1500
        monitor_read_only_timeout=500
        ping_interval_server_msec=120000
        ping_timeout_server=500
        commands_stats=true
        sessions_sort=true
        connect_retries_on_failure=10
}


# defines all the MySQL servers
mysql_servers =
(
# {
#   address = "127.0.0.1" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
#   port = 3306           # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
#   hostgroup = 0         # no default, required
#   status = "ONLINE"     # default: ONLINE
#   weight = 1            # default: 1
#   compression = 0       # default: 0
#   max_replication_lag = 10  # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned
# },
#       {
#               address = "/var/lib/mysql/mysql.sock"
#               port = 0
#               hostgroup = 0
#       },
#       {
#               address="127.0.0.1"
#               port=21891
#               hostgroup=0
#               max_connections=200
#       },
#       { address="127.0.0.2" , port=3306 , hostgroup=0, max_connections=5 },
#       { address="127.0.0.1" , port=21892 , hostgroup=1 },
#       { address="127.0.0.1" , port=21893 , hostgroup=1 }
#       { address="127.0.0.2" , port=3306 , hostgroup=1 },
#       { address="127.0.0.3" , port=3306 , hostgroup=1 },
#       { address="127.0.0.4" , port=3306 , hostgroup=1 },
#       { address="/var/lib/mysql/mysql.sock" , port=0 , hostgroup=1 }
)


# defines all the MySQL users
mysql_users:
(
#       {
#               username = "username" # no default , required
#               password = "password" # default: ''
#               default_hostgroup = 0 # default: 0
#               active = 1            # default: 1
#       },
#       {
#               username = "root"
#               password = ""
#               default_hostgroup = 0
#               max_connections=1000
#               default_schema="test"
#               active = 1
#       },
#       { username = "user1" , password = "password" , default_hostgroup = 0 , active = 0 }
)



#defines MySQL Query Rules
mysql_query_rules:
(
#       {
#               rule_id=1
#               active=1
#               match_pattern="^SELECT .* FOR UPDATE$"
#               destination_hostgroup=0
#               apply=1
#       },
#       {
#               rule_id=2
#               active=1
#               match_pattern="^SELECT"
#               destination_hostgroup=1
#               apply=1
#       }
)

scheduler=
(
#  {
#    id=1
#    active=0
#    interval_ms=10000
#    filename="/var/lib/proxysql/proxysql_galera_checker.sh"
#    arg1="0"
#    arg2="0"
#    arg3="0"
#    arg4="1"
#    arg5="/var/lib/proxysql/proxysql_galera_checker.log"
#  }
)


mysql_replication_hostgroups=
(
#        {
#                writer_hostgroup=30
#                reader_hostgroup=40
#                comment="test repl 1"
#       },
#       {
#                writer_hostgroup=50
#                reader_hostgroup=60
#                comment="test repl 2"
#        }
)

MySQL middleware proxysql (3): Multi-tier configuration system