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
491 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Creating and Managing Databases with phpMyAdmin in cPanel.
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now