Differences

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

--- maria_ha_maxscale [2020/10/22 15:48] – 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 88: / Line 93: @@
 ====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:
+<Code:bash| 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
+</Code>
+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:
+<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 |}}