Enabling Binary Logging and Recovering Binary Logs

Enabling Binary Logging

This section describes how to enable binary logging:

To enable MySQL binary logging:
  1. Edit the my.cnf file located at:

/exlibris/sfx_ver/sfx4_1/data/my.cnf

  1. Change the line that contains the option:

log-bin

to

log-bin = /exlibris/sfx_ver/sfx4_1/data/log-bin

By default, the binary logs are created in the MySQL data directory and their file name is in the format bin-log.00000x. If you want to store them elsewhere, supply a path name or file name to the log-bin option, such as log-bin = /path/to/elsewhere/filename.

  1. Restart MySQL.

To confirm that binary logs are being created, look in the MySQL data directory for a binary log file named with the .001 extension in the MySQL data directory (or whatever path you specified in my.cnf). A new binary log file with an incremental extension (for example, .002, .003) is created each time you stop or start MySQL, each time a FLUSH LOGS command is issued, and each time the size of the current binary log reaches the value of the MySQL setting max_binlog_size.

Recovering Binary Logs

The recovery from binary logs procedure assumes that you have also been performing a regular backup of the MySQL database. First restore the database to its state at the time of the last backup by importing the backup. Use the data stored in the binary logs to restore the database to the exact moment of failure (or whatever event is prompting the recovery).

Identify the binary log files that contain all of the updates issued to the server since your last backup. To load the binary log data, you must use the mysqlbinlog program to read the binary data. Send the output from mysqlbinlog to the MySQL command-line program:

mh

cd bin

../mysqlbinlog --database=<database> /exlibris/sfx_ver/sfx4_1/data/bin-log.00000x | mysql -uroot -p<mysql_root_pwd> --database=<database>

It is recommended to reset the binary logs prior to a recovery by issuing a RESET MASTER command to the server.

MySQL Privileges When Using Binary Logging

To prevent errors when applying revision updates when using binary logging, set up MySQL with SUPER privileges.

To configure SUPER privileges:
  1. Log on to MySQL as the sfxglb41 user:

mysql -uroot -p<root_mysql_pwd> mysql

  1. Type the following:

GRANT SUPER on *.* to sfxglb41@localhost;

Without these permissions, the following error occurs when applying revisions:

DBD::mysql::db do failed: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)