How to integrate ProxySQL in MySQL InnoDB Cluster

MySQL InnoDB Cluster is the most easy and integrated High Availability solution for MySQL.

The solution is composed of:

  • MySQL Server
  • MySQL Group Replication Plugin
  • MySQL Clone Plugin
  • MySQL Router
  • MySQL Shell

All those components are developed and tested together to provide the easiest and best experience to the MySQL users.

As the MySQL Router is a TCP Level 4 router (like HA Proxy), some users requiring a more “intelligent” proxy having other extra features like caching, read/write splitting in relation with the user or SQL, firewall, … may be interested in using ProxySQL… and this is a good choice !

ProxySQL / MySQL Router in OSI Model

However, even is ProxySQL supports MysQL Group Replication, for users coming from the easy experience when using MySQL InnoDB Cluster, the configuration might be a little confusing.

This is why I’ve created a plugin for the MySQL Shell to configure and use ProxySQL with MySQL InnoDB Cluster.

The plugin creates the hostgroups, the monitor user. It also allows you to import users from your cluster to ProxySQL without having to know any user password and leaved them hashed.

If the SYS Schema view used to monitor the cluster is missing, the plugin will also add it.

Let’s have a look on how to use it.

Installing the plugin

To install the plugin, the easiest way it to clone my github repository as ~/.mysqlsh/plugins/ext.

Then when you start MySQL Shell, you should have to possibility to see some objects in the ext global object:

ext global object completion using TAB

Preparing ProxySQL

On ProxySQL, you need to create an admin user that can connect remotely (from where you orchestrate your environment using the MySQL Shell). ProxySQL allows only adminto connect from localhost.

In /etc/proxysql.cnf, you modify the admin_variable like this:

admin_credentials="admin:admin;radmin:fred"

If you do so, you need to stop ProxySQL, remove the proxysql.db (usually in /var/lib/proxysql) and restart ProxySQL.

Now you are able to connect to ProxySQL’s SQL admin interface:

[root@mysql1 proxysql]# mysql -h 192.168.91.2 -P 6032 -u radmin -pfred
 mysql: [Warning] Using a password on the command line interface can be insecure.
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 5
 Server version: 5.5.30 (ProxySQL Admin Module)
 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
 Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Using the MySQL Shell

Let’s consider that we have a MySQL InnoDB Cluster like this:

{
     "clusterName": "myCluster", 
     "defaultReplicaSet": {
         "name": "default", 
         "primary": "mysql1:3306", 
         "ssl": "REQUIRED", 
         "status": "OK", 
         "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
         "topology": {
             "mysql1:3306": {
                 "address": "mysql1:3306", 
                 "mode": "R/W", 
                 "readReplicas": {}, 
                 "replicationLag": null, 
                 "role": "HA", 
                 "status": "ONLINE", 
                 "version": "8.0.17"
             }, 
             "mysql2:3306": {
                 "address": "mysql2:3306", 
                 "mode": "R/O", 
                 "readReplicas": {}, 
                 "replicationLag": null, 
                 "role": "HA", 
                 "status": "ONLINE", 
                 "version": "8.0.17"
             }, 
             "node3": {
                 "address": "mysql3:3306", 
                 "mode": "R/O", 
                 "readReplicas": {}, 
                 "replicationLag": null, 
                 "role": "HA", 
                 "status": "ONLINE", 
                 "version": "8.0.17"
             }
         }, 
         "topologyMode": "Single-Primary"
     }, 
     "groupInformationSourceMember": "mysql1:3306"
 }

