Author: Stephen R Lang

Setup central log server on CentOS 6

For security purposes, it is best practice to store all critical logs to a secured centralized syslog server, and keep the retention rate set for at least 1 year.

In this example, there are 4 servers:
syslog01 (192.168.1.200)
web01-local (192.168.1.201)
web02-local (192.168.1.202)
web03-local (192.168.1.203)

The goal here is to store all the system’s logs, as well as the individual Apache vhost’s logs to the central log server.

Install and perform initial configuration of rsyslog

On syslog01, confirm rsyslog is installed and running by:

[root@syslog01 ~]# yum install rsyslog
[root@syslog01 ~]# chkconfig rsyslog on
[root@syslog01 ~]# service rsyslog start

Open the firewall to allow inbound connections over port 514 to syslog01 by:

[root@syslog01 ~]# vim /etc/sysconfig/iptables
...
-A INPUT -s 192.168.1.0/24 -p tcp -m tcp --dport 514 -j ACCEPT
...
[root@syslog01 ~]# service iptables restart

Finally, create the directory that will be storing the logs:

[root@syslog01 ~]# mkdir -p /var/log/remote
[root@syslog01 ~]# chown root:root /var/log/remote
[root@syslog01 ~]# chmod 700 /var/log/remote

Determine log storage style on central log server

Here is where it needs to be decided how to store the logs on the central logging server. I am unaware of any recommend best practices as there are many ways to go about this. I’ll outline 4 common ways below:
– Option 1: Store each server’s logs in its own directory.
– Option 2: Store each server’s logs in its own directory with natural log rotation
– Option 3: Store each server’s logs in its own directory, broken down by program name
– Option 4: Store all server’s logs in a single file

Option 1: Store each server’s logs in its own directory

This will configure rsyslog to store the servers logs in a directory named after the remote server’s hostname. Log rotation will happen daily via logrotate.

To implement this, first create a backup of the existing /etc/rsyslog.conf, and create a new one as shown below. The parts I added or modified are in bold for reference:

[root@syslog01 ~]# mv /etc/rsyslog.conf /etc/rsyslog.orig
[root@syslog01 ~]# vim /etc/rsyslog.conf
# rsyslog v5 configuration file

# For more information see /usr/share/doc/rsyslog-*/rsyslog_conf.html
# If you experience problems, see http://www.rsyslog.com/doc/troubleshoot.html

#### MODULES ####

$ModLoad imuxsock # provides support for local system logging (e.g. via logger command)
$ModLoad imklog   # provides kernel logging support (previously done by rklogd)
#$ModLoad immark  # provides --MARK-- message capability

# Provides UDP syslog reception
$ModLoad imudp
# Moved below for RuleSets
#$UDPServerRun 514

# Provides TCP syslog reception
$ModLoad imtcp
# Moved below for RuleSets
#$InputTCPServerRun 514


#### GLOBAL DIRECTIVES ####

# Use default timestamp format
$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat

# File syncing capability is disabled by default. This feature is usually not required,
# not useful and an extreme performance hit
#$ActionFileEnableSync on

# Include all config files in /etc/rsyslog.d/
$IncludeConfig /etc/rsyslog.d/*.conf

$template RemoteHost, "/var/log/remote/%HOSTNAME%/syslog.log"

#### RULES ####

$RuleSet local

# Log all kernel messages to the console.
# Logging much else clutters up the screen.
#kern.*                                                 /dev/console

# Log anything (except mail) of level info or higher.
# Don't log private authentication messages!
*.info;mail.none;authpriv.none;cron.none                /var/log/messages

# The authpriv file has restricted access.
authpriv.*                                              /var/log/secure

# Log all the mail messages in one place.
mail.*                                                  -/var/log/maillog


# Log cron stuff
cron.*                                                  /var/log/cron

# Everybody gets emergency messages
*.emerg                                                 *

# Save news errors of level crit and higher in a special file.
uucp,news.crit                                          /var/log/spooler

# Save boot messages also to boot.log
local7.*                                                /var/log/boot.log

local0.*                                                /var/log/local0.log
local1.*                                                /var/log/local1.log
local2.*                                                /var/log/local2.log
local3.*                                                /var/log/local3.log
local4.*                                                /var/log/local4.log
local5.*                                                /var/log/local5.log
local6.*                                                /var/log/local6.log

# Log all messages also to the central log directory
*.* ?RemoteHost

# Bind the above ruleset as default
$DefaultRuleset local

# This uses the template above
$RuleSet remote
*.* ?RemoteHost

# Bind the above ruleset for remote logs
$InputUDPServerBindRuleset remote
$UDPServerRun 514
$InputTCPServerBindRuleset remote
$InputTCPServerRun 514


# ### begin forwarding rule ###
# The statement between the begin ... end define a SINGLE forwarding
# rule. They belong together, do NOT split them. If you create multiple
# forwarding rules, duplicate the whole block!
# Remote Logging (we use TCP for reliable delivery)
#
# An on-disk queue is created for this action. If the remote host is
# down, messages are spooled to disk and sent when it is up again.
#$WorkDirectory /var/lib/rsyslog # where to place spool files
#$ActionQueueFileName fwdRule1 # unique name prefix for spool files
#$ActionQueueMaxDiskSpace 1g   # 1gb space limit (use as much as possible)
#$ActionQueueSaveOnShutdown on # save messages to disk on shutdown
#$ActionQueueType LinkedList   # run asynchronously
#$ActionResumeRetryCount -1    # infinite retries if host is down
# remote host is: name/ip:port, e.g. 192.168.0.1:514, port optional
#*.* @@remote-host:514
# ### end of the forwarding rule ###

Then restart rsyslog:

[root@syslog01 ~]# service rsyslog restart

Now setup logrotate to rotate out the logs, compress them, and keep them on file for 1 year as shown below:

[root@syslog01 ~]# vim /etc/logrotate.d/centralrsyslog
/var/log/remote/*/*.log
{
daily
    rotate 365
    missingok
    daily
    compress
    delaycompress
    sharedscripts
    postrotate
        /bin/kill -HUP `cat /var/run/syslogd.pid 2> /dev/null` 2> /dev/null || true
    endscript
}

Finally, test the log rotation by running:

[root@syslog01 ~]# logrotate -f /etc/logrotate.d/centralrsyslog

Option 2: Store each server’s logs in its own directory with natural log rotation

Both the local and remote system logs will be stored in its own directory as follows:

/var/log/remote/2015-12_syslog01/syslog.conf
/var/log/remote/2015-12_web01-local/syslog.conf
/var/log/remote/2015-12_web02-local/syslog.conf
/var/log/remote/2015-12_web03-local/syslog.conf

As the months change, syslog will automatically create a new folder accordingly for that month, creating a type of natural log rotation.

To implement this, first create a backup of the existing /etc/rsyslog.conf, and create a new one as shown below. The parts I added or modified are in bold for reference:

[root@syslog01 ~]# mv /etc/rsyslog.conf /etc/rsyslog.orig
[root@syslog01 ~]# vim /etc/rsyslog.conf
# rsyslog v5 configuration file

# For more information see /usr/share/doc/rsyslog-*/rsyslog_conf.html
# If you experience problems, see http://www.rsyslog.com/doc/troubleshoot.html

#### MODULES ####

$ModLoad imuxsock # provides support for local system logging (e.g. via logger command)
$ModLoad imklog   # provides kernel logging support (previously done by rklogd)
#$ModLoad immark  # provides --MARK-- message capability

# Provides UDP syslog reception
$ModLoad imudp
# Moved below for RuleSets
#$UDPServerRun 514

# Provides TCP syslog reception
$ModLoad imtcp
# Moved below for RuleSets
#$InputTCPServerRun 514


#### GLOBAL DIRECTIVES ####

# Use default timestamp format
$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat

# File syncing capability is disabled by default. This feature is usually not required,
# not useful and an extreme performance hit
#$ActionFileEnableSync on

