-----Original Message----- From: Jacob, Raymond A Jr Sent: Monday, August 02, 2004 21:17 To: [EMAIL PROTECTED] Subject: archive db daily- Rough Draft
I am new to Databases and would appreciate a second set of eyes looking over my sql script. The script takes the existing snort database and copies it to a database named snort-2004-08-02. Then copies over a day's worth of information to the database snort-2004-08-02. Once the database has been copied over, mysqlhotcopy is used to copy the database to another directory. The database is then archived and compressed to a file with a .tgz extension. After a week the database is removed. I assume that I can find the age of the snort-2004-08-02 database from mysql without checking a table in the snort-2004-08-02 database When I determine that the database is over 7-days old I will drop the database? thank you, Raymond -----------copySnort.sql----------- #copySnort.sql copy snortdb database to snort-<yyyy>-<mm>-<dd> CREATE DATABASE snort-2004-08-02; USE snort-2004-08-02; # Not sure if this is even necessary Database may use my default information. GRANT INSERT,SELECT on snort-2004-08-02.* to [EMAIL PROTECTED]; CREATE TABLE acid_event ( sid INT UNSIGNED NOT NULL, cid INT UNSIGNED NOT NULL, signature INT UNSIGNED NOT NULL, sig_name VARCHAR(255), sig_class_id INT UNSIGNED, sig_priority INT UNSIGNED, timestamp DATETIME NOT NULL, ip_src INT UNSIGNED, ip_dst INT UNSIGNED, ip_proto INT, layer4_sport INT UNSIGNED, layer4_dport INT UNSIGNED, PRIMARY KEY (sid,cid), INDEX (signature), INDEX (sig_name), INDEX (sig_class_id), INDEX (sig_priority), INDEX (timestamp), INDEX (ip_src), INDEX (ip_dst), INDEX (ip_proto), INDEX (layer4_sport), INDEX (layer4_dport) ); INSERT snort-2004-08-02.acid_event.* SELECT snort.acid_event.* FROM snort.acid_event, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01 AND (snort.event.cid = snort.acid_event.cid); CREATE TABLE acid_ag ( ag_id INT UNSIGNED NOT NULL AUTO_INCREMENT, ag_name VARCHAR(40), ag_desc TEXT, ag_ctime DATETIME, ag_ltime DATETIME, PRIMARY KEY (ag_id), INDEX (ag_id)); INSERT snort-2004-08-02.acid_ag.* SELECT snort.acid_ag.*, snort.acid_ag_alert.ag_id, snort.acid_ag_alert.ag_cid FROM snort.acid_ag, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and '2004-08-01' AND (snort.event.cid = snort.acid_ag.cid); CREATE TABLE acid_ag_alert( ag_id INT UNSIGNED NOT NULL, ag_sid INT UNSIGNED NOT NULL, ag_cid INT UNSIGNED NOT NULL, PRIMARY KEY (ag_id, ag_sid, ag_cid), INDEX (ag_id), INDEX (ag_sid, ag_cid)); INSERT snort-2004-08-02.acid_ag_alert.* SELECT snort.acid_ag_alert.* FROM snort.acid_event, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01) AND (snort.event.cid = snort.acid_ag.cid); CREATE TABLE acid_ip_cache( ipc_ip INT UNSIGNED NOT NULL, ipc_fqdn VARCHAR(50), ipc_dns_timestamp DATETIME, ipc_whois TEXT, ipc_whois_timestamp DATETIME, PRIMARY KEY (ipc_ip), INDEX (ipc_ip) ); INSERT snort-2004-08-02.acid_ip_cache.* SELECT snort.acid_ip_cache.* FROM snort.acid_event, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01) AND (snort.event.timestamp = snort.acid_ip_cache.ip_dns_timestamp); CREATE TABLE protocols( protocol INT, name VARCHAR(50), description VARCHAR(50) ); INSERT snort-2004-08-02.protocols.* SELECT snort.protocols.* FROM snort.protocols.*; CREATE TABLE services( port INT, protocol INT, name VARCHAR(50), description VARCHAR(255) ); INSERT snort-2004-08-02.services.* SELECT snort.services.* FROM snort.services.*; CREATE TABLE flags( number INT, RES1 INT, RES2 INT, URG INT, ACK INT, PSH INT, RST INT, SYN INT, FIN INT, valid INT, description VARCHAR(255) ); INSERT snort-2004-08-02.flags.* SELECT snort.flags.* FROM snort.flags.*; CREATE TABLE schema ( vseq INT UNSIGNED NOT NULL, ctime DATETIME NOT NULL, PRIMARY KEY (vseq)); INSERT INTO schema (vseq, ctime) VALUES ('106', now()); CREATE TABLE event ( sid INT UNSIGNED NOT NULL, cid INT UNSIGNED NOT NULL, signature INT UNSIGNED NOT NULL, timestamp DATETIME NOT NULL, PRIMARY KEY (sid,cid), INDEX sig (signature), INDEX time (timestamp)); INSERT snort-2004-08-02.event.* SELECT snort.event.* FROM snort.acid_event, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01); CREATE TABLE signature ( sig_id INT UNSIGNED NOT NULL AUTO_INCREMENT, sig_name VARCHAR(255) NOT NULL, sig_class_id INT UNSIGNED NOT NULL, sig_priority INT UNSIGNED, sig_rev INT UNSIGNED, sig_sid INT UNSIGNED, PRIMARY KEY (sig_id), INDEX sign_idx (sig_name(20)), INDEX sig_class_id_idx (sig_class_id)); INSERT snort-2004-08-02.signature.* SELECT snort.signature.* FROM snort.signature; CREATE TABLE sig_reference (sig_id INT UNSIGNED NOT NULL, ref_seq INT UNSIGNED NOT NULL, ref_id INT UNSIGNED NOT NULL, PRIMARY KEY(sig_id, ref_seq)); INSERT snort-2004-08-02.sig_ref.* SELECT snort.sig_ref.* FROM snort.sig_ref; CREATE TABLE reference ( ref_id INT UNSIGNED NOT NULL AUTO_INCREMENT, ref_system_id INT UNSIGNED NOT NULL, ref_tag TEXT NOT NULL, PRIMARY KEY (ref_id)); CREATE TABLE reference_system ( ref_system_id INT UNSIGNED NOT NULL AUTO_INCREMENT, ref_system_name VARCHAR(20), PRIMARY KEY (ref_system_id)); INSERT snort-2004-08-02.reference_system.* SELECT snort.reference_system.* FROM snort.reference_system; CREATE TABLE sig_class ( sig_class_id INT UNSIGNED NOT NULL AUTO_INCREMENT, sig_class_name VARCHAR(60) NOT NULL, PRIMARY KEY (sig_class_id), INDEX (sig_class_id), INDEX (sig_class_name)); INSERT snort-2004-08-02.sig_class.* SELECT snort.sig_class.* FROM snort.sig_class; # store info about the sensor supplying data CREATE TABLE sensor ( sid INT UNSIGNED NOT NULL AUTO_INCREMENT, hostname TEXT, interface TEXT, filter TEXT, detail TINYINT, encoding TINYINT, last_cid INT UNSIGNED NOT NULL, PRIMARY KEY (sid)); INSERT snort-2004-08-02.sensor.* SELECT snort.sensor.* FROM snort.sensor; # All of the fields of an ip header CREATE TABLE iphdr ( sid INT UNSIGNED NOT NULL, cid INT UNSIGNED NOT NULL, ip_src INT UNSIGNED NOT NULL, ip_dst INT UNSIGNED NOT NULL, ip_ver TINYINT UNSIGNED, ip_hlen TINYINT UNSIGNED, ip_tos TINYINT UNSIGNED, ip_len SMALLINT UNSIGNED, ip_id SMALLINT UNSIGNED, ip_flags TINYINT UNSIGNED, ip_off SMALLINT UNSIGNED, ip_ttl TINYINT UNSIGNED, ip_proto TINYINT UNSIGNED NOT NULL, ip_csum SMALLINT UNSIGNED, PRIMARY KEY (sid,cid), INDEX ip_src (ip_src), INDEX ip_dst (ip_dst)); INSERT snort-2004-08-02.iphdr.* SELECT snort.iphdr.* FROM snort.iphdr, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01) AND ( snort.event.cid = snort.iphdr.cid) ; # All of the fields of a tcp header CREATE TABLE tcphdr( sid INT UNSIGNED NOT NULL, cid INT UNSIGNED NOT NULL, tcp_sport SMALLINT UNSIGNED NOT NULL, tcp_dport SMALLINT UNSIGNED NOT NULL, tcp_seq INT UNSIGNED, tcp_ack INT UNSIGNED, tcp_off TINYINT UNSIGNED, tcp_res TINYINT UNSIGNED, tcp_flags TINYINT UNSIGNED NOT NULL, tcp_win SMALLINT UNSIGNED, tcp_csum SMALLINT UNSIGNED, tcp_urp SMALLINT UNSIGNED, PRIMARY KEY (sid,cid), INDEX tcp_sport (tcp_sport), INDEX tcp_dport (tcp_dport), INDEX tcp_flags (tcp_flags)); INSERT snort-2004-08-02.tcphdr.* SELECT snort.tcphdr.* FROM snort.tcphdr, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01) AND ( snort.event.cid = snort.tcphdr.cid) ; # All of the fields of a udp header CREATE TABLE udphdr( sid INT UNSIGNED NOT NULL, cid INT UNSIGNED NOT NULL, udp_sport SMALLINT UNSIGNED NOT NULL, udp_dport SMALLINT UNSIGNED NOT NULL, udp_len SMALLINT UNSIGNED, udp_csum SMALLINT UNSIGNED, PRIMARY KEY (sid,cid), INDEX udp_sport (udp_sport), INDEX udp_dport (udp_dport)); INSERT snort-2004-08-02.updhdr.* SELECT snort.udphdr.* FROM snort.udphdr, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01) AND ( snort.event.cid = snort.udphdr.cid) ; # All of the fields of an icmp header CREATE TABLE icmphdr( sid INT UNSIGNED NOT NULL, cid INT UNSIGNED NOT NULL, icmp_type TINYINT UNSIGNED NOT NULL, icmp_code TINYINT UNSIGNED NOT NULL, icmp_csum SMALLINT UNSIGNED, icmp_id SMALLINT UNSIGNED, icmp_seq SMALLINT UNSIGNED, PRIMARY KEY (sid,cid), INDEX icmp_type (icmp_type)); INSERT snort-2004-08-02.icmphdr.* SELECT snort.icmphdr.* FROM snort.icmphdr, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01) AND ( snort.event.cid = snort.icmphdr.cid) ; # Protocol options CREATE TABLE opt ( sid INT UNSIGNED NOT NULL, cid INT UNSIGNED NOT NULL, optid INT UNSIGNED NOT NULL, opt_proto TINYINT UNSIGNED NOT NULL, opt_code TINYINT UNSIGNED NOT NULL, opt_len SMALLINT, opt_data TEXT, PRIMARY KEY (sid,cid,optid)); INSERT snort-2004-08-02.opt.* SELECT snort.opt.* FROM snort.opt, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01) AND ( snort.event.cid = snort.opt.cid) ; # Packet payload CREATE TABLE data ( sid INT UNSIGNED NOT NULL, cid INT UNSIGNED NOT NULL, data_payload TEXT, PRIMARY KEY (sid,cid)); INSERT snort-2004-08-02.data.* SELECT snort.data.* FROM snort.data, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01) AND ( snort.event.cid = snort.data.cid) ; # encoding is a lookup table for storing encoding types CREATE TABLE encoding(encoding_type TINYINT UNSIGNED NOT NULL, encoding_text TEXT NOT NULL, PRIMARY KEY (encoding_type)); INSERT INTO encoding (encoding_type, encoding_text) VALUES (0, 'hex'); INSERT INTO encoding (encoding_type, encoding_text) VALUES (1, 'base64'); INSERT INTO encoding (encoding_type, encoding_text) VALUES (2, 'ascii'); INSERT snort-2004-08-02.encoding.* SELECT snort.encoding.* FROM snort.encoding; # detail is a lookup table for storing different detail levels CREATE TABLE detail (detail_type TINYINT UNSIGNED NOT NULL, detail_text TEXT NOT NULL, PRIMARY KEY (detail_type)); INSERT INTO detail (detail_type, detail_text) VALUES (0, 'fast'); INSERT INTO detail (detail_type, detail_text) VALUES (1, 'full'); INSERT snort-2004-08-02.detail.* SELECT snort.detail.* FROM snort.detail; #-----end copySnort.sql-------- #-----PurgeSnortDaily.sql---------- use snort DELETE snort.acid_event.* SELECT snort.acid_event.* FROM snort.acid_event, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01 AND (snort.event.cid = snort.acid_event.cid); DELETE snort.acid_ag.* SELECT snort.acid_ag.*, snort.acid_ag_alert.ag_id, snort.acid_ag_alert.ag_cid FROM snort.acid_ag, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and '2004-08-01' AND (snort.event.cid = snort.acid_ag.cid); DELETE snort.acid_ag_alert.* SELECT snort.acid_ag_alert.* FROM snort.acid_event, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01) AND (snort.event.cid = snort.acid_ag.cid); DELETE snort.acid_ip_cache.* SELECT snort.acid_ip_cache.* FROM snort.acid_event, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01) AND (snort.event.timestamp = snort.acid_ip_cache.ip_dns_timestamp); DELETE snort.event.* SELECT snort.event.* FROM snort.acid_event, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01); DELETE snort.iphdr.* SELECT snort.iphdr.* FROM snort.iphdr, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01) AND ( snort.event.cid = snort.iphdr.cid) ; DELETE snort.tcphdr.* SELECT snort.tcphdr.* FROM snort.tcphdr, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01) AND ( snort.event.cid = snort.tcphdr.cid) ; DELETE snort.updhdr.* SELECT snort.udphdr.* FROM snort.udphdr, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01) AND ( snort.event.cid = snort.udphdr.cid) ; DELETE snort.icmphdr.* SELECT snort.icmphdr.* FROM snort.icmphdr, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01) AND ( snort.event.cid = snort.icmphdr.cid) ; DELETE snort.opt.* SELECT snort.opt.* FROM snort.opt, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01) AND ( snort.event.cid = snort.opt.cid) ; DELETE snort.data.* SELECT snort.data.* FROM snort.data, snort.event WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and 2004-08-01) AND ( snort.event.cid = snort.data.cid) ; #----end-PurgeSnortDaily.sql---------- #-------dropSnortDaily.sql--------- DROP DATABASE snort-2004-08-02 #-------end-dropSnortDaily.sql--------- Script: mysql -u eddie -p murphy < copySnort.sql mysqlhotcopy snort-2004-08-02 /var/snort/db-backups/ cd /var/snort/db-backups/ tar cvf snort-2004-08-02 | gzip -9 > /var/log/db/snort-2004-08-02.tgz mysql -u eddie -p murphy < PurgeSnortDaily.sql #rotate database( delete database that is 7days old) find . -name 'snort-*' -type d -ctime 7 exec rm -r {}\; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]