This article contains information about the backup concept and technical configuration as well as the procedure for switching to backup during a primary server failure.

This is an optional step if redundancy is required for your database server.

This article applies to MySQL 5.5 & +

Operational Summary

About Backup Servers

Your BL Backup server is designed to provide safety against data loss and on-air downtime in the event of drive or other hardware failure on your Primary server. When setup and running using the instructions below the Primary server will synchronize your data changes with the Backup server. Your Backup data will never be more than a couple seconds behind the Primary when replication is running.

During Normal Operation

All the parsers, readers, and Flow are setup on the Backup server but the parsers and readers are not running and Flow should not be accessed. No changes should be made to the data on the Backup server as it will conflict with the incoming replication changes from the Primary server.

Do not start or run parsers, readers or log in to Flow on the Backup server while the Primary is active

During a Primary Server Failure

During a Primary server failure, your on-air output is the most important component to have up and running. In the preferences of your player, switch the IP of the database from your Primary to your Backup. BL players have data caching which will keep the graphics on air for several minutes. After the player has been switched to the Backup database server, log in to the Backup server and start any readers and parsers. Continue to access Flow by using the Backup server IP in the same manner as you would your Primary server. When a new Primary server is obtained or the current Primary fixed, it will become the Backup and your current Backup which you have been running will be your Primary.

In the event of a full primary database server failure the easiest way to switch several players to point to the backup server is to:

Set the backup server IP to the primary server's IP.

If this is not an option in your environment the second option is to:

Set your player database IP to that of the Backup server.

Proceed with the remainder of the recovery steps:

Start all parsers and readers on the Backup server.
Start up the backup website using the instructions below and sign in to Flow on Backup server IP and continue work.

Starting Your Backup Flow Web Server

The Flow webserver is paused on your backup server to prevent causing synch issues with the backups. This can happen when a user signs in on the backup Flow and it creates an audit record that is not from the primary. The backup Flow can be tested on install, but after replication is setup, it will be paused to prevent data conflicts.

To restart your backup Flow web server follow the steps below.

Launch Internet Information Services (IIS) Manager. This can be found in the Administration section of the Control Panel or search for IIS in the Start Bar
Roll out the tree until the Default Web Site is visible and click on it to bring up the properties pane.
On the right of the pane will be controls for stopping, starting and restarting the website under the Manage Website heading.
Click on Start
You can now access your backup Flow though a web browser.

Replication Backup

Initial Preparation For Replication Backups

Both your servers must have the same MySQL version installed.

Create a Backup/Replication user

This user will be used on the backup to connect to the primary/source database to access the changes so it can apply them too. This user will be used on the primary/source by DMan to check the status of the backup server and report in to the Agent Status tool in Flow. Ideally having the same user on BOTH the Source and Backup system.

You can use the default blbackup user. See Configuring MySQL Users and look for the section on replication user.

Server Configuration

Binary logging must be enabled on the servers because the binary log is the basis for sending data changes from the source to its backups. If binary logging is not enabled, replication will not be possible.

Unless you have made changes to your MySQL setup, binary logging is enabled by default.

Adding Server ID to the INI file

Each server within a replication group must be configured with a unique server ID. This ID is used to identify individual servers within the group.

Find the my.ini configuration file in the C:\ProgramData\MySQL\MySQL Server 5.6 folder and edit it adding the following section under the mysqld section.

server-id =1 is a common ID for the primary

server-id=2 is a common ID for the backup

For the Primary

[mysqld]
server-id=1
log-bin=mysql-bin

 
# Alternatively to store log files on another drive with lots of space:
# log-bin=d:/MySQL/Logs/mysql-bin

For the Backup

[mysqld]
server-id=2

Additional server settings

Confirm the following entries are already present and if not add the additional entries as follows:

# binlog_format=mixed  # deprecated in version 8.0.34 
binlog_format=row 
binlog_row_image=minimal

# For the greatest possible durability and consistency in a replication setup that uses InnoDB with transactions, use these settings: 
#   https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_sync_binlog 
sync_binlog=1
innodb_flush_log_at_trx_commit=1

Recommended: Use an entry to control how soon binary logs expire

Restart mysql services

Restart the mysql service by either opening services, finding mysql and restarting it or by using the Oracle MySQL task bar services control option to stop and start the service.

A restart of the service is needed for configuration changes to take effect.

