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.
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