Install vendor’s mysql .. the debian (quick n dirty) way

Why ? It happen that one of my queries involving joining with a derived table was too slow. I am running the latest mysql-server version in debian testing (wheezy), version 5.5.30.
A Simplified example of the slow query

SELECT a, b
FROM table1
INNER JOIN (
   SELECT d, r
   FROM table 2
) AS derived1

This kind of query, with one more derived tables and 3 more joins on a final result set of about 200.000 rows, took 80 secs. I didn’t like it too much.
Then using EXPLAIN mysql statement it seemed that the 2 derived tables didn’t have ANY indexes, while all the others had.

Damn! Now what ? A search on mysql’s site popped me to a discussion about subquerry materialization in mysql 5.6. In a few words, this a a new amazing feature of mysql 5.6 that between others, creates on the fly temporary tables with indexes for many categories of derived tables.

For your information, the same query on the same result set, after updating to mysql 5.6.11 took about 4 seconds. A x20 speedup, deserves the hustle to do the update.


Time for an upgrade, I thought. Debian repositories are not updated yet, even unstable. In the download section of mysql server community edition, one can find a Debian package. I downloaded mysql-5.6.11-debian6.0-x86_64.deb.

When installing it (dpkg -i ), I realized that the only files installed were under /opt/mysql/server-5.6. One way to start the server is to use the provided init script (support-files/mysql-server). And here is a complete guide to install mysql server community edition (for Ubuntu, but applies to Debian also).

I followed another approach, as I wanted to be as close to Debian setup as possible. I created a new init script (/etc/init.d/mysql-opt) based on the Debian one and configured it to work.

NOTE: This script has dependencies on files from the debian’s mysql-server package, so do not uninstall it.

This script also uses a different my.cnf, /etc/mysql/my-opt.cnf.
You can create it by copying /etc/my.cnf to /etc/mysql/my-opt.cnf.
# cp /etc/my.cnf /etc/mysql/my-opt.cnf
A few things have to be modified.

  • Under the section [mysqld_safe], add the following line:
    mysqld = mysqld --defaults-file=/etc/mysql/my-opt.cnf
  • Under the section [mysqld], modify the following lines:
    basedir = /opt/mysql/server-5.6
    lc-messages-dir = /opt/mysql/server-5.6/share
  • Also, I had enabled
    log_slow_queries =
    which now is deprecated and should be replaced by the following two lines:
    slow-query-log = TRUE
    slow-query-log-file =

Do the following as root.

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

Keep a data backup just in case you will need to revert the old data
# cp -rp /var/lib/mysql /var/lib/mysql.old

Then, download the mysql-opt init script (see bottom) to /etc/init.d/mysql-opt and make it executable.
# chmod a+x /etc/init.d/mysql-opt

Test mysql-opt starts.
NOTE: This will update you current mysql internal tables. You did make the data backup, didn’t you ?
# /etc/init.d/mysql-opt start

Check the logs for errors. During debuging a server that doesn’t start you can manually issue
# /opt/mysql/server-5.6/bin/mysqld_safe --defaults-file=/etc/mysql/my-opt.cnf

If everything went ok, you can enable the opt mysql server during startup.
Disable the old script from running at init.
# update-rc.d -f mysql remove

Check that all links from you default runlevel (assuming 3) are missing. The following command should list nothing. If not manually delete mysql init script symlinks from all runlevels (/etc/rc[0,1,2,3,4,5,6].d)
# ls -l /etc/rc3.d | grep mysql

Install the new, ignoring the warnings.
# update-rc.d mysql-opt defaults

Verify it is installed.
# ls -l /etc/rc3.d | grep mysql

The previous command should now output something like:
lrwxrwxrwx 1 root root 19 May 10 15:55 S17mysql-opt -> ../init.d/mysql-opt

That’s it.

Here is the /etc/init.d/mysql-opt script

