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.

Monday, 4 February 2019

Install MariaDB Galera Cluster on CentOS 7

Configure MariaDB Galera Cluster on CentOS 7MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB. It is a fork of Galera Cluster, the world's most advanced, free and open source cluster engine. Currently, it only supports InnoDB storage engines.

MariaDB Galera Cluster is a true Multi-Master and Active-Active cluster. Due to it's synchronous behaviour, there will be no data lost in case of a node crash because all nodes always hold the same state.

MariaDB Galera Cluster also provides Automatic node provisioning. It means we do not have to manually backup the database and restore it on new node before adding it to Galera cluster. This features gives the additional benefit of Cloud support due to simple scale-in and scale-out operations.

In this article, we will create a two-node MariaDB Galera Cluster of MariaDB 10.3 Database on CentOS 7. However, the same steps can be used to configure a MariaDB Galera Cluster of larger size.

This article focuses on the installation of MariaDB Galera Cluster on CentOS 7 without diving into theory and definitions pertains to MariaDB. Therefore, it is recommended that you should read Getting Started with MariaDB - Second Edition by Packt Publishing to have basic understanding of MariaDB database.

System Specification:

For this article, we are using two CentOS 7 virtual machines as the Galera Cluster nodes.

Hostname: mariadb-01.example.com mariadb-02.example.com
IP Address: 192.168.116.81 /24 192.168.116.82/24
CPU: 2.4 Ghz (2 cores) 2.4 Ghz (2 cores)
Memory: 2 GB 2 GB
Operating System: CentOS 7.6 CentOS 7.6
MariaDB Version: 10.3.12 10.3.12

 

Installing MariaDB 10.3 Database Server on CentOS 7:

Connect to mariadb-01.example.com using ssh as root user.

Install MariaDB and MaxScale yum repositories.

[root@mariadb-01 ~]# 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 for all repositories.

[root@mariadb-01 ~]# yum makecache fast Loaded plugins: fastestmirror Determining fastest mirrors * base: repo.inara.pk * extras: repo.inara.pk * updates: repo.inara.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): mariadb-maxscale/7/x86_64/primary_db | 6.7 kB 00:02 (2/5): mariadb-tools/7/x86_64/primary_db | 11 kB 00:02 (3/5): mariadb-main/7/x86_64/primary_db | 50 kB 00:03 (4/5): extras/7/x86_64/primary_db | 156 kB 00:04 (5/5): updates/7/x86_64/primary_db | 1.4 MB 00:09 Metadata Cache Created

Install MariaDB 10.3 Server and Galera using yum command.

[root@mariadb-01 ~]# yum install -y mariadb-server galera ... Installed: MariaDB-compat.x86_64 0:10.3.12-1.el7.centos MariaDB-server.x86_64 0:10.3.12-1.el7.centos galera.x86_64 0:25.3.25-1.rhel7.el7.centos Dependency Installed: MariaDB-client.x86_64 0:10.3.12-1.el7.centos MariaDB-common.x86_64 0:10.3.12-1.el7.centos boost-program-options.x86_64 0:1.53.0-27.el7 lsof.x86_64 0:4.87-6.el7 perl.x86_64 4:5.16.3-294.el7_6 perl-Carp.noarch 0:1.26-244.el7 perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 perl-DBI.x86_64 0:1.627-4.el7 perl-Data-Dumper.x86_64 0:2.145-3.el7 perl-Encode.x86_64 0:2.51-7.el7 perl-Exporter.noarch 0:5.68-3.el7 perl-File-Path.noarch 0:2.09-2.el7 perl-File-Temp.noarch 0:0.23.01-3.el7 perl-Filter.x86_64 0:1.49-3.el7 perl-Getopt-Long.noarch 0:2.40-3.el7 perl-HTTP-Tiny.noarch 0:0.033-3.el7 perl-IO-Compress.noarch 0:2.061-2.el7 perl-Net-Daemon.noarch 0:0.48-5.el7 perl-PathTools.x86_64 0:3.40-5.el7 perl-PlRPC.noarch 0:0.2020-14.el7 perl-Pod-Escapes.noarch 1:1.04-294.el7_6 perl-Pod-Perldoc.noarch 0:3.20-4.el7 perl-Pod-Simple.noarch 1:3.28-4.el7 perl-Pod-Usage.noarch 0:1.63-3.el7 perl-Scalar-List-Utils.x86_64 0:1.27-248.el7 perl-Socket.x86_64 0:2.010-4.el7 perl-Storable.x86_64 0:2.45-3.el7 perl-Text-ParseWords.noarch 0:3.29-4.el7 perl-Time-HiRes.x86_64 4:1.9725-3.el7 perl-Time-Local.noarch 0:1.2300-2.el7 perl-constant.noarch 0:1.27-2.el7 perl-libs.x86_64 4:5.16.3-294.el7_6 perl-macros.x86_64 4:5.16.3-294.el7_6 perl-parent.noarch 1:0.225-244.el7 perl-podlators.noarch 0:2.5.1-3.el7 perl-threads.x86_64 0:1.87-4.el7 perl-threads-shared.x86_64 0:1.43-6.el7 rsync.x86_64 0:3.1.2-4.el7 Replaced: mariadb-libs.x86_64 1:5.5.60-1.el7_5 Complete!

Repeat above steps on all other nodes.

 

Configuring MariaDB Galera Cluster on CentOS 7:

Allow MariaDB and Galera service ports in Linux firewall.

[root@mariadb-01 ~]# firewall-cmd --permanent --add-service=mysql success [root@mariadb-01 ~]# firewall-cmd --permanent --add-port={4567,4568,4444}/tcp success [root@mariadb-01 ~]# firewall-cmd --reload success

