5.Skill Check Monitoring MySQL

Examine this output: | HOST | USER | ROLE | ENABLED | HISTORY | | % | % | % | YES | YES | 1 row in set (#.## sec) Which command produces this?

SELECT * FROM setup_objects LIMIT 5\G;

SELECT * FROM setup_actors LIMIT 5\G;

SELECT * FROM setup_instruments LIMIT 5\G;

SELECT * FROM setup_consumers LIMIT 5\G;

What can be configured by modifying the Performance Schema setup_actors table?

foreground threads that are monitored

stored procedures that are monitored

server metrics that are collected

thread classes that are instrumented

How does a Performance Schema provide insight into database activity? (Choose three)

By analyzing I/O wait statistics

By analyzing historical performance data

By interpreting the Performance Schema to DBAs for diagnostic use cases

By analyzing errors that occur on the system

By analyzing which queries are running

By analyzing the audit record of server activity in log files

Which Performance Schema instruments have no subcomponents? (Choose three)

idle

memory

statement

error

transaction

stage

Which command configures and enables slow query logging?

SET GLOBAL slow_query_log_file=’slow-query.log’;

SET GLOBAL log_output=’TABLE’;

SET GLOBAL slow_query_log=ON;

SET SESSION long_query_time=0.5;

Why is the General Query Log used in MySQL? (Choose two)

To record statements where execution time exceeds a specified threshold

To include update operations stored as row-based binary logs on slave servers

To discover queries with excessive execution time.

To record the time and type of each connection and the process ID of all operations

To record all statements executed against all tables

Which command displays collected server metrics stored in the performance schema?

SELECT * FROM setup_objects LIMIT 5\G;

SELECT * FROM setup_actors LIMIT 5\G;

SELECT * FROM setup_consumers LIMIT 5\G;

SELECT * FROM setup_instruments LIMIT 5\G;

Which MySQL logs can be stored in tables? (Choose two)

Slow query log

Error log

Binary log

General query log

Audit log

====

5.Skill Check Monitoring MySQL

Examine this output: | HOST | USER | ROLE | ENABLED | HISTORY | | % | % | % | YES | YES | 1 row in set (#.## sec) Which command produces this?

SELECT * FROM setup_objects LIMIT 5\G;

SELECT * FROM setup_actors LIMIT 5\G;

SELECT * FROM setup_instruments LIMIT 5\G;

SELECT * FROM setup_consumers LIMIT 5\G;

答案:

SELECT * FROM setup_actors LIMIT 5\G;

What can be configured by modifying the Performance Schema setup_actors table?

foreground threads that are monitored

stored procedures that are monitored

server metrics that are collected

thread classes that are instrumented

答案:

foreground threads that are monitored

说明：
Performance Schema Setup Tables
You configure the Performance Schema by modifying the contents of the setup_% tables.
• setup_actors: Which foreground threads (client connections) are monitored
• setup_objects: Which database objects (tables, stored procedures, triggers, events)
are monitored
• setup_threads: Which thread classes are instrumented
• setup_instruments: Which server metrics the Performance Schema collects
• setup_consumers: Where the instrumented events are stored

How does a Performance Schema provide insight into database activity? (Choose three)

By analyzing I/O wait statistics

By analyzing historical performance data

By interpreting the Performance Schema to DBAs for diagnostic use cases

By analyzing errors that occur on the system

By analyzing which queries are running

By analyzing the audit record of server activity in log files

答案:
By analyzing I/O wait statistics

By analyzing historical performance data

By analyzing which queries are running

说明：
Performance Schema
• A set of in-memory tables that MySQL uses to track performance metrics
– Implemented as the PERFORMANCE_SCHEMA storage engine
— Operates on tables in the performance_schema database
• Helps provide insight into database activity. For example:
– Which queries are running
– I/O wait statistics
– Historical performance data
• Only available if support is configured during the build
– Always available in Oracle binary distributions
– If available, it is enabled by default.
— To enable or disable it explicitly, start the server with the
performance_schema variable set to an appropriate value.

Which Performance Schema instruments have no subcomponents? (Choose three)

idle

memory

statement

error

transaction

stage

答案:
idle
error
transaction

说明：
Top-Level Instrument Components
• idle: An instrumented idle event. This instrument has no subcomponents.
• error: An instrumented error event. This instrument has no subcomponents.
• memory: An instrumented memory event
• stage: An instrumented stage event
• statement: An instrumented statement event
• transaction: An instrumented transaction event. This instrument has no further
components.
• wait: An instrumented wait event

Which command configures and enables slow query logging?

SET GLOBAL slow_query_log_file=’slow-query.log’;

SET GLOBAL log_output=’TABLE’;

SET GLOBAL slow_query_log=ON;

SET SESSION long_query_time=0.5;

答案:
SET GLOBAL slow_query_log=ON;

Why is the General Query Log used in MySQL? (Choose two)

To record statements where execution time exceeds a specified threshold

To include update operations stored as row-based binary logs on slave servers

To discover queries with excessive execution time.

To record the time and type of each connection and the process ID of all operations

To record all statements executed against all tables

答案:
To record the time and type of each connection and the process ID of all operations

To record all statements executed against all tables

说明：
General Query Log
• Is enabled by using the general_log server option
• Records connection information and details about statements received
– Records the time and type of each connection and the process ID of all operations
– Records all statements that are executed against all tables
– Excludes update operations stored as row-based binary log on slave servers
• Grows very quickly
– Enable it for short periods to gather a full record of all activities during those periods
• Has a large overhead
– Not feasible to enable it for long periods on busy production systems

Which command displays collected server metrics stored in the performance schema?

SELECT * FROM setup_objects LIMIT 5\G;

SELECT * FROM setup_actors LIMIT 5\G;

SELECT * FROM setup_consumers LIMIT 5\G;

SELECT * FROM setup_instruments LIMIT 5\G;

答案:

SELECT * FROM setup_instruments LIMIT 5\G;

说明：
Performance Schema Setup Tables
You configure the Performance Schema by modifying the contents of the setup_% tables.
• setup_actors: Which foreground threads (client connections) are monitored
• setup_objects: Which database objects (tables, stored procedures, triggers, events)
are monitored
• setup_threads: Which thread classes are instrumented
• setup_instruments: Which server metrics the Performance Schema collects
• setup_consumers: Where the instrumented events are stored

Which MySQL logs can be stored in tables? (Choose two)

Slow query log

Error log

Binary log

General query log

Audit log

答案：

Slow query log

General query log

说明：
Log File Characteristics
• Can use large amounts of disk space
• Can be stored in files
• Can be stored in tables
– General and slow query logs only
• Can be encrypted
– Audit and binary logs only
• Are written in text format
– Except binary log

4 Skill Check: Configuring MySQL

Which are server-specific groups in the my.cnf option file? (Choose three)

[mysqld_safe]

[mysqld]

[mysql]

[mysqldump]

[server]

[client]

You changed the maximum number of connections in MySQL. In the variable_info table, the max_connections system variable displays a VARIABLE_SOURCE of DYNAMIC. What does it mean?

The max_connections system variable was set from a server-specific mysqld-auto.cnf option file.

The max_connections system variable has not been configured in any startup file.

The max_connections system variable was set from the my.cnf file.

The max_connections system variable was changed at runtime.

Examine this command and results: SET GLOBAL port = 3303; ERROR 1238 (HY000): Variable “port” is a read only variable. How would you resolve this error? (Choose two)

Change the port option to dynamic in the my.cnf file.

Change the port option to dynamic at the command line.

Change the port number in the mysql-auto.cnf file.

Change the modifier from GLOBAL to SESSION in the command.

Change the default port number in the my.cnf file.

Which command displays the configuration options for a running MySQL server? (Choose two)

# mysqld –verbose –help

mysql> SHOW GLOBAL VARIABLES;

# mysqladmin -uroot -p variables

# systemctl status mysqld

# mysql –print-defaults

Examine this command which executes successfully: SET PERSIST max_connections=99; Which command displays the content of the file that includes this parameter change?

# cat /labs/slap-test-updates.sh

mysql> \! cat /var/lib/mysql/slow-query.log

# cat /labs/multi.cnf

mysql> \! cat /var/lib/mysql/mysqld-auto.cnf | python -m json.tool

Which system variables have both global and session scope in MySQL? (Choose two)

error_count

max_connections

innodb_buffer_pool_size

max_join_size

sort_buffer_size

===

4 Skill Check: Configuring MySQL

Which are server-specific groups in the my.cnf option file? (Choose three)

[mysqld_safe]

[mysqld]

[mysql]

[mysqldump]

[server]

[client]

答案：
[mysqld_safe]
[mysqld]
[server]

You changed the maximum number of connections in MySQL. In the variable_info table, the max_connections system variable displays a VARIABLE_SOURCE of DYNAMIC. What does it mean?

The max_connections system variable was set from a server-specific mysqld-auto.cnf option file.

The max_connections system variable has not been configured in any startup file.

The max_connections system variable was set from the my.cnf file.

The max_connections system variable was changed at runtime.

答案：
The max_connections system variable was changed at runtime.

Examine this command and results: SET GLOBAL port = 3303; ERROR 1238 (HY000): Variable “port” is a read only variable. How would you resolve this error? (Choose two)

Change the port option to dynamic in the my.cnf file.

Change the port option to dynamic at the command line.

Change the port number in the mysql-auto.cnf file.

Change the modifier from GLOBAL to SESSION in the command.

Change the default port number in the my.cnf file.

答案：
Change the port option to dynamic in the my.cnf file.
Change the port option to dynamic at the command line.

Which command displays the configuration options for a running MySQL server? (Choose two)

# mysqld –verbose –help

mysql> SHOW GLOBAL VARIABLES;

# mysqladmin -uroot -p variables

# systemctl status mysqld

# mysql –print-defaults

答案：
mysql> SHOW GLOBAL VARIABLES;
# mysqladmin -uroot -p variables

Examine this command which executes successfully: SET PERSIST max_connections=99; Which command displays the content of the file that includes this parameter change?

# cat /labs/slap-test-updates.sh

mysql> \! cat /var/lib/mysql/slow-query.log

# cat /labs/multi.cnf

mysql> \! cat /var/lib/mysql/mysqld-auto.cnf | python -m json.tool

答案：
mysql> \! cat mysql> \! cat /var/lib/mysql/mysqld-auto.cnf | python -m json.tool

Which system variables have both global and session scope in MySQL? (Choose two)

error_count

max_connections

innodb_buffer_pool_size

max_join_size

sort_buffer_size

答案：
sort_buffer_size
max_join_size

3.Skill Check: Understanding MySQL Architecture

How do you set the number of InnoDB buffer pool instances to 12?

Execute SET GLOBAL innodb_buffer_pool_size=12000 1024 1024;

Execute SET GLOBAL innodb_buffer_pool_instances=12;

Add innodb_buffer_pool_instances=12 to /etc/my.cnf

Add innodb_buffer_pool_size=12G to /etc/my.cnf

Which are true about the MyISAM storage engine? (Choose three)

It supports table-level locking

It supports spatial data types and indexes

It supports storing row data and indexes in memory

It supports all data types except spatial data types

It supports FULLTEXT indexes

Which storage engine supports INSERT and SELECT but not DELETE, REPLACE, or UPDATE commands?

ARCHIVE

MyISAM

BLACKHOLE

MEMORY

You must create a table with these attributes: 1. Table name: sqltab1 2. Stored in the general tablespace Which command will do this?

CREATE TABLE sqltab1(a INT PRIMARY KEY, b CHAR(4));

CREATE TABLESPACE myts ADD DATAFILE ‘myts_data1.ibd’;

CREATE TABLE sqltab1(a INT PRIMARY KEY, b CHAR(4)) DATA DIRECTORY=’/datadir2′;

CREATE TABLE sqltab1(a INT PRIMARY KEY) TABLESPACE=myts;

You must create a table with these attributes: 1. Table name: sqltab1 2. A single integer column d 3. Stored in its own default tablespace in the /tablespaces directory Which command will do this?

mysql> CREATE TABLE sqltab1 (d int) TABLESPACE=general;

mysql> CREATE TABLE sqltab1 (d int) TABLESPACE=external;

mysql> CREATE TABLESPACE external ADD DATAFILE ‘/tablespaces/sqltab1.ibd’;

mysql> CREATE TABLE sqltab1 (d INT) DATA DIRECTORY=’/tablespaces’;

Which features are supported by InnoDB? (Choose two)

Hash indexes

Clustered indexes

B-tree indexes

Cluster database support

T-tree indexes

Which MySQL storage engines are transactional and support foreign keys? (Choose two)

BLACKHOLE

NDBCLUSTER

MERGE

InnoDB

MyISAM

====

3.Skill Check: Understanding MySQL Architecture

How do you set the number of InnoDB buffer pool instances to 12?

Execute SET GLOBAL innodb_buffer_pool_size=12000 1024 1024;

Execute SET GLOBAL innodb_buffer_pool_instances=12;

Add innodb_buffer_pool_instances=12 to /etc/my.cnf

Add innodb_buffer_pool_size=12G to /etc/my.cnf

答案:
Add innodb_buffer_pool_instances=12 to /etc/my.cnf

Which are true about the MyISAM storage engine? (Choose three)

It supports table-level locking

It supports spatial data types and indexes

It supports storing row data and indexes in memory

It supports all data types except spatial data types

It supports FULLTEXT indexes

答案:
It supports table-level locking
It supports spatial data types and indexes
It supports FULLTEXT indexes

说明:
MyISAM Storage Engine
• Is used in many legacy systems
– Was the default storage engine before MySQL 5.5
• Is fast and simple, but subject to table corruption if server crashes
– Use REPAIR TABLE to recover corrupted MyISAM tables.
• Supports FULLTEXT indexes
• Supports spatial data types and indexes
• Supports table-level locking
• Supports raw table-level backup and recovery because of the simple file format
• No transactional support
• No support for table partitioning in MySQL 8.0 as compared to MySQL 5.7.

说明:
MySQL RPM Installation Process
• The RPM installation performs the following tasks:
– Extracts RPM files to their default locations
– Registers SysV init or systemd startup scripts
– Sets up the mysql user and group in the operating system
— The MySQL server process runs as the mysql user.
• When you start the service for the first time using service mysqld
start or systemctl start mysqld, MySQL:
– Creates the data directory and the default my.cnf file
— These files are owned by the mysql user and group.
– Creates the default root@localhost account
– Sets up a random temporary password for the root account and
writes that password to the error log file (/var/log/mysqld.log)
— You must change the password before you can use MySQL.

Which storage engine supports INSERT and SELECT but not DELETE, REPLACE, or UPDATE commands?

ARCHIVE

MyISAM

BLACKHOLE

MEMORY

答案:
ARCHIVE

说明:
ARCHIVE Storage Engine
The ARCHIVE storage engine is used for storing large volumes of data in a compressed
format, allowing for a very small footprint. It has these primary characteristics:
• Does not support indexes
• Supports INSERT and SELECT, but not DELETE, REPLACE, or UPDATE
• Supports ORDER BY operations and BLOB columns
• Accepts all data types except spatial data types
• Uses row-level locking
• Supports AUTO_INCREMENT columns
• Disabled by default, need to be enabled to use

You must create a table with these attributes: 1. Table name: sqltab1 2. Stored in the general tablespace Which command will do this?

CREATE TABLE sqltab1(a INT PRIMARY KEY, b CHAR(4));

CREATE TABLESPACE myts ADD DATAFILE ‘myts_data1.ibd’;

CREATE TABLE sqltab1(a INT PRIMARY KEY, b CHAR(4)) DATA DIRECTORY=’/datadir2′;

CREATE TABLE sqltab1(a INT PRIMARY KEY) TABLESPACE=myts;

答案:
CREATE TABLE sqltab1(a INT PRIMARY KEY, b CHAR(4)) TABLESPACE=myts;

You must create a table with these attributes: 1. Table name: sqltab1 2. A single integer column d 3. Stored in its own default tablespace in the /tablespaces directory Which command will do this?

mysql> CREATE TABLE sqltab1 (d int) TABLESPACE=general;

mysql> CREATE TABLE sqltab1 (d int) TABLESPACE=external;

mysql> CREATE TABLESPACE external ADD DATAFILE ‘/tablespaces/sqltab1.ibd’;

mysql> CREATE TABLE sqltab1 (d INT) DATA DIRECTORY=’/tablespaces’;
答案:
mysql> CREATE TABLE sqltab1 (d INT) DATA DIRECTORY=’/tablespaces’;

Which features are supported by InnoDB? (Choose two)

Hash indexes

Clustered indexes

B-tree indexes

Cluster database support

T-tree indexes

答案:

Clustered indexes
B-tree indexes

Which MySQL storage engines are transactional and support foreign keys? (Choose two)

BLACKHOLE

NDBCLUSTER

MERGE

InnoDB

MyISAM
答案:
InnoDB
NDBCLUSTER

说明:
The two MySQL storage engines that are transactional and support foreign keys are:
InnoDB
Transactional: Supports ACID-compliant transactions (COMMIT, ROLLBACK).
Foreign Keys: Enforces referential integrity with foreign key constraints.

NDBCLUSTER (MySQL Cluster)
Transactional: Provides distributed transactions across nodes.
Foreign Keys: Fully supports foreign keys in a clustered environment.

BLACKHOLE: Discards writes (non-transactional), no foreign keys.
MERGE: Combines MyISAM tables (non-transactional), no foreign keys.
MyISAM: Non-transactional, no foreign key support (only table-level locking).