Setting up a clustered MariaDB with Keepalived and HAProxy on debian 7 wheezy

This setup with utilize 5 servers. (you can get away with 4 but do your research on a 2 node MariaDB cluster) I'm using 3 freshly minted VM machines running debian 7 wheezy.

Cluster Node 1
hostname mariadb-00
IP address 10.0.10.10

Cluster Node 2
hostname mariadb-01
IP address 10.0.10.11

Cluster Node 3
hostname mariadb-02
IP address 10.0.10.12

Step 1 - Setting up MariaDB Servers Adding the MariaDB repository and installing the prerequisites

apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
add-apt-repository 'deb http://mirror.aarnet.edu.au/pub/MariaDB/repo/10.0/debian wheezy main'
apt-get update
apt-get install python-software-properties rsync
apt-get install mariadb-galera-server galera

Step 2 - Setting up MariaDB security The default installation is not very secure so we can improve on that. Start the mysqld service (yes MariaDB is still called mysql) on all nodes.

service mysql start

Next we need to run the mysql_secure_installation script so we can improve the security.

/usr/bin/mysql_secure_installation

Go through the script and note down your new root password if you changed it.

Step 3 – Setup MariaDB Galera Cluster users Now we need to setup some users that must be able to access the database. The ‘sst_user’ is the user which a database node will use for authenticating to another database node in the State Transfer Snapshot (SST) phase. Execute the following command on all nodes:

mysql -u root -p
mysql> DELETE FROM mysql.user WHERE user='';
mysql> GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'dbpass';
mysql> GRANT USAGE ON *.* to sst_user@'%' IDENTIFIED BY 'dbpass';
mysql> GRANT ALL PRIVILEGES on *.* to sst_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> quit

Please not that the ‘%’ means that the root or sst_user is allowed to access the database from any host. For production make it more secure by specifying the hostname(s) or IP addresses from which those users can access the database. Once the software has been installed, we can create the Galera configuration file: /etc/mysql/conf.d/cluster.cnf

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0
 
# Galera Provider Configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"
 
# Galera Cluster Configuration
wsrep_cluster_name="CLUSTERNAME"
wsrep_cluster_address="gcomm://IPOFNODE,IPOFOHTERNODE"
 
# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass
 
# Galera Node Configuration
wsrep_node_address="CURRENTNODEIP"
wsrep_node_name="CURRNETNODE"

Change the following lines to suit your configuration

wsrep_cluster_name="CLUSTERNAME"
wsrep_cluster_address="gcomm://IPOFNODE,IPOFOTHERNODE"
wsrep_node_address="CURRENTNODEIP"
wsrep_node_name="CURRNETNODE"

Notice that we need to set the IP addresses for the wsrep_cluster_address to match the addresses of our two nodes, then on each node, we set use the local hostname and IP address in the last two lines. Complete all of the above steps on both nodes, and then we should stop the mysql service on both nodes:

service mysql stop

Now we need to copy the contents of /etc/mysql/debian.cnf from node 1 to node 2 (there are some passwords in that file that need to match across both nodes once we’ve got them clustered). Once that’s complete, we can create the cluster. On the first node, we start the mysql service with as special argument to create the new cluster:

service mysql start --wsrep-new-cluster

And on the second node, we start the usual way:

service mysql start

We will need to allow our HAProxy nodes access to the MariaDB servers for health checks. On one of the MySQL nodes, logon to MySQL as root and enter the following:

mysql -u root -p
grant all on *.* to root@'%' identified by 'password' with grant option;
insert into mysql.user (Host,User) values ('192.168.1.30','haproxy');
insert into mysql.user (Host,User) values ('192.168.1.31','haproxy');
flush privileges;
exit

Notice that I specified the IP addresses of my HAProxy nodes, and I used the username ‘haproxy’. We don't' set a password for the haproxy user as it is restricted to your HAProxy hosts only. Setting up HAProxy and keepalived For this configuration, I’ve created two Ubuntu 14.04 servers (mine are virtual servers with two virtual cpus and 1 GB of RAM). You’d probably want to make these bigger in a production environment, depending on the number of concurrent connections you expect. I’ve given them hostnames and IP addresses: haproxy1 (192.168.1.30) haproxy2 (192.168.1.31) We’ll also need to allocate a third IP address to use as the virtual IP address (VIP). We’ll use 192.168.1.32. This will ultimately be the endpoint used to access the OpenStack services that we’ll build later. The first thing we need to do is to let the kernel know that we intend to bind additional IP addresses that won’t be defined in the interfaces file. To do that we edit /etc/sysctl.conf and add the following line: /etc/sysctl.conf

