MySQL: Difference between revisions

From MediaWiki
Jump to navigationJump to search
No edit summary
Line 29: Line 29:


Assuming one saved the above example script to the file: "'''/root/bin/listdb'''" one should be able to list all of the MySQL databases in the following manner:
Assuming one saved the above example script to the file: "'''/root/bin/listdb'''" one should be able to list all of the MySQL databases in the following manner:
{{Run Command|listdb|
[root@taco ~]#| /root/bin/listdb|fruity
geoinfo
inprotect
mysql
snort
snort_archive
test
[root@taco ~]|
}}


Computer screen using CSS (see [[MediaWiki:Common.css]]):
Computer screen using CSS (see [[MediaWiki:Common.css]]):
Line 57: Line 47:
</div>
</div>


<!--
Mixing CSS and template:
{{Screen|listdb|
<div class="userInput"><span class="prompt">[root@taco ~]# </span>/root/bin/listdb</div>
  <pre class="computerOutput">
fruity
geoinfo
inprotect
mysql
snort
snort_archive
test
</pre>
}}
Same as above, but added class styles in-line temporarily to better visualize rendering:
<div class="screen" style="background-color: black; color: #33FF33; line-height: 100%; border-style: solid; border-color: #888888;">
<div class="screenTitle" style="background-color: blue; color: white; padding: 4px;">listdb usage</div>
<div class="userInput" style="font-size: 100%; font-family: monospace; color: yellow; background-color: black; padding: 0px; margin: 0px; border-style: none;"><span class="prompt" style="color: #cccccc;">[root@taco ~]# </span>/root/bin/listdb</div>
<pre class="computerOutput" style="white-space: pre; margin: 0px; padding: 0px; font-size: 100%; line-height: 100%; background-color: black; border-style: none;">
fruity
geoinfo
inprotect
mysql
snort
snort_archive
test
</pre>
</div>
-->


== Backing Up and Restoring A Database ==
== Backing Up and Restoring A Database ==

Revision as of 00:41, 3 March 2007

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

Listing The Databases

The following script fragment demonstrates how one can list all of the databases currently available on a NST probe:

#!/bin/bash

# mysql_list
#
#   Lists all of the MySQL databases which currently exist.

mysql_list() {
  # Try to load MySQL access password from /etc/nst.conf
  [ -z "${NSTCTMYSQLPASSWD}" ] && [ -f /etc/nst.conf ] && . /etc/nst.conf;
  local PASSWD="--password${NSTCTMYSQLPASSWD+=}${NSTCTMYSQLPASSWD}";

  # Dump all databases
  echo "show databases;" | mysql \
      --host="127.0.0.1" \
      --user="root" \
      "${PASSWD}" | grep -v '^Database$';
}

# Dump all databases
mysql_list;

Assuming one saved the above example script to the file: "/root/bin/listdb" one should be able to list all of the MySQL databases in the following manner:

Computer screen using CSS (see MediaWiki:Common.css):

listdb usage
[root@taco ~]# /root/bin/listdb
fruity
geoinfo
inprotect
mysql
snort
snort_archive
test
[root@taco ~]#


Backing Up and Restoring A Database

NOTE: The following code fragments need to be run from the root user account so that the MySQL password can be read from the file: "/etc/nst.conf".

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";

  # Try to load MySQL access password from /etc/nst.conf
  [ -z "${NSTCTMYSQLPASSWD}" ] && [ -f /etc/nst.conf ] && . /etc/nst.conf;
  local PASSWD="--password${NSTCTMYSQLPASSWD+=}${NSTCTMYSQLPASSWD}";

  # 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" \
    "${PASSWD}" \
    --add-drop-database \
    --add-drop-table \
    --databases "${PKGDB}" \
    | gzip -c >| "${PKGSAVEFILE}";
}

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


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="${1}";
  local PKGSAVEDIR="${2}";

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

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

  # Try to load MySQL access password from /etc/nst.conf
  [ -z "${NSTCTMYSQLPASSWD}" ] && [ -f /etc/nst.conf ] && . /etc/nst.conf;
  local PASSWD="--password${NSTCTMYSQLPASSWD+=}${NSTCTMYSQLPASSWD}";

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

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

Assuming saved the backup script to the file: "/root/bin/backupdb" and the restore script shown above to the file: "/root/bin/restoredb", the following command line demonstrates how one could backup and then restore the snort database.

[root@taco ~]# /root/bin/backupdb snort
[root@taco ~]# ls -l /var/nst/backup/db
total 8
-rw-r--r-- 1 root root 5407 Feb 28 07:23 snort.sql.gz
[root@taco ~]# /root/bin/restoredb snort
[root@taco ~]#