Inside Out Bloghttp://inside-out.xyz/tag/96.html
Tue, 20 Mar 2018 00:16:53 -0400Joomla! - Open Source Content Managementen-gbdaniel.lucio@inside-out.xyz (Inside Out Blog)Updating Server System Variables in MariaDB & MySQL without restarting the deamonhttp://inside-out.xyz/technology/updating-server-system-variables-in-mariadb-mysql-without-restarting-the-deamon.html
http://inside-out.xyz/technology/updating-server-system-variables-in-mariadb-mysql-without-restarting-the-deamon.htmlWhen you need to start doing tuning on your database, the first thing you need to modify is the configuration file. So far, it is easy to do this; the ugly part of this is, you may need to restart database daemon if you change a variable. Good news! Some of those variables do not need a server restart.

First, check if the variable you need to modify is dynamic. If it is, do the following:

To verify the current value: SELECT @@max_connections;

To change the value: SET GLOBAL max_connections = 1024;

Enjoy!

]]>Updating Server System Variables in MariaDB & MySQL without restarting the deamonTue, 08 Dec 2015 22:37:24 -0500RPM for PAM_mysql 0.7 RC1http://inside-out.xyz/technology/rpm-for-pam_mysql-0-7-rc1.html
http://inside-out.xyz/technology/rpm-for-pam_mysql-0-7-rc1.htmlI don't know why I haven't done this before. I have completely forgotten! Today, I published in OKay's RPM repository RPMs for PAM MySQL 0.7 RC1. This is a module that allows PAM-aware applications to authenticate users through a MySQL database. Now configurable in terms of which host the database reside upon, which table and username and password column to interrogate.

For those who may ask, yes! It works with MariaDB. And as far as I have been using it, you can put complex INNER JOIN requests.

RPM's are available for Centos 6 and 7. And you can find it if you type yum search pam_mysql.

Enjoy!

]]>RPM for PAM_mysql 0.7 RC1Sun, 28 Feb 2016 22:09:58 -0500Accessing foreign tables in your local MySQL or MariaDBhttp://inside-out.xyz/technology/accessing-foreign-tables-in-your-local-mysql-or-mariadb.html
http://inside-out.xyz/technology/accessing-foreign-tables-in-your-local-mysql-or-mariadb.htmlYesterday I was needing to migrate a website, but it was not just another website. Many of you may know I have a project called EstoyEn. EstoyEn is a service that allows you to reach content with geo restriction; in other words, you may be able to watch Netflix catalogs from another country among other things. One of the techniques I use to archive this is using DNS and reversal HTTP/HTTPS proxies.

To justify the use of this database technique, the DNS uses the database to know how to answer. So, as the DNS is a public IP already know by all users, it can not be moved; I just need to move out the website. So, Ii needed an easy way to have some database tables locally without breaking the website; here it is were federated engine comes.

]]>Accessing foreign tables in your local MySQL or MariaDBThu, 31 Mar 2016 23:46:47 -0400Reconnecting with MySQL/MariaDB in Chttp://inside-out.xyz/technology/reconnecting-with-mysql-mariadb-in-c.html
http://inside-out.xyz/technology/reconnecting-with-mysql-mariadb-in-c.htmlToday while I was coding a DNS plugin against a MariaDB database, I realized that after a long idle time database closes the connection. After googling for a moment, I realized that both MySQL and MariaDB offers in its API a mechanism to allow reconnections. So here it is the way I did it.
]]>Reconnecting with MySQL/MariaDB in CSat, 02 Apr 2016 22:01:34 -0400RPM for PAM_mysql 0.7 RC1 with patcheshttp://inside-out.xyz/technology/rpm-for-pam_mysql-0-7-rc1-with-patches.html
http://inside-out.xyz/technology/rpm-for-pam_mysql-0-7-rc1-with-patches.htmlAfter working on this package some hours, I finally got a decent version ready to work. Today, I published in OKay's RPM repository RPMs for PAM MySQL 0.7 RC1. This is a module that allows PAM-aware applications to authenticate users through a MySQL database. Now configurable in terms of which host the database reside upon, which table and username and password column to interrogate.

This package supports Drupal 7 and Joomla 1.5 authentication scheme (for backwards compatibility). I have added some patches for debugging, so if it fails you will see in the Syslog log what is happening.

Sadly, RedHat and clones (CentOS, ClearOS included) don't include blowfish encryption ($2[axy}$\d{2}$ key). This is because a U.S. law that forbids encrypt algorithms to be exported (I am not a lawyer). So, using other encryption schemes will be good. Good news is that I know Mageia supports blowfish.

For those who may ask, yes! It works with MariaDB. And as far as I have been using it, you can put complex INNER JOIN requests.

RPM's are available for Centos 6 and 7. And you can find it if you type yum search pam_mysql.

