#!/bin/sh
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
MYSQL_BACKUP_PATH=/backup/mysql
BACKUP_DATE=`date "+%Y-%m-%d"`
set mask 077
cd $MYSQL_BACKUP_PATH/$PORT
find . -mtime +21 -delete
# 3306
PORT=3306
cd $MYSQL_BACKUP_PATH/$PORT
mkdir -p $BACKUP_DATE
cd $BACKUP_DATE
for MYSQL_DB in $(mysql -u backup -pSECRET -S /tmp/mysql.sock$PORT -Bse 'show databases' | grep -v '_schema'); do \
mkdir -p $MYSQL_DB
echo '===>' $MYSQL_DB;
mysqldump -u backup -pSECRET -S /tmp/mysql.sock$PORT $MYSQL_DB --no-data --triggers --routines --events > $MYSQL_DB/_structur
e.sql
for MYSQL_TABLE in $(mysql -u backup -pSECRET -S /tmp/mysql.sock$PORT -Bse 'show tables' $MYSQL_DB); do \
echo $MYSQL_TABLE;
mysqldump -u backup -pSECRET -S /tmp/mysql.sock$PORT $MYSQL_DB $MYSQL_TABLE --opt --lock-all-tables --default-character-set=cp1251 > $MYSQL_DB/$MYSQL_TABLE.sql
done
echo 'Archiving '$MYSQL_DB;
tar czf $MYSQL_DB.tgz $MYSQL_DB
rm -rf $MYSQL_DB
done
rsync -avR --password-file=/etc/rsync.key /backup/mysql rsync@where_you_want::backup_folder
CREATE DEFINER=`root`@`localhost` EVENT `clear_old_stat` ON SCHEDULE EVERY 1 DAY STARTS '2020-09-02 16:43:42' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
DELETE FROM ox_data_intermediate_ad WHERE interval_end < DATE_SUB(CURDATE(), INTERVAL 3 MONTH);
OPTIMIZE TABLE ox_data_intermediate_ad;
DELETE FROM ox_data_summary_ad_hourly WHERE date_time < DATE_SUB(CURDATE(), INTERVAL 3 MONTH);
OPTIMIZE TABLE ox_data_summary_ad_hourly;
DELETE FROM ox_data_summary_ad_zone_assoc WHERE interval_end < DATE_SUB(CURDATE(), INTERVAL 3 MONTH);
OPTIMIZE TABLE ox_data_summary_ad_zone_assoc;
DELETE FROM ox_data_summary_zone_impression_history WHERE interval_end < DATE_SUB(CURDATE(), INTERVAL 3 MONTH);
OPTIMIZE TABLE ox_data_summary_zone_impression_history;
END