Derek Neely

...notes for thyself, but useful for all...

Galera Cluster Install


May 11, 2017 by derek

We recently deployed a Galera Cluster for one of our clients. The installation/configuration for me at the time was a bit new and there were several areas where one tutorial was right but lacked in another. So, here is some documentation of my own with regards to how we got ours stood up. Hope this helps!

For this example I'm using CentOS and MariaDB. From working through the tutorials and such these are the easiest and everything just plays nice together. I'm also assuming you're starting on a fresh install with no current MySQL or MariaDB installed. If so, you'll want to clear all of that out and off of there before continuing.

For this example we'll be setting up 3 nodes (10.0.0.11/12/13). You can do this with 2, but at some point during my research/reading I read that the best setup is to use 3 nodes so that you always have 2 and it better helps keep things straight with the data.

On all Database Nodes:

Add the Galera MariaDB support distro into the available repos:

#> vi /etc/yum.repos.d/MariaDB.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Write the file and update yum, install, start mariadb, and secure the installation:

#> yum update

#> yum install MariaDB-server MariaDB-client

#> systemctl start mariadb

#> systemctl enable mariadb

#> mysql_secure_installation

Login to the server and setup a root user that will be used between the cluster

#> mysql -u root -p

MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by 'MySecurePassword' WITH GRANT OPTION;

MariaDB [(none)]> flush privileges;

note: instead of using the '%' for everyone, you can secure this down to the given network or individual nodes. This is for demo purposes.

If you have SELinux enabled you'll need to disable SELinux for mysql so that it has the ability to open non-standard ports to handle the replication and such. 

#> semanage permissive -a mysqld_t

note: If you get a 'command not found' then you'll need to install the manager with: 

#> yum install policycoreutils-python

The Galera cluster uses some non-standard ports for a MySQL installation so we'll need to also open those up:

#> firewall-cmd --add-port=3306/tcp --permanent
#> firewall-cmd --add-port=4567/tcp --permanent
#> firewall-cmd --add-port=4568/tcp --permanent
#> firewall-cmd --add-port=4444/tcp --permanent
#> firewall-cmd --reload

In other posts not all these ports are opened. The standard one mentioned is the 4567. However, I had to also open up 4568 and the cluster would 'start' with a failure without port 4444 open. So, for our instsallation all 3 of these were needed for a 'clean' setup.

Alright, its time to configure the MariaDB to use the Galera module and join up with their other buddies. Below is the configuration I use and works well. Of course with these settings you can tweak and tune them as well as there are some addiional configuration directives that are not here but can be applied. Take a look at the documentation for all those. The links are at the bottom for those.

#> vi /etc/my.cnf.d/server.cnf

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so #Synchronous multi-master wsrep provider
wsrep_cluster_address='gcomm://10.0.0.11,10.0.0.12,10.0.0.13' #List of nodes by their IP
wsrep_cluster_name='my_galera_cluster_name'
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

# Allow server to accept connections on all interfaces.
bind-address=0.0.0.0

Write the file. And get the cluster up and going. If you haven't already you'll want to stop the MariaDB process on the server(s).

Not on all nodes:

Start the cluster. If this is the first node (no other nodes have started yet), you'll need to boostrap the cluster:

#> galera_new_cluster

On all other nodes that are joining the first you can just start the service normally:

#> systemctl start mysql

This stands true also for any scenario where you may have shut down all the nodes as well. 

If for whatever reason you kill mysql on all the servers, or shut them all down at the same time. The cluster will not start after booting them all back up. Try to take note as to which node went down last and you'll need to bootstrap on that node using galera_new_cluster. You can then start mysql on the other nodes after the last node down is the first node back up.

You can check the status of your cluster and watch nodes come in and out (as you reboot/restart, etc) by looking at:

mysql -u root -p'MySecurePassword' -e "show status like 'wsrep%';"

HAProxy

Now all of this is fine and dandy for having a cluster of DBs up and going. You can write to any of the 3 and see the data on the other 2. Awesome! But, what good is that if one node drops or you have a high load? Hello, HAProxy! Let's load balance this thing.

For this example we're only doing the database. If you need more from HAProxy for other services you'll need to alter this setup. But this works for us with the Galera cluster.

#> yum install haproxy

#> systemctl enable haproxy

#> vi /etc/haproxy/haproxy.cfg

global
    log         127.0.0.1 local2

    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon

    stats socket /var/lib/haproxy/stats

defaults
    mode                    tcp
    log                     global
    option                  dontlognull
    option                  redispatch
    retries                 3
    timeout queue           45s
    timeout connect         5s
    timeout client          1m
    timeout server          1m
    timeout check           10s
    maxconn                 3000

# HAProxy statistics backend
listen haproxy-monitoring *:80
    mode    http
    stats   enable
    stats   show-legends
    stats   refresh           5s
    stats   uri               /
    stats   realm             Haproxy\ Statistics
    stats   auth              monitor:AdMiN123
    stats   admin             if TRUE

listen my-database-cluster
    bind 0.0.0.0:3306
    mode tcp
    option mysql-check user haproxy_check
    balance roundrobin
    server      dbserver1     10.0.0.11:3306 check
    server      dbserver2     10.0.0.12:3306 check
    server      dbserver3     10.0.0.13:3306 check

At this point you can give it a shot and start haproxy. If you don't see it up and running with ps aux, then you have an issue. Take a look at systemctl status haproxy.service. Amongst the dump you'll see something like:

...Starting proxy my-database-cluster: cannot bind socket [0.0.0.0:3306]

This is good ole selinux protecting your system from having some other service try to use another processes standard port. So, we just need to tell it that its ok.

There are 2 ways to do this

#> setsebool -P haproxy_connect_any=1

OR more pain way

#> setenforce permissive

#> systemctl restart haproxy

#> grep haprox /var/log/audit/audit.log | audit2allow -M haproxy

#> semodule -i haproxy.pp

#> setenforce enforcing

Allow external sources to connect to this server on the MySQL port:

firewall-cmd --add-port=3306/tcp --permanent; firewall-cmd --reload

In the HAProxy config you'll notice a check: . This is a connection string for HAProxy to connect to the backend servers to determine that they are up/down. haproxy_check is our user in this case. They have no real permissions but are able to connect and verify each server is up. We need to add this user to the cluster.

