Quick and Dirty Backup Script for MySQL on Windows

The following batch file is a simple backup script for MySQL. It exports all databases into a temporary SQL dump file and compresses it using 7Zip into a file with the current date as its name. It also automatically deletes backups that are older than 30 days.

Here is how to get started:

  1. Copy the batch file to a directory of your choice
    i.e. C:/Program Files/Scripts
  2. Place 7z.exe into the same directory or add it to the PATH environment variable
  3. Replace the placeholders YOUR_BACKUP_PATH and YOUR_ROOT_PASSWORD
  4. Add a scheduled task in Windows to run the batch file once a day

Feel free to modify the script to use a different packer tool or different output file names.

  1. @ECHO OFF
  2.  
  3. set backuppath="YOUR_BACKUP_PATH"
  4. set password="YOUR_ROOT_PASSWORD"
  5. set daystokeep=30
  6.  
  7. REM get current date in YYYYMMDD format
  8. for /F "tokens=1-4 delims=/ " %%I in ('date /t') do set curdate=%%L%%J%%K
  9.  
  10. REM export database into temporary file
  11. mysqldump -u root -p%password% –all-databases > %backuppath%\backup.sql
  12.  
  13. REM compress and delete temporary file
  14. start "Compressing Backup…" /min /wait 7z.exe a -bd -y -tzip -mx9 "%backuppath%\%curdate%.zip" "%backuppath%\backup.sql"
  15. del %backuppath%\backup.sql
  16.  
  17. REM delete older backups
  18. forfiles /P %backuppath% /S /M *.zip /D -%daystokeep% /C "cmd /C del /Q @PATH"

Glassy Vista Aero Forms in .NET

This article is part of a backup. It was originally posted on Sunday, March 25. 2007.

A search on Google will return quite a lot of more or less complicated and complete articles on how to implement Windows Vista’s Aero glass look in your own .NET applications. This posting will describe how to achieve this effect with as little code as possible, and how to get a bit more out of it by processing user input accordingly.

The Basics

Windows Vista provides two mechanisms to to create Aero glass in application forms. The first mechanism is to extend the glass areas from the form’s borders, which are already glassy, into the client area of your form. The API for this functionality is declared in the DwmApi.h header file of the Windows SDK:

  • DwmIsCompositionEnabled is a function to determine whether the operating system has Desktop Window Manager (DWM) composition enabled (see MSDN).
  • DwmExtendFrameIntoClientArea performs the actual work of extending the window frame into the client area (see MSDN).

The second mechanism for Aero glass is the DwmEnableBlurBehindWindow function, which will not be covered in this article. It allows for somewhat better control over blur effects in Windows forms, including the ability to define arbitrary glass regions.

Imports

Before the DWM functionality can be used in any .NET application, we have to import the appropriate APIs. All of the following sample code has been written in C#, but implementations will be very similar in Visual Basic and Managed C++.

Rather than adding the imports somewhere in your program code, it is good practice to isolate imported API functions, type definitions and contants in separate class files. This will allow you to keep things clean and reuse them in other projects. I recommend creating one class per header file, but you are free to choose a different aproach.

The first parameter of the DwmExtendFrameIntoClientArea function is a pointer to a MARGIN struct, which is defined in UxTheme.h of the Windows SDK. Its values hold the individual dimensions for the left, right, top and bottom margin:

  1. public class UxTheme
  2. {
  3.     [StructLayout(LayoutKind.Sequential)]
  4.     public struct MARGINS
  5.     {
  6.         public int cxLeftWidth;
  7.         public int cxRightWidth;
  8.         public int cyTopHeight;
  9.         public int cyBottomHeight;
  10.     }
  11. }

Importing the two functions from the DWM API is just as easy:

  1. public class DwmApi
  2. {
  3.     [DllImport("dwmapi.dll", PreserveSig = false)]
  4.     public static extern void DwmExtendFrameIntoClientArea (
  5.             IntPtr hwnd, ref UxTheme.MARGINS margins);
  6.  
  7.     [DllImport("dwmapi.dll", PreserveSig = false)]
  8.     public static extern bool DwmIsCompositionEnabled ();
  9. }

Make sure to include using clauses for the System and System.Runtime.InteropServices namespaces, which provide the definitions for other required types and attributes.

Implementation

Extending Aero glass into the client area of the form is pretty straightforward and consists of two steps. First the application needs to check whether the Aero glass feature is available on the system. If it is available, it can then simply pass the desired dimensions to the DWM API.

