How to install MySQL Server on Debian Linux

While installing MySQL Server it is always good to keep in mind that the logs and data folders will potentially have a big size. By default MySQL keeps them in the root mount point (i.e. ‘/’). That may cause your database server system disk to get full, which is never a good idea.

This article describes how to move these two folders to ‘/home’ which is ideally mounted into another disk and has enough space to keep your database data and logs.

First, I install the required apt-get packages as follows:

apt-get update
apt-get install mysql-server

To check the status:

/etc/init.d/mysql status

/usr/bin/mysqladmin  Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on i486
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.0.51a-24
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 3 sec

Threads: 1  Questions: 78  Slow queries: 0  Opens: 23  Flush tables: 1
Open tables: 17  Queries per second avg: 26.000.

Now, stop MySQL, move the folders to the right location, reconfigure MySQL and start again:

# Stop MySQL
/etc/init.d/mysql stop

# Move and reconfigure data
mkdir /home/mysql
mv /var/lib/mysql /home/mysql/mysql-data
ln -s /home/mysql/mysql-data/ /var/lib/mysql

# Move and reconfigure logs
mv /var/log/mysql/ /home/mysql/mysql-logs
ln -s /home/mysql/mysql-logs/ /var/log/mysql

# Start MySQL and check that everything is OK
/etc/init.d/mysql start
/etc/init.d/mysql status
/usr/bin/mysqladmin  Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on i486
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.0.51a-24
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 14 sec

Threads: 1  Questions: 78  Slow queries: 0  Opens: 23  Flush tables: 1
Open tables: 17  Queries per second avg: 5.571.

These are some settings that I usually put on the /etc/mysql/my.cnf configuration file:

# Here you can see queries with especially long duration
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time         = 1
log-queries-not-using-indexes

# A server-id unique
server-id                = 177
log-bin                  = /var/log/mysql/mysql-bin.log
log-bin-index            = /var/log/mysql/mysql-bin.log
innodb_file_per_table
# Unique log names (this prevents replication breaking upon hostname change :-)
relay-log                = iamalsounique98127-relay-bin
relay-log-index          = iamalsounique98127-relay-bin

# Taking care of the auto-increment values (for multi-master replication)
auto_increment_increment      = 10
auto_increment_offset         = 1

For these changes to take effect, you would need to restart MySQL:

/etc/init.d/mysql restart

If you want to ignore databases or tables you may use the following options:

binlog_ignore_db        = information_schema
replicate_ignore_db     = information_schema
binlog_ignore_db        = mysql
replicate_ignore_db     = mysql

# Ignore all the cache* tables which have caused DUPLICATE
# ENTRY issues. Unai.
replicate_wild_ignore_table = exampledb.cache%

Having ‘binlog_ignore_db’ is enough to exclude databases from replication BUT having ‘replicate_ignore_db’ as well will make things clearer since the databases that are being ignored will appear in both the ‘SHOW SLAVE STATUSG’ and ‘SHOW MASTER STATUSG’.

How to define shorewall rules to allow VRRP traffic

It is essential for routers that implement the Virtual Router Redundancy Protocol to be able to communicate with each other.

As the protocol defines, the master router needs to send multicast packets to the whole subnet and of course, the rest of the backup routers need to receive this announcements otherwise they will think that the master router is dead and will initiate an election of a master router.

If no router is able to receive this “multicasted” announcements they all will eventually think that they are the only ones alive and thus become master. All of them master. That brings networking issues.

This page covers how to define the rule(s) under shorewall firewall in order to allow this VRRP announcements pass-through.

Rule definition

VRRP’s announcement multicast packets have the following characteristics:

  1. They are sent to the following multicast IP address: 224.0.0.18
  2. They use the protocol vrrp
  3. They source IP address is a virtual router

Thus, a rule that allows all the incoming VRRP traffic would look like this:

ACCEPT  net fw:224.0.0.18 vrrp

A rule that allows VRRP packets from a specific router would look like this:

ACCEPT  net:OTHER_VIRTUAL_ROUTER_IP fw:224.0.0.18 vrrp

Example

Let’s imagine we have two routers implementing VRRP (using keepalived, for example). Their IPs are: 10.20.30.40 and 10.20.30.41. Their shorewall rules should include the following:

on 10.20.30.40:

ACCEPT  net:10.20.30.41 fw:224.0.0.18 vrrp

on 10.20.30.41:

ACCEPT  net:10.20.30.40 fw:224.0.0.18 vrrp

References

KeepAlived Installation under Debian Etch

Briefly, KeepAlived is a daemon that is able to provide failover capabilities to servers/services by binding virtual IP addresses to machines. In the event of failure, KeepAlived would reassign this virtual IP to another machine. This action is executed fast (less than 2 seconds) and automatically.

This is a very interesting daemon to be used in combination with HAProxy, for example. It would be possible to have a failovered load balancer. In the event of this load balancer failing, keepalived would switch to another that is up and running in such a clean and fast way that the clients would not notice.

Installation steps under Debian Etch

apt-get update
apt-get install keepalived

The system will ask a couple of questions. I usually reply using the default values, then configure myself manually the daemon, by editing /etc/keepalived/keepalived.conf.

To make the virtual IP address bindable, you should add this line /etc/sysctl.conf:

net.ipv4.ip_nonlocal_bind=1

Check binding:

sysctl -p

net.ipv4.ip_nonlocal_bind = 1

It is convenient to alter the order when keepalived is being started upon restarts. We probably want to have it started at the end so all the services are already running by the time keepalive runs. To do that:

update-rc.d -f keepalived remove
Removing any system startup links for /etc/init.d/keepalived ...
/etc/rc0.d/K20keepalived
/etc/rc1.d/K20keepalived
/etc/rc2.d/S20keepalived
/etc/rc3.d/S20keepalived
/etc/rc4.d/S20keepalived
/etc/rc5.d/S20keepalived
/etc/rc6.d/K20keepalived

update-rc.d keepalived defaults 90
Adding system startup for /etc/init.d/keepalived ...
/etc/rc0.d/K90keepalived -> ../init.d/keepalived
/etc/rc1.d/K90keepalived -> ../init.d/keepalived
/etc/rc6.d/K90keepalived -> ../init.d/keepalived
/etc/rc2.d/S90keepalived -> ../init.d/keepalived
/etc/rc3.d/S90keepalived -> ../init.d/keepalived
/etc/rc4.d/S90keepalived -> ../init.d/keepalived
/etc/rc5.d/S90keepalived -> ../init.d/keepalived

See Also

Having HAProxy check mysql status through a xinetd script

HAProxy is able to load balance MySQL wonderfully. The main issue is how to make sure that the backend MySQL server to forward the request to is up and running (I mean not just to establish a connection to port 3306, I mean something more “complete”, that performs a little operation against the MySQL server).

It is possible to make haproxy check the status of a mysql server using a small shell script managed through the xinetd daemon.

What this script basically does is performs a basic operation against the mysql database then returns http status 200 if the operation was successful or http status 500 if it there was any error (i.e. mysql was not available).

Script

The script looks like this:

#!/bin/bash
#
# This script checks if a mysql server is healthy running on localhost. It will
# return:
#
# "HTTP/1.x 200 OKr" (if mysql is running smoothly)
#
# - OR -
#
# "HTTP/1.x 500 Internal Server Errorr" (else)
#
# The purpose of this script is make haproxy capable of monitoring mysql properly
#
# Author: Unai Rodriguez
#
# It is recommended that a low-privileged-mysql user is created to be used by
# this script. Something like this:
#
# mysql> GRANT SELECT on mysql.* TO 'mysqlchkusr'@'localhost' 
#     -> IDENTIFIED BY '257retfg2uysg218' WITH GRANT OPTION;
# mysql> flush privileges;

MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USERNAME="mysqlchkusr"
MYSQL_PASSWORD="secret"

TMP_FILE="/tmp/mysqlchk.out"
ERR_FILE="/tmp/mysqlchk.err"

#
# We perform a simple query that should return a few results :-p
#
/usr/bin/mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME 
	--password=$MYSQL_PASSWORD -e"show databases;" > $TMP_FILE 2> $ERR_FILE

#
# Check the output. If it is not empty then everything is fine and we return
# something. Else, we just do not return anything.
#
if [ "$(/bin/cat $TMP_FILE)" != "" ]
then
	# mysql is fine, return http 200
	/bin/echo -e "HTTP/1.1 200 OKrn"
	/bin/echo -e "Content-Type: Content-Type: text/plainrn"
	/bin/echo -e "rn"
	/bin/echo -e "MySQL is running.rn"
	/bin/echo -e "rn"
else
	# mysql is fine, return http 503
	/bin/echo -e "HTTP/1.1 503 Service Unavailablern"
	/bin/echo -e "Content-Type: Content-Type: text/plainrn"
	/bin/echo -e "rn"
	/bin/echo -e "MySQL is *down*.rn"
	/bin/echo -e "rn"
fi

Steps on the MySQL server

First, you should create the script somewhere, and assign proper permissions:

chown nobody /opt//mysqlchk
chmod   744  /opt//mysqlchk

Then, set permissions into the mysql server:

mysql> GRANT SELECT on mysql.* TO 'mysqlchkusr'@'localhost' 
    -> IDENTIFIED BY 'secret' WITH GRANT OPTION;
mysql> flush privileges;
mysql> exit

Test:

/opt/mysqlchk
HTTP/1.x 200 OK

Now, configure xinetd by adding this line at the bottom of /etc/services:

mysqlchk        9200/tcp                        # mysqlchk

Then add this file /etc/xinetd.d/mysqlchk:

# default: on
# description: mysqlchk
service mysqlchk
{
        flags           = REUSE
        socket_type     = stream
        port            = 9200
        wait            = no
        user            = nobody
        server          = /opt/mysqlchk
        log_on_failure  += USERID
        disable         = no
        only_from       = 0.0.0.0/0 # recommended to put the IPs that need
                                    # to connect exclusively (security purposes)
        per_source      = UNLIMITED # Recently added (May 20, 2010)
                                    # Prevents the system from complaining
                                    # about having too many connections open from
                                    # the same IP. More info:
                                    # http://www.linuxfocus.org/English/November2000/article175.shtml
}

Restart xinetd (you can watch for issues on /var/log/syslog):

/etc/init.d/xinetd stop
/etc/init.d/xinetd start

Test:

telnet localhost 9200
Trying 127.0.0.1...
Connected to localhost.localdomain.
Escape character is '^]'.
HTTP/1.1 200 OK

Content-Type: Content-Type: text/plain

MySQL is running.

Connection closed by foreign host.

Steps on the HAProxy server
Now, in order to make haproxy check the status of the mysql service through the xinetd-managed-script, we should add something similar to this on the haproxy.cfg file:

listen  MySQL 10.135.2.67:3306
        mode    tcp
	option  httpchk
        server  10.135.2.69:3306 10.135.2.69:3306 check port 9200 inter 12000 rise 3 fall 3
        source  10.135.2.67

What is important?

  1. option httpchk.- tells haproxy to check for full http response (i.e. http headers: 2xx OK or 5xx ERROR)
  2. check port XXXX.- tells haproxy to check the status of the service by sending an http request on that port

How to install NAGIOS NRPE plugin under Debian Linux

NRPE allows you to remotely execute Nagios plugins on other Linux/Unix machines. This allows you to monitor remote machine metrics (disk usage, CPU load, etc.). NRPE can also communicate with some of the Windows agent addons, so you can execute scripts and check metrics on remote Windows machines as well. Citation.

You may follow the steps to install NRPE in any of the following ways:

1) Steps (compiling from sources)

First, you should download the latest NRPE version from HERE.

Then, install some required packages:

apt-get update
apt-get install build-essential libssl-dev

Unpack the NRPE addons, configure and install:

cd /opt
tar xvfz nrpe-2.12.tar.gz
cd nrpe-2.12
./configure --enable-command-args
make all
make install-plugin

2) Steps (using apt binaries)

apt-get update
apt-get install nagios-nrpe-plugin

Invocation
NRPE can now be invoked using the following:

/usr/lib/nagios/plugins/check_nrpe

Another option would be to create a symlink to make the invocation easier:

ln -s /usr/lib/nagios/plugins/check_nrpe /usr/bin/check_nrpe

Thus:

check_nrpe

HAProxy hot-reconfiguration

As of version 1.2.8, a new soft-reconfiguration mechanism has been introduced.
It is now possible to “pause” all the proxies by sending a SIGTTOU signal to
the processes. This will disable the listening socket without breaking existing
connections. After that, sending a SIGTTIN signal to those processes enables
the listening sockets again. This is very useful to try to load a new
configuration or even a new version of haproxy without breaking existing
connections. If the load succeeds, then simply send a SIGUSR1 which will make
the previous proxies exit immediately once their sessions are closed ; and if
the load fails, then simply send a SIGTTIN to restore the service immediately.
Please note that the ‘grace’ parameter is ignored for SIGTTOU, as well as for
SIGUSR1 when the process was in the pause mode. Please also note that it would
be useful to save the pidfile before starting a new instance.

The ‘-st’ and ‘-sf’ command line options are used to inform previously running
processes that a configuration is being reloaded. They will receive the SIGTTOU
signal to ask them to temporarily stop listening to the ports so that the new
process can grab them. If anything wrong happens, the new process will send
them a SIGTTIN to tell them to re-listen to the ports and continue their normal
work. Otherwise, it will either ask them to finish (-sf) their work then softly
exit, or immediately terminate (-st), breaking existing sessions. Citation.

Procedure

The command to be issued to restart HAProxy gracefully would be:

haproxy -f configfile -sf

Example (added the PID location):

haproxy -f /etc/haproxy/haproxy.cfg -p /var/run/haproxy.pid -sf $(cat /var/run/haproxy.pid)

References

Fixing VMWare vmxnet driver networking issues under Debian Linux

It seems that using particular combinations of VMWare Server and Linux Kernel version(s) while installing VMWare Tools under Linux guest machines, may render the virtual machine’s networking down.

This page provides a “hacky” workaround to solve this situation. There might be other deeper and more proper solutions out there but I came up with this one because it is very simple to put in place and not harmful at all.

So, let’s imagine this scenario:

  • VMWare Server 1.0.2 (other versions might apply as well, not tested thought)
  • Debian Linux 4.0 Etch guest, running 2.6.18-6-686 kernel (other versions might apply as well, not tested thought)
  • VMWare Tools 1.0.2-39867 (other versions might apply as well, not tested thought)

Then, after the VMWare Tools get installed, the screen shows something like this:

The configuration of VMware Tools 1.0.2 build-39867 for Linux for this running
kernel completed successfully.

You must restart your X session before any mouse or graphics changes take
effect.

You can now run VMware Tools by invoking the following command:
"/usr/bin/vmware-toolbox" during an X session.

To use the vmxnet driver, restart networking using the following commands:
/etc/init.d/networking stop
rmmod pcnet32
rmmod vmxnet
depmod -a
modprobe vmxnet
/etc/init.d/networking start

Enjoy,

--the VMware team

Right now the networking does not work. If you try to see what is going on, you should see something like this:

ifconfig

lo        Link encap:Local Loopback
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:15 errors:0 dropped:0 overruns:0 frame:0
          TX packets:15 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:1556 (1.5 KiB)  TX bytes:1556 (1.5 KiB)

That’s it. No network interfaces. If you go for VMWare Tools’ installer suggested steps, the networking should work again:

/etc/init.d/networking stop
rmmod pcnet32
rmmod vmxnet
depmod -a
modprobe vmxnet
/etc/init.d/networking start

E.g.:

ifconfig

eth0      Link encap:Ethernet  HWaddr 00:0C:29:23:95:ED
          inet addr:10.123.16.83  Bcast:10.123.16.255  Mask:255.255.255.0
          inet6 addr: fe80::20c:29ff:fe23:95ed/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:4425 errors:0 dropped:0 overruns:0 frame:0
          TX packets:7426 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:316655 (309.2 KiB)  TX bytes:494628 (483.0 KiB)
          Interrupt:169 Base address:0x1424

lo        Link encap:Local Loopback
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:15 errors:0 dropped:0 overruns:0 frame:0
          TX packets:15 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:1556 (1.5 KiB)  TX bytes:1556 (1.5 KiB)

The issue here is that this settings will be lost upon restart. I guess it is a matter of having the proper modules loaded properly but I have not been able to find the proper configuration files combination to get this reliably enough.

Proposed solution/workaround

Basically, create a simple script that does what VMWare Tools’ intaller suggests and have it invoked upon system restart.

The script should contain the following:

#! /bin/bash
#
# vmxnet driver loader - loads the vmware network driver.
#
#

PATH=/sbin:/bin:/usr/sbin:/usr/bin

/etc/init.d/networking stop
rmmod pcnet32
rmmod vmxnet
depmod -a
modprobe vmxnet
/etc/init.d/networking start

This file should be created under /etc/init.d folder, with 755 permissions (i.e. chmod 755 filename). The, to have it invoken upon restart you could do this:

update-rc.d vmxnet-loader defaults 10
 Adding system startup for /etc/init.d/vmxnet-loader ...
   /etc/rc0.d/K10vmxnet-loader -> ../init.d/vmxnet-loader
   /etc/rc1.d/K10vmxnet-loader -> ../init.d/vmxnet-loader
   /etc/rc6.d/K10vmxnet-loader -> ../init.d/vmxnet-loader
   /etc/rc2.d/S10vmxnet-loader -> ../init.d/vmxnet-loader
   /etc/rc3.d/S10vmxnet-loader -> ../init.d/vmxnet-loader
   /etc/rc4.d/S10vmxnet-loader -> ../init.d/vmxnet-loader
   /etc/rc5.d/S10vmxnet-loader -> ../init.d/vmxnet-loader

I suggest having the initscript invoked with a lower sequence code (i.e. 10) so the networking gets activated before other services which may use and/or need it.

That’s it. Now your virtual machine’s networking should be fine upon restarts.

See Also

HAProxy 1.3.15.2 installation under Debian Etch (compiling from sources)

HAProxy is an excellent load balancer which performs extremely well. This page explains how to install HAProxy 1.3.15.2 since this is one of the recommended versions on the ”HAProxy Mailing List”:

Server response time discrepancy

Also, it has been recommended on that thread to use one of the following kernels:

  • 2.6.22
  • 2.6.25
  • 2.6.18

Installation Steps

First, install some required tools/packages:

apt-get update
apt-get install build-essential make libpcre3 libpcre3-dev

If you want to stick to one of the recommended kernels, at the time this how-to was written, Debian Etch standard apt-get repositories include the kernel 2.6.18, which could be installed (optional):

apt-get install linux-kernel-headers

Then you should reboot after, to start using this new kernel.

If you want to find out which kernel you are using, you may want to run this:

uname -rs

The output should be something like this:

Linux 2.6.18-6-686

Now, you should configure syslog daemon to listen following this document:

Configuring syslog to receive messages from the network (aka listen)

Check the HAProxy’s README file, and make sure:

To build haproxy, you will need :

  • GNU make. Neither Solaris nor OpenBSD’s make work with this makefile. However, specific Makefiles for BSD and OSX are provided.
  • GCC between 2.91 and 4.3. Others may work, but not tested.
  • GNU ld

Proceed with the compilation as follows (note that I have used TARGET, CPU and USE_PCRE. These options need to be double checked on the readme file, it is very clear):

cd /opt/
wget http://haproxy.1wt.eu/download/1.3/src/haproxy-1.3.15.2.tar.gz
tar zxvf haproxy-1.3.15.2.tar.gz

#
# Double check your options on the readme file first!!!!
# http://sysbible.org/att/HAProxy-1.3.15_README.txt
#
cd /opt/haproxy-1.3.15.2
make TARGET=linux26 CPU=i686 USE_PCRE=1
make install

ln -s /usr/local/sbin/haproxy /usr/sbin/haproxy

Now you should be ready to go:

haproxy

HA-Proxy version 1.3.15.2 2008/06/21
Copyright 2000-2008 Willy Tarreau

Usage : haproxy -f  [ -vdVD ] [ -n  ] [ -N  ]
        [ -p
 ] [ -m  ]
        -v displays version ; -vv shows known build options.
        -d enters debug mode ; -db only disables background mode.
        -V enters verbose mode (disables quiet mode)
        -D goes daemon ; implies -q
        -q quiet mode : don't display messages
        -c check mode : only check config file and exit
        -n sets the maximum total # of connections (2000)
        -m limits the usable amount of memory (in MB)
        -N sets the default, per-proxy maximum # of connections (2000)
        -p writes pids of all children to this file
        -de disables epoll() usage even when available
        -ds disables speculative epoll() usage even when available
        -dp disables poll() usage even when available
        -sf/-st [pid ]* finishes/terminates old pids. Must be last arguments.
haproxy -vv

HA-Proxy version 1.3.15.2 2008/06/21
Copyright 2000-2008 Willy Tarreau

Build options :
  TARGET  = linux26
  CPU     = i686
  CC      = gcc
  CFLAGS  = -O2 -march=i686 -g
  OPTIONS = USE_PCRE=1

Forcing Perl to install CPAN packages via HTTP (i.e. avoiding FTP)

If you are behind a firewall and your FTP connectivity with the external world is just restricted you might get frustrated with Perl’s automatic way of installing packages (via CPAN) because it uses FTP protocol by default.

Solution

Edit your CPAN settings file (probably /etc/perl/CPAN/Config.pm) and change the line:

'urllist' => [],

it should look like this:

'urllist' => [q[http://www.perl.com/CPAN]],

After that you should be able to install automatically CPAN modules using http protocol instead of ftp.

See Also