piwik

dimecres, 12 de juny del 2013

myLVMbackup script with rotation 7 daily , 4 weekly, 4 monthly 


With this is script, we set up a backup system for the mySQL database with rotation. The rotation schedule is the following, let's say we launched the script 5 months ago, today we will find in our daily directory 7 backups, in weekly directory we see the backups from last 4 mondays, and in monthly directory there are the backup of first monday of the past 4 months


In that case we make use of rsync with symbolic links, which allows us to perform incremental backups while keeping the availability of a full backup.




#!/bin/bash
#Copyright (C) 2013 Josep Manel Andrés Moscardó
#
#This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published
#by the Free Software Foundation, either version 3 of the License,
# or (at your option) any later version.
#
#This program is distributed in the hope that it will be useful, but
#WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANT
#ABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General 
#Public License for more details.
#
#You should have received a copy of the GNU General Public License 
#along with this program. If not, see http://www.gnu.org/licenses/.
#

CAL=/usr/bin/cal
#Format= 2013-03-20
YESTERDAY=`date --date=yesterday +%Y-%m-%d`
#Int (where Monday is 1 and Sunday is 7)
TODAY=`date +%u`
#Format= 2013-03-20
SEVENDAYSAGO=`date --date '7 days ago' +%Y-%m-%d`
FOURWEEKSAGO=`date --date '28 days ago' +%Y-%m-%d`
FIVEWEEKSAGO=`date --date '35 days ago' +%Y-%m-%d`

#These variables will be used to find out which day is the backup from 5months ago that we have to remove
FIVEMONTHSAGOMONTH=`date --date='5 month ago' +%m`
FIVEMONTHSAGOYEAR=`date --date='5 month ago' +%Y`

#This function will give us the date of the first Monday of 5 months ago month.
function date5monthsago(){
  #Exact day for the backup from 5months ago
  local aux=`$CAL $FIVEMONTHSAGOMONTH $FIVEMONTHSAGOYEAR |
  awk '
  NR == 1 { next }
  NR == 2 { next }
  NF <= 5 { next }
  NF == 6 { print $1 ; exit }
  NF == 7 { print $2 ; exit }
  '`
  echo $aux
}

#AUX used for checking whether the backup from 28 days ago is the first Monday of the month backup, if so We'll keep it in monthly directory, if not, we'll remove it.
AUX=`date --date '35 days ago' +%d`

#Do daily backup and store it on /home/backup/daily
#To log Starting time
START=$(date +%s)
###########################################################
echo "#########################Starting myLVMbackup for dbMaster on dbBackup#####################" >> /home/backup/daily/mylvm-dbmaster-`date +%Y-%m-%d`.log 2>>1
###########################################################
mylvmbackup --socket=/var/run/mysqld/mysqld1.sock --innodb_recover --vgname=vgpool --lvname=lvdata --backuptype=rsync --relpath=replic_dbmaster --prefix=dbmaster  --backupdir=/home/backup/daily --datefmt=%Y-%m-%d --rsyncarg=" -a --link-dest=/home/backup/daily/dbmaster-${YESTERDAY}_mysql " >> /home/backup/daily/mylvm-dbmaster-`date +%Y-%m-%d`.log 2>&1
###########################################################
###########################################################

#To log Finishing time
END=$(date +%s)
TOTALTIME=$(( $END - $START ))
echo "############################################################################################" >> /home/backup/daily/mylvm-dbmaster-`date +%Y-%m-%d`.log
echo "Start time:$START, finish time:$END, seconds:$TOTALTIME" >> /home/backup/daily/mylvm-dbmaster-`date +%Y-%m-%d.log`


