Mysql – Running reports across multiple MySQL databases with the same schema

MySQL

Every client of our online services has its own MySQL database. All databases have exactly same schema, however, a number of databases is constantly changing as we archive db for inactive client's as well as new db gets created after every signup.

Is there a way to run analytics in this kind of environment? There seems to be no way to run query across multiple databases. The same applies to ETL solutions that we have investigated.

Merging databases and updating app code seems like a major effort. Create merged db for reporting purposes using db triggers might be the way to go.

Currently, if we need to run some reports across db, we use PHP script to execute query on every single client db which is very inefficient.

Any recommendations?

Best Answer

Not everything can be done in standard commands, or in complicated combinations of SQL.

I would write in my application coding language (PHP, Java, ...) SQL to fetch the list of databases from information_schema, then iterate through that list, doing the analytics.

Related Solutions

Mysql – Running multiple instances on the same host

To answer your immediate question on how to set it up, I and Randy Melder addressed this question May 31, 2011.

DTest and Laurynas Biveinis answered this more recently (Sep 20, 2011).

Some swear by its usage these days.

Years ago, mysql used to provide a script called safe_mysqld which made it simple to have multiple instances of mysql. All you did was create a my.cnf for the what instance on whatever port you wanted. Then called:

safe_mysqld custom_my.cnf

MySQL stopped distributing safe_mysqld in favor of mysqld_multi.

However, I have learned how to use mysqld_safe. Believe it or not, because of understanding mysqld_safe, I actually wrote my own mysql multi-instance engine back in Feb 2011. It is in production use right now with many of my employer's clients. Here is how I did it:

First, make this service engine called /etc/init.d/mysqlservice

#!/bin/bash

# Source function library.
. /etc/rc.d/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network

WHICH=/usr/bin/which
ECHO=`${WHICH} echo`
GREP=`${WHICH} grep`

#
#  Check for a four digit number greater than 3306
#  Make sure the my.cnf for the Port Number Exists
#
P1=${1}
if [ "${P1}" == "" ]
then
  ${ECHO} "Please Specify a Port Number for MySQL (3307 - 3399)"
  exit
fi
X=`${ECHO} "${P1}" | ${GREP} -c "^[Hh][Ee][Ll][Pp]$"`
if [ ${X} -eq 1 ]
then
  ${ECHO} "Usage: service mysqlservice <port-number [3307-3399]> {start|stop|restart|status|mycnf}"
  exit
fi
X=`${ECHO} "${P1}" | ${GREP} -c "^33[0-9][0-9]$"`
if [ ${X} -eq 0 ]
then
  ${ECHO} "Please Specify a Port Number for MySQL (3307 - 3399)"
  exit
fi
MYSQLD_PORT=${P1}
if [ ${MYSQLD_PORT} -lt 3307 ]
then
  ${ECHO} "Please Specify a Port Number for MySQL (3307 - 3399)"
  exit
fi
MYCNF=/etc/my${MYSQLD_PORT}.cnf
if [ ! -f ${MYCNF}  ]
then
  ${ECHO} "${MYCNF} Does Not Exist"
  exit
fi
MYCNF_BACKUP=${MYCNF}_backup

NOHUP=`${WHICH} nohup`
SLEEP=`${WHICH} sleep`
TAIL=`${WHICH} tail`
AWK=`${WHICH} awk`
CAT=`${WHICH} cat`
RM=`${WHICH} rm | ${TAIL} -1 | ${AWK} '{print $1}'`
MYSQLD_SAFE=`${WHICH} mysqld_safe`
MYSQLADMIN=`${WHICH} mysqladmin`
MYSQL=`${WHICH} mysql`
MYSQL_CONN="-uroot -p<rootpassword> -P${MYSQLD_PORT} -h127.0.0.1"
MYSQL_PING_FILE=/tmp/MySQL${MYSQLD_PORT}Ping.txt
MYSQL_STAT_FILE=/tmp/MySQL${MYSQLD_PORT}Status.txt
MYSQL_ERROR_LOG=/var/log/mysqld${MYSQLD_PORT}.log
MYSQLD_START="${MYSQLD_SAFE} --defaults-file=${MYCNF} --port=${MYSQLD_PORT}"
MYSQLD_STOP="${MYSQLADMIN} ${MYSQL_CONN} shutdown"
MYSQLD_PING="${MYSQLADMIN} ${MYSQL_CONN} ping"
PROGNAME="MySQL (Port ${MYSQLD_PORT})"

