Tag: MariaDB

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 challenges faced by the IT industry today especially larged 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, hackers.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].

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.

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:

[timestamp],[serverhost],[username],[host],[connectionid],
[queryid],[operation],[database],[object],[retcode]

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:
    [timestamp][syslog_host][syslog_ident]:[syslog_info][serverhost],[username],[host],
    [connectionid],[queryid],[operation],[database],[object],[retcode]
  • 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.

MariaDB-10.1 Galera Cluster on CentOS 7

Some times back, i posted two articles on MariaDB Master-Master replication and MariaDB Master-Slave replication. Well, after several requests from friends, i was asked to blog on MariaDB Galera Cluster. MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB. It is available on Linux only, and only supports the XtraDB/InnoDB storage engines (although there is experimental support for MyISAM. – Mariadb.com. You can easily download Galera Cluster using the Repository Configuration tool. There is also a slight difference between oldest version of MariaDB compared to that of the 10.1 series. Recently, a question was asked on ServerFault to which i clarified same. Check it out for more information. Lets now have some funs with MariaDB Galera Clustering.

Photo credits:Mariadb.org
Photo credits:Mariadb.org

I have created 3 CentOS7 virtual machines on Virtual Box. Lets called each Server as Galera1 [192.168.1.9], Galera2 [192.168.1.10] and Galera3[192.168.1.11]. You need to edit the /etc/hosts file to allow each machine to ping each others via hostname. For testing purposes port 3306 which is by default listening on MySQL should be open. I have also disable SELinux and Firewalld as same are for testing purposes. Lets now install MariaDB Galera Cluster.

1.Edit the repository by using configuration as mentioned on the official MariaDB website.

# MariaDB 10.1 CentOS repository list - created 2015-11-08 17:34 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

2. Lets install some Pre-requisites and other interesting tools.

yum install rsync nmap lsof perl-DBI nc

3. Install MariaDB-server and start the Mariadb service

yum install MariaDB-server MariaDB-client MariaDB-compat galera socat jemalloc
service mysql start

4. Launch the mysql_secure_installation command to set up the username and password. At this point you can start MariaDB normally by using the mysql command and create a database.

5. After the normal procedure to set up the first database on galera1, you can create a database and dump it, then import the database on galera2 and galera3. You have now 3 independant databases on 3 different servers. I would adivce you to dump the whole database with the command

mysqldump --all-databases > db.sql

6. We will now start setting up the Galera clustering. On galera1, edit the /etc/my.cnf.d/server.cnf file on galera1 and configure it as follows. The server galera1 is the being setup as the first primary cluster. So every other cluster is going to be set up in the network that is galera2 and galera3 is going to replicate itself from galera1. Add the following parameter under the [galera] option.

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://'
wsrep_cluster_name='galera'
wsrep_node_address='192.168.1.9'
wsrep_node_name='galera1'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

7. Once this is added, you can now start the first Galera cluster with the command. Make sure that the mysql service is stopped first. as at step5 we have started mysql to create a test database

service mysql start

8. You would noticed that the ports 3306 and 4567 should be listening

Screenshot from 2016-03-27 11-23-59

9. Now you would like to make galera2 joined the primary cluster i.e galera1. Simple set up the galera2 /etc/my.cnf.d/server.cnf file and under [galera] enter the following parameter. The only difference is the wsrep_node_name, wsrep_node_address and the gcomm value where you need to add the first cluster.

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://192.168.1.9'
wsrep_cluster_name='galera'
wsrep_node_address='192.168.1.10'
wsrep_node_name='galera2'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

10. Once mysql is started with the command service mysql start, you would noticed that galera2 is now on replicating from cluster1. You should noticed something similar to this on your log.

Screenshot from 2016-03-27 12-33-56

11. Repeat same procedure for galera3 by tweaking the gcomm value and modifying the wsrep_node_address etc..

If you interested in having some more ideas about the MariaDB improved security features, do check out the following link where i attended a conference on MariaDB organised by the MSCC and Hackers Mauritius.

Tips:

  • If  you are using the 10.0, you would need MariaDB-Galera-server package compared to 10.1 where you just need MariaDB-server.
  • There are no longer separate MariaDB Galera Cluster releases for MariaDB 10.1 and above. Simply download MariaDB (10.1 or above) and configure your cluster as normal. MariaDB-10.1 is just ‘Galera ready’.
  • If you are migrating from MariaDB-server to Galera make sure you have clean up all the residues belonging to the old versions. See http://serverfault.com/questions/646973/installing-mariadb-galera-via-yum-fails-with-no-package-mariadb-galera-server.
  • The galera package will provide you the /usr/lib64/galera/libgalera_smm.so  and several binaries for the cluster configuration. There are several cluster configuration under ws_rep see the documentation.
  • The socat package establishes two birectional byte streams and transfers data between them.
  • I would also recommend to disable SELinux on the machines.
  • When you start the mysql service, you can tail the log at /var/log/messages otherwise you can set up mysql log.
  • Use this command show status like ‘wsrep_%’; on your mysql prompt to understand the replication.
  • To know how much node in the cluster use the following command show status like ‘wsrep_cluster_size’;

MariaDB and improved security features presentation

If you have been following the MSCC – Mauritius Software Craftsmanship Community some weeks back, you would have noticed a forthcoming meetup on MariaDB and improved security features spoken by Joffrey Michaie from OceanDBA and Codarren Velvindron from the Hackers Mauritius. Thanks to Jochen Kirstätter (joki) founder of the MSCC who proudly sponsored the event.

mariadb

12784250_10153937032372365_1053507839_n

Joffrey at the MariaDB meet up

1915707_190175371353387_934013232802437990_n

Some craftsmans at the Meet up

1935103_190175328020058_3787078129675559662_n

Codarren explaining Glibc

12801299_190175304686727_3455294414280043217_n

Logan and me from hackers Mauritius

10399406_190175258020065_1829899531834394151_n

Codarren and me from hackers Mauritius

12718230_190175168020074_27057020749688372_n

Can you spot where am i ?

Screenshot from 2016-02-28 00-06-47

Jochen. founder of MSCC

The first part of the presentation started with Joffrey who gave a brief introduction of MariaDB and the importance of its security features. He also laid heavy emphasis on the backup concepts that DBA need to go through. What are most interesting are that there seem two additional services that are coming on from OceanDBA – Backup as a Service and DB as a service.

Other points raised up concerning the importance of backups are to start a clustering solutions, to perform analysis and several tests on the Pre-production or staging servers. Database backups also need to be tested as there can be corrupted zip files. Another interesting issue raised up is about the locking table mechanisms during backups. Other backup strategy and concept were also explained such as:

  • Cold backups – The downtime issues were raised up which according to me does not look practicable unless there is really a specific reason
  • Hot backups – Usually carried out by the MySQLDUMP utility by everyone.
  • Logical backups – Data that are usually backup as tables, views, indexes etc.. and they are mostly human readable statements. logical backups can be performed at the level of database and table.
  • A tool that is completely new to me is the mydumper which can be used to backup terabytes of data. Some interesting arguments raised up are –lock-all-tables –skip-lock-tables and –master-data
  • Binary backups – The binary backup which is the copy of the actual database structure and requires file system or disk subsystem access. It is one the fastest method to backup and very compatible for mixed MYISAM and INNODB tables.
  • HA (High availability) as backup – Usually used in clusters and in Galera replication. However, to ensure that there is no data loss, a SAN replication was also recommended for data centers.
  • Time delay replication – This was explained by taking an example, say a one hour delay backup based on the risk management that have been carried out.
  • The Percona tools which can be used alongside MariaDB for backup Analysis.

On the second part of the presentation, Codarren lays emphasis on the security aspects concerning MariaDB in the context of whether to use Glibc or MUSL. Glibc libraries are used on mail servers, SQL servers, forms etc.. Back to the Glibc Ghost vulnerability, an explanation was given by taking a web based form application where a particular field when filled with malicious information can be used to make call to Glibc library with the intention to return a specific value. To re-mediate at that situation, same was patched using the function getaddressinfo() This patch lead to another vulnerability. Since today, we can deduced that though Glibc has gone through various patch, yet, there are more bugs that have been discovered.

A solution was thus proposed to adapt the MUSL library infrastructure. We can see that the MUSL has a clean code policy compared to Glibc. Coddarren laid emphasis on the Alpine Linux operating system which is naturally based on MUSL. The size of Alpine compared to CentOS, Ubuntu, Debians are much smaller. Other issues raised are on the Grsecurity aspect which though are not widely spread are very important aspect to taken into consideration. MUSL looks to be very promising compared to GLIBC. Another analogy taken from the Docker technology where companies are adapting Alpine Linux in the production environment to escape Glibc.

ice_logo-5dcea9e47b780ff52f75c3c3304d54827f56211e

The third part of the presentation was continued on by Joffrey on the Galera clustering solution. An explanation given using a schema how replication is being done at the cluster level. Several particular Database schema was taken for example where a node with a cluster which is slow in terms of network or infrastructure issue where the other servers will have to wait for the request to reach its destinations. Other points mentioned are:

  • Split brain in Galera where human interactions are needed especially where the ratio of the number of nodes have different data from other nodes within same cluster. 
  • The importance of having applications built-in with retrying logic.
  • Galera conflict diagnostic. For example cert.log which is used to log and monitor conflict transactions.
  • Features such as auto-commit mode.
  • Galera load balancing using Haproxy – custom monitoring on cluster size.
  • MariaDB Maxscale which operate at layer 7 persistent connection.
  • Maxadmin command line utility to list servers that are in the cluster

Jochen has also laid emphasis on future meet ups and the proposal for members if they could find other suitable environment to carry out more interesting meet ups in days to come. No one could deny that they have not learn anything. Indeed, the meet up was really interesting and fruitful. Some stickers were shared having the MariaDB logo which i have already pasted at the back of my Laptop 🙂