#We will check if today is Monday, if so we'll move last monday's backup 1 directory up
if [ "$TODAY" -eq "1" ]; then
  if [ -d "/home/backup/daily/dbmaster-${SEVENDAYSAGO}_mysql"  ]; then
    mv /home/backup/daily/dbmaster-${SEVENDAYSAGO}_mysql /home/backup/weekly/
    mv /home/backup/daily/mylvm-dbmaster-${SEVENDAYSAGO}.log /home/backup/weekly/
  fi
  #Checking for a backup older than 28 days and also from first Monday of the month.If the backup from 28 days ago is not the firstMondayOfTheMonth we'll expire, or else keep it in monthly directory.  
  if [ -d "/home/backup/weekly/dbmaster-${FIVEWEEKSAGO}_mysql" ]; then
    #Is it from first Monday of the month?
    if [ "$AUX" -le "7" ]; then
      mv /home/backup/weekly/dbmaster-${FIVEWEEKSAGO}_mysql /home/backup/monthly/
      mv /home/backup/weekly/mylvm-dbmaster-${SEVENDAYSAGO}.log /home/backup/monthly/
      #Execute function to find out exact date for 5 months ago
      FIVEMONTHSAGODAY=$(date5monthsago)
      #Expire backups older than 4 months ago. Note that FIVEMONTHSAGODAY has only 1 digit,that is why we add "0" before.
      if [ -d "/home/backup/monthly/dbmaster-${FIVEMONTHSAGOYEAR}-${FIVEMONTHSAGOMONTH}-${FIVEMONTHSAGODAY}_mysql" ]; then
        rm -rf /home/backup/monthly/dbmaster-${FIVEMONTHSAGOYEAR}-${FIVEMONTHSAGOMONTH}-0${FIVEMONTHSAGODAY}_mysql
        rm /home/backup/monthly/mylvm-dbmaster-${FIVEMONTHSAGOYEAR}-${FIVEMONTHSAGOMONTH}-0${FIVEMONTHSAGODAY}.log
      fi
    else
      rm -rf /home/backup/weekly/dbmaster-${FIVEWEEKSAGO}_mysql
      rm /home/backup/weekly/mylvm-dbmaster-${FIVEWEEKSAGO}.log
    fi
  fi

#If today is NOT Monday we'll expire old daily backups
else
  #Is there a backup from 1weekago? If so we'll exxpire it
  if [ -d "/home/backup/daily/dbmaster-${SEVENDAYSAGO}_mysql" ]; then
    rm -rf /home/backup/daily/dbmaster-${SEVENDAYSAGO}_mysql
    #Remove log file also
    rm /home/backup/daily/mylvm-dbmaster-${SEVENDAYSAGO}.log
  fi    
fi
rm /root/debug-${SEVENDAYSAGO}.log

###################################################################
###Code for dbslave replication backup#############################
#To log Starting time
START2=$(date +%s)
###################################################################
echo "#########################Starting myLVMbackup for dbSlave on dbBackup#####################" >> /home/backup/daily/mylvm-dbslave-`date +%Y-%m-%d`.log 2>>1
###################################################################
mylvmbackup --socket=/var/run/mysqld/mysqld2.sock --innodb_recover --vgname=vgpool --lvname=lvdata --backuptype=rsync --relpath=replic_dbslave --prefix=dbslave  --backupdir=/home/backup/daily --datefmt=%Y-%m-%d --rsyncarg=" -a --link-dest=/home/backup/daily/dbslave-${YESTERDAY}_mysql " >> /home/backup/daily/mylvm-dbslave-`date +%Y-%m-%d`.log 2>>1

#To log Finishing time
END2=$(date +%s)
TOTALTIME2=$(( $END2 - $START2 ))
echo "############################################################################################" >> /home/backup/daily/mylvm-dbslave-`date +%Y-%m-%d`.log
echo "Start time:$START2, finish time:$END2, seconds:$TOTALTIME2" >> /home/backup/daily/mylvm-dbslave-`date +%Y-%m-%d.log`

