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]
type=service
router=readwritesplit
servers=dbserv1, dbserv2, dbserv3
user=maxscale_usr
passwd=96F99AA1315BDC3604B006F427DD9484

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 0.0.0.0 6003

Define nodes galera cluster

root@venus:~# maxadmin create server db-serv1 10.10.100.20 3306
root@venus:~# maxadmin create server db-serv2 10.10.100.21 3306
root@venus:~# maxadmin create server db-serv3 10.10.100.22 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

[maxcache]
type=filter
module=cache
storage=storage_inmemory
soft_ttl=180
hard_ttl=180
max_resultset_rows=0
max_count=0
max_size=100Mi
max_resultset_size=128Mi
cached_data=shared
selects=assume_cacheable

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

[rwsplit-service]
type=service
user=maxscale_usr
password=E047AEF73A772FD54D14478FD6B3CD0B
enable_root_user=false
max_retry_interval=3600
max_connections=0
connection_timeout=0
auth_all_servers=false
strip_db_esc=true
localhost_match_wildcard_host=true
log_auth_warnings=true
retry_on_failure=true
servers=db-serv1,db-serv2,db-serv3
filters=maxcache

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 [0.0.0.0]: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[10.10.100.20:3306]: new_slave. [Running] -> [Slave, Synced, Running]
Jan 26 18:28:00 MAXSCALE maxscale[15620]: Server changed state: db-serv2[10.10.100.21:3306]: new_slave. [Running] -> [Slave, Synced, Running]
Jan 26 18:28:00 MAXSCALE maxscale[15620]: Server changed state: db-serv3[10.10.100.22:3306]: 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/libMySQLBackend.so

root@venus:~# maxadmin list services
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
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
db-serv2           | 10.10.100.20    |  3306 |           0 | Slave, Synced, Running
db-serv1           | 10.10.100.21    |  3306 |           0 | Slave, Synced, Running
db-serv3           | 10.10.100.22     |  3306 |           0 | Master, Synced, Running
-------------------+-----------------+-------+-------------+--------------------

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

Setup Maxscale Debian Stretch (9.x)

Scenario

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

Install maxscale

root@venus:~# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | 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/gpg.1.sh --keyserver hkp://keyserver.ubuntu.com:80 --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 http://deb.debian.org/debian stretch InRelease
Get:2 http://deb.debian.org/debian stretch-updates InRelease [91.0 kB]
Hit:3 http://deb.debian.org/debian stretch Release
Hit:4 http://mariadb.biz.net.id/repo/10.1/debian stretch InRelease
Get:5 http://downloads.mariadb.com/MariaDB/mariadb-10.2/repo/debian stretch InRelease [3872 B]
Get:6 http://security.debian.org stretch/updates InRelease [63.0 kB]
Ign:8 http://downloads.mariadb.com/MaxScale/2.1/debian stretch InRelease
Get:9 http://security.debian.org stretch/updates/main amd64 Packages [333 kB]
Get:10 http://downloads.mariadb.com/Tools/debian stretch InRelease [2136 B]
Get:11 http://downloads.mariadb.com/MaxScale/2.1/debian stretch Release [2836 B]
Get:12 http://downloads.mariadb.com/MariaDB/mariadb-10.2/repo/debian stretch/main amd64 Packages [12.3 kB]
Get:13 http://downloads.mariadb.com/Tools/debian stretch/main amd64 Packages [1504 B]
Get:14 http://downloads.mariadb.com/MaxScale/2.1/debian stretch Release.gpg [819 B]
Get:15 http://downloads.mariadb.com/MaxScale/2.1/debian 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';
MariaDB [(none)]> FLUSH PRIVILEGES;

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
[maxscale]
threads=4

[rwsplit-service]
type=service
router=readwritesplit
user=maxscale
passwd=C5E5056B10F1C6D83EACEE213753D8DA

[CLI]
type=service
router=cli

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

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

reference
reference

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:


[Service]
ProtectHome=false

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)
https://mariadb.com/kb/en/library/systemd/
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

source