MySQL Logs Flashcards
True or False:
By default, no logs are enabled, except the error log on Windows
True
In what order do these steps occur for renaming a log file in a linux server:
A - $ mysqladmin flush-logs
B - $ mv host_name.err host_name.err-old
C - $ mv host_name.err-old backup-directory
a) ABC
b) BCA
c) BAC
d) CBA
c - BAC
$ mv host_name.err host_name.err-old
$ mysqladmin flush-logs
$ mv host_name.err-old backup-directory
True or False:
By default, the server writes files for all enabled logs in the usr directory
False
By default, the server writes files for all enabled logs in the data directory
True or False:
You can force the server to close and reopen the log files (or in some cases switch to a new log file) by flushing the logs
True
Name 2 ways to clean up these files regularly to ensure that the logs do not take up too much disk space
back up and remove old log files from time to time
tell MySQL to start logging to new files.
How do you force MySQL to start using new logs?
To force MySQL to start using new log files, flush the logs.
Execution of log-flushing statements or commands requires the server to have RELOAD privilege.
On Unix and Unix-like systems, another way to flush the logs is to send a signal to the server, which can be done by root or the account that owns the server process
Which logs are in binary file format? Choose 3.
a) binary log
b) DDL log
c) error log
d) relay log
e) general query log
a, b, d
the binary log, relay log, and DDL log are all in binary format.
Which statement cannot be used to view binary log?
a) mysqlbinlog [options] log_file
b) mysqlbin [options] log_file
c) SHOW BINLOG EVENTS IN ‘log_file’
d) BINLOG ‘str’
b
a) mysqlbinlog –> output includes events contained in log_file
c) SHOW BINLOG EVENTS IN ‘log_file’ –> Shows the events in the binary log. If you do not specify ‘log_name’, the first binary log is displayed
d) BINLOG ‘str’ –> the printable representation of certain events in binary log files.
BINLOG is an internal-use statement. It is generated by the mysqlbinlog program as the printable representation of certain events in binary log files.
Which option used in this statement is used to specify network interface to connect to MySQL Server?
mysqlbinlog [options] log_file
–bind-address
Which option used in this statement lists entries for just this database?
mysqlbinlog [options] log_file
–database
Which option used in this statement will write debugging log?
mysqlbinlog [options] log_file
–debug
Which option used in this statement will print debugging information when program exits?
mysqlbinlog [options] log_file
–debug-check
Which directory is the error log located in a linux system?
a) /var/log/mysql/
b) \ProgramData\MySQL\MySQL Server 8.0\Data\
c) /etc/mycnf
d) /var/lib/mysql/
a) /var/log/mysql/error.log
slow query and general query log files are found in /var/lib/mysql/mysql
Which directory is the error log located in a windows server?
a) /var/log/mysql/
b) \ProgramData\MySQL\MySQL Server 8.0\Data\
c) /var/lib/mysql
d) /etc/mysql/
b) \ProgramData\MySQL\MySQL Server 8.0\Data\
files are usually hidden
In what file would you find the location of the log files in a windows server?
a) /var/log/mysql/my.ini
b) \ProgramData\MySQL\MySQL Server 8.0\Data\my.ini
c) /var/lib/mysql
d) /etc/mysql/mycnf
b) \ProgramData\MySQL\MySQL Server 8.0\Data\my.ini
In what file would you find the location of the log files in a linux debian server?
a) /var/log/mysql/my.ini
b) \ProgramData\MySQL\MySQL Server 8.0\Data\my.ini
c) /etc/mycnf
d) /etc/mysql/mycnf
d) /etc/mysql/mycnf
In what file would you find the location of the log files in a linux Red Hat server?
a) /var/log/mysql/my.ini
b) \ProgramData\MySQL\MySQL Server 8.0\Data\my.ini
c) /etc/mycnf
d) /etc/mysql/mycnf
c) /etc/mycnf
Which of the following statements about the error log is false?
a) contains a record of mysqld startup and shutdown times.
b) Depending on configuration, error messages may also populate the Performance Schema error_log table, to provide an SQL interface to the log and enable its contents to be queried
c) when mysqld_safe notices abnormal mysqld exits, it restarts mysqld and writes a mysqld restarted message to the error log.
d) If used to start mysqld, mysqld_safe will not write messages to the error log.
e) contains diagnostic messages such as errors, warnings, and notes that occur during server startup and shutdown, and while the server is running.
d)
If used to start mysqld, mysqld_safe MAY write messages to the error log.
Which is not a feature of component-based error logging?
a) consists of a set of numbered files containing events that describe database changes, and an index file that contains the names of all used log files.
b) Log events can be filtered by filter components to affect the information available for writing.
c) Log events are output by sink (a destination for log events) components.
d) Built-in filter and sink components combine to implement the default error log format.
e) A loadable sink enables logging in JSON format and logging to the system log.
a)
relay logs consists of a set of numbered files containing events that describe database changes, and an index file that contains the names of all used relay log files.
Which system variable is described by the following?
global, dynamic
value = The components to enable for error logging.
loadable component named in the value must first be installed with INSTALL COMPONENT.
order is significant because the server executes components in the order listed
log_error_services
Which are the default values for log_error_services?
a) log_filter_internal; log_sink_internal
b) log_error_verbosity; log_error_supression_list
c) log_sink_internal; log_error_supression_list
d) log_sink_internal; log_error_verbosity
a) log_filter_internal; log_sink_internal
Which 2 variables affect the filtering performed by log_filter_internal?
log_error_verbosity → specifies the verbosity for handling events intended for the error log.
log_error_supression_list → applies to events intended for the error log and specifies which events to suppress when they occur with a priority of WARNING or INFORMATION.
Which is the default for log_error_verbosity?
a) value = 1 (error priority)
b) value = 2 (error, warning priority)
c) value = 3 (error, warning, information priority)
a) value = 1 (error priority)
Which statement is used to view log_error_services?
a) mysql> View @@GLOBAL.log_error_services;
b) mysql> SHOW @@GLOBAL.log_error_services;
c) mysql> SELECT @@GLOBAL.log_error_services;
c) mysql> SELECT @@GLOBAL.log_error_services;
Suppose that you want to configure, for every server startup, use of the JSON log sink (log_sink_json) in addition to the built-in log filter and sink (log_filter_internal, log_sink_internal).
Which is the correct order of steps?
a) Load the JSON sink if it is not loaded.
INSTALL COMPONENT ‘file://component_log_sink_json’;
b) The order of components named in log_error_services is significant, particularly with respect to the relative order of filters and sinks.
c) Set log_error_services to take effect at server startup or you can set it using SET PERSIST:
a –> c –> b
First load the JSON sink if it is not loaded:
INSTALL COMPONENT ‘file://component_log_sink_json’;
Then set log_error_services to take effect at server startup. You can set it in my.cnf:
[mysqld]
log_error_services=’log_filter_internal; log_sink_internal; log_sink_json’
Or you can set it using SET PERSIST:
SET PERSIST log_error_services = ‘log_filter_internal; log_sink_internal; log_sink_json’;
The order of components named in log_error_services is significant, particularly with respect to the relative order of filters and sinks. Consider this log_error_services value:
log_filter_internal; log_sink_1; log_sink_2
Which of the following is not a factor that influences error log output messages?
a) The information available to the log sink.
b) whether the server is Windows or Linux
c) log_timestamps controls the time zone of timestamps in messages written to the error log
d) The information relevant to the log sink.
b) whether the server is Windows or Linux
What log is described here?
general record of what mysqld is doing in the order that mysqld receives them which might be different than the order that they are executed
can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld
Each line that shows when a client connects also includes using connection_type to indicate the protocol used to establish the connection
can control during runtime
Be aware that this log type is a performance killer.
The General Query Log
Which of the following connection types is a TCP/IP connection established without SSL?
a) TCP/IP
b) SSL/TLS
c) Socket
d) Named Pipe
d) Shared Memory
TCP/IP (TCP/IP connection established without SSL),
Which of the following connection types is a TCP/IP connection established with SSL?
a) TCP/IP
b) SSL/TLS
c) Socket
d) Named Pipe
d) Shared Memory
SSL/TLS (TCP/IP connection established with SSL),
Which of the following connection types is a Unix socket file connection?
a) TCP/IP
b) SSL/TLS
c) Socket
d) Named Pipe
d) Shared Memory
Socket (Unix socket file connection),
Which of the following connection types is a Windows named pipe connection?
a) TCP/IP
b) SSL/TLS
c) Socket
d) Named Pipe
d) Shared Memory
Named Pipe (Windows named pipe connection)
Which of the following connection types is a Windows shared memory connection?
a) TCP/IP
b) SSL/TLS
c) Socket
d) Named Pipe
d) Shared Memory
Shared Memory (Windows shared memory connection).
Which statement about the general query log is false?
a) when binlog_format is ROW → updates are sent as row changes rather than SQL statements, which are never written to the general query log
b) when binlog_format is Mixed→ updates might not be written to the general query log (depends on the statement used)
c) log_timestamps system variable controls the time zone of timestamps in messages written to the general query log file
d) Passwords in statements written to the general query log are rewritten by the server to occur literally in plain text
d)
Passwords in statements written to the general query log are rewritten by the server NOT to occur literally in plain text
Which statements are false about the slow query log? (choose 2)
a) info on queries that took more than long_query_time (default 10s) seconds to execute and require at least min_examined_row_limit rows to be examined.
b) The time to acquire the initial locks is counted as execution time
c) mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might differ from execution order.
d) can’t control during runtime
e) Enabled by slow_query_log
f) Default off or 0
b)
The time to acquire the initial locks is not counted as execution time
d)
can control during runtime
True or false:
Slow query logs are always found in the /var/lib/mysql/ directory
False
Linux /var/lib/mysql/instance_name-slow.log
Windows \ProgramData\MySQL\MySQL Server 8.0\Data\mysql\slow.log
Which of the following types of queries are written to slow_query_log:
a) administrative statements
b) queries that take longer than long_query_time
c) queries that do not use indexes
d) replicated queries
b)
Administrative statements are not logged, nor are queries that do not use indexes for lookups.
Administrative statements include ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.
A replica does not write replicated queries to the slow query log.
log_output = destination or destinations for general query log and slow query log output
which option selects logging to log files?
a) FILE
b) TABLE
c) NONE
a) FILE
log_output = destination or destinations for general query log and slow query log output
which option selects logging to the general_log and slow_log tables in the mysql system schema?
a) FILE
b) TABLE
c) NONE
b) TABLE
log_output = destination or destinations for general query log and slow query log output
which option disables logging and if present in the value, it takes precedence over any other words that are present?
a) FILE
b) TABLE
c) NONE
c) NONE
How do you resolve “too many open files errors”? (pick 2)
a) execute FLUSH TABLES
b) Delete all the open files
c) ensure that the open_files_limit is greater than the value of table_open_cache_instance
d) execute DROP TABLE
a) and c)
Which is not a benefit of log tables?
a) client doesn’t need the appropriate log table privileges
b) Log entries have a standard format
c) Log contents are accessible through SQL statements
d) Logs are accessible remotely through any client that can connect to the server and issue queries
a)
client has to have the appropriate log table privileges
not necessary to log in to the server host and directly access the file system
Which are true about log tables? (pick 3)
a) CREATE TABLE, ALTER TABLE, and DROP TABLE are valid operations on a log table.
b) Partitioning of log tables is permitted.
c) By default, the log tables use the CSV storage engine that writes data in comma-separated values format.
d) No engines other than CSV or InnoDB are legal for the log tables.
e) FLUSH TABLES WITH READ LOCK and the state of the read_only system variable have no effect on log tables.
a), c), e)
Partitioning of log tables is not permitted.
By default, the log tables use the CSV storage engine that writes data in comma-separated values format.
No engines other than CSV or MyISAM are legal for the log tables.
Which statements are false about binary log? (pick 2)
a) Only complete events or transactions are logged or read back.
b) flushed when its size reaches the value of the max_binlog_size system variable.
c) not used for statements such as SELECT or SHOW that do not modify data.
d) makes performance faster.
e) contains “events” that describe database changes such as table creation operations or changes to table data
f) Passwords in statements written to the binary log are not rewritten by the server
d) f)
makes performance slightly slower.
Passwords in statements written to the binary log are rewritten by the server not to occur literally in plain text
True or false
From MySQL 8.0.14, binary log files and relay log files can be encrypted
True
From MySQL 8.0.14, binary log files and relay log files can be encrypted,
enable binlog_encryption (default off)
True or false
Binary logs are disabled by default except when you use mysqld to initialize the data directory manually by invoking it with the –initialize or –initialize-insecure option
False
Enabled by default except when you use mysqld to initialize the data directory manually by invoking it with the –initialize or –initialize-insecure option
Which statements about disabling the binary log is false?
a) To disable specify the –skip-log-bin option at startup.
b) To disable specify the –disable-log-bin option at startup.
c) If either of these options is specified and –log-bin is also specified, the option specified first takes precedence.
c)
If either of these options is specified and –log-bin is also specified, the option specified later takes precedence.
True or false
Compressed transaction payloads remain in a compressed state while they are sent in the replication stream to replicas
true
Compressed transaction payloads remain in a compressed state while they are sent in the replication stream to replicas
When binary log transaction compression is enabled (default is OFF), transaction payloads are compressed using the zstd algorithm, and then written to the server’s binary log file as:
a) multiple events
b) single event
c) depends on the size of the compressed file
b)
When binary log transaction compression is enabled (default is OFF), transaction payloads are compressed using the zstd algorithm, and then written to the server’s binary log file as a single event
Which of the following statements about binary file compression is false?
a) the max_allowed_packet and replica_max_allowed_packet or slave_max_allowed_packet limits for the server still apply
b) only saves storage space on the recipient (replica)
c) can also be filtered out on a replica by the usual filtering options
d) Binary log transaction compression can be applied to XA transactions.
e) Transactions with payloads that are compressed can be rolled back like any other transaction
b)
saves storage space both on the originator of the transaction and on the recipient
Which type of event is not excluded from binary log transaction compression? (pick 1)
a) Events relating to the GTID for the transaction
b) Non-transactional events
c) Trigger events
d) Events that are logged using statement-based binary logging.
e) Other types of control event, such as view change events and heartbeat events.
c) Trigger events
Which one of the binlog format options (ROW | STATEMENT | MIXED) is described below?
- default
- temp tables not included
- writes events to the binary log that indicate how individual table rows are affected.
- PER SESSION, some statements require a lot of execution time on the source, but result in just a few rows being modified
ROW
Which one of the binlog format options (ROW | STATEMENT | MIXED) is described below?
- the propagation of SQL statements from source to replica that replication was based on
- includes temp tables
- events can’t be compressed
- not a good practice even outside of replication
- may be issues with replicating nondeterministic statements → it is left up to the query optimizer
PER SESSION, performs updates that match many rows in the WHERE clause might want to use statement-based logging because it is more efficient to log a few statements than many rows.
STATEMENT
Which one of the binlog format options (ROW | STATEMENT | MIXED) is described below?
- statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases
- As of MySQL 8.0, operations on temporary tables are not logged in this logging format, and the presence of temporary tables in the session has no impact on the logging mode used for each statement.
- MySQL determines whether it can guarantee that the statement can be replicated using statement-based logging, if not it switches to ROW
MIXED
If binary format is MIXED, which of the following statements about switching to ROW is false?
a) When a function contains UUID().
b) When FOUND_ROWS() or ROW_COUNT() is used.
c) When USER(), CURRENT_USER(), or CURRENT_USER is used
d) When one of the tables involved is a log table in the mysql database.
e) When a statement doesn’t include system variables.
e)
When a statement refers to one or more system variables.
If binary format is MIXED, which of the following variables will not trigger a switch to row, staying in STATEMENT logging is false?
a) timestamp
b) foreign_key_checks
c) auto_increment_offset
d) auto_increment_increment
e) sql_auto_is_null
f) all of them
d) none of them
f) all of them
Switching the replication format while replication is ongoing can causes these types of issues: (pick 3)
a) changing the logging format on a replication source server causes a replica to change its logging format to match
b) STATEMENT mode is not replicated
c) MIXED or ROW logging mode is ignored by the replica.
d) replica is not able to convert binary log entries received in ROW logging format to STATEMENT format for use in its own binary log
b) c) d)
changing the logging format on a replication source server does not cause a replica to change its logging format to match
Which statements about the relay log are false? (pick 2)
a) consists of a set of numbered files containing events that describe database changes, and an index file that contains the names of all used relay log files.
b) replica uses a binary file to track the relay log files currently in use.
c) relay log file names have the default form host_name-relay-bin.nnnnnn
d) default relay log file and relay log index file names and locations cannot be overridden
e) used only on replicas, to hold data changes from the replication source server that must also be made on the replica.
b) d)
replica uses an index file to track the relay log files currently in use.
default relay log file and relay log index file names and locations can be overridden with, respectively, the relay_log and relay_log_index
In what order are the following steps used to resolve an issue after replication:
a) prepend the contents of the old relay log index file to the new one
b) stop the replica server
c) restart the replica.
b) a) c)
one way to work around it is to stop the replica server, prepend the contents of the old relay log index file to the new one, and then restart the replica.
On a Unix system, this can be done as shown here:
shell> cat new_relay_log_name.index»_space; old_relay_log_name.index
shell> mv old_relay_log_name.index new_relay_log_name.index
A replica server creates a new relay log file under the following conditions: (pick 3)
a) When the logs are flushed (for example, with FLUSH LOGS or mysqladmin flush-logs).
b) Each time the replication I/O (receiver) thread ends.
c) When the size of the current relay log file becomes too large, which is determined as follows: max_relay_log_size = 0
d) max_relay_log_size is 0, max_binlog_size determines the maximum relay log file size.
a) c) d)
Each time the replication I/O (receiver) thread starts.
max_relay_log_size > 0, that is the maximum relay log file size.
Which of the following statements about DDL log is false?
a) also known as metadata log
b) generated by data definition statements affecting table partitioning, such as ALTER TABLE t3 DROP PARTITION p2
c) written to the file ddl_log.log in MYSQL etc directory
d) always created when required, and has no user-configurable options
c)
written to the file ddl_log.log in MYSQL data directory
True or false
DDL log can hold up to 1048573 entries, equivalent to 4 GB in size. When limit is exceeded, rename or remove the file before it is possible to execute any additional DDL statements.
True
You Enable binary logging on the MySQL Server with the configuration binlog-format=ROW.
Which db updates are logged on the master server to the binary log by default?
a) all updates except the TEMPDB database
b) all updates except tp the PERFORMANCE_SCHEMA db
c) all updates not involving temp tables
d) all updates to the default database, except temp tables
d) all updates to the default database, except temp tables
