[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
?
499 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
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 Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

656 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