Categories
Grep Linux MySQL Passwords Windows Windows 2003

MySQL Backup Shell Script

***TO CREATE BACKUP SCRIPT ON LOCAL SERVER
mkdir /root/bin
vi /root/bin/mysqlbak.sh (then copy script into this file)
change parameters in script to match database
chmod 755 /root/bin/mysqlback.sh

***TO VERIFY SCRIPT WORKS
1. run this command
/root/bin/mysqlbak.sh
2. when script is done, check subdirs of /var/backup/db/daily for tarred & gzipped backups

***TO SCHEDULE THIS TO RUN DAILY, CREATE SYMBOLIC LINK
ln -s /root/bin/mysqlbak.sh /etc/cron.daily/mysqlbak.sh

***TO RESTORE
mysql -uUSER -pPASSWORD DBNAME_RESTORE < DBNAME_BACKUPFILE.sql

#!/bin/bash
#
# MySQL Backup Script
# VER. 2.5 – http://sourceforge.net/projects/automysqlbackup/
# Copyright (c) 2002-2003 wipe_out@lycos.co.uk
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#
#=====================================================================
#=====================================================================
# Set the following variables to your system needs
# (Detailed instructions below variables)
#=====================================================================

# crontab entry that will work for this script
# 57 5 * * 2,3,4,5,6 /root/bin/mysqlbackup.sh

# Username to access the MySQL server e.g. dbuser
USERNAME=

# Username to access the MySQL server e.g. password
PASSWORD=

# Host name (or IP address) of MySQL server e.g localhost
DBHOST=localhost

# List of DBNAMES for Daily/Weekly Backup e.g. “DB1 DB2 DB3” – you can put “all” as the value to backup all

DBNAMES=” mysql phpMyAdmin”
# Backup directory location e.g /var/www/backup/db
BACKUPDIR=”/var/backup/db”

# Mail setup
# What would you like to be mailed to you?
# – log : send only log file
# – files : send log file and sql files as attachments (see docs)
# – stdout : will simply output the log to the screen if run manually.
# – quiet : Only send logs if an error occurs to the MAILADDR.
MAILCONTENT=”log”

# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
MAXATTSIZE=”4000″

# Email Address to send mail to? (user@domain.com)
MAILADDR=”backups@soundenterprises.com”

# ============================================================
# === ADVANCED OPTIONS ( Read the doc’s below for details )===
#=============================================================

# List of DBBNAMES for Monthly Backups.
MDBNAMES=”mysql $DBNAMES”

# List of DBNAMES to EXCLUDE if DBNAMES are set to all (must be in ” quotes)
DBEXCLUDE=””

# Include CREATE DATABASE in backup?
CREATE_DATABASE=yes

# Separate backup directory and file for each DB? (yes or no)
SEPDIR=yes

# Which day do you want weekly backups? (1 to 7 where 1 is Monday)
DOWEEKLY=7

# Choose Compression type. (gzip or bzip2)
COMP=gzip

# Compress communications between backup server and MySQL server?
COMMCOMP=no

# Additionally keep a copy of the most recent backup in a seperate directory.
LATEST=no

# The maximum size of the buffer for client/server communication. e.g. 16MB (maximum is 1GB)
MAX_ALLOWED_PACKET=

# For connections to localhost. Sometimes the Unix socket file must be specified.
SOCKET=

# Command to run before backups (uncomment to use)
#PREBACKUP=”/etc/mysql-backup-pre”

# Command run after backups (uncomment to use)
#POSTBACKUP=”/etc/mysql-backup-post”

