Wednesday, August 10, 2016

Installing MariaDB MaxScale the hard way

If you are like me (let's for everyones sake hope you are not, though) you like to do things the hard way, in particular when it comes to testing things. For example when installing things on your Linux box, just to try them out, you might not want to do a yum install an rpm -ivh or an apt-get to have some files spread all over your system, instead you want to tar xvf some tarball and possibly, if you are in a good mood or you want to be a nice so you get some gifts for christmas or maybe because it is just that day, you unpack that tarball in /usr/local instead of in /home/bofh/junk. And this will usually get you in some trouble, but as we have already determined that we are truly bad (maybe we should get a tattoo or two also, or is the right to death-metal antics reserved for IT security personel only? Sure seems so) we can ignore that and get to work.

Here I will show you how to install MariaDB MaxScale from a tar-ball and get it running, without touching any system directories or anything if you want to test it or if you, even in production, want to install it in some non-standard location (like /usr/local. I actually like to have stuff there, I don't know what's so wrong with that. I'm a rebel, I know).

To begin with, let's download MariaDB MaxScale tarball (rpm's are for wussies), for example from mariadb.com where you should register and then go to "my portal"->"Downloads"->"MariaDB MaxScale" and download an appropriate .tar.gz for your operating system of choice. In my case I download it for CentOS 6 / RHEL 6 and as the current MariaDB MaxScale version is 1.4.3 I issue the command in my home directory (/home2/anders):
$ wget https://downloads.mariadb.com/enterprise/<my tag>/mariadb-maxscale/1.4.3/rhel/6/x86_64/maxscale-1.4.3-1.rhel.6.x86_64.tar.gz
With <my tag> replaced by a generated tag on mariadb.com. Following this we are stuck with a tarball named maxscale-1.4.3-1.rhel.6.x86_64.tar.gz and we unpack that as usual and then create a more readable link to the created directory:
$ tar xvfz maxscale-1.4.3-1.rhel.6.x86_64.tar.gz
$ ln -s maxscale-1.4.3-1.rhel.6.x86_64 maxscale143
So far nothing magic has happened. The next step is to create a few directories in our new maxscale143 directory where MariaDB MaxScale will keep temporary, stuff, logs etc:
$ cd maxscale143
$ mkdir cache data log

The next step after this is to create a MariaDB MaxScale configuration file. There is a template for this in the etc subdirectory so we just have to copy that:
$ cp etc/maxscale.cnf.template etc/maxscale.cnf
The supplied config file will start MariaDB MaxScale with just 1 server defined, and unless you have this server running on a non-standard port or on another machine than the one where MariaDB MaxScale itself is running, you can leave this configuration file alone, and if not you have to edit the [server1] section appropriately.

Another thing to look for is iptables / firewalld settings, but this you already know about I guess. You might want to turn them off (which is not recommended at all) or configure it appropriately. As per the default configuration with MariaDB MaxScale 1.4.3, ports 4006, 4008 and 6603 will be listened to, so you configure iptables / firewalld appropriately. And don't turn them of, do this the right way for once. I turned iptables off by the way, just to annoy you.

Now, MariaDB MaxScale will connect to the server we defined in the configuration file above, and we need to allow it to connect and execute a few commands. There are two users that MariaDB MaxScale can use, one to connect and get authentication data, like usernames and passwords, and another separate one to monitor the state of the server. In the supplied configuration template these two users use the same account, namely myuser using mypwd as the password, and this is what I use in the following where are set up the appropriate user and grant in the MariaDB server I am connecting to, and also note that I am assuming that MariaDB MaxScale and the MariaDB server on question run on the same node. So connect to MariaDB and issue the following commands:
MariaDB> CREATE USER 'myuser'@'localhost'  IDENTIFIED BY 'mypwd';
MariaDB> GRANT SELECT ON mysql.user TO 'myuser'@'localhost';
MariaDB> GRANT SELECT ON mysql.db TO 'myuser'@'localhost';
MariaDB> GRANT SELECT ON mysql.tables_priv TO 'myuser'@'localhost';
MariaDB> GRANT SHOW DATABASES ON *.*TO 'myuser'@'localhost';
MariaDB> GRANT REPLICATION SLAVE ON *.* TO 'myuser'@'localhost';
MariaDB> GRANT REPLICATION CLIENT ON *.* TO 'myuser'@'localhost';

With this in place we are ready to start MariaDB MaxScale, but this is an itsy bitsy more complex than you think. The issue is that the default locations for a lot of stuff that MariaDB MaxScale wants to use is somewhere in the global file system, and they are also not relative to some basedir as is conveniently the case with MariaDB server itself. to support this, instead of putting all this in the global section in the MariaDB MaxScale config file I'll instead put any necessary arguments to get MaxScale going on the command line, and for that I have created three scripts, 1 to set up the environment, one to start MariaDB MaxScale and one to stop it. Let's start with the environment one. This is places in the MariaDB MaxScale home directory (maxscale143) is called maxenv.sh and has the following contents:
#!/bin/bash
#
MAXSCALE_HOME=$(cd $(dirname $BASH_SOURCE) ; pwd)
PATH=$PATH:$MAXSCALE_HOME/usr/bin
export LD_LIBRARY_PATH=$MAXSCALE_HOME/usr/lib64/maxscale


The next file to create is the script to start MariaDB MaxScale, this is called startmax.sh, is again placed in the MariaDB MaxScale root directory and has this content:
#!/bin/bash
#
. `dirname $0`/maxenv.sh

$MAXSCALE_HOME/usr/bin/maxscale \
  --config=$MAXSCALE_HOME/etc/maxscale.cnf \
  --logdir=$MAXSCALE_HOME/log \
  --language=$MAXSCALE_HOME/var/lib/maxscale \
  --datadir=$MAXSCALE_HOME/data \
  --libdir=$MAXSCALE_HOME/usr/lib64/maxscale \
  --piddir=$MAXSCALE_HOME --syslog=no \
  --cachedir=$MAXSCALE_HOME/cache

As you can see this invokes maxenv.sh before going on to start MariaDB MaxScale. The only parameter that I really don't have to set here, but which I set anyway, again  just to be annoying to the world in general, is --syslog=no as we are only testing things here and logging to syslog is then not really appropriate (but it is the default).

All we need now is script to stop MariaDB MaxScale, and for this create a file called stopmax.sh in the MariaDB MaxScale home directory with this content:
#!/bin/bash
#
. `dirname $0`/maxenv.sh

if [ -e "$MAXSCALE_HOME/maxscale.pid" ]; then
   kill -term `cat $MAXSCALE_HOME/maxscale.pid`
fi


Following this, the one thing that remains to be done is to make the scripts we just created executable:
$ chmod +x maxenv.sh startmax.sh stopmax.sh

Now we are ready to try things, let's start MariaDB MaxScale first:
$ ./startmax.sh
And then let's see if we can connect to the MariaDB server through MariaDB MaxScale:
$ mysql -h 127.0.0.1 -P 4006 -u myuser -pmypwd
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 6950
Server version: 10.0.0 1.4.3-maxscale MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>

As you can see, I am not connecting as root here, as this is not allowed by MariaDB MaxScale by default. Also, I am not connecting to localhost as that assumes I am connecting using a socket, which is not what we want to do here.

This is all for today, now I'll need to start my Harley-Davidson and head down town to hang with the other tough guys (OK, I'm really taking my beaten-up Ford and pick up the kids from Kindergarten, I admit it).

