MySQL Backup Batch Script With 7Zip And Email Alert

This is yet another MySQL Backup batch script with the following features:

• Dumps each database into separate folder and script.
• Allows to set database(s) excluded from backup.
• Each script is compressed (7zip) and encrypted with a password.
• Keeps the last n copies of backups.
• Email alert on backup errors.

It requires the following tools:

• MySQL bin utilities - mysql.exe and mysqldump.exe.
• 7zip compression tool.
• blat emailer tool.

Note: It can backup any networked MySQL databases (either Windows or Linux platforms), as long as the MySQL bin utilities can access them.

This script is easy to configure and highly customizable. You only need to change the values in the "Begin/End Settings" sections. Please refer the script header remarks for more information and other options.

How to use:

1. Create a batch file named as "weizh-mysql-backup.bat" with the following contents:

@ECHO OFF

REM =====================================================================================
REM Copyright 2011 Weizh Chang
REM 
REM This program is free software: you can redistribute it and/or modify
REM it under the terms of the GNU General Public License as published by
REM the Free Software Foundation, either version 3 of the License, or
REM (at your option) any later version.
REM
REM This program is distributed in the hope that it will be useful,
REM but WITHOUT ANY WARRANTY; without even the implied warranty of
REM MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
REM GNU General Public License for more details.
REM
REM You should have received a copy of the GNU General Public License
REM along with this program.  If not, see <http://www.gnu.org/licenses/>.
REM =====================================================================================

REM =====================================================================================
REM Program    : Weizh MySQL Backup Batch Script
REM File       : weizh-mysql-backup.bat
REM Version    : 1.0.0.0
REM URL        : simpcode.blogspot.com
REM Description:
REM
REM Please change the settings in the Begin/End Settings sections below.
REM
REM This script requires:
REM - MySQL bin utilities - mysql.exe and mysqldump.exe.
REM - 7zip compression tool (www.7-zip.org).
REM - blat emailer tool (www.blat.net).
REM
REM Features:
REM - Dumps each database into separate folder and script.
REM - Allows to set database(s) excluded from backup.
REM - Each script is compressed (7zip) and encrypted with a password.
REM - Keeps the last n copies of backups.
REM - Email alert on backup errors.
REM =====================================================================================


@ECHO ON


REM ================ Begin MySQL Settings ====================

SET mySQLBinDir=C:\Program Files\MySQL\MySQL Server 5.0\bin\

SET host=192.168.100.2
SET uid=admin
SET pwd=adminPassw0rd
SET port=3306

SET backupDir=D:\MySQL_Backup\
SET backupPrefix=MySQL_3306_
SET backupCopy=3

REM ==== Set mysqldump switch (optional)
SET sqldumpSwitch=--single-transaction

REM ==== Set database excluded from backup (comma separated, without quotes and spaces)
SET excludeDb=information_schema,performance_schema,test

REM ==== Set the 7zip exe directory and password
SET zipDir=C:\Program Files\7-Zip\
SET zipPwd=yourZipPassw0rd

REM ================ End MySQL Settings ======================

REM ================ Begin Email Settings ====================

REM ==== Set all values to empty if not using email alert
SET emailServer=mail.example.com
SET emailPort=25
SET emailUid=admin@example.com
SET emailPwd=adminPassw0rd
SET sendFrom=admin@example.com
SET sendTry=5
SET subject=MySQL Backup Alert

REM ==== Set receiver email (comma separated, without quotes and spaces)
SET sendTo=alex@example.com

REM ==== Set the blat exe directory
SET emailDir=C:\blat\

REM ================ End Email Settings ======================


REM ================ Begin Backup ============================

ECHO Performing backup...

REM ==== Set date and time
For /F "tokens=2-4 delims=/ " %%a IN ('DATE /t') DO (SET mydate=%%c-%%a-%%b)
SET mytime=%TIME:~0,8%
SET mytime=%mytime: =0%

REM ==== Set command variable
SET doShowDb="%mySQLBinDir%mysql" -B -N -e "SHOW DATABASES" -h %host% -u %uid% -p%pwd% -P %port%
SET doDump="%mySQLBinDir%mysqldump" %sqldumpSwitch% -h %host% -u %uid% -p%pwd% -P %port%
SET doZip="%zipDir%7z.exe" a -t7z -mx9 -mhe -p%zipPwd%
SET doZipTest="%zipDir%7z.exe" -r -p%zipPwd% t

REM ==== Loop and backup each database
SETLOCAL ENABLEDELAYEDEXPANSION

SET errorDbTemp=

REM ==== Test the connection
!doShowDb!
IF !ERRORLEVEL! NEQ 0 (
    SET errorDbTemp=MySQL Connection Error
    GOTO EndLoop
)

FOR /F "tokens=*" %%G IN ('"!doShowDb!"') DO (
    
    SET exclude=0
    SET errorFound=0

    IF NOT "%excludeDb%"=="" (
        FOR %%S IN (%excludeDb%) DO (
            IF /I "%%S"=="%%G" SET exclude=1
        )
    )

    IF !exclude!==0 (
        REM ==== Set backup directory and file for this db
        IF NOT EXIST "%backupDir%%%G" MD "%backupDir%%%G"
        For /F "tokens=2-4 delims=/ " %%a IN ('DATE /t') DO (SET backupDate=%%c-%%a-%%b)
        SET backupTime=!TIME:~0,8!
        SET backupTime=!backupTime: =0!
        SET backupFile=%backupDir%%%G\%backupPrefix%%%G_!backupDate:-=!_!backupTime::=!

        REM ==== Perform MySQLDump
        !doDump! --databases %%G > "!backupFile!.sql"

        IF !ERRORLEVEL! NEQ 0 (SET errorFound=1)

        IF NOT EXIST "!backupFile!.sql" (
            SET errorFound=1
        ) ELSE (
            REM ==== Zip then delete the sql file
            !doZip! "!backupFile!.7z" "!backupFile!.sql"

            IF !ERRORLEVEL! NEQ 0 (SET errorFound=1)

            REM ==== Test the zip integrity
            !doZipTest! "!backupFile!.7z"

            IF !ERRORLEVEL! NEQ 0 (SET errorFound=1)
 
            DEL /Q "!backupFile!.sql"
        )

        IF !errorFound!==1 (
            REM ==== If errors, clean up and append the db name to a temp variable
            IF EXIST "!backupFile!.*" DEL /Q "!backupFile!.*"
            SET errorDbTemp=!errorDbTemp!%%G;
        ) ELSE (
            REM ==== Delete old zip copies, sort by file name descending
            IF EXIST "%backupDir%%%G\%backupPrefix%%%G_*.7z" (
                SET zipCount=0

                FOR /F "tokens=*" %%U in ('DIR /A:-D-H /O:-N /B "%backupDir%%%G\%backupPrefix%%%G_*.7z"') DO (
                    SET /A zipCount+=1
                    IF !zipCount! GTR %backupCopy% DEL /Q "%backupDir%%%G\%%U"
                )
            )
        )
    )
)

:EndLoop

ENDLOCAL & SET errorDb=%errorDbTemp%


IF NOT "%errorDb%"=="" GOTO SendError

GOTO Finish

REM ================ End Backup ==============================


REM ================ Begin Send Error Email Alert ============
:SendError

IF "%emailServer%"=="" (
    ECHO Backup error on %errorDb%
    GOTO Finish
)

ECHO Sending error email alert

REM ==== Set sendTo with surrounding quotes
SET sendTo="%sendTo%"

REM ==== Set email temp file
SET tmpErrorFile="%~dp0mysql_backup_error-%RANDOM%.tmp"

(
ECHO Dear Database Admin,
ECHO.
ECHO There were errors while performing the MySQL Backup on the following databases:
ECHO.
ECHO Date: %mydate%      Time: %mytime%
ECHO Host: %host%      Port: %port%
ECHO.
ECHO -----------------------------------------------------------------
ECHO %errorDb%
ECHO -----------------------------------------------------------------
ECHO.
ECHO.
ECHO Regards,
ECHO MySQL Server Admin
) >%tmpErrorFile%


REM ==== Send error email alert
"%emailDir%blat.exe" %tmpErrorFile% -server %emailServer%:%emailPort% -f %emailUid% -u %emailUid% -pw %emailPwd% -from %sendFrom% -to %sendTo% -subject "%subject%" -try %sendTry%

REM ==== Cleanup
IF EXIST %tmpErrorFile% DEL /Q %tmpErrorFile%

REM ================ End Send Error Email Alert ==============


:Finish
ECHO Backup process completed

2. Set values in the Begin/End MySQL Settings and Begin/End Email Settings sections. Put this file into any folder you like.

3. You may use the Windows Task Scheduler to run this script routinely or at anytime you prefer.

SQL Server 2008 R2 Express Installation And Setup

This post provides a basic guideline about installing SQL Server 2008 R2 Express together with the SQL Server Management Studio.

1. If you do not have Windows Power Shell installed, please install it from here.

• On that page, find the Download information > Windows Management Framework Core (WinRM 2.0 and Windows PowerShell 2.0), then select Download the Windows Management Framework Core for Windows XXX package now, whereby XXX is your Windows edition (thanks to this site for the link).
• Note: Power Shell is only needed by SQL Server Management Studio. You may skip this step if you do not want to install the studio.

2. Download and install "SQL Server 2008 R2 Express With Tools" from here.

• Note: To do a clean install, you may first want to remove any existing SQL Server 2008 (or R2) database and utilities from the Control Panel > Add/Remove Programs.

