MaxScale 2.2.X setup (MariaDBMon+ReadWriteSplit Router)

MAXSCALE_SETUP_RWSPLIT_MARIADBMON

#: This gist is supposed to be used when someone else intends to have a simple Maxscale 2.2.X++ setup
#: considering a replication cluster. By replication cluster I mean one master and X slaves; in the 
#: case of this gist, I'm using just one slave, but, you can add as many as you want to add more to the
#: below gist. You will notice that Maxscale after setup will be configured using dynamic commands and
#: that is the place you will adjust your servers to be created an added to the service+monitor that
#: will be created. 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

#: after set up, checking version/setup
[root@box01 ~]# maxscale --version-full
MaxScale 2.2.1 - eda82881619388a3512d6cfcbcf9ad83ea930339
Source:        maxscale-2.2.1-tt4
CMake flags:   -DBUILD_TESTS=N -DBUILD_MMMON=Y -DBUILD_CDC=Y -DPACKAGE=Y -DDISTRIB_SUFFIX=centos.7
Jenkins build: jenkins-build-19207

###################### 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.%' IDENTIFIED BY '6HjR3YStnuXuzN4wHQtfMWKcMK4buQFg';
  GRANT SELECT ON mysql.user TO 'maxusr'@'10.%';
  GRANT SELECT ON mysql.db TO 'maxusr'@'10.%';
  GRANT SELECT ON mysql.tables_priv TO 'maxusr'@'10.%';
  GRANT SHOW DATABASES ON *.* TO 'maxusr'@'10.%';
  GRANT SELECT ON mysql.roles_mapping TO maxusr@'10.%';

-- 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.%' IDENTIFIED BY 'cxERPE6rM2CRrnJRFU5hUYgpHj9VaRUG';
  GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO maxmon@'10.%';
  GRANT CREATE, SELECT, UPDATE, INSERT, DELETE ON maxscale_schema.* TO maxmon@'10.%';

-- USER (mariadb): MariaDBMon (switchover/rejoin) replication_user
-- replication_user and replication_password, this is for the MariaDBMon to 
-- configure the replication after a switchover or nodes rejoin
-- here I'm considering REPLICATION CLIENT and SLAVE as one can be using
-- the mysqlrplshow from MySQL Utilities when the replication user need that privilege
CREATE USER mariadb@'10.%' IDENTIFIED BY 'aL5U4GHHKEQsq6Qvpz5fC7wzjU2WrSaV';
  GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO mariadb@'10.%';

#: 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.

#: generating the encrypted password for maxusr
[root@mxs01 ~]# maxpasswd /var/lib/maxscale/ 6HjR3YStnuXuzN4wHQtfMWKcMK4buQFg #: maxusr
9A426725B0C8564D7D76F0C9EF68AF6614EFE5AB2AB133D0BB0776110CB318F21AC5D4049FB8E29B3001C8D2AFE92B22

#: generating the encrypted password for maxmon
[root@mxs01 ~]# maxpasswd /var/lib/maxscale/ cxERPE6rM2CRrnJRFU5hUYgpHj9VaRUG #: maxmon
A06233EAA82C3BFE4E372CEA1CAC4D346967824248F222AE8176158A55799EE0340802BA60067D33DFD0A6696713C19E

#: generating the encrypted password for the replication user mariadb
[root@mxs01 ~]# maxpasswd /var/lib/maxscale/ aL5U4GHHKEQsq6Qvpz5fC7wzjU2WrSaV #: mariadb
963F909605CA1D01ACBC5E92C9D42C79E1F2DE7A6D84B5B5CB7FD86BF1C297FC84684464184358E5F59C3E6BE3F978FF

#: adjust permissions for the .secrets file
$ chown maxscale:maxscale /var/lib/maxscale/.secrets