In the following example, we will override the form’s OnLoad method to set up the effect on program start. Windows forms in .NET can have a non-zero Padding value to shrink the form’s client area. The example will use the DWM API to ensure that the padding created around the client area will be rendered with Aero glass instead of the form’s original background color.

  1. protected override void OnLoad (EventArgs e)
  2. {
  3.     base.OnLoad(e);
  4.  
  5.     if (!this.DesignMode && DwmApi.DwmIsCompositionEnabled())
  6.     {
  7.         UxTheme.MARGINS m = new UxTheme.MARGINS();
  8.  
  9.         m.cxLeftWidth = this.Padding.Left;
  10.         m.cxRightWidth = this.Padding.Right;
  11.         m.cyTopHeight = this.Padding.Top;
  12.         m.cyBottomHeight = this.Padding.Bottom;
  13.  
  14.         DwmApi.DwmExtendFrameIntoClientArea(this.Handle, ref m);
  15.  
  16.         this.BackColor = Color.Black;
  17.     }
  18. }

The way Vista Aero works is that it will only paint the glass look on pixels that are set to black color. In order to avoid custom painting methods (as suggested in several other articles), we simply set the form’s background color to black in the last line of the function above. Rest assured that the black color will never actually be visible.

And this is really all that is needed to create a simple Aero glass effect. The following paragraphs will cover some more advanced topics and explain how to make things look and feel a little better.

Controls & Drawing on Glass

After experimenting with the code from the previous paragraph, you will probably have noticed that there are some subtle, yet unacceptable rendering problems when placing Windows Forms controls on the newly created glass regions. The text of buttons, labels, textboxes and other controls is now rendered with a glass effect, even if you host them on top of other containers, such as panels or page controls, or if you use different front colors.

Unfortunately, there is no simple fix to this problem, as the text drawing routines used by the built-in controls do not use alpha channels when rendering themselves. It is safe to say that most standard controls cannot be used on Aero glass. If you want to have controls on the glass regions, you have to draw them yourself using the drawing functions in GDI+. One exception would be controls that use 32-bit images with alpha channels, or images without black color, such as in image buttons or picture boxes.

Windows provides a set of handy drawing functions, such as DrawThemeTextEx for shadowed text on glass, to simplify the implementation of custom drawing routines. The exact details of drawing custom controls is not part of this article. Please refer to the web links at the end of this page for more details.

Processing User Input

The extended window frame region does not automatically become part of the form’s caption bar and will therefore not allow users to click and drag the form around. This problem can easily be solved by adding a little bit of custom code.

The trick is to capture WM_NCHITTEST messages, which check whether the mouse pointer should be captured by the receiving form, and then to pretend that the user clicked on the form’s caption bar while, in fact, the click hit the extended window frame.

The actual implementation requires three constants declared in WinUser.h:

  1. public class WinUser
  2. {
  3.     public const int WM_NCHITTEST = 0×0084;
  4.    
  5.     public const int HTCLIENT = 1;
  6.     public const int HTCAPTION = 2;
  7. }

Windows messages can be captured by overriding the form’s WndProc method. Once the mouse coordinates passed in the LParam parameter have been converted from screen coordinates into the form’s local client coordinate system, it is very easy to determine which part of the form received the click:

  1. protected override void WndProc (ref Message m)
  2. {
  3.     base.WndProc(ref m);
  4.  
  5.     if ( (m.Msg == WinUser.WM_NCHITTEST) &&
  6.          (m.Result.ToInt32() == WinUser.HTCLIENT) )
  7.     {
  8.         if (DwmApi.DwmIsCompositionEnabled())
  9.         {
  10.             int i = m.LParam.ToInt32();
  11.             Point p = this.PointToClient(
  12.                 new Point(i & 0xffff, i >> 16));
  13.  
  14.             if ( (p.X < this.Padding.Left) ||
  15.                  (p.X >= this.ClientSize.Width - this.Padding.Right) ||
  16.                  (p.Y < this.Padding.Top) ||
  17.                  (p.Y >= this.ClientSize.Height - this.Padding.Bottom) )
  18.             {
  19.                 m.Result = (IntPtr)WinUser.HTCAPTION;
  20.             }
  21.         }
  22.     }
  23. }

The form can now be dragged around on the screen, not only by clicking its caption bar, but also by clicking any of the extended Aero glass areas.

Other Considerations

The code shown above is a very simple demonstration of what is necessary to leverage the new Aero glass features of Windows Vista. While it works fine for demonstration purposes, you might want to keep in mind potential issues when running your software in the real world.

One less obvious thing to consider is the fact that users have the ability to turn the new DWM features on or off at any time, and your application should be able to handle this. Applications can listen to the new message WM_DWMCOMPOSITIONCHANGED in order to get notified about changes in the composion state.

Additional information can be found on MSDN.

Related Articles

