MySQL

MySQL

This page contains some notes on using the MySQL database server on a NST probe.

Backing Up A Database

The following script fragment demonstrates how one can backup a single database kept at the server:

#!/bin/bash

# mysql_backup DBNAME BACKUPDIR
#
#   Function to backup MySQL database to specified backup directory.

mysql_backup() {
  # Name of database and directory to save backup under.
  local PKGDB="${1}";
  local PKGSAVEDIR="${2}";

  local PKGSAVEFILE="${PKGSAVEDIR}/${PKGDB}.sql.gz";

  # Load MySQL access password
  if [ "${MYSQLCTPASSWD}" == "" ]; then
    . /etc/nst.conf || return 1;
  fi

  # Create save directory if it doesn't exist yet
  [ -d "${PKGSAVEDIR}" ] || mkdir -p "${PKGSAVEDIR}" || return 1;

  # Dump the database
  mysqldump \
    --host="127.0.0.1" \
    --user="root" \
    --password="${NSTCTMYSQLPASSWD}" \
    --add-drop-database \
    --add-drop-table \
    --databases "${PKGDB}" \
    | gzip -c >| "${PKGSAVEFILE}";
}

# Backup database named "wikidb" unless user specified different name on command line
mysql_backup "${1:-wikidb}" "/var/nst/backup/db";

NOTE: The above script needs to be run as root so that it can pull the MySQL password from the file: "/etc/nst.conf".

The following script fragment demonstrates how to restore the database (WARNING: THIS REPLACES ANY EXISTING DATABASE HAVING THE SAME NAME):

#!/bin/bash

# mysql_restore DBNAME BACKUPDIR
#
#   Restores a MySQL database that was previously saved using the "mysql_backup" function.

mysql_restore() {
  # Name of database and directory where backup was saved
  local PKGDB="wikidb";
  local PKGSAVEDIR="/var/nst/backup/db";

  local PKGSAVEFILE="${PKGSAVEDIR}/${PKGDB}.sql.gz";

  # Verify backup exists
  [ -f "${PKGSAVEFILE}" ] || return 1;

  # Load MySQL access password
  if [ "${MYSQLCTPASSWD}" == "" ]; then
    . /etc/nst.conf || return 1;
  fi

  # Restore the database
  gzip -dc < "${PKGSAVEFILE}" | \
    mysql \
      --host="127.0.0.1" \
      --user="root" \
      --password="${NSTCTMYSQLPASSWD}";
}

# Restore database named "wikidb" unless user specified different name on command line
mysql_restore "${1:-wikidb}" "/var/nst/backup/db";