Discover Centmin Mod today
Register Now

ProxySQL for MariaDB Galera Cluster

Discussion in 'Centmin Mod User Tutorials & Guides' started by brijendrasial, Jan 18, 2020.

  1. brijendrasial

    brijendrasial Active Member

    207
    154
    43
    Mar 21, 2018
    Ratings:
    +236
    Local Time:
    5:58 PM
    1.13.9
    10.0.22-MariaDB
    First follow the guide and install MariaDB Cluster. (https://linuxglobe.com/2020/01/17/creating-mariadb-galera-clustering/)

    Install ProxySQL
    Code:
    yum install "perl(DBD::mysql)" -y
    rpm -ivh https://github.com/sysown/proxysql/releases/download/v2.0.8/proxysql-2.0.8-1-centos7.x86_64.rpm
    systemctl start proxysql
    Check Status of ProxySQL

    Code:
    systemctl status proxysql
    [​IMG]

    Enter into ProxySQL admin


    Code:
    mysql -u admin -padmin -P6032 -h 127.0.0.1
    We have a cluster to MariaDB server with named IP’s 192.168.1.1, 192.168.1.2, 192.168.1.3 Respectively. Now we will create read and write host group for all the following above IP’s. hostgroup_id 10 is for write group and hostgroup_id 20 is for read group.

    In the above syntax hostgroup_id is 10 which is write group. Also I added a comment as write group. I added all 3 of my MariaDB servers as write group.

    In the above syntax hostgroup_id is 20 which is read group. Also I added a comment as read group. I added all 3 of my MariaDB servers as read group.

    Create default hostgroup for mysql users

    Code:
    UPDATE mysql_users SET default_hostgroup=10;
    Create users that will connect to application

    Create rule which will be sent to write group. In the below example regex is used and select update query is sent to hostgroup 10 which is write group.

    Create rule which will be sent to read group. In the below example select queries are sent to hostgroup 20 which is read group.

    Set retries on failure

    Code:
    SET mysql-query_retries_on_failure=10;
    Setup scheduler for log processing or in case of node failure to take another node in use.

    In above we can see /usr/share/proxysql/tools/proxysql_galera_checker.sh 10, 20, 1, 1 is a 10 is write hostgroup, 20 is read hostgroup, 1 is as how many writers are used and 1 means if writers are readers.

    Lets apply the changes

    Set correct permission
    Code:
    chown proxysql:proxysql /var/lib/proxysql/reload

    On one of the MariaDB node create a user as below. The monitor user is default user added by proxySQL to monitor the node

    Code:
    CREATE USER 'monitor'@'YOURPROXYSQLIP' IDENTIFIED BY 'monitor';
    On ProxySQL server.


    Code:
    mysql -u admin -padmin -P6032 -h 127.0.0.1
    
    SELECT hostgroup_id, hostname, comment, status FROM mysql_servers;
    [​IMG]
    As you can see in image write group only 1 server is online and other 2 is soft offline that means if online write node goes offline then second write node will take over and for read groups all servers are online.
    Article Source: https://linuxglobe.com/2020/01/18/proxysql-for-mariadb-galera-clustering-centos-7/