3. When prompted, select "New installation or add features to an existing installation". Follow the instructions.

• Instance Configuration > Named instance: You may want to rename it to "SQLEXPRESS2008R2" or just use the default.
• Server Configuration > Service Accounts: Enter any existing Windows account name and password which has sufficient rights (e.g. the admin account).
  
• Database Engine Configuration > Authentication mode: Select "Windows authentication mode" (this can be changed later).
• Restart the computer if needed.

4. Go to Start > Microsoft SQL Server 2008 R2 > Configuration Tools > SQL Server Configuration Manager:

• SQL Server Services > SQL Server: Make sure it is running.
• SQL Server Network Configuration > Protocols for xxxx: You can enable and set the remote network connection settings here.

5. Go to Start > Microsoft SQL Server 2008 R2 > SQL Server Management Studio > Connect to server:

• Server name: serverName\instanceName (e.g. myserver\SQLEXPRESS2008R2)
• Authentication: Windows Authentication

If everything is correct, you should be able to connect to the database now.


Setup Security and Authentication using SQL Server Management Studio

1. Enable "SQL Server Authentication Mode":

• In Object Explorer > Right-click the server to Properties > Security > Server authentication: Select "SQL Server and Windows Authentication mode".
• Once set, you may need to restart the service from "SQL Server Configuration Manager".

2. Enable "sa" login:

• By default the "sa" account is disabled. To enable:
  In Object Explorer > Security > Logins > sa > Right-click to Properties:
  • General: Set the password.
  • Status > Login: Select "Enabled".

3. Add new SQL Server authentication:

• In Object Explorer > Security > Logins > Right-click to New Login > Select "SQL Server authentication": From here, you can set the login name, password, default database and other information.

Additional Resources:
Introduction to SQL Server Express 2008 R2

Assemblies Merging With Post-build Event

ILMerge is a useful utility to merge multiple .NET assemblies into a single assembly. By using the "Post-build" event command line, we can automate the merging process when building our projects.

There is a better method by using MSBuild. Please refer Assemblies Merging With ILMerge and MSBuild (TODO).

For my projects, I use a ILMergeHelper batch file to run the ILMerge process. By using this script, all files inside the bin output are copied over to a sub folder "ReleaseMerged". All merging process are done within this sub folder, as such, nothing in the bin output folder will be affected.

Syntax:

ILMergeHelper.bat <targetDir> <targetFileName> <solutionName> <inputAssemblies> [cleanAssemblies]

Arguments:

• targetDir - The merged output directory.
• targetFileName - The merged output file name.
• solutionName - The project solution name.
• inputAssemblies - The assemblies to be merged. Must enclose with double-quotes, separate each assembly name with a space within the quotes. Supports wildcards.
• cleanAssemblies - Optional. The files to be deleted after the merging completed. If not set, will be same as inputAssemblies. Must enclose with double-quotes, separate each assembly name with a space within the quotes. Supports wildcards.

Note: Please see example below.

Setup ILMerge and ILMergeHelper (only set once):

1. Download and install the ILMerge utility.

• Note: You may copy the "ILMerge.exe" and put it into any custom folder, as long as you change to the correct path in your post-build command line.

2. Create a batch file named as "ILMergeHelper.bat" with the following contents:

@ECHO OFF

REM #############################################################################################################
REM #### ILMergeHelper.bat
REM #### A utility to perform ILMerge using the Post-build event from the Visual Studio project
REM #### Author: Weizh Chang
REM ####
REM #### Usage: ILMergeHelper.bat <targetDir> <targetFileName> <solutionName> <inputAssemblies> [cleanAssemblies]
REM ####
REM #### Example (set in Post-build event): 
REM #### ILMergeHelper.bat $(TargetDir) $(TargetFileName) $(SolutionName) "support*.dll" "support*.dll"
REM #############################################################################################################

@ECHO ON

REM #### Set variables from arguments

SET targetDir=%1
SET targetFileName=%2
SET solutionName=%3

SET inputAssemblies=%4
SET inputAssemblies=%inputAssemblies:"=%

SET cleanAssemblies=%5
IF (%cleanAssemblies%)==() SET cleanAssemblies="%inputAssemblies%"
SET cleanAssemblies=%cleanAssemblies:"=%

SET mergedDir=%targetDir%..\ReleaseMerged\

REM #### Set the merged file name
SET mergedFileName=%targetFileName%

REM #### Remove existing merged directory
IF EXIST %mergedDir% RD /S /Q %mergedDir%

REM #### Copy all contents from the build target folder to the merged folder
XCOPY %targetDir%* %mergedDir% /i /E /Y /Q
CD /D "%mergedDir%"

REM #### Perform ILMerge
%~dp0ILMerge /lib:%mergedDir% /wildcards /allowDup /ndebug /out:%mergedDir%%mergedFileName% %targetFileName% %inputAssemblies%