#=====================================================================
# Options documantation
#=====================================================================
# Set USERNAME and PASSWORD of a user that has at least SELECT permission
# to ALL databases.
#
# Set the DBHOST option to the server you wish to backup, leave the
# default to backup “this server”.(to backup multiple servers make
# copies of this file and set the options for that server)
#
# Put in the list of DBNAMES(Databases)to be backed up. If you would like
# to backup ALL DBs on the server set DBNAMES=”all”.(if set to “all” then
# any new DBs will automatically be backed up without needing to modify
# this backup script when a new DB is created).
#
# If the DB you want to backup has a space in the name replace the space
# with a % e.g. “data base” will become “data%base”
# NOTE: Spaces in DB names may not work correctly when SEPDIR=no.
#
# You can change the backup storage location from /backups to anything
# you like by using the BACKUPDIR setting..
#
# The MAILCONTENT and MAILADDR options and pretty self explanitory, use
# these to have the backup log mailed to you at any email address or multiple
# email addresses in a space seperated list.
# (If you set mail content to “log” you will require access to the “mail” program
# on your server. If you set this to “files” you will have to have mutt installed
# on your server. If you set it to “stdout” it will log to the screen if run from
# the console or to the cron job owner if run through cron. If you set it to “quiet”
# logs will only be mailed if there are errors reported. )
#
# MAXATTSIZE sets the largest allowed email attachments total (all backup files) you
# want the script to send. This is the size before it is encoded to be sent as an email
# so if your mail server will allow a maximum mail size of 5MB I would suggest setting
# MAXATTSIZE to be 25% smaller than that so a setting of 4000 would probably be fine.
#
# Finally copy automysqlbackup.sh to anywhere on your server and make sure
# to set executable permission. You can also copy the script to
# /etc/cron.daily to have it execute automatically every night or simply
# place a symlink in /etc/cron.daily to the file if you wish to keep it
# somwhere else.
# NOTE:On Debian copy the file with no extention for it to be run
# by cron e.g just name the file “automysqlbackup”
#
# Thats it..
#
#
# === Advanced options doc’s ===
#
# The list of MDBNAMES is the DB’s to be backed up only monthly. You should
# always include “mysql” in this list to backup your user/password
# information along with any other DBs that you only feel need to
# be backed up monthly. (if using a hosted server then you should
# probably remove “mysql” as your provider will be backing this up)
# NOTE: If DBNAMES=”all” then MDBNAMES has no effect as all DBs will be backed
# up anyway.
#
# If you set DBNAMES=”all” you can configure the option DBEXCLUDE. Other
# wise this option will not be used.
# This option can be used if you want to backup all dbs, but you want
# exclude some of them. (eg. a db is to big).
#
# Set CREATE_DATABASE to “yes” (the default) if you want your SQL-Dump to create
# a database with the same name as the original database when restoring.
# Saying “no” here will allow your to specify the database name you want to
# restore your dump into, making a copy of the database by using the dump
# created with automysqlbackup.
# NOTE: Not used if SEPDIR=no
#
# The SEPDIR option allows you to choose to have all DBs backed up to
# a single file (fast restore of entire server in case of crash) or to
# seperate directories for each DB (each DB can be restored seperately
# in case of single DB corruption or loss).
#
# To set the day of the week that you would like the weekly backup to happen
# set the DOWEEKLY setting, this can be a value from 1 to 7 where 1 is Monday,
# The default is 6 which means that weekly backups are done on a Saturday.
#
# COMP is used to choose the copmression used, options are gzip or bzip2.
# bzip2 will produce slightly smaller files but is more processor intensive so
# may take longer to complete.
#
# COMMCOMP is used to enable or diable mysql client to server compression, so
# it is useful to save bandwidth when backing up a remote MySQL server over
# the network.
#
# LATEST is to store an additional copy of the latest backup to a standard
# location so it can be downloaded bt thrid party scripts.
#
# If the DB’s being backed up make use of large BLOB fields then you may need
# to increase the MAX_ALLOWED_PACKET setting, for example 16MB..
#
# When connecting to localhost as the DB server (DBHOST=localhost) sometimes
# the system can have issues locating the socket file.. This can now be set
# using the SOCKET parameter.. An example may be SOCKET=/private/tmp/mysql.sock
#
# Use PREBACKUP and POSTBACKUP to specify Per and Post backup commands
# or scripts to perform tasks either before or after the backup process.
#
#
#=====================================================================
# Backup Rotation..
#=====================================================================
#
# Daily Backups are rotated weekly..
# Weekly Backups are run by default on Saturday Morning when
# cron.daily scripts are run…Can be changed with DOWEEKLY setting..
# Weekly Backups are rotated on a 5 week cycle..
# Monthly Backups are run on the 1st of the month..
# Monthly Backups are NOT rotated automatically…
# It may be a good idea to copy Monthly backups offline or to another
# server..
#
#=====================================================================
# Please Note!!
#=====================================================================
#
# I take no resposibility for any data loss or corruption when using
# this script..
# This script will not help in the event of a hard drive crash. If a
# copy of the backup has not be stored offline or on another PC..
# You should copy your backups offline regularly for best protection.
#
# Happy backing up…
#
#=====================================================================
# Restoring
#=====================================================================
# Firstly you will need to uncompress the backup file.
# eg.
# gunzip file.gz (or bunzip2 file.bz2)
#
# Next you will need to use the mysql client to restore the DB from the
# sql file.
# eg.
# mysql –user=username –pass=password –host=dbserver database < /path/file.sql
# or
# mysql –user=username –pass=password –host=dbserver -e “source /path/file.sql” database
#
# NOTE: Make sure you use “” in the above command because
# you are piping the file.sql to mysql and not the other way around.
#
# Lets hope you never have to use this.. 🙂
#