#
#       This service will use mysqld_safe to run mysql server instances on other ports
#

start() {
  cd /tmp
  ${ECHO} -n $"Starting ${PROGNAME}"
  ${NOHUP} ${MYSQLD_START} 2>/dev/null 1>/dev/null &
  ATTEMPTS=0
  STARTING_MYSQLD=1
  MINUTES_TO_TRY=3
  (( TICKS_TO_TRY = MINUTES_TO_TRY*240 ))
  while [ ${STARTING_MYSQLD} -eq 1 ]
  do
    ${ECHO} -n "."
    ${SLEEP} 0.25
    READY_FOR_CONNECTIONS=`${TAIL} ${MYSQL_ERROR_LOG} | ${GREP} -c "ready for connections\.$"`
    (( ATTEMPTS++ ))
    if [ ${ATTEMPTS} -eq ${TICKS_TO_TRY} ] ; then STARTING_MYSQLD=0 ; fi
    if [ ${READY_FOR_CONNECTIONS}  -eq 1 ] ; then STARTING_MYSQLD=2 ; fi
  done
  ${ECHO}
  if [ ${STARTING_MYSQLD} -eq 2 ]
  then
    ${ECHO} "Started ${PROGNAME}"
  else
    ${TAIL} -30 ${MYSQL_ERROR_LOG}
  fi
}

stop() {
  ${ECHO} -n $"Stopping ${PROGNAME}"
  ${MYSQLD_STOP}
  ATTEMPTS=0
  STOPPING_MYSQLD=1
  MINUTES_TO_TRY=10
  (( TICKS_TO_TRY = MINUTES_TO_TRY*240 ))
  while [ ${STOPPING_MYSQLD} -eq 0 ]
  do
    ${ECHO} -n "."
    ${SLEEP} 0.25
    MYSQL_HAS_BEEN_SHUTDOWN=`${TAIL} ${MYSQL_ERROR_LOG} | ${GREP} -c "Shutdown complete$"`
    (( ATTEMPTS++ ))
    if [ ${ATTEMPTS} -eq ${TICKS_TO_TRY} ] ; then STOPPING_MYSQLD=0 ; fi
    if [ ${READY_FOR_CONNECTIONS}  -eq 1 ] ; then STOPPING_MYSQLD=2 ; fi
  done
  ${ECHO}
  if [ ${STOPPING_MYSQLD} -eq 2 ]
  then
    ${ECHO} "Stopped ${PROGNAME}"
  else
    ${TAIL} -30 ${MYSQL_ERROR_LOG}
  fi
}

# See how we were called.

${MYSQLD_PING} 2>/dev/null > ${MYSQL_PING_FILE}
MYSQL_RUNNING=`grep -c "mysqld is alive" ${MYSQL_PING_FILE}`
${RM} ${MYSQL_PING_FILE}