#!/bin/bash
#
### BEGIN INIT INFO
# Provides:          mysql
# Required-Start:    $remote_fs $syslog
# Required-Stop:     $remote_fs $syslog
# Should-Start:      $network $time
# Should-Stop:       $network $time
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: Start and stop the mysql database server daemon in the opt installation
# Description:       Controls the main MySQL database server daemon "mysqld"
#                    and its wrapper script "mysqld_safe".
### END INIT INFO
#
set -e
set -u
${DEBIAN_SCRIPT_DEBUG:+ set -v -x}

# PFX=/usr/
PFX=/opt/mysql/server-5.6

test -x $PFX/bin/mysqld_safe || exit 0

. /lib/lsb/init-functions

SELF=$(cd $(dirname $0); pwd -P)/$(basename $0)
CONF=/etc/mysql/my-opt.cnf
MYADMIN="$PFX/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"

# priority can be overriden and "-s" adds output to stderr
ERR_LOGGER="logger -p daemon.err -t /etc/init.d/mysql-opt -i"

# Safeguard (relative paths, core dumps..)
cd /
umask 077

# mysqladmin likes to read /root/.my.cnf. This is usually not what I want
# as many admins e.g. only store a password without a username there and
# so break my scripts.
export HOME=/etc/mysql/

## Fetch a particular option from mysql's invocation.
#
# Usage: void mysqld_get_param option
mysqld_get_param() {
	/$PFX/bin/mysqld --print-defaults \
		| tr " " "\n" \
		| grep -- "--$1" \
		| tail -n 1 \
		| cut -d= -f2
}

## Do some sanity checks before even trying to start mysqld.
sanity_checks() {
  # check for config file
  if [ ! -r $CONF ]; then
    log_warning_msg "$0: WARNING: $CONF cannot be read. See README.Debian.gz"
    echo                "WARNING: $CONF cannot be read. See README.Debian.gz" | $ERR_LOGGER
  fi

  # check for diskspace shortage
  datadir=`mysqld_get_param datadir`
  if LC_ALL=C BLOCKSIZE= df --portability $datadir/. | tail -n 1 | awk '{ exit ($4>4096) }'; then
    log_failure_msg "$0: ERROR: The partition with $datadir is too full!"
    echo                "ERROR: The partition with $datadir is too full!" | $ERR_LOGGER
    exit 1
  fi
}

## Checks if there is a server running and if so if it is accessible.
#
# check_alive insists on a pingable server
# check_dead also fails if there is a lost mysqld in the process list
#
# Usage: boolean mysqld_status [check_alive|check_dead] [warn|nowarn]
mysqld_status () {
    ping_output=`$MYADMIN ping 2>&1`; ping_alive=$(( ! $? ))

    ps_alive=0
    pidfile=`mysqld_get_param pid-file`
    if [ -f "$pidfile" ] && ps `cat $pidfile` >/dev/null 2>&1; then ps_alive=1; fi
    
    if [ "$1" = "check_alive"  -a  $ping_alive = 1 ] ||
       [ "$1" = "check_dead"   -a  $ping_alive = 0  -a  $ps_alive = 0 ]; then
	return 0 # EXIT_SUCCESS
    else
  	if [ "$2" = "warn" ]; then
  	    echo -e "$ps_alive processes alive and '$MYADMIN ping' resulted in\n$ping_output\n" | $ERR_LOGGER -p daemon.debug
	fi
  	return 1 # EXIT_FAILURE
    fi
}

#
# main()
#

case "${1:-''}" in
  'start')
	sanity_checks;
	# Start daemon
	log_daemon_msg "Starting MySQL database server opt" "mysqld"
	if mysqld_status check_alive nowarn; then
	   log_progress_msg "already running"
	   log_end_msg 0
	else
	    # Could be removed during boot
	    test -e /var/run/mysqld || install -m 755 -o mysql -g root -d /var/run/mysqld

	    # Start MySQL! 
  	    $PFX/bin/mysqld_safe --defaults-file=$CONF > /dev/null 2>&1 &

	    # 6s was reported in #352070 to be too few when using ndbcluster
	    for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14; do
                sleep 1
	        if mysqld_status check_alive nowarn ; then break; fi
		log_progress_msg "."
	    done
	    if mysqld_status check_alive warn; then
                log_end_msg 0
	        # Now start mysqlcheck or whatever the admin wants.
		
		(
			# Taken from /etc/mysql/debian-start
			#
			# This script is executed by "/etc/init.d/mysql" on every (re)start.
			# 
			# Changes to this file will be preserved when updating the Debian package.
			#
			
			source /usr/share/mysql/debian-start.inc.sh

			MYSQL="$PFX/bin/mysql --defaults-file=/etc/mysql/debian.cnf"
			MYADMIN="$PFX/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
			MYUPGRADE="$PFX/bin/mysql_upgrade --defaults-extra-file=/etc/mysql/debian.cnf"
			MYCHECK="$PFX/bin/mysqlcheck --defaults-file=/etc/mysql/debian.cnf"
			MYCHECK_SUBJECT="WARNING: mysqlcheck has found corrupt tables"
			MYCHECK_PARAMS="--all-databases --fast --silent"
			MYCHECK_RCPT="root"

			# The following commands should be run when the server is up but in background
			# where they do not block the server start and in one shell instance so that
			# they run sequentially. They are supposed not to echo anything to stdout.
			# If you want to disable the check for crashed tables comment
			# "check_for_crashed_tables" out.  
			# (There may be no output to stdout inside the background process!)
			log_action_msg "Checking for tables which need an upgrade, are corrupt or were not closed cleanly. "
			(
			  upgrade_system_tables_if_necessary;
			  check_root_accounts;
			  check_for_crashed_tables;
			) >&2 & 
		)
	        #  output=$(/etc/mysql/debian-start)
		# [ -n "$output" ] && log_action_msg "$output"
	    else
	        log_end_msg 1
		log_failure_msg "Please take a look at the syslog"
	    fi
	fi
	;;

  'stop')
	# * As a passwordless mysqladmin (e.g. via ~/.my.cnf) must be possible
	# at least for cron, we can rely on it here, too. (although we have 
	# to specify it explicit as e.g. sudo environments points to the normal
	# users home and not /root)
	log_daemon_msg "Stopping MySQL database server" "mysqld"
	if ! mysqld_status check_dead nowarn; then
	  set +e
	  shutdown_out=`$MYADMIN shutdown 2>&1`; r=$?
	  set -e
	  if [ "$r" -ne 0 ]; then
	    log_end_msg 1
	    [ "$VERBOSE" != "no" ] && log_failure_msg "Error: $shutdown_out"
	    log_daemon_msg "Killing MySQL database server by signal" "mysqld"
	    killall -15 mysqld
            server_down=
	    for i in 1 2 3 4 5 6 7 8 9 10; do
              sleep 1
              if mysqld_status check_dead nowarn; then server_down=1; break; fi
            done
          if test -z "$server_down"; then killall -9 mysqld; fi
	  fi
        fi

        if ! mysqld_status check_dead warn; then
	  log_end_msg 1
	  log_failure_msg "Please stop MySQL manually and read /usr/share/doc/mysql-server-5.5/README.Debian.gz!"
	  exit -1
	else
	  log_end_msg 0
        fi
	;;

  'restart')
	set +e; $SELF stop; set -e
	$SELF start 
	;;

  'reload'|'force-reload')
  	log_daemon_msg "Reloading MySQL database server" "mysqld"
	$MYADMIN reload
	log_end_msg 0
	;;

  'status')
	if mysqld_status check_alive nowarn; then
	  log_action_msg "$($MYADMIN version)"
	else
	  log_action_msg "MySQL is stopped."
	  exit 3
	fi
  	;;

  *)
	echo "Usage: $SELF start|stop|restart|reload|force-reload|status"
	exit 1
	;;
esac

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s