Maxscale split read/write query mariadb galera cluster

Hi All..

Today I will show howto spliting query read/write using maxscale with MariaDB

OS : Ubuntu 16.04 (Xenial)
LB : Maxscale-2.1
DB : MariaDB-10.2 (Galera Cluster)

First step before you start to create configuration is generate keys to store password with encrypted use the maxkeys command to it

root@venus:~# maxkeys /var/lib/maxscale/

Once the keys have been created the maxpasswd command can be used to generate the encrypted password.

root@venus:~# maxpasswd your_password

The username and password, either encrypted or plain text, are stored in the service section using the user and passwd parameters.

[Splitter Service]
servers=dbserv1, dbserv2, dbserv3

For the setup of maxscale you can see on the previous article

Next step is define a galera monitor

root@venus:~# maxadmin create monitor galera-monitor galeramon
root@venus:~# maxadmin alter monitor galera-monitor user=maxscale_usr password=96F99AA1315BDC3604B006F427DD9484 monitor_interval=1000
root@venus:~# maxadmin restart monitor galera-monitor

Define listener for rwsplit-service

root@venus:~# maxadmin create listener rwsplit-service rwsplit-listener 6003

Define nodes galera cluster

root@venus:~# maxadmin create server db-serv1 3306
root@venus:~# maxadmin create server db-serv2 3306
root@venus:~# maxadmin create server db-serv3 3306

Add galera server to rwsplit-service

root@venus:~# maxadmin add server db-serv1 galera-monitor rwsplit-service
root@venus:~# maxadmin add server db-serv2 galera-monitor rwsplit-service
root@venus:~# maxadmin add server db-serv3 galera-monitor rwsplit-service

Add Filter Cached for query caching

Configuration file at /var/lib/maxscale/maxscale.cnf.d/maxcached.cnf. Create file maxscached.cnf and add the paragraph like bellow


Next add line ‘filters=maxcache’ to /var/lib/maxscale/maxscale.cnf.d/rwsplit-service.cnf like bellow


And the finaly you can start service maxscale

root@venus:~# systemctl start maxscale

Check the error log in /var/log/maxscale/maxscale.log

If maxscale start Success you can see status maxscale like bellow and you can use maxadmin command to confirm that MariaDB MaxScale is running and the services, listeners etc have been correctly configured.

root@venus:~#  systemctl status maxscale
● maxscale.service - MariaDB MaxScale Database Proxy
   Loaded: loaded (/lib/systemd/system/maxscale.service; disabled; vendor preset: enabled)
   Active: active (running) since Fri 2018-01-26 18:28:00 WIB; 4h 44min ago
  Process: 15618 ExecStart=/usr/bin/maxscale (code=exited, status=0/SUCCESS)
  Process: 15614 ExecStartPre=/usr/bin/install -d /var/run/maxscale -o maxscale -g maxscale (code=exited, status=0/SUCCESS)
 Main PID: 15620 (maxscale)
    Tasks: 12
   Memory: 4.9M
      CPU: 1min 34.412s
   CGroup: /system.slice/maxscale.service
           └─15620 /usr/bin/maxscale

Jan 26 18:28:00 MAXSCALE maxscale[15620]: [MySQLAuth] [rwsplit-service] Loaded 11 MySQL users for listener rwsplit-listener.
Jan 26 18:28:00 MAXSCALE maxscale[15620]: Listening for connections at []:6003 with protocol MySQL
Jan 26 18:28:00 MAXSCALE maxscale[15620]: Listening for connections at [/tmp/maxadmin.sock]:0 with protocol MaxScale Admin
Jan 26 18:28:00 MAXSCALE maxscale[15620]: Started MaxScale log flusher.
Jan 26 18:28:00 MAXSCALE maxscale[15620]: MaxScale started with 8 server threads.
Jan 26 18:28:00 MAXSCALE systemd[1]: Started MariaDB MaxScale Database Proxy.
Jan 26 18:28:00 MAXSCALE maxscale[15620]: Server changed state: db-serv1[]: new_slave. [Running] -> [Slave, Synced, Running]
Jan 26 18:28:00 MAXSCALE maxscale[15620]: Server changed state: db-serv2[]: new_slave. [Running] -> [Slave, Synced, Running]
Jan 26 18:28:00 MAXSCALE maxscale[15620]: Server changed state: db-serv3[]: new_master. [Running] -> [Master, Synced, Running]
Jan 26 18:28:05 MAXSCALE maxscale[15620]: Loaded module MySQLBackend: V2.0.0 from /usr/lib/x86_64-linux-gnu/maxscale/

root@venus:~# maxadmin list services
Service Name              | Router Module     | #Users | Total Sessions | Backend databases
rwsplit-service           | readwritesplit    |      1 |          13595 | db-serv1, db-serv2, db-serv3
CLI                       | cli               |      2 |              6 | 

root@venus:~# maxadmin list servers
Server             | Address         | Port  | Connections | Status              
db-serv2           |    |  3306 |           0 | Slave, Synced, Running
db-serv1           |    |  3306 |           0 | Slave, Synced, Running
db-serv3           |     |  3306 |           0 | Master, Synced, Running

root@venus:~# maxadmin list listeners
Name                 | Service Name        | Protocol Module    | Address         | Port  | State
rwsplit-listener     | rwsplit-service     | MySQLClient        |         |  6003 | Running
CLI Listener         | CLI                 | maxscaled          | default         |     0 | Running

Setup Maxscale Debian Stretch (9.x)


OS : Debian Stretch (9.x)
RDBMS : MariaDB-10.1 (galera cluster)
LB : maxscale-2.1

Install maxscale

