Technology Blog

MySQL

Find Databases created outside of Plesk

by on Sep.10, 2014, under MySQL, Plesk

So today I needed a quick script to list all of the mysql databases on the system and compare that to the databases that were created inside of plesk. I wanted to print any databases that were created outside of plesk.

Why you might ask? It came to my attention that some of our system admins were creating databases manually through command line and setting up users that way when doing code deployments onto our shared plesk servers.

Whats wrong with that? Well if the database is outside of plesk, then plesk backup system, and plesk migration manager won’t know about the databases, nor the db users/passwords. This means if you are relying on plesk backup xml, or plesk migration manager for your server upgrades or disaster recovery, you’re going to have an issue with missing databases at some point!

The code is simple and is below. From memory it should work on plesk 8-11 and I have tested it on v10/11 myself.

Next to add to the list, is to set this up as a cron and email our general support queue if a new db is created outside of the plesk system.

To install / run, copy the below into a file called db_checker.php, then at a command line run “php db_checker.php”. You will need to be the root user to run this script.

Leave a Comment more...

Shell Script to Optimize all tables for a database

by on Feb.16, 2012, under Linux, MySQL

This shell script finds all the tables for a database and run a command against it.

 

See FileĀ optimize-tables

The file’s contents are below as well:

#!/bin/sh

# this shell script finds all the tables for a database and run a command against it
# @usage "mysql_tables.sh --optimize MyDatabaseABC"
# @date 6/14/2006
# @version 1.1 - 1/28/2007 - add repair
# @version 1.0 - 6/14/2006 - first release
# @author Son Nguyen

DBNAME=$2

printUsage() {
  echo "Usage: $0"
  echo " --optimize <dbname>"
  echo " --repair <dbname>"
  return
}


doAllTables() {
  # get the table names
  TABLENAMES=`mysql -D $DBNAME -e "SHOW TABLES\G;"|grep 'Tables_in_'|sed -n 's/.*Tables_in_.*: \([_0-9A-Za-z]*\).*/\1/p'`

  # loop through the tables and optimize them
  for TABLENAME in $TABLENAMES
  do
    mysql -D $DBNAME -e "$DBCMD TABLE $TABLENAME;"
  done
}

if [ $# -eq 0 ] ; then
  printUsage
  exit 1
fi

case $1 in
  --optimize) DBCMD=OPTIMIZE; doAllTables;;
  --repair) DBCMD=REPAIR; doAllTables;;
  --help) printUsage; exit 1;;
  *) printUsage; exit 1;;
esac

Leave a Comment more...

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!

Visit our friends!

A few highly recommended friends...