If this server had been used as a backup previously don`t forget to issue the STOP SLAVE command or restarting the service will fail after adding the log-bin line in your my.ini

Obtain the IP address of your source server as it will be needed during the configuration of the Backup server

You will need to create a replication user if you haven't already done so. You can use the default blbackup user. See Configuring MySQL Users and look for the section on replication user.

Both the source and the server require this user to be created.

Making a Copy of the Primary Database

The backup server needs to have a recent snapshot of the primary database in order to start replication.

Do not make any changes to the backup database or tables, even if you think it might be a laugh. They must be atomically identical to the source export for the replication to begin.

Attach the Backup to the Source

The source password is stored in clear text in the backup database table so if your systems are not disconnected from cloud access it is strongly recommended to create a replication specific user on the source and use that account for replication access.

Replication only access can be granted to this user prohibiting any other type of source database access if the password and account name were to be accessed.

If you fall under this scenario, please contact BL Support for assistance in creating a replication user account for use in your backups.

Backup Configuration

On the Backup server using MySQL Workbench open a Query window and enter the following :

Default Source_User is blbackup .

Use the lightning bolt icon to process the query

Watch for the result to be a success.

Create A Source Backup

You need to create a source dump of the data on the source/primary database server.

The best way to do this is with a batch/command line process. Once you setup the necessary files you can easily re-use them without going through all the setup parts - just run the commands.

You can get copies of the sample/template files online:

For MySQL before 8.0.26: https://www.bannisterlake.com/dl/installs/Chameleon/_Utilities/mysql/master_slave_copy.zip
For MySQL 8.0.26 and up https://bannisterlake.com/dl/installs/Chameleon/_Utilities/mysql/replicationCopy826.zip

Possible location is: D:\Bannister Lake\MySqlBackups\source and the file structure will look like this:

Edit Source Dump Config file

You will need a configuration file named 📄 dump_source_options.cnf as follows. (This is a plain text file - saved with the cnf extension.)

Edit it with a text editor to define the correct password for the mysql backup user.

Edit Source Dump Command BAT file

Prepare the following command customized to your environment to create a dump file.

The easiest way to do this is to have a batch file that you can run with this command in it: 📄 dump_source.bat

Edit this batch file with a text editor to define the correct

mysql server bin folder location
host ip address or name (localhost is recommended default value)
location for the dump file and error log file
- this should be a drive with enough space to hold the dump file - it doesn't have to be the c:\
- if you remove the drive and folder from the file name it will save it to the same folder as the batch file is in

Before running the source dump

Run this sql command on the primary server database by using Workbench or even the MySQL command line.

To start MySQL on the command line open a command prompt and enter: (you can do this from the backup even)

Then you'll see the mysql> prompt

enter the RESET SOURCE statement and press enter to execute.

Run the source dump bat file

Now Run the dump batch file - double click or use a command window:

open a command prompt (type cmd in the start menu to find it)
change your drive and directory to be the one your source dump batch file is in
type dump_source.bat and press enter
if everything is setup correctly your batch file will run and create the source dump file

Import Dump

If you haven't already done so based on the instructions in the step before creating the source dump - stop and reset the backup now.

Execute the following command in a MySQL query on the backup:

Copy the new data file onto your backup server and import the data.

Run the following command customized to your environment to import the dump file.

The easiest way to do this is to have a batch file that you can run with this command in it: 📄 import_source.bat

Edit this batch file with a text editor to define the correct

mysql server bin folder location
password
host name/ip
import file location (copied from source server)
import result log file (a text file)

Run this batch file using a command prompt window like above or just double-click the batch file to run it.

Start Backup Processing

Now that the Source data has been restored onto the backup server, it is time to start the backup process. Execute the following command in a MySQL query on the backup:

Verifying That Your Backup Is Running

Verify the Backup server is connecting to the source by entering the following command in the query window on the backup server

The status returned will either be "Waiting for Source Event" or "Consuming Source Binary Logs". Both of these indicate successful setup of backup replication however be sure to then also scroll through the columns and check for Last_SQL_Error and Last_IO_Error and make sure both these are blank (i.e. no error). IF there is an error - then the replication is not actively working.

Replication Status Monitoring

There is an automatic check of the replication status that displays in Flow in the Agent Status widget along with the other automated reader and data management apps. It will also indicate if these error messages have been triggered. Note that this does require that the necessary configuration is done on the primary and backup servers. For more info see: Replication and Backup Status Monitor

Congratulations you have setup your backup server.

Limiting Replication To Certain Databases or Tables

If you want to limit the database or tables that are replicated use the replicate_wild_table_do setting in the BACKUP my.ini by defining database.table combinations. Wild cards are supported using %.

The following is an example of filtering on only the elector municipal database, grabbing only the data tables.

To limit replication to a certain database use

1 Operational Summary
2 Replication Backup
- 2.1 Initial Preparation For Replication Backups
  - 2.1.1 Create a Backup/Replication user
  - 2.1.2 Server Configuration
    - 2.1.2.1 Adding Server ID to the INI file
    - 2.1.2.2 Additional server settings
    - 2.1.2.3 Restart mysql services
- 2.2 Making a Copy of the Primary Database
  - 2.2.1 Attach the Backup to the Source
    - 2.2.1.1 Backup Configuration
  - 2.2.2 Create A Source Backup
    - 2.2.2.1 Edit Source Dump Config file
    - 2.2.2.2 Edit Source Dump Command BAT file
    - 2.2.2.3 Before running the source dump
    - 2.2.2.4 Run the source dump bat file
  - 2.2.3 Import Dump
  - 2.2.4 Start Backup Processing
  - 2.2.5 Verifying That Your Backup Is Running
- 2.3 Replication Status Monitoring
- 2.4 Limiting Replication To Certain Databases or Tables