How To : Install MaxScale on CentOS

On my previous post I intalled and configure a 3 node MariaDB 10.1 cluster for my evaluation purpose
for our current project…

Let install the latest MaxScale ..

[root@lenuxbase tmp]# rpm -ivh maxscale-2.0.3-1.rhel.7.x86_64.rpm
warning: maxscale-2.0.3-1.rhel.7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 8167ee24: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:maxscale-2.0.3-1                 ################################# [100%]
[root@lenuxbase tmp]#

[root@maxscale01 ~]# cat /etc/passwd | grep max
maxscale:x:986:981::/home/maxscale:/bin/bash
[root@maxscale01 ~]#


[root@maxscale01 ~]# cat /etc/passwd | grep max
maxscale:x:986:981::/home/maxscale:/bin/false
[root@maxscale01 ~]#

[root@maxscale01 ~]# usermod maxscale -s /bin/bash
[root@maxscale01 ~]# cat /etc/passwd | grep max
maxscale:x:986:981::/home/maxscale:/bin/bash
[root@maxscale01 ~]#

From any node in our cluster, lets create our maxscale User.

[root@lnxmrdbclstr01 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.1.21-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create user 'maxscale'@'maxscale01' identified by 'maxscalepass';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]>
MariaDB [(none)]> GRANT SELECT ON mysql.db TO 'maxscale'@'maxscale01';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]>
MariaDB [(none)]> GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'maxscale01';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]>
MariaDB [(none)]> grant REPLICATION SLAVE on *.* to 'maxscale'@'maxscale01';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]>
MariaDB [(none)]>  grant REPLICATION CLIENT on *.* to 'maxscale'@'maxscale01';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>

Lets validate our newly create user.

MariaDB [(none)]> show grants for 'maxscale'@'maxscale01';
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for maxscale@maxscale01                                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'maxscale'@'maxscale01' IDENTIFIED BY PASSWORD '*E7ACDE28142BFC311A2CE191B3CB3E510A27EB60' |
| GRANT SELECT ON `mysql`.`db` TO 'maxscale'@'maxscale01'                                                                                          |
| GRANT SELECT ON `mysql`.`user` TO 'maxscale'@'maxscale01'                                                                                        |
| GRANT SELECT ON `mysql`.`tables_priv` TO 'maxscale'@'maxscale01'                                                                                 |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>

Now we need to encrypt our password so that no can read it.

-bash-4.2$ maxkeys
Generating .secrets file in /var/lib/maxscale ...
-bash-4.2$
 
-bash-4.2$ maxpasswd /var/lib/maxscale/.secrets maxscalepass
D3B8C48A6B13796367AED2CF0CABFE6A
-bash-4.2$

My Goal here is write to a single node and reads for other, this is our application requirement.

-bash-4.2$ cat /etc/maxscale.cnf
[maxscale]
threads=4

[Splitter Service]
type=service
router=readwritesplit
servers=lnxmrdbclstr01, lnxmrdbclstr02, lnxmrdbclstr03
user=maxscale
passwd=D3B8C48A6B13796367AED2CF0CABFE6A

[Splitter Listener]
type=listener
service=Splitter Service
protocol=MySQLClient
port=3306
socket=/tmp/ClusterMaster

[lnxmrdbclstr01]
type=server
address=192.168.56.101
port=3306
protocol=MySQLBackend

[lnxmrdbclstr02]
type=server
address=192.168.56.102
port=3306
protocol=MySQLBackend

[lnxmrdbclstr03]
type=server
address=192.168.56.103
port=3306
protocol=MySQLBackend


[Galera Monitor]
type=monitor
module=galeramon
disable_master_failback=1
servers=lnxmrdbclstr01, lnxmrdbclstr02, lnxmrdbclstr03
user=maxscale
passwd=D3B8C48A6B13796367AED2CF0CABFE6A

[CLI]
type=service
router=cli

[CLI Listener]
type=listener
service=CLI
protocol=maxscaled
address=localhost
port=6603
-bash-4.2$


Now start the MaxScale and Check the process.

[root@maxscale01 maxscale]# systemctl status maxscale.service
● maxscale.service - MariaDB MaxScale Database Proxy
   Loaded: loaded (/usr/lib/systemd/system/maxscale.service; disabled; vendor preset: disabled)
   Active: active (running) since Wed 2017-02-01 00:50:18 EST; 5s ago
  Process: 6833 ExecStart=/usr/bin/maxscale --user=maxscale (code=exited, status=0/SUCCESS)
  Process: 6831 ExecStartPre=/usr/bin/install -d /var/run/maxscale -o maxscale -g maxscale (code=exited, status=0/SUCCESS)
 Main PID: 6835 (maxscale)
   CGroup: /system.slice/maxscale.service
           └─6835 /usr/bin/maxscale --user=maxscale

