?
Solved

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

Posted on 2013-05-25
3
Medium Priority
?
500 Views
Last Modified: 2013-05-27
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.
0
Comment
Question by:rleyba828
3 Comments
 
LVL 27

Accepted Solution

by:
wilcoxon earned 1800 total points
ID: 39196627
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
 
LVL 28

Assisted Solution

by:FishMonger
FishMonger earned 200 total points
ID: 39196712
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
 

Author Comment

by:rleyba828
ID: 39199258
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question