#We will check if today is Monday, if so we ll move last mondays backup 1 directory up
if [ "$TODAY" -eq "1" ]; then
  if [ -d "/home/backup/daily/dbslave-${SEVENDAYSAGO}_mysql"  ]; then
    mv /home/backup/daily/dbslave-${SEVENDAYSAGO}_mysql /home/backup/weekly/
    mv /home/backup/daily/mylvm-dbslave-${SEVENDAYSAGO}.log /home/backup/weekly/
  fi
  #Checking for a backup older than 28 days and also from first Monday of the month.If the backup from 28 days ago is not the firstMondayOfTheMonth we ll expire, or else keep it in monthly directory.  
  if [ -d "/home/backup/weekly/dbslave-${FIVEWEEKSAGO}_mysql" ]; then
    #Is it from first Monday of the month?
    if [ "$AUX" -le "7" ]; then
      mv /home/backup/weekly/dbslave-${FIVEWEEKSAGO}_mysql /home/backup/monthly/
      mv /home/backup/weekly/mylvm-dbslave-${SEVENDAYSAGO}.log /home/backup/monthly/
      #Execute function to find out exact date for 5 months ago
      FIVEMONTHSAGODAY=$(date5monthsago)
      #Expire backups older than 4 months ago. Note that FIVEMONTHSAGODAY has only 1 digit,that is why we add "0" before.
      if [ -d "/home/backup/monthly/dbslave-${FIVEMONTHSAGOYEAR}-${FIVEMONTHSAGOMONTH}-${FIVEMONTHSAGODAY}_mysql" ]; then
        rm -rf /home/backup/monthly/dbslave-${FIVEMONTHSAGOYEAR}-${FIVEMONTHSAGOMONTH}-0${FIVEMONTHSAGODAY}_mysql
        rm /home/backup/monthly/mylvm-dbslave-${FIVEMONTHSAGOYEAR}-${FIVEMONTHSAGOMONTH}-0${FIVEMONTHSAGODAY}.log
      fi
    else
      rm -rf /home/backup/weekly/dbslave-${FIVEWEEKSAGO}_mysql
      rm /home/backup/weekly/mylvm-dbslave-${FIVEWEEKSAGO}.log
    fi
  fi

#If today is NOT Monday we ll expire old daily backups
else
  #Is there a backup from 1weekago? If so we will expire it
  if [ -d "/home/backup/daily/dbslave-${SEVENDAYSAGO}_mysql" ]; then
    rm -rf /home/backup/daily/dbslave-${SEVENDAYSAGO}_mysql
    #Remove log file also
    rm /home/backup/daily/mylvm-dbslave-${SEVENDAYSAGO}.log
  fi    
fi
rm /root/debug-slave${SEVENDAYSAGO}.log















Publicat per



a





Cap comentari:
  






















Etiquetes de comentaris:
,
,

















          

        

          

        

dimecres, 5 de juny del 2013


          

        









How to avoid apparmor errors when running multiple mysql instances














When Apparmor complains about deny access 
to some directory, like in this case with mysql data paths and 
mysql.sock, we'll have to add those paths to the apparmor config file 
for mysql.






