Archive for the ‘Databases’ Category

RabbitMQ users queues exchanges bindings import export

June 19, 2015 2 comments

All I see it’s dead world
And I know that’s our fault
Living Absent minded
(Archeon – Dead World)

If you want to deploy test RabbitMQ, migrate from one node/cluster to another one or just back up your Rabbit metadata, there is a simple way to do it through RabbitMQ API.

API is available at http://rabbitmq:15762/api/ – with most of the documentation. To back up metadata, simply run:

$ curl -i -u <username>:<password> http://rabbitmq:15672/api/definitions

Output is in JSON format, and you can save it in a file by redirecting curl output to a file.
Later if you decide to restore the saved file to another (or same) Rabbit instance, it’s a single line command again:

$ curl -i -u <username>:<password>   \
  -H "content-type:application/json" \
  -X POST                            \
  --data @/tmp/rabbit_defs           \

And that’s it!


HaProxy error 503 if dependencies are down

November 16, 2014 Leave a comment

I’m screaming revenge again
I’ve been wrong for far too long
(Pantera – Mouth for War)

Every http app usually has a number of dependencies – like database (PostgreSQL or MySQL), caching layers (memcache, redis) or NoSQL backends like MongoDB. In case any of those dependencies are down, it’s good to inform public about it in a neat way. If you fail to do that, search engines will probably down rank your site. General advice from SEO sites is to avoid returning 200 OK or 404 Not Found when you want to inform users that site is down. In case of downtime, proper way to inform users (which include crawlers too) is to retutrn 503 Service Unavailable.

If your site depends heavily on some service like MySQL and won’t work without it, this check can be done at load balancer (HaProxy) level. Configuration is simple – first step is to create a user in MySQL database for MySQL check:

$ mysql -u root -e "INSERT INTO mysql.user (Host,User) \
  values ('','haproxy_check'); FLUSH PRIVILEGES;" -p

HaProxy uses this database user for functional checks (runs SELECT(1)). HaProxy config file should look like this:

backend myhttpdfarm
        option httpchk HEAD /check.txt HTTP/1.0
        server web01 weight 1 web01

backend mysql
        option mysql-check user haproxy_check
        server mysql_master check

listen main
        errorfile 503 /etc/haproxy/503.html
        use_backend myhttpdfarm if { srv_is_up(mysql/mysql_master) eq 1 }

Crucial part is “us_backend … if” directive. You can even chain conditionals, and add additional checks for memcache, Mongo, etc…

Using ElasticSearch templates with Logstash

October 18, 2014 1 comment

I cast aside my chains
Fall from reality
Suicidal disease
The face of all your fears
(At The Gates – Blinded by fear)

Logstash is great tool for acquiring logs and turning them from txt files into JSON documents. When using ElasticSearch as backend for Logstash, Logstash auto-creates indexes. This can be a bit of a problem if you have fields with dots in its contents, like “host”. If host name is recorded as FQDN (as it usually is), then ElasticSearch will analyse that field and split it at dots. Now, gets dissected into node04 and – which can pose a problem in Kibana interface when you try to figure out where the logs came from. Example I got in my case was that 3 nodes in one cluster had approximately 1/3 of requests each, and there was also “” showed as combined graph with those 3 values added, as you can see on the graph below:


Green bar equals the addition of yellow, blue and orange. Now, this could make sense if you had different domains and wanted to see comparison between them, but since I did not want that I had to find a way to remove it. Enter Elasticsearch templates. Templates will automatically be applied to new indices, when they are created. Since Logstash automatically creates indices, this is ideal to set things up as we want.

Since I wanted to change mappings, first thing I have to do is get current mappings to see what am I up against:

% wget http://elasticsearch:9200/<index_name>/_mapping

That page will give you JSON document, which is in oneliner style. To correct the coding style to classic JSON, to make it more readable, just copy/paste it to and click Validate. Now, remove everything except the fields you want to modify. My example mapping with everything except relevant lines removed looks something like this:

    "<index_name>": {
        "mappings": {
            "apache_error": {
                "properties": {"host":{"type":"string"},}
                "properties": {"host":{"type":"string"},}

Now, we have to add  “index”: “not_analyzed” param for every host key in the upper json. Also, template regex should be added so that ElasticSearch knows indices that this template applies to:

After his manipulation, we can either wrap it into new <template_name> tag, or upload it directly to Elasticsearch with curl:

curl -XPUT localhost:9200/_template/template_developer -d '
    "template": "developer*",
    "mappings": {
        "apache_error": {
            "properties": {"host":{"type":"string"}, "index":"not_analyzed"}
            "properties":{"host":{"type":"string"}, "index":"not_analyzed"}

Now, some of us engineers don’t like to upload configurations to services, but like to keep them in text files. That is also possible, by creating a directory called templates under path.conf – which is usually /etc/elasticsearch. In the latter case, template should look a liitle bit different:

# cat /etc/elasticsearch/templates/template_developer.json 
    "template_developer": {
        "template": "developer*",
        "mappings": {
            "apache_error": {
                "properties": {
                    "host": {"type": "string", "index": "not_analyzed"}
            "apache_access": {
                "properties": {
                    "host": {"type": "string", "index": "not_analyzed"}

After this, you need to restart Elasticsearch, and recreate indice, and that’s it! New graph looks like:


Migrating Zabbix from MySQL to PostgreSQL

February 18, 2014 Leave a comment

Three evil giants
of the south
are constantly on the attack
With lies and fire
from their mouths
but we always send them back
(Amon Amarth – Guardians of Asgaard)

Sooner or later someone will come across a similar problem – migrating some MySQL based application to PgSQL. Reasons are various. I decided to migrate Zabbix to another backend because it was the only app using MySQL while all others were using PostgreSQL. So there was really no point at all to keep up with another database, set up another replication, backups, etc… Someone else will probably have different reasons.

So, how did I do it? First of all, it’s important to understand database schema and data types. Since Zabbix supports both backends, it’s obvious migration shouldn’t be that hard. First thing I did was upgrade to latest version of Zabbix (from 2.0 to 2.2.1). I also upgraded database to Percona 5.6. PostgreSQL versoin of choice was latest from 9.3 series. So this is my setup:

  • MySQL Percona 5.6
  • Zabbix 2.2.1
  • PostgreSQL 9.3

After upgrade to latest versions of Zabbix and MySQL, it’s good to make sure zabbix housekeeper has done it’s work, and database is as compacted as it should be. To be sure of this, take a look at zabbix-server logs and search for term ‘housekeep’.

As for migration, I decided to both dump tables and save them into CSV format. This is a simple script that does it:

mkdir /var/tmp/zabbix/CSV; chown -R mysql: /var/tmp/zabbix
for i in `find /var/lib/mysql/zabbix/ -type f | \
 sed 's/\....$//' | sort | uniq | sed 's#/var/lib/mysql/zabbix/##'`; do
   mysqldump --compact -c -e -n -t --compatible=postgresql zabbix $i > $i.sql ;
   mysqldump -t -T/var/tmp/zabbix/CSV zabbix $i --fields-terminated-by=';' ;

So, for every table in zabbix database in MySQL, I create both SQL dump (in postgresql compatible mode) and a CSV dump with semicolon as a column delimiter. Why did I choose semicolon? Because Zabbix tends to have a lot of colons in it’s data, so this eases up insertion of data and lowers needed number of escape characters, which can pose problems.
Note: As you can notice, I use InnoDB’s ‘ file-per-table’ mode, that’s why I can get a list of available tables just by searching through /var/lib/mysql. If you don’t use that setting, you should modify the commad by using “SHOW TABLES” query.

Next step is to modify schema.sql from Zabbix server installation and remove some of the constraints. I removed all “ALTER TABLE“, “CREATE INDEX” and “CREATE UNIQUE INDEX” queries from schema file, and imported that schema into zabbix database in PostgreSQL:

$ psql zabbix < schema_modified.sql

Those queries will be needed later, after all data is successfully imported.

Now that zabbix database has it’s base structure, we can start with importing data. I’ve used “COPY” command that imports from CSV. Import is atomic, so if some error occurs, target table will stay empty.

$ psql zabbix
zabbix# COPY alerts FROM '/var/tmp/zabbix/CSV/alerts.txt' DELIMITER ';' CSV;

If COPY exits with number of inserted rows, that that table is successfully imported. If, on the other hand, COPY spits out some kind of error or warning, that table probably wasn’t imported correctly. So, I recorded all the failed tables, and decided to import them via standard SQL dump. This is a list of tables that failed to import from CSV format:


Typical error which I saw was:

ERROR:  missing data for column "agent"

There were other errors, which I did not want to even try to fix. So, my next move was to use SQL dumps for those tables. But, I had to modify them a little bit, by adding following header:

SET standard_conforming_strings = 'off';
SET backslash_quote = 'on';
SET escape_string_warning = 'off';

and offcourse a footer with “COMMIT” 😉 These settings are compatibility flags used for importing MySQL dumps. After all data was imported, all I needed to do is add all those indexes and table alterations from schema.sql that was ommited when I created database, and to fix permissions:

for i in `echo '\dt' | psql zabbix | grep 'public |' | \
awk -F\| '{ print $2 }' `; do
    echo "ALTER TABLE public.${i} OWNER TO zabbix;" | psql zabbix;

And that’s it, bye bye MySQL 🙂

Categories: Databases Tags: , , ,

PostgreSQL and MySQL password hashes

December 28, 2013 Leave a comment

The fate of Norns await us all There is no way to escape The day to answer Oden’s call Or walk through hel’s gate (Amon Amarth – The Fate of Norns)

PostgreSQL and MySQL roles store their text passwords as hashes. Automation tools and scripts will often want to set those hashes, so it’s useful to know how to generate them correctly. There is a difference how PostgreSQL and MySQL hashes are generated. Authenticating via password – PostgreSQL can use ‘password’ or ‘md5’. Main difference is how the password is transferred between client and server. In the former case it is transferred in plaintext, and if the ‘md5’ is used then it’s transferred as a hash. But, PostgreSQL password is not a simple MD5 hash of a plaintext passphrase. PostgreSQL hashes combined name and password and adds ‘md5’ in front of the hash. This simple ruby script describes the process:

#!/usr/bin/env ruby
require 'digest/md5'
puts 'md5' + Digest::MD5.hexdigest(ARGV[0] + ARGV[1])

ARGV[0] is pgsql role password, and ARGV[1] is pgsql role name. So it’s interesting that PostgreSQL uses both role name and password to generate md5 password hash. On the other hand, MySQL doesn’t do any kind of combining, pure passphrase is hashed with SHA1 algorithm, but with two passes. Quick and easy way to generate MySQL password hashes is to use the MySQL-centric query ‘SELECT PASSWORD’:

mysql> SELECT PASSWORD('mypass');
| PASSWORD('mypass')                        |
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
1 row in set (0.03 sec)

But how does that query work under the hood? MySQL uses combination of two SHA1 passes for passwords hashing, and this little ruby snippet shows how:

#!/usr/bin/env ruby
require 'digest/sha1'
puts '*' + Digest::SHA1.hexdigest(Digest::SHA1.digest(ARGV[0])).upcase

MySQL uses two passes to make the life harder to potential attackers.

Cleaning OSSEC MySQL database

November 21, 2012 Leave a comment

If I had wings, would I be forgiving?
If I had horns
Would there be flames to shy my smile?

(Dark Tranquillity – Punish my heaven)

OSSEC is a good intrusion detection tool  that can help pinpoint not only security breaches but general software anomalies too. It can be installed as standalone and as server-client tool. Server can write data about alerts to database (usually it’s MySQL). But, on a large network one has to be really careful about using database as an alert datastore. Things can get out of control quite quickly.
Here is how current setup looks like on one of the installations that I help managing:

# /var/ossec/bin/agent_control -l | grep -c "^\s*ID"

So, this means that this server controls 149 agents. Average on this installation is about 300 alerts per day, but that is mainly due to lack of spare time available for optimization of custom rules. Nevertheless, all of the alerts go to the MySQL database too. So, by doing simple arithmetic we should be at around 55’000 database entries? Well, not quite, because OSSEC also logs events for which no alert will be sent… So this number is huge – take a look:

mysql> select count(*) from alert;
| count(*)  |
| 123008768 |

So this means that there is 675’000 rows inserted to MySQL per day! And all that goes to MyISAM by default OSSEC schema! Database is around 35GB, so it was time to do something about it. We decided to shrink the database to 32 days, and to purge the rest. Now, little shell magic comes to play. I’ve created a script that will be called with cron daily job. This is how the script looks like:

DELETE2TIME=`/bin/date -d "32 days ago" "+%Y-%m-%d %H:%M:%S"`

   /bin/grep "<$1>" /var/ossec/etc/ossec.conf | /bin/sed "s|^.*<$1>\(.*\)</$1>|\1|g"

MYSQLHOST=`getvaluefromossec "hostname"`
MYSQLDB=`getvaluefromossec "database"`
MYSQLUSER=`getvaluefromossec "username"`
MYSQLPASS=`getvaluefromossec "password"`

echo "
SET @delete2time=\"$DELETE2TIME\";
DELETE FROM alert WHERE timestamp < UNIX_TIMESTAMP(@delete2time);
DELETE FROM data WHERE timestamp < @delete2time;

You can simply put this script to /etc/cron.daily and it will run every day and purge only 1 day of old data. You can modify the number in the date command invocation by how far back do you want to keep your data. In this example data is kept 32 days.

Note: first run may take a long time if you have large database and want to purge lot of rows 😉 In my case – deleting of month of history entries (22.5 million rows in two tables) took 45 minutes!

Note2: I almost forgot… You should OPTIMIZE TABLE after big deletes to free disk space occupied by MyISAM tables. This can be done in cron.daily script, by adding these two lines:


although I would strongly recommend to organize these kind of tasks in cooperation with DBA.

Categories: Databases, Security Tags: ,

The greatest database ever

July 19, 2009 1 comment

Trough the fiery caverns we sail
Virgin at my side, my guide and master
Questing through the nine plains of hell
(Iced Earth – Dante’s Inferno)

Another day at work… Another day of troubles with a piece of shit known as MySQL. MyISAM, InnoDB, NBD, Memory, Merge, Federated. They announced that 5.4 will bring new storage engine along. As these current ones were not enough. Apparently not. Because they are all stinkin’ worthless shit. Dozen of storage engines and none of them really works. I hate MySQL… And every day of being forced to use it, I hate it more and more. MySQL is on a good path to surpass even vegeterians on my black list. I hated Sun for acquiring MySQL AB. I then loved Oracle for acquiring Sun : ) Because I hope that MySQL will be beaten to death, raped, ripped, torn apart, bombarded, nuked, and then reborn just to repeat the process again. That is the crappiest database world has ever seen. It’s insult to others like PGSQL to call that piece of hackery a database. Why don’t you need 10 different storage engines with PostgreSQL? Because they have one that works kicks, and kicks ass of all MySQL ones for example. Those guys at MySQL are idiots. In the dictionary under word idiot is supposed to be the following explanation: “The guy that engineers or programs MySQL”. And what about SQL92 standard? That’s whole other storry to tell. How the guys at MySQL AB break standards can be seen as the greatest try to redefine word Microsoft ever. INSERT with SET instead of VALUES? And all other bullshit? So that they can be sure noone will switch over to some other normal database ever… Fuckin’ morons…

%d bloggers like this: