Sysadmin papilusion mysql backup 19 06 2012 - Seizam/seizamcore GitHub Wiki

• user : root
• host : papilusion.reverse.seizam.com
• date : 06/19/12 09:21
• curpath : /root

• Edit /root/mysql-daily-backup.sh, see [1]
• Edit /root/mysql-daily-backup.sh, see [2]

 [email protected]# chmod 550 mysql-daily-backup.sh 

• Edit /etc/crontab, see [3]

[1]\* Edit /root/mysql-daily-backup.sh

--- old	2012-06-19 09:22:28.000000000 +0000
+++ new	2012-06-19 09:23:15.000000000 +0000
@@ -0,0 +1,102 @@
+#!/bin/bash
+# Based on script from http://blog.m0le.net/2012/02/17/le-petit-script-de-backup-de-nono/ [[email protected], v1.0, 16/02/2012]
+
+
+
+# folders and files names date formats
+short_date_format='%Y%m%d';
+full_date_format='%Y%m%d-%H%M%S';
+
+# How many days we want to keep backups
+keepday="7";
+
+# Where to store backup
+backup_dir='/mnt/backups';
+
+# Who will receive report
+mail='root';
+
+# Mail subject
+mail_subject="[Report] [Papilusion]";
+
+
+
+##########
+# Script #
+##########
+
+
+
+
+# Create backup dir (name contains date)
+backup_date=`date +${short_date_format}`;
+curr_backup_dir=${backup_dir}'/'${backup_date};
+if [ -d "${curr_backup_dir}" ]
+then
+	# use full date format if there is already a backup with the short format
+	backup_date=`date +${full_date_format}`;
+	curr_backup_dir=${backup_dir}'/'${backup_date};
+fi
+# -p = no error if existing, make parent directories as needed
+mkdir -p ${curr_backup_dir};
+
+# Report init
+report_file=${curr_backup_dir}'/report.log';
+start_date=`date +'%Y/%m/%d %H:%M:%S'`;
+echo 'Backup '${backup_date}' report' > ${report_file}; 
+echo " " >> ${report_file};
+echo 'Starts '${start_date} > ${report_file};
+echo " " >> ${report_file};
+
+echo 'Current backup will be '${curr_backup_dir} >> ${report_file};
+echo " " >> ${report_file};
+
+
+
+# Which directories to backup + which to exclude
+# syntax :
+#     tar jcf le_nom_de_larchive.tar.bz2 --exclude='/le/repertoire/a/exclure' --exclude='/eventuellement/le/deuxieme/repertoire/a/exclure' /le/dossier/a/archiver/ 2>/dev/null
+#     tar jcf ${curr_backup_dir}/m0le.net-${backup_date}-var-lib-mysql.tar.bz2 /var/lib/mysql/ 2>/dev/null
+
+#tar jcf ${curr_backup_dir}/szfiles-${backup_date}.tar.bz2 /var/www/seizam/usersFilesAreThere 2>/dev/null
+
+
+
+# Creates databases dumps using MySQL maintenance user
+for DB in $(echo "show databases" | mysql --defaults-file=/etc/mysql/debian.cnf -N)
+do
+	output=${curr_backup_dir}/mysqldump-${DB}-${backup_date}.sql;
+	mysqldump --defaults-file=/etc/mysql/debian.cnf $DB > ${output} 2>/dev/null;
+        gzip ${output};
+	chmod -R 700 ${curr_backup_dir};
+done
+
+# Write what we have backed up to report
+echo "Backup content:" >> ${report_file};
+ls -lh ${curr_backup_dir} -I report.log >> ${report_file};
+echo " " >> ${report_file};
+
+# Delete the backup created $removal_date ($keepday days ago)
+removal_date=`date --date "-${keepday} days" +${short_date_format}`;
+echo "Removal of backup(s) exactly ${keepday} days old, ie backup(s) of ${removal_date}. Going to be removed:" >> ${report_file};
+echo "{" >> ${report_file};
+ls -lh ${backup_dir} | grep " ${removal_date}" >> ${report_file};
+echo "}" >> ${report_file};
+removal_backup_dir=${backup_dir}'/'${removal_date};
+rm -rf ${removal_backup_dir}*;
+echo " " >> ${report_file};
+
+# List available backups
+echo "Now available backups:" >> ${report_file};
+ls -lh ${backup_dir} >> ${report_file};
+echo " " >> ${report_file};
+
+# Log end date
+end_date=`date +'%Y/%m/%d %H:%M:%S'`;
+echo " " >> ${report_file};
+
+# Finalize report, lock files and send by email
+echo 'Ends '${end_date} >> ${report_file};
+chmod -R 500 ${curr_backup_dir};
+mail -s "${mail_subject} Backup ${backup_date} report" ${mail} < ${report_file};
+

[2]\* Edit /root/mysql-daily-backup.sh

--- old	2012-06-19 09:26:31.000000000 +0000
+++ new	2012-06-19 09:30:41.000000000 +0000
@@ -90,3 +90,5 @@
 echo "Now available backups:" >> ${report_file};
-ls -lh ${backup_dir} >> ${report_file};
+du -sh ${backup_dir} >> ${report_file};
+ls -lh ${backup_dir} | grep -v total >> ${report_file};
+
 echo " " >> ${report_file};

[3]\* Edit /etc/crontab

--- old	2012-06-19 09:34:29.000000000 +0000
+++ new	2012-06-19 09:36:56.000000000 +0000
@@ -19,2 +19,8 @@
 
+# each day at 7.30 fr
+30 5	* * *	root	ntpdate-debian >> /var/log/ntpdate-debian-cron.log
+
+# each day at 7.45 fr
+45 7	* * *	root    /root/mysql-daily-backup.sh
+
 # each day at 8.00 fr
@@ -27,4 +33 @@
 */5 *	* * *	root	/root/exec-php-send-daily-report.sh "extensions/Wikiplaces/updateUsages.php --lifespan=30" /var/seizam/w
-
-# each day at 7.30 fr
-30 5	* * *	root	ntpdate-debian >> /var/log/ntpdate-debian-cron.log