Skip to main content

MySQL Dump cronjob on linux server

Creating mysql dump for production databases is very important. In case of any failure or malefic attack on DB, The only way to restore to previous state is by having proper mysql dump file. Follows an example mysql dump shell script with auto naming with dumping time stamp. Its important to create the shell scripts in unix mode, if you create on a PC, It'll add some unwanted character encoding, and it'll lead to some erroneous response on the script.

#!/bin/bash
today=`date +%Y%m%d%H%M`
backup="cmtdbbackup-$today.sql"
mysqldump -u user -ppassword mydb > /apps/mysql_dump/$backup
Its important to create the shell script carefully. No spaces, unless its required. in between -u and user name, there is a space, but no space between -p and password. As per the usage of system, we can decide on which interval the cronjob could be set. normally the format of a crontab entry is like the following.

* * * * * /apps/scripts/scheduled_job.sh >> /dev/null 2>&1
 Here the first 5 portions are the timing interval markers (Minute (0-59), Hour (2-24), Day of month (1-31), Month (1-12, Jan, Feb, etc), Day of week (0-6) 0 = Sunday, 1 = Monday etc or Sun, Mon etc). next to those is the script to run. The '>> /dev/null 2>&1' part means to send any standard output to /dev/null (the linux trash can) and to redirect standard error (2) to the same place as the standard output (1). Basically it runs the command without any output to a terminal.

If you want to run the script for some specific times only, lets say you want to run it on 4 pm and 8 pm, the timers looks like the following

0 16,20 * * *

if you want to run the script only in week days, the timers will be look like the following.

0 16,20 * * 1,2,3,4,5

If you run more than one script, better give them as seperate jobs.
Labels:

Comments

  1. கவினன்[Kavinan]May 22, 2011 at 6:52 PM

    Add the following at the end for storing the dump as tar.gz

    tar -czvf backup.tar.gz backup.sql
    rm backup.sql

    ReplyDelete

Post a Comment

Popular posts from this blog

VBScript for URL link creation

We had a need to create URL link of our project on users's desktop using a script. First stage the users had enough rights on their PC's so we included the required icon file also with the script and passed the following VBScript to users to click on it to create the desktop icon link. ' Definie shell object Set objWSHShell = WScript.CreateObject("Wscript.Shell") strDesktop = objWSHShell.SpecialFolders("Desktop") ' Copy the icon file to windows Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFileCopy = objFSO.GetFile( objWSHShell.CurrentDirectory&"\icon.ico") objFileCopy.Copy ("C:\WINDOWS\system32\") ' Define link properties and create link strShortcutName = "Log In" ' Shortcut Name - Edit for the wanted name strShortcutPath = "http://thesystem.com/Login.aspx" ' Link URL - Edit to the wanted URL strIconPath = "C:\WINDOWS\syst
Post a Comment
Read more

Round the float

There are 3 types of rounding a float to integer is applicable in any language. we programmers will come across such situation in every project. the three types are, round to next bigger integer, with not considering the decimal value. round to the next smaller integer, with not considering the decimal value. round the float according to the value of decimal value, the real round of float number. The rounding methods depend on which scenario we need the round function. Follows the examples of php rounding methods. for the case 1, rounding to the next big integer, we use  ceil() function Example :  $rounded_value = ceil($floor_value); if rounded the following values, the results are like shown 2 = ceil(1.3) 2 = ceil(1.5) 2 = ceil(1.7) for the case 2, round to the next small integer, we could use floor() Example: $rounded_value = floor($floor_value); if rounded the following values, the results are like shown 1 = floor(1.3) 1 = floor(1.5) 1 = floor(1.7) for the case 3, round as
Post a Comment
Read more

VBScript for compressing files in a folder and send on FTP

This is a simple vbscript compression script. works flawlessly on text files even bigger than 15GB. We have a SSIS data generation package installed on our DB server which generates huge data files for data mining operations for current year, current month. The below scripts are doing the rest; compress the files and FTP to the data mining server location.   '====================================================== ' Function : Zip datamart CSV, CTL files to local server for archive purposes. '====================================================== Function WindowsZip(sFile, sZipFile, szPath, szDate)   Set oZipShell = CreateObject("WScript.Shell")    Set oZipFSO = CreateObject("Scripting.FileSystemObject")   Set LogFile = oZipFSO.CreateTextFile(szPath & "\Logs\log_" & szDate & ".log", true)   LogFile.WriteLine("=======================================")   LogFile.WriteLine(Now & " - Com
Post a Comment
Read more