MySQL Binary Logs

If you supply the --log-bin option with no string (as shown) or with an empty string, the base name defaults to host_name-bin.To change the base name use: --log-bin[=base_name]If you do not include an absolute path in the base_name definition the binary logs will be written to the data directory.When you enable Binary Logging an index file is also created using the base name with the .index suffix.

To disable at startup add this to the startup command...

--skip-log-bin

--disable-log-bin

A suitably privileged client can disable binary logging of its own statements using...

SET sql_log_bin=OFF

Flush

logrotate

FLUSH BINARY LOGS;

Closes and reopens any binary log file to which the server is writing. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file. This operation requires the RELOAD privilege. (1)

Deleting Binary Logs

Note that deleting the logs has implications for PITR and Replication. Understand these implications before continuing.

To delete all existing binary log files and resets the binary log index file...

RESET MASTER

If you do not want the numbering to restart at 1, then use...

RESET MASTER TO 1234

If you just want to remove older logs (without impacting Replication)...

For PITR you need to retain all logs since the start of the last backup

PURGE BINARY LOGS TO 'mysql-bin.010';

PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';

The PURGE BINARY LOGS statement deletes all the binary log files listed in the log index file prior to the specified log file name or date. BINARY and MASTER are synonyms. Deleted log files also are removed from the list recorded in the index file, so that the given log file becomes the first in the list. PURGE BINARY LOGS requires the BINLOG_ADMIN privilege. (2)

If binary logs have been removed from the system using the linux rm command or similar, then you will need to manually edit the .index file...

Binary logs are automatically deleted according to the setting of binlog_expire_logs_seconds. This occurs when the binary log is flushed.

Binary Log Configuration

binlog_encryption

Introduced in MySQL 8.0.14

Default: OFF

SELECT @@binlog_encryption;

To resize this variable online (without the need to restart MySQL Server)...

SET GLOBAL binlog_encryption=ON;

binlog_expire_logs_seconds

This variable replaces: expire-logs-days

Default: 2592000

SELECT @@binlog_expire_logs_seconds;

To resize this variable online (without the need to restart MySQL Server)...

SET GLOBAL binlog_expire_logs_seconds=1296000;

www.calculateme.com/time/

2592000 seconds = 30 Days1296000 seconds = 15 Days
For PITR you need all binlogs since your last successful backup.

binlog_format

Default: ROW

SELECT @@binlog_format;

To resize this variable online (without the need to restart MySQL Server)...

SET GLOBAL binlog_format=ROW;

STATEMENTMIXEDROW

log_replica_updates

Introduced in 8.0.26

Default: ON

"Causes the replica to write the updates that are received from a source and performed by the replication SQL thread to the replica's own binary log." (3) Useful when you want to chain replication servers.

SELECT @@log_replica_updates;

log_slave_updates

Deprecated in 8.0.26. Use log_replica_updates instead

Default: OFF (5.7) / ON (8.0)

Useful when you want to chain replication servers

SELECT @@log_slave_updates;

max_binlog_size

Default: 1073741824 bytes (1 GB)

SELECT @@max_binlog_size;

sync_binlog

Default: 1
Recommended: 1

See also: innodb_flush_log_at_trx_commit

SELECT @@sync_binlog;

To change this variable online (without the need to restart MySQL Server)...

SET GLOBAL sync_binlog=0;

0 relies on the OS to flush the binary log to disk from time to time as it does for any other file. This improves performance but risks data loss.

"Caution Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. In this case, the durability of transactions is not guaranteed even with the recommended settings, and in the worst case, a power outage can corrupt InnoDB data. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try to disable the caching of disk writes in hardware caches." (4)

my.cnf

binlog_formatSTATEMENTMIXEDROW

server-idIn MySQL 5.7 the instance will not start if Binary Logging is enabled and server-id is not set. A setting of zero implies you will not be using replication.

Example entries relating to binary logs...

binlog-format = ROW

binlog_expire_logs_seconds = 2592000

expire-logs-days = 0

log-bin = /var/lib/mysql/log

log-replica-updates = ON

sync-binlog = 1

max_binlog_size = 1024M

server-id = 0

binlog_expire_logs_seconds2592000 seconds = 30 Days1296000 seconds = 15 Days
For PITR you need all binlogs since your last successful backup.

www.calculateme.com/time/

Bibliography & References

Page updated

Google Sites

Report abuse