MySQL Binary Log Point in Time Recovery Quiz

1. Which command purges binary logs older than a specified date?

PURGE BINARY LOGS BEFORE '2024-01-01';

DELETE BINARY LOGS BEFORE DATE;

REMOVE OLD LOGS;

FLUSH BINARY LOGS;

2. When performing PITR after restoring a full backup, what is the next step?

Apply binary logs from backup timestamp to recovery target time

Restart the MySQL server immediately

Purge all binary logs

Disable binary logging

3. What is the binary log position used for in recovery?

To identify exactly where in the log to start or stop recovery

To compress log files efficiently

To encrypt sensitive data

To schedule automated backups

4. Which option in mysqlbinlog converts binary log data to SQL statements?

--base64-output=DECODE-ROWS

--to-sql

--convert-sql

--output-format=sql

5. In PITR, why is it important to preserve binary logs after a full backup?

They contain changes needed to reach a specific point in time

They serve as a secondary backup copy

They improve database query performance

They reduce storage requirements

6. What is the purpose of the server-id variable in replication and binary logging?

Uniquely identifies a MySQL server instance

Encrypts binary log contents

Limits the number of concurrent connections

Controls backup compression levels

7. When recovering from a specific error event, which mysqlbinlog option helps exclude it?

--stop-position

--skip-error

--exclude-event

--filter-position

8. Which system variable controls the maximum binary log file size?

Max_binlog_size

Binlog_max_size

Log_bin_max_file_size

Binary_log_file_limit

9. What is the relationship between FLUSH LOGS and binary log rotation?

FLUSH LOGS closes current log and creates a new one

FLUSH LOGS archives old logs to a backup location

FLUSH LOGS compresses the current binary log

FLUSH LOGS enables binary logging if disabled

10. In PITR recovery, what does the --stop-datetime parameter do?

Specifies when to stop applying log events

Sets the server start time

Limits the number of log events processed

Filters events by database name

11. What is the primary purpose of the binary log in MySQL?

Recording all data changes for replication and recovery

Storing query execution plans

Caching frequently accessed data

Compressing database files for storage

12. What is a binary log index file?

A file listing all binary log filenames in order

An indexed search structure for log contents

A compressed summary of all log events

A replication status tracker

13. How does binlog_format='MIXED' handle replication?

Always uses statement-based logging

Always uses row-based logging

Automatically switches between SBL and RBL based on context

Combines both formats in a single log entry

14. What is the binary log filename format in MySQL?

Binlog.001, binlog.002, etc.

Hostname-bin.000001, hostname-bin.000002, etc.

Mysql-bin.000001, mysql-bin.000002, etc.

Server-log.001, server-log.002, etc.

15. Which option in mysqlbinlog specifies a start time for log reading?

--start-time

--begin-time

--from-time

--log-start-time

16. When performing PITR, what is the critical prerequisite?

A full backup and accessible binary logs

Multiple replica servers

Real-time data replication enabled

Compressed backup files

17. What does PITR stand for in MySQL recovery?

Parallel Incremental Time Restoration

Point in Time Recovery

Progressive Incremental Transaction Replay

Persistent Indexed Transaction Record

18. Which tool is used to read and parse MySQL binary logs?

Mysqlbinlog

Mysqldump

Mysql_recovery

Binlog_parser

19. What command enables binary logging in MySQL?

SET log_bin = ON;

ENABLE BINARY LOG;

Log-bin parameter in my.cnf

START BINARY LOGGING;

20. Which binary log format is most suitable for row-level recovery operations?

Statement-based logging (SBL)

Row-based logging (RBL)

Mixed-based logging (MBL)

Query-based logging (QBL)

MySQL DBA Binary Log Point in Time Recovery Quiz