Using and Maintaining the Binary Log

See Overview of the Binary Log for a general overview of what the binary log is, and Activating the Binary Log for how to make sure it's running on your system.

For details on using the binary log for replication, see the Replication section.

Purging log files

To delete all binary log files on the server, run the RESET MASTER command. To delete all binary logs before a certain datetime, or up to a certain number, use PURGE BINARY LOGS.

Log files can also be removed automatically with the expire_logs_days system variable. This is set to 0 by default (no removal), but can be set to a time, in days, after which a binary log file will be automatically removed. Always set higher than any possible slave lag.

Examples

PURGE BINARY LOGS TO 'mariadb-bin.000063';

PURGE BINARY LOGS BEFORE '2013-04-22 09:55:22';

Safely purging binary log files while replicating

To be sure replication is not broken while deleting log files, perform the following steps:

• Get a listing of binary log files on the master by running SHOW BINARY LOGS.
• Go to each slave server and run SHOW SLAVE STATUS to check which binary log file each slave is currently reading.
• Find the earliest log file still being read by a slave. No log files before this one will be needed.
• If you wish, make a backup of the log files to be deleted
• Purge all log files before (not including) the file identified above.

Binary log format

There are three formats for the binary log. The default is statement-based logging, while row-based logging and a mix of the two formats are also possible. See Binary Log Formats for a full discussion.

Selectively logging to the binary log

By default, all changes to data or data structure are logged. This behavior can be changed by starting the server with the --binlog-ignore-db=database_name or --binlog-do-db=database_name options.

--binlog-ignore-db=database_name specified a database to ignore for logging purposes, while --binlog-do-db=database_name will not log any statements unless they apply to the specified database.

--binlog-ignore-db=database_name behaves differently depending on whether statement-based or row-based logging is used. For statement-based logging, the server will not log any statement where the default database is database_name. The default database is set with the USE statement.

Similarly, --binlog-do-db=database_name also behaves differently depending on whether statement-based or row-based logging is used.

For statement-based logging, the server will only log statement where the default database is database_name. The default database is set with the USE statement.

For row-based logging, the server will log any updates to any tables in the named database/s, irrespective of the current database.

Examples

Assume the server has started with the option --binlog-ignore-db=employees. The following example is logged if statement-based logging is used, and is not logged with row-based logging.

USE customers;
UPDATE employees.details SET bonus=bonus*1.2;

This is because statement-based logging examines the default database, in this case, customers. Since customers is not specified in the ignore list, the statement will be logged. If row-based logging is used, the example will not be logged as updates are written to the tables in the employees database.

Assume instead the server started with the option --binlog-do-db=employees. The following example is not logged if statement-based logging is used, and is logged with row-based logging.

USE customers;
UPDATE employees.details SET bonus=bonus*1.2;

This is again because statement-based logging examines the default database, in this case, customers. Since customers is not specified in the do list, the statement will not be logged. If row-based logging is used, the example will be logged as updates are written to the tables in the employees database.

See also

• PURGE LOGS