Database Systems Archives - Page 2 of 3 - The Blog of Nitin J Mutkawoa, AKA @TheTunnelix

Category: Database Systems

Activating MariaDB Audit log

05/30/2016 Nitin J Mutkawoa Add Comment

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

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:

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:

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’;

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.

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.

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’]

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:

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

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:

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.

Database Systems, Security linux, MariaDB, security

Backup, Restore and work on PostgreSQL cmd line

03/31/2016 Nitin J Mutkawoa Add Comment

Last time, we have a look how to install and set up PostgreSQL on a CentOS machine as well as to create a database. Let’s now see some more details about backups and database restore as well as some commands to master the basic of psql on command line.

1.We have created a database called test. Here is an example how you create a table on Postgres

test=# create table hackers( id int primary key not null, country char(20), number int not null, project varchar(50) );

2. You can view your table with the command \d or \d <table name>

3. To perform a dump of a database log in as the user which have access to the database and launch the following command.

pg_dump test > db_backup.sql

To restore the same backup on a postgres machine, you need to have the database created first

psql test < db_backup.sql

4. A little work around of psql around the database for example accessing directly the database test with the command psql test and to list all the database can be done with \l

5. Let’s say you want to switch between database test and toto. This can be done with \c test and \c toto

6. To show all tables inside the database can be done with the command \dt

7. The schema of the table can also be seen with the command \d+ <table name> Here, it will be \d+ hackers

8. All users and permissions can be viewed with \du

9. Databases are create and drop inside the psql command line ending with a semi column

10. You can also alter user role with superuser and non superuser

11. You can also output the result of a select statement from the psql prompt directly using the following (For example the to output the result of a select statement to ~/SelectStatement.out) :

postgres=# \o ~/SelectStatement.out - Start logging the output
postgres=# Select * from users;     - The command
postgres=# \o                       - Stop the logging

Database Systems centos, PostgreSQL

Install, Setup and Create DB on PostgreSQL

03/30/2016 Nitin J Mutkawoa Add Comment

PostgreSQL is yet another open source object relational database system. Its compatible with almost all operating system including BSD, Windows and Linux. I will be using a Centos7 machine to install a PostgreSQL and set up some basics of PostgreSQL. In a next article, i will give some idea of something more robust you can do with PostgreSQL.

1.You can install PostgreSQL from the repository with the following command

yum install postgresql-server postgresql-contrib

2. Now the first action you need to perform after PostgreSQL server installation is to initialize the database by creating a new database cluster

postgresql-setup initdb

3. You can now start the postgresql service. Postgres will be listening on port 5432

systemctl start postgresql

4. Now that we have PostgreSQL installed on our machine, we can create the first super user with following command. Let’s called the user test. Its a practice to use the username having sudo privilege on the machine itself.

sudo -u postgres createuser --superuser  test

5. To connect on the Postgres command line use the following. postgres is the default user and psql is what u what to run

sudo -u postgres psql

6. You should have something similar to this.

7. Now you can set the password for the superuser that you have just created. In my case it is user ‘test’ at step 4

\password test

8. Once you have been prompted to enter the password twice means that you have already set up PostgreSQL. You can exit with the command

\q

9. To connect to the default postgres database you simply need to use the command. To quit follow step6

psql postgres

10. Lets create a database with the superuser. You might need to add your user to the group postgres with the command usermod -g test postgres as postgres will need permission to access your home directory to drop the .psql_history file

sudo -u postgres createdb test

11. To get on the command line you just need to type psql which should show you something similar to this

Database Systems, Linux Application centos, PostgreSQL

MariaDB-10.1 Galera Cluster on CentOS 7

03/27/2016 Nitin J Mutkawoa 8 Comments

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.

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

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.

11. Repeat the 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 organized by the MSCC.

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’;

Database Systems centos, MariaDB, VirtualBox

MariaDB and improved security features presentation

02/27/2016 Nitin J Mutkawoa 3 Comments

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 cyberstorm Mauritius. Thanks to Jochen Kirstätter (joki) founder of the MSCC who proudly sponsored the event.

mariadb

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 ?

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 a 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 the 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 has 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 calls 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 deduce that though Glibc has gone through the various patch, yet, there are more bugs that have been discovered.

A solution was thus proposed to adopt 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, Debian are much smaller. Other issues raised are on the Grsecurity aspect which though is not widely spread are a very important aspect to take into consideration. MUSL looks to be very promising compared to GLIBC. Another analogy is taken from the Docker technology where companies are adapting Alpine Linux in the production environment to escape Glibc.

The third part of the presentation was continued on by Joffrey on the Galera clustering solution. An explanation is given using a schema how replication is being done at the cluster level. Several particular Database schemas were 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 the 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 meetups and the proposal for members if they could find other suitable environments to carry out more interesting meetups in days to come. No one could deny that they have not learned 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 🙂

Database Systems, Linux Application, MeetUps and Presentations, Security MariaDB