Zero logo

MySQL Auto DB Backup

The Uniform Server XII module ZeroXII_mysql_autoback_1_0_0 allows you to select MySQL databases to automatically backup. These backups are archived in either or both 7z and zip archive formats, each archive is uniquely identified by adding a time stamp. You can select one of two predefined time stamp formats. In addition you have options to FTP archives for external storage and provide e-mail notification. The e-mail notification is a simple page which includes the error log. Archives on a host PC are deleted after a pre-defined number of days, number of days is a selectable option.

Install module (plugin)

Install The Uniform Server MySQL Auto DB Backup plugin as follows:

  1. Download ZeroXII_mysql_autoback_1_0_0.exe module (plugin)
  2. Copy ZeroXII_mysql_autoback_1_0_0.exe module (plugin) to folder UniServerZ
  3. Double click on the downloaded module
  4. This starts the extraction process
  5. If requested allow overwriting of existing files.
  6. If you wish to save space delete file ZeroXII_mysql_autoback_1_0_0.exe
  • Folder UniServerZ now contains two new files and one new folder:
    • UniDbBackup.bat - Starts the MySQL Auto DB Backup user interface
    • ZeroXII_mysql_autoback_1_0_0_read_me.txt - Contains additional informs
    • db_auto_backup - Folder contains backups and log files.
  • Folder UniServerZ\utils now contains three new files and one folder:
    • start_task_schedule_w7_w8.bat - Short cut to run Windows Task sheduler
    • UniDbBackup.exe - Uniform Server MySQL Auto DB Backup application
    • us_7za.exe - Third party application. Renamed 7za.exe to us_7za.exe prevents Win8 clashes.
    • UniDbBackupConfig - Folder for configuration and tracking
  • Folder UniServerZ\utils\UniDbBackupConfig - Configuration and tracking:
    • us_db_backup_config.ini - Configuration file for MySQL Auto DB Backup
    • us_db_ref_list.txt - Tracking, full list of previous DBs, automatically created
    • us_db_to_exclude.txt - Tracking, DBs to exclude, automatically created

Warning

The Uniform Server MySQL Auto DB Backup application and UniController are independent applications this means there is no interaction between the two.

  • Very important.
    • If running from Task Scheduler disable the UniServer_Auto_Backup task or ensure you make changes before the task is timed to run.
  • Databases added or deleted while running the "UniServer Auto Backup" user interface.
    • To allow new change to be picked up ensure you restart the "UniServer Auto Backup" user interface.

Application start up parameters

The MySQL Auto DB Backup application UniDbBackup.exe is started in one of two modes, these modes are selected by passing a parameter to it at start-up:

  • No Parameter
    • Starting the application with no parameters for example double clicking on UniDbBackup.bat or UniDbBackup.exe opens the user configuration window.
    • The user configuration window allows you to configure the application and optionally run a database backup manually.
  • Parameter auto
    • Starting the application with parameter set to auto for example using Task Scheduler, runs the application in background mode.
    • Before running in this mode ensure you have manually run the backup application to confirm correct configuration.

User configuration window

Start the application by double clicking on file UniServerZ\UniMysqBackup.bat or UniServerZ\utils\UniDbBackup.exe this opens the user interface window.

Note: If Databases to archive and Databases to exclude are blank the MySQL server is not running. Start the MySQL server and restart this application.

  • Databases to archive
    • Displays all databases that will be archived. To exclude a database click its name and click the > button.
  • Databases to exclude
    • Displays all databases that will be excluded. To include a database click its name and click the < button.
  • Select Time Stamp Format
    • Select a time stamp (A or B) you prefer this will be added to all file names and used in log files.
  • Enable/Disable Archive Type
    • With both options unchecked only raw sql backup files are created, these are not compressed.
    • With either option selected all sql files are archived in the selected format and then deleted.
    • Both options selected creates 7z and zip archives. Again all sql files are deleted.
  • Keep Each Backup Archive for
    • Files are retained for the number of days specified from their creation date. After this date they are deleted.
Main MySQL Auto back window

With the above configuration complete you can click Run DB Backup this will create archives as appropriate. These along with the log files are located in folder UniServerZ\db_auto_backup. If you have configured FTP and E-mail notification they will have been executed during the backup process, for details refer to appropriate sections below. Note: This manual backup method is fully portable.

