How to Complete MySQL Database Backup and Recovery Using Binlogs for Retail Pro Prism

Binlogs, or binary log files, are logs of transactions which make changes to a MySQL database. Binlogs can be used to replay those transactions against a MySQL database to do point-in-time recovery.  MySQL's Binlogs are similar to Oracle Archive logs.

 

Links to MySQL Binlog Documentation

For details about binlogging beyond what is contained in this document please refer to the official MySQL documentation.

MySQL: The Binary Log

MySQL: Point in Time Recovery Using Binary Log

 

Binlog Configuration

Edit the MySQL my.ini file (C:\ProgramData\MySQL\MySQL Server 5.7\my.ini).

Add the following lines below the [mysqld] section in the my.ini file. Make sure the log-bin directory exists (C:/backups/binlogs/binlog). Then stop and start the MySQL service.

log-bin = C:/backups/binlogs/binlog

max_binlog_size = 100M

log_bin_trust_function_creators = 1

 

Back Up the Database

Run the commands below from a command window to create a backup of the rpsods schema. Make sure to change to the directory where your MySQL binaries reside.

When the backup is run it will reset the binlogs, removing any binlogs created before the backup was created. Edit the user and password as needed.

echo SET SESSION SQL_LOG_BIN=0; > c:\backups\rpsods_backup.sql

mysqldump.exe --triggers --events --routines --single-transaction --flush-logs --delete-master-logs --add-drop-database -uroot -psysadmin -h localhost --databases rpsods >> c:\backups\rpsods_backup.sql

 

Restore the Database

Run the command below from a command window to restore a database backup. Make sure to change to the directory where your MySQL binaries reside.

mysql.exe -uroot -psysadmin -h localhost rpsods < c:\backups\rpsods_backup.sql

 

Replay Binlogs Against Restored Database

Run the command below from a command window to replay any binlogs created since the last database backup. Make sure to change to the directory where your MySQL binaries reside. Edit the binlog file names, user, and password as needed.

mysqlbinlog.exe --disable-log-bin c:\backups\binlogs\binlog.000001 c:\backups\binlogs\binlog.000002 c:\backups\binlogs\binlog.000003 | mysql_exe -uroot -psysadmin