Set SELinux to permissive mode for now, and we will enable the enforcing mode later, after creating an SELinux policy for MariaDB Galera cluster.

[root@mariadb-01 ~]# setenforce 0

Now edit MariaDB configuration file.

[root@mariadb-01 ~]# vi /etc/my.cnf.d/server.cnf

and configure galera section as follows:

[galera] # Mandatory settings wsrep_on=ON wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_address=gcomm://192.168.116.81,192.168.116.82 binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2

Perform above steps on each node.

Start Galera cluster on mariadb-01.example.com.

[root@mariadb-01 ~]# galera_new_cluster

Start MariaDB service on all other nodes.

[root@mariadb-01 ~]# systemctl start mariadb.service

If the service started successfully then, it shows that we have successfully configured our Galera cluster.

Configure MariaDB database instance on each node.

[root@mariadb-01 ~]# mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] Y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] Y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] Y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] Y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] Y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB!

Connect with MariaDB instance on any node and check wsrep_cluster_size.

[root@mariadb-01 ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 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)]> show global status like 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 2 | +--------------------+-------+ 1 row in set (0.083 sec)

wsrep_cluster_size confirms that all of our nodes are now connected in Galera cluster.

 

Create SELinux Policy for MariaDB Galera cluster:

Since, we have configured SELinux in permissive mode. Therefore, all the violations by MariaDB and Galera has been recorded in /var/log/audit/audit.log. We can use it to create a concrete SELinux policy.

Use fgrep and audit2allow commands to extract policy violations log into a text file.

[root@mariadb-01 ~]# fgrep "mysqld" /var/log/audit/audit.log | audit2allow -m MySQL_galera -o MySQL_galera.te

Compile these logs to a SELinux policy module.

[root@mariadb-01 ~]# checkmodule -M -m MySQL_galera.te -o MySQL_galera.mod checkmodule: loading policy configuration from galera.te checkmodule: policy configuration loaded checkmodule: writing binary representation (version 19) to MySQL_galera.mod

Create a package of compiled policy module.

[root@mariadb-01 ~]# semodule_package -m MySQL_galera.mod -o MySQL_galera.pp

Import this policy into SELinux.

[root@mariadb-01 ~]# semodule -i MySQL_galera.pp

Set SELinux to run in enforcing mode.

[root@mariadb-01 ~]# setenforce 1

Test SELinux is working fine by restart MariaDB service on each node.

Finally, enable the MariaDB service on all nodes.

[root@mariadb-01 ~]# systemctl enable mariadb.service

We have successfully configured a MariaDB Galera Cluster on CentOS 7. Although we have configured a two node cluster, but the same steps are good enough for configuring a MariaDB Galera cluster of larger size.

After configuring a MariaDB Galera Cluster, you should be looking next for a database proxy to perform load balancing and routing for our cluster. Therefore, it is highly recommended that you should read our next post Install MariaDB MaxScale Database Proxy on CentOS 7.

Install MariaDB Galera Cluster on CentOS 7



4 comments:

  1. at restart all nodes, all nodes sendme this error. While not restart this problem not appear


    ● mariadb.service - MariaDB 10.3.13 database server
    Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
    Drop-In: /etc/systemd/system/mariadb.service.d
    └─migrated-from-my.cnf-settings.conf
    Active: failed (Result: exit-code) since dom 2019-02-24 23:35:16 CST; 1min 14s ago
    Docs: man:mysqld(8)
    https://mariadb.com/kb/en/library/systemd/
    Process: 7308 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
    Process: 7134 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
    Process: 7120 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Main PID: 7308 (code=exited, status=1/FAILURE)
    Status: "MariaDB server is down"

    feb 24 23:35:15 dbs01 mysqld[7308]: at gcomm/src/pc.cpp:connect():158
    feb 24 23:35:15 dbs01 mysqld[7308]: 2019-02-24 23:35:15 0 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():209: Failed to open backend connection: -110 (Connection timed out)
    feb 24 23:35:15 dbs01 mysqld[7308]: 2019-02-24 23:35:15 0 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1458: Failed to open channel 'my_wsrep_cluster' at 'gcomm://dbs01,dbs02,dbs03': -110 (Connection timed out)
    feb 24 23:35:15 dbs01 mysqld[7308]: 2019-02-24 23:35:15 0 [ERROR] WSREP: gcs connect failed: Connection timed out
    feb 24 23:35:15 dbs01 mysqld[7308]: 2019-02-24 23:35:15 0 [ERROR] WSREP: wsrep::connect(gcomm://dbs01,dbs02,dbs03) failed: 7
    feb 24 23:35:15 dbs01 mysqld[7308]: 2019-02-24 23:35:15 0 [ERROR] Aborting
    feb 24 23:35:16 dbs01 systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
    feb 24 23:35:16 dbs01 systemd[1]: Failed to start MariaDB 10.3.13 database server.
    feb 24 23:35:16 dbs01 systemd[1]: Unit mariadb.service entered failed state.
    feb 24 23:35:16 dbs01 systemd[1]: mariadb.service failed.

    ReplyDelete
    Replies
    1. Go to the node that was shutdown in the last during last clean shutdown.
      Then start that node using galera_new_cluster
      Start the remaining nodes using systemctl start mariadb.

      Hopefully this will resolve your problem.
      Please let us know about the result.

      Delete
  2. What IP will be used to connect remotely?
    Is there an IP-scan, like in Oracle?
    Or do we put the IP of any node?

    ReplyDelete
    Replies
    1. Hi,
      No, there isn't any SCAN as in Oracle RAC.
      But the replication is multi-master here, therefore, you can connect to any node.
      You can also setup a MaxScale Load Balancer for this cluster.

      Delete