# Include all config files in /etc/rsyslog.d/
$IncludeConfig /etc/rsyslog.d/*.conf

$template RemoteHost, "/var/log/remote/%$YEAR%-%$MONTH%_%HOSTNAME%/syslog.log"

#### RULES ####

$RuleSet local

# Log all kernel messages to the console.
# Logging much else clutters up the screen.
#kern.*                                                 /dev/console

# Log anything (except mail) of level info or higher.
# Don't log private authentication messages!
*.info;mail.none;authpriv.none;cron.none                /var/log/messages

# The authpriv file has restricted access.
authpriv.*                                              /var/log/secure

# Log all the mail messages in one place.
mail.*                                                  -/var/log/maillog


# Log cron stuff
cron.*                                                  /var/log/cron

# Everybody gets emergency messages
*.emerg                                                 *

# Save news errors of level crit and higher in a special file.
uucp,news.crit                                          /var/log/spooler

# Save boot messages also to boot.log
local7.*                                                /var/log/boot.log

local0.*                                                /var/log/local0.log
local1.*                                                /var/log/local1.log
local2.*                                                /var/log/local2.log
local3.*                                                /var/log/local3.log
local4.*                                                /var/log/local4.log
local5.*                                                /var/log/local5.log
local6.*                                                /var/log/local6.log

# Log all messages also to the central log directory
*.* ?RemoteHost

# Bind the above ruleset as default
$DefaultRuleset local

# This uses the template above
$RuleSet remote
*.* ?RemoteHost

# Bind the above ruleset for remote logs
$InputUDPServerBindRuleset remote
$UDPServerRun 514
$InputTCPServerBindRuleset remote
$InputTCPServerRun 514


# ### begin forwarding rule ###
# The statement between the begin ... end define a SINGLE forwarding
# rule. They belong together, do NOT split them. If you create multiple
# forwarding rules, duplicate the whole block!
# Remote Logging (we use TCP for reliable delivery)
#
# An on-disk queue is created for this action. If the remote host is
# down, messages are spooled to disk and sent when it is up again.
#$WorkDirectory /var/lib/rsyslog # where to place spool files
#$ActionQueueFileName fwdRule1 # unique name prefix for spool files
#$ActionQueueMaxDiskSpace 1g   # 1gb space limit (use as much as possible)
#$ActionQueueSaveOnShutdown on # save messages to disk on shutdown
#$ActionQueueType LinkedList   # run asynchronously
#$ActionResumeRetryCount -1    # infinite retries if host is down
# remote host is: name/ip:port, e.g. 192.168.0.1:514, port optional
#*.* @@remote-host:514
# ### end of the forwarding rule ###

Then restart rsyslog:

[root@syslog01 ~]# service rsyslog restart

Option 3: Store each server’s logs in its own directory, broken down by program name

This one will break down the logs to store them in a directory named after the remote server’s hostname, and further more break them down by program name.

To implement this, first create a backup of the existing /etc/rsyslog.conf, and create a new one as shown below. The parts I added or modified are in bold for reference:

[root@syslog01 ~]# mv /etc/rsyslog.conf /etc/rsyslog.orig
[root@syslog01 ~]# vim /etc/rsyslog.conf
# rsyslog v5 configuration file

# For more information see /usr/share/doc/rsyslog-*/rsyslog_conf.html
# If you experience problems, see http://www.rsyslog.com/doc/troubleshoot.html

#### MODULES ####

$ModLoad imuxsock # provides support for local system logging (e.g. via logger command)
$ModLoad imklog   # provides kernel logging support (previously done by rklogd)
#$ModLoad immark  # provides --MARK-- message capability

# Provides UDP syslog reception
$ModLoad imudp
# Moved below for RuleSets
#$UDPServerRun 514

# Provides TCP syslog reception
$ModLoad imtcp
# Moved below for RuleSets
#$InputTCPServerRun 514


#### GLOBAL DIRECTIVES ####

# Use default timestamp format
$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat

# File syncing capability is disabled by default. This feature is usually not required,
# not useful and an extreme performance hit
#$ActionFileEnableSync on

# Include all config files in /etc/rsyslog.d/
$IncludeConfig /etc/rsyslog.d/*.conf

$template RemoteHost, "/var/log/remote/%HOSTNAME%/%PROGRAMNAME%.log"

#### RULES ####

$RuleSet local

# Log all kernel messages to the console.
# Logging much else clutters up the screen.
#kern.*                                                 /dev/console

# Log anything (except mail) of level info or higher.
# Don't log private authentication messages!
*.info;mail.none;authpriv.none;cron.none                /var/log/messages

# The authpriv file has restricted access.
authpriv.*                                              /var/log/secure

# Log all the mail messages in one place.
mail.*                                                  -/var/log/maillog


# Log cron stuff
cron.*                                                  /var/log/cron

# Everybody gets emergency messages
*.emerg                                                 *

# Save news errors of level crit and higher in a special file.
uucp,news.crit                                          /var/log/spooler

# Save boot messages also to boot.log
local7.*                                                /var/log/boot.log

local0.*                                                /var/log/local0.log
local1.*                                                /var/log/local1.log
local2.*                                                /var/log/local2.log
local3.*                                                /var/log/local3.log
local4.*                                                /var/log/local4.log
local5.*                                                /var/log/local5.log
local6.*                                                /var/log/local6.log

# Log all messages also to the central log directory
*.* ?RemoteHost

# Bind the above ruleset as default
$DefaultRuleset local

# This uses the template above
$RuleSet remote
*.* ?RemoteHost

# Bind the above ruleset for remote logs
$InputUDPServerBindRuleset remote
$UDPServerRun 514
$InputTCPServerBindRuleset remote
$InputTCPServerRun 514


# ### begin forwarding rule ###
# The statement between the begin ... end define a SINGLE forwarding
# rule. They belong together, do NOT split them. If you create multiple
# forwarding rules, duplicate the whole block!
# Remote Logging (we use TCP for reliable delivery)
#
# An on-disk queue is created for this action. If the remote host is
# down, messages are spooled to disk and sent when it is up again.
#$WorkDirectory /var/lib/rsyslog # where to place spool files
#$ActionQueueFileName fwdRule1 # unique name prefix for spool files
#$ActionQueueMaxDiskSpace 1g   # 1gb space limit (use as much as possible)
#$ActionQueueSaveOnShutdown on # save messages to disk on shutdown
#$ActionQueueType LinkedList   # run asynchronously
#$ActionResumeRetryCount -1    # infinite retries if host is down
# remote host is: name/ip:port, e.g. 192.168.0.1:514, port optional
#*.* @@remote-host:514
# ### end of the forwarding rule ###

Then restart rsyslog:

[root@syslog01 ~]# service rsyslog restart

Now setup logrotate to rotate out the logs, compress them, and keep them on file for 1 year as shown below:

[root@syslog01 ~]# vim /etc/logrotate.d/centralrsyslog
/var/log/remote/*/*.log
{
daily
    rotate 365
    missingok
    daily
    compress
    delaycompress
    sharedscripts
    postrotate
        /bin/kill -HUP `cat /var/run/syslogd.pid 2> /dev/null` 2> /dev/null || true
    endscript
}

Finally, test the log rotation by running:

[root@syslog01 ~]# logrotate -f /etc/logrotate.d/centralrsyslog

Option 4: Store all server’s logs in a single file

This is useful is you want to be able to use grep to find the information you need from a single file. Just keep in mind that if you have 10-20+ servers, this log file could get very large, even with a daily log rotation in place.

This will tell rsyslog to store ALL your servers logs in /var/log/remote/syslog.log.

To implement this, first create a backup of the existing /etc/rsyslog.conf, and create a new one as shown below. The parts I added or modified are in bold for reference:

[root@syslog01 ~]# mv /etc/rsyslog.conf /etc/rsyslog.orig
[root@syslog01 ~]# vim /etc/rsyslog.conf
# rsyslog v5 configuration file

# For more information see /usr/share/doc/rsyslog-*/rsyslog_conf.html
# If you experience problems, see http://www.rsyslog.com/doc/troubleshoot.html