Log in to any of them (they're clustered) and add the user:

CREATE USER 'haproxy_check'@'%';

I used the loose '%' sign again to ease. If you want to tighten this up you can. Just add a user per HAProxy server you'll use. We'll have 2 for redundancy when we're done.

Install and configure keepalived so that we can have a floating IP that we'll connect to. We'll go ahead and configure it on one haproxy server and then just clone that server, alter the configs and test.

#> yum install keepalived

#> systemctl enable keepalived

#> vi /etc/keepalived/keepalived.conf

global_defs {
   notification_email {
     # Add in the emails of folks that need to notified on a failure
     support@company.com
   }
   notification_email_from haproxy-server@company.com
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id GALERACLUSTER_HAPROXY
}

vrrp_script chk_haproxy {
  script   "pidof haproxy"
  interval 2
}

vrrp_instance DELTASTAGE_VIP {
    interface ens160
    virtual_router_id 42
    priority 100
    advert_int 1

    unicast_src_ip 10.0.0.8 # update to 10.0.0.9 on other node
    unicast_peer {
        10.0.0.9 # update to 10.0.0.8 on other node
    }

    authentication {
        auth_type PASS
        auth_pass 1111
    }

    virtual_ipaddress {
        10.0.0.10/24
    }

    track_script {
        chk_haproxy
    }
}

Start up keepalived:

#> systemctl start keepalived

You should be able to see the floating ip (.10) with ip addr show on one of the nodes (or at least the first node if you haven't cloned yet). Connect to this IP just as if its the MySQL/MariaDB server. Write some scripts, test, bounce the servers up and down and see how everything is flowing. Should have a nice redundant and high performance database cluster.

If you have any questions, see a mistake or just want to chat, ping ever on the Twitter-verse (@derekneely).

Resources:
http://galeracluster.com/
http://galeracluster.com/documentation-webpages/galerainstallation.html
https://geekdudes.wordpress.com/2015/07/18/setting-up-failover-cluster-for-mariadb-on-centos-7/
http://galeracluster.com/documentation-webpages/haproxy.html

 

Linux, System Administration, MariaDB, Galera Cluster

Apache Proxy Basic Auth to Backend Service


Apr 18, 2017 by derek


We recently needed to deploy a Docker container (Docker registry server) that requires basic auth to the backend service. The standard Apache config for proxying back to the Docker container worked fine for pushing and pulling images and browser based stuff we were doing.

ProxyPass / http://127.0.0.1:5000/
ProxyPassReverse / http://127.0.0.1:5000/

However, the issue we wound up having was tying in the registry APIs to a little tool kit we run to help visualize and browse the repositories and images we have on our registry. The issue we were having is we were unable to pass through the basic auth needed due to the initial OPTIONS request that is made when sending the Authorization header. So, besides the additional CORS stuff we had to enable in the Apache config we also needed to handle the proxying a little differently.

RewriteEngine  On
RewriteCond %{REQUEST_METHOD} !=OPTIONS
RewriteRule /(.*)  http://127.0.0.1:5000/$1 [P,L]
ProxyPassReverse / http://127.0.0.1:5000/

If you have any questions or need help, feel free to ping me on Twitter.

Security, Proxy, Web, Apache

Hosting with CherryPy


Apr 14, 2017 by derek


At work we've been working with GAE quite a bit for deploying apps for ourselves and customers. In addition we do a lot with Python/Django for these apps. 

As of recent when deploying our Django app out to GAE we've noticed sporadic 500/502s. By default GAE uses and 'prefers' to use gunicorn. Out of pure frustration we decided to switch up our WSGI server. I've used uWSGI for a good while when deploying a basic Nginx/Django site and always had good luck with it. However, getting uWSGI up and working out on GAE was a bit problematic for us. So, one of our engineers decided to try Waitress and CherryPy. His Waitress config appeared pretty straight forward and worked well. He then found a compairson of the two and it seemed to show CherryPy being the faster of the two, so we gave that a shot and seemed to be pretty quick. So, for now, we're sticking with it. However, the documentation for the 'latest' of CherryPy didn't seem to cover things very well when it came to the server configs. So, here are some of the configs we wound up using to get everything up and going. This is for both GAE and if you just want to run it directly on a server or a Docker container.

If you're running a Django site on GAE you just need to get the application up and running. No need for additional mapping for static files and such. With GAE your static files and such will be out on a CDN somewhere so no mapping is needed. 

server.py

from myapp.wsgi import application

import cherrypy

server_config = {
    'global': {
        'server.socket_host': '0.0.0.0', 
        'server.socket_port': 8080, 
        'server.thread_pool': 30, 
        'server.max_request_body_size': 10485760, # Limit body size to 10M in bytes
        'server.max_request_header_size': 512000, # Limit header size to 512K in bytes
        'server.socket_timeout': 60, 
        'log.screen': True,
        'log.error_file': '/tmp/myapp.error.log',
        'log.access_file': '/tmp/myapp.access.log',
        'environment': 'production',
    }
}

if __name__ == '__main__':
    cherrypy.server.unsubscribe()
    cherrypy.config.update(server_config)
    cherrypy.tree.graft(application, '/')
    cherrypy.server.subscribe()
    cherrypy.engine.start()
    cherrypy.engine.block()

Now in the app.yaml file you'll set your entry point to entrypoint: python3 server.py

So, that should set you straight for throwing the app out on GAE using CherryPy instead of the default gunicorn.

I however wanted to take this a little further and move my own site onto CherryPy. But, I don't host on GAE but instead on a GCE instance with Nginx proxying back to a uWSGI socket. But, when we start using a new tech at work I like to try and deploy it for myself to build my knowledge and confidence in the technology. So, I worked up 2 different means of hosting the site: Direct CherryPy server running out of its virtualenv and a Docker container. As of this point/post the site is running out of the Docker container.

First off though lets cover my my server.py

from derekneely.wsgi import application
from django.conf import settings

import cherrypy
import os

server_config = {
    'global': {
        'server.socket_host': '0.0.0.0',
        'server.socket_port': 8080,
        'server.thread_pool': 10,
        'log.screen': True,
        'log.error_file': '/tmp/derekneely.com.error.log',
        'log.access_file': '/tmp/derekneely.com.access.log',
        'environment': 'production',
    }
}

application_config = {
    '/': {
        'tools.staticdir.root': os.path.dirname(__file__)
    },
}

# OPTIONAL CONFIG FOR HOSTING STATIC FILES
static_config = {
    '/': {
        'tools.staticdir.on': True,
        'tools.staticdir.dir': settings.STATIC_ROOT,
        'tools.expires.on': True,
        'tools.expires.secs': 86400
    }
}

# OPTIONAL CONFIG FOR HOSTING STATIC FILES
media_config = {
    '/': {
        'tools.staticdir.on': True,
        'tools.staticdir.dir': settings.MEDIA_ROOT,
        'tools.expires.on': True,
        'tools.expires.secs': 86400
    }
}

if __name__ == '__main__':
    cherrypy.server.unsubscribe()
    cherrypy.config.update(server_config)

    cherrypy.tree.mount(None, '', application_config)
    cherrypy.tree.mount(None, '/static', static_config) # OPTIONAL
    cherrypy.tree.mount(None, '/media', media_config) # OPTIONAL

    cherrypy.tree.graft(application, '/')

    cherrypy.server.subscribe()
    cherrypy.engine.start()
    cherrypy.engine.block()

The items noted as # OPTIONAL are just that. I maintained this code in place so I had it as a reference for hosting static files through CherryPy. However, I put Nginx in front of the CherryPy server so I use Nginx to server up content out of /static and /media. Now with all that said and done we just need to fire up the server and get it to run when the server boots. This 'application' uses a virtualenv for all of its dependencies. And, I did not know this until just recent but, turns out you can run your application in its virtualenv without having to actually activate the virtualenv (i.e. env/bin/activate). You can just run the version of python that was installed with your virtualenv and it all magically works! In my case it was something like: /sites/derekneely.com/env/bin/python3.4 /sites/derekneely.com/server.py &

And to get this up and going at boot I did something like:

rc.local

/sites/derekneely.com/env/bin/python3.4 /sites/derekneely.com/server.py > /var/log/cherrypy.log

There's probably some other better fancier way of doing this using a startup script and such but for now this worked for me. My end goal was to get this moved over to a Docker image anyhow. Let's cover that now.

So, not much changed regarding my server.py. I did remove the static/media stuff because I'll use Nginx to do that for me. There are numerous Python/Django Docker examples out there and all most likely would need to be tweaked or tuned for your particular needs. But, below is the Dockerfile that worked for me to get this up and going.

FROM python:3.4-slim

RUN apt-get update && apt-get install -y \
      gcc \
      gettext \
      mysql-client libmysqlclient-dev \
      postgresql-client libpq-dev \
      sqlite3 \
      python3-pip \
      python3-dev \
      libpython3-dev \
      libtiff-dev \
      libjpeg-dev \
      zlib1g-dev \
      libfreetype6-dev \
      liblcms2-dev \
      libwebp-dev \
      tcl8.5-dev \
      tk8.5-dev \
      python-tk \
   && rm -rf /var/lib/apt/lists/*

ENV PYTHONPATH /usr/local/lib/python3.4/dist-packages/
ENV PATH $PYTHONPATH:$PATH

RUN /usr/bin/pip3 install --upgrade pip setuptools

ADD . /app
RUN /usr/bin/pip3 install -r /app/requirements.txt

CMD /usr/bin/python3.4 /app/server.py
EXPOSE 8080

Many of the items I'm installing were due to the various packages I have in requirements.txt. You may not need all of them and in fact I'm still working to tune it down myself.

After you build this particular image you can get it on over to your server and stand it up as such:

docker run --name derekneelycom \
    -d \
    --restart=always \
    -p 8081:8080 \
    -v /path/to/my/static:/app/static \
    -v /path/to/my/media:/app/media \
    derekneelycom

If you see any issues with any of these or have any additional questions feel free to ping me on Twitter @derekneely

 

Python, Docker, Nginx
Page 1 of 9 >>>

Social


Tweet Tweet Tweet


© 2016 Derek Neely