Difference between revisions of "MySQL"
(New page: = 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 backu...) |
|||
(32 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 | ||
+ | |||
+ | # 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 == | ||
− | + | 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"; | ||
− | # | + | # 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 | # 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" \ | ||
− | + | "${PASSWD}" \ | |
--add-drop-database \ | --add-drop-database \ | ||
--add-drop-table \ | --add-drop-table \ | ||
Line 40: | Line 84: | ||
} | } | ||
− | # Backup database named " | + | # Backup database named "fruity" unless user specified different name on command line |
− | mysql_backup "${1:- | + | mysql_backup "${1:-fruity}" "/var/nst/backup/db"; |
</pre> | </pre> | ||
− | |||
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 57: | Line 100: | ||
mysql_restore() { | mysql_restore() { | ||
# Name of database and directory where backup was saved | # Name of database and directory where backup was saved | ||
− | local PKGDB=" | + | local PKGDB="${1}"; |
− | local PKGSAVEDIR=" | + | local PKGSAVEDIR="${2}"; |
local PKGSAVEFILE="${PKGSAVEDIR}/${PKGDB}.sql.gz"; | local PKGSAVEFILE="${PKGSAVEDIR}/${PKGDB}.sql.gz"; | ||
Line 65: | Line 108: | ||
[ -f "${PKGSAVEFILE}" ] || return 1; | [ -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 | # Restore the database | ||
Line 75: | Line 117: | ||
--host="127.0.0.1" \ | --host="127.0.0.1" \ | ||
--user="root" \ | --user="root" \ | ||
− | + | "${PASSWD}"; | |
} | } | ||
− | # Restore database named " | + | # Restore database named "fruity" unless user specified different name on command line |
− | mysql_restore "${1:- | + | 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:
fruity geoinfo inprotect mysql snort snort_archive test
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.
total 8 -rw-r--r-- 1 root root 5407 Feb 28 07:23 snort.sql.gz