Author Photo

Ahmer's SysAdmin Recipes is a blog for Linux System Administrators. This blog provides recipes for Installation & Configuration of Linux, Ubuntu, Oracle Database, MySQL, Apache, Nginx, Oracle Cloud Control, FreeIPA, Kerberos & OpenLDAP, Subversion, PXE, DevOps, etc. over Red Hat Enterprise Linux (RHEL), CentOS, Ubuntu and Windows.

Please give me your feedback and help me improve this blog. Please let me know If you want me to write on a specific topic.

Friday, 8 February 2019

Install MariaDB MaxScale Database Proxy on CentOS 7

Install MariaDB MaxScale Database Proxy on CentOS 7

MaxScale is an intelligent database proxy developed by MariaDB Corporation. MaxScale is free and open source under Business Source License (BSL). MaxScale extends the high availability, load-balancing, scalability and security of MariaDB server and it simplifies the application development by decoupling it from underlying database infrastructure.

In our previous post “Install MariaDB Galera Cluster on CentOS 7”, we have configured a two node MariaDB cluster on CentOS 7 server using Galera. In this article, we will install and configure MariaDB MaxScale database proxy for our Galera cluster.

Reading Advice: Migrating to MariaDB: Toward an Open Source Database Solution

 

System Specification:

We will use the same MariaDB Galera cluster that we have configured in our previous post. Besides Galera cluster, we have also provisioned a virtual machine with following specification. This new virtual machine is used as the MaxScale database proxy for our MariaDB Galera cluster.

Hostname: maxscale.example.com
IP Address: 192.168.116.80/24
CPU: 2.4 Ghz (2 cores)
Memory: 1 GB
Operating System: CentOS 7.6

 

Install MariaDB MaxScale on CentOS 7:

Connect to arbitrary node maxscale.example.com using ssh.

Install MariaDB and MaxScale yum repositories.

[root@maxscale ~]# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo. [info] Adding trusted package signing keys... [info] Succeessfully added trusted package signing keys.

Build yum cache.

[root@maxscale ~]# yum makecache fast Loaded plugins: fastestmirror Determining fastest mirrors * base: mirrors.ges.net.pk * extras: mirrors.ges.net.pk * updates: mirrors.ges.net.pk base | 3.6 kB 00:00 extras | 3.4 kB 00:00 mariadb-main | 2.9 kB 00:00 mariadb-maxscale | 2.4 kB 00:00 mariadb-tools | 2.9 kB 00:00 updates | 3.4 kB 00:00 (1/5): extras/7/x86_64/primary_db | 156 kB 00:01 (2/5): mariadb-tools/7/x86_64/primary_db | 11 kB 00:02 (3/5): mariadb-maxscale/7/x86_64/primary_db | 6.7 kB 00:02 (4/5): mariadb-main/7/x86_64/primary_db | 50 kB 00:03 (5/5): updates/7/x86_64/primary_db | 1.4 MB 00:04 Metadata Cache Created

Install MariaDB MaxScale using yum command.

[root@maxscale ~]# yum install -y maxscale Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirrors.ges.net.pk * extras: mirrors.ges.net.pk * updates: mirrors.ges.net.pk Resolving Dependencies --> Running transaction check ---> Package maxscale.x86_64 0:2.3.3-1 will be installed --> Processing Dependency: libgnutls.so.28(GNUTLS_3_1_0)(64bit) for package: maxscale-2.3.3-1.x86_64 --> Processing Dependency: libgnutls.so.28(GNUTLS_3_0_0)(64bit) for package: maxscale-2.3.3-1.x86_64 --> Processing Dependency: libgnutls.so.28(GNUTLS_1_4)(64bit) for package: maxscale-2.3.3-1.x86_64 --> Processing Dependency: libgnutls.so.28()(64bit) for package: maxscale-2.3.3-1.x86_64 --> Running transaction check ---> Package gnutls.x86_64 0:3.3.29-8.el7 will be installed --> Processing Dependency: trousers >= 0.3.11.2 for package: gnutls-3.3.29-8.el7.x86_64 --> Processing Dependency: libnettle.so.4()(64bit) for package: gnutls-3.3.29-8.el7.x86_64 --> Processing Dependency: libhogweed.so.2()(64bit) for package: gnutls-3.3.29-8.el7.x86_64 --> Running transaction check ---> Package nettle.x86_64 0:2.7.1-8.el7 will be installed ---> Package trousers.x86_64 0:0.3.14-2.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: maxscale x86_64 2.3.3-1 mariadb-maxscale 23 M Installing for dependencies: gnutls x86_64 3.3.29-8.el7 base 680 k nettle x86_64 2.7.1-8.el7 base 327 k trousers x86_64 0.3.14-2.el7 base 289 k Transaction Summary ================================================================================ Install 1 Package (+3 Dependent packages) Total download size: 24 M Installed size: 94 M Downloading packages: (1/4): trousers-0.3.14-2.el7.x86_64.rpm | 289 kB 00:03 (2/4): nettle-2.7.1-8.el7.x86_64.rpm | 327 kB 00:04 (3/4): gnutls-3.3.29-8.el7.x86_64.rpm | 680 kB 00:04 (4/4): maxscale-2.3.3-1.centos.7.x86_64.rpm | 23 MB 01:43 -------------------------------------------------------------------------------- Total 239 kB/s | 24 MB 01:43 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : nettle-2.7.1-8.el7.x86_64 1/4 Installing : trousers-0.3.14-2.el7.x86_64 2/4 Installing : gnutls-3.3.29-8.el7.x86_64 3/4 Installing : maxscale-2.3.3-1.x86_64 4/4 Verifying : gnutls-3.3.29-8.el7.x86_64 1/4 Verifying : trousers-0.3.14-2.el7.x86_64 2/4 Verifying : maxscale-2.3.3-1.x86_64 3/4 Verifying : nettle-2.7.1-8.el7.x86_64 4/4 Installed: maxscale.x86_64 0:2.3.3-1 Dependency Installed: gnutls.x86_64 0:3.3.29-8.el7 nettle.x86_64 0:2.7.1-8.el7 trousers.x86_64 0:0.3.14-2.el7 Complete!