root@server10124:~# vi /etc/apparmor.d/usr.sbin.mysqld
  /var/run/mysqld/mysqld.pid w,
  /var/run/mysqld/mysqld.sock w,
  /var/run/mysqld/mysqld1.pid w,
  /var/run/mysqld/mysqld1.sock w,
  /var/run/mysqld/mysqld2.pid w,
  /var/run/mysqld/mysqld2.sock w,
  /run/mysqld/mysqld.pid w,
  /run/mysqld/mysqld.sock w,
  /run/mysqld/mysqld1.pid w,
  /run/mysqld/mysqld1.sock w,
  /run/mysqld/mysqld2.pid w,
  /run/mysqld/mysqld2.sock w,
  /home/replic_dbmaster/** rwk,
  /home/replic_dbmaster/ r,
  /home/replic_dbslave/** rwk,
  /home/replic_dbslave/ r,




Then, all we have to do is reload Apparmor and start up mysql instances.





root@server10124:~# service apparmor restart















Publicat per



a





Cap comentari:
  






















Etiquetes de comentaris:
,
,

























Piwik installation












Since we are hosting many websites, we are going to need to define an Apache VirtualHost with the following configuration.



vi /etc/apache2/sites-available/piwik.arruixaqueplou.org



     ServerAdmin webmaster@piwik.arruixaqueplou.org
     ServerName piwik.arruixaqueplou.org
     ServerAlias piwik.arruixaqueplou.org
     DocumentRoot /var/www/public_html/
     ErrorLog /var/www/public_html/logs/error.log
     CustomLog /var/www/public_html/logs/access.log combined




After that, we will need to enable the site and restart apache:




a2ensite piwik.arruixaqueplou.org
service apache2 reload




We are going to need the latest Piwik version, available through 
their web servers, so we will need to go to the Piwik root directory 
were the installation is going to take place and pull the latest 
version.




wget http://piwik.org/latest.zip
unzip latest.zip -d public_html




Assign the right permissions for the web server:




chown -R www-data:www-data public_html/



Before running Piwik's installation script, we need to change the
permissions of several directories. Piwik requires these permissions
to remain set to function properly. Issue the following commands:




chmod a+w /var/www/public_html/tmp
chmod a+w /var/www/public_html/config



Now we are ready to go through web browser web based installation process by typing piwik.arruixaqueplou.org into the browser.



mySQL users

We had added a piwikuser accound with full privileges.




mysql> create user 'piwikuser'@'localhost' identified by 'piwikpass!';
mysql> grant all privileges on piwik.* to 'piwikuser'@'localhost' with grant option;





The next screen will ask us to create a Piwik account with full 
privileges.










Publicat per



a





Cap comentari:
  






















Etiquetes de comentaris:

























How to allow zabbix user to execute certain command as a privilege user
















We just need to add the following line to /etc/sudoers



1


zabbix ALL=(root) NOPASSWD:/bin/bash /etc/zabbix/scripts/hparray.sh










After NOPASSWD: we'll place the commands we want to allow.












Publicat per



a





Cap comentari:
  






















Etiquetes de comentaris:
,

























Monitor swap space in Zabbix 













#Monitoring % Swap space
UserParameter=swap.percentage, free|tail -n -1|awk '{print ($3/$2)*100}'












Publicat per



a





Cap comentari:
  














































Script that monitors the status of a HP physical RAID



















 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


#!/bin/bash

PHDRIVE[1]=`hpacucli ctrl all show config detail | grep "physicaldrive 1I:1:1" | awk '{print $10}' | sed 's/)//' | sed -e 's/[\t ]//g;/^$/d'`
PHDRIVE[2]=`hpacucli ctrl all show config detail | grep "physicaldrive 1I:1:2" | awk '{print $10}' | sed 's/)//' | sed -e 's/[\t ]//g;/^$/d'`
PHDRIVE[3]=`hpacucli ctrl all show config detail | grep "physicaldrive 1I:1:3" | awk '{print $10}' | sed 's/)//' | sed -e 's/[\t ]//g;/^$/d'`
PHDRIVE[4]=`hpacucli ctrl all show config detail | grep "physicaldrive 1I:1:4" | awk '{print $10}' | sed 's/)//' | sed -e 's/[\t ]//g;/^$/d'`
#PHDRIVE[1]="FAULT"
#PHDRIVE[2]="FAULT"
#PHDRIVE[3]="OK"
#PHDRIVE[4]="OK"
#echo ${PHDRIVE[1]}

#Meaning of the variable ALARM, 
# 0 ----------- No alarms, everything OK
# 1 ----------- Disk 1 doesn't have STATUS OK
# 2 ----------- Disk 2 doesn't have STATUS OK
# 3 ----------- Disk 3 doesn't have STATUS OK
# 4 ----------- Disk 4 doesn't have STATUS OK
ALARM=0

for i in `seq 1 4`;
do
  if [ "${PHDRIVE[$i]}" != "OK" ]; then
    ALARM=$i
  fi
done

echo $ALARM



















Publicat per



a





Cap comentari:
  






















Etiquetes de comentaris:
,

























How to list all the applications that had been installed through apt-get













( zcat $( ls -tr /var/log/apt/history.log*.gz ) ; cat /var/log/apt/history.log ) | egrep '^(Start-Date:|Commandline:)' | grep -v aptdaemon | egrep '^Commandline:' | sed 's/Commandline: //'












Publicat per



a





Cap comentari:
  






















Etiquetes de comentaris:

























How to send emails through command line













echo -e "Subject: Here is gonna be the subject\nFrom: backup@arruixaqueplou.org\nTo: alarm@arruixaqueplou.org\n\$(`cat /home/backup/daily/mylvm-$(date +%Y-%m-%d.log) && cat /home/backup/web2/mysql/mysql_$(date +%Y-%m-%d)/backup_mysql_$(date +%Y-%m-%d) && cat /home/backup/web4/mysql/mysql_$(date +%Y-%m-%d)/backup_mysql_$(date +%Y-%m-%d) && cat /home/backup/web2/gluster/gluster_$(date +%Y-%m-%d)/backup_gluster_$(date +%Y-%m-%d)`) " | sendmail -t












Publicat per



a





Cap comentari:
  






















Etiquetes de comentaris:

























Sed hacks.












How to remove empty lines from a file:

sed '/^$/d' file.txt

Remove commented lines from a specific file

sed 's/^#.*//' file.txt