root@venus:~# curl -sS | sudo bash
[info] Repository file successfully written to /etc/apt/sources.list.d/mariadb.list
[info] Adding trusted package signing keys...
Executing: /tmp/apt-key-gpghome.gG3xpXKl7J/ --keyserver hkp:// --recv-keys 0x8167EE24 0xE3C94F49 0xcbcb082a1bb943db 0xF1656F24C74CD1D8 0x135659e928c12247
gpg: key 135659E928C12247: public key "MariaDB Maxscale " imported
gpg: key F1656F24C74CD1D8: "MariaDB Signing Key " not changed
gpg: key CBCB082A1BB943DB: public key "MariaDB Package Signing Key " imported
gpg: key CE1A3DD5E3C94F49: public key "MariaDB Enterprise Signing Key " imported
gpg: key 70E4618A8167EE24: public key "MariaDBManager" imported
gpg: Total number processed: 5
gpg: imported: 4
gpg: unchanged: 1
Ign:1 stretch InRelease
Get:2 stretch-updates InRelease [91.0 kB]
Hit:3 stretch Release
Hit:4 stretch InRelease
Get:5 stretch InRelease [3872 B]
Get:6 stretch/updates InRelease [63.0 kB]
Ign:8 stretch InRelease
Get:9 stretch/updates/main amd64 Packages [333 kB]
Get:10 stretch InRelease [2136 B]
Get:11 stretch Release [2836 B]
Get:12 stretch/main amd64 Packages [12.3 kB]
Get:13 stretch/main amd64 Packages [1504 B]
Get:14 stretch Release.gpg [819 B]
Get:15 stretch/main amd64 Packages [666 B]
Fetched 511 kB in 2s (183 kB/s)
Reading package lists... Done
[info] Succeessfully added trusted package signing keys.

root@venus:~# apt-cache search maxscale
maxscale - MaxScale - The Dynamic Data Routing Platform
maxscale-devel - MaxScale plugin development headers

root@venus:~# apt-get install maxscale

Creating Database Users

1. Connect to the current master server in your galera cluster as the root user
2. Create the user, substituting the username, password and host on which maxscale runs within your environment
3. Grant select privileges on the mysql.user, mysql.db, mysql.tables_priv to maxscale user
4. Grant show database and replication client to maxscale user
5. Create the additional user for apps to connect the database from maxscale

MariaDB [(none)]> create user 'maxscale_usr'@'172.17.%' identified by '123456';
MariaDB [(none)]> grant SELECT on mysql.user to 'maxscale_usr'@'172.17.%';
MariaDB [(none)]> GRANT SELECT ON mysql.db TO 'maxscale_usr'@'172.17.%';
MariaDB [(none)]> GRANT SELECT ON mysql.tables_priv TO 'maxscale_usr'@'172.17.%';
MariaDB [(none)]> GRANT SHOW DATABASES ON *.* TO 'maxscale_usr'@'172.17.%';
MariaDB [(none)]> grant REPLICATION CLIENT on *.* to 'maxscale_usr'@'172.17.%';

MariaDB [(none)]> CREATE USER 'joe'@'172.17.0.%' IDENTIFIED BY 'secret_password';
MariaDB [(none)]> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'joe'@'172.17.0.%' IDENTIFIED BY 'secret_password';

Create Maxscale configuration file

main configuration file of maxscale at /etc/maxscale.cnf

In this version you can running maxscale with minimal configuration like bellow

root@venus:~# cat /etc/maxscale.cnf



[CLI Listener]

Start service maxscale
root@venus:~# systemctl start maxscale


Mariadb-10.2 failed to start

Hi Folk..

I have a problem with MariaDB-10.2 running under Ubuntu-16.04 with systemd, when i was build galera-cluster. The problem when i want to change the default datadir to another path “/home/mysql/data”. I got warning messeges

[Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist

and mariadb failed to start. to solve the problem i googling and got reference from mariadb and do two steps then mariadb running well, the steps are

Create file /etc/systemd/system/mariadb.service.d/homedir.conf containing the following:


and then run systemctl daemon-reload

root@solusiDB ~# systemctl daemon-reload

After that start service mariadb or run galera_new_cluster if you want to setup master bootstrap galera

root@solusiDB ~# galera_new_cluster

Check status mariadb to see error or no

root@solusiDB~# systemctl status mariadb
● mariadb.service - MariaDB 10.2.12 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─homedir.conf, migrated-from-my.cnf-settings.conf
Active: active (running) since Thu 2018-01-25 14:20:09 WIB; 2h 57min ago
Docs: man:mysqld(8)
Process: 8488 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 8485 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Process: 8126 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR ||
Process: 8121 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 8116 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Main PID: 8433 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 48
Memory: 3.2G
CPU: 1min 25.903s
CGroup: /system.slice/mariadb.service
└─8433 /usr/sbin/mysqld --wsrep-new-cluster --wsrep_start_position=7e548b33-019d-11e8-8355-a6b9578527fa:0

Jan 25 14:20:03 solusiDB systemd[1]: Starting MariaDB 10.2.12 database server...
Jan 25 14:20:08 solusiDB mysqld[8126]: WSREP: Recovered position 7e548b33-019d-11e8-8355-a6b9578527fa:0
Jan 25 14:20:08 solusiDB mysqld[8433]: 2018-01-25 14:20:08 139670425610432 [Note] /usr/sbin/mysqld (mysqld 10.2.12-MariaDB-10.2.12+maria~xenial-log) starting as process 8433 ...
Jan 25 14:20:09 solusiDB systemd[1]: Started MariaDB 10.2.12 database server.

See MariaDB seccess to start

I hope this article help you

Thank you