Link to home
Start Free TrialLog in
Avatar of rleyba828
rleyba828Flag for Australia

asked on

Scripting a mysqlcommand to create/delete tables based on date

Hi Team,

  I have mysql running in a docker container in my centos 6.6. server.  With my docker container  called "mysql",  I create this useful script that copies  my "PATCH_RECORD" table in my niis database daily and it works fine.

docker exec mysql mysql -u root -pmypassword niis -e  "CREATE TABLE patching_`date  +%d_%h_%Y` SELECT * FROM PATCH_RECORD; "

Open in new window


I then have a cron job that runs the script daily so I have daily backups of my PATCH_RECORD table.

Now I need to drop the backed up tables older than 5 days by running another cron job so i don't keep tables more than 5 days old.  I am trying to get the exact syntax for this but can't seem to catch it.  Something like this:

docker exec mysql mysql -u root -pmypassword niis -e  "DROP TABLE patching_`date +%d_%h_%Y -5d`; 

Open in new window


The bash interpreter is interpreting -5d as text so it is resulting in a syntax error.  I tried various combinations of -v, or -d   or putting "-5d" with no luck.

Any help with syntax will be appreciated.
Avatar of skullnobrains
skullnobrains

docker exec mysql mysql -u root -pmypassword niis -e  'DROP\ TABLE\ 'patching_`date +%d_%h_%Y -5d`;

the date command is in this case executed outside of the container. i suppose you don't care
Avatar of rleyba828

ASKER

Thanks skullnobrains:

Yes, the date command is executed outside the container   and I am sure we are on the right track with that because the example I gave above to create the tables was working fine and the date variable was being interpreted correctly.


This command:

docker exec mysql mysql -u root -pmypassword niis -e  'DROP\ TABLE\ 'patching_`date +%d_%h_%Y -5d`; 

Open in new window


causes the interpreter to output:
date: invalid option -- '5'

Open in new window


I think it is the combination of the ' and ` and possibly  "   that causes the issue.

Can you suggest how I might put maybe the whole command in some bash variable   and then execute that?  I can't pin down the syntax either:

docker exec mysql mysql -u root -pmypassword niis -e $drop  

where $drop="DROP TABLE patching_`date +%d_%h_%Y -5d`;

Thanks again.
After all the trial and error,   this seemed to have worked:

docker exec mysql mysql -u root -pmypassword niis -e  "DROP TABLE patching_`date  +%d_%h_%Y --date='5 days ago'`;" 

Open in new window

yeah, -5d did not look like the linux syntax either...  i assumed you worked on a different system

i find this one a bit easier to read

docker exec mysql mysql -u root -pmypassword niis -e  'DROP\ TABLE\ patching_'`date  +%d_%h_%Y --date=-5days`

Open in new window


or even

docker exec mysql mysql -u root -pmypassword niis -e `date  '+DROP TABLE patching_%d_%h_%Y' --date=-5days`"

Open in new window


... good to see you got it right anyway

btw, when in doubt, you can usually debug such things by prepending 'echo' to whichever command is built in a possibly incomplete way and/or running "sh -x" rather than just sh
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.