Get the most out of your Centmin Mod LEMP stack
Become a Member

Install Hands on Maxscale and Mariadb Galera Cluster

Discussion in 'Centmin Mod User Tutorials & Guides' started by brijendrasial, Dec 20, 2019.

  1. brijendrasial

    brijendrasial Active Member

    125
    94
    28
    Mar 21, 2018
    Ratings:
    +148
    Local Time:
    5:28 PM
    1.13.9
    10.0.22-MariaDB
    MariaDB MaxScale is a database proxy that extends the high availability, scalability, and security of MariaDB Server while at the same time simplifying application development by decoupling it from underlying database infrastructure.

    First Create 4 Nodes of Centminmod Installation

    Node1:192.168.1.1 (CMM Installed) (MariaDB Cluster 1)
    Node2:192.168.1.2 (CMM Installed) (MariaDB Cluster 2)
    Node3:192.168.1.3 (CMM Installed) (MariaDB Cluster 3)
    Node4:192.168.1.4 (CMM Installed) (Maxscale)


    Open These ports 4567,4568,4444,3306 under TCP in your firewall (CSF or IPTABLES) on All MariaDB Cluster nodes.

    On 192.168.1.1 (CMM Installed) (MariaDB Cluster 1)

    Edit my.cnf file and at the end add lines below

    Must change
    Replace wsrep_node_address=”192.168.1.1″ with your node1 IP address.
    Replace wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3 with your node1,node2,node3 IP respectively.


    Alternatively you can also

    Replace wsrep_cluster_name=”bullten” as you want but do remember it should be same on all nodes.
    Replace wsrep_node_name=”node1″ to any name you want.


    Code:
    vi /etc/my.cnf

    Stop MariaDB

    Code:
    systemctl stop mariadb

    Start Galera Cluster

    Code:
    galera_new_cluster
    Change the permission
    Code:
    chown -R mysql:mysql /var/lib/mysql

    On 192.168.1.2 (CMM Installed) (MariaDB Cluster 2)


    Edit my.cnf file and at the end add lines below

    Must change
    Replace wsrep_node_address=”192.168.1.2″ with your node2 IP address.
    Replace wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3 with your node1,node2,node3 IP respectively.


    Alternatively you can also

    Replace wsrep_node_name=”node2″ to any name you want.

    Code:
    vi /etc/my.cnf
    Change the permission
    Code:
    chown -R mysql:mysql /var/lib/mysql
    Stop MariaDB
    Code:
    systemctl stop mariadb
    Start MariaDB
    Code:
    systemctl start mariadb

    On 192.168.1.3 (CMM Installed) (MariaDB Cluster 3)


    Edit my.cnf file and at the end add lines below

    Must change
    Replace wsrep_node_address=”192.168.1.3″ with your node3 IP address.
    Replace wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3 with your node1,node2,node3 IP respectively.


    Alternatively you can also

    Replace wsrep_node_name=”node3″ to any name you want.

    Code:
    vi /etc/my.cnf
    Change the permission
    Code:
    chown -R mysql:mysql /var/lib/mysql
    Stop MariaDB
    Code:
    systemctl stop mariadb
    Start MariaDB
    Code:
    systemctl start mariadb
    Now your Node is connected to check cluster size run the below command on any MariaDB node

    Code:
    SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';
    [​IMG]
    wsrep_cluster_size = 3 shows three nodes are inter connected.


    On
    192.168.1.4 (CMM Installed) Install (Maxscale)

    Install Maxscale

    Code:
    curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -- --mariadb-server-version=mariadb-10.3
    sed -i 's|mariadb-main|mariadb|' /etc/yum.repos.d/mariadb.repo
    yum -y install maxscale
    
    On one of the MariaDB node run the below commands. Replace 192.168.1.4 with your maxscale node IP.

    Code:
    create user 'maxscale_user'@'192.168.1.4' identified by 'maxscale_password';
    
    grant select on mysql.user to 'maxscale_user'@'192.168.1.4';
    
    grant select on mysql.roles_mapping to 'maxscale_user'@'192.168.1.4';
    
    grant select on mysql.db to 'maxscale_user'@'192.168.1.4';
    
    grant select on mysql.tables_priv to 'maxscale_user'@'192.168.1.4';
    
    grant show databases on *.* to 'maxscale_user'@'192.168.1.4';
    Generate and save the encrypted password
    Code:
    maxkeys
    maxpasswd maxscale_password
    [​IMG]

    Save Original configuration file of Maxscale

    Code:
    mv /etc/maxscale.cnf /etc/maxscale.cnf.old


    Create new maxscale.cnf file and add below entries.


    password=9D797E4DAB257039C167186BC2527463DB5878F85E543A3A418EDA8ED447344B is the encrypted password we created above i.e maxscale_password.
    module=galeramon describes galera cluster monitoring
    port=5492 Port at which remotely we will connect.


    Code:
    vi /etc/maxscale.cnf
    verify configuration file

    Code:
    maxscale -c -U maxscale
    Start and enable Maxscale service

    Code:
    systemctl start maxscale.service
    systemctl enable maxscale.service
    On Maxscale node enter into mysql and check cluster. After running the below command stop any node of MariaDB and check if hostname values changes then.

    Code:
    mysql -h 192.68.1.4 -P 5492 -u maxscale_user -pmaxscale_password
    show variables like 'hostname';
    [​IMG]
    Now lets try some Maxscale commands using maxctrl

    Code:
    maxctrl list servers
    [​IMG]
    Code:
    maxctrl show servers
    [​IMG]
    Code:
    maxctrl list modules
    [​IMG]
    Code:
    maxctrl list services
    [​IMG]
    Code:
    maxctrl list threads
    [​IMG]
    Take Server Out for Maintenance.

    Code:
    maxctrl set server server1 maintenance
    [​IMG]
    Set server back online after maintenance

    Code:
    maxctrl clear server server1 maintenance
    [​IMG]
    Code:
    maxctrl show threads --tsv
    Source: Hands on Maxscale and Galera Cluster Centos7 – LinuxGlobe
     
    Last edited: Dec 21, 2019
  2. eva2000

    eva2000 Administrator Staff Member

    44,718
    10,193
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,797
    Local Time:
    9:58 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    Thanks for sharing ! (y)

    Regarding maxscale install
    Code (Text):
    curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
    yum -y install maxscale
    

    be careful that installs 3 repos including one that Centmin Mod already setup for MariaDB and it defaults to MariaDB 10.4 instead of Centmin Mod 123.09beta01's MariaDB 10.3 ! So if you did a yum update you would accidently update Centmin Mod MariaDB 10.3 to 10.4 which you don't want to do right now. Centmin Mod has it's own way of updating to MariaDB 10.4 which automatically takes into account /etc/my.cnf settings changes required for MariaDB 10.4 https://community.centminmod.com/threads/mariadb-10-1-upgrades-to-mariadb-10-2-10-3-and-10-4.17793/

    So instead make sure you specify MariaDB 10.3 as server version repo you want
    Code (Text):
    mkdir -p /root/tools
    cd /root/tools
    wget -4 https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
    chmod +x mariadb_repo_setup
    ./mariadb_repo_setup --mariadb-server-version=mariadb-10.3
    yum -y install maxscale
    

    example of repo setup run
    Code (Text):
    ./mariadb_repo_setup --mariadb-server-version=mariadb-10.3
    [warning] Found existing file at /etc/yum.repos.d/mariadb.repo. Moving to /etc/yum.repos.d/mariadb.repo.old_1.
    [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo.
    [info] Adding trusted package signing keys...
    [info] Successfully added trusted package signing keys.
    

    sdiff side by side diff compare of differences between old Centmin Mod setup mariadb.repo (left) vs mariadb_repo_setup script's setup mariadb.repo (right). Looks like even the baseurl differs using yum.mariadb.org/10.3/centos7-amd64 vs downloads.mariadb.com/MariaDB/mariadb-10.3/
    Code (Text):
    sdiff -w200 -s /etc/yum.repos.d/mariadb.repo.old_1 /etc/yum.repos.d/mariadb.repo
    name = MariaDB                                                                                     |    name = MariaDB Server
    baseurl = http://yum.mariadb.org/10.3/centos7-amd64                                                |    baseurl = https://downloads.mariadb.com/MariaDB/mariadb-10.3/yum/rhel/$releasever/$basearch
    gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB                                                 |    gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Server-GPG-KEY
    gpgcheck=1                                                                                         |    gpgcheck = 1
    exclude=MariaDB-Galera-server                                                                      |    enabled = 1
                                                                                                       >
                                                                                                       >    [mariadb-maxscale]
                                                                                                       >    # To use the latest stable release of MaxScale, use "latest" as the version
                                                                                                       >    # To use the latest beta (or stable if no current beta) release of MaxScale, use "beta" as the v
                                                                                                       >    name = MariaDB MaxScale
                                                                                                       >    baseurl = https://downloads.mariadb.com/MaxScale/2.4/centos/$releasever/$basearch
                                                                                                       >    gpgkey = file:///etc/pki/rpm-gpg/MariaDB-MaxScale-GPG-KEY
                                                                                                       >    gpgcheck = 1
                                                                                                       >    enabled = 1
                                                                                                       >
                                                                                                       >    [mariadb-tools]
                                                                                                       >    name = MariaDB Tools
                                                                                                       >    baseurl = https://downloads.mariadb.com/Tools/rhel/$releasever/$basearch
                                                                                                       >    gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Enterprise-GPG-KEY
                                                                                                       >    gpgcheck = 1
                                                                                                       >    enabled = 1
    

    or via diff
    Code (Text):
    diff -u /etc/yum.repos.d/mariadb.repo.old_1 /etc/yum.repos.d/mariadb.repo
    --- /etc/yum.repos.d/mariadb.repo.old_1 2019-09-25 13:23:04.412553694 +0000
    +++ /etc/yum.repos.d/mariadb.repo       2019-12-20 16:26:25.483536220 +0000
    @@ -1,6 +1,22 @@
    -[mariadb]
    -name = MariaDB
    -baseurl = http://yum.mariadb.org/10.3/centos7-amd64
    -gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
    -gpgcheck=1
    -exclude=MariaDB-Galera-server
    +[mariadb-main]
    +name = MariaDB Server
    +baseurl = https://downloads.mariadb.com/MariaDB/mariadb-10.3/yum/rhel/$releasever/$basearch
    +gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Server-GPG-KEY
    +gpgcheck = 1
    +enabled = 1
    +
    +[mariadb-maxscale]
    +# To use the latest stable release of MaxScale, use "latest" as the version
    +# To use the latest beta (or stable if no current beta) release of MaxScale, use "beta" as the version
    +name = MariaDB MaxScale
    +baseurl = https://downloads.mariadb.com/MaxScale/2.4/centos/$releasever/$basearch
    +gpgkey = file:///etc/pki/rpm-gpg/MariaDB-MaxScale-GPG-KEY
    +gpgcheck = 1
    +enabled = 1
    +
    +[mariadb-tools]
    +name = MariaDB Tools
    +baseurl = https://downloads.mariadb.com/Tools/rhel/$releasever/$basearch
    +gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Enterprise-GPG-KEY
    +gpgcheck = 1
    +enabled = 1
    

    also the main mariadb repo name changes form mariadb to mariadb-main so any Centmin Mod features which look for mariadb repo name will be broken now

    example listing available mariadb repo yum packages referencing mariadb will be broken
    Code (Text):
    yum list available --disablerepo=* --enablerepo=mariadb
    Loaded plugins: fastestmirror, priorities, versionlock
    Error getting repository data for mariadb, repository not found
    

    while referencing the new renamed mariadb-main will work
    Code (Text):
    yum list available --disablerepo=* --enablerepo=mariadb-main
    Loaded plugins: fastestmirror, priorities, versionlock
    Loading mirror speeds from cached hostfile
    Available Packages
    MariaDB-backup.x86_64                                                                                                                10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-backup-debuginfo.x86_64                                                                                                      10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-cassandra-engine.x86_64                                                                                                      10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-cassandra-engine-debuginfo.x86_64                                                                                            10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-client-debuginfo.x86_64                                                                                                      10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-common-debuginfo.x86_64                                                                                                      10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-connect-engine.x86_64                                                                                                        10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-connect-engine-debuginfo.x86_64                                                                                              10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-cracklib-password-check.x86_64                                                                                               10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-cracklib-password-check-debuginfo.x86_64                                                                                     10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-devel-debuginfo.x86_64                                                                                                       10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-gssapi-server.x86_64                                                                                                         10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-gssapi-server-debuginfo.x86_64                                                                                               10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-oqgraph-engine.x86_64                                                                                                        10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-oqgraph-engine-debuginfo.x86_64                                                                                              10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-rocksdb-engine.x86_64                                                                                                        10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-rocksdb-engine-debuginfo.x86_64                                                                                              10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-server-debuginfo.x86_64                                                                                                      10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-shared-debuginfo.x86_64                                                                                                      10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-test.x86_64                                                                                                                  10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-test-debuginfo.x86_64                                                                                                        10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-tokudb-engine.x86_64                                                                                                         10.3.21-1.el7.centos                                                                                     mariadb-main
    MariaDB-tokudb-engine-debuginfo.x86_64                                                                                               10.3.21-1.el7.centos                                                                                     mariadb-main
    libzstd.x86_64                                                                                                                       1.3.4-1.el7                                                                                              mariadb-main
    
    of course easy to fix, just edit mariadb.repo and change name of mariadb-main repo back to mariadb via sed replacement
    Code (Text):
    sed -i 's|mariadb-main|mariadb|' /etc/yum.repos.d/mariadb.repo
    

    contents of edited /etc/yum.repos.d/mariadb.repo
    Code (Text):
    [mariadb]
    name = MariaDB Server
    baseurl = https://downloads.mariadb.com/MariaDB/mariadb-10.3/yum/rhel/$releasever/$basearch
    gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Server-GPG-KEY
    gpgcheck = 1
    enabled = 1
    
    [mariadb-maxscale]
    # To use the latest stable release of MaxScale, use "latest" as the version
    # To use the latest beta (or stable if no current beta) release of MaxScale, use "beta" as the version
    name = MariaDB MaxScale
    baseurl = https://downloads.mariadb.com/MaxScale/2.4/centos/$releasever/$basearch
    gpgkey = file:///etc/pki/rpm-gpg/MariaDB-MaxScale-GPG-KEY
    gpgcheck = 1
    enabled = 1
    
    [mariadb-tools]
    name = MariaDB Tools
    baseurl = https://downloads.mariadb.com/Tools/rhel/$releasever/$basearch
    gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Enterprise-GPG-KEY
    gpgcheck = 1
    enabled = 1
    

    verify referencing MariaDB main yup repo via = mariadb name
    Code (Text):
    yum list available --disablerepo=* --enablerepo=mariadb
    Loaded plugins: fastestmirror, priorities, versionlock
    Loading mirror speeds from cached hostfile
    mariadb                                                                                                                                                                                                                            | 2.9 kB  00:00:00  
    mariadb/7/x86_64/primary_db                                                                                                                                                                                                        |  53 kB  00:00:02  
    Available Packages
    MariaDB-backup.x86_64                                                                                                                  10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-backup-debuginfo.x86_64                                                                                                        10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-cassandra-engine.x86_64                                                                                                        10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-cassandra-engine-debuginfo.x86_64                                                                                              10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-client-debuginfo.x86_64                                                                                                        10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-common-debuginfo.x86_64                                                                                                        10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-connect-engine.x86_64                                                                                                          10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-connect-engine-debuginfo.x86_64                                                                                                10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-cracklib-password-check.x86_64                                                                                                 10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-cracklib-password-check-debuginfo.x86_64                                                                                       10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-devel-debuginfo.x86_64                                                                                                         10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-gssapi-server.x86_64                                                                                                           10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-gssapi-server-debuginfo.x86_64                                                                                                 10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-oqgraph-engine.x86_64                                                                                                          10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-oqgraph-engine-debuginfo.x86_64                                                                                                10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-rocksdb-engine.x86_64                                                                                                          10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-rocksdb-engine-debuginfo.x86_64                                                                                                10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-server-debuginfo.x86_64                                                                                                        10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-shared-debuginfo.x86_64                                                                                                        10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-test.x86_64                                                                                                                    10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-test-debuginfo.x86_64                                                                                                          10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-tokudb-engine.x86_64                                                                                                           10.3.21-1.el7.centos                                                                                        mariadb
    MariaDB-tokudb-engine-debuginfo.x86_64                                                                                                 10.3.21-1.el7.centos                                                                                        mariadb
    libzstd.x86_64                                                                                                                         1.3.4-1.el7                                                                                                 mariadb
    
     
  3. brijendrasial

    brijendrasial Active Member

    125
    94
    28
    Mar 21, 2018
    Ratings:
    +148
    Local Time:
    5:28 PM
    1.13.9
    10.0.22-MariaDB
    Always I keep my proxy node without centminmod so didnt notice that issue. Thanks for update :)
     
  4. eva2000

    eva2000 Administrator Staff Member

    44,718
    10,193
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,797
    Local Time:
    9:58 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    Yeah some folks might try this on their Centmin Mod servers so good to highlight this :)

    I believe you can just change
    Code (Text):
    curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
    yum -y install maxscale
    

    to
    Code (Text):
    curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -- --mariadb-server-version=mariadb-10.3
    sed -i 's|mariadb-main|mariadb|' /etc/yum.repos.d/mariadb.repo
    yum -y install maxscale
    
     
  5. brijendrasial

    brijendrasial Active Member

    125
    94
    28
    Mar 21, 2018
    Ratings:
    +148
    Local Time:
    5:28 PM
    1.13.9
    10.0.22-MariaDB
    Perfect done..
     
  6. pamamolf

    pamamolf Premium Member Premium Member

    3,824
    370
    83
    May 31, 2014
    Ratings:
    +712
    Local Time:
    2:58 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    4567 port may need udp also?

    4567 For Galera Cluster replication traffic. Multicast replication uses both UDP transport and TCP on this port.