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
494 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 26

Accepted Solution

by:
wilcoxon earned 450 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 50 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql disables rename 4 93
MySQL Error Code 2 25
How to update the value of duplicated records (except latest one) 2 24
java mysql insert application 14 28
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
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…
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…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

856 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