Note 1: Preferably use the 7z archive option, it has better compression.
Note 2: If the database is large it will take a while to complete, before closing the application wait for the green progress bar to stop.

Background design notes:

Two files us_db_ref_list.txt and us_db_to_exclude.txt are automatically generated. They track the previous and current state of databases contained in the MySQL (Mariadb) server.

If the files do not exist they are created, us_db_ref_list.txt is populated with all current databases that are achievable and us_db_to_exclude.txt is created containing zero files.

A user assigns databases to exclude these are saved to file us_db_ref_list.txt and the internal display lists, databases to backup and exclude are updated.

Server databases added or deleted are detected next time Uniform Server Auto DB backup is run. The current list of databases is compared to those contained in file us_db_ref_list.txt a difference signals a changed. The file us_db_ref_list.txt is updated to contain the current list of databases, any databases missing in file us_db_to_exclude.txt are deleted and the file updated. Both display lists are updated to reflect the new state.

FTP configuration

In the event of a local disk crash, a database backup created locally is of little use. Ideally after creating a backup, copy it to another PC for safe retrieval at a latter date. Uniform Server Auto DB Backup provides an option to FTP archives to another machines automatically. These archives are transferred as soon as they are created.

The following section assumes you have either an FTP account with a service provider or running an FTP server on another machine within your network. In either case, configuring the FTP options is similar as explained below:

Use FTP check-box:
Checked enables editing FTP parameters and informs the application to use FTP. Unchecked disables FTP and menu parameter editing.

  • Server address:
    • Enter the FTP server name for example ftp.example.com or server IP address.
  • User Name:
    • Enter user FTP account name for example: us_user
  • Password:
    • Enter user FTP account password for example: us_user123
  • Directory/Path
    • Enter folder name on the FTP server. Use / for top-level. If a specific folder, supply its full path for example folder all_backups in folder db_backups use: /db_backups/all_backups
  • Port
    • The standard port is 21. Valid values are 1-65535
 MySQL Auto back FTP window

Configuration parameters are saved either when the FTP test button is clicked or when the menu window is closed.

FTP Test button: This button allows you to test the FTP configuration. When clicked two files test_7z.txt and test_zip.txt are created in folder db_auto_backup. These are FTP'ed to the FTP server and deleted. Confirm these files have been transferred to your FTP server, correct any errors.

Note: Uniform Server provides FTP modules ZeroXII_filezilla_server_1_0_0 (for Win 7 and Win 8) and ZeroXII_filezilla_server_xp_1_0_0 (for Windows xp). These are portable versions of the FileZilla server, however if you wish, they can optionally be run as a service. They allow you to quickly set-up a FTP server on another machine to provide external backups of your database archives. For details see Portable FileZilla Server page.

E-Mail Notification configuration

Uniform Server Auto DB Backup for convenience provides an option to E-mail a notification to a designated e-mail address. The E-Mail notification contains a time stamp in the subject title along with the error log file content. This information will allow you to quickly decide if any action is required should the backup fail.

Use E-Mail notification check-box:
Checked enables editing e-mail parameters and informs the application to use E-Mail notification, unchecked disables E-Mail notification and menu parameter editing.

Use SMTP authentication check-box:
Checked enables editing authentication parameters and informs the application to use SMTP authentication, unchecked disables SMTP authentication and menu parameter editing.

  • SMTP Server:
    • Enter your ISPs email server SMTP address for example: smtp.tiscali.co.uk
  • From:
    • Enter your ISP email address for example: your.name@tiscali.co.uk
  • To:
    • If sending to yourself enter your ISP email address for example: your.name@tiscali.co.uk alternatively use another address you own.
Generally when using your service provider for e-mail you do not required authentication. However if this is a requirement enter details as required.
  • User:
    • Enter user name required by your ISP for authentication.
  • Password:
    • Enter password required by your ISP for authentication.
 MySQL Auto back mail notification

Configuration parameters are saved either when the E-Mail test button is clicked or when the menu window is closed.

E-Mail Test button: This button allows you to test the E-Mail notification configuration. When clicked an e-mail is sent to the e-mail address configured.
The e-mail sent will have a subject titled UniServer Auto DB Backup followed by a time stamp.

