Differences

This shows you the differences between two versions of the page.

--- maria_ha_maxscale [2020/10/22 17:14] – [Setup Galera Cluster] andonovj
+++ maria_ha_maxscale [2020/10/24 18:17] (current) – [Testing the Environment] andonovj
@@ Line 18: / Line 18: @@
   * node4
-=====Setting up=====
+=====Configuration=====
-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:
+The whole processes will go as follows:
+  - Install MariaDB and Maxscale on all nodes
+  - Configure Galera Cluster
+  - Configure Maxscale
+  - Feature tests
+====Setup Repo====
 <Code:bash|Repo Config>
 [root@node1 ~]#  curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
@@ Line 110: / Line 115: @@
   * 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:
+<Code:bash|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     |
++--------------------+-------+
+</Code>
+As you can see we have ONLY one member on the first node.
+Once that is done start the mysql on the other nodes:
+<Code:bash|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
+</Code>
+After that, we can see that the members of the cluster increased:
+<Code:bash|Cluster members>
+MariaDB [(none)]> show global status like 'wsrep_cluster_size';
++--------------------+-------+
+| Variable_name      | Value |
++--------------------+-------+
+| wsrep_cluster_size | 3     |
++--------------------+-------+
+row in set (0.000 sec)
+MariaDB [(none)]>
+</Code>
+====Prepare 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:
+<Code:bash|Create user & Grant permissions>
+[root@node2 ~]# 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)]> create user 'myuser'@'192.168.10.101' identified by 'mypwd';
+Query OK, 0 rows affected (0.023 sec)
+MariaDB [(none)]> grant select on mysql.user to 'myuser'@'192.168.10.101';
+Query OK, 0 rows affected (0.002 sec)
+MariaDB [(none)]> grant select on mysql.db to 'myuser'@'192.168.10.101';
+Query OK, 0 rows affected (0.002 sec)
+MariaDB [(none)]> grant select on mysql.tables_priv to 'myuser'@'192.168.10.101';
+Query OK, 0 rows affected (0.002 sec)
+MariaDB [(none)]> grant show databases on *.* to 'myuser'@'192.168.10.101';
+Query OK, 0 rows affected (0.002 sec)
+MariaDB [(none)]>
+</Code>
+====Setup MaxScale====
+The configuration of MaxScale is stored in: "/etc/maxscale.cnf"
+<Code:bash|Maxscale Config>
+# Globals
+[maxscale]
+threads=1
+admin_secure_gui=false                 <- We don't have SSL, we aren't so rich.
+admin_host=192.168.10.101              <- Defining NIC interface instead of localhst: 127.0.0.1
+admin_port=8989                        <- Default port.
+# Servers
+[server1]
+type=server
+address=192.168.10.102
+port=3306
+protocol=MySQLBackend
+[server2]
+type=server
+address=192.168.10.103
+port=3306
+protocol=MySQLBackend
+[server3]
+type=server
+address=192.168.10.104
+port=3306
+protocol=MySQLBackend
+# Monitoring for the servers
+[Galera-Monitor]
+type=monitor
+module=galeramon
+servers=server1,server2,server3
+user=myuser
+password=mypwd
+# Galera router service
+[Splitter-Service]
+type=service
+router=readwritesplit
+servers=server1,server2,server3
+user=myuser
+password=mypwd
+# Galera cluster listener
+[Splitter-Listener]
+type=listener
+service=Splitter-Service
+protocol=MariaDBClient
+address=192.168.10.101
+port=3306
+</Code>
+It took my REALLY LONG TIME, to figure out the configurations as they change from release to release. Like every open source software....
+After that we can start the maxscale:
+<Code:bash|Start MaxScale>
+[root@node1 maxscale]# service maxscale start
+Redirecting to /bin/systemctl start maxscale.service
+</Code>
+=====Testing the Environment====
+In our test:
+  - We will connect to the listener on the maxscale server
+  - Check the server (e.g. server 3)
+  - Shutdown the MariaDB on the server from step 2)
+  - Check the server from our connection (we should be moved)
+<Code:bash|Session on node1>
+[root@node1 maxscale]# mysql -h 192.168.10.101 -P 3306 -u myuser -p
+Enter password:
+Welcome to the MariaDB monitor.  Commands end with ; or \g.
+Your MariaDB connection id is 1
+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 variables like 'hostname';
++---------------+-------+
+| Variable_name | Value |
++---------------+-------+
+| hostname      | node3 |
++---------------+-------+
+</Code>
+Now from the session, connected on node3, shutdown the MariaDB:
+<Code:bash|Session on node 3>
+[root@node3 ~]# systemctl stop mariadb.service
+</Code>
+Once we shut down the instance, our session should be moved to running server, so let's check it.
+From the first session, without reconnected, re-query the same data.
+<Code:bash|Session on node1>
+MariaDB [(none)]> show variables like 'hostname';
++---------------+-------+
+| Variable_name | Value |
++---------------+-------+
+| hostname      | node4 |
++---------------+-------+
+row in set (0.001 sec)
+</Code>
+After, we can also connect to the GUI, which should listen on: http://192.168.10.101:8989
+===GUI===
+{{ :mariadbmaxscaleentry.jpg?600 |}}
+The default credentials are:
+Username: admin
+Password: mariadb
+{{ :mariadbmaxscaleoverview.jpg?800 |}}