Above command will replace # 'ed lines by empty lines, in case we want to merge both steps into a single command, we'll issue the following command.

sed -e 's/^#.*//;/^$/d' file.txt










Publicat per



a





Cap comentari:
  






















Etiquetes de comentaris:

























MySQL commands












==========================

Backup database using dump

==========================

1.- Backup

mysqldump -u root xcms > new-xcms.sql



2.- Restore

(Make sure we have created the corresponding database)

mysql -u root -proot xcms < new-xcms.sql



3.-Rename DB

---------------

mysql -e "CREATE DATABASE \`new_database\`;"



for table in `mysql -B -N -e "SHOW TABLES;" old_database`

do 

  mysql -e "RENAME TABLE \`old_database\`.\`$table\` to \`new_database\`.\`$table\`"

done

mysql -e "DROP DATABASE \`old_database\`;"

----------------







4.-Create DB

create database old_xcms;



5.- Backup triggers and stored procedures

mysqldump -u root --all-databases --triggers --routines -d -t > dbMasterProc.sql



6.- Create user and grant all privileges

mysql> create user 'root'@'172.16.%';

mysql> grant all privileges on *.* to 'root'@'172.16.%';

mysql> show grants;

+--------------------------------------------------+

| Grants for root@172.16.%                         |

+--------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.16.%' |

+--------------------------------------------------+

1 row in set (0.00 sec)



7.- Skip execution of one row in replication servers:

mysql> set global sql_slave_skip_counter = 1;



############

Show columns

############

mysql> show columns from tbl_gamechat;

+----------------+----------------------+------+-----+---------+----------------+

| Field          | Type                 | Null | Key | Default | Extra          |

+----------------+----------------------+------+-----+---------+----------------+

| GCHT_iID       | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |

| GCHT_TLST_iID  | int(10) unsigned     | NO   | MUL | 0       |                |

| GCHT_GAM_iID   | int(10) unsigned     | NO   | MUL | 0       |                |

| GCHT_PLY_iID   | int(10) unsigned     | NO   | MUL | 0       |                |

| GCHT_cText     | varchar(255)         | NO   |     |         |                |

| GCHT_dDate     | timestamp            | YES  | MUL | NULL    |                |

| GCHT_iChatType | smallint(5) unsigned | NO   |     | 0       |                |

+----------------+----------------------+------+-----+---------+----------------+



##########

See rows

##########



mysql> select * from tbl_gamechat where GCHT_iID='24875';

+----------+---------------+--------------+--------------+------------+---------------------+----------------+

| GCHT_iID | GCHT_TLST_iID | GCHT_GAM_iID | GCHT_PLY_iID | GCHT_cText | GCHT_dDate          | GCHT_iChatType |

+----------+---------------+--------------+--------------+------------+---------------------+----------------+

|    24875 |          1148 |       756640 |      1029050 |            | 2013-02-19 12:31:03 |              0 |

+----------+---------------+--------------+--------------+------------+---------------------+----------------+

1 row in set (0.00 sec)



##########

Delete row

##########



mysql> delete  from tbl_gamechat where GCHT_iID='24875';










Publicat per



a





Cap comentari:
  






















Etiquetes de comentaris:
,

















          

        

          

        

dimarts, 4 de juny del 2013


          

        









Usefull hacks for grep command.












How to count the number of lines that match with a specific pattern in a file:

grep -c Josep /etc/passwd

Using -v option, we'll be inverting the results. The number of lines that do not match the pattern will be shown.

grep -cv Josep /etc/passwd

Ignoring case sensitive:

grep -i josep /etc/passwd



Searching through all subdirectories the files that inside them have at least one occurrence of the pattern.

grep -r mysql /home/josep 










Publicat per



a





Cap comentari:
  






















Etiquetes de comentaris:

















        

      









Subscriure's a:
Missatges (Atom)