Mit CPC Mit CPC - 2 months ago 8
MySQL Question

Unable to delete 90 days older data from mysql via shell script

Below shell script:

#!/bin/bash

date +'%F %T'

echo "Show Database"
mysql -u [user] -p[password] -e 'USE eventime;SELECT date1,time1 FROM dt;'

if [ "$date + '%F %T'" > "$SELECT date1,time1 FROM dt" ]; then
mysql -u [user] -p[password] -u 'USE eventime;DELETE from dt where = 'date1';

echo "Delete Success!";
else
echo "Can't Delete row";
fi

Answer

Your script with errors. But you can user mysql script only, quite easy If you need to delete row, older than 90 days:

SELECT STR_TO_DATE(CONCAT(DATE_FORMAT(now(),'%Y-%m-%d'),'-1'),'%Y-%m-  %d')-INTERVAL 90 DAY;

Please check result, should by date you need. If not, correct query. So

delete from dt  where date1 >(SELECT STR_TO_DATE(CONCAT(DATE_FORMAT(now(),'%Y-%m-%d'),'-1'),'%Y-%m-%d')-
INTERVAL 90 DAY);   

Please be careful and read help for sql fuctions used in case of doubt. Next time put output obtained, sofware versions and so on, so answers will be more exact. Your bash script will contain shao-bao, licence information, and command mysql to execute query :) Good luck.