Technology Blog

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

 

Important Note:

Since these were Rackspace servers – the default repository that they connect to does not have the xen kernel packages and xen software on them.  We had to call and have Rackspace add the capability of using certain commands like “yum install xen”

Step 1.1:Install the xen kernel and xen.

 

# yum --disableexcludes=all install kernel-xen xen

 

Step 1.2: Reboot

 

# reboot

 

 

Step 1.3:Ensure the new kernel loaded.  You should see xen after the kernel version:

 

# uname –a

 

Linux web1.myhostname.com 2.6.18-238.9.1.el5xen #1 SMP Fri Mar 18 12:53:42 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

 

 

Step 1.4:Ensure xen is running on boot.

 

# xm list

Name                                      ID Mem(MiB) VCPUs State   Time(s)

Domain-0                                   0     7989     8 r-----   6268.9

 

Congrats the host machine is setup!

Step 2: Let’s setup a CentOS virtual machine.  Virt-install:

 

# virt-install -–prompt

 

What is the name of your virtual machine? mnode1

How much RAM should be allocated (in megabytes)? 8024

What would you like to use as the disk (file path)? /home/xen/mnode1

How large would you like the disk (/home/xen/mnode1) to be (in gigabytes)? 100

What is the install URL? http://mirror.cogentco.com/pub/linux/centos/5.6/os/x86_64/

 

<code>Starting install...</code>

Retrieving file .treeinfo...                             |  417 B     00:00

Retrieving file vmlinuz...                               | 2.1 MB     00:01

Retrieving file initrd.img...                            | 7.9 MB     00:03

Creating storage file...                                 | 100 GB     00:00

Creating domain...                                       |    0 B     00:05

error: Failed to get local hostname

Domain installation still in progress. You can reconnect to

the console to complete the installation process.

 

Step 4:At this point you need to connect to the virtual machines “console” to continue.

# xm console <hostname>

Step 5:Go through the centos install…..I’ll omit this process.  But in short you WILL need to know the ip address, subnet, and gateway of your server.

 

 

Install iptables (Hopefully, you’ve done this already)

# yum install iptables* -y

Download, unpack, install of APF from source:

 

# cd /usr/local/src

# wget http://www.rfxn.com/downloads/apf-current.tar.gz

# tar -zxf apf-current.tar.gz

# cd apf-9*

# ./install.sh

 

Cleanup source install files:

# rm -Rf /usr/local/src/apf-9* && cd

 

Backup original APF config:

# cp /etc/apf/conf.apf /etc/apf/conf.apf.bak

 

Edit APF Configuration file:

# nano -w /etc/apf/conf.apf

 

Change the following settings:

RAB="0" to RAB="1"

RAB_PSCAN_LEVEL=”2″ to RAB_PSCAN_LEVEL=”3″

TCR_PASS=”1″ to TCR_PASS=”0″

DLIST_PHP=”0″ to DLIST_PHP=”1″

DLIST_SPAMHAUS=”0″ to DLIST_SPAMHAUS=”1″

DLIST_DSHIELD=”0″ to DLIST_DSHIELD=”1″

DLIST_RESERVED=”0″ to DLIST_RESERVED=”1″

IG_TCP_CPORTS=”21,22,25,53,80,443,3306″

IG_UDP_CPORTS=”″

EGF=”1”

EG_TCP_CPORTS=”22,3306,80,443″

EG_UDP_CPORTS=”″

DEVEL_MODE=”0”

 

Step 20: Reload APF:

# /usr/local/sbin/apf–r

Step 21:Make sure APF starts automatically after reboot:

# chkconfig --add apf

# chkconfig –level 345 apf on