Thursday, December 31, 2009

[Level 3] Clone ZFS Permissions

One day, someone ask me how to clone ZFS permissions from one ZFS file system to another ZFS file system.
It seems that zfs command doesnot support the permission like getfacl setfacl to clone the file system permissions.
Therefore I try to write a script to implement that,
please refer to the following code.

#!/usr/bin/bash
showUsage() {
  cat <<EOF
Usage:
  $0 source_zfs target_zfs
Ex.
  $0 rpool/fs1 karajon/fs2
EOF
}

##################################### main

sSZFS=$1
sTZFS=$2
declare -i fPermSet=0
declare -i fLocalPerm=0

zfs allow $sSZFS | while read s
do
  ( echo $s | grep "^---- Permissions on " > /dev/null ) && continue
  ( echo $s | grep "^Permission sets:$" > /dev/null ) && fPermSet=1 && fLocalPerm=0 && continue
  ( echo $s | grep "^Local+Descendent permissions:$" > /dev/null ) && fLocalPerm=1 && fPermSet=0 && continue
  if [ $fPermSet -eq 1 ]
  then
    echo "zfs allow -s $s $sTZFS"

    zfs allow -s $s $sTZFS

  elif [ $fLocalPerm -eq 1 ]
  then
    echo "zfs allow -u ${s#user } $sTZFS"

    zfs allow -u ${s#user } $sTZFS

  fi
done



Wish this helps.

regards,
Stanley Huang

Wednesday, December 30, 2009

[Level 2] MySQL Query with Regular Expression.

If you  want to let your query more powerful,
you can use regular expression to enhance your SQL query.

mysql> create table testRegExp (name varchar(32));
mysql> insert into testRegExp values('Stanley'),('Christy'),('Joseph'),('Chantelle');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from testRegExp where name regexp '^(S|C)';
+-----------+
| name      |
+-----------+
| Stanley   |
| Christy   |
| Chantelle |
+-----------+
3 rows in set (0.00 sec)

mysql> 


Ref:
http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Wish this helps.

regards,
Stanley Huang

Sunday, December 27, 2009

[Level 2] MySQL XML Functions.

One day, a friends of mine asked me if MySQL support XML?
Actually, MySQL havs two XML functions, called "ExtractValue" and "UpdateValue".
You can use these two function as the following samples:


mysql> CREATE TABLE persons (
id int auto_increment primary key,
data text
);



mysql> INSERT INTO persons(data) values
('
<person>
  <name>stanley</name>
  <sex>m</sex>
  <addr>taipei</addr>
  <tels>
    <tel>12340001</tel>
    <tel>12340002</tel>
    <tel>12340003</tel>
  </tels>
</person>
'),
('
<person>
  <name>joseph</name>
  <sex>m</sex>
  <addr>taipei</addr>
  <tels>
    <tel>12350001</tel>
    <tel>12350002</tel>
    <tel>12350003</tel>
  </tels>
</person>
');


mysql> select
ExtractValue(data,'//person/tels/tel[1]'),
ExtractValue(data,'/person/tels/tel[2]'),
ExtractValue(data,'person/tels/tel[3]')
from persons
where id=1\G

*************************** 1. row ***************************
ExtractValue(data,'//person/tels/tel[1]'): 12340001
ExtractValue(data,'/person/tels/tel[2]'): 12340002
ExtractValue(data,'person/tels/tel[3]'): 12340003


mysql> select
data,
ExtractValue(data,'//person/name'),
UpdateXML(data,'//person/name','<name>christy</name>'),
ExtractValue(UpdateXML(data,'//person/name','<name>christy</name>'),'//person/name')
from persons
where id=1\G

*************************** 1. row ***************************
data: stanleymtaipei123400011234000212340003
ExtractValue(data,'//person/name'): stanley
UpdateXML(data,'//person/name','<name>christy</name>'): <name>christy</name>mtaipei123400011234000212340003
ExtractValue(UpdateXML(data,'//person/name','<name>christy</name>'),'//person/name'): christy





Ref:
http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html
http://www.w3.org/TR/xpath
http://www.zvon.org/xxl/XPathTutorial/General/examples.html

Wish this helps.

regards,
Stanley Huang

Friday, December 18, 2009

[Level 1] MySQL 5.5 new features -- Semisynchronous Replication.

MySQL 5.5 m2 has released.
In m2, MySQL 5.5 has a great called "Semisynchronous Replication".
How the semi works?

1. First, the client connect to server and be indicated that is semi or not.
2. If semi is enable on server site and there is at least one client and waits untilone semi client acknowledges that it has received.


please refer to the following link for more info.


Wish this helps.

regards,
Stanley Huang

Tuesday, December 15, 2009

[Level 3] Using DTrace for MySQL DB.

I'm glade to here that, in MySQL 5.4 beta, it supports that to use Solaris DTrace to monitor MySQL SQL statement.


About the probe for MySQL, please refer to the following link:
http://dev.mysql.com/doc/refman/5.4/en/dba-dtrace-mysqld-ref.html

I use one of the scripts to demo the result of DTrace,
http://dev.mysql.com/doc/refman/5.4/en/dba-dtrace-ref-query.html

The result as below:
Sessin 1:
[stanley@Stanley-NB]:/usr/mysql# mysql

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.4.3-beta MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

stanley@localhost:stanley mysql> show tables;
Empty set (0.00 sec)

stanley@localhost:stanley mysql>



Sessin 2:
root@Stanley-NB:/tmp# ./MySQL_query.d

Who                  Database             Query                                    Time(ms)
stanley@localhost    stanley              select @@version_comment limit 1         0       
stanley@localhost    stanley              select USER()                            0       
stanley@localhost    stanley              show tables                              2       


Wish this helps.

regards,
Stanley Huang

Thursday, December 10, 2009

[Level 3] Script for clone VirtualBox 3.1.0 .

Before, I wrote a script to clone VirtualBox with ZFS technology.
After new VirtualBox release, because of the architecture and command changed,
the previous script will occur error while running.
Therefore, I modify the script for version 3.1.0 later of VirtualBox.

#!/usr/bin/bash
showUsage() {
  cat <<EOF
Usage:
  $0 [c]reate/[d]elete New_Machine_Name Source_Machine_Name interface begin_index end_index
Ex.
  $0 [c]reate OS128a_MySQL OS128a yge0 1 10
  $0 [d]elete OS128a_MySQL OS128a yge0 1 10
  $0 [d]elete OS128a_MySQL OS128a yge0
VMMachine:
`showVMS | sed -e 's/^/  /'`
EOF
}

## Usage:
##   setUUID vdi_file
## Ex.
##   setUUID /Karajon/VBoxes/Guest/guest.vdi
setUUID() {
  sVDI=$1
  VBoxManage -q internalcommands setvdiuuid $sVDI
}

## Usage:
##   createVM vm_name os_type.
## Ex.
##   createVM S10u8_MySQL Solaris_64
## PS.
##   OS Type: Solaris Solaris_64 OpenSolaris OpenSolaris_64
createVM() {
  sVMName=$1
  sOSType=$2
  /opt/VirtualBox/VBoxManage -q createvm \
    --name $sVMName \
    --ostype $sOSType \
    --register
}

## Usage:
##   modifyVMNIC vm_name source_vm interface
## Ex.
##   modifyVMNIC S10u8_MySQL S10u8_Source yukonx0
modifyVMNIC() {
  sTVMName=$1
  sSVMName=$2
  sIF="$3 - Ethernet"
  sAttachedTo=bridged
  sAdapterType=82540EM
  while read n
  do
    /opt/VirtualBox/VBoxManage -q modifyvm $sTVMName \
      --nic$n $sAttachedTo \
      --nictype$n $sAdapterType \
      --cableconnected$n on \
      --bridgeadapter$n "$sIF"
  done <<EOF
  `VBoxManage -q showvminfo $sSVMName | grep -v disabled | grep "^NIC [1-4]:" | cut -d: -f1 | awk '{print($2)}'`
EOF
}

## Usage:
##   cloneStorageController target_vm source_vm
## Ex.
##   cloneStorageController S10u8_MySQL S10u8_Source
cloneStorageController() {
  sTVMName=$1
  sSVMName=$2
  declare -a aSC
  declare -i nCount=0
 
  VBoxManage -q showvminfo $sSVMName | grep -i "^Storage Controller .*:" | while read x
  do
    aSC[$nCount]="`echo $x | cut -d: -f2 | sed -e 's/^[ ]*//'`"
    nCount=$nCount+1
    if [ $nCount -eq 5 ]
    then
      sCT=`echo ${aSC[0]} | awk '{print($1)}' | tr [A-Z] [a-z]`
      VBoxManage -q storagectl $sTVMName \
        --name "${aSC[0]}" \
        --add $sCT \
        --controller ${aSC[1]}
      nCount=0
    fi
  done
}

## Usage:
##   modifyVM vm_name hda seq_no
## Ex.
##   modifyVM S10u8_MySQL /Karajon/VBox/$sVMName/$sVDI $nSeq
modifyVM() {
  sVMName=$1
  declare -i nMemory=768
  sHDA=$2
  #sDVD_ISO=/Karajon/ISOs/osol-1002-125-ai-x86.iso
 
  declare -i nVRDPport=3390+$3

  /opt/VirtualBox/VBoxManage -q modifyvm $sVMName \
    --memory $nMemory \
    --hda $sHDA \
    --boot1 disk --boot2 dvd \
    --audio solaudio --audiocontroller ac97 \
    --vrdp on --vrdpport $nVRDPport
    ## --dvd $sDVD_ISO \
}

## Usage:
##   startVM vm_name
## Ex.
##   startVM S10_U8
startVM() {
  sVMName=$1
  /opt/VirtualBox/VBoxManage -q startvm $sVMName
}

showVMS() {
  VBoxManage -q list vms
}

## Usage:
##   getVMOSType vm_name
## Ex.
##   getVMOSType OS111b_MySQL
getVMOSType() {
  sVMName=$1
  sOSType=""
  sGuestOS=`VBoxManage -q showvminfo $sVMName | grep "^Guest OS:" | cut -d: -f2`
  sOS=`echo $sGuestOS | awk '{print($1)}'`
  declare -i nVer=`echo $sGuestOS | cut -d\( -f2 | cut -d' ' -f1`
  sOSType="$sOS"_"$nVer"
  echo "$sOSType"
}

## Usage:
##   getVMHDA vm_name
## Ex.
##   getVMHDA OS111b_MySQL
getVMHDA() {
  sHDAFilter='IDE Controller (0, 0):'
  sVMName=$1
  sHDA=`VBoxManage -q showvminfo $sVMName | grep "^$sHDAFilter" | cut -d: -f2 | awk '{print($1)}'`
  echo $sHDA
}

## Usage:
##   getZFS vdi_file
## Ex.
##   getZFS /Karajon/VBoxes/OS111b_MySQL/abc.vdi
getZFS() {
  sVDIFile=$1
  sVDIFolder=`dirname $sVDIFile`
  sZFS=`zfs list -H | grep $sVDIFolder$ | awk '{print($1)}'`
  echo $sZFS
}

## Usage:
##   cloneFS source_zfs target_name index
## Ex.
##   sCloneFS=`cloneZFS Karajon/VBoxes/OS111b_origin S10u8_MySQL_1`
## PS. use this function to get clone ZFS name
cloneZFS() {
  sSZFS=$1
  sSnapshotName=$2
  sTZFS=`echo $sSZFS | sed -e 's|/[^/]*$||'`/$sSnapshotName
  pfexec zfs snapshot $sSZFS@$sSnapshotName || exit 2
  pfexec zfs clone $sSZFS@$sSnapshotName $sTZFS || exit 2
  echo $sTZFS
}

## Ex.
##   checkVersion v1 v2
##   v1>v2,  return 1
##   v1==v2, return 0
##   v1
checkVersion() {
  sVar1=$1
  sVar2=$2
  nF1=0
  nF2=0
  sReturn=0
  declare -i nCount=1
  while true
  do
    n1=`echo $sVar1 | cut -d. -f$nCount` && [ -z $n1 ] && n1=0 && nF1=1
    n2=`echo $sVar2 | cut -d. -f$nCount` && [ -z $n2 ] && n2=0 && nF2=1
    if [ $nF1 -eq 1 ] && [ $nF2 -eq 1 ]
    then
      sReturn=0
    else
      if [ $n1 -gt $n2 ]
      then
        nF1=1
        nF2=1
        sReturn=1
      elif [ $n2 -gt $n1 ]
      then
        nF1=1
        nF2=1
        sReturn=255
      else
        sReturn=0
      fi
    fi
    [ $nF1 -eq 1 ] && [ $nF2 -eq 1 ] && break
    nCount=$nCount+1
  done
  return $sReturn
}

checkVBoxVer() {
  sVBoxMinVer=$1
  sCurrVer=`VBoxManage -v | cut -dr -f1`
  nReturn=0
  checkVersion $sCurrVer $sVBoxMinVer
  nResult=$?
  if [ $nResult -ne 255 ]
  then
    nReturn=0
  else
    nReturn=1
  fi
  echo $nReturn
  return $nReturn
}

########################################################### main

[ $# -lt 4 ] && echo "Error without enough parameters, exit program..." && showUsage && exit 1
sAct=$1
sNMN=$2
sSMN=$3
sIF=$4
declare    sVBoxMinVer=3.1.0
declare -i nStartMachine=$5
declare -i nEndMachine=$6 && [ $nEndMachine -eq 0 ] && nEndMachine=$nStartMachine

sOSType=`getVMOSType $sSMN`
sSHDA=`getVMHDA $sSMN`
sSZFS=`getZFS $sSHDA`
sCloneFS=""
sTHDA=""
sTZFS=""

##sDVD_ISO=/Karajon/ISOs/osol-1002-125-ai-x86.iso

if [ `checkVBoxVer $sVBoxMinVer` -ne 0 ]
then
  cat <<EOF
Current version of VirtualBox is `VBoxManage -v`.
And to run this script,
the version of VirtualBox must great than $sVBoxMinVer.
EOF
  exit 255
fi

case $sAct in
c|create)
  # set -vx
  declare -i nCount=$nStartMachine;
  while true
  do
    [ $nCount -gt $nEndMachine ] && break
    createVM "$sNMN"_"$nCount" $sOSType
    sCloneFS=`cloneZFS $sSZFS "$sSMN"_"$nCount"`
    sTHDADir=`zfs get -H mountpoint $sCloneFS | awk '{print($3)}'`
    sTHDA=$sTHDADir/`basename $sSHDA`
    setUUID $sTHDA

    ## rename vdi filename
    mv $sTHDA $sTHDADir/`basename $sSHDA | cut -d. -f1`_$nCount.`basename $sSHDA | cut -d. -f2-`
    sTHDA=$sTHDADir/`basename $sSHDA | cut -d. -f1`_$nCount.`basename $sSHDA | cut -d. -f2-`

    cloneStorageController "$sNMN"_"$nCount" $sSMN
    modifyVM "$sNMN"_"$nCount" $sTHDA $nCount
    modifyVMNIC "$sNMN"_"$nCount" $sSMN $sIF
    nCount=nCount+1
    sleep 1
  done
  # set +vx
  ;;
d|delete)
  # set -vx
  declare -i nCount=$nStartMachine;
  while true
  do
    [ $nCount -gt $nEndMachine ] && break
    sHDA=`getVMHDA "$sNMN"_"$nCount"`
    echo "deleting $sNMN"_"$nCount" ...
    VBoxManage -q modifyvm "$sNMN"_"$nCount" --hda none || exit 3
    VBoxManage -q unregistervm "$sNMN"_"$nCount" --delete || exit 3
    #VBoxManage -q list hdds ## list hdds info
    VBoxManage -q closemedium disk $sHDA # remove disk medium
    zfs list -t snapshot | grep $sSMN@"$sSMN"_"$nCount" | while read f x
    do
      pfexec zfs destroy -R $f
    done
    nCount=nCount+1
  done
  # set +vx
  ;;
*)
  echo "Error with wrong action($sAct), exit program..."
  showUsage
  exit 1
  ;;
esac





Wish this helps.

regards,
Stanley Huang

Sunday, December 6, 2009

Gnome-commander in OpenSolaris

If you like to use the utility on Windows called "Total Commander", you would like "Gnome-commander" in OpenSolaris.
You can download the Gnome-commander in opensolaris.org repository. The package name called "SUNWgnome-commander".

Wish this helps.

regards,
Stanley Huang

Friday, December 4, 2009

[Level 3] Get process pid which open port!

How to get your process that open port.
The following will help you to check it!

#!/usr/bin/bash
showUsage() {
  cat <<EOF
Usage:
  $0 [port/pid/program](sort column, default is sort by 'port'.)
Ex.
  $0 1/port
  $0 2/pid
  $0 3/program
EOF
}

####################################### main

typeset -i nSortKey
case $1 in
1|port|"")
  sFlag="-n"
  nSortKey=1
  ;;
2|pid)
  sFlag="-n"
  nSortKey=2
  ;;
3|program)
  nSortKey=3
  ;;
*)
  showUsage
  exit 0
  ;;
esac

echo "port\tpid\tcmd"
ps -eo pid,comm | while read pid cmd
do
  pfiles $pid 2> /dev/null | grep port: | awk "{printf(\"%s\t%s\t%s\n\",\$5,\"$pid\",\"$cmd\")}"
done | sort $sFlag -u -k $nSortKey 



Wish this helps.

regards,
Stanley Huang

Thursday, December 3, 2009

[Level 2] Enhance the performance with MySQL checksum table with MyISAM engine.

You can create MyISAM table with the option "checksum=1", to let MyISAM table store the checksum value. Otherwise, checksum table, will need to do a full table scan.
PS. This only support on MyISAM engine.

mysql> create table t (id int) engine=MyISAM checksum=1;
mysql> checksum table t;

Wish this helps.

regards,
Stanley Huang

Tuesday, December 1, 2009

VirtualBox 3.1 released

Sun Microsystems release VirtualBox 3.1 at 11/30. The new version has new features that people looking forward to:

1. Teleportaion (aka live migration): It supports that to let virtual machine to migration between different platform, it enhances the virtual server with HA and flexibility.
2. Snapshots flexibility: The guest can rollback from different revision, and also can have branched snapshots.
3. Network attachement on the fly: The guest can modify the network interace on line.

More information, please refer to:
http://www.virtualbox.org/wiki/Changelog





Wish this helps.

regards,
Stanley Huang