Tag: MariaDB

Install Zabbix with MariaDB PHP7 HTTPD and on Centos7

When it comes to monitoring, one of the famous web application for monitoring is Zabbix. In this article, we will see the basic installation and configuration of a Zabbix machine on a CentOS7. Zabbix is an open-source monitoring software tool for diverse IT components, including networks, servers, virtual machines (VMs) and cloud services. Zabbix provides monitoring metrics, among others network utilization, CPU load and disk space consumption. It works as a Client/Server model.

Always check the official documentation for installation. The machine has been deployed on a Virtual Box machine with the following configurations:

  • 2048 MB RAM
  • 1 CPU
  • 10GB storage
  • hostname as zabbixserver zabbixserver.local
  • IP Address 192.168.0.30

1. After deploying your machine, always make sure it is up-to-date and begin by installing a web server. I chose Apache httpd which is pretty famous and can be installed pretty easily. Also, consider installing the Epel Repository. After installing the Apache httpd, start the service and set it on auto-restart mode.

yum install epel-release -y
yum install httpd -y
systemctl enable httpd
systemctl start httpd

2. We also need to install PHP. The latest PHP7 can be easily installed through a repository. Also consider other PHP packages that will connect with the database, providing the PHP CLI, MOD PHP for Apache, etc..

rpm -Uvh https://mirror.webtatic.com/yum/el7/webtatic-release.rpm
yum install php72w mod_php72w php72w-common php72w-cli php72w-xml php72w-pear php72w-devel php72w-gd php72w-mysql php72w-mbstring php72w-bcmath 

3. Then, tweak the PHP configuration file (/etc/php.ini) as follows:

max_execution_time = 700
max_input_time = 700
memory_limit = 512M
post_max_size = 64M
upload_max_size = 16M
date.timezone = US/Eastern

Note that: the configuration depends on your location as well as on the sizing of the machines.

4. Let’s now install MariaDB:

yum install mariadb-server -y
systemctl start mariadb
systemctl enable mariadb

5. Configure MariaDB by launching the following command and follow the instruction as shown in the screenshot below:

mysql_secure_installation

6. To access the database you need to launch the command mysql -u root -p. However, you can also define the password in /etc/my.cnf.d/client.cnf under the [client] header enter the password as follows:

password = xxxxx

In this way, you can just launch the mysql command to log in directly to the database.

Consider also to make the database listen-only locally as we are deploying the Zabbix server to interact with the database locally. For that, you need to modify the file /etc/my.cnf and under the [mysqld] header enter the following parameter:

bind-address=127.0.0.1

7. Now, its time to create the database, assign the passwords, and privileges. Connect on the MariaDB database:

create database zabbix_server;
grant all privileges on zabbix_server.* to [email protected]'localhost' identified by 'zabbixpassword';
flush privileges;
quit

8. We will now install the Zabbix Server. I got the repository on the official Zabbix website.

rpm -Uvh https://repo.zabbix.com/zabbix/4.4/rhel/7/x86_64/zabbix-release-4.4-1.el7.noarch.rpm
yum install zabbix-get zabbix-server-mysql zabbix-web-mysql zabbix-agent -y

Note that, we installed both the agent and server on the Zabbix server.

9. We will now configure the Zabbix database by unpacking the tables and the schema in the database that has been created at step 7:

zcat /usr/share/doc/zabbix-server-mysql-4.4.0/create.sql.gz | mysql zabbix_server

If your Zabbix server version is different, find the correct directory.

10. Modify the Zabbix server configuration as follows at /etc/zabbix/zabbix_server.conf:

DBName=zabbix_server
DBUser=zabbixuser
DBPassword=zabbixpassword
DBHost=localhost

11. Restart and Enable the Zabbix Server:

systemctl start zabbix-server
systemctl enable zabbix-server

12. Modify the Zabbix client configuration as follows at /etc/zabbix/zabbix_agentd.conf:

Server=127.0.0.1
ServerActive=127.0.0.1
Hostname=zabbixserver

13. Restart and Enable the Zabbix Agent:

systemctl start zabbix-agent
systemctl enable zabbix-agent

14. Consider restarting all the services:

systemctl restart zabbix-agent
systemctl restart zabbix-server
systemctl restart MariaDB
systemctl restart httpd

15. Add the following firewall rules

firewall-cmd --add-service={http,https} --permanent
firewall-cmd --add-port={10050/tcp,10051/tcp} --permanent
firewall-cmd --reload

For more information about Firewalld, visit the article 35 commands to understand Firewalld in RHEL7 environment.

16. At this stage, if you try to access the following link, it should be accessible:

http://192.168.0.30/zabbix/setup.php

17. Follow the steps and login on your Zabbix machine with username admin and password zabbix.

Setting up a basic mail server with Postfix, Dovecot and MariaDB

Setting up a mail server is very simple if you understand the basic concept. In this blog post, I’m going to focus on the installation of a basic mail server using Postfix (MTA), Dovecot(MDA), and MariaDB. I will explain it stepwise and move along the basics too on this blog post. Also, consider having a VPS or any server with a public IP address ready for the mail server. Some guys for testing purpose, try to hide their dynamic public address using other tools over the internet. I guess you must be ready by now. I am also using an RHEL6 machine for this installation. The concept remains the same in case you want to install on an RHEL7/8 or Ubuntu server machine. Let’s see what are some tools and prerequisites needed for the installation:

Photo credits: postfix.org
Photo credits: postfix.org
  • DNS record for your mail server.
  • Some Firewall rules to be allowed.
  • Postfix (MTA) – A Mail Transfer Agent that permits you to route and delivers electronic mails. Postfix is both an SMTP server and an SMTP agent.
  • Dovecot (MDA) – A Mail Delivery Agent that primarily used as a mail storage server. It is a secure IMAP and POP3 server. It can also act as a mail proxy server.
  • MariaDB – A database server where you will store the users, domains, and aliases.

Now, in the real world, this is not the case as we need other accessories to enhance security, robustness, and integrity. Implementation of Dovecot with MariaDB w/ SASL interconnection for the mail server. DKIM, DANE, SPF, and DMARC are other accessories that need to be used. I will get in detail about those terms in future articles. In this article, I will focus on a classic basic mail server.

Adding the DNS record

1. You will need to add an ‘A’ DNS record, followed by an ‘MX’ record. I blurred the IP Address here for security purposes.

Some Firewall rules here

2. You will also need to allow IMAP (143) and SMTP(25) on the machine

iptables -I INPUT -p tcp -s 100.100.100.100 --dport 143 -j ACCEPT
iptables -I INPUT -p tcp -s 100.100.100.100 --dport 25 -j ACCEPT

3.  SSH on your server, add an entry in your /etc/hosts file. Example:

100.100.100.100 mail.tunnelix.com

Postfix configuration and installation

4. Install the Postfix using the following command:

yum install postfix

5. Now, the configuration to modify at the /etc/postfix/main.cf are as follows:

queue_directory = /var/spool/postfix
command_directory = /usr/sbin
daemon_directory = /usr/libexec/postfix
data_directory = /var/lib/postfix
mail_owner = postfix
myhostname = mail.tunnelix.com
mydomain = tunnelix.com
myorigin = $mydomain
inet_interfaces = all
inet_protocols = all
mydestination = $myhostname, localhost.$mydomain, localhost, $mydomain
unknown_local_recipient_reject_code = 550
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases
home_mailbox = Maildir/
mail_spool_directory = /var/spool/mail
debug_peer_level = 2
debugger_command =
         PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
         ddd $daemon_directory/$process_name $process_id & sleep 5
sendmail_path = /usr/sbin/sendmail.postfix
newaliases_path = /usr/bin/newaliases.postfix
mailq_path = /usr/bin/mailq.postfix
setgid_group = postdrop
html_directory = no

6. Launch the following command:

postfix reload && /etc/init.d/postfix restart

7. A netstat -ntpl should show port 25 is listening on all IP Addresses.

8. A telnet mail.tunnelix.com should prompt you the following:

Notice the ESMTP Postfix after doing the telnet which means that the Postfix server is up.

9. At this level, you should able to send yourself an e-mail from your Gmail which proves that your Postfix is working pretty fine.

10. The mailbox is located in the directory /Maildir. Let’s say you have a user called ‘Tom’ and you have sent the mail to [email protected], then you should get the mail in /home/tom/Maildir.

Dovecot installation and configuration

11. Perform the installation of the dovecot package and its dependencies:

yum install dovecot

12. Edit the /etc/dovecot/dovecot.conf file and set up the following parameter:

listen = *,

13. Edit the /etc/dovecot/conf.d/10-auth.conf and set up the following parameter:

disable_plaintext_auth = no

auth_mechanisms = plain login

14. Edit the /etc/dovecot/conf.d/10-mail.conf and set up the following parameter:

mail_location = mbox:~/mail:INBOX=/var/spool/mail/%u

mail_privileged_group = mail

15. Okay, at this stage, you should see that the dovecot service is running and telnet localhost on port 143 should show you ‘Dovecot Ready’.

Testing your mail system

16. You can test your outgoing mail as follows:

echo “This is a test” | sudo mail -s “This is a test” [email protected] -aFrom:[email protected]

17. For incoming mail check in the following directory directory:

/var/spool/mail/vhosts

Tips:

  • Some terms to grasp are important to understand the basics behind mail transmission/reception.
    1. MUA (Mail User Agent – A software used for mail message retrieval, commonly known as an email client, such as mutt, Evolution, and Thunderbird ),
    2. MTA (Mail Transfer Agent is a software that transfers mail from one device to another using  SMTP.
    3. MDA (Mail Delivery Agent is another software component that helps with the delivery of email.
Credits: ccm.net

Note: This type of Mail configuration is for Learning purposes only. Do not apply to the production environment without considering security implications. In future articles, I will explain an example of how to secure your mail using DANE, DMARC, SPK, and DKIM.

Attending AWSome day online conference 2019

The AWSome day was a free online Conference and a training event sponsor by Intel that will provide a step-by-step introduction to the core AWS (Amazon Web Services) services. Its free and everyone can attend. It was scheduled on 26 March 2019 online. The agenda covered broad topics such as AWS Cloud Concepts, AWS Core Services, AWS Security, AWS Architecting and AWS Pricing and Support. It’s pretty interesting for IT manager, system engineers, system administrators, and architects who are eager to learn more about cloud computing and how to get started on the AWS cloud. I do have some experience in managing AWS servers and even host my own server. However, I registered for the free training to refresh my knowledge and get more exposure such as the AWS pricing which I am not aware at all. Another interesting thing is that you will receive a certificate of attendance and you received 25 USD of AWS credits. Pretty cool right?

Right from the beginning, I knew this was something interesting. I encountered a minor problem whilst signing in. I had to send a mail to support and it was resolved immediately. Once connected to the lobby, it was pretty easy to attend and follow the online conference. After some minutes, Steven Bryen, head in the AWS Cloud delivered the keynote speech.

There was also an online challenge and I score 25,821 on the Trivia Leaderboard.

On the “Ask an Expert” tab, I was mostly interested in Man on the Side Attack – MOTS attack. They referred me to the WAF section on AWS. Another interesting link is the whitepaper of the AWS Overview of Security guidelines. AWS also offers comprehensive security across all the layers, SSL, DDoS, Firewall, HSM and Networking. I also shoot some question on Metric and Monitoring on application level such as on MariaDB. I discovered about the RDS performance insight. For applications on EC2, Containers, and Lamda, X-Ray looks very promising. Apart from virtualization, its good to note that AWS also provides containerization services.

The event was pretty enriching. The panel on the question area knows well their subject. I discovered a lot by participating in the AWSomeDay. I’m looking forward to AWS certifications in the near future.

Securing MySQL traffic with Stunnel in a jail environment on CentOS

Stunnel is a program by Michal Trojnara that allows you to encrypt arbitrary TCP connections inside SSL. Stunnel can also allow you to secure non-SSL aware daemons and protocols (like POP, IMAP, LDAP, etc) by having Stunnel provide the encryption, requiring no changes to the daemon’s code. It is a proxy designed to add TLS encryption functionality to existing clients and servers without any changes in the programs’ code. Its architecture is optimized for security, portability, and scalability (including load-balancing), making it suitable for large deployments. – Stunnel.org

The concept that lies behind Stunnel is about the encryption methodology that is used when the client is sending a message to a server using a secure tunnel. In this article, we will focus on using MySQL alongside Stunnel. MariaDB Client will access the MariaDB server database using the Stunnel for more security and robustness.



Photo Credits: danscourses.com
Photo Credits: danscourses.com

I will demonstrate the installation and configuration using the CentOS distribution which is on my Virtual Box lab environment. I created two CentOS 7 virtual machines with hostname as stunnelserver and stunnelclient. We will tunnel the MySQL traffic via Stunnel. You can apply the same concept for SSH, Telnet, POP, IMAP or any TCP connection.

The two machines created are as follows:

  1. stunnelserver : 192.168.100.17 – Used as the Server
  2. stunnelclient : 192.168.100.18 – Used as the Client

Basic package installation and configuration on both servers

1. Install the Stunnel and OpenSSL package on both the client and the server.

yum install stunnel openssl -y

2. As we will be using Stunnel over MariaDB, you can use the MariaDB repository tools to get the links to download the repository. Make sure you have the MariaDB-client package installed on the stunnelclient which will be used as client to connect to the server. Also, install both packages on the stunnelserver. The commands to install the MariaDB packages are as follows:

sudo yum install MariaDB-server MariaDB-client

3. For more information about installations of MariaDB, Galera etc, refer to these links:

MariaDB Galera Clustering

MariaDB Master/Master installation

MariaDB Master/Slave installation


Configuration to be carried out on the stunnelserver (192.168.100.17)

 

 

4. Once you have all the packages installed, it’s time to create your privatekey.pem. Then, use the private key to create the certificate.pem. Whilst creating the certificate.pem, it will prompt you to enter some details. Feel free to fill it.

openssl genrsa -out privatekey.pem 2048

openssl req -new -x509 -days 365 -key privatekey.pem -out certificate.pem

5. Now comes the most interesting part to configure the stunnel.conf file by tunnelling it to the MySQL port on the stunnelserver. I observed that the package by default does not come with a stunnel.conf or even a Init script after installing it from the repository. So, you can create your own Init script. Here is my /etc/stunnel/stunnel.conf on the server:

chroot = /var/run/stunnel
setuid = stunnel
setgid = stunnel
pid = /stunnel.pid
debug = 7
output = /stunnel.log
sslVersion = TLSv1
[mysql]
key = /etc/stunnel/privatekey.pem
cert = /etc/stunnel/certificate.pem
accept = 44323
connect = 127.0.0.1:3306

6. Position your privatekey.pem and certificate.pem at /etc/stunnel directory. Make sure you have the right permission (400) on the privatekey.pem.

 

7. Based upon the configuration in part 5, we will now create the /var/run/stunnel directory and assign it with user and group of stunnel:

useradd -G stunnel stunnel && mkdir /var/run/stunnel && chown stunnel:stunnel /var/run/stunnel

8.  The port 44323 is a non reserved port which I chose to tunnel the traffic from the client.

9. As we do not have the Init script by default in the package, start the service as follows:

stunnel /etc/stunnel/stunnel.conf

10. A netstat or ss command on the server will show the Stunnel listening on port 44323.

Configuration to be carried out on the stunnelclient (192.168.100.18)

11. Here is the stunnel.conf file on the stunnel client :

verify = 2
chroot = /var/run/stunnel
setuid = stunnel
setgid = stunnel
pid = /stunnel.pid
CAfile = /etc/stunnel/certificate.pem
client = yes
sslVersion = TLSv1
renegotiation = no
[mysql]
accept = 24
connect = 192.168.100.17:44323

12. Import the certificate.pem in the /etc/stunnel/ directory.

scp <user>@<ipofstunnelserver>:/etc/stunnel/certificate.pem

13. Based upon the configuration in part 11, we will now create the /var/run/stunnel directory and assign it with user and group of stunnel:

useradd -G stunnel stunnel && mkdir /var/run/stunnel && chown stunnel:stunnel /var/run/stunnel

14. You can now start the service on the client as follows:

stunnel /etc/stunnel/stunnel.conf

15. A netstat on the client will show the Stunnel listening on port 24.

16. You can now connect on the MySQL database from your client to your server through the tunnel. Example:

mysql -h 127.0.0.1 -u <Name of Database> -p -P 24



Tips:

  • When starting Stunnel, the log and the pid file will be created automatically inside the jail environment that is /var/run/stunnel.
  • You can also change the debug log level. Level is a one of the syslog level names or numbers emerged (0), alert (1), crit (2), err (3), warning (4), notice (5), info (6), or debug (7). All logs for the specified level and all levels numerically less than it will be shown. Use debug = debug or debug = 7 for greatest debugging output. The default is notice (5).
  • If you compile from source, you will have a free log rotate and Init scripts. Probably on CentOS, it’s not packaged with the script!
  • You can also verify if SSLv2 and SSLv3 have been disabled using openssl s_client -connect 127.0.0.1:44323 -ssl3 and try with -tls1 to compare.
  • For the purpose of testing, you might need to check your firewall rules and SELINUX parameters.
  • You don’t need MariaDB-Server package on the client.
  • Stunnel is running on a Jail environment. The logs and the PID described in part 5 and 11 will be found in /var/run/stunnel.
  • You can invoke stunnel from inetd. Inetd is the Unix ‘super server’ that allows you to launch a program (for example the telnet daemon) whenever a connection is established to a specified port. See the “Stunnel how’s to” for more information. The Stunnel manual can also be viewed here.

 

Introducing MariaDB MaxScale 2.0 – Webinar

Today, I attended a webinar conference on MariaDB MaxScale carried out by Roger Bodamer at 10.00 AM PDT. That was really interesting and I strongly feel the future of system and database administrators are going to be enhanced with this technology.

It started with a thorough introduction about the challenges faced by the IT industry today especially large firms to market agility, system integration, real-time insights, security, and high availability. Some basic explanations on both application (customer-centric front-end) and database(transaction focused back-end) levels were addressed. At the application level, this deal mainly with continuous delivery of the application, microservices and modular deployment whereas at database level, it lays emphasis on security, scalability, high availability and high quality data management.

“MariaDB MaxScale makes it easy to handle the scalability and availability of your database cluster, and also secure it and manage the maintenance downtime. MaxScale is a next generation database proxy that goes well beyond routing, with advanced filtering, enhanced security and authentication. It is a multi-threaded, event-driven engine, that has its main functionality provided by plugins loaded at runtime. With MaxScale’s innovative architecture you can update the data layer on scale-out architectures, without impacting application performance.” – MariaDB

Photo Credits: MariaDB MaxScale
Photo Credits: MariaDB MaxScale

There are so many features in MaxScale that have really amazed me in the security pillar such as data motion, data in use and the DB firewall. Emphasis was laid on the type of job MaxScale that can be performed compared to simple master-slaves environments. MaxScale is really good at replication when it comes on one master having hundreds of slaves which prevents load on the master which means that MaxScale can take great care of replication issues. Another feature of MaxScale is when database is used to store information, a specific route can be defined for traffic within the database itself. Keymaps also comes into a great feature to manage those issues. Schemas can be updated. The goal is high availability. Another feature is data streaming which can be controlled. I noticed a heavy emphasis on security especially when it comes to whitelisting/blacklisting at database level to reduce DDOS attack drastically at database level.

A demo was then displayed live showing some interesting features of MariaDB MaxScale. An interesting GUI web interface called MaxPanel is used to connect with the servers Master-Slave-Slave (All three instances on docker instances) which give indications about the server names, addresses, ports, connections, and status (Server running). A load was generated on of the slave server where writes were configured on the master and read only on the slaves. The tool which was used to generate the load is HammerDB. In the conf file, under the dbfw-blacklist, rules can be inserted there to prevent certain type of query for example a “select * from tablename” which if the table have too many records, this will consume heavy amount of resources. To remediate this issue, a rule can be specified to use the “where” statement. An example of such entry is rule safe_order deny no_where_clause on_queries select which means that no select to be made with a where clause. Also rule query_regex deny regex ‘*.orders*’ which is going to be applied on the table called orders.

After the demo, the questions and answers session were tossed out. This includes different architectures where plugging architectures can be used with specific protocols. Some answers still need to be cleared out as regards to MUSL compatibility with MaxScale which was pointed by me. Some days back, cyberstorm.mu have enhanced MariaDB to be MUSL compatible. That was really an interesting and educational session for MariaDB MaxScale especially for administrators. I am looking forward for MariaDB MaxScale installation soon.