rleyba828
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.
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:
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.
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; "
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`;
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.
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:
causes the interpreter to output:
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.
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`;
causes the interpreter to output:
date: invalid option -- '5'
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.
ASKER
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'`;"
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
or even
... 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
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`
or even
docker exec mysql mysql -u root -pmypassword niis -e `date '+DROP TABLE patching_%d_%h_%Y' --date=-5days`"
... 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 TRIALMembers 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.
the date command is in this case executed outside of the container. i suppose you don't care