Difference between revisions of "MySQL"

From NST Wiki
Jump to navigationJump to search
(→‎Backing Up A Database)
 
(31 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
= MySQL =
+
This page contains some notes on using the MySQL database server on a '''NST''' probe.
 +
 
 +
<noinclude>[[Category:Database]]</noinclude>
 +
== Listing The Databases ==
 +
 
 +
The following script fragment demonstrates how one can list all of the databases currently available on a '''NST''' probe:
 +
 
 +
<pre class="programListing">
 +
#!/bin/bash
  
−
This page contains some notes on using the MySQL database server on a '''NST''' probe.
+
# 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;
 +
</pre>
 +
 
 +
 
 +
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:
 +
 
 +
<div class="screen">
 +
  <div class="screenTitle">''Script:'' "listdb" Usage</div>
 +
  <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><div class="userInput"><span class="prompt">[root@taco ~]# </span></div>
 +
</div>
 +
 
 +
== Backing Up and Restoring A Database ==
  
−
== Backing Up 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:
 
The following script fragment demonstrates how one can backup a single database kept at the server:
  
−
<pre>
+
<pre class="programListing">
 
#!/bin/bash
 
#!/bin/bash
  
Line 21: Line 66:
 
   local PKGSAVEFILE="${PKGSAVEDIR}/${PKGDB}.sql.gz";
 
   local PKGSAVEFILE="${PKGSAVEDIR}/${PKGDB}.sql.gz";
  
−
   # Load MySQL access password
+
   # Try to load MySQL access password from /etc/nst.conf
−
   if [ "${MYSQLCTPASSWD}" == "" ]; then
+
   [ -z "${NSTCTMYSQLPASSWD}" ] && [ -f /etc/nst.conf ] && . /etc/nst.conf;
−
    . /etc/nst.conf || return 1;
+
   local PASSWD="--password${NSTCTMYSQLPASSWD+=}${NSTCTMYSQLPASSWD}";
−
   fi
 
  
 
   # Create save directory if it doesn't exist yet
 
   # Create save directory if it doesn't exist yet
Line 33: Line 77:
 
     --host="127.0.0.1" \
 
     --host="127.0.0.1" \
 
     --user="root" \
 
     --user="root" \
−
     --password="${NSTCTMYSQLPASSWD}" \
+
     "${PASSWD}" \
 
     --add-drop-database \
 
     --add-drop-database \
 
     --add-drop-table \
 
     --add-drop-table \
Line 40: Line 84:
 
}
 
}
  
−
# Backup database named "wikidb" unless user specified different name on command line
+
# Backup database named "fruity" unless user specified different name on command line
−
mysql_backup "${1:-wikidb}" "/var/nst/backup/db";
+
mysql_backup "${1:-fruity}" "/var/nst/backup/db";
 
</pre>
 
</pre>
  
−
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):
 
The following script fragment demonstrates how to restore the database (WARNING: THIS REPLACES ANY EXISTING DATABASE HAVING THE SAME NAME):
  
−
<pre>
+
<pre class="programListing">
 
#!/bin/bash
 
#!/bin/bash
  
Line 65: Line 108:
 
   [ -f "${PKGSAVEFILE}" ] || return 1;
 
   [ -f "${PKGSAVEFILE}" ] || return 1;
  
−
   # Load MySQL access password
+
   # Try to load MySQL access password from /etc/nst.conf
−
   if [ "${MYSQLCTPASSWD}" == "" ]; then
+
   [ -z "${NSTCTMYSQLPASSWD}" ] && [ -f /etc/nst.conf ] && . /etc/nst.conf;
−
    . /etc/nst.conf || return 1;
+
   local PASSWD="--password${NSTCTMYSQLPASSWD+=}${NSTCTMYSQLPASSWD}";
−
   fi
 
  
 
   # Restore the database
 
   # Restore the database
Line 75: Line 117:
 
       --host="127.0.0.1" \
 
       --host="127.0.0.1" \
 
       --user="root" \
 
       --user="root" \
−
       --password="${NSTCTMYSQLPASSWD}";
+
       "${PASSWD}";
 
}
 
}
  
−
# Restore database named "wikidb" unless user specified different name on command line
+
# Restore database named "fruity" unless user specified different name on command line
−
mysql_restore "${1:-wikidb}" "/var/nst/backup/db";
+
mysql_restore "${1:-fruity}" "/var/nst/backup/db";
 +
</pre>
 +
 
 +
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.
 +
 
 +
<div class="screen">
 +
  <div class="screenTitle">''Script:'' "backupdb" & "restoredb" Usage</div>
 +
  <div class="userInput"><span class="prompt">[root@taco ~]# </span>/root/bin/backupdb snort</div>
 +
  <div class="userInput"><span class="prompt">[root@taco ~]# </span></div>
 +
  <div class="userInput"><span class="prompt">[root@taco ~]# </span>ls -l /var/nst/backup/db</div>
 +
<pre class="computerOutput">
 +
total 8
 +
-rw-r--r-- 1 root root 5407 Feb 28 07:23 snort.sql.gz
 
</pre>
 
</pre>
 +
  <div class="userInput"><span class="prompt">[root@taco ~]# </span>ls -l /var/nst/backup/db</div>
 +
  <div class="userInput"><span class="prompt">[root@taco ~]# </span></div>
 +
</div>

Latest revision as of 07:17, 4 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:

Script: "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.

Script: "backupdb" & "restoredb" Usage
[root@taco ~]# /root/bin/backupdb snort
[root@taco ~]#
[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 ~]# ls -l /var/nst/backup/db
[root@taco ~]#
Retrieved from "https://wiki.networksecuritytoolkit.org/nstwiki/index.php?title=MySQL&oldid=183"