#### MODULES ####

$ModLoad imuxsock # provides support for local system logging (e.g. via logger command)
$ModLoad imklog   # provides kernel logging support (previously done by rklogd)
#$ModLoad immark  # provides --MARK-- message capability

# Provides UDP syslog reception
$ModLoad imudp
# Moved below for RuleSets
#$UDPServerRun 514

# Provides TCP syslog reception
$ModLoad imtcp
# Moved below for RuleSets
#$InputTCPServerRun 514


#### GLOBAL DIRECTIVES ####

# Use default timestamp format
$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat

# File syncing capability is disabled by default. This feature is usually not required,
# not useful and an extreme performance hit
#$ActionFileEnableSync on

# Include all config files in /etc/rsyslog.d/
$IncludeConfig /etc/rsyslog.d/*.conf

$template RemoteHost, "/var/log/remote/syslog.log"

#### RULES ####

$RuleSet local

# Log all kernel messages to the console.
# Logging much else clutters up the screen.
#kern.*                                                 /dev/console

# Log anything (except mail) of level info or higher.
# Don't log private authentication messages!
*.info;mail.none;authpriv.none;cron.none                /var/log/messages

# The authpriv file has restricted access.
authpriv.*                                              /var/log/secure

# Log all the mail messages in one place.
mail.*                                                  -/var/log/maillog


# Log cron stuff
cron.*                                                  /var/log/cron

# Everybody gets emergency messages
*.emerg                                                 *

# Save news errors of level crit and higher in a special file.
uucp,news.crit                                          /var/log/spooler

# Save boot messages also to boot.log
local7.*                                                /var/log/boot.log

local0.*                                                /var/log/local0.log
local1.*                                                /var/log/local1.log
local2.*                                                /var/log/local2.log
local3.*                                                /var/log/local3.log
local4.*                                                /var/log/local4.log
local5.*                                                /var/log/local5.log
local6.*                                                /var/log/local6.log

# Log all messages also to the central log directory
*.* ?RemoteHost

# Bind the above ruleset as default
$DefaultRuleset local

# This uses the template above
$RuleSet remote
*.* ?RemoteHost

# Bind the above ruleset for remote logs
$InputUDPServerBindRuleset remote
$UDPServerRun 514
$InputTCPServerBindRuleset remote
$InputTCPServerRun 514


# ### begin forwarding rule ###
# The statement between the begin ... end define a SINGLE forwarding
# rule. They belong together, do NOT split them. If you create multiple
# forwarding rules, duplicate the whole block!
# Remote Logging (we use TCP for reliable delivery)
#
# An on-disk queue is created for this action. If the remote host is
# down, messages are spooled to disk and sent when it is up again.
#$WorkDirectory /var/lib/rsyslog # where to place spool files
#$ActionQueueFileName fwdRule1 # unique name prefix for spool files
#$ActionQueueMaxDiskSpace 1g   # 1gb space limit (use as much as possible)
#$ActionQueueSaveOnShutdown on # save messages to disk on shutdown
#$ActionQueueType LinkedList   # run asynchronously
#$ActionResumeRetryCount -1    # infinite retries if host is down
# remote host is: name/ip:port, e.g. 192.168.0.1:514, port optional
#*.* @@remote-host:514
# ### end of the forwarding rule ###

Then restart rsyslog:

[root@syslog01 ~]# service rsyslog restart

Now setup logrotate to rotate out the logs, compress them, and keep them on file for 1 year as shown below:

[root@syslog01 ~]# vim /etc/logrotate.d/centralrsyslog
/var/log/remote/*/*.log
{
daily
    rotate 365
    missingok
    daily
    compress
    delaycompress
    sharedscripts
    postrotate
        /bin/kill -HUP `cat /var/run/syslogd.pid 2> /dev/null` 2> /dev/null || true
    endscript
}

Finally, test the log rotation by running:

[root@syslog01 ~]# logrotate -f /etc/logrotate.d/centralrsyslog

Setup clients to forward their logs to your central log server

For this, I am opting to use TCP. Append the following at the bottom of /etc/rsyslog.conf on each client server:

[root@syslog01 ~]# vim /etc/rsyslog.conf
*.* @@192.168.1.200:514

Then restart Rsyslog:

[root@syslog01 ~]# service rsyslog restart

Apache logs

If there was a requirement within the organization to also send Apache error logs access logs to the central syslog server, that can be done pretty easily. However as these can get very large, make sure you have enough disk space on your central syslog server to handle this.

Apache does not have the ability to natively send over the logs, so logger must be used. The following would need to be done on each vhost configured for both 80 and 443. Be sure to change the tags for each site accordingly in the logger entry below:

[root@syslog01 ~]# vim /etc/httpd/vhost.d/example.com.conf
...
CustomLog "|/usr/bin/logger -p local0.info -t example.com-access" combined
ErrorLog "|/usr/bin/logger -p local0.error -t example.com-error"
...

Restart Apache when done

[root@syslog01 ~]# service httpd restart

Additional RemoteHost templates

As seen in the examples above, there are a number of ways you can work with the directories created. Some common ones that I know of are below:

Each folder is labeled with the hostname it came from:

$template RemoteHost, "/var/log/remote/%HOSTNAME%/syslog.log"

Each folder is labeled with the hostname and IP address it came from:

$template RemoteHost, "/var/log/remote/%HOSTNAME%-%fromhost-ip%/syslog.log"

Each folder is labeled with the hostname, and break down the logs within by program name:

$template RemoteHost, "/var/log/remote/%HOSTNAME%/%PROGRAMNAME%.log"

Each folder is labeled with the year, month and hostname, creating a type of natural log rotation:

$template RemoteHost, "/var/log/remote/%$YEAR%-%$MONTH%_%HOSTNAME%/syslog.log"

Backing up MySQL with Holland

Taken directly from the vendors website, Holland is an Open Source backup framework originally developed at Rackspace and written in Python. Its goal is to help facilitate backing up databases with greater configurability, consistency, and ease. Holland is capable of backing up other types of data, too. Because of its plugin structure, Holland can be used to backup anything you want by whatever means you want.

Notable Features
– Pluggable Framework
– Supports Multiple Backup Sets
– Database and Table Filtering (Using GLOBs)
– Auto-Detection of Transactional DBs
– Safe use of –single-transaction with mysqldump
– In-Line and Pluggable Compression
– Backups Suitable for Point-In-Time Recovery / Replication
– MySQL + LVM Snapshot and Logical Backups
– PostgreSQL backups using pgdump

Website: http://hollandbackup.org
Documentation: http://docs.hollandbackup.org

How to install Holland on Ubuntu / Debian

As the packages don’t exist in the distro’s repositories, pull them from the official repositories.

This works on the followings OS’s:
– Ubuntu 12.04
– Ubuntu 14.04
– Ubuntu 14.10
– Ubuntu 16.04
– Debian 7
– Debian 8

You can setup the repo by running the following on the commandline as root:

eval $(cat /etc/os-release)
 
DIST="xUbuntu_${VERSION_ID}"
[ $ID == "debian" ] && DIST="Debian_${VERSION_ID}.0"
 
curl -s http://download.opensuse.org/repositories/home:/holland-backup/${DIST}/Release.key | sudo apt-key add -
echo "deb http://download.opensuse.org/repositories/home:/holland-backup/${DIST}/ ./" > /etc/apt/sources.list.d/holland.list

Then install Holland:

apt-get update
apt-get install holland holland-mysqldump holland-common

How to install Holland on CentOS and RedHat

The holland packages exist in the EPEL repositories. Using the list below, install the EPEL repo for your distro:

# CentOS 5 / RedHat 5
rpm -ivh http://dl.fedoraproject.org/pub/epel/5/x86_64/epel-release-5-4.noarch.rpm
 
# CentOS 6 / RedHat 6
rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

# CentOS 7 / RedHat 7
rpm -ivh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm

Then install Holland by:

yum install holland holland-mysqldump holland-common

How to configure Holland

