yum install wget unzip epel-release jq -y ; wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup ;chmod +x mariadb_es_repo_setup;  
./mariadb_es_repo_setup --token="xxxxxx" --apply --mariadb-server-version="11.8" --mariadb-maxscale-version=latest   

yum install MariaDB-server -y  ; systemctl start mariadb; systemctl enable mariadb;
yum install maxscale -y ; systemctl enable maxscale ; systemctl start maxscale ;

############ Primary + Replica - /etc/my.cnf.d/server.cnf ############
[mariadb-11.8]
server_id=1                        # db-az1=1, db-az2=2 -- MUST be unique per node
log_bin=mariadb-bin
log_slave_updates=ON
binlog_format=ROW
log_error
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_slave_enabled=ON
rpl_semi_sync_master_wait_no_slave=ON
rpl_semi_sync_master_wait_point=AFTER_SYNC
rpl_semi_sync_master_timeout=10000 
############ Primary + Replica - /etc/my.cnf.d/server.cnf ############
systemctl restart mariadb

# Create users on Primary node after binlog initiated - so it replicates to replica
CREATE USER 'maxuser'@'%' IDENTIFIED BY 'aBcd123%';
GRANT FILE, BINLOG ADMIN, CONNECTION ADMIN, SLAVE MONITOR ,REPLICATION CLIENT, REPLICATION SLAVE ADMIN, SUPER, RELOAD, PROCESS, SHOW DATABASES, EVENT, SET USER ON *.* TO 'maxuser'@'%';
-- Service user: used by the readwritesplit service for client
GRANT SELECT ON mysql.* TO 'maxuser'@'%';
GRANT SHOW DATABASES ON *.* TO 'maxuser'@'%';
GRANT READ_ONLY ADMIN ON *.* TO 'maxuser'@'%';

CREATE USER 'repl'@'%' IDENTIFIED BY 'aBcd123%';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

CREATE USER 'basic'@'%' IDENTIFIED BY 'aBcd123%';   -- replace password before prod
# GRANT SLAVE MONITOR ON *.* TO 'basic'@'%';
GRANT FILE, BINLOG ADMIN, CONNECTION ADMIN, SLAVE MONITOR ,REPLICATION CLIENT, REPLICATION SLAVE ADMIN, SUPER, RELOAD, PROCESS, SHOW DATABASES, EVENT, SET USER ON *.* TO 'basic'@'%';


CREATE USER 'admin'@'%' IDENTIFIED BY 'aBcd123%';
GRANT ALL ON *.* TO 'admin'@'%';

FLUSH PRIVILEGES;

# replica - add to the 2 node cluster
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='55.555.55.555', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='aBcd123%', MASTER_USE_GTID=slave_pos;
START SLAVE;
show slave status \G


# arbitor node
############ Arbitor - /etc/my.cnf.d/server.cnf ############
[mariadb-11.8]
server_id=3
skip-log-bin                       # arbitrator must not binlog -- required so
read_only=ON
############ Arbitor - /etc/my.cnf.d/server.cnf ############
systemctl restart mariadb

 # arbitor node - only create the 1 basic user 
CREATE USER 'basic'@'%' IDENTIFIED BY 'aBcd123%';   -- replace password before prod
GRANT SLAVE MONITOR ON *.* TO 'basic'@'%';


# Both maxscale nodes
############ /etc/maxscale.cnf ############
[maxscale]
admin_host=0.0.0.0     
admin_port=8989     
threads=auto
log_info=1

# -----------------------------------------------------------------------------
# Servers
# -----------------------------------------------------------------------------

# Update address= <with real MariaDB IP/hostname>
[db-az1]
type=server
address=11.111.11.111
port=3306
protocol=MariaDBBackend

[db-az2]
type=server
address=22.222.22.222        
port=3306
protocol=MariaDBBackend

[arbitrator-az3]
type=server
address=33.333.33.333   
port=3306
protocol=MariaDBBackend
monitoruser=basic
monitorpw=aBcd123%

# -----------------------------------------------------------------------------
# Monitor
# -----------------------------------------------------------------------------

[MyMonitor]
type=monitor
module=mariadbmon
servers=db-az1,db-az2,arbitrator-az3
servers_no_promotion=arbitrator-az3
monitor_interval=1000ms
backend_connect_timeout=3s
backend_read_timeout=3s
backend_write_timeout=3s
backend_connect_attempts=1
failcount=3
auto_failover=yes
auto_rejoin=yes
switchover_on_low_disk_space=false
enforce_read_only_servers=1
enforce_simple_topology=true
cooperative_monitoring_locks=majority_of_all
# Credentials used for db-az1/db-az2 (arbitrator uses its own monitoruser/monitorpw above)
user=maxuser
password=aBcd123%
# Credentials the monitor uses to (re)configure replication during failover/rejoin
replication_user=repl
replication_password=aBcd123%

# -----------------------------------------------------------------------------
# Service
# -----------------------------------------------------------------------------

[Read-Write-Service]
type=service
router=readwritesplit
servers=db-az1,db-az2
user=maxuser
password=aBcd123%
master_reconnection=true
master_failure_mode=fail_on_write
transaction_replay=true
transaction_replay_attempts=5

# -----------------------------------------------------------------------------
# Listener
# -----------------------------------------------------------------------------

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
address=0.0.0.0
port=3306
############ /etc/maxscale.cnf ############

systemctl restart maxscale
maxctrl list servers