case "$2" in
  start)
    if [ ${MYSQL_RUNNING} -eq 0 ]
    then
      start
    else
      ${ECHO} ${PROGNAME} is Running
    fi
    ;;
  stop)
    if [ ${MYSQL_RUNNING} -eq 0 ]
    then
      ${ECHO} ${PROGNAME} is Not Running
    else
      stop
    fi
    ;;
  restart)
    stop
    ${SLEEP} 1
    start
    ;;
  status)
    if [ ${MYSQL_RUNNING} -eq 0 ]
    then
      ${ECHO} ${PROGNAME} is Not Running
    else
      ${MYSQL} ${MYSQL_CONN} -A -e"status" > ${MYSQL_PING_FILE}
      ${GREP} "Server version:" < ${MYSQL_PING_FILE}  > ${MYSQL_STAT_FILE}
      ${GREP} "UNIX socket:"    < ${MYSQL_PING_FILE} >> ${MYSQL_STAT_FILE}
      ${GREP} "Uptime:"   < ${MYSQL_PING_FILE} >> ${MYSQL_STAT_FILE}
      ${GREP} "Threads:"  < ${MYSQL_PING_FILE} >> ${MYSQL_STAT_FILE}
      ${CAT} ${MYSQL_STAT_FILE}
    fi
    ;;
  mycnf)
    less ${MYCNF}
    ;;
  edit)
    cp ${MYCNF} ${MYCNF_BACKUP}
    vi ${MYCNF}
    ;;
  *)
    ${ECHO} $"Usage: $0 {start|stop|restart|status|mycnf}"
    ${ECHO} $"Usage: $0 help"
    exit 1
esac

exit

Next, create mysql instance file for a specfic port.

For example, here is the service mysql3307

#!/bin/sh
#
# readahead:    Prereads programs required for startup into memory
#
# chkconfig: 2345 4 99
# description:  This service causes the programs used during startup \
#               to be loaded into memory before they are needed,\
#               thus improving startup performance
#
#

# Sanity checks.
[ -x /usr/sbin/readahead ] || exit 0

# Check for > 384 MB
#free -m | gawk '/Mem:/ {exit ($2 >= 384)?0:1}' || exit 0

# Source function library.
#. /etc/rc.d/init.d/functions

WHICH=/usr/bin/which
SERVICE=`${WHICH} service`

MYSQL=`${WHICH} mysql`
PORT_NUMBER=3307

# See how we were called.
case "$1" in
    start)      ${SERVICE} mysqlservice ${PORT_NUMBER} start    ;;
    stop)       ${SERVICE} mysqlservice ${PORT_NUMBER} stop     ;;
    status)     ${SERVICE} mysqlservice ${PORT_NUMBER} status   ;;
    restart)    ${SERVICE} mysqlservice ${PORT_NUMBER} restart  ;;
    mycnf)      ${SERVICE} mysqlservice ${PORT_NUMBER} mycnf    ;;
    edit)       ${SERVICE} mysqlservice ${PORT_NUMBER} edit     ;;
    *)
        echo $"Usage: $0 {start|stop|restart|status|mycnf}"
        ;;
esac

Make sure that the mysql instance file has the port number defined in PORT_NUMBER between 3307 and 3399.

As mysql instance file calls mysqlservice, please note that mysqlservice will check for the config file for the specfic port.

In the case of mysql3307, mysqlservice will look for /etc/my3307.cnf.

Make sure you have the following explicitly defined in /etc/my3307.cnf

[mysqld]
datadir=/var/lib/mysql3307
socket=/var/lib/mysql3307/mysql.sock
port=3307

[mysqld_safe]
log-error=/var/log/mysqld3307.log

The datadir and socket can be at places of your choosing.

To create mysql3308 and other services, following the same paradigm but use the exactly port number defined throughout.

Give it a Try !!!

Mysql – Help with COUNT across multiple columns

Given the new information (you should probably edit the question, not add an answer, but I assume you are not entitled to). First I don't understand the database design, do you always get 4 affected devices per fault? Anyhow, you can transpose your table like:

select affected_device_1 from faults
union all
select affected_device_2 from faults
union all
select affected_device_3 from faults
union all
select affected_device_4 from faults

I won't be efficient but it is the best I can think of. From there you can count the number of occurrences for each device:

select affected_device, count(1)
from (
    select affected_device_1 as affected_device from faults
    union all
    select affected_device_2 as affected_device from faults
    union all
    select affected_device_3 as affected_device from faults
    union all
    select affected_device_4 as affected_device from faults
) as T
group by affected_device;

Note that if a fault contains the same device twice the count will be wrong.

Best Answer

Related Solutions

Mysql – Running multiple instances on the same host

Mysql – Help with COUNT across multiple columns

Related Question