Now that Holland is installed, its now time to configure it.

First create the backup directory where you will be storing your database backups:

mkdir -p /var/lib/mysqlbackup

Then configure holland to store the backups in the directory created above:

vim /etc/holland/holland.conf
...
backup_directory = /var/lib/mysqlbackup
backupsets = default
...

Next we setup default backupset within Holland that will control the retention period, compression, credentials, etc:

cat << EOF > /etc/holland/backupsets/default.conf
 
## Default Backup-Set
##
## Backs up all MySQL databases in a one-file-per-database fashion using
## lightweight in-line compression and engine auto-detection. This backup-set
## is designed to provide reliable backups "out of the box", however it is
## generally advisable to create additional custom backup-sets to suit
## one's specific needs.
##
## For more inforamtion about backup-sets, please consult the online Holland
## documentation. Fully-commented example backup-sets are also provided, by
## default, in /etc/holland/backupsets/examples.
 
[holland:backup]
plugin = mysqldump
backups-to-keep = 7
auto-purge-failures = yes
purge-policy = after-backup
estimated-size-factor = 1.0
 
# This section defines the configuration options specific to the backup
# plugin. In other words, the name of this section should match the name
# of the plugin defined above.
[mysqldump]
file-per-database       = yes
#lock-method        = auto-detect
#databases          = "*"
#tables             = "*"
#stop-slave         = no
#bin-log-position   = no
 
# The following section is for compression. The default, unless the
# mysqldump provider has been modified, is to use inline fast gzip
# compression (which is identical to the commented section below).
[compression]
method = gzip
options = "--rsyncable"
 
[mysql:client]
defaults-extra-file       = /root/.my.cnf
EOF

In order for Holland to backup the databases, setup the /root/.my.cnf by running:

cat << EOF > /root/.my.cnf
[client]
user=root
password=your_root_mysql_password_here
EOF

Now setup the nightly cronjob that will run Holland:

cat << EOF > /etc/cron.d/holland
30 3 * * * root /usr/sbin/holland -q bk
EOF

Finally, run the backup job to ensure Holland works. Please note this will lock your tables when it runs, so do not run this during peak times as it could cause downtime for your site or application!

/usr/sbin/holland -q bk

You can verify Holland ran successfully by checking the logs:

tail -f /var/log/holland/holland.log
2015-12-16 03:58:01,789 [INFO] Wrote backup manifest /var/lib/mysqlbackup/default/20151216_035801/backup_data/MANIFEST.txt
2015-12-16 03:58:01,793 [INFO] Executing: /usr/bin/mysqldump --defaults-file=/var/lib/mysqlbackup/default/20151216_035801/my.cnf --flush-privileges --max-allowed-packet=128M --lock-tables mysql
2015-12-16 03:58:01,888 [ERROR] /usr/bin/mysqldump[25783]: -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
2015-12-16 03:58:01,888 [INFO] Final on-disk backup size 181.77KB
2015-12-16 03:58:01,889 [INFO] 26.47% of estimated size 686.67KB
2015-12-16 03:58:01,889 [INFO] Backup completed in 0.24 seconds
2015-12-16 03:58:01,902 [INFO] Purged default/20151209_035801
2015-12-16 03:58:01,902 [INFO] 1 backups purged
2015-12-16 03:58:01,909 [INFO] Released lock /etc/holland/backupsets/default.conf
2015-12-16 03:58:01,909 [INFO] --- Ending backup run ---

Holland Tips and Tricks

Below are some of the more common tips and tricks for working with Holland. There are broken down as follows:
1. Alternate username for Holland
2. Change Default Retention
3. Hourly Backups
4. Backing up 2 or more database servers

1. Alternate username for Holland

For security purposes, a client may want to have a user, other then root, performing the Holland backups. To configure this, first create a new MySQL user for Holland:

mysql
GRANT SELECT, RELOAD, SUPER, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW ON *.* TO 'holland_backup'@'localhost' IDENTIFIED BY 'secretpassword';
flush privileges;

Now update /etc/holland/backupsets/default.conf to reflect the new credentials:

vi /etc/holland/backupsets/default.conf
...
[mysql:client]
# defaults-file       = /root/.my.cnf
user = holland_backup
password = secretpassword
...

2. Change Default Retention

This guide sets Holland to keep 7 backups of your databases. Please note that this does not mean 7 days as it makes the assumption that you are only running one Holland backup job each night.

With that said, if you wanted to configure a retention of 14 days, you would update “backups-to-keep” in /etc/holland/backupsets/default.conf as shown below:

vi /etc/holland/backupsets/default.conf
...
[holland:backup]
plugin = mysqldump
backups-to-keep = 14
auto-purge-failures = yes
...

3. Hourly Backups

You can configure Holland to be more aggressive with how often it runs. Assuming we have a MySQL Slave server with a small database, and we want
– Holland backups every 4 hours
– 7 day retention

You would first update the cronjob as follows:

vi /etc/cron.d/holland
30 */4 * * * root /usr/sbin/holland -q bk

Now update the “backups-to-keep” in /etc/holland/backupsets/default.conf to keep 42 backups (6 backups a day * 7 days) as shown below

vi /etc/holland/backupsets/default.conf
...
[holland:backup]
plugin = mysqldump
backups-to-keep = 42
auto-purge-failures = yes
...

4. Backing up 2 or more database servers

An environment may have two or more database servers. Perhaps they exist on another server, or perhaps they are using something like Rackspace Cloud Database, or Amazon RDS. Below is how you can configure Holland to backup multiple targets:

First, create a copy of the default.conf we configured in this guide to serve as a starting point:

cd /etc/holland/backupsets/
cp default.conf clouddatabase.conf

Now update the clouddatabase.conf to configure the remote database server IP and credentials. You must comment out the defaults-extra-file as shown below:

vim clouddatabase.conf

...
[mysql:client]
#defaults-extra-file = /root/.my.cnf

# define external database information

host     = xxxxxxxxx.rackspaceclouddb.com
user     = holland
password = your_holland_mysql_password_here
port     = 3306
...

Now update your retention settings:

vi /etc/holland/backupsets/clouddatabase.conf
...
[holland:backup]
plugin = mysqldump
backups-to-keep = 7
auto-purge-failures = yes
...

Finally, update the holland.conf to include your second config file:

vim /etc/holland/holland.conf
...
backupsets = default, clouddatabase
...

Finally, run the backup job to ensure Holland works. Please note this will lock your tables which it runs, so do not run this during peak times as it could cause downtime for your site or application!

/usr/sbin/holland -q bk

How to install and configure Lsyncd

People looking to create a load balanced web server solution often ask, how can they keep their web servers in sync with each other? There are many ways to go about this: NFS, lsync, rsync, etc. This guide will discuss using Lsyncd to keep their slave web servers in sync every 20 seconds.

Taken directly from the vendors website, Lsyncd watches a local directory trees event monitor interface (inotify or fsevents). It aggregates and combines events for a few seconds and then spawns one (or more) process(es) to synchronize the changes. By default this is rsync. Lsyncd is thus a light-weight live mirror solution that is comparatively easy to install not requiring new filesystems or blockdevices and does not hamper local filesystem performance.

This article will be broken down by the following operating systems, as each has some minor catches. Simply scroll down to your desired operating system listed below:
CentOS 6 – How To Install And Configure Lsyncd
CentOS 7 – How To Install And Configure Lsyncd
Ubuntu 12.04 – How To Install And Configure Lsyncd
Ubuntu 14.04 – How To Install And Configure Lsyncd

CentOS 6 – How To Install And Configure Lsyncd
Install Lsyncd via yum. Please note, this will automatically setup:
– Lsyncd 2.1.5
– /etc/logrotate.d/lsyncd
– /etc/init.d/lsyncd
– Provide a place holder for /etc/lsyncd.conf

Install it by running:

yum -y install lsyncd
chkconfig lsyncd on

Now setup the lsyncd configuration by:

vim /etc/lsyncd.conf
 
