Administering Multi Tenent MySQL Installations

Problem

Table of Contents

When we have many MySQL installation in same hardware and many MySQL instances running from different base directories,Administration can get into messy ebecause we need to remember each instance’s base directory, port, socket, data directory, error log file etc.
ofcource DBA can maintain this information in a spreadsheet and copy / paste.
it works if you have only few databases to manage. and things can get even messy if you are not regularly updating your spreadsheet.

Idea !

configuration file .cnf (parameter file) contains all information about an instance.
why can’t i read those files and switch the environment variables accordingly?

How to read .cnf file ?

its simple mysql comes with a utility named my_print_defaults!. so work is done!.
Hay wait..wait. but how to parse it and put it in environment variables?
Eureka!..mysqld_safe already does it..let me copy and paste few lines from there

Desinging the solution

for easy administration i decide to keep all configuraiton files (.cnf) files in same location:
/u01/app/oracle/mysql
Now it is matter of just listing all .cnf file and set the environment variable accoding to selection.
for parsing the parameter file values, use parse_arguments function from mysqld_safe.

Code

#!/bin/sh
#------------------------------------------------------------
# This file contains Public Domain code
#   Jobin Augustine - 2-August-2013
#------------------------------------------------------------

#---------location of cnf files-----
CNF_DIR=/u01/app/oracle/mysql
#---------------------------------------
cd $CNF_DIR
ls *.cnf
echo "Select a cnf file from above list :"
read CNF
parse_arguments() {
  # We only need to pass arguments through to the server if we don't
  # handle them here.  So, we collect unrecognized options (passed on
  # the command line) into the args variable.
  pick_args=
  if test "$1" = PICK-ARGS-FROM-ARGV
  then
    pick_args=1
    shift
  fi

  for arg do
    # the parameter after "=", or the whole $arg if no match
    val=`echo "$arg" | sed -e 's;^--[^=]*=;;'`
    # what's before "=", or the whole $arg if no match
    optname=`echo "$arg" | sed -e 's/^\(--[^=]*\)=.*$/\1/'`
    # replace "_" by "-" ; mysqld_safe must accept "_" like mysqld does.
    optname_subst=`echo "$optname" | sed 's/_/-/g'`
    arg=`echo $arg | sed "s/^$optname/$optname_subst/"`
    case "$arg" in
      # these get passed explicitly to mysqld
      --basedir=*) MY_BASEDIR_VERSION="$val" ;;
      --datadir=*) DATADIR="$val" ;;
      --pid-file=*) pid_file="$val" ;;
      --plugin-dir=*) PLUGIN_DIR="$val" ;;
      --user=*) user="$val"; SET_USER=1 ;;

      # these might have been set in a [mysqld_safe] section of my.cnf
      # they are added to mysqld command line to override settings from my.cnf
      --log-error=*) err_log="$val" ;;
      --port=*) mysql_tcp_port="$val" ;;
      --socket=*) mysql_unix_port="$val" ;;

      # mysqld_safe-specific options - must be set in my.cnf ([mysqld_safe])!
      --core-file-size=*) core_file_size="$val" ;;
      --ledir=*) ledir="$val" ;;
      --malloc-lib=*) set_malloc_lib "$val" ;;
      --mysqld=*) MYSQLD="$val" ;;
      --mysqld-version=*)
        if test -n "$val"
        then
          MYSQLD="mysqld-$val"
          PLUGIN_VARIANT="/$val"
        else
          MYSQLD="mysqld"
        fi
        ;;
      --nice=*) niceness="$val" ;;
      --open-files-limit=*) open_files="$val" ;;
      --open_files_limit=*) open_files="$val" ;;
      --skip-kill-mysqld*) KILL_MYSQLD=0 ;;
      --syslog) want_syslog=1 ;;
      --skip-syslog) want_syslog=0 ;;
      --syslog-tag=*) syslog_tag="$val" ;;
      --timezone=*) TZ="$val"; export TZ; ;;
      --help) usage ;;

      *)
        if test -n "$pick_args"
        then
          append_arg_to_args "$arg"
        fi
        ;;
    esac
  done
}

parse_arguments `my_print_defaults --defaults-file=$CNF mysqld`
echo 'DATADIR:'$DATADIR
echo 'BASE:'$MY_BASEDIR_VERSION
echo 'PORT:'$mysql_tcp_port
echo 'SOCKET:'$mysql_unix_port
echo 'ALERT:'$err_log
export SOCKET=$mysql_unix_port
export PORT=$mysql_tcp_port
export PIDFILE=$pid_file
export DATADIR=$DATADIR
export BASE=$MY_BASEDIR_VERSION
export ALERT=$err_log
alias dba='mysql -u root -S $SOCKET'
alias myshut='mysqladmin shutdown -u root -S $SOCKET'
alias mystart='$BASE/bin/mysqld_safe --defaults-file=$CNF_DIR/$CNF &'

How to use it

I put the about into a file named “my” and given execute permission.
and kept this file in a directory in PATH.
here it is:

$ . my <---execute the script with dot (.) infront
my2.cnf  my3.cnf  my4.cnf  my5.cnf  my6.cnf  my7.cnf  my.cnf  my-new.cnf
Select a cnf file from above list :
my7.cnf     <--- Your input from the above list of cnf files
DATADIR:/u01/app/oracle/dat/data_8/data
BASE:/u01/app/oracle/mysql
PORT:3308
SOCKET:/tmp/mysql3.sock
ALERT:/u01/app/oracle/datadir/mysql-error3.log

Now All environment variables and aliases will be set accordingly
see this

$ dba
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql>

$ echo $SOCKET
/tmp/mysql3.sock