#
#=====================================================================
#=====================================================================
#=====================================================================
#
# Should not need to be modified from here down!!
#
#=====================================================================
#=====================================================================
#=====================================================================
PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/mysql/bin
DATE=`date +%Y-%m-%d_%Hh%Mm` # Datestamp e.g 2002-09-21
DOW=`date +%A` # Day of the week e.g. Monday
DNOW=`date +%u` # Day number of the week 1 to 7 where 1 represents Monday
DOM=`date +%d` # Date of the Month e.g. 27
M=`date +%B` # Month e.g January
W=`date +%V` # Week Number e.g 37
VER=2.5 # Version Number
LOGFILE=$BACKUPDIR/$DBHOST-`date +%N`.log # Logfile Name
LOGERR=$BACKUPDIR/ERRORS_$DBHOST-`date +%N`.log # Logfile Name
BACKUPFILES=””
OPT=”–quote-names –opt” # OPT string for use with mysqldump ( see man mysqldump )

# Add –compress mysqldump option to $OPT
if [ “$COMMCOMP” = “yes” ];
then
OPT=”$OPT –compress”
fi

# Add –compress mysqldump option to $OPT
if [ “$MAX_ALLOWED_PACKET” ];
then
OPT=”$OPT –max_allowed_packet=$MAX_ALLOWED_PACKET”
fi

# Create required directories
if [ ! -e “$BACKUPDIR” ] # Check Backup Directory exists.
then
mkdir -p “$BACKUPDIR”
fi

if [ ! -e “$BACKUPDIR/daily” ] # Check Daily Directory exists.
then
mkdir -p “$BACKUPDIR/daily”
fi

if [ ! -e “$BACKUPDIR/weekly” ] # Check Weekly Directory exists.
then
mkdir -p “$BACKUPDIR/weekly”
fi

if [ ! -e “$BACKUPDIR/monthly” ] # Check Monthly Directory exists.
then
mkdir -p “$BACKUPDIR/monthly”
fi