settings {
   logfile = "/var/log/lsyncd/lsyncd.log",
   statusFile = "/var/log/lsyncd/lsyncd-status.log",
   statusInterval = 20
}
servers = {
 "x.x.x.x",
 "x.x.x.x",
 "x.x.x.x"
}
 
for _, server in ipairs(servers) do
sync {
    default.rsyncssh,
    source="/var/www/",
    host=server,
    targetdir="/var/www/",
    excludeFrom="/etc/lsyncd-excludes.txt",
    rsync = {
        compress = true,
        archive = true,
        verbose = true,
        rsh = "/usr/bin/ssh -p 22 -o StrictHostKeyChecking=no"
    }
}
end

Create the place holder for lsyncd-excludes.txt

touch /etc/lsyncd-excludes.txt

Finally, start the service

chkconfig lsyncd on
service lsyncd start

CentOS 7 – How To Install And Configure Lsyncd

Install Lsyncd via yum. Please note, this will automatically setup
– Lsyncd 2.1.5
– /etc/logrotate.d/lsyncd
– /etc/init.d/lsyncd
– Provide a place holder for /etc/lsyncd.conf

Install it by running

yum -y install lsyncd
systemctl enable lsyncd.service

Now setup the lsyncd configuration by:

vim /etc/lsyncd.conf
 
settings {
   logfile = "/var/log/lsyncd/lsyncd.log",
   statusFile = "/var/log/lsyncd/lsyncd-status.log",
   statusInterval = 20
}
servers = {
 "x.x.x.x",
 "x.x.x.x",
 "x.x.x.x"
}
 
for _, server in ipairs(servers) do
sync {
    default.rsyncssh,
    source="/var/www/",
    host=server,
    targetdir="/var/www/",
    excludeFrom="/etc/lsyncd-excludes.txt",
    rsync = {
        compress = true,
        archive = true,
        verbose = true,
        rsh = "/usr/bin/ssh -p 22 -o StrictHostKeyChecking=no"
    }
}
end

Create the place holder for lsyncd-excludes.txt and /var/log/lsyncd:

touch /etc/lsyncd-excludes.txt
mkdir /var/log/lsyncd

Finally, start the service

systemctl start lsyncd.service

Ubuntu 12.04 – How To Install Lsyncd

Install Lsyncd via apt. Please note, this will automatically setup
– Lsyncd 2.0.4
– /etc/init.d/lsyncd

But it will not setup:
– /etc/logrotate.d/lsyncd
– /etc/lsyncd/lsyncd.conf.lua

Install it by running:

apt-get update
apt-get install lsyncd

Now setup the lsyncd configuration by:

mkdir /etc/lsyncd
vim /etc/lsyncd/lsyncd.conf.lua
 
settings = {
logfile = "/var/log/lsyncd/lsyncd.log",
statusFile = "/var/log/lsyncd/lsyncd-status.log",
statusInterval = 20
}

servers = {
 "x.x.x.x",
 "x.x.x.x",
 "x.x.x.x"
}

for _, server in ipairs(servers) do
sync { 
    default.rsync, 
    source="/var/www/",
    target=server..":/var/www/",
    excludeFrom="/etc/lsyncd/lsyncd-excludes.txt",
    rsyncOps={"-e", "/usr/bin/ssh -o StrictHostKeyChecking=no", "-avz"}
}
end

Create the place holder for lsyncd-excludes.txt and logs directory

touch /etc/lsyncd/lsyncd-excludes.txt
mkdir /var/log/lsyncd

Setup log rotate script

vim /etc/logrotate.d/lsyncd

/var/log/lsyncd/*log {
    missingok
    notifempty
    sharedscripts
    postrotate
    if [ -f /var/lock/lsyncd ]; then
      /usr/sbin/service lsyncd restart > /dev/null 2>/dev/null || true
    fi
    endscript
}

Finally, start the service

service lsyncd start

Ubuntu 14.04 and 16.04 – How To Install Lsyncd

Install Lsyncd via apt. Please note, this will automatically setup
– Lsyncd 2.1.5
– /etc/init.d/lsyncd

But it will not setup:
– /etc/logrotate.d/lsyncd
– /etc/lsyncd/lsyncd.conf.lua

Install it by running

apt-get update
apt-get install lsyncd

Now setup the lsyncd configuration by:

mkdir /etc/lsyncd
vim /etc/lsyncd/lsyncd.conf.lua
 
settings {
   logfile = "/var/log/lsyncd/lsyncd.log",
   statusFile = "/var/log/lsyncd/lsyncd-status.log",
   statusInterval = 20
}
servers = {
 "x.x.x.x",
 "x.x.x.x",
 "x.x.x.x"
}
 
for _, server in ipairs(servers) do
sync {
    default.rsyncssh,
    source="/var/www/",
    host=server,
    targetdir="/var/www/",
    excludeFrom="/etc/lsyncd/lsyncd-excludes.txt",
    rsync = {
        compress = true,
        archive = true,
        verbose = true,
        rsh = "/usr/bin/ssh -p 22 -o StrictHostKeyChecking=no"
    }
}
end

Create the place holder for lsyncd-excludes.txt and logs directory

touch /etc/lsyncd/lsyncd-excludes.txt
mkdir /var/log/lsyncd

Setup log rotate script

vim /etc/logrotate.d/lsyncd

/var/log/lsyncd/*log {
    missingok
    notifempty
    sharedscripts
    postrotate
    if [ -f /var/lock/lsyncd ]; then
      /usr/sbin/service lsyncd restart > /dev/null 2>/dev/null || true
    fi
    endscript
}

Finally, start the service

service lsyncd start

Note: On Ubuntu 16.04, if ‘service lsyncd start’ does not start daemon, then manually start lsyncd, which will allow systemd to work with it from here on out by running:

/usr/bin/lsyncd /etc/lsyncd/lsyncd.conf.lua
service lsyncd stop
service lsyncd start

How to setup OpenVZ on the Rackspace Cloud

Testing out changes in a production environment is never a good idea. However prepping test servers can be tedious as you have to find the hardware and setup the operating system before you can begin. So I want a faster and more cost effective medium, turning a single Cloud Server into a virtualized host server for my test servers. Welcome OpenVZ.

Taken from the providers site, OpenVZ (Open Virtuozzo) is an operating system-level virtualization technology for Linux. It allows a physical server to run multiple isolated operating system instances, called containers, virtual private servers (VPSs), or virtual environments (VEs.) OpenVZ is similar to Solaris Containers and LXC.

To managed my OpenVZ containers, I prefer to use Proxmox, which provides a clean control panel for managing my containers.

This guide will document how to install Proxmox on a 2G Rackspace Cloud Server running Debian 7. The Proxmox installation will install everything needed to run OpenVZ.

Install Proxmox

For this to work, we need a vanilla Debian 7 Cloud Server, and install Proxmox on top of it, which will install the required kernel.

To get things started, update /etc/hosts to setup your fqdn, and remove any resolvable ipv6 domains:

[root@proxmox ~]# cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.6.177 proxmox.yourdomain.com proxmox pvelocalhost

# The following lines are desirable for IPv6 capable hosts
::1     ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
ff02::3 ip6-allhosts

Now backup the /etc/apt/source.list, and create a fresh one to use proxmox’s repos:

mv /etc/apt/sources.list /etc/apt/sources.list.bak
vim /etc/apt/sources.list
[ ADD ]
deb http://ftp.at.debian.org/debian wheezy main contrib

# PVE repository provided by proxmox.com, only for installation (this repo will stay on 3.1)
deb http://download.proxmox.com/debian wheezy pve

# security updates
deb http://security.debian.org/ wheezy/updates main contrib

Now add the Proxmox VE repository key:

wget -O- "http://download.proxmox.com/debian/key.asc" | apt-key add -

Update the package index and then update the system to install Proxmox:

apt-get update && apt-get dist-upgrade

Install proxmox kernel and headers:

apt-get install pve-firmware pve-kernel-2.6.32-26-pve
apt-get install pve-headers-2.6.32-26-pve

7. Update grub and reboot into proxmox kernel:

vim /etc/default/grub
# From
GRUB_DEFAULT=0
# To
GRUB_DEFAULT=3
...
update-grub2
reboot

Once the cloud server comes back online, confirm you are running the pve kernel

uname -a
Linux proxmox 2.6.32-26-pve #1 SMP Mon Oct 14 08:22:20 CEST 2013 x86_64 GNU/Linux

** If the kernel is a 3.2 kernel, something is wrong and grub booted off default kernel, not pve. Go back and confirm all the steps worked properly.

Remove the old Debian Kernel as it is no longer needed:

apt-get remove linux-image-amd64 linux-image-3.2.0-4-amd64 linux-base
update-grub

Install proxmox ve packages

apt-get install proxmox-ve-2.6.32 ntp ssh lvm2 postfix ksm-control-daemon vzprocps open-iscsi bootlogd

Open up firewall to allow inbound 8006 from your workstations IP address:

ufw allow from x.x.x.x

Setup NAT for VE’s

As the Rackspace Cloud server comes with 1 IP address, I will be making use of NAT’ed IP addresses to assign to my individual containers. The steps are documented below:

Update /etc/sysctl.conf to allow ip_forwarding:

vim /etc/sysctl.conf
[ ADD ]
net.ipv4.ip_forward=1

Then apply the new setting:

sysctl -p

To setup the NAT rules, we need to setup a script that will start on boot. Below is a script that I found on https://vpsaddicted.com/install-and-configure-proxmox-ve-for-nat-ipv4-vps-on-debian-wheezy/.

Two things need to be taken into consideration here:
1. Change IP address below (123.123.123.123) in the NAT rule to your Cloud server’s public IP address.
2. This assumes you want to use a 10.0.0.0/24 network for your VE’s.

vim /etc/init.d/vz-routing
#!/bin/sh
case "$1" in
 start) echo "vz-routing started"
# It's important that you change the SNAT IP to the one of your server (not the local but the internet IP)
# The following line adds a route to the IP-range that we will later assign to the VPS. That's how you get internet access on # your VPS.
/sbin/iptables -t nat -A POSTROUTING -s 10.0.0.0/24 -o eth0 -j SNAT --to 123.123.123.123

# Allow servers to have access to internet:
/sbin/iptables -A FORWARD -s 10.0.0.0/24 -j ACCEPT
/sbin/iptables -A FORWARD -d 10.0.0.0/24 -j ACCEPT
# Be sure to add net.ipv4.ip_forward=1 to /etc/sysctl.conf, then run sysctl -p

# These are the rules for any port forwarding you want to do
# In this example, all traffic to and from the ports 11001-11019 gets routed to/from the VPS with the IP 10.0.0.1.
# Also the port 11000 is routed to the SSH port of the vps, later on you can ssh into your VPS through yourip:11000

#/sbin/iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 11000 -j DNAT --to 10.0.0.1:22
#/sbin/iptables -t nat -A PREROUTING -i eth0 -p udp --dport 11001:11019 -j DNAT --to 10.0.0.1
#/sbin/iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 11001:11019 -j DNAT --to 10.0.0.1

# In my case I also dropped outgoing SMTP traffic, as it's one of the most abused things on servers

#/sbin/iptables -A FORWARD -j DROP -p tcp --destination-port 25
#/sbin/iptables -A FORWARD -j DROP -p tcp --destination-port 2525
#/sbin/iptables -A FORWARD -j DROP -p tcp --destination-port 587
#/sbin/iptables -A FORWARD -j DROP -p tcp --destination-port 465
#/sbin/iptables -A FORWARD -j DROP -p tcp --destination-port 2526
#/sbin/iptables -A FORWARD -j DROP -p tcp --destination-port 110
#/sbin/iptables -A FORWARD -j DROP -p tcp --destination-port 143
#/sbin/iptables -A FORWARD -j DROP -p tcp --destination-port 993

;;

*) echo "Usage: /etc/init.d/vz-routing {start}"
exit 2
;;

esac
exit 0

Setup permissions, set to run on boot, and run it:

chmod 755 /etc/init.d/vz-routing
update-rc.d vz-routing defaults
/etc/init.d/vz-routing start

That should be it! Navigate your browser to the control panel, login with your root SSH credentials, and your ready to go:

https://x.x.x.x:8006

How to setup DRBD

Distributed Replicated Block Device (DRBD) mirrors block devices between multiple hosts. You can think of this loosely as network Raid 1.

DRBD is meant to run in a Active / Passive setup, meaning, you can only mount the disk on one node at a time. This is not a DRBD limitation, but rather a limitation of the common file systems (ext3, ext4, xfs, etc), since they cannot account for 2 or more servers accessing a single disk.

As with any form of data replication, always ensure you have good backups before you begin, and ensure that you have good backups throughout the life cycle of the setup. There is always a chance of data corruption or complete data loss due to some unforeseen situation, so make sure you have backups, and you have tested restoring from those backups!

Requirements

There are a few requirements that need to be met for DRBD to function properly and securely:

1. 2x servers with similar block devices
2. DRBD kernel module and userspace utilities
3. Private network between the servers
4. iptables port 7788 open between servers on the Private network
5. /etc/hosts configured
6. NTP synchronized

Preparation

For the purposes of this article, my two servers running CentOS 6 will be:

drbd01 192.168.5.2 | Cloud Block Storage 50G SSD
drbd01 192.168.5.3 | Cloud Block Storage 50G SSD

First, ensure that /etc/hosts are setup properly on both servers:

cat /etc/hosts
192.168.5.2 drbd01
192.168.5.3 drbd02

Next, open up iptables on both servers to allow communications across the private network:

cat /etc/sysconfig/iptables
-A INPUT -i eth2 -s 192.168.5.0/24 -p tcp --dport 7788 -m comment --comment "Allow DRBD on private interface" -j ACCEPT
...
service iptables restart

Finally, prep your block devices, but do not format them with a filesystem! For this guide, I am going to assume you are using separate disks for this, which are setup on /dev/xvdb:

fdisk /dev/xvdb
N
P 
1
enter
enter
t (choose 83)
w (write)
fdisk -l /dev/xvdb1 (confirm all looks well)

Install DRBD

CentOS requires the use of the RPM packages found in the repo, http://www.elrepo.org. This will provide the DKMS-based kernel module and userspace tools.

On both nodes:

rpm -Uvh http://www.elrepo.org/elrepo-release-6-6.el6.elrepo.noarch.rpm
yum repolist
yum install drbd83-utils kmod-drbd83 dkms ntp ntpdate
service ntpd restart && chkconfig ntpd on
reboot

Configure DRBD

First, configure the global_common.conf

vi /etc/drbd.d/global_common.conf
# Change
usage-count no;
# To 
usage-count yes;

Then search for syncer {, and add rate 10M;. An example is posted below:

syncer {
# rate after al-extents use-rle cpu-mask verify-alg csums-alg
rate 10M;
}

Some important notes:

1. usage-count. The DRBD project keeps statistics about the usage of various DRBD versions. This is done by contacting an HTTP server every time a new DRBD version is installed on a system. This can be disabled by setting usage-count no;. The default is usage-count ask; which will prompt you everytime you upgrade DRBD.

2. rate 10M: This throttles the total bandwidth that DRBD will use to perform its tasks between the 2 nodes. A good rule of thumb for this value is to use about 30% of the available replication bandwidth. Thus, if you had an I/O subsystem capable of sustaining write throughput of 180MB/s, and a Gigabit Ethernet network capable of sustaining 110 MB/s network throughput (the network being the bottleneck), you would calculate: 110 x 0.3 = 33MB/s. I opted to go with 10M for this article. 10M is a bit on the low side, so read the following guide and increased your limits as needed depending on your available bandwidth: https://drbd.linbit.com/users-guide/s-configure-sync-rate.html

Resource Settings

Configure the 2 nodes so they can communicate with each other. On both servers, setup:

vi /etc/drbd.d/cent00.res
resource cent00 {
  protocol C;
  startup { wfc-timeout 0; degr-wfc-timeout 120; }
  disk { on-io-error detach; }
  net { cram-hmac-alg "sha1"; shared-secret "4ftl421dg987d33gR"; }
  on drbd01 {
    device /dev/drbd0;
    disk /dev/xvdb1;
    meta-disk internal;
    address 192.168.5.2:7788;
  }
  on drbd02 {
    device /dev/drbd0;
    disk /dev/xvdb1;
    meta-disk internal;
    address 192.168.5.3:7788;
  }
}

Now initialize the resources DRBD will be using, and set drbd01 to be primary. This is done by:

[root@drbd01 ~]# drbdadm create-md cent00
[root@drbd02 ~]# drbdadm create-md cent00
 
[root@drbd01 ~]# service drbd start; chkconfig drbd on
[root@drbd02 ~]# service drbd start; chkconfig drbd on
[root@drbd01 ~]# drbdadm -- --overwrite-data-of-peer primary cent00

Once this is done, the disks will begin to sync up. This could take several hours. You can check the status by:

[root@drbd01 ~]# cat /proc/drbd 
version: 8.3.16 (api:88/proto:86-97)
GIT-hash: a798fa7e274428a357657fb52f0ecf40192c1985 build by phil@Build64R6, 2013-09-27 16:00:43
0: cs:SyncSource ro:Primary/Secondary ds:UpToDate/Inconsistent C r-----
ns:1124352 nr:0 dw:0 dr:1125016 al:0 bm:68 lo:0 pe:1 ua:0 ap:0 ep:1 wo:f oos:19842524
[>...................] sync'ed: 5.4% (19376/20472)M
finish: 0:31:21 speed: 10,536 (10,312) K/sec

Setup Filesystem

It is recommended to wait until the initial synchronization is complete. It simply depends on the size of the block storage, and the speed of the internal network connecting the 2 servers. You can check the status by running

[root@drbd01 ~]# cat /proc/drbd

Then before continuing, make sure you are on the primary node first:
** Due to WordPress, I had to put a space in the opening tags to avoid it being processed as markup.

[root@drbd01 ~]# drbdadm -- status cent00
< drbd-status version="8.3.16" api="88">
< resources config_file="/etc/drbd.conf">
< resource minor="0" name="cent00" cs="Connected" ro1="Primary" ro2="Secondary" ds1="UpToDate" ds2="UpToDate" />
< /resources>
< /drbd-status>

We’ll use the standard ext4 file system for this:

[root@drbd01 ~]# mkfs.ext4 /dev/drbd0
[root@drbd01 ~]# mkdir /data
[root@drbd01 ~]# mount -t ext4 /dev/drbd0 /data

Testing Scenarios

Below are some basic test scenarios you can simulate pretty easily. This goes without saying, but do not experiment with these scenarios on your production environment! Know what they do before you run them in production since they can cause problems if your not ready for it!

These are broken down into the following tests:
Test 1: Promote drbd02 to become primary
Test 2: Testing secondary node failure
Test 3: Testing primary node failure
Test 4: Recovering from split-brain

Test 1: Promote drbd02 to become primary

Unmount the partition and demote the current primary (drbd01) to secondary:

[root@drbd01 ~]# umount /data
[root@drbd01 ~]# drbdadm secondary cent00
On other server, drbd02, promote it to primary and mount the drbd device:
[root@drbd02 ~]# drbdadm primary cent00
[root@drbd02 ~]# mkdir /data
[root@drbd02 ~]# mount -t ext4 /dev/drbd0 /data
[root@drbd02 ~]# ls -d /data/*

At this time, drbd02 will now be the primary, and drdb01 will now be the secondary node.

Test 2: Testing secondary node failure

To see what happens when the secondary server goes offline:
Shutdown your secondary node, which in this case, is drbd02:

[root@drbd02 ~]# shutdown -h now

Now, back on the primary node drbd01, add a few files to the volume:

[root@drbd01 ~]# mkdir -p /data/test/
[root@drbd01 ~]# cp /etc/hosts /data/test/

Power back on the secondary node drbd02, and watch the system sync back up. Note, depending on how much data was written, it may take a bit of time for the volumes to become consistent again. You can check the status with:

[root@drbd01 ~]# cat /proc/drbd

Test 3: Testing primary node failure

This tests what happens when primary node goes offline, and someone promotes the secondary node before the primary comes online and can be demoted (split-brain).

If you want to simulate this worst case scenario, and you don’t care about your data, then perform the following:

[root@drbd01 ~]# echo 1 > /proc/sys/kernel/sysrq ; echo b > /proc/sysrq-trigger
[root@drbd01 ~]# reboot -f -n

Or just shutdown drbd01 (primary), and then log into drbd02 (secondary), and promote it to master:

[root@drbd02 ~]# drbdadm primary cent00
[root@drbd02 ~]# mkdir /data
[root@drbd02 ~]# mount -t ext4 /dev/drbd0 /data

Then boot drbd01 again and enjoy the split-brain scenario! For obvious reasons, do NOT do this on drives containing any data you need for anything! If the primary node loses the replication link, and you made the other node primary BEFORE connectivity is restored, you WILL have split-brain. Avoid that at all costs.

Test 4: Recovering from split-brain

In the event of split-brain, you may be able to correct it by performing the following, but do not do this blindly! Make sure you understand what this is doing before you run it on your production data, otherwise you may lose data you wanted! More information can be found at http://drbd.linbit.com/docs/working/

For reference:
– drbd01 : Primary node
– drbd02 : Secondary node

On secondary node

[root@drbd02 ~]# drbdadm secondary cent00
[root@drbd02 ~]# drbdadm -- --discard-my-data connect cent00

And back on the primary node

[root@drbd01 ~]# drbdadm connect cent00
[root@drbd01 ~]# cat /proc/drbd

Detecting if MySQLdump was running

Ever have those times when your site was down for a few minutes, and you have no way to explain why?

One possible thing to check for would be to see if one your developers accidentally ran a MySQLdump on your production databases during that time. You can see this pretty easily if you suspect its currently running by:

mysql
mysql> show processlist;
...
SELECT /*!40001 SQL_NO_CACHE */ * FROM `your_table`;

You can then verify it by running the following query:

mysql> select COUNT(1) mysqldumpThreads
from information_schema.processlist
where info like 'SELECT /*!40001 SQL_NO_CACHE */%';
+------------------+
| mysqldumpThreads |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

If you get something other than 0, like what is shown above, then mysqldump is running.

If the issue already passed, and you are just trying to do a root cause analysis, check your MySQL slow query log for queries like what is posted below around the timestamp in question:

# Time: 151112 10:34:12
# User@Host: root[root] @ localhost []
# Query_time: 539.418400  Lock_time: 0.000041 Rows_sent: 60547211  Rows_examined: 60547211
SET timestamp=1420389252;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `your_table`;

Increase open-files-limit in MariaDB on CentOS 7 with systemd

In the new age of linux known as systemd, a word that many still prepend colorful words to, I stumbled across another gotcha. Changing MariaDb’s open files limit.

Typically you would configure this in /etc/my.cnf or in /etc/security/limits.conf, but this no longer appears to be the case as systemd wants to control it.

I ran across having to do this cause I was receiving the following errors in the database when running mysqldump, mysqlcheck, etc:

mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_7f96_2.MAI' (Errcode: 24)' when trying to dump tablespaces

mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'EXAMPLE'': Can't read dir of './DB_NAME/' (errno: 24) (1018)

mysqldump: Got error: 23: "Out of resources when opening file './DB_NAME/TABLE.MYD' (Errcode: 24)" when using LOCK TABLES

So to increase the open files limit which is the accepted fix for this, you need to do the following with OS’s that use systemd:

First, check to see what your current open files is set to in MariaDB:

MariaDB [(none)]> SHOW VARIABLES LIKE 'Open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
1 row in set (0.00 sec)

Create a directory to store the MariaDB service changes for systemd:

mkdir -p /etc/systemd/system/mariadb.service.d/
cat /etc/systemd/system/mariadb.service.d/limits.conf
[Service]
LimitNOFILE=20000

Now reload the systemd daemon so it is aware of the changes applied to systemd. This does not restart any service:

systemctl daemon-reload

Finally, restart the MariaDB service so it will apply the new setting:

systemctl stop mariadb
systemctl start mariadb

You can now verify that you setting went into place by:

MariaDB [(none)]> SHOW VARIABLES LIKE 'Open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 20000 |
+------------------+-------+
1 row in set (0.00 sec)

More details and the reason behind this is found here:

cat /usr/lib/systemd/system/mariadb.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file "/etc/systemd/system/mariadb.service",
# containing
#	.include /lib/systemd/system/mariadb.service
#	...make your changes here...
# or create a file "/etc/systemd/system/mariadb.service.d/foo.conf",
# which doesn't need to include ".include" call and which will be parsed
# after the file mariadb.service itself is parsed.
#
# For more info about custom unit files, see systemd.unit(5) or
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F

# For example, if you want to increase mariadb's open-files-limit to 10000,
# you need to increase systemd's LimitNOFILE setting, so create a file named
# "/etc/systemd/system/mariadb.service.d/limits.conf" containing:
#	[Service]
#	LimitNOFILE=10000

# Note: /usr/lib/... is recommended in the .include line though /lib/...
# still works.
# Don't forget to reload systemd daemon after you change unit configuration:
# root> systemctl --system daemon-reload

MySQL recovery mode

InnoDB tables rarely get corrupted, but it can occur when MySQL is randomly killed off by power outages, hardware problems, or the OOM killer running. When this happens, you will see some scary looking log entries in your MySQL error log, such as:

InnoDB: Assertion failure in thread 114256846 in file blah.c line 5423
InnoDB: Failing assertion: page_get_n_recs(page) > 1
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
...
obscure backtrace message
...
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Usually, you can get MySQL back up in recovery mode pretty quickly by setting the following variable in /etc/my.cnf under the section [mysqld]:

vim /etc/my.cnf

[mysqld]
innodb_force_recovery = 1

Then try to restart MySQL:

service mysql restart

Once you have MySQL running, you can now check to see what tables may be corrupted:

mysqlcheck -c --all-databases

After you find the database that is corrupted, you can try to repair it by running:

mysqlcheck -r your_database_name
ie:  mysqlcheck -r drupal

Now confirm that the databases and tables are now showing ‘OK’:

mysqlcheck -c --all-databases

If all is well, take MySQL out of recovery mode by removing the following variable from /etc/my.cnf:

vim /etc/my.cnf
# Remove
innodb_force_recovery = 1

Finally, restart MySQL and check your logs to ensure that there are no further errors being recorded:

service mysql restart

MySQL slave promotion

There are times when you may need to promote to MySQL Slave server to a Master server. This task can be done pretty easily, but it is important to note that once this is done, you will need to rebuild your Master MySQL server as it is no longer the point of truth for your databases.

Stop the ‘IO_THREAD’ on the MySQL SLAVE:

mysql> stop slave io_thread;

Check the ‘processlist’ to make sure that the SLAVE has read the entire relay log:

mysql> show processlist;;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 2 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
| 4 | system user | | NULL | Connect | 124 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

Stop the MySQL Slave process on MySQL 5.0 and MySQL 5.1

mysql> stop slave;
mysql> reset slave;

Stop the MySQL Slave process on MySQL 5.5 and MySQL 5.6

mysql> stop slave;
mysql> reset slave all;

Confirm that the following line is commented out in your /etc/my.cnf

# read-only = 1

Restart MySQL to re-read your my.cnf:

service mysql restart

Finally, don’t forget to update your web applications or anything else using the databases to point to the MySQL Slave IP address since it is now the new MySQL Master server.

Setting up MySQL Master Slave Replication using mysqldump

This article is part of a series of setting up MySQL replication. As with most things, there is always more than one way to do something. In the case of setting up MySQL replication, or rebuilding it, some options are better than others depending on your use case.

The articles in the series are below:
Setting up MySQL Replication using mysqldump
Setting up MySQL Replication using Percona XtraBackup
Setting up MySQL Replication using Rsync
Setting up MySQL Replication using LVM snapshots

This guide will document how to setup MySQL Master / Slave Replication using mysqldump.

MySQL Master / Slave Replication is useful for a number of situations, such as:
– Perform hourly backups on the Slave without locking the Master database
– Provide a degree of redundancy as you can promote the Slave
– Splitting the reads between the Master and Slave

Setting up MySQL Replication is pretty straight forward. Keep in mind that this guide requires you use mysqldump to create a backup of your existing databases, which will lock your tables while it runs. So be sure to schedule it accordingly!

Setup the Master MySQL server

Configure the my.cnf as shown below:

log-bin=/var/lib/mysql/db01-binary-log
expire-logs-days=5
server-id=1

Then restart MySQL to apply the settings:

# CentOS / RHEL:
[root@db01 ~]# service mysqld restart

# Ubuntu / Debian:
[root@db01 ~]# service mysql restart

Finally, grant access to the Slave so it has access to communicate with the Master:

mysql> GRANT REPLICATION SLAVE ON *.* to 'repl’@’10.x.x.x’ IDENTIFIED BY 'your_password';

Setup the Slave MySQL server

Configure the my.cnf as shown below:

relay-log=/var/lib/mysql/db02-relay-log
relay-log-space-limit = 4G
read-only=1
server-id=2

Then restart MySQL to apply the settings:

# CentOS / RHEL:
[root@db02 ~]# service mysqld restart

# Ubuntu / Debian:
[root@db02 ~]# service mysql restart

Finally, prep the slave server to connect to the Master MySQL server by:

# For MySQL 5.0 and 5.1
mysql> STOP SLAVE; RESET SLAVE;

# For MySQL 5.5 and 5.6
mysql> STOP SLAVE; RESET SLAVE ALL;

# Then run this command for all MySQL versions
mysql> CHANGE MASTER TO MASTER_HOST=’10.x.x.x’, MASTER_USER='repl', MASTER_PASSWORD='your_password'

RESET SLAVE will remove any previously configuration replication settings. Also, the MASTER_LOG_FILE and MASTER_LOG_POS is not needed as the following section will be dumping the databases with the –master-data flag.

Importing the databases onto the Slave MySQL server

A dump of the Master MySQL server is needed in order to get the Slave MySQL server in sync. Please note that this will lock your tables while this runs!

Perform this on the master server by:

[root@db01 ~]# mysqldump --all-databases --master-data | gzip -1 > /root/all.sql.gz

Now transfer over the database dump to the Slave MySQL server:

[root@db01 ~]# scp /root/all.sql.gz [email protected]:/root/

On the MySQL Slave server, import the dump by running:

[root@db02 ~]# zcat /root/all.sql.gz | mysql
[root@db02 ~]# mysql
mysql> FLUSH PRIVILEGES;
mysql> quit

As this dump contains the internal database ‘mysql’, all the passwords, including root, will be the same as they are on the Master MySQL server. So be sure to update /root/.my.cnf with the proper credentials:

[root@db02 ~]# vi /root/.my.cnf
[client]
user=root
password=db01_root_mysql_password

Also be sure to update any passwords your database backup scripts may use to reflect the new credentials!

Start and test replication

Start replication on the Slave MySQL server by:

[root@db02 ~]# mysql
mysql> START SLAVE;

And verify replication is online and working by confirming the following output:

mysql> SHOW SLAVE STATUS\G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
...

If those values are the same as what is shown above, then replication is working properly! Perform a final test by creating a test database on the Master MySQL server, then check to ensure it shows up on the Slave MySQL server. Afterwards, feel free to drop that test database on the Master MySQL server.

From here, you should be good to go! Just be sure to setup a monitoring check to ensure that replication is always running and doesn’t encounter any errors. A very basic MySQL Replication check can be found here:
https://github.com/stephenlang/system-health-check