We need to connect to one of the node, but for the configuration (writing the SYS Schema view used by ProxySQL and the monitor user, it’s better to connect directly to the primary master, mysql1 in our example. Once connected, we can create a ext.proxysql object by connecting to ProxySQL:

ext.proxysql creation in MySQL Shell – returning all methods

We can now use any method. But we also need to configure ProxySQL to use our MySQL InnoDB Cluster:

Great ! It’s possible now to see the hosts configured directly in ProxySQL and also the hostgroups that the plugin automatically created:

Now we can also import in ProxySQL the users that will connect to MySQL using the proxy. First let’s check the users created in MySQL:

List of users created in our MySQL InnoDB Cluster (in SQL mode)

As we only want the app_% users to be imported in ProxySQL, we will use the importUsers() method, the first parameter is the hostgroup id:

Import of MySQL users in ProxySQL

As you can see they are all imported in the hostgroup 2. We need to put our app_read user in hostgroup 3 (secondary / R/O) using setUserHostgroup() method:

As you can see, by default, the read/write split is done via the user. Of course you can still write your routing rules to the desired hostgroup.

And finally, we can see the usage and some statistics in the Shell using the status() method:

ProxySQL object status() method output

Conclusion

Thanks to the MySQL Shell’s plugin framework, it’s now possible to use ProxySQL with MySQL InnoDB Cluster with the same level of ease !

Don’t hesitate to test the plugin, improve it and submit pull requests 😉

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

21 Comments

  1. Hi,
    I have a problem with installing plugin on RHEL 7. I downloaded zip file and unzipped it into /root/.mysqlsh/plugins/ext directory. I restarted mysqlsh , but mysqlsh does not see ext plugin.
    When I run \? ext I get “No help items found matching ext” message.

    Thank you.

    • Hi Alec,
      Thank you for testing this.
      The content of the ext directory should be like this:

      [fred@imac ~/.mysqlsh/plugins/ext] (master) $ ls
__init__.py  __init__.pyc  innodb  LICENSE  mysqlsh_plugins_common.py  mysqlsh_plugins_common.pyc  proxysql  __pycache__  README.md  router  schema  security

      If you don’t see anything (there are some python dependencies sometimes like for the router plugin that requires python3-requests for example, check in ~/.mysqlsh/mysqlsh.log

      Cheers,

  2. HI,

    i am using Mysql 8.0.19 and proxysql 2.0.8. getting below error. help me on this.

    MySQL 172.16.1.6:3306 ssl JS > proxysql=ext.proxysql.create(‘padmin@172.16.1.7:6032’)
    Password: **********
    Connected to ProxySQL (2.0.8-67-g877cab1)
    {
    “configure”: <Function:>,
    “connections”: <Function:>,
    “getUsers”: <Function:>,
    “hostgroups”: <Function:>,
    “hosts”: <Function:>,
    “importUsers”: <Function:>,
    “setUser”: <Function:>,
    “setUserHostgroup”: <Function:>,
    “status”: <Function:>,
    “version”: <Function:>
    }
    MySQL 172.16.1.6:3306 ssl JS > proxysql.configure()
    User-defined function threw an exception:
    Traceback (most recent call last):
    File “init.py”, line 32, in
    File “/root/.mysqlsh/plugins/ext/proxysql/proxysql.py”, line 141, in configure
    session.run_sql(“create user %s identified by ‘%s'” % (self.monitor_user, self.monitor_pwd))
    mysqlsh.DBError: MySQL Error (1819): ClassicSession.run_sql: Your password does not satisfy the current policy requirements
    (ScriptingError)
    MySQL 172.16.1.6:3306 ssl JS >

    • Hi Buchanan, I think the error message is pretty clear. Could you try with a better password? (8 characters, at least 1 upper case and 1 number)

  3. Hi lefred ,
    I have two question.
    1.When the primary server innodb cluster falls down and there is a new primary server ,will proxysql auto change the primary server to the new one?

    2. I installed the plugin to /root/.mysqlsh/plugins/ext directory, mysqlsh reported loading plugin error for all the pluginslike below:

    2021-04-23 08:22:42: Error: Error loading Python file ‘/root/.mysqlsh/plugins/ext/config/init.py’:
    Execution failed:
    Traceback (most recent call last):
    File “init.py”, line 2, in
    ModuleNotFoundError: No module named ‘mysqlsh_plugins_common’

    I have to set the PYTHONPATH envirement variable,
    export PYTHONPATH=/root/.mysqlsh/plugins/ext

    then mysqlsh don’t report error . but for js, there is no the ext object ,ext.proxysql is not valid, I need to use proxysql object directly.
    what may be the problem?

    many thanks.

  5. Hi Lefred,
    I don’t undertand something about this plugin. I’m actually testing an innodb cluster on 3 Windows servers (yes, we don’t always make what we want…). So, I installed a Debian server with ProxySQL. Do you confirm that it is on this linux server where I need to install Mysql shell and your plugin ? (in your text, you seem to install it on the primary mysql server ?).
    Thank you for precisions you can give,
    thank you for your job.
    Frank

    • Hi Frank,

      The plugin should work anywhere the shell is installed and has some python modules (those are standard for this plugin). But of course ProxySQL’s admin interface must be reachable (explained in the blog post).

      As you can notice, this plugin was written some time ago already. It is compatible with ProxySQL version 2, I have not tested with newer versions.

      Cheers,

  6. Well, I installed “requests” and “prettytable” as your blog says, but I have a lot of other errors (see above some example). If there is no list, I’ll look at each error, to install each one.

    ModuleNotFoundError: No module named ‘mysqlsh_plugins_common’
    ModuleNotFoundError: No module named ‘legacy_connect’
    ModuleNotFoundError: No module named ‘support’
    ModuleNotFoundError: No module named ‘demo’
    ModuleNotFoundError: No module named ‘mysqlsh_plugins_common’
    ModuleNotFoundError: No module named ‘pyclamd’
    ModuleNotFoundError: No module named ‘router’
    ModuleNotFoundError: No module named ‘mysqlsh_plugins_common’
    ….

      • mysql shell is :
        MySQL Shell 8.0.28

        I think that I understand the mistake : I’ve follow your blog here cloned your github repository as ~/.mysqlsh/plugins/ext : so; probably, I installed all your modules, not only the proxysql one ?
        If yes, how to install only that one ?

      • Ah sh*** I post too fastly 🙁
        Probably I have to simply delete other modules in ext, isn’t it ? 😉

      • OK. If so, one last error in the log :
        ModuleNotFoundError: No module named ‘proxysql’
        but
        mysqlsh –pym pip install proxysql
        ERROR: Could not find a version that satisfies the requirement proxysql (from versions: none)
        :'(

        • the proxysql module is provided in the github plugins repo with all the others and should be in ~/.mysqlsh/plugins:

          [fred@imac ~] $ ls -ld .mysqlsh/plugins/proxysql/
          drwxrwxr-x. 1 fred fred 80 Feb 10 2021 .mysqlsh/plugins/proxysql/

          • Ooooook ! Now I see. The right commands :
            mkdir -p ~/.mysqlsh/plugins
            git clone https://github.com/lefred/mysqlshell-plugins.git ~/.mysqlsh/plugins
            mysqlsh –pym pip install requests
            mysqlsh –pym pip install pyclamd

            and now no more errors. I continue to test and tell you if it works with proxysql 2.3.

            Thanks !
            Frank

  7. Hello. Thank you for creating this wonderful plug-in.
    I really want to use this plug-in, but I can’t fix this error.

    I checked this error on mysqlsh.log

    ========
    2022-09-09 14:39:05: Error: Error loading Python file ‘/Users/xxxx/.mysqlsh/plugins/scan/init.py’:
    Execution failed:
    Traceback (most recent call last):
    File “init.py”, line 2, in
    ModuleNotFoundError: No module named ‘pyclamd’
    ========

    So I tried to install a pyclamd with a pip.

    ========
    mysqlsh –pym pip install pyclamd
    WARNING: Found errors loading plugins, for more details look at the log at: /Users/xxxx/.mysqlsh/mysqlsh.log
    Collecting pyclamd
    Using cached pyClamd-0.4.0.tar.gz (11 kB)
    Preparing metadata (setup.py): started
    Preparing metadata (setup.py): finished with status ‘error’
    error: subprocess-exited-with-error

    × python setup.py egg_info did not run successfully.
    │ exit code: 1
    ╰─> [1 lines of output]
    /Users/xxxx/mysql-shell-8.0.30-macos12-arm64/bin/mysqlsh: unknown option -c
    [end of output]

    note: This error originates from a subprocess, and is likely not a problem with pip.
    error: metadata-generation-failed

    × Encountered error while generating package metadata.
    ╰─> See above for output.

    note: This is an issue with the package mentioned above, not pip.
    hint: See above for details.
    ========

    How can I solve this problem?

    – MySQL: MySQL 8.0.30
    – mysqlsh: mysqlsh 8.0.30
    – Python: Python 3.10.6 (on Mac)

    Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.

Continue