Enjoy!

]]>RPM for PAM_mysql 0.7 RC1 with patchesSun, 29 May 2016 15:13:44 -0400Export a MySQL/MariaDB SELECT into a CSV filehttp://inside-out.xyz/technology/export-a-mysql-mariadb-select-into-a-csv-file.html
http://inside-out.xyz/technology/export-a-mysql-mariadb-select-into-a-csv-file.htmlThis is not a new question, but I think it is interesting to know how to export a SELECT statement into a CSV formatted file. In this example, I am going to export a FusionBPX CDR format.

]]>Export a MySQL/MariaDB SELECT into a CSV fileWed, 13 Jul 2016 00:55:44 -0400HAProxy for MySQL/MariaDB Load Balance and High Availability Clusterhttp://inside-out.xyz/technology/haproxy-for-mysql-mariadb-load-balance-and-high-availability-cluster.html
http://inside-out.xyz/technology/haproxy-for-mysql-mariadb-load-balance-and-high-availability-cluster.htmlAfter you have already setup your MySQL or MariaDB cluster in master-master mode, the next step is to know how to put this in high availability or load balance scheme. One of the many software you can use for this is the HAProxy project. HAProxy is a TCP/HTTP reverse proxy which is particularly suited for high availability environments. Indeed, it can: route HTTP requests depending on statically assigned cookies, spread load among several servers while assuring server persistence through the use of HTTP cookies, switch to backup servers in the event of a main server fails, accept connections to special ports dedicated to service monitoring, stop accepting connections without breaking existing ones, add, modify, and delete HTTP headers in both directions, block requests matching particular patterns, report detailed status to authenticated users from a URI intercepted by the applications.

So, I will explain how I did this configuration.

]]>HAProxy for MySQL/MariaDB Load Balance and High Availability ClusterWed, 10 Aug 2016 03:09:20 -0400Conecting to a RDS such as Azure's, AWS' or Google'shttp://inside-out.xyz/technology/conecting-to-a-rds-such-as-azure-s-aws-or-google-s.html
http://inside-out.xyz/technology/conecting-to-a-rds-such-as-azure-s-aws-or-google-s.htmlRDS services are becoming very common now. Big players like AZURE, Amazon (AWS) or Google are ofering them. They are very handy, you get rid of scalability problems and you only focus on your database management.

One of the features you will find, as I did, in these new services is the enforced security. Which it is good, as the information traves through the Internet. Bad thing is not every system is aware of using TLS/SSL connections. I will talk how i did it in my cases.

]]>Conecting to a RDS such as Azure's, AWS' or Google'sTue, 30 May 2017 13:26:15 -0400Moving away from the FILTER() SQL Clauseshttp://inside-out.xyz/technology/moving-away-from-the-filter-sql-clauses.html
http://inside-out.xyz/technology/moving-away-from-the-filter-sql-clauses.htmlIt seems that the FILTER() clause was introduced in SQL 2003 revision. The FILTER clause allows you to have a better control when doing statistics functions in SLQL such as COUNT(), MAX(), MIN(), SUM() and others. Sadly, so far only Postgresql has implemented it. According to my readings on the net, other big players such as MySQL, MariaDB, Oracle, Microsoft SQL, SQLite (and maybe others) do not support it. Lucky us, there is a way to translate those queries.

Translating SUM(field) FILTER(WHERE) to SUM(CASE WHEN)

Please note this approach works with any other SQL function that returns a statistic calculation (MAX, MIN, AVERAGE). There is an alternative for COUNT(), I will write about it bellow.

Let's have this query as an example:

SELECT SUM(balance) FILTER (WHERE expired = 0) AS total FROM mytable

This could be translated as

SELECT SUM(CASE WHEN expired = 0 THEN balance ELSE 0 END) AS total FROM mytable

It is very important to put the ELSE 0, otherwise, the result could it be null (which it is different than zero). I know this example could be written in a very simple way. But trust me, there are many other SQL sentences that can hardly be translated into JOINS.

Translating COUNT(*) FILTER(WHERE) to SUM(CASE WHEN)

There is no a direct way to support COUNT(CASE WHEN). At least, I tried on MariaDB 10.1 and I got a syntax error. There is another approach. Let us say we have this SQL sentence:

We are changing the COUNT() into a SUM(), but instead of adding the value in the variable, we are fixing it with a 1.

Good luck!

]]>Moving away from the FILTER() SQL ClausesWed, 21 Jun 2017 09:51:15 -0400Monitoring the MySQL/MariaDB Maximum Connection Cap with Nagioshttp://inside-out.xyz/technology/monitoring-the-mysql-mariadb-maximum-connection-cap-with-nagios.html
http://inside-out.xyz/technology/monitoring-the-mysql-mariadb-maximum-connection-cap-with-nagios.htmlJust another beautiful day to have databases issues. This time, I have a beautiful MariaDB Galera Cluster running as a backend of a FreeSWITCH Cluster. It seems that operation does not take a single rest and it is keeping adding new extensions. New extensions mean more calls, which it can be translated to more databases queries and connections.

The issue here is not changing the max_connection value in the database configuration file. The issue, as I see, is that doing that will waste memory that could be used somewhere else. Therefore, the value needs to increased gradually; at some point, this will not be enough and more tunings, including database register cleaning might be done. For now, just let's configure an alert that will tell us when it is a good time to start thinking what to do.