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