net.ipv4.ip_nonlocal_bind=1

Then we run the following command to make this take effect without rebooting:

sysctl -p

To install HAproxy on Debian Wheezy, you have to go through backports. First add backports in /etc/apt/sources.list :

deb http://ftp.debian.org/debian/ wheezy-backports main

Then install haproxy:

apt-get update
apt-get install haproxy keepalived mysql-client

Next, we define the keepalived configuration by creating the following file: /etc/keepalived/keepalived.conf

global_defs {
  router_id haproxy1
}
vrrp_script haproxy {
  script "killall -0 haproxy"
  interval 2
  weight 2
}
vrrp_instance 50 {
  virtual_router_id 50
  advert_int 1
  priority 101
  state MASTER
  interface eth0
  virtual_ipaddress {
    192.168.1.32 dev eth0
  }
  track_script {
    haproxy
  }
}

Notice there’s a few specific items that we need to set for this. I’ve set the router_id to be the hostname, and I’ve specified the VIP as 192.168.1.32. When you create this file on the second node, make sure to use the hostname of the second node. Next, we will define the HAProxy configuration: /etc/haproxy/haproxy.cfg

global
        log /dev/log    local0
        chroot /var/lib/haproxy
        stats socket /run/haproxy/admin.sock mode 660 level admin
	user haproxy
	group haproxy
	daemon
 
defaults
	log	global
	option	dontlognull
        contimeout 5000
        clitimeout 50000
        srvtimeout 50000
	errorfile 400 /etc/haproxy/errors/400.http
	errorfile 403 /etc/haproxy/errors/403.http
	errorfile 408 /etc/haproxy/errors/408.http
	errorfile 500 /etc/haproxy/errors/500.http
	errorfile 502 /etc/haproxy/errors/502.http
	errorfile 503 /etc/haproxy/errors/503.http
	errorfile 504 /etc/haproxy/errors/504.http
 
listen stats 192.168.1.30:80
        mode http
        option httplog
        stats enable
        stats uri /stats
        stats realm HAProxy\ Statistics
        stats auth admin:password
 
listen galera 103.29.172.121:3306
        balance source
        mode tcp
        option tcpka
        option mysql-check user haproxy
        server em-mariadb-00 103.29.172.124:3306 check weight 1
        server em-mariadb-01 103.29.172.125:3306 check weight 1

Notice that I’ve used the local IP address in the file in two locations, in the global section for the log location, and in the stats listener. When you setup the second node, make sure to use its IP address. Also notice the username and password in the status auth line. Set this to whatever you want. Then, you will be able to access the stats page via your browser. Now we need to enable HAProxy. To do this, edit the file /etc/default/haproxy and change ENABLED from 0 to 1: /etc/default/haproxy

# Set ENABLED to 1 if you want the init script to start haproxy.
ENABLED=1
# Add extra flags here.
#EXTRAOPTS="-de -m 16"

Now we can restart the services:

service keepalived restart
service haproxy restart

Auto start after reboot: Edit Default /etc/default/haproxy

# Set ENABLED to 1 if you want the init script to start haproxy. 
ENABLED=1

Edit Init job: /etc/init.d/haproxy

HAPROXY=/usr/sbin/haproxy 
EXTRAOPTS= 
ENABLED=1


How to renew expired lotus domino administrator ID

Your only ID to access your lotus domino server has expired, below is how to renew that ID to gain access to your server again.

1. Open the Domino Administrator client. (The server should be set to "local".)
2. Select the Configuration tab.
3. Select Tools -> Certification -> Certify.
4. Select the certifier ID and enter the password. Set the server to Local.
Note: The following error may appear: "The public key that is being used does not match the one that was certified." This occurs because the client cannot connect to the Notes certifier document in the address book on the server. To continue past this error, select "Yes" when prompted with the following: "Do you wish to continue without updating the Certifier ID?"
5. Select the Notes/Admin ID to certify. Note: You will see an error: "Entry not found in index, Do you want to certify anyway?" Click Yes.
6. Ensure that the server is still set to "local" (at the top of the dialog), set the expiration date, and then click Certify.
7. At this point you should have access to the server, as long as public key checking is not enabled on the server. If public key checking is enabled on the server, you must complete step 8 before you can access the server.
8. Copy the public key from the ID into the Person document (Certificates tab -> Notes certified public key field.) File -> Security -> User Security (this opens the user ID) Select Your Identity -> Your Certificates Click the "Other actions" button and choose Mail, Copy Certificate (Public Key)... Select Copy Certificate (this will place the public key on the system clipboard) Close the open windows to exit User Security. Select the People view in the server's Domino Directory, open the user's Person document in Edit mode, and click the Certificates tab Select the entire contents of the Notes certified public key field and paste the key from the clipboard; save and close Rebuild the view by pressing the key combination Shift + F9.


