MySQL Maintenance Examples

Here are examples of regular maintenance for mysql

  1. Simple MySQL Backup Script

    #!/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"
  2. Simple Log Rollover Scheme. This may be done with error files, log files, etc.

    cp /path/to/mysql/log/mysqld.err /path/to/mysql/log/mysqld-`date '+%w'`.err ;
    cp /dev/null /path/to/mysql/log/mysqld.err
  3. Sample Unix Cron Entries (empty lines will fail in cron, beware)

    #
    #       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