###########################################################################################
#: Here, if you have two Maxscale instances in HA with keepalived or Corosync/Pacemaker,
#: after creating the .secrets file, you can rsync that file for the second host and have
#: the same encrypted password hash for the users passwords.
#
#: An example would be like below:
#: instance A
#: [root@max01 ~]# ls -lh /var/lib/maxscale/.secrets
#: -r--------. 1 root root 48 Jul  5 16:17 /var/lib/maxscale/.secrets
#: [root@max01 ~]# cat /var/lib/maxscale/.secrets | md5sum
#: 5f84b7cb7915bbec493e1d055dac2722  -
#: [root@max01 ~]# maxpasswd 123
#: 472C1AFEBEE1B9466F531482F8196056
#: 
#: moving the file
#: [root@max01 ~]# rsync -av -e ssh /var/lib/maxscale/.secrets 35.171.16.59:/var/lib/maxscale/.secrets
#: sending incremental file list
#: .secrets
#
#: sent 141 bytes  received 35 bytes  352.00 bytes/sec
#: total size is 48  speedup is 0.27
#
#: instance B
#: [root@max02 ~]# ls -lh /var/lib/maxscale/.secrets
#: -r--------. 1 root root 48 Jul  5 16:17 /var/lib/maxscale/.secrets
#: [root@max02 ~]# cat /var/lib/maxscale/.secrets | md5sum
#: 5f84b7cb7915bbec493e1d055dac2722  -
#: [root@max02 ~]# maxpasswd 123
#: 472C1AFEBEE1B9466F531482F8196056
###########################################################################################

#: 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

[maxscale]
threads=auto

[replication-rwsplit-service]
type=service
router=readwritesplit
user=maxusr
password=9A426725B0C8564D7D76F0C9EF68AF6614EFE5AB2AB133D0BB0776110CB318F21AC5D4049FB8E29B3001C8D2AFE92B22
max_slave_connections=100%
max_slave_replication_lag=200
use_sql_variables_in=master
connection_keepalive=3
master_accept_reads=true
strict_multi_stmt=true
strict_sp_calls=true
master_failure_mode=fail_on_write

[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=127.0.0.1
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@box01 ~]# systemctl start maxscale.service

#: checking the current status of Maxscale
[root@box01 ~]# systemctl status maxscale.service | grep active
   Active: active (running) since Thu 2018-02-01 19:52:37 GMT; 12s ago

#: From now on, make sure you have the IP addresses of each of the server within your topology
#: With the below dynamic command, we're going to:
#:  - Create a MariaDBMon monitor to monitor the servers: https://tinyurl.com/y7932fbo
#:  - 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 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 mariadbmon

#: alter the monitor to add user and passwd for the user accessing the backends
maxadmin alter monitor replication-cluster-monitor user=maxmon password=A06233EAA82C3BFE4E372CEA1CAC4D346967824248F222AE8176158A55799EE0340802BA60067D33DFD0A6696713C19E monitor_interval=1000 replication_user=mariadb replication_password=963F909605CA1D01ACBC5E92C9D42C79E1F2DE7A6D84B5B5CB7FD86BF1C297FC84684464184358E5F59C3E6BE3F978FF failcount=5 auto_failover=true auto_rejoin=true detect_replication_lag=1

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

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

#: creating the servers
maxadmin create server box02 192.168.50.12 3306
maxadmin create server box03 192.168.50.13 3306

#: adding the servers under the monitor and service
maxadmin add server box02 replication-cluster-monitor replication-rwsplit-service
maxadmin add server box03 replication-cluster-monitor replication-rwsplit-service

#: with everything OK, you can list servers and get the current state
#: commands for simple check
[root@box01 ~]# maxadmin list monitors
----------------------------+--------------
Monitor                     | Status
----------------------------+--------------
replication-cluster-monitor | Running
---------------------+---------------------

[root@box01 ~]# maxadmin list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
box02              | 192.168.50.12   |  3306 |           0 | Master, Running
box03              | 192.168.50.13   |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------

#: confirm the creation of the listener where you really want
[root@box01 ~]# netstat -l | grep 53310
tcp        0      0 0.0.0.0:53310           0.0.0.0:*               LISTEN