I decided to publish my own created cool bash scripts...
Delete inactive characters:
I know in database structure exist deltimers, not always work fine...
Cool characters statistics...
Backup mysql + send into ftp
Delete inactive characters:
Code:
#!/bin/bash
# Delete inactive characters. script by Dulin
MYSQL_USER=''
MYSQL_PASS=''
MYSQL_DB=''
WEEKS='2'
# Don't touch anything...
DATA=`date +%s`
CALC=`expr $DATA - $WEEKS \* 7 \* 24 \* 60 \* 60`
MYSQL_LOGIN="--user=$MYSQL_USER --password=$MYSQL_PASS $MYSQL_DB"
# Make log with deleted characters
echo "SELECT id,name from players where lastlogin < $CALC and id > 6;" | mysql $MYSQL_LOGIN >> ./removed.log
echo "Removing inactive players..."
for db in $(echo "SELECT id from players where lastlogin < $CALC and id > 6;" | mysql $MYSQL_LOGIN | awk {'print $1'} | sed -e '1d')
do
echo `echo "SELECT name from players where id = $db;" | mysql $MYSQL_LOGIN`
echo "DELETE FROM player_skills WHERE player_id = $db;" | mysql $MYSQL_LOGIN >/dev/null
echo "DELETE FROM player_storage WHERE player_id = $db;" | mysql $MYSQL_LOGIN >/dev/null
echo "DELETE FROM player_items WHERE player_id = $db;" | mysql $MYSQL_LOGIN >/dev/null
echo "DELETE FROM player_depotitems WHERE player_id = $db;" | mysql $MYSQL_LOGIN >/dev/null
echo "DELETE FROM player_deaths WHERE player_id = $db;" | mysql $MYSQL_LOGIN >/dev/null
echo "DELETE FROM players WHERE id = $db;" | mysql $MYSQL_LOGIN >/dev/null
done
Cool characters statistics...
Code:
#!/bin/bash
# Char stats. By Dulin
MYSQL_USER=''
MYSQL_PASS=''
MYSQL_DB=''
WEEKS='2'
# Don't touch anything...
DATA=`date +%s`
CALC=`expr $DATA - $WEEKS \* 7 \* 24 \* 60 \* 60`
MYSQL_LOGIN="--user=$MYSQL_USER --password=$MYSQL_PASS $MYSQL_DB"
echo "Statistics of $MYSQL_DB..."
echo -n "Inactive players: "
echo "SELECT COUNT(id) from players where lastlogin < $CALC and id > 6;" | mysql $MYSQL_LOGIN | sed -e '1d'
echo -n "Active players: "
echo "SELECT COUNT(id) from players where lastlogin > $CALC and id > 6;" | mysql $MYSQL_LOGIN | sed -e '1d'
echo -n "Total players: "
echo "SELECT COUNT(id) from players where id > 6;" | mysql $MYSQL_LOGIN | sed -e '1d'
Backup mysql + send into ftp
Code:
#!/bin/bash
# Mysql backup+ftp by Dulin
# Configuration
ftp_site=''
ftp_user=''
ftp_pass=''
BACKUPD="/backup/mysql"
MySQLuser=''
MySQLpass=''
# End configuration
ROK=`date +%Y`
MIESIAC=`date +%m`
DZIEN=`date +%d`
GIM=`date +%H-%M`
if [ -d $BACKUPD/$ROK/$MIESIAC/$DZIEN ]
then
echo
else
mkdir -p $BACKUPD/$ROK/$MIESIAC/$DZIEN
fi
for db in $(echo "SHOW DATABASES;" | mysql --user=$MySQLuser --password=$MySQLpass | grep -v -e "Database" -e "information_schema")
do
mysqldump --user="$MySQLuser" --password="$MySQLpass" $db >$BACKUPD/$ROK/$MIESIAC/$DZIEN/$db.sql
done
cd $BACKUPD/$ROK/$MIESIAC/$DZIEN && tar --remove-files -jcPf $BACKUPD/$ROK/$MIESIAC/$DZIEN/mysql-$GIM.tar.bz2 *.sql
ftp -in <<EOF
open $ftp_site
user $ftp_user $ftp_pass
bin
mkdir mysql
mkdir mysql/$ROK-$MIESIAC-$DZIEN
put $BACKUPD/$ROK/$MIESIAC/$DZIEN/mysql-$GIM.tar.bz2 mysql/$ROK-$MIESIAC-$DZIEN/mysql-$GIM.tar.bz2
close
bye
EOF
Last edited: