Need help with a BASH or PERL script where arguments of a command come from a short list.

Hi Team,
  Just need help crafting a short (maybe 2 or lines?) script which I will later use in a crontab to delete mysql tables that are 8 days old.  My starting point would be something like this:

[root@localhost Scripts]# date -d "8 days ago" +"%d-%b"
17-May

Open in new window


I want to parameterize that day and apply it to the first line of my script, which should look like:

root@localhost Scripts]# mysql -u root -pmypassword test -e"SHOW TABLES LIKE '%17_May%';"
+------------------------------------------+
| Tables_in_test (%17_May%)                |
+------------------------------------------+
| FIREWALLS_17_May_2013_10_40_11         | 
| ROUTERS_17_May_2013_01_00_01             | 
| SERVERS_17_May_2013_10_40_01             | 
| OTHERS_17_May_2013_10_40_18              | 
+------------------------------------------+

Open in new window


Finally, as the second line of the script, it would then take in the 4 elements of the output above and put them as parameters of the command like the one below.  It is I guess by design that mysql doesn't accept any wildcard parameters when dropping tables, so they have to be put in one-by-one:

[root@localhost Scripts]# mysql -u root -pmypassword test -e"DROP TABLE FIREWALLS_17_May_2013_10_40_11, ROUTERS_17_May_2013_01_00_01, SERVERS_17_May_2013_10_40_01, OTHERS_17_May_2013_10_40_18;"  

Open in new window


I just need help parameterizing the date, and then somehow converting the list to a set of arguments for the next command.  I'm not an expert on sed, and awk to get rid of the bars and dashes in the output of the show tables command.

Thanks very much.
rleyba828Asked:
Who is Participating?
 
wilcoxonConnect With a Mentor Commented:
This should do what you want...
#!/usr/bin/perl
use strict;
use warnings;
my $dt = `date -d "8 days ago" +"%d-%b"`;
chomp $dt;
open PIPE, "mysql -u root -pmypassword test -e\"SHOW TABLES LIKE '$dt';\" |" or die "could not open mysql pipe: $!";
my @tables;
while (<PIPE>) {
    chomp;
    next unless m{^\|\s+(\w+)};
    my $tbl = $1;
    next if ($tbl =~ m{^Tables_in_});
    push @tables, $tbl;
}
close PIPE;
if (@tables) {
    system('mysql -u root -pmypassword test -e"DROP TABLE ' . join(', ', @tables) . ';"') == 0
        or die $?;
} else {
    warn "no tables to drop\n";
}

Open in new window

0
 
FishMongerConnect With a Mentor Commented:
It appears to me that you're using an odd scheme design.  Instead of having multiple "FIREWALLS" tables (and the like) based on dates, you should have a single "FIREWALLS" table that is partitioned by range based on a date field.  That would make it extremely easy to drop the desired partitions.

mysql range partitioning
0
 
rleyba828Author Commented:
Hi wilcoxon,   excellent scripting.  I just did a few tweaks and got the thing working.  Thanks very much for this.   Big help.

Hi FishMonger, thanks as well.   Unfortunately, the tables I need to drop are system generated and I don't have access to the main DB application, so I am unable to modify any schemas.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.