Since Aero glass is one of the more popular Vista development topics, there already are a variety of related articles on the internet. Tim Sneath’s short introduction was probably one of the first working examples. Another introduction can be found on The Hackman’s blog. Of course, The Code Project has an article as well. Sample code for Visual Basic can be found in a posting on the AeroXperience forums. An excellent article for Managed C++ can be found on Kenny Kerr’s blog, which also covers the usage of the DwmEnableBlurBehindWindow function.

Microsoft has an introductory article on DWM including instructions on how to draw on Aero glass, as well as the Application Compatibility Cookbook for Vista. Another great resource for DWM related development is Greg Schlechter’s blog.

The problem of rendering built-in controls on Aero glass is explained in a posting on the MSDN forums. The Code Project has a very detailed article on custom drawing in Vista and another, not quite as useful article on text drawing in particular. Sample code for using DrawThemeTextEx in Visual Basic is available on the french website VBFrance.

Web Platform Installer with UrlScan breaks WHS Connector

When installing the Microsoft Web Platform installer on Windows Home Server and enabling the UrlScan feature during setup, parts of the Windows Home Server Connector will stop functioning properly. In particular, the Windows Home Server Console will be unable to retrieve client updates from the server and instead respond with a “404 – Resource not found” message on the setup screen. The reason for this is the default configuration of UrlScan, which blocks certain file extensions and special characters that are used in URL strings submitted by the Connector to the server.

Solution

To re-enable support for the WHS Connector, the filtering of backslashes and executable file extensions need to be turned off:

  1. Navigate to the UrlScan installation folder
    Normally this is in C:\WINDOWS\system32\inetsrv\urlscan\
  2. Open the file UrlScan.ini in your favorite text editor
  3. In the [DenyExtensions] section comment out the entry for .exe
  4. In the [DenyUrlSequences] section comment out the entry for the backslash
  5. Save all changes to the file.

Resetting privileges for MySQL root account

I played around with the HeidiSQL front-end for MySQL and somehow managed to remove all privileges from the database’s root account while editing other user accounts. Needless to say, none of the other accounts had access to the administrative internals of MySQL, so that I essentially threw away the master key to my database server.

Solution

Luckily, MySQL provides the option to bypass privileges for all user accounts, as is necessary in situations like these. Here are the steps to enable this option:

  1. Browse to the MySQL installation directory
    i.e. C:/Program Files/MySQL/MySQL Server 5.1
  2. Open the file my.ini in your favorite text editor
    Note: in earlier versions of MySQL this file is called my.cnf
  3. In the [mysqld] of this file add the following line:
    skip-grant-tables
  4. Restart the MySQL server, for example in the Service Manager in Windows

You should now be able to log into the MySQL server using any of your acounts, including the root account. Use your favorite tool to fix up the privileges table for root or add other user accounts as needed.

IMPORTANT: Don’t forget to take out the added line from my.ini when you are done fixing up the database and restart the service once again. Otherwise your database will grant access to anyone attempting to login, which is obviously a major security hole.

Outlook Cannot Connect to Outgoing (SMTP) E-mail Server Through Wireless

If your Outlook successfully sends emails from your home network, but fails to do so on certain wireless networks, this may be because the network port 25 is blocked by the internet provider you are using at the time. If you are seeing this problem at work, your company may allow sending emails only on the wired ethernet network, but prohibit it on the wireless network for security or monitoring reasons. In certain public wireless network, the use of port 25 may also be blocked to prevent abuse through spamming or other illegal activities. Furthermore, many internet server providers (ISP) may block access to the SMTP port, because they reserve it for mail servers to relay emails to each other and to block widespread worms and viruses.

Solution

Many email providers, such as Yahoo, Google and Hotmail provide port 587 (SMTP email submission) as an alternative port to send emails on. If you are having problems sending email through port 25, try changing it to port 587 with the following steps:

  1. Select Tools from the main menu
  2. Click on E-mail Accounts… or Account Settings… to open the Internet Accounts window
  3. Click on View or change existing e-mail accounts and click Next
  4. Select the email account you wish to change and click Change
  5. In the E-mail Accounts window click the More Settings… button
  6. Select the Advanced tab
  7. Change the setting Outgoing server (SMTP) from 25 to 587
  8. Click Ok to close the properties window
  9. Click Next and Finish to save your settings

Your email provider will likely require authentication for your outgoing email as well, so make sure that you setup Outlook with the correct user credentials for your email account.

Other Considerations

If your Outlook is still unable to send emails through port 587, contact your ISP or local network administrator to find out if another port is provided for sending emails

If available, you may also consider using Secure SMTP through SSL (port 465), the IMAP protocol (port 143) or the Secure IMAP protocol through TLS/SSL (port 993)