Mindless Backup mySQL (OpenSource)

Set it and forget it

Mindless Backup runs as often as you like and pop-ups a simple Window that shows you how the backup is progressing, so that you know its working.   It also logs its actions in the Windows event log, so you can use the Event Viewer to find information about past runs, even configuring Mindless Backup to stay silent without a pop-up window.

Compression supports both Winzip and the standard zip program(s) included with Windows.  So, if you have a desktop or server edition of Windows, you won't need to buy anything to use Mindless Backup (which itself is free!)

locally or across a network.  For example, you can use Mindless Backup to backup your office files and database to your home office or other remote location, creating your own, personal cloud backup.

Download

Installation for database backup

These instructions walk you through installing the pre-requisites for Mindless Backup, configuring it to operate for your setup, and creating recurring backups.  If you don't want to perform database backups, you can skip the steps related to those.

For the purposes of this document let’s assume there are 2 servers,

dbserver is the database server (MySQL or MariadDB)
backupserver is the machine where the backups will be stored.

Step 1. Install MySQL server and MySQL utilities

Using MariaDB?  You also need to do this! (Yes, you need to install these tools even if you use MariaDB[1].   You can keep using MariaDB as your database server, but you'll need these tools to perform database backups).

Install the MySQL Server (Client only OK) and MySQL Utilities on dbserver.  The downloads you want are:

http://dev.mysql.com/downloads/mysql/

http://dev.mysql.com/downloads/utilities/

When setting up these tools, be sure to tell the setup program to add the tools to the path.

Step 2. Expand your path

Select System under the Control Panel, then advanced settings, environment variables (the last button window.

Then select “Path” from the list, press Edit and then (new windows) press Edit Text (do not “edit” or “browse”) and add “C:\Program Files\MySQL\MySQL Server 5.7\bin;C:\Program Files\Winzip” (or, wherever these paths are for you).

Step 3.  Create a backup user on dbserver

Using your favorite database management tool, create a user that will be used to perform the remote backup (let’s call this user RemoteBackupUser) and grant access to RemoteBackupUser from backupserver to the databases that you want to back up.  I found I needed these privileges for the backup user: SELECT, SHOW VIEW, EVENT, TRIGGER, RELOAD, REPLICATION CLIENT, SUPER.

For example, say you want to backup a database called mystore, you could

GRANT

Step 4. Store encrypted login information

We'll use the MySQL configuration editor to define login paths, which allow us to access the database without exposing the access credentials.

At a MySQL command prompt, execute the following command:

mysql_config_editor set --login-path=path_name  --host=dbserver --user="RemoteBackupUser" --port=3306 –password

where

• path_name is a string of your choosing, a name for the connection,
• dbserver is the name or IP address of your database server,
• RemoteBackupUser is the name of the user you created in Step 3, and
• 3306 is the database server port.  3306 is the default, you don't need to change it unless your server operates on a non-standard port.

This will prompt you for a password and you should enter the password you created at Step 3.  So, you won’t need to expose the password in batch files.

Step 5.  Store the Mindless Backup files

In order to automate the task you’ll need to create a short script which you can download here. Put the script in a location – I chose C:\Program Files\Mindless Backup (MySQL).

Edit the first two lines of the script to include your database server name and login path.  You can create separate files for each server that you want to backup.

Mindless backup stores files in a directory called Month/# where # is the day.

NO SPACES IN THE FILE NAME run_dbserver_backup.vbs

Step 6. Create the automated task

Control Panel, Administrative Tools, Task Scheduler.

Create Task (top right pane of Task Scheduler window)

In the General tab, you pick when to run.. I am always logged on so I leave the defaults.

In the Triggers tab, click on New and select when to run the task.  Do not run more often than once an hour.  Consider “Stop task if it runs longer than” under Advanced Settings.  Press OK when done.

In the Actions tab, click New. Action should be “Start a program” and type in the following:

Program/script:                C:\Windows\System32\cscript.exe

(if your C:\Windows is elsewhere, adjust accordingly)

Add arguments:                .\run_dbserver_backup.vbs

(no quotes or anything around it)

Start in:                                C:\Program Files\Mindless Backup (MySQL)

Conditions and settings are up to you

[1] These tools allow the database password to be stored encrypted, so that you don't have to expose it in your configuration.