Here are examples of regular maintenance for MySQL

Table 1. Simple MySQL Backup Script

Field

Type

Description

ID

int4

Unique ID for a metric that can be collected for a resource. Points to actually measurements in EAM_MEASUREMENT_DATA_* tables.

VERSION_COL

int8

Indicates version of the row, increments upon each change to the row.

INSTANCE_ID

int4

The resource type the measurement is for. Uniquely identifies a resource type of a given inventory level - platform, server, service. For example, the ID 10001 uniquely identifies the platform type "MacOSX".

TEMPLATE_ID

int4

ID of a template that points to the EAM_MEASUREMENT_TEMPL table.

MTIME

int8

Time modified.

ENABLED

boolean

Is this metric enabled?

COLL_INTERVAL

int8

How often this metric is collected.

DSN

varchar(2048)

A string which describes the measurement from the plugin-xml text.

RESOURCE_ID

int4

Uniquely identifies the resource for which the metric is associated, unique across platforms, servers, services. Points to the EAM_ RESOURCE table.

#!/bin/sh

START=`date '+%A %Y/%m/%d %H:%M:%S'`
DAY=`date +%A`
MYSQLADMIN="/usr/bin/mysqladmin"
MYSQLDUMP="/usr/bin/mysqldump"
USER="root"
PASSWORD="mysql"
DBNAME="hqdb"
DEST="/home/mysql/dumps/$DBNAME-$DAY.sql.gz"
flushCmd="$MYSQLADMIN -u $USER -p$PASSWORD flush-logs"
dumpCmd="$MYSQLDUMP -u $USER -p$PASSWORD --quick --single-transaction $DBNAME"
gzip="gzip"
echo "Starting backup: $START"
echo "$flushCmd && $dumpCmd | $gzip > $DEST"
$flushCmd && $dumpCmd | $gzip > $DEST
END=`date '+%A %Y/%m/%d %H:%M:%S'`
echo "Backup completed: $END"

Simple Log Rollover Scheme

This can be used with error files, log files, and so on.

cp /path/to/mysql/log/mysqld.err /path/to/mysql/log/mysqld-`date '+%w'`.err ;
cp /dev/null /path/to/mysql/log/mysqld.err

Sample Unix Cron Entries

Be aware that empty lines in the code with fail in cron.

#
#       Field 1: (0-59) minute
#       Field 2: (0-23) hour
#       Field 3: (1-31) day  of  the month
#       Field 4: (1-12) month of the year
#       Field 5: (0-6)  day of the week - 1=Monday
# ----------------------------------------------------------------------------
#
0 2 * * * backup.sh
0 1 * * * cp /path/to/mysql/log/mysqld.err /path/to/mysql/log/mysqld-`date '+%w'`.err ;
cp /dev/null /path/to/mysql/log/mysqld.err