REM #### Delete all assemblies that have been merged
DEL /Q %cleanAssemblies%

3. Put this file into the same folder as ILMerge.exe (e.g. C:\Program Files\Microsoft\ILMerge\).

4. Open your project's Properties > Build Events > Post-build event, and enter the ILMergeHelper command. For example:

"C:\Program Files\Microsoft\ILMerge\ILMergeHelper.bat" $(TargetDir) $(TargetFileName) $(SolutionName) "support*.dll MySql*.dll" "support*.* MySql*.* test.xml"

Note:

• The above example merges all assemblies named support*.dll and MySql*.dll, then delete all files with names support*.*, MySql*.* and test.xml.
• This example uses macros $(TargetDir) and $(TargetFileName) as the merged output directory and file name. You may change them to other values you preferred.

5. Build the project. The merged assembly is created inside the "ReleaseMerged" folder.


Additional Resources:
Assemblies Merging With ILMerge and MSBuild (TODO)

MySQL Table Name Starts With 'test'

By default, the `mysql.db` table contains rows that permit access by any user to the `test` database and other databases with names that start with `test`.

To disable this, after a fresh MySQL installation, please run the following command:

DELETE FROM mysql.db WHERE Db LIKE 'test%';

Capture The DataGridView CheckBox Column Click Event

The .Net DataGridView CheckBox Column control can be a bit tricky to use. Recently I've encountered a problem where I need to do some calculations when a user checks or unchecks the CheckBox. However, the events triggered always seems to return the previous value. E.g. When the CheckBox is checked (true), the value is captured as uncheck (false), and vice-versa.

After some digging around, I found that using both "CellContentClick" and "CellContentDoubleClick" events can do the trick.

For example, we have a grid named "myGrid" with columns "colTick" (the CheckBox) and "colAmt" (an amount field). Bind this grid to the following 2 events:

private void myGrid_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
    GetTotalSelection(e.ColumnIndex, e.RowIndex);
}

private void myGrid_CellContentDoubleClick(object sender, DataGridViewCellEventArgs e)
{
    GetTotalSelection(e.ColumnIndex, e.RowIndex);
}

Then we process the checked row values in the "GetTotalSelection" method, accumulating the total selected amount into a private variable "_totalSelAmt":

private decimal _totalSelAmt = 0;
....

/// <summary>
/// Calculates total amount selected.
/// </summary>
private void GetTotalSelection(int colIndex, int rowIndex)
{
    if (colIndex < 0 || rowIndex < 0)
        return;

    if (myGrid.Columns[colIndex].Name != colTick.Name)
        return;

    myGrid.EndEdit();

    bool tick      = Convert.ToBoolean(myGrid[colIndex, rowIndex].Value);     // Gets the current row check value.
    decimal selAmt = Convert.ToDecimal(myGrid[colAmt.Name, rowIndex].Value);  // Gets the current row amount.

    if (tick)
        _totalSelAmt += selAmt;
    else
        _totalSelAmt -= selAmt;
}

Now, every time a user checks or unchecks the CheckBox, the variable "_totalSelAmt" will be updated with the latest total selected amount.

Online Syntax Highlighting Using ToHtml.com

Just want to share a source code syntax highlighting tool I discovered recently that is easy to use and supports many different languages, even the rare ones.

The steps are rather straight forward. Just go to ToHtml.com, paste your code inside the "Source Code" panel. Then choose your language type and hit "Highlight". In a short while your code will be formatted into the "HTML code" panel, which you can just copy and paste directly into your web site or blog. In addition, you can also select the style you want, and preview the formatted output.

There is no worries on any special characters like > or <, it will take care for you as well.

Originally I was trying to use SyntaxHighlighter, but after spending much time troubleshooting, it just won't work on this blog. I'm not sure where I did wrong, maybe I will give it another shot when I have more time to spare. However, for now, I think this online tool already can fulfill my needs. Cheers :)

Update MySQL Table Field With Value Derived From Another Table

If you need to update certain field in a table with value derived from another table (e.g. performing a sum), you could do this using a JOIN statement in the following way (which I referred from this post):

UPDATE table1 a
JOIN
   ( SELECT batch_no, SUM(sale_amt) AS sum_sale_amt
     FROM table2
     WHERE source = 'SALE'
     GROUP BY batch_no
   ) b ON 
   b.batch_no = a.batch_no
SET a.total_sale_amt = b.sum_sale_amt
WHERE a.cancel <> 1;

In the above example, we sum the `sale_amt` from `table2` where the `source` is 'SALE', grouped by `batch_no`, into an alias `sum_sale_amt`. Then we update this sum into `table1`'s field `total_sale_amt` based on the `batch_no`. This update only applies to `table1` records which is not cancelled.

This query can be run multiple-times to update/fix any inconsistency error when saving from the derived values.