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’.

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

Installing unrar package under Debian Linux

What we need to do is install this package (at least for Debian 4.0 Etch. You may find for your appropriate version here). Since it belongs to Debian’s non-free section you need to make sure you have non-free enabled on your apt sources.

You may test that everything will be smooth by invoking the install command with the “simulate” switch (-s):

apt-get update
apt-get -s install unrar

Building Dependency Tree... Done
The following NEW packages will be installed:
  unrar
0 upgraded, 1 newly installed, 0 to remove and 82 not upgraded.
Inst unrar (1:3.5.2-0.1 Debian:3.1r7/oldstable)
Conf unrar (1:3.5.2-0.1 Debian:3.1r7/oldstable)

It actually looks OK; there are no dependency problems and the upgrade is minimal (this package does not require to install many others). I then proceed with the “real” thing:

apt-get install unrar

Reading Package Lists... Done
Building Dependency Tree... Done
The following NEW packages will be installed:
  unrar
0 upgraded, 1 newly installed, 0 to remove and 82 not upgraded.
Need to get 87.9kB of archives.
After unpacking 221kB of additional disk space will be used.
Get:1 http://mirror.aarnet.edu.au oldstable/non-free unrar 1:3.5.2-0.1 [87.9kB]
Fetched 87.9kB in 1s (53.2kB/s)
Selecting previously deselected package unrar.
(Reading database ... 47406 files and directories currently installed.)
Unpacking unrar (from .../unrar_1%3a3.5.2-0.1_i386.deb) ...
Setting up unrar (3.5.2-0.1) ...