# verify access
yum install MariaDB-client -y
mariadb -h 55.555.55.555  -u maxuser -paBcd123% -e "select 1 as connected"
mariadb -h 55.555.55.555  -u repl -paBcd123% -e "select 1 as connected"
mariadb -h 55.555.55.555  -u admin -paBcd123% -e "select 1 as connected"

############ /root/loop_workload.py ############
#!/usr/bin/env python3
import mariadb, random, sys, time

HOSTS = "44.444.44.444,55.55.555.555"   # comma-separated -- tried in order at connect() time only
DUR = int(sys.argv[1]) if len(sys.argv) > 1 else 120

def connect():
    return mariadb.connect(host=HOSTS, port=3306, user="admin", password="aBcd123%",
                            database="test", connect_timeout=2,
                            read_timeout=5, write_timeout=5, reconnect=True)

conn = connect()
cur = conn.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS t (id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, c INT, d VARCHAR(32))")
conn.commit()

end = time.time() + DUR
while time.time() < end:
    try:
        op = random.randint(0, 2)
        if op == 0:
            cur.execute("INSERT INTO t (a,b,c,d) VALUES (?,?,?,?)",
                        (random.randint(0, 99999), random.randint(0, 99999),
                         random.randint(0, 99999), f"x{random.randint(0, 99999)}"))
            conn.commit()
            print(f"INSERT id={cur.lastrowid}")
        else:
            cur.execute("SELECT id FROM t ORDER BY RAND() LIMIT 1")
            row = cur.fetchone()
            if row:
                rid = row[0]
                if op == 1:
                    cur.execute("UPDATE t SET a=?, d=? WHERE id=?",
                                (random.randint(0, 99999), f"u{random.randint(0, 99999)}", rid))
                    conn.commit()
                    print(f"UPDATE id={rid}")
                else:
                    cur.execute("DELETE FROM t WHERE id=?", (rid,))
                    conn.commit()
                    print(f"DELETE id={rid}")
    except mariadb.Error as e:
        print(f"ERROR: {e}", file=sys.stderr)
        try:
            conn.close()
        except Exception:
            pass
        try:
            conn = connect()
            cur = conn.cursor()
        except mariadb.Error as e2:
            print(f"RECONNECT FAILED: {e2}", file=sys.stderr)
    sys.stdout.flush()
    time.sleep(1)

conn.close()
############ /root/loop_workload.py ############
chmod +x loop_workload.py

sudo dnf install -y mariadb-connector-c-devel gcc python3-devel
pip3 install mariadb
maxctrl call command mariadbmon switchover MyMonitor db-az1
./loop_workload.py 120

# remote poll maxscale
watch -n1 'curl -s -u admin:mariadb http://44.444.44.444:8989/v1/servers | jq -r ".data[] | [.id, .attributes.state, .attributes.gtid_current_pos] | @tsv" | column -t; echo; curl -s -u admin:mariadb http://44.444.44.444:8989/v1/services/Read-Write-Service | jq -r "\"Current Connections: \" + (.data.attributes.statistics.connections|tostring)"'


watch -n1 'curl -s -u admin:mariadb http://55.55.555.555:8989/v1/servers | jq -r ".data[] | [.id, .attributes.state, .attributes.gtid_current_pos] | @tsv" | column -t; echo; curl -s -u admin:mariadb http://55.55.555.555:8989/v1/services/Read-Write-Service | jq -r "\"Current Connections: \" + (.data.attributes.statistics.connections|tostring)"'

# on arbitor node
bash loop_workload.sh 5

# Turn off AZ1 (MXS1 & MDB1)
aws ec2 describe-instances --instance-ids i-0173bd37d5fe28d18 i-0a853c6b6a9fc8b46 --query 'Reservations[].Instances[].[Tags[?Key==`Name`].Value | [0], InstanceId, State.Name]' --output table
aws ec2 stop-instances --instance-ids i-0173bd37d5fe28d18 i-0a853c6b6a9fc8b46 | jq -r '.StoppingInstances[] | [.InstanceId, .CurrentState.Name] | @tsv' | column -t
aws ec2 describe-instances --instance-ids i-0173bd37d5fe28d18 i-0a853c6b6a9fc8b46 --query 'Reservations[].Instances[].[Tags[?Key==`Name`].Value | [0], InstanceId, State.Name]' --output table
aws ec2 start-instances --instance-ids i-0173bd37d5fe28d18 i-0a853c6b6a9fc8b46 | jq -r '.StartingInstances[] | [.InstanceId, .CurrentState.Name] | @tsv' | column -t


# Turn off AZ2 (MXS2 & MDB2)
aws ec2 describe-instances --instance-ids i-040f80d82a09b8a38 i-0d9592303e1343809 --query 'Reservations[].Instances[].[InstanceId,State.Name]' --output table
aws ec2 stop-instances --instance-ids  i-040f80d82a09b8a38 i-0d9592303e1343809 | jq -r '.StoppingInstances[] | [.InstanceId, .CurrentState.Name] | @tsv' | column -t
aws ec2 describe-instances --instance-ids i-040f80d82a09b8a38 i-0d9592303e1343809 --query 'Reservations[].Instances[].[InstanceId,State.Name]' --output table
aws ec2 start-instances --instance-ids  i-040f80d82a09b8a38 i-0d9592303e1343809 | jq -r '.StartingInstances[] | [.InstanceId, .CurrentState.Name] | @tsv' | column -t