Note: You can change the text "UniServer Auto DB Backup" by editing the configuration file us_db_backup_config.ini The test e-mail contains the following content

  • This test E-Mail confirms UniServer Zero XII E-Mail notification is correctly configured. More content.....

Confirm you received the test e-mail.

Testing

Before proceeding to auto backup configuration ensure backups are created and you can restore from these backups.

After configuring FTP and E-mail notification perform a manual database backup by clicking Run DB Backup button ensure the following:

  • Database archives are created in folder db_auto_backup.
  • The database archive files have been FTP'ed to your FTP server
  • An e-mail notification was received by checking your e-mail.
  • Finally and most important use the backups to restore databases to confirm correct operation.

Auto DB backup configuration

Automatic back up is performed using an existing Windows service known as Task Scheduler. This provides a more consistent and reliable implementation and is compatible across all Windows versions. Note using Task Scheduler the servers are no longer portable because changes (entries) are made to the Windows registry.

The Uniform Server MySQL Auto DB Backup application is started with parameter auto which runs the application in the background. This parameter is specified when configuring task Scheduler. The following is a text summary of the steps required to configure Task scheduler. You may prefer a step-by step guide including images see Create Scheduled Task page for details.

Note: Sections "Delete a Task" and "Quick Test" are included for completeness

-------------
Create a Task
-------------

1) Start Task Scheduler: Navigate to folder C:\UniServerZ\utils and double click on start_task_schedule_w7_w8.bat

2) Task Scheduler:
   a) Click "Create Basic Task" opens the Create Task window.
   b) Enter a Name in the Name field. Use "UniServer_Auto_Backup" without the quotes
   c) Enter a Description in the Description field. Use "Uniform Server MySQL automatic backup" without the quotes
   d) Click "Next" button

3) Create Basic Task Wizard - Task Trigger:
   a) Select one of the options: "Daily", "Weekly" or "Monthly" For this example select "Daily" for a daily backup
   b) Click "Next" button.

4) Create Basic Task Wizard - Daily:
   a) Set the starting date, start time e.g 28/08/2014 20:00:00 
   b) Set "Recur every 1 days" makes backup every day
   c) Click "Next" button.

5) Create Basic Task Wizard - Action
   a) Select Action to perform, click "Start a program" radio button
   b) Click "Next" button.

6) Create Basic Task Wizard - Start a Program
   a) "Program/script" Click "Browse" button.
      Navigate to application UniDbBackup.exe for example  C:\UniServerZ\utils\UniDbBackup.exe 
      Click "Open" button.
   b) Add arguments: Enter "auto"  without the quotes
   c) Click "Next" button.

7) Create Basic Task Wizard - Summary
   a) Check Box "Open the Properties dialog for this when I click Finish" click to select.
   b) Click "Finish" button. New task is created and added to Windows schedule  

8) UniServer_Auto_Backup Properties(Local Computer):
   This displays task properties shown for completeness.
   a) Click "OK" button.
   Click File>Exit

-------------
Delete a Task
-------------
To remove a task, start Task Scheduler, select task from the library list and delete. The following details
how to remove the Uniform Server UniServer_Auto_Backup task:

9) Task Scheduler: - Delete task:
   Start Task Scheduler: Navigate to folder C:\UniServerZ\utils and double click on start_task_schedule_w7_w8.bat
   a) Left menu: Select "Task Scheduler Library"
   b) Center menu: Select "UniServer_Auto_Backup" ensure it is highlighted
   c) Right menu: Click "Delete" menu item.
   d) Pop-up displayed "Do you want to delete this task" click "Yes" button
   e) Click File>Exit

----------
Quick Test
----------
For testing there is no need to wait for the scheduled time you can instantly run the Task as follows:

10) Task Scheduler: - Run task:
    a) Start Task Scheduler: Navigate to folder C:\UniServerZ\utils and double click on start_task_schedule_w7_w8.bat
    b) Left menu: Select "Task Scheduler Library"
    c) Centre menu: Select "UniServer_Auto_Backup" ensure it is highlighted
    d) Right menu: Click "Run" forces a run with the parameters set.
--oOo--