Keep on SQL'ing
/Karlsson

Monday, August 8, 2016

MyQuery 3.5.6 released

I released version 3.5.6 of MyQuery, and there are quite a number of new features and fixes in there. The #1 bugfix is that the annoying access warnings that poped up from Windows when saving to the registry are gone, as I have now moved the registry to a more Windows 10 acceptable place. Among the new features are:
  • JSON format output when saving results.
  • More flexible CSV format output with many new options.
  • Ability to save Dyncol as JSON in CSV and JSON output.
  • Nicer formatting of numbers in status dialogs.
  • Auto refresh of status dialogs
 As this is a version with many new features, I still consider this a Beta. I have built it on Windows 10 and tested it on Windows 10 and 7, 64-bit, although MyQuery itself is still a 32-bit windows application.

Happy SQL'ing
/Karlsson

Tuesday, February 16, 2016

Loading JSON into MariaDB or even MySQL - mysqljsonimport 2.0 is available

It was a long time since I updated mysqljsonimport or mysqljsonexport and I had a few things I wanted to do with them. This release is significant enough for me to bump it up to 2.0, and the same is in the works for mysqljsonexport. The one big thing that is now implemented is reasonably advanced support for MariaDB Dynamic Columns, and it is actually pretty flexible, allowing you to load a nested JSON object into a MariaDB Dynamic Column. But don't worry, it will still link and run with MySQL if that is what you want to do (but then you will not have the dynamic column features, for obvious reasons),

Download from Sourceforge as usual (yes, I know I am oldfashioned and that I should have put it on github). Also as usual is the documentation in pdf format that is also downloadable separately.

/Karlsson

Monday, December 14, 2015

Getting started with MariaDB on IBM POWER 8

