Sysadmin grodoudou backup mysql 14062012

Session

• user : root
• host : grodoudou.seizam.com
• date : 14/06/12 17:22
• curpath : /root

Commands

 [email protected]# cd /root/

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

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

• An email has been sent to root
• Now, check newly created backup

 [email protected]# ls -lsah /mnt/backups/

total 28K
4.0K drwxr-xr-x 4 root root 4.0K Jun 14 17:25 .
4.0K drwxr-xr-x 3 root root 4.0K Nov 13  2010 ..
4.0K drwx------ 2 root root 4.0K Jun 14 17:25 20120614
 16K drwx------ 2 root root  16K Jun  7 16:49 lost+found

 [email protected]# ls -lsah /mnt/backups/20120614/

total 532K
4.0K drwx------ 2 root root 4.0K Jun 14 17:25 .
4.0K drwxr-xr-x 4 root root 4.0K Jun 14 17:25 ..
4.0K -rwx------ 1 root root  413 Jun 14 17:25 mysqldump-information_schema-20120614.sql.gz
140K -rwx------ 1 root root 134K Jun 14 17:25 mysqldump-mysql-20120614.sql.gz
376K -rwx------ 1 root root 372K Jun 14 17:25 mysqldump-wikidb-20120614.sql.gz
4.0K -rwx------ 1 root root  596 Jun 14 17:25 report.log

• Edit /etc/crontab, see [2]

Files

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

--- old	2012-06-14 17:22:52.000000000 +0200
+++ new	2012-06-14 17:24:35.000000000 +0200
@@ -0,0 +1,101 @@
+
+#!/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';
+
+
+
+
+
+##########
+# 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 "Backup ${backup_date} report" ${mail} < ${report_file};

• [2] Edit /etc/crontab

--- old	2012-06-14 17:30:12.000000000 +0200
+++ new	2012-06-14 17:32:29.000000000 +0200
@@ -25,2 +25,5 @@
 
+# each day at 7 am (fr timezone)
+0 5	* * *	root	/root/mysql-daily-backup.sh
+
 */1 * * * * root /usr/local/rtm/bin/rtm 42 > /dev/null 2> /dev/null