Feb 01 00:50:18 maxscale01.bai.com maxscale[6835]: Listening connections at 0.0.0.0:3306 with protocol MySQL
Feb 01 00:50:18 maxscale01.bai.com maxscale[6835]: Listening connections at /tmp/ClusterMaster with protocol MySQL
Feb 01 00:50:18 maxscale01.bai.com maxscale[6835]: Loaded module maxscaled: V2.0.0 from /usr/lib64/maxscale/libmaxscaled.so
Feb 01 00:50:18 maxscale01.bai.com maxscale[6835]: Listening connections at localhost:6603 with protocol MaxScale Admin
Feb 01 00:50:18 maxscale01.bai.com maxscale[6835]: MaxScale started with 4 server threads.
Feb 01 00:50:18 maxscale01.bai.com systemd[1]: Started MariaDB MaxScale Database Proxy.
Feb 01 00:50:18 maxscale01.bai.com maxscale[6835]: Started MaxScale log flusher.
Feb 01 00:50:18 maxscale01.bai.com maxscale[6835]: Server changed state: lnxmrdbclstr01[192.168.56.101:3306]: new_master. [Running] -> [Master, Synced, Running]
Feb 01 00:50:18 maxscale01.bai.com maxscale[6835]: Server changed state: lnxmrdbclstr02[192.168.56.102:3306]: new_slave. [Running] -> [Slave, Synced, Running]
Feb 01 00:50:18 maxscale01.bai.com maxscale[6835]: Server changed state: lnxmrdbclstr03[192.168.56.103:3306]: new_slave. [Running] -> [Slave, Synced, Running]
[root@maxscale01 maxscale]#

Lets check the maxscale now..

-bash-4.2$ maxadmin -pmariadb list services
Services.
--------------------------+----------------------+--------+---------------
Service Name              | Router Module        | #Users | Total Sessions
--------------------------+----------------------+--------+---------------
Splitter Service          | readwritesplit       |      2 |     2
CLI                       | cli                  |      2 |     2
--------------------------+----------------------+--------+---------------

-bash-4.2$ maxadmin -pmariadb list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
lnxmrdbclstr01     | 192.168.56.101  |  3306 |           0 | Master, Synced, Running
lnxmrdbclstr02     | 192.168.56.102  |  3306 |           0 | Slave, Synced, Running
lnxmrdbclstr03     | 192.168.56.103  |  3306 |           0 | Slave, Synced, Running
-------------------+-----------------+-------+-------------+--------------------
-bash-4.2$

-bash-4.2$ maxadmin -pmariadb list listeners
Listeners.
---------------------+--------------------+-----------------+-------+--------
Service Name         | Protocol Module    | Address         | Port  | State
---------------------+--------------------+-----------------+-------+--------
Splitter Service     | MySQLClient        | *               |  3306 | Running
Splitter Service     | MySQLClient        | /tmp/ClusterMaster |     0 | Running
CLI                  | maxscaled          | localhost       |  6603 | Running
---------------------+--------------------+-----------------+-------+--------

-bash-4.2$


From any node lets login using MaxScale.

[root@lnxmrdbclstr01 ~]# mysql -u dbadmin -p -h maxscale01 -P 3306
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 6848
Server version: 10.0.0 2.0.3-maxscale MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>


MySQL [(none)]> status;
--------------
mysql  Ver 15.1 Distrib 10.1.21-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          6848
Current database:
Current user:           dbadmin@maxscale01
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MySQL
Server version:         10.0.0 2.0.3-maxscale MariaDB Server
Protocol version:       10
Connection:             maxscale01 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 1 hour 31 min 1 sec

Threads: 7  Questions: 372  Slow queries: 11  Opens: 18  Flush tables: 1  Open tables: 12  Queries per second avg: 0.068
--------------

MySQL [(none)]>



Now, from another session lets stop our node and see what happens to our existing session.

-bash-4.2$ maxadmin -pmariadb list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
lnxmrdbclstr01     | 192.168.56.101  |  3306 |           1 | Master, Synced, Running
lnxmrdbclstr02     | 192.168.56.102  |  3306 |           1 | Slave, Synced, Running
lnxmrdbclstr03     | 192.168.56.103  |  3306 |           1 | Slave, Synced, Running
-------------------+-----------------+-------+-------------+--------------------
-bash-4.2$

[root@lnxmrdbclstr01 ~]# service mysql stop
Stopping mysql (via systemctl):                            [  OK  ]
[root@lnxmrdbclstr01 ~]#


-bash-4.2$ maxadmin -pmariadb list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
lnxmrdbclstr01     | 192.168.56.101  |  3306 |           0 | Down
lnxmrdbclstr02     | 192.168.56.102  |  3306 |           0 | Master, Synced, Running
lnxmrdbclstr03     | 192.168.56.103  |  3306 |           0 | Slave, Synced, Running
-------------------+-----------------+-------+-------------+--------------------
-bash-4.2$

MySQL [(none)]> status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6844
Current database: *** NONE ***

--------------
mysql  Ver 15.1 Distrib 10.1.21-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          6844
Current database:
Current user:           dbadmin@maxscale01
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MySQL
Server version:         10.0.0 2.0.3-maxscale MariaDB Server
Protocol version:       10
Connection:             maxscale01 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 1 hour 33 min 16 sec

Threads: 8  Questions: 375  Slow queries: 3  Opens: 17  Flush tables: 1  Open tables: 11  Queries per second avg: 0.067
--------------

MySQL [(none)]>

As long as you properly stopped the MariaDB, Galera can still serve client even if only 1 node is left.

but if you shutdown abort two server at the same time, then your cluster is not accessible.

MySQL [(none)]> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 1045 (28000): failed to create new session
ERROR: Can't connect to the server

unknown [(none)]> show databases;
No connection. Trying to reconnect...
ERROR 1045 (28000): failed to create new session
ERROR: Can't connect to the server

unknown [(none)]>

-bash-4.2$ maxadmin -pmariadb list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
lnxmrdbclstr01     | 192.168.56.101  |  3306 |           0 | Down
lnxmrdbclstr02     | 192.168.56.102  |  3306 |           1 | Running
lnxmrdbclstr03     | 192.168.56.103  |  3306 |           0 | Down
-------------------+-----------------+-------+-------------+--------------------
-bash-4.2$


The good thing having maxscale is no need to setup haproxy/keepalive.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s