MAXSCALE_SETUP_RWSPLIT_GALERAMON

#: This gist is supposed to be used when someone else intends to have a simple Maxscale 2.2.X++ setup
#: considering a MariaDB Cluster. The service has a listener, here, allocated the mysql/3306 port.

###################### SETUP MARIADB OFFICIAL REPO AND MAXSCALE PACKAGES ######################

#: executing the command for the repository setup on servers
[root@box01 ~]# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
[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.

#: setup Maxscale form the repository
yum -y install maxscale

###################### CREATE USERS FOR MAXSCALE ON BACKEND DATABASE SERVERS ######################
1. MAKE SEURE USERS DON'T EXISTS PRIOR TO RUN THE BELOW COMMANDS;
2. RUN THE CREATE USER FIRST AND THE GRANT COMMANDS AFTER;
3. CHANGE THE USERNAMES IN CASE THE MENTIONED ONES ALREADY EXISTS.

#: on the backends/database servers, create the needed users
#: my servers IPs are all on the same subnet, classe A
#: then, my users with 10.% - check yours and create the below users
#: more on users creation at:
#:> https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-setting-up-mariadb-maxscale/

-- USER (maxusr): ReadWriteSplit user 
-- user for the service configuration, it's going to be part of the basic 
-- configuration file, but, encrypted with maxpasswd. Take, the same configurations
-- for the service added to the basic file, will be copied to the persistdir (var/lib/maxscale/maxscale.cnf.d)
-- with the same defined configs, so, if you mistake the password, you have now two locations to correct it
CREATE USER 'maxusr'@'10.136.%' IDENTIFIED BY '6HjR3YStnuXuzN4wHQtfMWKcMK4buQFg';
  GRANT SELECT ON mysql.user TO 'maxusr'@'10.136.%';
  GRANT SELECT ON mysql.db TO 'maxusr'@'10.136.%';
  GRANT SELECT ON mysql.tables_priv TO 'maxusr'@'10.136.%';
  GRANT SHOW DATABASES ON *.* TO 'maxusr'@'10.136.%';
  GRANT SELECT ON mysql.roles_mapping TO maxusr@'10.136.%';

-- USER (maxmon): MariaDBMon user
-- user created for the monitor MariaDBMon, to pull state, 
-- do the failover, switchover and rejoin servers part of existing clusters
CREATE USER maxmon@'10.136.%' IDENTIFIED BY 'cxERPE6rM2CRrnJRFU5hUYgpHj9VaRUG';
  GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO maxmon@'10.136.%';
  GRANT CREATE, SELECT, UPDATE, INSERT, DELETE ON maxscale_schema.* TO maxmon@'10.136.%';

#: flush privileges on the main master
box01 [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

###################### GETTING ENCRYPTED VERSION OF USER PASSWORDS ######################

#: after having the users created, make sure you run the maxkeys to create the .secrets
#: for creating the encrypted version of the users passwords - best practices is not use
#: clear text passwords on the Maxscale configuration files we're going to create soon

#: run maxkeys to create an encryption keys to so create an encrypted version of the user's passwords
#: the encrypted version of the users maxusr, maxmon and mariadb will be created with maxpasswd
[root@box01 ~]# maxkeys 
Generating .secrets file in /var/lib/maxscale.

[root@mxs01 ~]# maxpasswd 6HjR3YStnuXuzN4wHQtfMWKcMK4buQFg #: maxusr
705F44116091BA3AA20FD12E01375E9B294452FC42A9B350A79F95200D326AD0D265EECF15B0B65E58416D649CF6D676

[root@mxs01 ~]# maxpasswd cxERPE6rM2CRrnJRFU5hUYgpHj9VaRUG #: maxmon
93F6F7B39EE521F60153731DC160DE11C3C3304F655ABD5D4BAC3F1F48EACE1293B57DE81A4484B9508454E7658D1C46

###########################################################################################

#: by now, we don't have users created on backends and encrypted passwords
#: we set up maxscale package and now we will need to put together what's called
#: the basic configuration - /etc/maxscale.cnf
#: don't mistake the password for the rwsplit service defintion as it's going to be 
#: duplicate soon

[root@mxs01 ~]# cat /etc/maxscale.cnf
[maxscale]
threads=auto
log_augmentation=1
ms_timestamp=1
syslog=1

[rwsplit-service]
type=service
router=readwritesplit
user=maxusr
password=705F44116091BA3AA20FD12E01375E9B294452FC42A9B350A79F95200D326AD0D265EECF15B0B65E58416D649CF6D676
connection_timeout=30

[CLI]
type=service
router=cli

[CLI-Unix-Listener]
type=listener
service=CLI
protocol=maxscaled
socket=default

[CLI-Inet-Listener]
type=listener
service=CLI
protocol=maxscaled
address=0.0.0.0
port=6603

#: enable Maxscale to start at the boot time
[root@box01 ~]# systemctl enable maxscale.service
Created symlink from /etc/systemd/system/multi-user.target.wants/maxscale.service to /usr/lib/systemd/system/maxscale.service.

#: check if it is enabled
[root@box01 ~]# systemctl is-enabled maxscale.service
enabled

#: starting Maxscale
[root@mxs01 ~]# systemctl start maxscale.service
[root@mxs01 ~]# systemctl status maxscale.service | grep active
   Active: active (running) since Sun 2018-10-28 21:47:22 UTC; 2min 13s ago

#: From now on, make sure you have the IP addresses of each of the server within your topology
#: With the below dynamic commands, we're going to:
#:  - Create a GaleraMon monitor to monitor the servers: https://tinyurl.com/y9fvvu9u
#:  - Create a listener for the service "replication-rwsplit-service" already loaded with the basic config file (maxscale.cnf)
#:  - Create each of the servers parts of the MariaDB Cluster's topology you are going to have on Maxscale
#:  - Add created servers to the monitor (replication-cluster-monitor) and the service (replication-rwsplit-service)
#: Additionally, to have a real sense of what's going on after entering each of the below commands, tail the Maxscale log file
#: as it's clear enough to let you know what's going on.

#: creating the monitor
maxadmin create monitor replication-cluster-monitor galeramon

#: alter the monitor to add user and passwd for the user 
#: accessing the backends
maxadmin alter monitor replication-cluster-monitor user=maxmon password=93F6F7B39EE521F60153731DC160DE11C3C3304F655ABD5D4BAC3F1F48EACE1293B57DE81A4484B9508454E7658D1C46 \
monitor_interval=1000 available_when_donor=true use_priority=true 

#: restart the monitor we created
maxadmin restart monitor replication-cluster-monitor

#: create the listener on port 3306
maxadmin create listener rwsplit-service replication-rwsplit-listener 0.0.0.0 3306

#: creating the servers
maxadmin create server n01 10.136.10.212   3306
maxadmin create server n02 10.136.110.173  3306
maxadmin create server n03 10.136.110.213  3306

#: adding the servers under the monitor and service
maxadmin add server n01 replication-cluster-monitor rwsplit-service
maxadmin add server n02 replication-cluster-monitor rwsplit-service
maxadmin add server n03 replication-cluster-monitor rwsplit-service

#: setting node's priorities
maxadmin alter server n01 priority=100
maxadmin alter server n02 priority=200
maxadmin alter server n03 priority=300

#: list servers to check if it's like below:
[root@mxs01 ~]# maxadmin list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
n01                | 10.136.10.212   |  3306 |           0 | Master, Synced, Running
n02                | 10.136.110.173  |  3306 |           0 | Slave, Synced, Running
n03                | 10.136.110.213  |  3306 |           0 | Slave, Synced, Running
-------------------+-----------------+-------+-------------+--------------------