{"id":2218,"date":"2013-07-11T11:21:37","date_gmt":"2013-07-11T03:21:37","guid":{"rendered":"http:\/\/rmohan.com\/?p=2218"},"modified":"2013-07-11T11:37:09","modified_gmt":"2013-07-11T03:37:09","slug":"mysql-backup-using-samba","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=2218","title":{"rendered":"Mysql backup using samba"},"content":{"rendered":"<pre><strong>#!\/bin\/bash<\/strong>\r\n\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # This script creates a copy of the \/opt\/zimbra folder and then compress<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # it localy before sending it over the nework so make sure there is enough space<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # left on your local hard drive ($parentfold variable).<\/strong>\r\n\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # A samba share is used in this script, fill the ## NETWORKING ## section<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # and the $pathfold variable with your info.<\/strong>\r\n\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # sendEmail is used in this script, if you wish to use it, fill the ## MAILING ## section<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # with your info.<\/strong>\r\n\r\n<strong>dateformat=$(date +\\%d-\\%m-\\%Y)\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# Format of the date used in the script.<\/strong>\r\n<strong>datebegin=$(date)\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# Date when the backup begins its routine.<\/strong>\r\n<strong>beginjob=$(date +%s)\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# Second at which the routine starts.<\/strong>\r\n<strong>remotename=mremote\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# Name of the temporary local folder where to mount the Samba share.<\/strong>\r\n<strong>pathfold=\/\/PATH\/TO\/SHARE\/MYSQL\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# Network folder for the backup; **folder must exist remotely**.<\/strong>\r\n<strong>parentfold=\/tmp\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# Where it all begins.<\/strong>\r\n<strong>basefold=$parentfold\/.mysql\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# Local parent folder created for the script usage.<\/strong>\r\n<strong>remotefold=$basefold\/$remotename\u00a0\u00a0 \u00a0# Path of local folder to mount the remote Samba share.<\/strong>\r\n<strong>muser=root\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# User that launch the backup routine (root).<\/strong>\r\n<strong>mpass='ROOT-PASSWORD'\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# Password of the user that launch the backup routine.<\/strong>\r\n<strong>mserver='localhost'\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# Mysql server being backed up.<\/strong>\r\n<strong>bakname=mysql_$dateformat.bak.db.sql\u00a0\u00a0 \u00a0# Actual backup name.<\/strong>\r\n<strong>archname=mysql_$dateformat.tgz\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# Archive name that will be created.<\/strong>\r\n<strong>logpath=$basefold\/\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# Log file path.<\/strong>\r\n<strong>logfile=mysql_backup_LOGS_$dateformat.txt\u00a0\u00a0 \u00a0# Log file name.<\/strong>\r\n<strong>logging=$parentfold\/$logfile\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# Complete log path.<\/strong>\r\n<strong>returns=''\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# Simple line return for the logs.<\/strong>\r\n<strong>mysqlv=$(mysql -V)\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# MySQL version<\/strong>\r\n<strong>uname=$(uname -a)\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# Kernel version<\/strong>\r\n<strong>lsb=$(lsb_release -d | cut -f2)\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# OS version<\/strong>\r\n\r\n<strong>## INFO ##<\/strong>\r\n\r\n<strong>echo \"========================================================<\/strong>\r\n<strong>MySql Backup Summary - $(date +%d-%m-%Y)<\/strong>\r\n<strong>========================================================<\/strong>\r\n<strong>$returns\" &gt; $logging<\/strong>\r\n\r\n<strong># Declaring mysql server version.<\/strong>\r\n<strong>echo \"MySQL version:<\/strong>\r\n<strong>---------------<\/strong>\r\n<strong>$mysqlv<\/strong>\r\n<strong>$returns\" &gt;&gt; $logging<\/strong>\r\n\r\n<strong># Declaring Operating System version.<\/strong>\r\n<strong>echo \"Operating system version:<\/strong>\r\n<strong>--------------------------<\/strong>\r\n<strong>$lsb<\/strong>\r\n<strong>$returns<\/strong>\r\n<strong>Kernel version:<\/strong>\r\n<strong>----------------<\/strong>\r\n<strong>$uname<\/strong>\r\n<strong>$returns\" &gt;&gt; $logging<\/strong>\r\n\r\n<strong>echo \"** The backup job started on: $datebegin.<\/strong>\r\n<strong>$returns<\/strong>\r\n<strong>* Initializing...<\/strong>\r\n<strong>\" &gt;&gt; $logging<\/strong>\r\n\r\n<strong>## LOCAL WORKING FOLDER ##<\/strong>\r\n\r\n<strong># Creating the backup structure.<\/strong>\r\n<strong>echo '* Creating the temporary directory structure for the backup...' &gt;&gt; $logging<\/strong>\r\n<strong>if [ ! -d \"$remotefold\" ]; then<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 mkdir -p $remotefold<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 echo \" - Successfully created the local working directory under $remotefold.\" &gt;&gt; $logging<\/strong>\r\n<strong>else<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 echo \" - Directory $remotefold exist, attempting to continue.\" &gt;&gt; $logging<\/strong>\r\n<strong>fi<\/strong>\r\n\r\n<strong>## NETWORKING ##<\/strong>\r\n\r\n<strong># Mounting the network folder where the backup will be sent.<\/strong>\r\n<strong>echo $returns &gt;&gt; $logging<\/strong>\r\n<strong>echo '* Attempting to Mount the Network Backup Folder...' &gt;&gt; $logging<\/strong>\r\n<strong>mount -t cifs $pathfold $remotefold -o user='USERNAME',pass='PASSWORD',domain='WORKGROUP' &gt;&gt; $logging<\/strong>\r\n\r\n<strong>if [ $? -eq 0 ]; then<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 echo \" - Remote path $pathfold successfully mounted.\" &gt;&gt; $logging<\/strong>\r\n<strong>else<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 echo \" - Error encountered while mounting remote path $pathfold.\" &gt;&gt; $logging<\/strong>\r\n\r\n<strong># Creating a subfolder with the date as name on the remote foler.<\/strong>\r\n<strong>cd $remotefold<\/strong>\r\n<strong>#mkdir $dateformat<\/strong>\r\n\r\n<strong>if [ ! -d \"$dateformat\" ]; then<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 mkdir $dateformat<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 echo \" - Successfully created a subfolder called $dateformat in the remote folder\"\u00a0 &gt;&gt; $logging<\/strong>\r\n<strong>else<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 echo \" - Could not create a sub-directory called $dateformat as it already exists, continuing....\" &gt;&gt; $logging<\/strong>\r\n<strong>fi<\/strong>\r\n\r\n<strong>## MYSQL Backup ##<\/strong>\r\n\r\n<strong># Backup command<\/strong>\r\n<strong>echo $returns &gt;&gt; $logging<\/strong>\r\n<strong>echo '* Taking the backup of ALL databases...' &gt;&gt; $logging<\/strong>\r\n<strong>cd $basefold<\/strong>\r\n<strong>mysqldump -u $muser -h $mserver --password=\"$mpass\" --all-databases &gt; $bakname<\/strong>\r\n\r\n<strong>if [ $? -eq 0 ]; then<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 echo \" - Successful dump of ALL databases in $basefold as $bakname.\" &gt;&gt; $logging<\/strong>\r\n<strong>else<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 echo \" - Mysqldump task encountered an error, please review your server logs.\" &gt;&gt; $logging<\/strong>\r\n<strong>fi<\/strong>\r\n\r\n<strong>rawsize=$(du -sh $bakname | cut -f1)<\/strong>\r\n\r\n<strong>## COMPRESSION ##<\/strong>\r\n\r\n<strong># Compressing the backup into a tar.gz archive<\/strong>\r\n<strong>echo $returns &gt;&gt; $logging<\/strong>\r\n<strong>echo '* Compressing the backup into a .tgz archive...' &gt;&gt; $logging<\/strong>\r\n<strong>tar cfz $archname $bakname &gt; \/dev\/null 2&gt;&amp;1<\/strong>\r\n\r\n<strong>if [ $? -eq 0 ]; then<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 echo \" - Successfully compressed the backup as $archname.\" &gt;&gt; $logging<\/strong>\r\n<strong>else<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 echo \" - Compression task encountered an error, please review your server logs.\" &gt;&gt; $logging<\/strong>\r\n<strong>fi<\/strong>\r\n\r\n<strong># Calculating the size of the backup once compressed.<\/strong>\r\n<strong>compsize=$(du -sh $archname | cut -f1)<\/strong>\r\n\r\n<strong>## REMOTE COPY ##<\/strong>\r\n\r\n<strong># Copying the MySql backup archive from the local backup folder to the remote backup folder.<\/strong>\r\n<strong>echo $returns &gt;&gt; $logging<\/strong>\r\n<strong>echo \"* Copying the Backup to the Network folder...\" &gt;&gt; $logging<\/strong>\r\n<strong>cd $basefold<\/strong>\r\n<strong>cp $archname $remotefold\/$dateformat<\/strong>\r\n<strong>cd ~<\/strong>\r\n\r\n<strong>if [ $? -eq 0 ]; then<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 echo \" - Backup successfully transfered to $pathfold\/$dateformat.\" &gt;&gt; $logging<\/strong>\r\n<strong>else<\/strong>\r\n<strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 echo \" - Error encountered while transfering the backup, review your server logs.\" &gt;&gt; $logging<\/strong>\r\n<strong>fi<\/strong>\r\n\r\n<strong>## VERIFICATION ##<\/strong>\r\n\r\n<strong># Checking that the MySql service is still running.<\/strong>\r\n<strong>chkserv=$(ps aux | grep \/usr\/sbin\/mysqld | grep -v grep)<\/strong>\r\n<strong>echo \"<\/strong>\r\n<strong>* Querying the MySql service to ensure it is running...<\/strong>\r\n<strong>$chkserv<\/strong>\r\n<strong>$returns\" &gt;&gt; $logging<\/strong>\r\n\r\n<strong>echo \"Native database(s) size: $rawsize<\/strong>\r\n<strong>Compressed database(s) size: $compsize<\/strong>\r\n<strong>$returns\" &gt;&gt; $logging<\/strong>\r\n\r\n<strong># Ending\u00a0 stopwatch, calculating time difference and announcing it.<\/strong>\r\n<strong>dateend=$(date)<\/strong>\r\n<strong>echo \"** The backup job ended on: $dateend.<\/strong>\r\n<strong>$returns\" &gt;&gt; $logging<\/strong>\r\n\r\n<strong>endjob=$(date +%s)<\/strong>\r\n<strong>elapsed=$(( $endjob - $beginjob ))<\/strong>\r\n<strong>hours=$(( $elapsed \/ 3600 ))<\/strong>\r\n<strong>elapsed=$(( $elapsed - $hours * 3600 ))<\/strong>\r\n<strong>minutes=$(( $elapsed \/ 60 ))<\/strong>\r\n<strong>seconds=$(( $elapsed - $minutes * 60 ))<\/strong>\r\n\r\n<strong>echo \"The backup routine took: $hours hours $minutes minutes $seconds seconds to complete.<\/strong>\r\n<strong>$returns\" &gt;&gt; $logging<\/strong>\r\n\r\n<strong>wpath=$(echo $pathfold | sed -e 's\/\\\/\/\\\\\/g')<\/strong>\r\n<strong>echo \"A copy of the present log can be found along with the backup at:<\/strong>\r\n<strong>Linux:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 smb:$pathfold<\/strong>\r\n<strong>Windows:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $wpath\" &gt;&gt; $logging<\/strong>\r\n<strong>echo \"$returns<\/strong>\r\n<strong>$returns<\/strong>\r\n<strong>Backup routine completed.<\/strong>\r\n<strong>\" &gt;&gt; $logging<\/strong>\r\n\r\n<strong>## MAILING ##<\/strong>\r\n\r\n<strong># Once completed, the log can be emailed easily by installing the sendEmail package. Comment out the next line<\/strong>\r\n<strong># if you do not want to use sendEmail or change information accordingly.<\/strong>\r\n<strong>esender=mysql-backups@testdomain.com<\/strong>\r\n<strong>erecipient=admin@testdomain.com<\/strong>\r\n<strong>esubject='MySQL backup logs'<\/strong>\r\n<strong>ebody='This is the Backup Log of the MySQL server. Please review the attached document for detailed information.'<\/strong>\r\n\r\n<strong>sendemail -q -f $esender -t $erecipient -u $esubject -m $ebody -a $logging<\/strong>\r\n\r\n<strong># Copying the logs over the network folder.<\/strong>\r\n<strong>cp $logging $remotefold\/$dateformat<\/strong>\r\n\r\n<strong># Unmount the remote backup folder.<\/strong>\r\n<strong>umount $remotefold<\/strong>\r\n\r\n<strong>## CLEANING AND EXITING ##<\/strong>\r\n\r\n<strong># Cleaning the server of the backup files\/folders we created.<\/strong>\r\n<strong>rm -rf $basefold<\/strong>\r\n<strong>rm $logging<\/strong>\r\n\r\n<strong>exit<\/strong><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>#!\/bin\/bash # This script creates a copy of the \/opt\/zimbra folder and then compress # it localy before sending it over the nework so make sure there is enough space # left on your local hard drive ($parentfold variable). # A samba share is used in this script, fill the ## NETWORKING ## section # [&#8230;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2218"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2218"}],"version-history":[{"count":3,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2218\/revisions"}],"predecessor-version":[{"id":2220,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2218\/revisions\/2220"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2218"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2218"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2218"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}