Tag: MariaDB

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 : – Used as the Server
  2. stunnelclient : – 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 (



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
key = /etc/stunnel/privatekey.pem
cert = /etc/stunnel/certificate.pem
accept = 44323
connect =

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 (

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
accept = 24
connect =

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 -u <Name of Database> -p -P 24


  • 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 -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.

MySQL Binlogs Analysis for data loss

Some days back, I encountered a server where it happens that some data was altered in the database. A quick report can be generated with the mysqlbinlog command.

Photo Credits: Mysql.com
Photo Credits: Mysql.com

The MySQL bin logs contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

  • For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 18.2, “Replication Implementation”.
  • Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 8.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.

It is to be noted that by enabling My SQL bin logs, servers will tend to react more slowly though the benefits are really useful. My SQL bin logs should not be deleted straight forward with a rm -f command but rather with the command PURGE BINARY LOGS TO ‘mysql-bin.111’;

In this article i will demonstrate some command to strip out interesting information from a Binary log file. Logs that can alter information in a database are classified with the following database requests such as UPDATE, DELETE, INSERT, DELETE, REPLACE and ALTER.

1. If you want to read the whole content of a binlog, use the following command. This will comprise of all request made including selects statement.

mysqlbinlog binlog.1111

2.Let’s say you have a list of binlogs and you want to find all the ALTER carried out only for a specific database called “question”

mysqlbinlog binlog.* | grep -i -e "^alter" | grep -i -e "question" >> /tmp/alter_question.txt

3. Let’s say you want to find the date for 03/05/2016 of all alter commands carried out from the file generated from part 1

grep -i -A 3 '#160503' /tmp/alter_question.txt | less

4. If you want to extract all ALTER from a bunch of binlogs for a specific database (question)

mysqlbinlog --database=question binlog.* | grep -B 5 -i -e "^alter" >> /tmp/alter_question.txt

5. You might also want to retrieve information from a specific date and time.

mysqlbinlog --start-datetime="2016-2-02 5:00:00"--stop-datetime="2016-03-03 8:10:00" mysql-bin.000007

However, analysis of the Mysqlbin logs are pretty vast. It depends what are the information that is being needed. I also find out that the mk-query-digest is also an interesting tool to extract information and perform analysis. SEE http://linux.die.net/man/1/mk-query-digest 

Activating MariaDB Audit log

“The purpose of the MariaDB Audit Plugin is to log the server’s activity. Records about who connected to the server, what queries ran and what tables were touched can be stored to the rotating log file or sent to the local syslogd.”MariaDB. The MariaDB  Audit Plugin works for MariaDB, MySQL and Percona server.

Photo Credits: MariaDB
Photo Credits: MariaDB

Links and Basics

Let’s see how to install the MariaDB audit plugin to retrieve all activity on the database server. I am actually testing it on a MariaDB 5.5 series database. You can use a 10.1 series if you want. For MariaDB installation there are articles which i have posted some times back such as MariaDB Galera cluster installation, MariaDB and its improved security features, Master-Master replication on MariaDB and a Master-Slave replication on MariaDB. I think those articles are pretty straight forward to dive into the installation procedures. Imagine having so many users connected on a database performing so many queries. One on the way to trace those requests are through the binlogs or by activating the MariaDB audit log. Let’s see how to activate the MariaDB Audit log.

Verification and Prerequisites

After installing your Database server, get into the console and launch the following command SHOW GLOBAL VARIABLES LIKE ‘plugin_dir’ ; It should prompt you something like this:

Screenshot from 2016-05-30 08-26-50

If you are using a 32-bit system if would be found in /usr/lib/mysql/plugin. Get into that directory and checked if you got a file called server_audit.so By default, on new MariaDB, its already available. However, if you are using MySQL-Server, or an old MariaDB or Percona server you would need to install the server_audit plugin. Download it from this link: http://www.skysql.com/downloads/mariadb-audit-plugin-beta Once downloaded, extract it and copy the file server_audit.so to the plugin dir value path [See screenshot above].

[google_ad data_ad_slot=” data_ad_format=’rectangle’]

This does not means its already activated. You can verify it using the following commands SELECT * FROM information_schema.plugins WHERE plugin_name=’server_audit’; If it returns an emply set, it means its not yet activated otherwise you should have something like this:

Screenshot from 2016-05-30 08-37-56

Activating the Plugin

To activate the plugin, you can restart the service. However, there is another option to prevent any MySQL downtime by launching this command INSTALL PLUGIN server_audit SONAME ‘server_audit.so’;

Screenshot from 2016-05-30 08-42-33

Plugin configuration

After activating the plugin and if you are going to launch the command SHOW GLOBAL VARIABLES LIKE ‘server_audit%’; by default it would output you the following parameters.

Screenshot from 2016-05-30 08-51-03

These default values would normally create a log file called server_audit.log in the data directory. The values are self explicit. For example the parameter server_audit_file_rotate_size with value 1000000 means that when the size of the log is going to reach 1000000 bytes, its going to be rotated and nine files will be used before the log file will be overwritten. You also need to choose which type of events you want to log. Here is an example when activating all CONNECT, QUERY and TABLE event. If you want to audit only the CONNECT even, do set the variable to CONNECT only.

Screenshot from 2016-05-30 09-02-51

You can also turn off the plugin using the command SET GLOBAL server_audit_logging=OFF; As mentioned previously, the logs are saved at /var/lib/mysql/server_audit.log Here is an example of a log.

[google_ad data_ad_slot=” data_ad_format=’rectangle’]

Screenshot from 2016-05-30 09-08-30

Here are some of the most important variables:

  • server_audit_logging – Enables audit logging; if it’s not set to ON, audit events will not be recorded and the audit plugin will not do anything.
  • server_audit_events – Specifies the events you wish to have in the log. By default the value is empty, which means that all events are recorded. The options are: CONNECTION (users connecting and disconnecting), QUERY (queries and their result), and TABLE (which tables are affected by the queries).
  • server_audit_excl_users, server_audit_incl_users – These variables specify which users’ activity should be excluded from or included in the audit. server_audit_incl_users has the higher priority. By default, all users’ activity is recorded.
  • server_audit_output_type – By default auditing output is sent to a file. The other option is syslog, meaning all entries go to the syslog facility.
  • server_audit_syslog_facility, server_audit_syslog_priority – Specifies the syslog facility and the priority of the events that should go to syslog.

Log File Examination

Log file can also be examined. The audit is performed in such a way that if even one user connect and disconnect the the MySQL it would be easily detected. A connect and disconnect would usually appears as this:

Screenshot from 2016-05-30 09-22-42

Queries woud look like this. Even if there are errors on the query, it would keep it in the log

Screenshot from 2016-05-30 09-25-22

The server_audit_events variable specifies which of the five events to log, taking a comma-separated list of the event types as an argument. There are six types of log records:

Photo Credits: MariaDB
Photo Credits: MariaDB

The audit log format looks like this:


Other Tips and Tricks

  • To avoid a heavy load on the machine, you can exclude a specific user using the parameter server_audit_excl_users=test,toto 
  • If the server_audit_output_type variable is set to SYSLOG instead of the default, FILE, the audit log file format will be as follows:
  • Be aware, though, that passwords given with functions PASSWORD() or OLD_PASSWORD() in DML statements will still be logged as plain text in queries. Key strings used with encrypt functions likeENCODE() and AES_ENCRYPT() are also still logged in plain text.
  • DDL and DML statements can also be audited.