Shell script automation using cron which query's MySQL Tables


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Shell script automation using cron which query's MySQL Tables
# 1  
Old 09-19-2018
Shell script automation using cron which query's MySQL Tables

What I have:

I have a input.sh (script which basically connect to mysql-db and query's multiple tables to write back the output to output1.out file in a directory)

note: I need to pass an integer (unique_id = anything b/w 1- 1000) next to the script everytime I run the script which generates output file by that integer I passed (output1.out, output2.out .... etc....)

I simply run this command now: sh input.sh 88 |mysql -h XXXX -u XXXX -p XXXX ---> this writes back the output(output1.out) file to a location.

What I require:

I want to write a wrapper script and schedule with a cron job --

to run this script daily with multiple unique_id's at once and writes out the output files with respective unique_id numbers ( output1.out, output2.out etc..) and send the resulting output file (output1.out) as an attachment to an email distribution list..

The format of the output file should: more manageable, for example, the email recipients(users) can change the format so it can be read/edited in excel, add a column for action.

o/p file to send in email, for example:

output1.out --- email1.abc.com

output2.out --- email2.xyz.com etc....

please let me know how does this works - let me know in detail steps (kind of new to crontab)

Thanks in Advance
# 2  
Old 09-20-2018
If your code works from the command line it will work from cron, for the most part.

So you should get your script working from the command line before "worrying" about cron.
This User Gave Thanks to Neo For This Post:
# 3  
Old 09-20-2018
okay, I have issues writing the script in the first place (like I said, new to scripting and crontab etc.. )

so far,

I made a wrapper script:
Code:
#!/bin/bash
## command for topdown report genration
for value in {164,174,59,78} ## more business_ids can be added in the future
do
   sh run_script.sh $value 
done

exit 0

this successfully runs and writes the output file in a different location
output164.out
output174.out
etc....


I tried to schedule it every 38 minute, for this I tried in crontab:

Code:
 
38 * * * * /complete/path/run_script.sh 2>&1 | tee /complete/output_files_path/* | mail -s "test_report" abc@xyz.com

note: like I mentioned earlier, the output files are many (4 for now), so I used * in the path...

as of now:
I end up with error

mail - blank
spool/mail/user ----- null, hope it's okay

tee: /complete_output_path/output/: ---- is a directory

I need:
All the files in the output directory to be send as attachments to email distribution lists
example: output1.out ----- email1
output2.out ----- email2


please let me know how to make it happen..
# 4  
Old 09-20-2018
First of all, your crontab entry does not schedule every 38 minutes:

Code:
38 * * * * /your/crontab/script.sh

That crontab entry executes at 38 minutes after the hour, every hour.

There are many examples of how to write a crontab entry in the man pages.

For example on linux:

https://www.unix.com/man-page/linux/5/crontab/


Quote:
EXAMPLE CRON FILE

Code:
# use /bin/bash to run commands, instead of the default /bin/sh
       SHELL=/bin/bash
       # mail any output to `paul', no matter whose crontab this is
       MAILTO=paul
       #
       # run five minutes after midnight, every day
       5 0 * * *       $HOME/bin/daily.job >> $HOME/tmp/out 2>&1
       # run at 2:15pm on the first of every month -- output mailed to paul
       15 14 1 * *     $HOME/bin/monthly
       # run at 10 pm on weekdays, annoy Joe
       0 22 * * 1-5    mail -s "It's 10pm" joe%Joe,%%Where are your kids?%
       23 0-23/2 * * * echo "run 23 minutes after midn, 2am, 4am ..., everyday"
       5 4 * * sun     echo "run at 5 after 4 every sunday"
       # Run on every second Saturday of the month
       0 4 8-14 * *    test $(date +%u) -eq 6 && echo "2nd Saturday"

Maybe you can review the examples and repost your idea for the correct crontab entry for us to review?

I am not the "kind of guy" to post solutions for people, as I prefer to lead people to learn to think and solve problems for themselves.

Quote:
SOAPBOX

If we just "give you the solution" then we are not teaching you to solve problems on your own; we are doing your work for you. We are here to teach and lead people to solutions, and to help them development themselves as unix/linux systems admins, developers, problem solvers and coders, not to do your work.

/SOAPBOX
Your reply describes a crontab entry that is different than your crontab entry.
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Using bash script : How to Import data from a dsv file into multiple tables in mysql

HI I have a dsv file that looks like: <<BOF>> record_number|id_number|first name|last name|msisdn|network|points|card number|gender 312|9101011234011|Test Junior|Smith|071 123 4321|MTN|73|1241551413214444|M 313|9012023213011|Bob|Smith|27743334321|Vodacom|3|1231233232323244|M... (4 Replies)
Discussion started by: tera
4 Replies

2. Shell Programming and Scripting

Shell Script to execute Oracle query taking input from a file to form query

Hi, I need to query Oracle database for 100 users. I have these 100 users in a file. I need a shell script which would read this User file (one user at a time) & query database. For instance: USER CITY --------- ---------- A CITY_A B CITY_B C ... (2 Replies)
Discussion started by: DevendraG
2 Replies

3. Web Development

mysql query for multiple columns from multiple tables in a DB

Say I have two tables like below.. status HId sName dName StartTime EndTime 1 E E 9:10 10:10 2 E F 9:15 10:15 3 G H 9:17 10:00 logic Id devName capacity free Line 1 E 123 34 1 2 E 345 ... (3 Replies)
Discussion started by: ilan
3 Replies

4. Shell Programming and Scripting

Using a shell script variable in a mysql query using 'LIKE'

Hello. I am writing a simple script that reads a text file and removes records from a mysql database. The items in the text file are of the format: firstname.middle.lastXXX, where XXX is a 3 digit number. The table has an email field that will match the firstname.middle.last. So, I thought I... (1 Reply)
Discussion started by: bricoleur
1 Replies

5. Shell Programming and Scripting

Help with Truststore automation shell script

Please close this thread. I have raise this question in appropriate thread. Thanks (0 Replies)
Discussion started by: KuldeepSinghTCS
0 Replies

6. Shell Programming and Scripting

Query Oracle tables and return values to shell script that calls the query

Hi, I have a requirement as below which needs to be done viz UNIX shell script (1) I have to connect to an Oracle database (2) Exexute "SELECT field_status from table 1" query on one of the tables. (3) Based on the result that I get from point (2), I have to update another table in the... (6 Replies)
Discussion started by: balaeswari
6 Replies

7. Shell Programming and Scripting

Help with Shell Script automation

can someone look into this one please... I am struck at this point. I do not know what logic to be followed here. I can go ahead with my work only, if this step is done. Please Help. I have a process X in a shell script. Once the process X is done, it generates a log file. Process X is basically... (1 Reply)
Discussion started by: ss3944
1 Replies

8. Shell Programming and Scripting

Passing a variable from shell script to mysql query?

I heard this was possible but from my research I haven't been able to figure it out yet. Seems it should be simple enough. Basically from a high level view I'm trying to accomplish... . $X='grep foo blah.log' then 'mysql command SELECT foo FROM bar WHERE ' . $X or something like that. ... (2 Replies)
Discussion started by: kero
2 Replies

9. Shell Programming and Scripting

Tables to query to find users for database from shell script

I am coding shell script. I need to connect to different databases like DB2, Oracle and Sybase. I would then need to query tables where it has all the groups, users for that database. I would also need who has what kind of permissions. EG: I know for DB2 some TABAUTH table needs to be... (0 Replies)
Discussion started by: pinnacle
0 Replies
Login or Register to Ask a Question