if [ “$LATEST” = “yes” ]
then
if [ ! -e “$BACKUPDIR/latest” ] # Check Latest Directory exists.
then
mkdir -p “$BACKUPDIR/latest”
fi
eval rm -fv “$BACKUPDIR/latest/*”
fi

# IO redirection for logging.
touch $LOGFILE
exec 6>&1 # Link file descriptor #6 with stdout.
# Saves stdout.
exec > $LOGFILE # stdout replaced with file $LOGFILE.
touch $LOGERR
exec 7>&2 # Link file descriptor #7 with stderr.
# Saves stderr.
exec 2> $LOGERR # stderr replaced with file $LOGERR.

# Functions

# Database dump function
dbdump () {
mysqldump –user=$USERNAME –password=$PASSWORD –host=$DBHOST $OPT $1 > $2
return 0
}

# Compression function plus latest copy
SUFFIX=””
compression () {
if [ “$COMP” = “gzip” ]; then
gzip -f “$1”
echo
echo Backup Information for “$1”
gzip -l “$1.gz”
SUFFIX=”.gz”
elif [ “$COMP” = “bzip2” ]; then
echo Compression information for “$1.bz2”
bzip2 -f -v $1 2>&1
SUFFIX=”.bz2″
else
echo “No compression option set, check advanced settings”
fi
if [ “$LATEST” = “yes” ]; then
cp $1$SUFFIX “$BACKUPDIR/latest/”
fi
return 0
}

# Run command before we begin
if [ “$PREBACKUP” ]
then
echo ======================================================================
echo “Prebackup command output.”
echo
eval $PREBACKUP
echo
echo ======================================================================
echo
fi

if [ “$SEPDIR” = “yes” ]; then # Check if CREATE DATABSE should be included in Dump
if [ “$CREATE_DATABASE” = “no” ]; then
OPT=”$OPT –no-create-db”
else
OPT=”$OPT –databases”
fi
else
OPT=”$OPT –databases”
fi

# Hostname for LOG information
if [ “$DBHOST” = “localhost” ]; then
HOST=`hostname`
if [ “$SOCKET” ]; then
OPT=”$OPT –socket=$SOCKET”
fi
else
HOST=$DBHOST
fi

# If backing up all DBs on the server
if [ “$DBNAMES” = “all” ]; then
DBNAMES=”`mysql –user=$USERNAME –password=$PASSWORD –host=$DBHOST –batch –skip-column-names -e “show databases”| sed ‘s/ /%/g’`”

# If DBs are excluded
for exclude in $DBEXCLUDE
do
DBNAMES=`echo $DBNAMES | sed “s/b$excludeb//g”`
done

MDBNAMES=$DBNAMES
fi

echo ======================================================================
echo AutoMySQLBackup VER $VER
echo http://sourceforge.net/projects/automysqlbackup/
echo
echo Backup of Database Server – $HOST
echo ======================================================================

# Test is seperate DB backups are required
if [ “$SEPDIR” = “yes” ]; then
echo Backup Start Time `date`
echo ======================================================================
# Monthly Full Backup of all Databases
if [ $DOM = “01” ]; then
for MDB in $MDBNAMES
do

# Prepare $DB for using
MDB=”`echo $MDB | sed ‘s/%/ /g’`”

if [ ! -e “$BACKUPDIR/monthly/$MDB” ] # Check Monthly DB Directory exists.
then
mkdir -p “$BACKUPDIR/monthly/$MDB”
fi
echo Monthly Backup of $MDB…
dbdump “$MDB” “$BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql”
compression “$BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql”
BACKUPFILES=”$BACKUPFILES $BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql$SUFFIX”
echo ———————————————————————-
done
fi

for DB in $DBNAMES
do
# Prepare $DB for using
DB=”`echo $DB | sed ‘s/%/ /g’`”

# Create Seperate directory for each DB
if [ ! -e “$BACKUPDIR/daily/$DB” ] # Check Daily DB Directory exists.
then
mkdir -p “$BACKUPDIR/daily/$DB”
fi

if [ ! -e “$BACKUPDIR/weekly/$DB” ] # Check Weekly DB Directory exists.
then
mkdir -p “$BACKUPDIR/weekly/$DB”
fi

# Weekly Backup
if [ $DNOW = $DOWEEKLY ]; then
echo Weekly Backup of Database ( $DB )
echo Rotating 5 weeks Backups…
if [ “$W” -le 05 ];then
REMW=`expr 48 + $W`
elif [ “$W” -lt 15 ];then
REMW=0`expr $W – 5`
else
REMW=`expr $W – 5`
fi
eval rm -fv “$BACKUPDIR/weekly/$DB_week.$REMW.*”
echo
dbdump “$DB” “$BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql”
compression “$BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql”
BACKUPFILES=”$BACKUPFILES $BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql$SUFFIX”
echo ———————————————————————-

# Daily Backup
else
echo Daily Backup of Database ( $DB )
echo Rotating last weeks Backup…
eval rm -fv “$BACKUPDIR/daily/$DB/*.$DOW.sql.*”
echo
dbdump “$DB” “$BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql”
compression “$BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql”
BACKUPFILES=”$BACKUPFILES $BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql$SUFFIX”
echo ———————————————————————-
fi
done
echo Backup End `date`
echo ======================================================================

else # One backup file for all DBs
echo Backup Start `date`
echo ======================================================================
# Monthly Full Backup of all Databases
if [ $DOM = “01” ]; then
echo Monthly full Backup of ( $MDBNAMES )…
dbdump “$MDBNAMES” “$BACKUPDIR/monthly/$DATE.$M.all-databases.sql”
compression “$BACKUPDIR/monthly/$DATE.$M.all-databases.sql”
BACKUPFILES=”$BACKUPFILES $BACKUPDIR/monthly/$DATE.$M.all-databases.sql$SUFFIX”
echo ———————————————————————-
fi

# Weekly Backup
if [ $DNOW = $DOWEEKLY ]; then
echo Weekly Backup of Databases ( $DBNAMES )
echo
echo Rotating 5 weeks Backups…
if [ “$W” -le 05 ];then
REMW=`expr 48 + $W`
elif [ “$W” -lt 15 ];then
REMW=0`expr $W – 5`
else
REMW=`expr $W – 5`
fi
eval rm -fv “$BACKUPDIR/weekly/week.$REMW.*”
echo
dbdump “$DBNAMES” “$BACKUPDIR/weekly/week.$W.$DATE.sql”
compression “$BACKUPDIR/weekly/week.$W.$DATE.sql”
BACKUPFILES=”$BACKUPFILES $BACKUPDIR/weekly/week.$W.$DATE.sql$SUFFIX”
echo ———————————————————————-

# Daily Backup
else
echo Daily Backup of Databases ( $DBNAMES )
echo
echo Rotating last weeks Backup…
eval rm -fv “$BACKUPDIR/daily/*.$DOW.sql.*”
echo
dbdump “$DBNAMES” “$BACKUPDIR/daily/$DATE.$DOW.sql”
compression “$BACKUPDIR/daily/$DATE.$DOW.sql”
BACKUPFILES=”$BACKUPFILES $BACKUPDIR/daily/$DATE.$DOW.sql$SUFFIX”
echo ———————————————————————-
fi
echo Backup End Time `date`
echo ======================================================================
fi
echo Total disk space used for backup storage..
echo Size – Location
echo `du -hs “$BACKUPDIR”`
echo
echo ======================================================================
echo If you find AutoMySQLBackup valuable please make a donation at
echo http://sourceforge.net/project/project_donations.php?group_id=101066
echo ======================================================================

# Run command when we’re done
if [ “$POSTBACKUP” ]
then
echo ======================================================================
echo “Postbackup command output.”
echo
eval $POSTBACKUP
echo
echo ======================================================================
fi

#Clean up IO redirection
exec 1>&6 6>&- # Restore stdout and close file descriptor #6.
exec 1>&7 7>&- # Restore stdout and close file descriptor #7.

if [ “$MAILCONTENT” = “files” ]
then
if [ -s “$LOGERR” ]
then
# Include error log if is larger than zero.
BACKUPFILES=”$BACKUPFILES $LOGERR”
ERRORNOTE=”WARNING: Error Reported – “
fi
#Get backup size
ATTSIZE=`du -c $BACKUPFILES | grep “[[:digit:][:space:]]total$” |sed s/s*total//`
if [ $MAXATTSIZE -ge $ATTSIZE ]
then
BACKUPFILES=`echo “$BACKUPFILES” | sed -e “s# # -a #g”` #enable multiple attachments
mutt -s “$ERRORNOTE MySQL Backup Log and SQL Files for $HOST – $DATE” $BACKUPFILES $MAILADDR < $LOGFILE #send via mutt
else
cat “$LOGFILE” | mail -s “WARNING! – MySQL Backup exceeds set maximum attachment size on $HOST – $DATE” $MAILADDR
fi
elif [ “$MAILCONTENT” = “log” ]
then
cat “$LOGFILE” | mail -s “MySQL Backup Log for $HOST – $DATE” $MAILADDR
if [ -s “$LOGERR” ]
then
cat “$LOGERR” | mail -s “ERRORS REPORTED: MySQL Backup error Log for $HOST – $DATE” $MAILADDR
fi
elif [ “$MAILCONTENT” = “quiet” ]
then
if [ -s “$LOGERR” ]
then
cat “$LOGERR” | mail -s “ERRORS REPORTED: MySQL Backup error Log for $HOST – $DATE” $MAILADDR
cat “$LOGFILE” | mail -s “MySQL Backup Log for $HOST – $DATE” $MAILADDR
fi
else
if [ -s “$LOGERR” ]
then
cat “$LOGFILE”
echo
echo “###### WARNING ######”
echo “Errors reported during AutoMySQLBackup execution.. Backup failed”
echo “Error log below..”
cat “$LOGERR”
else
cat “$LOGFILE”
fi
fi

if [ -s “$LOGERR” ]
then
STATUS=1
else
STATUS=0
fi

# Clean up Logfile
eval rm -f “$LOGFILE”
eval rm -f “$LOGERR”

exit $STATUS

Categories
Linux Networking Routing Windows Windows 2000 Windows 2003

Host-based routes Windows Server 2000 2003 XP

To display the entire contents of the IP routing table, type:

route print

To display the routes in the IP routing table that begin with 10., type:

route print 10.*

To add a default route with the default gateway address of 192.168.12.1, type:

route add 0.0.0.0 mask 0.0.0.0 192.168.12.1

To add a route to the destination 10.41.0.0 with the subnet mask of 255.255.0.0 and the next hop address of 10.27.0.1, type:

route add 10.41.0.0 mask 255.255.0.0 10.27.0.1

To add a persistent route to the destination 10.41.0.0 with the subnet mask of 255.255.0.0 and the next hop address of 10.27.0.1, type:

route -p add 10.41.0.0 mask 255.255.0.0 10.27.0.1

To add a route to the destination 10.41.0.0 with the subnet mask of 255.255.0.0, the next hop address of 10.27.0.1, and the cost metric of 7, type:

route add 10.41.0.0 mask 255.255.0.0 10.27.0.1 metric 7

To add a route to the destination 10.41.0.0 with the subnet mask of 255.255.0.0, the next hop address of 10.27.0.1, and using the interface index 0x3, type:

route add 10.41.0.0 mask 255.255.0.0 10.27.0.1 if 0x3

To delete the route to the destination 10.41.0.0 with the subnet mask of 255.255.0.0, type:

route delete 10.41.0.0 mask 255.255.0.0

To delete all routes in the IP routing table that begin with 10., type:

route delete 10.*

To change the next hop address of the route with the destination of 10.41.0.0 and the subnet mask of 255.255.0.0 from 10.27.0.1 to 10.27.0.25, type:

route change 10.41.0.0 mask 255.255.0.0 10.27.0.25

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/route.mspx?mfr=true

Categories
Blog Linux VPN Windows Windows 2003

TCP Optimization for Windows

I have recently been creating nasty batch files to alter default and non-existing networking settings to improve performance on file transfers for replication and came across this awesome tool that is free and does a great job.

We set our MTU to 1350 to take in to account firewall overhead in the vpn mesh. TCP Optimizer is the only tool you need, no installation necessary you just get the .exe to run and it can show you all the settings it is going to change. Highly recommended.

Find your optimum MTU by trying different non-fragmentable pings with specific sizes e.g. ping -f -l 1322 may work where ping -f -l -1323 may not. The highest value you get without an error is what you can use. Don’t rely on Wireshark results as it can’t tell if your firewall breaks up the packet into small chunks or not when looking at your server’s packet trace.

There is a related hotfix for local file copy issues: http://support.microsoft.com/default.aspx?scid=kb;EN-US;920739 for large file (500MB) and greater. This problem occurs when you copy large files locally from a fast disk to a slow disk. For example, this problem can occur when you copy large files from a cluster disk to local system disk.

PSSQL team blog has a nice performance implication of the scalable networking pack that shows it can hinder SQL server performance. http://blogs.msdn.com/psssql/archive/2008/10/01/windows-scalable-networking-pack-possible-performance-and-concurrency-impacts-to-sql-server-workloads.aspx
They mention you will need to disable SNP also which in in this Windows Server 2003 patch http://support.microsoft.com/default.aspx?scid=kb;EN-US;948496 or you can disable EnableTCPChimney, EnableRSS and EnableTCPA.

I found a good article on disabling TCP offloading at a NIC card manufacturer: http://www.alacritech.com/Support/FAQs/DisableOffload.aspx Test to see if helps for large file transfers and you need Windows 2003 SP2 or higher OS. Reboot is not required for this change.  I have attached a vb script that will update the interfaces window size and MTU (1350).  Just for your reference, you could manually find correct NIC interface use this command line to get value from command line:

reg query HKLMSYSTEMCurrentControlSetServicesTcpipParametersInterfaces /f AddressType /s

It creates:

[HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParametersInterfaces{Some Random GUID Value Here to Identify your NIC}]"MTU"=dword:00000546

My .reg file I use (Descriptions of options are found in this KB: http://support.microsoft.com/kb/314053):

Windows Registry Editor Version 5.00
REM Run this from command line: netsh int ip set chimney disabled
[HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParameters]"Tcp1323Opts"=dword:00000001"GlobalMaxTcpWindowSize"=dword:3fffffff"TcpWindowSize"=dword:0000ffff"EnablePMTUDiscovery"=dword:00000001
[HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesLanmanServerParameters]"SizReqBuf"=dword:0000ffff
[HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesNetbtParameters]"NodeType"=dword:8
[HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesNetbtParameters]"UseDnsOnlyForNameResolutions"=dword:1

Download: Interfaces.vbs.txt (1.29 KB)

Categories
Blog Linux Passwords SSH Windows Windows 2003

Cygwin SSHd on a Windows 2003 AMI Within Amazon EC2

Recently, I needed to configure a Windows 2003 AMI in EC2 to run a ssh server. I would have expected this to be a simple job, with a variety of choices for making this work, but in the end it was far more time consuming, complicated, and frustrating than I would have guessed. Here is a quick road map of what I did.

My initial thought was that there must be a free, native port of openssh for Windows that installs as a service and otherwise conforms to the Windows environment…wrong! I can’t tell you why this is the case — maybe ssh is just not a microsofty way of doing remote terminals and file transfers — but I couldn’t find anything resembling a free, functional port of openssh for Windows. I found a few blog posts that mentioned that people had tried this, but ultimately they gave up when faced with the integration between openssh’s user/group namespace functions and Windows’ user/group concepts (to say nothing of the differences between the Windows command prompt and the UNIX shells). And these blog posts ultimately suggested that it was easier to run sshd via cygwin than it would be to port sshd to run natively. So….cygwin time!

UNIX is my OS of choice, and I’ve had cygwin on every Windows box I have ever had, so it was a quick jump to download the cygwin installer and install the packages I needed on a freshly started Windows 2003 instance in EC2 (incidentally, I am running the 64-bit, large EC2 instance AMI of Windows 2003 Server with SQL Server Express and no Authentication Services). The openssh package comes with a simple script — ssh-host-config — to generate the server host keys and create the users needed for privilege separation, so it was a nice, simple, relatively painless install. There are a few things that the config script misses, however, which requires you to run it several times before it ultimately succeeds (although it is nice enough to point out the problem each time and prompt you to fix it). After playing with it, I came up with the following actions to perform before running ssh-host-config in order to make it succeed the first time without errors:

0) Add the following line to /cygwin.bat:
set CYGWIN=binmode tty ntsec


1) Run a new cygwin bash shell (after the edit of cygwin.bat) and enter:
mount -s --change-cygdrive-prefix /
chmod +r /etc/passwd /etc/group
chmod 755 /var



2) Run a new cygwin bash shell (to pick up the cygdrive prefix change) and enter:
ssh-host-config
-- yes for privilege separation
-- "binmode tty ntsec" for CYGWIN environment variable setting for the service
-- enter your password of choice for the cyg_server account



3) Enter the following to start sshd:
net start sshd


4) Open the Windows Firewall editor, and add an exception for TCP traffic on port 22 for sshd.

5) If you haven’t already done so, open up port 22 for your EC2 instance group (assuming you are running your instance in the default group):
ec2-authorize -p 22 default


If everything went well, sshd is running and available on port 22, and you can login normally via ssh from other machines. All that is left to do is bundle up a new AMI to capture the cygwin installation…and that should be a piece of cake, right? The updated EC2 API has a new method — ec2-bundle-instance — that kicks off an AMI bundling job for an EC2 instance running Windows, so it should be as simple as calling this method and then grabbing a beer to wait for it to complete. If only it were that simple…

Unlike the AMI bundling scripts for Linux-based EC2 instances, which are ultimately just packaging up the existing file system, the Windows AMI bundling mechanism needs to perform several Windows-specific functions that are ultimately a real pain in the neck. First and foremost is sysprep. Sysprep is Microsoft’s answer to the problem of Windows virtualization; apparently the simple cloning of a Windows installation is not acceptable, and a new Windows SID should be generated for each new instantiation of a Windows virtual image. Sysprep does some other things, too (search for sysprep on Microsoft’s support web site for a more complete description — I am certainly not an expert on it), but ultimately the SID generation is the one that causes problems for a lot of installed software…like cygwin. After bundling a new AMI and starting a new instance with it, I found that sshd is hosed for no apparent reason. Attempts to start sshd via “net start sshd” produce the following cryptic error message:

The CYGWIN sshd service is starting.
The CYGWIN sshd service could not be started.
The service did not report an error.More help is available by typing NET HELPMSG 3534.



WTF?
After several time-consuming iterations of start new instance -> install cygwin -> bundle new AMI -> start new AMI instance -> wonder why sshd is hosed, I found something in the HKEY_USERS tree of the Windows registry that changes after the bundling step. Prior to bundling, with a functioning cygwin/sshd, I see the following in the registry:

[HKEY_USERSS-1-5-21-2574196159-1727499900-3384088469-1013SoftwareCygnus Solutions]
[HKEY_USERSS-1-5-21-2574196159-1727499900-3384088469-1013SoftwareCygnus SolutionsCygwin]
[HKEY_USERSS-1-5-21-2574196159-1727499900-3384088469-1013SoftwareCygnus SolutionsCygwinmounts v2]
[HKEY_USERSS-1-5-21-2574196159-1727499900-3384088469-1013SoftwareCygnus SolutionsCygwinProgram Options]
[HKEY_USERSS-1-5-21-2574196159-1727499900-3384088469-500SoftwareCygnus Solutions]
[HKEY_USERSS-1-5-21-2574196159-1727499900-3384088469-500SoftwareCygnus SolutionsCygwin]
[HKEY_USERSS-1-5-21-2574196159-1727499900-3384088469-500SoftwareCygnus SolutionsCygwinmounts v2]
[HKEY_USERSS-1-5-21-2574196159-1727499900-3384088469-500SoftwareCygnus SolutionsCygwinProgram Options]

After bundling, in a new instance in which sshd is hosed, I see the following in the registry:

[HKEY_USERSS-1-5-21-4261372910-2505678249-1238160980-500SoftwareCygnus Solutions][HKEY_USERSS-1-5-21-4261372910-2505678249-1238160980-500SoftwareCygnus SolutionsCygwin]
[HKEY_USERSS-1-5-21-4261372910-2505678249-1238160980-500SoftwareCygnus SolutionsCygwinmounts v2]
[HKEY_USERSS-1-5-21-4261372910-2505678249-1238160980-500SoftwareCygnus SolutionsCygwinProgram Options]

All of the other registry entries related to cygwin remain the same before and after the bundling step, so my guess is that the loss of entries in the bundled instance is the source of the trouble. But what exactly are those entries?

Again, I’m no windows expert, but the entries in question appear to have the windows SID followed by a user identifier (e.g. in S-1-5-21-4261372910-2505678249-1238160980-500, S-1-5-21-4261372910-2505678249-1238160980 is the SID, and 500 is the user id). Looking at the /etc/passwd file for cygwin, the user id 500 corresponds to the Administrator account, and user id 1013 corresponds to the cyg_server account, used by sshd as a privileged account for switching effective user ids during login. So, my hypothesis is that the privileges for the cyg_server account are somehow lost by sysprep during the bundling step, and sshd is hosed without them in the new bundled AMI instance.To test my hypothesis, I decided to configure the AMI bundling step to skip sysprep. The base Windows EC2 AMIs come with an application in the start menu called “ec2Service Setting” that has a check box to enable/disable sysprep during AMI bundling, so it is easy enough to test this. However, I have no idea what happens to Windows if I disable sysprep during bundling, and I was not able to find a satisfactory answer via internet searches. The closest I got to an answer was to see several of the Amazon admins on the EC2 forum comment that it was not a good idea to disable sysprep if you were going to instantate multiple instances. I also found several documents online that discussed how sysprep was used to sanitize a Windows installation, generate a new SID, and make it generic for installation on any type of hardware. Since the virtual hardware of EC2 is, roughly speaking, identical (given that it is using Xen underneath the hood), I’m not too worried about the hardware issue. I have no idea about “sanitizing” the Windows instance or SID generation, though, so bundling without sysprep might mortally wound Windows (again…I’m no Windows expert). And I do want to run multiple instances from the bundled AMI, so that might be a non-starter as well. So I guess I will try the ready-shoot-aim approach of seeing what happens when I turn it off…

Compressing time, I started with a fresh Windows instance, installed cygwin and configured sshd like before, turned off sysprep and bundled it, started a new instance from the new bundled AMI, and…sshd still works. The new instance retains the SID that it had prior to bundling, and the registry entries are still there for the cyg_server account. Windows also appears to be working in all respects, but I’m not sure I could detect problems that might result internally from the omission of sysprep in the bundling. I guess I can run one more test, starting a bunch of instances at once, to see if having the same SID causes them to interfere with one another. I started four instances, running concurrently, and they each seem to be working fine. Or at least I can’t detect any problems.

So, in closing, it looks like I may have a solution: turn off sysprep if you want to use cygwin sshd in a bundled Windows AMI. Someone with more Microsoft kung-fu might be able to figure out how to make sysprep retain the registry entries for the cyg_server account, or maybe they would write a script to insert them directly into the registry at restart if they are missing…who knows. But for me, disabling sysprep seems to be the way to go. I found lots of other complaints on the internet about sysprep and what it does to installed software when the SID changes, so I’m guessing that there will be a lot of bundled AMIs in EC2 that are created with sysprep disabled. If there are, in fact, issues with multiple instances using the same SID, then I expect we will be reading about it in the EC2 forums, since everyone who creates a new AMI from the base Windows AMIs without sysprep will have the same base SID in their AMIs, and so on….