Change the Group-Office database collation and character set

Recently we converted the Group-Office database to the collation "utf8mb4_unicode_ci". We did this to support 4 byte unicode characters. Some customers may want to use another collation because of sorting in the database. For example Danish users want to use "utf8mb4_danish_ci". You can choose any collation as long as it starts with "utf8mb4".
You can use this bash script to convert all tables in the database.
Make a backup first!

#!/bin/bash
DB="go61mb4"
COLLATE="utf8mb4_unicode_ci"
USER="root"
PASSWORD="SECRET"

(
  echo 'set foreign_key_checks=0;ALTER DATABASE `'"$DB"'` \
  CHARACTER SET utf8mb4 COLLATE '"$COLLATE"';'
  mysql -u "$USER" -p"$PASSWORD"  "$DB" -e "SHOW TABLES" --batch --skip-column-names \
  | xargs -I{} echo 'ALTER TABLE `'{}'` \
  CONVERT TO CHARACTER SET utf8mb4 COLLATE '"$COLLATE"';'
) \
| mysql -u "$USER" -p"$PASSWORD" "$DB"

Change the Group-Office database collation and character set

Recently we converted the Group-Office database to the collation "utf8mb4_unicode_ci". We did this to support 4 byte unicode characters. Some customers may want to use another collation because of sorting in the database. For example Danish users want to use "utf8mb4_danish_ci". You can choose any collation as long as it starts with "utf8mb4".
You can use this bash script to convert all tables in the database.
Make a backup first!

#!/bin/bash
DB="go61mb4"
COLLATE="utf8mb4_unicode_ci"
USER="admin"
PASSWORD="mks14785"

(
  echo 'set foreign_key_checks=0;ALTER DATABASE `'"$DB"'` \
  CHARACTER SET utf8mb4 COLLATE '"$COLLATE"';'
  mysql -u "$USER" -p"$PASSWORD"  "$DB" -e "SHOW TABLES" --batch --skip-column-names \
  | xargs -I{} echo 'ALTER TABLE `'{}'` \
  CONVERT TO CHARACTER SET utf8mb4 COLLATE '"$COLLATE"';'
) \
| mysql -u "$USER" -p"$PASSWORD" "$DB"

Simple monitoring

I was searhing for a simple monitoring tool but most tools out there are very complex. We use 3rd party virtual private servers now so hardware monitoring is not our business. We're interested in monitoring two things:

1. Free disk space
2. CPU load

So I use two simple scripts that send out an e-mail alert when the diskspace is low or the CPU load it too high.

You can create the two scripts below and edit the MAILTO and TRIGGER variables to suit your needs /usr/local/bin/monitor-load.sh:

#!/bin/bash
TRIGGER=2.00
MAILTO=mail@somedomain.com


host=`hostname -f`
load=`cat /proc/loadavg | awk '{print $2}'`
response=`echo | awk -v T=$TRIGGER -v L=$load 'BEGIN{if ( L > T){ print "greater"}}'`
if [[ $response = "greater" ]]
then
        #capture top command output for the mail body
        body=`top -n 1 -b`

        echo "$body\n."|mail -s "High load on $host - [ $load ]" $MAILTO
fi

/usr/local/bin/monitor-disk-usage.sh:

#!/bin/bash
MAILTO=mail@somedomain.com
TRIGGER=80

HOST=`hostname -f`
CURRENT=$(df / | grep / | awk '{ print $5}' | sed 's/%//g')

if [ "$CURRENT" -gt "$TRIGGER" ] ; then

    mail -s "Disk Space Alert for $HOST" $MAILTO << EOF
The root partition of '$HOST' remaining free space is critically low. Used: $CURRENT%
EOF
fi

Make these two scripts executable:

chmod +x /usr/local/bin/monitor-load.sh 
chmod +x /usr/local/bin/monitor-disk-usage.sh

Now schedule these script in system cron jobs:
/etc/cron.d/monitor:

# Check server load and diskspace
* * * * * root /usr/local/bin/monitor-load.sh
0 7,16 * * * root /usr/local/bin/monitor-disk-usage.sh

This will check the server load every minute and disk space at 7am and 4pm. Finally it's wise to test if mails sent from console arrive. Change the e-mail address in this command and run it:

echo "."|mail -s "Test mail from console" mail@somedomain.com