Some assorted notes on MySQL

Contents 1 Compiling on SLES 9.2 1.1 Custom Build 1.2 Build Options 1.3 Init Script 1.4 my.cnf

Compiling on SLES 9.2

Custom Build

• Version: 5.0.24-log
• Default Character Set: utf8
• Port: 3306
• Binaries: /usr/local/bin
• Executable Libraries: /usr/local/libexec
• Configuration Info: /var/lib/mysql5/my.cnf
• Init Script: /etc/init.d/mysql5 (custom init script - see details)
• Data: /var/lib/mysql5
• Socket: /var/lib/mysql5/mysql.sock
• LAN to DMZ Firewall Port: Open to support-04
• Passwords: Old MySQL 4 style passwords to accomdate PHP4
• Query Cache: 16MB

Build Options

The server was built was the following options:

(Note: SSL support was not possible with the SuSE packaged openSSL which is from March 2004, SSL support will require a custom build of a modern version of openSSL).

#!/bin/bash
export CXX=gcc
export CFLAGS="-O3"
export CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti"
./configure \
--disable-parallel \
--prefix=/usr/local \
--sysconfdir=/etc/mysql5 \
--localstatedir=/var/lib/mysql5 \
--infodir=/usr/local/info \
--mandir=/usr/local/man \
--with-unix-socket=/var/lib/mysql5/mysql.sock \
--with-tcp-port=3306 \
--with-charset=utf8 \
--with-extra-charsets=all \
--enable-thread-safe-client \
--enable-assembler \
--with-mysql-ldflags=-all-static \
--with-client-ldflags=-all-static

Init Script

The init script that came with the source code is broken. The build system makes no attempt to incorporate any of the config info provided. It is also not written to cope with multiple MySQL servers running on the same host.

This is the custom version:

#!/bin/sh

basedir="/usr/local"
datadir="/var/lib/mysql5"

bindir="$basedir/bin"
defaultsfile="$datadir/my.cnf"
pid_file="$datadir/mysqld5.pid"
server_pid_file="$datadir/mysqld-ares.pid"
use_mysqld_safe=0
user=mysql
libexecdir="$basedir/libexec"

lsb_functions="/lib/lsb/init-functions"
source $lsb_functions

PATH="$PATH:$basedir/bin"
export PATH

other_args="$*"

mode=$1    # start or stop
shift

wait_for_pid () {
  i=0
  while test $i -lt 35 ; do
    sleep 1
    case "$1" in
      'created')
        test -s $pid_file && i='' && break
        ;;
      'removed')
        test ! -s $pid_file && i='' && break
        ;;
      *)
        echo "wait_for_pid () usage: wait_for_pid created|removed"
        exit 1
        ;;
    esac
    echo $echo_n ".$echo_c"
    i=`expr $i + 1`
  done

  if test -z "$i" ; then
    log_success_msg
  else
    log_failure_msg
  fi
}
# Safeguard (relative paths, core dumps..)
cd $basedir

case "$mode" in
  'start')
    # Start daemon

    manager=$bindir/mysqlmanager
    if test -x $libexecdir/mysqlmanager
    then
      manager=$libexecdir/mysqlmanager
    fi

    echo $echo_n "Starting MySQL"
    if test -x $manager -a "$use_mysqld_safe" = "0"
    then
      # Give extra arguments to mysqld with the my.cnf file. This script may
      # be overwritten at next upgrade.
      $manager --defaults-file=$defaultsfile --user=$user --pid-file=$pid_file >/dev/null 2>&1 &
      wait_for_pid created

    elif test -x $bindir/mysqld_safe
    then
      # Give extra arguments to mysqld with the my.cnf file. This script
      # may be overwritten at next upgrade.
      pid_file=$server_pid_file
      $bindir/mysqld_safe --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &
      wait_for_pid created
    else
      log_failure_msg "Couldn't find MySQL manager or server"
    fi
    ;;

  'stop')
    # Stop daemon. We use a signal here to avoid having to know the
    # root password.

    # If the manager pid_file doesn't exist, try the server's
    if test ! -s "$pid_file"
    then
      pid_file=$server_pid_file
    fi

    if test -s "$pid_file"
    then
      mysqlmanager_pid=`cat $pid_file`
      echo $echo_n "Shutting down MySQL"
      kill $mysqlmanager_pid
      # mysqlmanager should remove the pid_file when it exits, so wait for it.
      wait_for_pid removed
    else
      log_failure_msg "MySQL manager or server PID file could not be found!"
    fi
    ;;

  'restart')
    # Stop the service and regardless of whether it was
    # running or not, start it again.
    $0 stop  $other_args
    $0 start $other_args
    ;;

  'reload')
    if test -s "$server_pid_file" ; then
      mysqld_pid=`cat $server_pid_file`
      kill -HUP $mysqld_pid && log_success_msg "Reloading service MySQL"
      touch $server_pid_file
    else
      log_failure_msg "MySQL PID file could not be found!"
    fi
    ;;

  *)
    # usage
    echo "Usage: $0  {start|stop|restart|reload}"
    exit 1
    ;;
esac

my.cnf

Some changes made to the stock config file:

[mysqld]
max_connections = 300
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
default-character-set=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
query_cache_type=1
query_cache_size=16777216
ft_min_word_len=3

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld5.log
pid-file=/var/run/mysqld5/mysqld.pid

[client]
default-character-set=utf8