Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

MySQL 8.0 Certification Exam > MySQL Logs > Flashcards

MySQL Logs Flashcards

1
Q

True or False:

By default, no logs are enabled, except the error log on Windows

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

A

c - BAC

$ mv host_name.err host_name.err-old
$ mysqladmin flush-logs
$ mv host_name.err-old backup-directory

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

True or False:

By default, the server writes files for all enabled logs in the usr directory

A

False

By default, the server writes files for all enabled logs in the data directory

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Name 2 ways to clean up these files regularly to ensure that the logs do not take up too much disk space

A

back up and remove old log files from time to time

tell MySQL to start logging to new files.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

How do you force MySQL to start using new logs?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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

a, b, d

the binary log, relay log, and DDL log are all in binary format.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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’

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Which option used in this statement is used to specify network interface to connect to MySQL Server?

mysqlbinlog [options] log_file

A

–bind-address

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Which option used in this statement lists entries for just this database?

mysqlbinlog [options] log_file

A

–database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Which option used in this statement will write debugging log?

mysqlbinlog [options] log_file

A

–debug

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Which option used in this statement will print debugging information when program exits?

mysqlbinlog [options] log_file

A

–debug-check

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

a) /var/log/mysql/error.log

slow query and general query log files are found in /var/lib/mysql/mysql

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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/

A

b) \ProgramData\MySQL\MySQL Server 8.0\Data\

files are usually hidden

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

b) \ProgramData\MySQL\MySQL Server 8.0\Data\my.ini

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

d) /etc/mysql/mycnf

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

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

A

c) /etc/mycnf

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

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.

A

d)

If used to start mysqld, mysqld_safe MAY write messages to the error log.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

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

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

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

A

log_error_services

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

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

a) log_filter_internal; log_sink_internal

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Which 2 variables affect the filtering performed by log_filter_internal?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

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

a) value = 1 (error priority)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

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;

A

c) mysql> SELECT @@GLOBAL.log_error_services;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

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

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

26
Q

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.

A

b) whether the server is Windows or Linux

27
Q

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.

A

The General Query Log

28
Q

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

A

TCP/IP (TCP/IP connection established without SSL),

29
Q

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

A

SSL/TLS (TCP/IP connection established with SSL),

30
Q

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

A

Socket (Unix socket file connection),

31
Q

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

A

Named Pipe (Windows named pipe connection)

32
Q

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

A

Shared Memory (Windows shared memory connection).

33
Q

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

A

d)

Passwords in statements written to the general query log are rewritten by the server NOT to occur literally in plain text

34
Q

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

A

b)

The time to acquire the initial locks is not counted as execution time

d)
can control during runtime

35
Q

True or false:

Slow query logs are always found in the /var/lib/mysql/ directory

A

False

Linux /var/lib/mysql/instance_name-slow.log

Windows \ProgramData\MySQL\MySQL Server 8.0\Data\mysql\slow.log

36
Q

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

A

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.

37
Q

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

a) FILE

38
Q

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

A

b) TABLE

39
Q

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

A

c) NONE

40
Q

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

a) and c)

41
Q

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

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

42
Q

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

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.

43
Q

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

A

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

44
Q

True or false

From MySQL 8.0.14, binary log files and relay log files can be encrypted

A

True

From MySQL 8.0.14, binary log files and relay log files can be encrypted,

enable binlog_encryption (default off)

45
Q

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

A

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

46
Q

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.

A

c)

If either of these options is specified and –log-bin is also specified, the option specified later takes precedence.

47
Q

True or false

Compressed transaction payloads remain in a compressed state while they are sent in the replication stream to replicas

A

true

Compressed transaction payloads remain in a compressed state while they are sent in the replication stream to replicas

48
Q

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

A

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

49
Q

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

A

b)

saves storage space both on the originator of the transaction and on the recipient

50
Q

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.

A

c) Trigger events

51
Q

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
A

ROW

52
Q

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.

A

STATEMENT

53
Q

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
A

MIXED

54
Q

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.

A

e)

When a statement refers to one or more system variables.

55
Q

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

A

f) all of them

56
Q

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

A

b) c) d)

changing the logging format on a replication source server does not cause a replica to change its logging format to match

57
Q

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.

A

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

58
Q

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.

A

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&raquo_space; old_relay_log_name.index
shell> mv old_relay_log_name.index new_relay_log_name.index

59
Q

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

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.

60
Q

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

A

c)

written to the file ddl_log.log in MYSQL data directory

61
Q

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.

A

True

62
Q

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

A

d) all updates to the default database, except temp tables

MySQL 8.0 Certification Exam (9 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions