Tag Archives: linux

How to install MySQL Server on Debian Linux

Posted on by
Reply

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:

Shell
1
2
apt-get update
apt-get install mysql-server

To check the status:

Shell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/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:

Shell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 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:

Shell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 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:

Shell
1
/etc/init.d/mysql restart

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

Shell
1
2
3
4
5
6
7
8
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 STATUS\G’ and ‘SHOW MASTER STATUS\G’.

How to define shorewall rules to allow VRRP traffic

Posted on by
2

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:

Shell
1
ACCEPT net fw:224.0.0.18 vrrp

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

Shell
1
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:

Shell
1
ACCEPT net:10.20.30.41 fw:224.0.0.18 vrrp

on 10.20.30.41:

Shell
1
ACCEPT net:10.20.30.40 fw:224.0.0.18 vrrp

References

KeepAlived Installation under Debian Etch

Posted on by
1

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

Shell
1
2
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:

Shell
1
net.ipv4.ip_nonlocal_bind=1

Check binding:

Shell
1
2
3
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:

Shell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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

Posted on by
11

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:

Shell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
#!/bin/bash
#
# This script checks if a mysql server is healthy running on localhost. It will
# return:
#
# "HTTP/1.x 200 OK\r" (if mysql is running smoothly)
#
# - OR -
#
# "HTTP/1.x 500 Internal Server Error\r" (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 OK\r\n"
    /bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -e "\r\n"
    /bin/echo -e "MySQL is running.\r\n"
    /bin/echo -e "\r\n"
else
    # mysql is fine, return http 503
    /bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
    /bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -e "\r\n"
    /bin/echo -e "MySQL is *down*.\r\n"
    /bin/echo -e "\r\n"
fi

Steps on the MySQL server

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

Shell
1
2
chown nobody /opt//mysqlchk
chmod 744 /opt//mysqlchk

Then, set permissions into the mysql server:

Shell
1
2
3
4
mysql> GRANT SELECT on mysql.* TO 'mysqlchkusr'@'localhost' \
-> IDENTIFIED BY 'secret' WITH GRANT OPTION;
mysql> flush privileges;
mysql> exit

Test:

Shell
1
2
/opt/mysqlchk
HTTP/1.x 200 OK

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

Shell
1
mysqlchk 9200/tcp # mysqlchk

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

Shell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 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):

Shell
1
2
/etc/init.d/xinetd stop
/etc/init.d/xinetd start

Test:

Shell
1
2
3
4
5
6
7
8
9
10
11
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:

Shell
1
2
3
4
5
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

Posted on by
3

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:

Shell
1
2
apt-get update
apt-get install build-essential libssl-dev

Unpack the NRPE addons, configure and install:

Shell
1
2
3
4
5
6
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)

Shell
1
2
apt-get update
apt-get install nagios-nrpe-plugin

Invocation
NRPE can now be invoked using the following:

Shell
1
/usr/lib/nagios/plugins/check_nrpe

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

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

Thus:

Shell
1
check_nrpe