This is an old revision of the document!
Overview
Maxscale is proxy that extends the high availability of MariaDB. MariaDB MaxScale is an advanced database proxy, firewall, and query router. MaxScale is a component of MariaDB Platform. MaxScale is compatible with MariaDB Enterprise Server, MariaDB ColumnStore, and MariaDB Community Server. Let's imagine the following configuration:
- 192.168.10.101 node1
- 192.168.10.102 node2
- 192.168.10.103 node3
- 192.168.10.104 node4
The Cluster will be configured as follows:
Maxscale:
- node1
Galera Cluster:
- node2
- node3
- node4
Setting up
To set up we have to install the packages and configure the repositories. Do that on all nodes in the cluster. In our case on all 4:
Repo Config
[root@node1 ~]# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash [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 [root@node1 ~]#
Install MariaDB Server
After that we can install the Maria DB
Install MariaDB
[root@node1 ~]# yum -y install MariaDB-server Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: ftp.cvut.cz * extras: ftp.cvut.cz * updates: ftp.cvut.cz mariadb-main | 2.9 kB 00:00:00 mariadb-maxscale | 2.4 kB 00:00:00 mariadb-tools | 2.9 kB 00:00:00 (1/3): mariadb-maxscale/7/x86_64/primary_db | 7.1 kB 00:00:00 (2/3): mariadb-tools/7/x86_64/primary_db | 14 kB 00:00:00 (3/3): mariadb-main/7/x86_64/primary_db | 70 kB 00:00:00 Resolving Dependencies --> Running transaction check ---> Package MariaDB-server.x86_64 0:10.5.6-1.el7.centos will be installed --> Processing Dependency: perl(DBI) for package: MariaDB-server-10.5.6-1.el7.centos.x86_64 --> Processing Dependency: libaio.so.1(LIBAIO_0.1)(64bit) for package: MariaDB-server-10.5.6-1.el7.centos.x86_64 --> Processing Dependency: MariaDB-client for package: MariaDB-server-10.5.6-1.el7.centos.x86_64 --> Processing Dependency: perl(Data::Dumper) for package: MariaDB-server-10.5.6-1.el7.centos.x86_64 --> Processing Dependency: galera-4 for package: MariaDB-server-10.5.6-1.el7.centos.x86_64 --> Processing Dependency: MariaDB-common for package: MariaDB-server-10.5.6-1.el7.centos.x86_64 --> Processing Dependency: lsof for package: MariaDB-server-10.5.6-1.el7.centos.x86_64 --> Processing Dependency: libaio.so.1(LIBAIO_0.4)(64bit) for package: MariaDB-server-10.5.6-1.el7.centos.x86_64 Installed: MariaDB-compat.x86_64 0:10.5.6-1.el7.centos MariaDB-server.x86_64 0:10.5.6-1.el7.centos Dependency Installed: MariaDB-client.x86_64 0:10.5.6-1.el7.centos MariaDB-common.x86_64 0:10.5.6-1.el7.centos boost-program-options.x86_64 0:1.53.0-28.el7 galera-4.x86_64 0:26.4.5-1.el7.centos libaio.x86_64 0:0.3.109-13.el7 lsof.x86_64 0:4.87-6.el7 pcre2.x86_64 0:10.23-2.el7 perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 perl-DBI.x86_64 0:1.627-4.el7 perl-Data-Dumper.x86_64 0:2.145-3.el7 perl-IO-Compress.noarch 0:2.061-2.el7 perl-Net-Daemon.noarch 0:0.48-5.el7 perl-PlRPC.noarch 0:0.2020-14.el7 socat.x86_64 0:1.7.3.2-2.el7 Replaced: mariadb-libs.x86_64 1:5.5.60-1.el7_5
Install MariaDB Client
Install MariaDB Client
Install MariaDB Client
[root@node1 ~]# yum -y install MariaDB-client Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: ftp.cvut.cz * extras: ftp.cvut.cz * updates: ftp.cvut.cz Package MariaDB-client-10.5.6-1.el7.centos.x86_64 already installed and latest version Nothing to do [root@node1 ~]#
Setup Galera Cluster
To setup the Galera Cluster, we have to edit the configuration for MariaDB (/etc/my.cnf.d/server.cnf) on: node2, node3 and node4. That configuration is used only for testing, for production, there will be WAY MORE configuration parameters:
Galera Configuration
[galera] # Mandatory settings wsrep_on=ON wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_address="gcomm://192.168.10.102,192.168.10.103,192.168.10.104" binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2
The description of these parameters you can check below:
- wsrep_on – This is a session level flag to indicate of the operations we are performing will be replicated, much like how the sql_log_bin setting controls if statements are written to the binlog when using MariaDB Replication. The default of this is ON, but we set it anyway, to be safe.
- wsrep_provider – This points to the location of the Galera library. Although MariaDB is set up to use Galera from scratch, you still have to point to the Galera library. This is installed as part of the MariaDB-Server installation above.
- wsrep_cluster_address – This is where we define the nodes in the cluster, in general you don’t need to list all nodes and new nodes can be added later to a running cluster, but in this case we know what the cluster looks like so we set it up here.
- binlog_format – Although the binlog, in terms of the actual binlog files, isn’t used by Galera, the binlog facility is, and for Galera to work you have to run with row format in the binlog.
- default_storage_engine – Again, this is the default value, but just to be safe let’s set up MariaDB Server to explicitly use the InnoDB Storage Engine, which is the only engine supported by Galera.
- innodb_autoinc_lock_mode – This setting defines how the InnoDB Storage Engine generates values for auto_increment columns. Using mode 2 here is very important for Galera to work properly. In short, mode 2 cause much less locking during auto_increment generation and hence doesn’t interfere with other locking. Values other than 2 can cause deadlocking and other issues with Galera.
After that we can finally bootstrat the cluster ONLY on the first node:
Bootstrap the cluter
[root@node2 log]# galera_new_cluster [root@node2 log]# ps -ef | grep mysql mysql 24722 1 2 17:12 ? 00:00:00 /usr/sbin/mariadbd --wsrep-new-cluster --wsrep_start_position=75f4c5af-1488-11eb-9f9a-5b2d1b1d312a:2 root 24749 18744 0 17:12 pts/0 00:00:00 grep --color=auto mysql [root@node2 log]# ps -ef ^C [root@node2 log]# [root@node2 log]# mysql -uroot Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 7 Server version: 10.5.6-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show global status like 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 1 | +--------------------+-------+
As you can see we have ONLY one member on the first node. Once that is done start the mysql on the other nodes:
Start the MariaDB on the other Nodes
[root@node3 ~]# service mariadb start
Redirecting to /bin/systemctl start mariadb.service
[root@node3 ~]#
[root@node4 ~]# service mariadb start
Redirecting to /bin/systemctl start mariadb.service
[root@node4 ~]# service mariadb status
Redirecting to /bin/systemctl status mariadb.service
● mariadb.service - MariaDB 10.5.6 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Thu 2020-10-22 17:13:11 UTC; 5s ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Process: 23170 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 22993 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 22991 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 23042 (mariadbd)
Status: "Taking your SQL requests now..."
CGroup: /system.slice/mariadb.service
└─23042 /usr/sbin/mariadbd --wsrep_start_position=00000000-0000-0000-0000-000000000000:2
Oct 22 17:13:11 node4 mariadbd[23042]: 2020-10-22 17:13:11 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 5)
Oct 22 17:13:11 node4 mariadbd[23042]: 2020-10-22 17:13:11 2 [Note] WSREP: Server node4 synced with group
Oct 22 17:13:11 node4 mariadbd[23042]: 2020-10-22 17:13:11 2 [Note] WSREP: Server status change joined -> synced
Oct 22 17:13:11 node4 mariadbd[23042]: 2020-10-22 17:13:11 2 [Note] WSREP: Synchronized with group, ready for connections
Oct 22 17:13:11 node4 mariadbd[23042]: 2020-10-22 17:13:11 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
Oct 22 17:13:11 node4 mariadbd[23042]: 2020-10-22 17:13:11 0 [Note] Reading of all Master_info entries succeeded
Oct 22 17:13:11 node4 mariadbd[23042]: 2020-10-22 17:13:11 0 [Note] Added new Master_info '' to hash table
Oct 22 17:13:11 node4 mariadbd[23042]: 2020-10-22 17:13:11 0 [Note] /usr/sbin/mariadbd: ready for connections.
Oct 22 17:13:11 node4 mariadbd[23042]: Version: '10.5.6-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
Oct 22 17:13:11 node4 systemd[1]: Started MariaDB 10.5.6 database server.
After that, we can see that the members of the cluster increased:
Cluster members
MariaDB [(none)]> show global status like 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 1 row in set (0.000 sec) MariaDB [(none)]>
Setup Users for MaxScale
First we need to set up a user that MariaDB MaxScale use to attach to the cluster to get authentication data. On 192.168.10.182, using the MariaDB command line as the database root user: