Difference between revisions of "MySQL"
From NST Wiki
Jump to navigationJump to search (→Backing Up A Database) |
(→Backing Up A Database) |
||
Line 4: | Line 4: | ||
== Backing Up 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: | ||
Line 40: | Line 42: | ||
} | } | ||
− | # 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): | ||
Line 78: | Line 79: | ||
} | } | ||
− | # 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. | ||
+ | |||
+ | <pre> | ||
+ | [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 ~]# | ||
</pre> | </pre> |
Revision as of 08:24, 28 February 2007
MySQL
This page contains some notes on using the MySQL database server on a NST probe.
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:
#!/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 "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; # 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 "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 ~]#