IBM POWER 8 is latest generation of the IBM POWER series, and it's a hot one. Above all, for you reading this, POWER 8 is the most Linux friendly so far and IBM really wants you to try this out. Seveal Linux distributions are supporting POWER 8 now, and MariaDB is of course the database of choise. Some cools things with the POWER 8 architecture are the support for CAPI (google for more details) and the fact that POWER 8 machines, due to a vastly superior memory architecture, can grow in memory size, which in general is good news but if you want your own POWER 8, this makes then a bit expensive (although maybe not when you consider the performance you get). IBM has fixed that recently and have announced the LC series of servers which start at $6.600 (see more here: http://www-03.ibm.com/systems/power/hardware/linux-lc.html).

So, whar about MariaDB then? Well, MariaDB is a standard component in the Linux distributions that support IBM POWER 8, but we here at MariaDB didn't stop there. We have made numerous fixes to MariaDB to make it perfom at it's best and to increase stability even more. To get at those nice additions and enhancements though, you have to run with the latest MariaDB versions and use the binary builds we provide, and here I'll show you how to do that.

To begin with, you have to register with MariaDB.com, which is free if you want to try MariaDB Enterprise. So surf to MariaDB.com and you should get something like this:
On the top right, as indicated above, are "Login" and "Sign up" links. Select the last of these two and follow the procedure. Then you can revsit this page and log in, and the the links at the top right now say "Logout", "My Portal" and "Profile". Click on the "My Portal" link and you get to a page that looks like this:
Above is indicated the "Downloads" tab, click on this and you will be taken to a page with many different download sections. In this case, let's assume you are on Ubuntu, then select this:
Now it is time to do the actual installation, just follow the steps listed on the page, as I write this, this means I will run:
wget https://downloads.mariadb.com/enterprise/dnae-wefq/generate/10.0/mariadb-enterprise-repository.deb
 dpkg -i mariadb-enterprise-repository.deb
 sudo apt-get update
sudo apt-get install mariadb-server
Following all this. MariaDB is now up and running, but I see you asking yourself, what about POWER 8? How do I install MariaDB on that? And fact is, that is exactly what we have dne here. Or Intel x86 for that matter, the procedure is exactky the same, the differences are handles behind the scenes.

Happy power hacking, I will get back soon with a writeup on MaxScale on POWER 8
/Karlsson

Wednesday, August 26, 2015

MyOraDump, Oracle dump utility, version 1.2

I have now released version 1.2 of MyOraDump, my Oracle data extraction tool. This version has one new feature, which is transaction support for MySQL format exports which does speed up loading data a lot! Trust me, it really does! Also I have fixed a bug that did cause a crash at the end of the run, I have no idea why this didn't show up before, but there you go and now it is fixed.

MyOraDump 1.2 can be downloaded sourceforge, and as usual there is also a pdf only download if you want to read up on the tool before using it.

/Karlsson

Friday, August 7, 2015

Oracle dump utility version 1.1

Today I released version 1.1 of myoradump for download from sourceforge. If you don't know what myoradump is, this is a utility for exporting data from an Oracle database in some relevant text format so that it can be imported to some other database.

The main thing in version 1.1 is that I have added a whole bunch of new output formats, so make it even easier to get your data out of expensive Oracle and into something more effective. The new formats supported are:
  • MySQL - The format of this is a bunch of INSERT statements that you get when you use mysqldump for example and is useful for import into MariaDB (and MySQL). INSERT arrays are supported as a bunch of more options.
  • JSON - This format is rather obvious, the output is a file consisting of one JSON object per row. To support binary data, which is a no-no in JSON, base64 encoding of binary data is also supported.
  • JSON Array - The format is similar to JSON, but instead of separate objects per row, this format consists of one or more JSON arrays of JSON objects.
  • HTML - This format will produce a valid HTML TABLE. This is sometimes useful when you want to view output data that includes UTF8 characters for example.
In additions, this version of  myoradump includes a bunch of new features and bug fixes. I will follow up this post with one that includes some specific examples of using myoradump eventually.

So, don't touch that dial!
/Karlsson

Friday, June 26, 2015

Oracle dump utility

I have created a small program to extract data from an Oracle database in a format suitable for importing into MariaDB (and MySQL I guess). It is pretty fast as it is written in C and uses the OCI interface. Also, it supports most of the Oracle basic types, including BLOB, CLOB and LONG. UTF8 is also supported and there are many generic features, as well as some features specific for later import into MariaDB.

Download the 1.0 version from Sourceforge where the programs source, that is using autotools for building, as well as documentation is available. I have not tested to build on any other version of Oracle than 11, but maybe someone could help me there.

/Karlsson