Windows Server Group Edit missing Network, System, Desktop items.

I had a server today that in the Group Edit Policy was missing some of the location items.
To add them back is rather simple.

Open Group Edit (Start->Run->gpedit.msc)
Right click on the "Administrative Templates"
Click "Add/Remove Templates"
You will see a dialog box pop up listing the current installed templates.
Click "Add", this will open a location dialog defaulting to "Windows\system32\inf" folder, it will show a bunch of .adm files. From the list add in Conf.adm and System.adm.
Close the Add/Remove Templates dialog box and you should now have the required options.


Raspberry Pi running from USB hard drive

I wanted to be able to run my Pi to store logs, monitor some of my internal network etc. I didn't like the idea of continually writing to my SD card as we all know these will die over time. So below is how I setup my pi to boot from the SD card and run the whole system from an external USB hard drive. I'm using raspbian for the flavor of the OS on my Pi. Short version Install your selected OS on the SD Card and boot. Plug in external USB hard drive. Partition and Format hard drive. Copy system to hard drive. Mount the new system adjust the configuration and reboot. Long version Don't plug your USB hard drive in yet. Install Raspbian in the usual way to your SD Card. Boot your Pi and make sure it is updated to the current.
apt-get update
apt-get upgrade
rpi-update
reboot
Login to your Pi after the reboot
fdisk -l
note the output, now plugin your new usb hard drive.
fidsk -l
This should show another HDD something like /dev/sda Now we need to create and setup our partition table on our hard drive.
fdisk /dev/sda
Enter "p" will list any existing partitions. Delete any partitions by entering "n" and the the number of the partition to delete. Now we will create a small swap partition and a partition for the OS. While still in fdisk. Create our swap partition. Enter "n" to create a partition, "p" to select primary partition and then "1" to select the partition number one. Select the default start sector. Enter +2G to specify the size. Enter "t" to set the type of a partition. Enter "1" and then "82" to make this one a swap partition. Create the root partition Enter "n" to create a partition, "p" to select primary partition and then "2" to select the partition number two. Select the default start sector and default end sector to fill up the rest of the disk. Enter "p" to list the partitions and verify things are like you expect. Enter "w" to write the new partition table and exit. Initialize the swap partition.
mkswap /dev/sda1
There is no need to format the root partition since we are going to do a raw copy of an existing file system over to it. Copy the second partition of the SD card to the second partition of your hard disk (mmcblk0p2 may not be the correct one for you to copy, use your fdisk -l output from earlier).
dd if=/dev/mmcblk0p2 of=/dev/sda2 bs=32M conv=noerror,sync
This will take sometime to copy over. Once completed check the file system for errors.
e2fsk -f /dev/sda2
Press "y" if any errors are encountered. The file system that we copied still looks like a small one to the system. We will need to resize the root filesystem. to file the partition we created.
resize2fs /dev/sda2
Again this will take some time to run. Now we modify the boot configuration to use the new root partition on the hard drive.
cp /boot/cmdline.txt /boot/cmdline.txt.orig
vi /boot/cmdline.txt
Change the text
/dev/mmcblk0p2     /
to be
/dev/sda2          /
Update fstab for the new mount configurations. To do this we have to mount the usb hard disk to modify the /etc/fstab file.
mount /dev/sda2 /mnt
vi /mnt/etc/fstab
Change the text
/dev/mmcblk0p2
to be
/dev/sda2
Add the following line at the bottom.
/dev/sda1     none     swap     sw          0     0
Finally stop the system from using the SD for the system swap file.
rm /mnt/etc/rc2.d/S02dphys-swapfile
sync
reboot
If all goes to plan your Pi should now only use the SD card for the intial boot and everything else operates off the usb hard drive. Some USB hard drives require a delay to spin up on start up, you may have to add bootdelay and or rootdelay options to your /boot/cmdline.txt One other issue is if you get "Volume was not properly unmounted. Some data may be corrupt. Please run fsck" you will need to reset the Dirty bit on the SD card.
cd ~
umount /boot
git clone http://daniel-baumann.ch/git/software/dosfstools.git
cd dosfstools
make
./fsck.fat -V /dev/mmcblk0p1
./fsck.fat -a /dev/mmcblk0p1
mount /boot
Cheers Adam