Now connect to an instance of MariaDB Galera Cluster and create a user for monitoring and authentication by MaxScale. (Since, our nodes have formed a cluster, therefore, we only need to execute the following commands once on any node).

[root@mariadb-01 ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 11 Server version: 10.3.12-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>

Execute following commands to create a MaxScale user with necessary privileges to perform it's function properly.

MariaDB [(none)]> create user 'maxscale'@'192.168.116.80' identified by '123'; Query OK, 0 rows affected (0.005 sec) MariaDB [(none)]> grant select on mysql.user to 'maxscale'@'192.168.116.80'; Query OK, 0 rows affected (0.041 sec) MariaDB [(none)]> grant select on mysql.db to 'maxscale'@'192.168.116.80'; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> grant select on mysql.tables_priv to 'maxscale'@'192.168.116.80'; Query OK, 0 rows affected (0.043 sec) MariaDB [(none)]> grant show databases on *.* to 'maxscale'@'192.168.116.80'; Query OK, 0 rows affected (0.003 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.004 sec)

Create a user with privileges to remotely connect from any machine. This user will be used by our application to connect to MariaDB Galera cluster.

MariaDB [(none)]> create user ahmer@'%' identified by '123'; Query OK, 0 rows affected (0.005 sec) MariaDB [(none)]> grant show databases on *.* to ahmer@'%'; Query OK, 0 rows affected (0.003 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.004 sec) MariaDB [(none)]> exit Bye

Now, connect to maxscale.example.com and configure MaxScale database proxy.

Edit MaxScale configurations.

[root@maxscale ~]# mv /etc/maxscale.cnf /etc/maxscale.cnf.old [root@maxscale ~]# vi /etc/maxscale.cnf

and add following directives. (Please refer to MaxScale Documentation for more information about MaxScale configuration parameters)

#Global MaxScale Settings [maxscale] threads=auto #Define Server Nodes [mariadb-01] type=server address=192.168.116.81 port=3306 protocol=MariaDBBackend [mariadb-02] type=server address=192.168.116.82 port=3306 protocol=MariaDBBackend #Define Monitoring Service [Galera-Monitor] type=monitor module=galeramon servers=mariadb-01,mariadb-02 user=maxscale password=123 monitor_interval=1000 #Define Galera Service [Galera-Service] type=service router=readconnroute router_options=synced servers=mariadb-01,mariadb-02 user=maxscale passwd=123 #Define Galera Listener [Galera-Listener] type=listener service=Galera-Service protocol=MariaDBClient port=4306 #Define Administration Service [MaxAdmin-Service] type=service router=cli #Define Administration Listener [MaxAdmin-Listener] type=listener service=MaxAdmin-Service protocol=maxscaled socket=default

Allow Service Port in Linux Firewall.

[root@maxscale ~]# firewall-cmd --permanent --add-port=4306/tcp success [root@maxscale ~]# firewall-cmd --reload success

Start and enable MaxScale service.

[root@maxscale ~]# systemctl start maxscale.service [root@maxscale ~]# systemctl enable maxscale.service

Make some connections from clients to Galera Cluster using ahmer user via MaxScale database proxy.

[root@client-01 ~]# mysql -h maxscale.example.com -P 4306 -u ahmer -p

Connect to maxscale.example.com using ssh, and use maxadmin command to see status of connections to MariaDB Galera Cluster.

[root@maxscale ~]# maxadmin MaxScale> list servers Servers. -------------------+-----------------+-------+-------------+-------------------- Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+-------------------- mariadb-01 | 192.168.116.81 | 3306 | 2 | Slave, Synced, Running mariadb-02 | 192.168.116.82 | 3306 | 3 | Master, Synced, Running -------------------+-----------------+-------+-------------+-------------------- MaxScale>

The above command shows the status of nodes in MariaDB Galera cluster including the active connections and replication status. You can use help command to get help on a maxadmin command or refer to maxadmin documentation for complete reference.

We have successfully install MariaDB MaxScale database proxy on CentOS 7 server.

Install MariaDB MaxScale Database Proxy on CentOS 7


YOU MIGHT ALSO LIKE:

No comments:

Post a Comment