Scripting a mysqlcommand to create/delete tables based on date

rleyba828
rleyba828 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Author

Commented:
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.

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial