OCP题库

试题 1:

Choose two.

Your MySQL server was upgraded from an earlier major version. The sales database contains three tables, one of which is the transactions table, which has 4 million rows. You are running low on disk space on the datadir partition and begin to investigate. Examine these commands and output: (见图片)

Which two statements are true?

A) The transactions table was created with innodb_file_per_table = OFF. [正确]
B) Truncating the sales and leads table will free up disk space. [正确]
C) Executing SET GLOBAL innodb_row_format = COMPRESSED and then ALTER TABLE transactions will free up disk space. [错误] 没有这个变量
D) Executing ALTER TABLE transactions will enable you to free up disk space. [错误]
E) Truncating the transactions table will free up the most disk space. [错误]

试题 2:

*************************** 1. row ***************************
EXPLAIN:
-> Sort: <temporary>.Population DESC (actual time=0.306..8.431 rows=125 loops=1)
 -> Stream results (actual time=0.145..8.033 rows=125 loops=1)
  -> Nested loop inner join (cost=241.12 rows=205) (actual time=0.141..7.787 rows=125 loops=1)
   -> Filter: (world.country.Continent = 'Asia') (cost=25.40 rows=34) (actual time=0.064..0.820 rows=51 loops=1)
    -> Table scan on country (cost=25.40 rows=239) (actual time=0.059..0.359 rows=239 loops=1)
   -> Filter: (world.city.Population > 1000000) (cost=4.53 rows=6) (actual time=0.030..0.131 rows=2 loops=51)
    -> Index lookup on city using CountryCode (CountryCode=world.country.`Code`) (cost=4.53 rows=18) (actual time=0.023..0.096 rows=35 loops=51)

1 row in set (0.0094 sec)

A) The country table is accessed as the first table, and then joined to the city table. [正确]
B) 35 rows from the city table are included in the result. [错误]
C) The optimizer estimates that 51 rows in the country table have Continent = ‘ Asia ‘. [错误]
D) It takes more than 8 milliseconds to sort the rows. [错误]
E) The query returns exactly 125 rows. [正确]

试题 3:

Choose four.

A newly deployed replication master database has a 10/90 read to write ratio. The complete dataset is currently 28G but will never fluctuate(波动) beyond +-10%. The database storage system consists of two locally attached PCI-E Enterprise grade disks (mounted as /data1 and /data2). The server is dedicated to this MySQL Instance. System memory capacity is 64G. The my.cnf file contents are displayed here:

[mysqld]
datadir = /data1/
innodb_buffer_pool_size = 28G
innodb_log_file_size = 150M

Which four changes provide the most performance improvement, without sacrificing(牺牲) data integrity?

A) innodb-doublewrite = off [错误]
B) innodb_log_group_home_dir = /data2/ [正确]
C) innodb_log_file_size = 1G [正确]
D) innodb_undo_directory = /dev/shm [错误]
E) log-bin = /data2/ [正确]
F) innodb_flush_log_at_trx_commit = 0 [错误]
G) sync_binlog = 0 [错误]
H) innodb_buffer_pool_size = 32G [正确]
I) disable-log-bin [错误]

答案分析： 不牺牲数据完整性的情况下提供了最大的性能改进

试题 4:

Which two actions will secure a MySQL server from network-based attacks?

A) Use MySQL Router to proxy connections to the MySQL server. [错误]
B) Place the MySQL instance behind a firewall. [正确]
C) Use network file system (NFS) for storing data. [错误]
D) Change the listening port to 3307. [错误]
E) Allow connections from the application server only. [正确]

试题 5:

Choose four.

You must store connection parameters for connecting a Linux-based MySQL client to a remote Windows-based MySQL server listening on port 3309. Which four methods can be used to configure user, host, and database parameters?

A) Embed login information into the SSH tunnel definition. [错误]
B) Execute mysql_config_editor to configure the user connection. [正确]
C) Configure ~/.my.cnf. [正确]
D) Execute the mysqladmin command to configure the user connection. [错误]
E) Execute the command in a bash script. [正确]
F) Configure environment variables. [正确]
G) Define a UNIX socket. [错误]
H) Use the usermod program to store static user information. [错误]
I) Configure ~/.ssh/config for public key authentication. [错误]

答案: you want to specify the TCP/IP port number using the MYSQL_TCP_PORT variable.

试题 6:

Choose two.

Examine this statement, which executes successfully:

CREATE TABLE employees (
  emp_no int unsigned NOT NULL,
  birth_date date NOT NULL,
  first_name varchar(14) NOT NULL,
  last_name varchar(16) NOT NULL,
  hire_date date NOT NULL,
  PRIMARY KEY (emp_no)
) ENGINE = InnoDB;

Now examine this query:

1
2
3

SELECT emp_no, first_name, last_name, birth_date
FROM employees
WHERE MONTH(birth_date) = 4;

You must add an index that can reduce the number of rows processed by the query. Which two statements can do this?

A) ALTER TABLE employees ADD INDEX ((CAST(birth_date ->> ‘$.month’ AS unsigned))); [错误]
B) ALTER TABLE employees ADD INDEX (birth_date DESC); [错误]
C) ALTER TABLE employees ADD COLUMN birth_month tinyint unsigned GENERATED ALWAYS AS (MONTH(birth_date)) VIRTUAL NOT NULL, ADD INDEX (birth_month); [正确]
D) ALTER TABLE employees ADD INDEX (birth_date); [错误]
E) ALTER TABLE employees ADD COLUMN birth_month tinyint unsigned GENERATED ALWAYS AS (birth_date ->> ‘$.month’) VIRTUAL NOT NULL, ADD INDEX (birth_month); [错误]
F) ALTER TABLE employees ADD INDEX ((MONTH(birth_date))); [正确]

试题 7:

Choose two.

Which two queries are examples of successful SQL injection attacks?

A) SELECT user, passwd FROM members WHERE user = ‘?’; INSERT INTO members (‘user’, ‘passwd’) VALUES (‘bob@example.com‘, ‘secret’); – ‘; [正确]
B) SELECT user, phone FROM customers WHERE name = ‘\; DROP TABLE users; – ‘; [正确]
C) SELECT id, name FROM user WHERE user.id = (SELECT members.id FROM members); [错误]
D) SELECT id, name FROM user WHERE id = 23 OR id = 32 OR 1=1; [错误]
E) SELECT id, name FROM user WHERE id = 23 OR id = 32 AND 1=1; [错误]
F) SELECT email, passwd FROM members WHERE email = ‘INSERT INTO members('email', 'passwd') VALUES ('bob@example.com', 'secret'); – ‘; [错误]

答案: F 在注入的语句前面没有分号。

试题 8:

Choose two.

Which two tools are available to monitor the global status of InnoDB locking?

A) SHOW ENGINE INNODB STATUS; [正确] (核对后选这个)
B) SHOW TABLE STATUS; [错误]
C) INFORMATION_SCHEMA.INNODB_TABLESTATS. [错误]
D) SHOW STATUS; [错误]
E) INFORMATION_SCHEMA.STATISTICS [错误]
F) INFORMATION_SCHEMA.INNODB_METRICS [错误] (核对后选这个)

试题 9:

Choose two.

Which two authentication plugins require the plain text client plugin for authentication to work?

A) LDAP authentication [正确]
B) SHA256 authentication [错误]
C) Windows Native authentication [错误]
D) PAM authentication [正确]
E) MySQL Native Password [错误]
F) LDAP SASL authentication [错误]

答案: https://dev.mysql.com/doc/refman/8.0/en/cleartext-pluggable-authentication.html

试题 10:

Which three are types of information stored in the MySQL data dictionary?

A) InnoDB buffer pool LRU management data [错误]
B) performance metrics [错误]
C) access control lists [正确]
D) server runtime configuration [错误]
E) server configuration rollback [错误]
F) view definitions [正确]
G) stored procedure definitions [正确]

(Note: The question appears twice with slightly different options. Formatting the second instance as well.)

Which four are types of information stored in the MySQL data dictionary?

A) performance metrics [错误]
B) table definitions [正确]
C) access control lists [正确]
D) view definitions [正确]
E) server runtime configuration [错误]
F) server configuration rollback [错误]
G) stored procedure definitions [正确]
H) InnoDB buffer pool LRU management data [错误]

试题 12:

Choose two.

Examine this SQL statement:

1	mysql> GRANT r_read@localhost TO mark WITH ADMIN OPTION;

Which two are true?

A) Mark can grant the privileges assigned to the r_read@localhost role to another user. [错误] (mark 只能把角色授予给其他人，而不能把因该角色而获得的权限授予给其他用户)
B) Mark can grant the r_read@localhost role to another user. [正确]
C) ADMIN OPTION causes the role to be activated by default. [错误]
D) Mark must connect from localhost to activate the r_read@localhost role. [错误]
E) Mark can revoke the r_read@localhost role from another role. [正确]
F) ADMIN OPTION allows Mark to drop the role. [错误]

试题 13:

Choose two.

Which two statements are true about general tablespaces?

A) General tablespaces support temporary tables. [错误]
B) Dropping a table from a general tablespace releases the space back to the operating system. [错误]
C) A new table can be created explicitly in a general tablespace. [正确]
D) An existing table can be moved into a general tablespace. [正确]
E) A general tablespace can have multiple data files. [错误]

试题 14:

(Note: scale up 垂直扩展在现有服务器中增加硬件资源，以提高性能和处理能力)

Which three methods are part of a ‘scale up’ approach to capacity planning?

A) adding additional MySQL servers to the existing host [错误]
B) adding more CPU power [正确]
C) adding a replication slave [错误]
D) adding more RAM [正确]
E) adding more storage to your disk array [正确]
F) sharding the server into a parallel server farm [错误]
G) adding a new node to InnoDB Cluster [错误]

试题 15:

Choose three.

A user wants to connect without entering his or her username and password on the Linux command prompt. Which three locations can be used to store the user’s mysql credentials to satisfy this requirement?

A) $HOME/.mysqlrc file [错误]
B) /etc/my.cnf file [正确]
C) DATADIR/mysqld-auto.cnf file [错误]
D) $HOME/.my.cnf file [正确]
E) $HOME/.mylogin.cnf file [正确]
F) $MYSQL_HOME/my.cnf file [错误]
G) $HOME/.mysql/auth/login file [错误]

答案: E mysql_config_editor 出可以给指定的连接和密码生成一个加密文件.mylogin.cnf，默认位于当前用户家目录下。 F $MYSQL_HOME/my.cnf Server-specific options (server only)

试题 16:

Choose two.

Examine the modified output:

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 1612

Seconds_Behind_Master value is steadily growing. What are two possible causes?

A) The master is producing a large volume of events in parallel but the slave is processing them serially. [正确]
B) This value shows only I/O latency and is not indicative of the size of the transaction queue. [错误]
C) One or more large tables do not have primary keys. [错误]
D) The master is most probably too busy to transmit data and the slave needs to wait for more data. [正确]
E) The parallel slave threads are experiencing lock contention. [错误]

试题 17:

Choose two.

Which two are true about binary logs used in asynchronous replication?

A) The master connects to the slave and initiates log transfer. [错误]
B) They contain events that describe all queries run on the master. [错误]
C) They contain events that describe database changes on the master. [正确]
D) They are pulled from the master to the slave. [正确]
E) They contain events that describe only administrative commands run on the master. [错误]

试题 18:

You have appropriate privileges and are about to shut down a running MySQL server process on Oracle Linux 7. Which three are valid methods that will shut down the MySQL server?

A) mysqld_safe -S /tmp/mysql.sock SHUTDOWN [错误]
B) kill mysqld_safe [错误]
C) mysqladmin shutdown [正确]
D) mysql -S /tmp/mysql.sock –shutdown [错误]
E) mysqld_safe –shutdown [错误]
F) systemctl stop mysqld [正确]
G) mysql> SHUTDOWN; [正确]

试题 19:

Choose two.

Examine this MySQL Shell command:

1	dba.rebootClusterFromCompleteOutage()

Which two statements are true?

A) It stops and restarts all InnoDB Cluster instances and initializes the metadata. [错误]
B) It only stops and restarts all InnoDB Cluster instances. [错误]
C) It is not mandatory that all instances are running and reachable before running the command. [正确]
D) It performs InnoDB Cluster instances rolling restart. [正确]
E) It reconfigures InnoDB Cluster if the cluster was stopped. [错误]
F) It picks the minimum number of instances necessary to rebuild the quorum and reconfigures InnoDB Cluster. [错误]
G) It only starts all InnoDB Cluster instances. [错误]

答案: 参考文档 MySQL 8.0 for Database Administrators StudentGuide 2.pdf 页数 P431

试题 20:

Choose two.

Examine this command and output: (见下图)

<html><body><table><tr><td colspan="10">root@dbhost:/var/lib/mysql#ls-al</td></tr><tr><td>total540 drwxrwxr-x1mysqlmysql</td><td></td><td></td><td></td><td>4096</td><td>Aug</td><td>22</td><td>14:07.</td><td></td><td></td></tr><tr><td></td><td></td><td>root root</td><td></td><td>4096</td><td>May</td><td>22</td><td>00:42.</td><td></td><td></td></tr><tr><td>drwxr-xr-x1</td><td></td><td></td><td></td><td>56</td><td></td><td>20</td><td></td><td>13:58auto.cnf</td><td></td></tr><tr><td>-rw-r-----</td><td></td><td></td><td>1mysqlmysql</td><td>4096</td><td>Aug Aug</td><td></td><td></td><td>21 10:28 accounting</td><td></td></tr><tr><td>drwxr-xr-x</td><td></td><td></td><td>1mysqlmysql</td><td>1112</td><td></td><td></td><td></td><td>13:58ca.pem</td><td></td></tr><tr><td>-rw-r--r--</td><td></td><td></td><td>1mysqlmysql</td><td></td><td></td><td>Aug20</td><td></td><td></td><td></td></tr><tr><td>-rw-r-</td><td>1</td><td></td><td>mysqlmysql</td><td>172040</td><td></td><td>Aug22</td><td></td><td></td><td>14:07 ib_buffer_pool</td></tr><tr><td>-rw-r-</td><td>1</td><td></td><td>mysqlmysql</td><td>12582919</td><td>Aug</td><td>22</td><td></td><td>14:07ibdata1</td><td></td></tr><tr><td>-rw-r-</td><td>1</td><td></td><td>mysqlmysql</td><td>50331648</td><td>Aug</td><td></td><td></td><td></td><td>2214:07ib_logfile0</td></tr><tr><td>-rw-r-</td><td>1</td><td></td><td>mysqlmysql</td><td>50331648</td><td>Aug</td><td></td><td></td><td></td><td>2013:47ib_logfile1</td></tr><tr><td>-rw-r-</td><td>1</td><td></td><td>mysqlmysql</td><td>292292</td><td>Aug</td><td></td><td></td><td>2214:07ibtmp1</td><td></td></tr><tr><td>drwxr-x--</td><td>1</td><td></td><td>mysqlusers</td><td>4096 64064</td><td>Aug Aug</td><td></td><td></td><td>2013:59mysq1</td><td></td></tr><tr><td>-rw-r-</td><td></td><td></td><td>1mysqlmysql</td><td>4096</td><td>Aug</td><td></td><td>2013:59</td><td></td><td>2215:18mysq1-error.log</td></tr><tr><td>drwxr-x--</td><td>1</td><td></td><td>mysqlmysql</td><td>1680</td><td></td><td>Aug20</td><td>13:59</td><td></td><td>performance_schema</td></tr><tr><td>-rw-rw--</td><td>1</td><td></td><td>mysqlmysql</td><td></td><td>452 Aug</td><td>20</td><td>13:59</td><td></td><td>private_key.pem</td></tr><tr><td>-rw-r--r--</td><td>1 1</td><td></td><td>mysql mysql mysqlmysql</td><td>1112</td><td>Aug</td><td>20</td><td></td><td>13:58</td><td>public_key.pem server-cert.pem</td></tr><tr><td>-rw-r--r-- rw-</td><td>1</td><td></td><td>mysqlmysql</td><td></td><td>1680</td><td>Aug</td><td>20</td><td>13:58</td><td>server-key.pem</td></tr><tr><td>drwxr-x---</td><td></td><td></td><td>mysqlmysql</td><td></td><td>4096</td><td>Aug 20</td><td></td><td>13:59</td><td></td></tr><tr><td></td><td>1</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>sys</td></tr></table></body></html>

Which two options will improve the security of the MySQL instance?

A) Remove the world read/execute privilege from the accounting directory. [正确]
B) Remove world read privileges from the public_key.pem file. [错误]
C) Change the group ownership of the mysql directory to the mysql user group. [错误]
D) Change the parent directory owner and group to mysql. [错误]
E) Remove world read privileges from the server-cert.pem certificate file. [错误]
F) Remove group read/write privileges from the private_key.pem file. [正确]

答案: c 把 root 属主改成 mysql 应该是没有必要的

试题 21:

Choose two.

Which two statements are true about general tablespaces?

A) General tablespaces support temporary tables. [错误]
B) Dropping a table from a general tablespace releases the space back to the operating system. [错误]
C) An existing table can be moved into a general tablespace. [正确]
D) A general tablespace can have multiple data files. [错误]
E) A new table can be created explicitly in a general tablespace. [正确]

答案: Creation of temporary general tablespaces is not supported. General tablespaces do not support temporary tables. InnoDB general tablespaces support only one data file per tablespace. You cannot add a second data file to an existing general tablespace. 参考文档 MySQL 8.0 for Database Administrators StudentGuide 1.pdf 页数 P154

试题 22:

Choose three.

Examine this command, which executes successfully:

1	cluster.addInstance('<user>@<host>:<port>', {recoveryMethod: 'clone'})

Which three statements are true?

A) It is always slower than recoveryMethod: ‘incremental’. [错误]
B) InnoDB tablespaces outside the datadir are able to be cloned. [正确]
C) A target instance must exist, then it will be provisioned with data from an instance already in the cluster and joined to the cluster. [正确]
D) The account used to perform this recovery needs the BACKUP_ADMIN privilege. [正确]
E) A new instance is installed, initialized, and provisioned with data from an instance already in the cluster and joined to the cluster. [错误]
F) InnoDB redo logs must not rotate for the duration of the execution; otherwise, the recovery will fail. [错误]

答案: 参考文档 MySQL 8.0 for Database Administrators StudentGuide 2.pdf 页数 P419 Cloning Remote Data User-created InnoDB tables and tablespaces that reside outside of the data directory on the donor MySQL server instance are cloned to the same path on the recipient MySQL server instance.

试题 23:

Choose three.

Which three sets of item information are visible in the mysql system database?

A) time zone information and definitions [正确]
B) help topics [正确]
C) plugins [正确]
D) audit log events [错误]
E) performance monitoring information [错误]
F) rollback segments [错误]
G) information about table structures [错误]

答案: mysql Database MySQL stores the mysql system database on disk just like any other database. The mysql database contains information such as users, privileges, plugins, help topics, and timezone data. All the InnoDB tables in the mysql system database are stored in the mysql general tablespace (mysql.ibd) at the data directory level. Non InnoDB tables are stored in the mysql database Directory https://dev.mysql.com/doc/refman/8.0/en/system-schema.html

试题 24:

Which two situations will cause the binary log to rotate?

A) FLUSH HOSTS executed [错误]
B) max_binlog_size exceeded [正确]
C) max_binlog_cache_size exceeded [错误]
D) SET sql_log_bin=1 executed [错误]
E) SET sync_binlog=1 executed [错误]
F) FLUSH LOGS executed [正确]

试题 25:

Choose three.

Which three statements are true about MySQL replication?

A) Each slave must have its own MySQL user for replication. [错误]
B) A replication user must have the SELECT privilege for all tables that need to be replicated. [错误]
C) Each instance in a replication topology must have a unique server ID. [正确]
D) Any instance can have multiple slaves, but it can have only one master. [错误]
E) Binary logs contain only transactions originating from a single MySQL instance. [错误]
F) Replication can use only TCP/IP connections. [正确]
G) Binary logging must be enabled on the master in order to replicate to other instances. [正确]

试题 26:

The data in this instance transient; no backup or replication will be required. It is currently under performing. The database size is static and including indexes is 19G. Total system memory is 32G. After profiling the system, you highlight these MySQL status and global variables:

Com_rollback = 85408355
Com_commit = 1234342
Innodb_buffer_pool_pages_free = 163840
[mysqld]
Buffer_pool_size = 20G
Innodb_flush_log_at_trx_commit = 2
Disable-log-bin

The OS metrics indicate that disk is a bottleneck. Other variables retain their default values. Which two changes will provide the most benefit to the instance?

A) sync_binlog = 0 [错误]
B) buffer_pool_size = 24G [正确]
C) innodb_flush_log_at_trx_commit = 1 [错误]
D) innodb_doublewrite = 0 [正确]
E) max_connections = 10000 [错误]
F) innodb_log_file_size = 1G [错误]

试题 27:

Choose two.

User fwuser@localhost is registered with the MySQL Enterprise Firewall and has been granted privileges for the SAKILA database. Examine these commands that you executed and the results:

1	mysql> SELECT MODE FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS WHERE USERHOST = 'fwuser@localhost';

(见下图)

You then execute this command:

1	mysql> CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'RESET');

Which two are true?

1	mysql> SELECT RULE FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST WHERE USERHOST = 'fwuser@localhost';

A) The fwuser@localhost account is removed from the mysql.user table. [错误]
B) The information_schema.MYSQL_FIREWALL_WHITELIST table is truncated. [错误]
C) The whitelist of the fwuser@localhost account is truncated. [正确]
D) The mysql.firewall_users table is truncated. [错误]
E) The firewall resets all options to default values. [错误]
F) The fwuser@localhost account mode is set to DETECTING. [错误]
G) The fwuser@localhost account mode is set to OFF. [正确]

答案: When a profile is assigned any of the preceding mode values, the firewall stores the mode in the profile. Firewall mode-setting operations also permit a mode value of RESET, but this value is not stored: setting a profile to RESET mode causes the firewall to delete all rules for the profile and set its mode to OFF. RESET 概要文件设置为 RESET 模式会导致防火墙删除概要文件的所有规则并将其模式设置为 OFF。设置为 RESET 时，除了关闭 Firewall 保护，同时也会将该账号之前训练学习的白名单全部清空，这样下次再想采用 Firewall 保护就需要重头开始了，除非再也不用了，否则不建议这么做 https://dev.mysql.com/doc/refman/8.0/en/firewall-usage.html
b 选项指定的表是对的，但其中保存了全部的 profile。

试题 28:

Choose two.

Examine this statement and output:

mysql> SHOW GRANTS FOR jsmith;
+----------------------------------------------------------+
| Grants for jsmith@%                                      |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jsmith'@'%'                       |
| GRANT UPDATE (Name) ON `world`.`country` TO 'jsmith'@'%' |
+----------------------------------------------------------+
2 rows in set (0.00 sec)

Which two SQL statements can jsmith execute?

A) UPDATE world.country SET Name = CONCAT(‘New ‘, Name); [错误]
B) UPDATE world.country SET Name = ‘one’ LIMIT 1; [正确]
C) UPDATE world.country SET Name = ‘first’ ORDER BY Name LIMIT 1; [错误]
D) UPDATE world.country SET Name = ‘all’; [正确]
E) UPDATE world.country SET Name = ‘new’ WHERE Name = ‘old’; [错误]

答案: 权限里缺少 select 语句

试题 29:

Choose two.

There are five MySQL instances configured with a working group replication. Examine the output of the group members:

1	mysql> SELECT MEMBER_ID, MEMBER_STATE FROM performance_schema.replication_group_members;

(见下图)

Which two statements are true about network partitioning in the cluster?

A) The group replication will buffer the transactions on the online nodes until the unreachable nodes return online. [错误]
B) A manual intervention to force group members to be only the working two instances is required. [正确]
C) The cluster will shut down to preserve data consistency. [错误]
D) There could be both a 2 node and 3 node group replication still running, so shutting down group replication and diagnosing the issue is recommended. [正确]
E) The cluster has built-in high availability and updates group_replication_ip_whitelist to remove the unreachable nodes. [错误]

试题 30:

Choose two.

Which two statements are true about InnoDB data-at-rest encryption?

A) It supports all indexes transparently. [正确]
B) It decrypts data for use in memory. [正确]
C) It supports only non-blob datatypes. [错误]
D) It does not support the transportable tablespaces feature. [错误]
E) It enforces encryption from disk to memory and over network transmission. [错误]

答案: 参考文档 MySQL 8.0 for Database Administrators StudentGuide 2.pdf 页数 P77

试题 31:

Choose three.

Which three statements are true about MySQL Enterprise Firewall?

A) On Windows systems, it is controlled and managed using the Windows Internet Connection Firewall control panel. [错误]
B) System tables named firewall_users and firewall_whitelist in the mysql database provide persistent storage of firewall data. [正确]
C) It is available only in MySQL Enterprise versions. [正确]
D) It provides INFORMATION_SCHEMA tables that enable views into firewall data. [正确]
E) Firewall functionality is dependent on SHA-256 and ANSI-specific functions built in to the mysql.firewall table. [错误]
F) It shows only notifications for blocked connections, which originated outside of your network’s primary domain. [错误]

答案: 参考文档 MySQL 8.0 for Database Administrators StudentGuide 2.pdf 页数 P91

试题 32:

Choose two.

Which two storage engines provide a view of the data consistent with the storage system at any moment?

A) InnoDB [正确]
B) ARCHIVE [错误]
C) MyISAM [错误]
D) MEMORY [错误]
E) NDB [正确]

试题 33:

Choose three.

Which three are requirements for a secure MySQL Server environment?

A) Minimize the number of non-MySQL Server-related processes running on the server host. [错误]
B) Restrict the number of OS users that have access at the OS level. [正确]
C) Ensure appropriate file system privileges for OS users and groups. [正确]
D) Keep the entire software stack on one OS host. [正确]
E) Encrypt the file system to avoid needing exact file-system permissions. [错误]
F) Run MySQL server as the root user to prevent incorrect sudo settings. [错误]

试题 34:

Choose two.

Examine this list of MySQL data directory binary logs:

binlog.000001
binlog.000002
.......
binlog.000289
binlog.000300
binlog.000301
binlog.index

Now examine this command, which executes successfully:

1	mysqldump --delete-master-logs --all-databases > /backup/db_backup.sql

Which two are true?

A) All databases are backed up to the output file. [正确]
B) All non-active binary logs are removed from the master. [正确]
C) All binary logs are backed up and then deleted. [错误]
D) All binary logs are deleted from the master. [错误]
E) All databases, excluding master metadata, are backed up to the output file. [错误]
F) All details regarding deleted logs and master metadata are captured in the output file. [错误]

答案: 参考文档 MySQL 8.0 for Database Administrators StudentGuide 2.pdf 页数 P293

试题 35:

You want to install and configure MySQL on Linux server with tarball binaries in the /app/mysql/ directory, where the bin directory is found at /app/mysql/bin and the data directory at /app/data. Which two parameters are required to configure the MySQL instance?

A) The configuration basedir = /app/mysql is needed. [正确]
B) The configuration datadir = /app/data is needed. [正确]
C) The configuration log-bin = /app/data is needed. [错误]
D) The configuration datadir = /app/mysql/data is needed [错误]
E) The configuration innodb_log_group_home_dir = /datadir is needed. [错误]
F) The configuration basedir = /app/mysql/bin is needed. [错误]

试题 36:

A valid raw backup of the shop.customers MyISAM table was taken. You must restore the table. You begin with these steps:

Confirm that secure_file_priv = '/var/tmp'
mysql> DROP TABLE shop.customers;
shell> cp /backup/customers.MY* /var/lib/mysql/shop/

Which two actions are required to complete the restore? (Choose two.)

A) shell> cp /backup/customers.sdi /var/tmp [正确]
B) shell> cp /backup/customers.sdi /var/lib/mysql/shop/ [错误]
C) mysql> SOURCE '/var/tmp/customers.sdi' [错误]
D) mysql> IMPORT TABLE FROM '/var/tmp/customers.sdi' [正确]
E) shell> cp /backup/customers.frm /var/lib/mysql/shop/ [错误]
F) mysql> IMPORT TABLE FROM '/var/lib/mysql/shop/customers.sdi' [错误]
G) mysql> ALTER TABLE shop.customers IMPORT TABLESPACE [错误]
H) mysql> ALTER TABLE shop.customers DISCARD TABLESPACE [错误]

试题 37:

Choose two.

You are investigating performance problems in a MySQL database; all data fits in memory. You determine that SELECT queries to one table is the main cause for poor response times. Which two have the biggest potential for eliminating the problem?

A) high concurrency [正确]
B) operating system resources [错误]
C) column definitions [错误]
D) innodb mutexes [错误]
E) non-transaction storage engine [正确]
F) table indexes [错误]

答案: 数据都在内存里

试题 38:

Choose two.

You have semi-synchronous replication configured and working with one slave. rpl_semi_sync_master_timeout has never been reached. You find that the disk system on the master has failed and as a result, the data on the master is completely unrecoverable.

Which two statements are true?

A) The slave automatically identifies that the master is unreachable and performs any required actions so that applications can start using the slave as the new master. [错误]
B) Reads from the slave can return outdated data until the value of the rpl_semi_sync_master_timeout variable is reached. [错误]
C) No committed transactions are lost. [正确]
D) Reads from the slave can return outdated data for some time, until it applies all transactions from its relay log. [正确]
E) A small amount of committed transactions may be lost in case they were committed just before the disk failure. [错误]
F) As soon as the incident happens, application can read data from the slave and rely on it to return a full and current set of data. [错误]

答案: rpl_semi_sync_master_timeout has never been reached, 还没有 timeout，if reaches then it will change to async mode.

试题 39:

Choose three.

You are considering using file-system snapshots to back up MySQL. Which three statements are true?

A) There is a slight performance cost while the snapshot is active. [正确]
B) The backup window is almost zero from the perspective of the application. [正确]
C) They allow direct copying of table rows with operating system copy commands. [错误]
D) They do not back up views, stored procedures, or configuration files. [错误]
E) They take roughly twice as long as logical backups. [错误]
F) They work best for transaction storage engines that can perform their own recovery when restored. [正确]
G) They do not use additional disk space. [错误]

试题 40:

Choose two.

Which two commands will display indexes on the parts table in the manufacturing schema?

A) EXPLAIN manufacturing.parts;
B) SELECT * FROM information_schema.statistics WHERE table_schema = ‘manufacturing’ AND TABLE_NAME = ‘parts’; [错误]
C) DESCRIBE manufacturing.parts; [正确]
D) SHOW INDEXES FROM manufacturing.parts; [正确]
E) SELECT * FROM information_schema.COLUMN_STATISTICS; [错误]

试题 41:

On examination, your MySQL installation datadir has become recursively world (所有用户) read/write/executable. What are two major concerns of running an installation with incorrect file privileges?

A) Data files could be deleted. (datafiles) [正确]
B) Users could overwrite configuration files. (mysqld-auto.cnf) [正确]
C) SQL injections could be used to insert bad data into the database. [错误]
D) Extra startup time would be required for the MySQL server to reset the privileges. (没有这个功能) [错误]
E) MySQL binaries could be damaged, deleted, or altered. (basedir 中才有 MySQL binaries) [错误]

试题 42:

Choose three.

Which three requirements must be enabled for group replication?

A) replication filters [错误]
B) semi-sync replication plugin [错误]
C) slave updates logging [正确]
D) binary log checksum [错误]
E) primary key or primary key equivalent on every table [正确]
F) binary log MIXED format [错误]
G) binary log ROW format [正确]

答案: https://dev.mysql.com/doc/refman/8.0/en/group-replication-requirements.html

试题 43:

You are attempting to start your mysqld. Examine this log output:

2019-12-12T22:21:40:353800z 0 [System] [MY-010116] Server /mysql/bin/mysqld (mysqld 8.0.18-commercial) starting as process 29740
2019-12-12T22:21:40:458802z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2019-12-12T22:21:40:459259z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2019-12-12T22:21:40:459423z 1 [ERROR] [MY-012594] [InnoDB] If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2019-12-12T22:21:40:459606z 1 [ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation.
2019-12-12T22:21:40:459891z 1 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.

Which two things must you check?

A) the configuration file for correct datadir setting [正确]
B) that you are using the correct version of MySQL [错误]
C) that the TLS/SSL certificates are still valid [错误]
D) for the possibility that the files are locked by another process [错误]
E) for the presence of the missing files in other locations [正确]
F) that the user attempting to connect to the database is using the correct username and password [错误]

(Note: Question 44 seems to be a duplicate of 42 and is removed)

试题 45:

Choose two.

Which two statements are true about raw binary backups?

A) They are converted to a highly compressible binary format. [错误]
B) They are required to obtain FIPS security compliance. [错误]
C) The resulting files are easily human readable. [错误]
D) The data format is identical to how MySQL stores the data on disk. [正确]
E) They are faster than logical backups because the process is a simple file or file system copy. [正确]

试题 46:

Choose two.

Which two methods can be used to determine whether a query uses the hash join algorithm?

A) EXPLAIN FORMAT=JSON [正确]
B) EXPLAIN FORMAT=TRADITIONAL [错误]
C) EXPLAIN FORMAT=TREE [正确]
D) EXPLAIN without any formatting argument [错误]
E) EXPLAIN ANALYZE [错误]

试题 47:

Choose two.

You have an InnoDB Cluster configured with three servers. Examine this command, which executes successfully:

1	mysqldump -uroot -p -d mydatabase > mydatabase_backup.sql

Due to data loss, the cluster is initialized and a restore is attempted resulting in this error:

1	ERROR 13176 (HY000) at line 23: Cannot update GTID_PURGED with the Group Replication plugin running

Which two actions, either one of which, can fix this error and allow a successful restore of the cluster?

A) Remove the group replication plugin from each instance before restoring. [错误]
B) Remove the @@GLOBAL.gtid_executed statement from the dump file. [错误]
C) Stop all instances except the primary read/write master instance and run the restore. [错误]
D) Restore using the –set-gtid-purged = OFF option. [错误]
E) Remove the @@GLOBAL.gtid_purged statement from the dump file. [正确]
F) Create the backup by using the –set-gtid-purged = OFF option. [正确]

试题 48:

Choose three.

Which three are types of InnoDB tablespaces?

A) data tablespaces [正确]
B) schema tablespaces [错误]
C) redo tablespaces [错误]
D) temporary table tablespaces [正确]
E) undo tablespaces [正确]
F) encryption tablespaces [错误]

试题 49:

Choose two.

Examine this statement and output:

1
2
3

mysql> SELECT ROW_NUMBER() OVER() AS QN, query, exec_count, avg_latency, lock_latency
       FROM sys.statement_analysis
       ORDER BY exec_count;

(见下图)

<html><body><table><tr><td>QNquery</td><td></td><td></td><td>|exec_count|avg_latency |lock_latency</td><td></td></tr><tr><td>1 SELECTSUM(</td><td colspan="2">K）FROMmYSCh.（）-INTERVAL？SOL_TSI_HOUR</td><td>381268131.44ms</td><td>11.01m</td></tr><tr><td>2</td><td colspan="2">SELECT id' val 'a' 'b updatedWHEREcreated<？</td><td>150317358.34us</td><td>30.06s</td></tr><tr><td>3 SELECT</td><td>empno 'val cre ated+INTERVAL？SOL_TSI_DAY</td><td></td><td>600523.32ms</td><td>120.24ms</td></tr><tr><td></td><td>b ？AND？ORKBETWEEN？AND？</td><td></td><td></td><td></td></tr><tr><td></td><td>C FROM m</td><td></td><td>20010.32s</td><td>40.19ms</td></tr><tr><td>SELECT b FROM</td><td colspan="2">myschem G emp_no WHEREval=？</td><td>1121.03s</td><td>274.00us</td></tr><tr><td colspan="2">A)QN=2[错误]</td><td colspan="3"></td></tr><tr><td colspan="2"></td><td colspan="3"></td></tr><tr><td colspan="2">B)QN=3[错误]</td><td colspan="3"></td></tr><tr><td colspan="2">E)QN=5[正确]</td><td colspan="3" rowspan="2"></td></tr><tr><td colspan="2"></td></tr><tr><td colspan="2">D)QN=1[错误]</td><td colspan="3"></td></tr><tr><td colspan="2">C)QN=4[正确]</td><td colspan="3"></td></tr><tr><td colspan="2"></td><td colspan="3"></td></tr></table></body></html>

You must try to reduce query execution time. Which two queries should you focus on?

A) QN=2 [错误]
B) QN=3 [错误]
C) QN=4 [正确]
D) QN=1 [错误]
E) QN=5 [正确]

解析: 看 avg_latency，执行次数多的 sql 语句已经没有优化的空间了。

试题 50:

Choose two.

You are asked to review possible options for a new MySQL instance. It will be a large, busy reporting data warehousing instance. Which two innodb_data_file_path configurations would satisfy long-term storage demands?

A) ibdata1:12M:autoextend [正确]
B) ibdata1:12M;ibdata2:12M:autoextend [正确]
C) ibdata1:12M;ibdata2:12M;ibdata3:12M [错误]
D) ibdata1:12M;/tmp/ibdata2:12M:autoextend [错误]
E) ibdata1:12M [错误]
F) ibdata1:12M:autoextend;ibdata2:12M:autoextend [错误]

答案: The autoextend attribute can be specified only for the last data file in the innodb_data_file_path setting.

试题 51:

Choose two.

An existing asynchronous replication setup is running MySQL 8. Which two steps are a part of implementing GTID replication?

A) Enable GTID by executing this on the master and the slave: SET GLOBAL GTID_ENABLED = on; [错误]
B) On the slave, alter the MySQL master connection setting with: ALTER channel CHANGE MASTER TO MASTER_AUTO_POSITION = 1; [错误]
C) Execute this on the slave to enable GTID: RESET SLAVE; START SLAVE GTID_NEXT = AUTOMATIC; [错误]
D) Execute this on the slave to enable GTID: START SLAVE IO_THREAD WITH GTID; [错误] (“SET GLOBAL GTID_PURGED = ‘current_gtid’” 和 “START SLAVE”。)
E) Restart MySQL (master and slave) with these options enabled: –gtid_mode=ON –log-bin –log-slave-updates –enforce-gtid-consistency [正确]
F) On the slave, alter the MySQL master connection setting with: CHANGE MASTER TO MASTER_AUTO_POSITION = 1; [正确]

答案: 没有 GTID_Enabled 这个系统变量

试题 52:

Choose two.

You plan to install MySQL Server by using the RPM download. Which two statements are true?

A) You must manually initialize the data directory. [错误]
B) You can provide the root password interactively. [错误]
C) The MySQL RPM package installation supports deploying multiple MySQL versions on the same host. [错误]
D) MySQL uses the RPM relocatable installation target feature. [错误]
E) You can find the root password in the error log after the first start. [正确]
F) The functionality is split among several RPM package files. [正确]

试题 53:

Choose four.

Which four connection methods can MySQL clients specify with the --protocol option when connecting to a MySQL server?

A) IPv4 [错误]
B) SOCKET [正确]
C) MEMORY [正确]
D) PIPE [正确]
E) IPv6 [错误]
F) FILE [错误]
G) TCP [正确]
H) DIRECT [错误]

答案: –protocol = {TCP|SOCKET|PIPE|MEMORY}

试题 54:

Choose four.

You have a MySQL client installed on your Linux workstation with a default installation. You have your admin login credentials to connect to a MySQL server running Microsoft Windows on remote host 192.0.2.1:3306 to connect to the world database. Which four options need to be specified to complete this task with a single command?

A) –port=3306 [错误] (Default port)
B) –protocol=pipe [错误]
C) –host=192.0.2.1 [正确]
D) –protocol=UDP [错误]
E) –user=admin [正确]
F) –password [正确] (Prompts for password)
G) –socket=/tmp/mysql.sock [错误]
H) –shared-memory-base-name=world [错误]
I) –database=world [正确]

试题 55:

Choose three.

Which three are characteristics of a newly created role?

A) It is stored in the mysql.role table. [错误，没有这个表]
B) It can be dropped using the DROP ROLE statement. [正确]
C) It can be protected with a password. [错误]
D) It can be granted to user accounts. [正确]
E) It can be renamed using the RENAME ROLE statement. [错误]
F) It is created as a locked account. [正确]

答案: A role when created is locked, has no password.

试题 56:

Table t is an InnoDB table. Examine these statements and output:

select count(1) from t;
+----------+
| count(1) |
+----------+
|       72 |
+----------+

mysql> show indexes from t \G
*************************** 1. row ***************************
       Table: t
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: a
   Collation: A
 Cardinality: 72
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
Index_comment:
     Visible: YES
  Expression: NULL
*************************** 2. row ***************************
       Table: t
  Non_unique: 1
    Key_name: b_idx
Seq_in_index: 1
 Column_name: b
   Collation: A
 Cardinality: 1
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
Index_comment:
     Visible: NO
  Expression: NULL
2 rows in set (0.00 sec)

Which two are true?

A) ANALYZE TABLE t would update index statistics uniquely for the PRIMARY index. [错误]
B) Table t has two viable indexes to be used for queries. [错误] (b_idx is invisible)
C) SELECT b from t would perform a table scan. [正确] (b_idx is invisible)
D) Index b_idx has a low number of unique values. [正确] (Cardinality: 1)
E) SELECT a FROM t would perform a table scan. [错误] (Uses PRIMARY index)

试题 57:

Choose two.

Which two statements are true about the mysqld-auto.cnf file?

A) It is always updated with changes to system variables. [错误] (Only with SET PERSIST)
B) This file is for logging purposes only and is never processed. [错误]
C) It is read and processed at the end of startup configuration. [正确]
D) This file is for storing MySQL Server configuration options in JSON format. [正确]
E) It is read and processed at the beginning of startup configuration. [错误]
F) This file is for storing MySQL server_uuid values only. [错误]

试题 58:

Choose two.

Examine this command and output: (见下图)

mysql> SELECT * FROM performance_schema.data_locks LIMIT 1\G
*************************** 1. row ***************************
                ENGINE: INNODB
       ENGINE_LOCK_ID: 1200:146
ENGINE_TRANSACTION_ID: 1200
            THREAD_ID: 45
             EVENT_ID: 11
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: mytable1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 118793337250203
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1922,1922

Which two statements are true?

A) The lock is an exclusive lock. [正确] (LOCK_MODE: X)
B) The lock is a shared lock. [错误]
C) The lock is a row-level lock. [正确] (LOCK_TYPE: RECORD)
D) The lock is an intentional lock. [错误]
E) The lock is at the metadata object level. [错误]
F) The lock is at the table object level. [错误]

试题 59:

Choose two.

Your MySQL installation is running low on space due to binary logs. You need to reduce your log space usage urgently. Which two sets of actions when completed will accomplish this?

A) Use SET PERSIST binlog_expire_logs_seconds =. [错误] (Doesn’t purge immediately)
B) Use SET GLOBAL binlog_expire_logs_seconds =and restart the server. [错误] (Restart not needed for global set)
C) Use PURGE BINARY LOGS TO ‘‘. [正确]
D) Set binlog_expire_logs_seconds in my.cnf. [错误] (Requires restart, not urgent)
E) Use SET GLOBAL binlog_expire_logs_seconds =and run the FLUSH BINARY LOGS command. [正确] (FLUSH BINARY LOGS forces check for expiration)
F) Set binlog_expire_logs_seconds = 0 in my.cnf and restart the server. [错误] (Disables expiration, doesn’t purge)

试题 60:

Choose three.

You must run multiple instances of MySQL Server on a single host. Which three methods are supported?

A) Use system tools to lock each instance to its own CPU. [错误]
B) Use resource groups to lock different instances on separate CPUs. [错误]
C) Run mysqld with –datadir defined for each instance. [错误] (Need more than just datadir)
D) Run MySQL Server docker containers. [正确]
E) Start mysqld or mysqld_safe using different option files for each instance. [正确]
F) Use systemd with different settings for each instance. [正确]

答案: https://dev.mysql.com/doc/refman/8.0/en/using-systemd.html

试题 61:

Choose two.

Your MySQL installation is running low on space due to binary logs. You need to reduce your log space usage urgently. Which two sets of actions when completed will accomplish this?

A) Use PURGE BINARY LOGS TO ‘‘. [正确]
B) Use SET GLOBAL binlog_expire_logs_seconds =and run the FLUSH BINARY LOGS command. [正确]
C) Use SET GLOBAL binlog_expire_logs_seconds =and restart the server. [错误]
D) Use SET PERSIST binlog_expire_logs_seconds =. [错误]
E) Set binlog_expire_logs_seconds = 0 in my.cnf and restart the server. [错误]
F) Set binlog_expire_logs_seconds in my.cnf. [错误]

(Note: This seems identical to question 59)

试题 62:

The replication for master and slave MySQL Server is up and running. The disk space occupied by the binary log files continues to grow. Which two methods manage this issue?

A) Execute the FLUSH LOGS statement. [错误] (Rotates logs, doesn’t purge)
B) Delete all binary log files manually on the file system to release storage space. [错误] (Dangerous, breaks replication)
C) Execute the PURGE BINARY LOGS statement. [正确]
D) On the master server, disable binary logging by removing the –log-bin option [错误] (Stops replication)
E) Set the binlog_expire_logs_seconds variable. [正确]

试题 63:

Choose two.

You administer a three node, single primary InnoDB Cluster. Examine cluster.status() displayed here:

1	"statusText": "Cluster is ONLINE and can tolerate up to ONE failure."

Which two statements are true?

A) If two instances are unreachable because of network failure, the cluster will reconfigure to work with a single instance. [错误] (Loses quorum)
B) Reconfiguring the cluster as multi-primary, will increase tolerance to two failures. [错误] (Still needs quorum)
C) There is a quorum and transactions can be committed normally. [错误] (Status text implies quorum, but this isn’t the direct meaning)
D) If two instances crash, it will produce an outage. [正确] (Loses quorum)
E) Restarting an arbitrary instance will always provoke primary instance failover. [错误] (Only if primary restarts)
F) Shutting down two instances with the SHUTDOWN command will produce an outage. [正确] (Loses quorum)

答案: https://dev.mysql.com/doc/refman/8.0/en/group-replication-fault-tolerance.html Cluster is ONLINE and can tolerate up to ONE failure: 容错节点

试题 64:

Choose two.

Which two MySQL Server accounts are locked by default?

A) any new ROLE accounts [正确]
B) any internal system accounts [正确] (mysql.sys, mysql.session, mysql.infoschema)
C) any user created with a username, but missing the host name [错误]
D) any user set as DEFINER for stored programs [错误]
E) any user created without a password [错误]

试题 65:

Examine this command, which executes successfully:

1	mysqlpump --user=root --password > full_backup.sql

Which two databases will be excluded from this dump?

A) world [错误]
B) employee [错误]
C) information_schema [正确]
D) mysql [错误]
E) sys [正确]

试题 66:

Choose three.

Which three commands can report all the current connections running on the MySQL server?

A) SELECT * FROM performance_schema.events_transactions_current [错误]
B) SELECT * FROM performance_schema.threads [正确]
C) SHOW FULL PROCESSLIST [正确]
D) SELECT * FROM information_schema.processlist [正确]
E) SHOW EVENTS [错误]
F) SELECT * FROM sys.metrics [错误]
G) SELECT * FROM information_schema.events [错误]
H) SELECT * FROM sys.statement_analysis [错误]

答案: performance_schema.threads 表包含每个服务器线程的一行。每行包含关于线程的信息，并指示是否为其启用监视和历史事件日志记录: Sys.metrics: 视图总结了 MySQL 服务器指标，以显示变量名、值、类型，以及它们是否被启用 information_schema.events: EVENTS 表提供了关于事件管理器事件的信息 sys.statement_analysis: 这些视图列出了带有聚合统计信息的规范化语句。

试题 67:

Choose three.

Identify three functions of MySQL Enterprise Monitor.

A) Analyze query performance. [正确]
B) Start a logical backup. [错误]
C) Determine the availability of monitored MySQL servers. [正确]
D) Centrally manage users. [错误]
E) Start a MySQL Enterprise backup. [错误]
F) Centrally manage server configurations. [错误]
G) Start and stop MySQL Server. [错误]
H) Create customized alerts and provide notification alerts. [正确]

试题 68:

Choose three.

Which three actions will secure a MySQL server from network-based attacks?

A) Construct a perimeter network to allow public traffic [正确] (DMZ)
B) Place the MySQL instance behind a firewall. [正确]
C) Use network file system (NFS) for storing data. [错误]
D) Change the listening port to 3307. [错误] (Security through obscurity)
E) Use MySQL Router to proxy connections to the MySQL server. [错误] (Router itself doesn’t secure, configuration does)
F) Allow connections from the application server only. [正确]

(Note: Question 4 asked for two, this asks for three)

试题 69:

Choose two.

Which two statements are true about MySQL server multi-source replication?

A) It must use GTID replication. [错误]
B) It is not compatible with auto-positioning. [错误]
C) It needs to be re-instanced after a crash to maintain consistency. [错误]
D) It uses only time-based replication conflict resolution. [错误]
E) It does not attempt to detect or resolve replication conflicts. [正确]
F) It relies on relay_log_recovery for resilient operations. [正确]

答案: 参考文档 MySQL 8.0 for Database Administrators StudentGuide 2.pdf 页数 P313 Sources in a multisource replication topology can be configured to use either GTID-based replication, or binary log position-based replication. For a multithreaded replica, setting relay_log_recovery = ON automatically handles any inconsistencies and gaps in the sequence of transactions that have been executed from the relay log.

试题 70:

Choose two.

Which two statements are true about using MySQL Enterprise Monitor Query Analyzer?

A) It is possible to retrieve a normalized statement, but never the exact statement that was executed. [错误] (Can show examples)
B) The single query QRTi pie chart in the Query Analyzer view is based on the average execution of all statements. [错误]
C) It is possible to import data into the Query Analyzer from heterogeneous sources, such as CSV. [错误]
D) It is possible to list and analyze statements in an arbitrary graph range selection from timeseries graphs. [正确]
E) It is possible to configure the Query Analysis built-in advisor to get notified about slow query execution. [正确]

试题 71:

Choose two.

You must export data from a set of tables in the world_x database. Examine this set of tables: (country, countryinfo, location)
Which two options will export data into one or more files?

A) shell> mysqldump world_x country countryinfo location > mydump.sql [正确]
B) mysql> SELECT * INTO OUTFILE '/output/country.txt' FROM world_x.country; mysql> SELECT * INTO OUTFILE '/output/countryinfo.txt' FROM world_x.countryinfo; mysql> SELECT * INTO OUTFILE '/output/location.txt' FROM world_x.location; [正确]
C) shell> mysqlexport world_x country countryinfo location > mydump.sql [错误] (No such command)
D) mysql> CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql/world_x/country'; mysql> CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql/world_x/countryinfo'; mysql> CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql/world_x/location'; [错误] (Clones entire instance data)
E) shell> mysql --batch world_x.country world_x.countryinfo world_x.location > mydump.sql [错误] (Incorrect syntax for exporting multiple tables)

答案: 没有 mysqlexport 这个工具

试题 72:

Choose three.

Which are three benefits of using mysqlbackup instead of mysqldump?

A) mysqlbackup can perform partial backup of stored programs. [错误]
B) mysqlbackup allows logical backups with concurrency resulting in faster backups and restores. [错误] (mysqlbackup is physical)
C) mysqlbackup integrates tape backup and has the virtual tape option. [正确]
D) mysqlbackup can back up tables with the InnoDB engine without blocking reducing wait times due to contention. [正确] (Hot backup)
E) mysqlbackup does not back up MySQL system tables, which shortens backup time. [错误]
F) mysqlbackup restores data from physical backups, which are faster than logical backups. [正确]

答案: Used as a hint to the Media Management Software (MMS) for the selection of media and policies for tape backup. https://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/meb-mms.html

试题 73:

Choose two.

All MySQL Server instances belonging to InnoDB Cluster have SSL configured and enabled. You must configure InnoDB Cluster to use SSL for group communication. Which two statements are true?

A) An existing InnoDB Cluster must be dissolved and created from scratch to enable SSL for group communication. [正确]
B) If only some InnoDB Cluster members are enabled for SSL group communication, and –ssl-mode = PREFERRED, communication will fall back to unencrypted connection. [错误]
C) SSL group communication must be enabled at cluster creation time by specifying createCluster({memberSslMode:’REQUIRED’}). [正确]
D) SSL group communication can be enabled for an existing cluster, one instance at time, by setting group_replication_ssl_mode. [错误]
E) SSL group communication requires the use of an additional set of parameters group_replication_recovery_*. [错误]
F) Configuring SSL group communication also configures SSL distributed recovery. [错误]

答案: https://dev.mysql.com/doc/mysql-shell/8.0/en/configuring-innodb-cluster.html https://dev.mysql.com/doc/refman/8.0/en/group-replication-configuring-ssl-for-recovery.html The SSL mode of a cluster can only be set at the time of creation.

试题 74:

After installing MySQL 8.0 on Oracle Linux 7, you initialize the data directory with the mysqld --initialize command. Which two will assist in locating the root password?

A) the root_pw variable stored in the mysql.install table [错误]
B) the root password displayed on the screen via a Warning message [正确] (Only with –initialize-insecure)
C) the root password inserted in the error log set by the –log-error=file_name variable [正确] (Default behavior for –initialize)
D) the root password written to the /root/.my.cnf file [错误]
E) as root, executing the SHOW PASSWORD command by using the SHA-256 password encryption plugin [错误]

(Correction: –initialize writes to error log, –initialize-insecure does not set a password)

试题 75:

Choose two.

Identify two ways to significantly improve data security.

A) Configure mysqld to run as the system admin account, such as root. [错误]
B) Use a private network behind a firewall. [正确]
C) Configure mysqld to use only networked disks. [错误]
D) Configure MySQL to have only one administrative account. [错误]
E) Configure mysqld to use only local disks or attached disks and to have its own account in the host system. [正确]

分析： 网络隔离最好， D 的问题是 administrator 这个权限太大了，应该有多个 administrative account，每个只有部分权限。

试题 76:

Which two are valid uses for binary logs on a MySQL instance?

A) logging the duration and locks for all queries [错误]
B) replication [正确]
C) audit of all queries [错误] (General log is better for this)
D) point-in-time recovery [正确]
E) recording the order in which queries are issued [错误] (Records changes, not all queries)

试题 77:

Choose two.

Which two are features of MySQL Enterprise Firewall?

A) blocking of potential threats by configuring pre-approved whitelists [正确]
B) modifying SQL statement dynamically with substitutions [错误]
C) recording incoming SQL statement to facilitate the creation of a whitelist of permitted commands [正确] (DETECTING mode)
D) automatic locking of user accounts who break your firewall [错误]
E) provides stateless firewall access to TCP/3306 [错误]

答案: 参考文档 MySQL 8.0 for Database Administrators StudentGuide 2.pdf 页数 P89

试题 78:

Choose three.

Which three methods display the complete table definition of an InnoDB table?

A) hexdump -v -C table.frm [错误] (No .frm in 8.0)
B) REPAIR TABLE table USE_FRM [错误] (No .frm in 8.0)
C) mysqldump –no-data schema table [正确]
D) Query the Information Schema. [正确] (e.g., information_schema.columns, information_schema.tables)
E) SELECT * FROM table \G [错误] (Shows data, not definition)
F) SHOW CREATE TABLE [正确]

答案: 8 里面没有 frm

试题 79:

Which two statements are true about the mysql_config_editor program?

A) It provides an interface to change my.cnf files. [错误]
B) It manages the configuration of client programs. (only work for mysql client) [正确]
C) It can move datadir to a new location. [错误]
D) It manages the configuration of user privileges for accessing the server. [错误]
E) It will use client options by default unless you provide –login-path. (e.g., mysql --login-path=client) [正确]
F) It manages the configuration of the MySQL Firewall feature. [错误]
G) It can be used to create and edit SSL certificates and log locations. [错误]

试题 80:

Choose three.

A MySQL server is monitored using MySQL Enterprise Monitor’s agentless installation. Which three features are available with this installation method?

A) MySQL Replication monitoring [正确]
B) security-related advisor warnings [正确]
C) CPU utilization [错误]
D) disk usage and disk characteristics including disk advisors warnings [错误]
E) MySQL Query Analysis data [正确] (Collected via Performance Schema)
F) operating system memory utilization [错误]
G) network-related information and network characteristics [错误]

答案: 无代理的情况下，只能监控 mysql 数据库自己的状态

试题 81:

Choose four.

Which four are types of information stored in the MySQL data dictionary?

A) server runtime configuration [错误]
B) server configuration rollback [错误]
C) performance metrics [错误]
D) stored procedure definitions [正确]
E) InnoDB buffer pool LRU management data [错误]
F) view definitions [正确]
G) table definitions. [正确]
H) access control lists [正确]

答案: https://dev.mysql.com/doc/refman/8.0/en/data-dictionary.html

(Note: This seems identical to question 10)

试题 82:

Choose two.

Examine this statement:

1	mysql> DROP ROLE r_role1, r_role2;

Which two are true?

A) You must revoke r_role1 and r_role2 from all users and other roles before dropping the roles. [错误]
B) You must revoke all privileges from r_role1 and r_role2 before dropping the roles. [错误]
C) It fails if at least one of the roles does not exist. [正确]
D) Existing connections can continue to use the roles’ privileges until they reconnect. [错误] (Privileges revoked immediately)
E) It fails if you do not have the ADMIN OPTION of the roles r_role1 and r_role2. [错误] (Need DROP ROLE or CREATE USER privilege)
F) It fails if any of the roles is specified in the mandatory_roles variable. [正确]

试题 83:

Choose two.

Which two are true about differences between logical and physical upgrades of MySQL databases?

A) Logical upgrades are much faster because they do not require restarting the mysqld process. [错误]
B) Physical upgrades are much faster because they do not require restarting the mysqld process. [错误] (Both usually require restart/downtime)
C) Physical upgrades are performed for current instances on bare metal deployments, whereas logical upgrades are used for virtual machines or containerized instances. [错误] (Both can be used anywhere)
D) Post-upgrade table storage requirements after physical upgrades are usually smaller than that after logical upgrades. [错误]
E) Post-upgrade table storage requirements after logical upgrades are usually smaller than that after physical upgrades. [正确] (Logical rebuilds tables)
F) Physical upgrades leave data in place, whereas logical upgrades require data to be restored from mysqldump-type backups taken before the upgrades. [正确]

试题 84:

Choose two.

On examination, your MySQL installation datadir has become recursively world read/write/executable. What are two major concerns of running an installation with incorrect file privileges?

A) Extra startup time would be required for the MySQL server to reset the privileges. [错误]
B) MySQL binaries could be damaged, deleted, or altered. [错误] (Binaries usually not in datadir)
C) SQL injections could be used to insert bad data into the database. [错误] (Permissions don’t directly cause SQL injection)
D) Data files could be deleted. [正确]
E) Users could overwrite configuration files. [正确] (e.g., mysqld-auto.cnf)

(Note: This seems identical to question 41)

试题 85:

Choose two.

You made some table definition changes to a schema in your MySQL Server. Which two statements reflect how MySQL Server handles the table definition changes?

A) MySQL Server stores a copy of the serialized data in the InnoDB user tablespace. [正确] (SDI in .ibd files)
B) MySQL writes SDI to the binary log for distributed backups. [错误]
C) MySQL implicitly executes FLUSH TABLES and stores a snapshot backup of the metadata. [错误]
D) The metadata is serialized in (SDI). [正确]
E) MySQL keeps InnoDB metadata changes in .sdi files in datadir. [错误] (Only non-InnoDB engines use separate .sdi files)

答案: In addition to storing metadata about database objects in the data dictionary, MySQL stores it in serialized form. This data is referred to as serialized dictionary information (SDI). InnoDB stores SDI data within its tablespace files. Other storage engines store SDI data in .sdi files that are created for a given table in the table’s database directory. SDI data is generated in a compact JSON format. 参考文档 MySQL 8.0 for Database Administrators StudentGuide 1.pdf 页数 P141

试题 86:

Choose three.

Which three settings control global buffers shared by all threads on a MySQL server?

A) tmp_table_size [错误] (Session)
B) innodb_buffer_pool_size [正确]
C) table_open_cache [正确]
D) sort_buffer_size [错误] (Session)
E) key_buffer_size [正确] (MyISAM global index buffer)
F) read_buffer_size [错误] (Session)

答案:

key_buffer_size：用于设置MyISAM存储引擎索引缓存的大小。索引缓存存索引块，加速索引的读取。
read_buffer_size：用于设置每个客户端连接的读取缓冲区大小。当客户端进行数据读取操作时，数据会首先被读入缓冲区，然后由缓冲区提供给客户端。(Session)
tmp_table_size：用于设置临时表的内存缓冲区大小。当需要在内存中创建临时表时，会使用这个缓冲区来存储临时表的数据。如果临时表的大小超过了这个缓冲区的大小，MySQL会将临时表存储在磁盘上。(Session)
sort_buffer_size：用于设置排序操作的缓冲区大小。当进行排序操作时，MySQL会使用这个缓冲区来存储排序数据。如果排序数据的大小超过了这个缓冲区的大小，MySQL会使用临时文件进行排序。(Session)
innodb_buffer_pool_size：用于设置InnoDB存储引擎的缓冲池大小。缓冲池是InnoDB用来存储数据和索引的主要缓冲区。增大这个参数的值可以提高InnoDB存储引擎的性能。(Global)
table_open_cache：用于设置表缓存的大小。表缓存存储了最常用的表的定义信息，可以加速表的打开和关闭操作。增大这个参数的值可以提高对表的访问性能。(Global)

试题 87:

Choose two.

You are using mysqlcheck for server maintenance. Which two statements are true?

A) The mysqlcheck –check –all-databases command takes table write locks while performing a series of checks. [错误] (Usually READ lock)
B) The mysqlcheck –repair –all-databases command can repair an InnoDB corrupted table. [错误] (Repair doesn’t work well for InnoDB)
C) The mysqlcheck –analyze –all-databases command performs a series of checks to spot eventual table corruptions. [错误] (Analyze updates statistics)
D) The mysqlcheck command can be renamed mysqlrepair so that it repairs tables by default. [正确]
E) The mysqlcheck –optimize –all-databases command reclaims free space from table files. [正确]

答案：
A Each table is locked and therefore unavailable to other sessions while it is being processed, although for check operations, the table is locked with a READ lock only.
B –repair, -r Perform a repair that can fix almost anything except unique keys that are not unique. (Primarily for MyISAM)

试题 88:

Which two MySQL Server accounts are locked by default?

A) any new ROLE accounts [正确]
B) any user created without a password [错误]
C) any internal system accounts [正确]
D) any user created with a username, but missing the host name [错误]
E) any user set as DEFINER for stored programs [错误]

(Note: This seems identical to question 64)

试题 89:

Choose three.

Your MySQL server is running on the Microsoft Windows platform. Which three local connection protocols are available to you?

A) UDP [错误]
B) shared memory [正确]
C) SOCKET [错误] (Unix socket)
D) named pipes [正确]
E) X Protocol [错误] (Network protocol)
F) TCP/IP [正确] (Can connect to 127.0.0.1)

答案: 在 windows 上没有 socket

试题 90:

Which two authentication plugins require the plain text client plugin for authentication to work?

A) Windows Native authentication [错误]
B) PAM authentication [正确]
C) LDAP SASL authentication [错误]
D) LDAP authentication [正确]
E) SHA256 authentication [错误]
F) MySQL Native Password [错误]

(Note: This seems identical to question 9)

试题 91:

Choose two.

Which two statements are true about the data dictionary object cache?

A) The dictionary object caches use a Least Recently Used (LRU) algorithm to manage entries in each cache. [正确]
B) Character set and collation definition objects are not cached. [错误]
C) All dictionary object caches have a hard-coded size. [错误] (Configurable sizes exist)
D) If the dictionary object cache becomes full, MySQL server will be unable to create any more tables/objects. [错误] (LRU evicts old entries)
E) tablespace_definition_cache sets the number of tablespace objects that can be stored in the dictionary object cache. [正确]

答案: https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-object-cache.html

试题 92:

Choose two.

Examine this statement, which executes successfully:

1	CREATE USER mary@192.0.2.100 IDENTIFIED BY 'P@SSw0rd' REQUIRE NONE PASSWORD EXPIRE;

Which two are true?

A) Mary must connect using the username ‘mary@192.0.2.100‘. [错误] (Username is ‘mary’)
B) Mary requires no password to connect to the MySQL server. [错误]
C) Mary must connect from the client machine 192.0.2.100. [正确]
D) Mary cannot connect to the MySQL server until the DBA resets her password. [错误] (She can connect but must change password)
E) Mary cannot query data until she changes her password. [正确] (Due to PASSWORD EXPIRE)

试题 93:

Choose two.

Examine this command, which executes successfully on InnoDB Cluster:

1	dba.dropMetadataSchema()

Which two statements are true?

A) The mysql_innodb_cluster_metadata schema is dropped from the instance where the connection was established. [错误]
B) Group Replication is still operational, but InnoDB Cluster must be reimported under MySQL Shell. [正确]
C) The command drops the mysql_innodb_cluster_metadata schema and re-creates it. [错误]
D) Connections driven by MySQL Router are not affected by the command. [错误] (Router relies on metadata)
E) The mysql_innodb_cluster_metadata schema is dropped from all reachable members of the cluster. [正确]
F) Group Replication will be dissolved and all metadata purged. [错误] (Only metadata schema dropped)

答案: Drops the Metadata Schema.

(Note: This seems identical to question 113)

试题 94:

Choose two.

User account baduser@hostname on your MySQL instance has been compromised (攻击). Which two commands stop any new connections using the compromised account?

A) ALTER USER baduser@hostname PASSWORD DISABLED; [错误] (Allows connection without password)
B) ALTER USER baduser@hostname DEFAULT ROLE NONE; [错误] (Affects privileges, not connection)
C) ALTER USER baduser@hostname MAX_USER_CONNECTIONS 0; [错误] (Affects resource limits, not login)
D) ALTER USER baduser@hostname IDENTIFIED WITH mysql_no_login; [正确]
E) ALTER USER baduser@hostname ACCOUNT LOCK; [正确]

答案: use the mysql_no_login authentication plugin to prevent clients from using the accounts to log in directly to the MySQL server.

试题 95:

Choose two.

Which two are use cases of MySQL asynchronous replication?

A) You can scale reads by adding multiple slaves. [正确]
B) MySQL Enterprise Backup will automatically back up from an available slave. [错误]
C) You can scale writes by creating a replicated mesh. [错误] (Can lead to conflicts)
D) It guarantees near real-time replication between a master and a slave. [错误] (Asynchronous means potential lag)
E) It allows backup to be done on the slave without impacting the master. [正确]

试题 96:

Choose two.

Examine this command, which executes successfully:

1	mysqlpump --user=root --password > full_backup.sql

Which two databases will be excluded from this dump?

A) mysql [错误]
B) information_schema [正确]
C) world [错误]
D) employee [错误]
E) sys [正确]

答案: mysqlpump does not dump the performance_schema, ndbinfo, or sys schema by default. information_schema 不存在数据库

(Note: This seems identical to question 65)

试题 97:

Choose two.

A valid raw backup of the shop.customers MyISAM table was taken. You must restore the table. You begin with these steps:

Confirm that secure_file_priv = '/var/tmp'
mysql> DROP TABLE shop.customers;
shell> cp /backup/customers.MY* /var/lib/mysql/shop/

Which two actions are required to complete the restore?

A) shell> cp /backup/customers.sdi /var/tmp [正确]
B) mysql> SOURCE '/var/tmp/customers.sdi' [错误]
C) shell> cp /backup/customers.sdi /var/lib/mysql/shop/ [错误]
D) mysql> ALTER TABLE shop.customers DISCARD TABLESPACE [错误]
E) shell> cp /backup/customers.frm /var/lib/mysql/shop/ [错误] (No .frm in 8.0)
F) mysql> IMPORT TABLE FROM '/var/lib/mysql/shop/customers.sdi.' [错误]
G) mysql> IMPORT TABLE FROM '/var/tmp/customers.sdi' [正确]
H) mysql> ALTER TABLE shop.customers IMPORT TABLESPACE [错误]

答案: https://dev.mysql.com/doc/refman/8.0/en/import-table.html

(Note: This seems identical to question 36)

试题 98:

Choose two.

Which two MySQL Shell commands are excluded from the InnoDB Cluster creation procedure?

A) cluster.addInstance() #添加节点 [错误]
B) dba.configureLocalInstance() ##持久化配置信息 [错误]
C) dba.checkInstanceConfiguration() ##检查节点 [错误]
D) cluster.setPrimaryInstance() ##设置主节点 [正确] (Not needed for initial creation)
E) dba.configureInstance() ##检查配置 [错误]
F) dba.createCluster() #创建集群 [错误]
G) cluster.forceQuorumUsingPartitionOf() #将集群从 quorum 丢失场景恢复到可操作状态。如果一个组被分区或发生的崩溃超过了可容忍的范围，则可能会出现这种情况。 [正确] (Recovery, not creation)

答案: https://dev.mysql.com/doc/mysql-shell/8.0/en/deploying-new-production-cluster.html 第一个节点就是主节点，不用设置。

试题 99:

Choose two.

Examine this command, which executes successfully:

1	shell> mysqldump --master-data=2 --single-transaction --result-file=dump.sql mydb

Which two statements are true?

A) This option uses the READ COMMITTED transaction isolation mode. [错误] (Uses REPEATABLE READ)
B) It enforces consistent backups for all storage engines. [错误] (–single-transaction only works for transactional engines like InnoDB)
C) It is a cold backup. [错误] (Hot backup for InnoDB)
D) The backup created is a consistent data dump. [正确] (For InnoDB tables)
E) It executes flush tables with read lock. [正确] (–master-data causes this lock briefly to get binlog position)

试题 100:

Choose two.

Which two methods allow a DBA to reset a user’s password?

A) SET PASSWORD statement [正确] (SET PASSWORD FOR 'user'@'host' = 'new_password';)
B) mysql_secure_installation utility [错误] (Sets root password initially)
C) ALTER USER statement [正确] (ALTER USER 'user'@'host' IDENTIFIED BY 'new_password';)
D) GRANT statement [错误] (Manages privileges)
E) mysqladmin client program [错误] (Can only change password for the user running mysqladmin)

答案: mysqladmin 只能改自己的

试题 101:

Choose two.

The data in this instance is transient; no backup or replication will be required. It is currently under performing.

The database size is static and including indexes is 19G.
Total system memory is 32G.

After profiling the system, you highlight these MySQL status and global variables:

Com_rollback = 85408355
Com_commit = 1242324
Innodb_buffer_pool_pages_free = 163840
[mysqld]
buffer_pool_size = 20G
innodb_flush_log_at_trx_commit = 2
disable-log-bin

The OS metrics indicate that disk is a bottleneck. Other variables retain their default values.

Which two changes will provide the most benefit to the instance?

A) sync_binlog = 0 [错误]
B) buffer_pool_size = 24G [错误]
C) innodb_flush_log_at_trx_commit = 1 [错误]
D) innodb_doublewrite = 0 [正确]
E) max_connections = 10000 [错误]
F) innodb_log_file_size = 1G [正确]

试题 102:

Choose two.

Examine Joe’s account:

1 2	CREATE USER 'joe'@'%' IDENTIFIED BY 'secret'; GRANT ALL PRIVILEGES ON . TO 'joe'@'%';

All existing connections for joe are killed. Which two commands will stop joe establishing access to the MySQL instance?

A) ALTER USER ‘joe‘@’%’ ACCOUNT LOCK [正确]
B) ALTER USER ‘joe‘@’%’ PASSWORD HISTORY DEFAULT; [错误]
C) REVOKE ALL PRIVILEGES ON . FROM ‘joe‘@’%’; [错误] (Still allows connection)
D) ALTER USER ‘joe‘@’%’ SET password = ‘invalid‘; [错误] (Changes password, doesn’t lock)
E) ALTER USER ‘joe‘@’%’ IDENTIFIED BY ‘invalid‘ PASSWORD EXPIRE; [正确] (Forces password change on next login, effectively blocking until reset)
F) REVOKE USAGE ON . FROM ‘joe‘@’%’; [错误] (USAGE cannot be revoked)

答案:
F: REVOKE 无法取回 USAGE 权限
E: If the password is expired (whether manually or automatically), the server either disconnects the client or restricts the operations permitted to it

试题 103:

Which two can minimize security risks when creating user accounts?

A) Avoid the use of wildcards in host names. [正确]
B) Avoid the use of wildcards in usernames. [错误] (Wildcards not allowed in usernames)
C) Require the use of mixed case usernames. [错误] (Usernames are case-insensitive by default)
D) Do not allow accounts without passwords. [正确]
E) Require users to have the FIREWALL_USER privilege defined. [错误]

试题 104:

Choose two.

Mary connects to a Linux MySQL Server from a client on a Windows machine. Examine this statement and output: (见下图)

Which two are true?

A) Mary connected from a client machine whose IP address is 192.0.2.101. [正确] (USER() shows connection origin)
B) Mary connected to the database server whose IP address is 192.0.2.101. [错误]
C) Mary has the privileges of account mary@%. [正确] (CURRENT_USER() shows effective privileges)
D) Mary connected using a UNIX socket. [错误] (Shows host IP)
E) Mary authenticated to the account mary@192.0.2.101. [错误] (Authenticated as mary@%)

试题 105:

Choose two.

Which two actions can obtain information about deadlocks?

A) Run the SHOW ENGINE INNODB MUTEX command from the mysql client. [错误]
B) Enable the innodb_status_output_locks global parameter. [错误] (innodb_status_output is the parameter)
C) Enable the innodb_print_all_deadlocks global parameter. [正确] (Logs all deadlocks to error log)
D) Run the SHOW ENGINE INNODB STATUS command from the mysql client. [正确] (Shows the latest deadlock)
E) Use the sys.innodb_lock_waits view. [错误] (Shows current lock waits, not past deadlocks)

答案:
innodb_print_all_deadlocks: When this option is enabled, information about all deadlocks in InnoDB user transactions is recorded in the mysqld error log. Otherwise, you see information about only the last deadlock, using the SHOW ENGINE INNODB STATUS command.

试题 106:

Choose two.

Which two are true about binary logs used in asynchronous replication?

A) They contain events that describe all queries run on the master. [错误] (Only changes)
B) They contain events that describe database changes on the master. [正确]
C) They are pushed from the master to the slave. [错误]
D) They contain events that describe only administrative commands run on the master. [错误]
E) They are pulled from the master to the slave. [正确]

答案: https://dev.mysql.com/doc/refman/8.0/en/replication-implementation.html

(Note: Question 107 seems incomplete or corrupted, skipping)

试题 108:

Choose two.

Which two are contained in the InnoDB system tablespace (ibdata1) by default?

A) doublewrite buffer [正确]
B) change buffer [正确]
C) InnoDB Data Dictionary [错误] (In MySQL 8.0, data dictionary is in mysql.ibd)
D) primary indexes [错误] (Only if innodb_file_per_table=OFF)
E) table data [错误] (Only if innodb_file_per_table=OFF)
F) user privileges [错误] (Stored in mysql schema tables)

答案: 参考文档 MySQL 8.0 for Database Administrators StudentGuide 1.pdf 页数 152

试题 109:

Choose two.

Examine these InnoDB Cluster parameter settings:

cluster.setInstanceOption('host1:3377', 'memberWeight', 40)
cluster.setInstanceOption('host2:3377', 'memberWeight', 30)
cluster.setInstanceOption('host3:3377', 'memberWeight', 40)
cluster.setInstanceOption('host3:3377', 'exitStateAction', 'ABORT_SERVER')
cluster.setOption('expelTimeout', 1)

Now examine the partial status: (见下图)

// Assuming status shows host1 as primary, host2 and host3 as secondary, all ONLINE
"topology": {
    "host1:3377": { "address": "host1:3377", "status": "ONLINE", "mode": "R/W", ... },
    "host2:3377": { "address": "host2:3377", "status": "ONLINE", "mode": "R/O", ... },
    "host3:3377": { "address": "host3:3377", "status": "ONLINE", "mode": "R/O", ... }
}

A permanent network failure isolates host3. Which two statements are true?

A) The instance deployed on host3 will automatically rejoin the cluster when connectivity is reestablished. [错误] (ABORT_SERVER requires manual restart)
B) Failure of the instance deployed on host1 provokes an outage. [错误] (host3 also has weight 40, quorum maintained with host2)
C) The instance deployed on host3 is expelled from the cluster and must be rejoined using cluster.addInstance(‘host3:3377’). [错误] (Use rejoinInstance after restart)
D) The primary instance can be specified by using the command cluster.setPrimaryInstance(:). [正确] (Can manually set primary if needed)
E) The instance deployed on host2 is elected as the new primary instance. [错误] (host1 remains primary as it’s reachable and has highest weight with host3)
F) Issuing command cluster.switchToMultiPrimaryMode() will fail to enable multi-primary mode. [正确] (Requires all nodes reachable)

答案:
E 答案也不对应该是权重高的 host1 成为 primary。
A 不对，因为实例关闭了。原因如下：
rejoinInstance(). If exitStateAction is set to ABORT_SERVER then in the event of leaving the cluster unexpectedly, the instance shuts down MySQL, and it has to be started again before it can rejoin the cluster.
exitStateAction = group_replication_exit_state_action
ABORT_SERVER：实例将关闭MySQL
OFFLINE_MODE 这种模式下，已连接的客户端用户将在下一个请求时断开连接，连接将不再被接受，拥有 CONNECTION_ADMIN 特权(或已弃用的 SUPER 特权)的客户端用户除外。
READ_ONLY

试题 110:

Choose two.

A clean shutdown was performed with innodb_fast_shutdown = 0. While you were manipulating files, all files were accidentally deleted from the top-level data directory. Which two files must be restored from backup to allow the DB to restart cleanly?

A) ib_buffer_pool [错误] (Recreated on startup)
B) ib_logfile0 [错误] (Recreated if missing after clean shutdown)
C) mysql.ibd [正确] (Contains data dictionary)
D) ibdata1 [正确] (Contains system tablespace components like doublewrite, change buffer)
E) ibtmp1 [错误] (Temporary tablespace, recreated)
F) undo_001 [错误] (Undo tablespaces, recreated if default)

试题 111:

Examine this command and output:

mysql> SELECT * FROM performance_schema.table_io_waits_summary_by_table WHERE COUNT_STAR > 0 \G
*************************** 2. row ***************************
    OBJECT_TYPE: TABLE
  OBJECT_SCHEMA: test
    OBJECT_NAME: demo_test
     COUNT_STAR: 61567093
  SUM_TIMER_WAIT: 59009007572922
  MIN_TIMER_WAIT: 395922
  AVG_TIMER_WAIT: 958095
  MAX_TIMER_WAIT: 558852005358
      COUNT_READ: 38665056
  SUM_TIMER_READ: 20598719962188
  MIN_TIMER_READ: 395922
  AVG_TIMER_READ: 532728
  MAX_TIMER_READ: 558852005358
     COUNT_WRITE: 22902028
 SUM_TIMER_WRITE: 38410287610743
 MIN_TIMER_WRITE: 1130688
 AVG_TIMER_WRITE: 1677006
 MAX_TIMER_WRITE: 17205682920
     COUNT_FETCH: 38665056
 SUM_TIMER_FETCH: 20598719962188
 MIN_TIMER_FETCH: 395922
 AVG_TIMER_FETCH: 532728
 MAX_TIMER_FETCH: 558852005358
    COUNT_INSERT: 0
SUM_TIMER_INSERT: 0
MIN_TIMER_INSERT: 0
AVG_TIMER_INSERT: 0
MAX_TIMER_INSERT: 0
    COUNT_UPDATE: 0
SUM_TIMER_UPDATE: 0
MIN_TIMER_UPDATE: 0
AVG_TIMER_UPDATE: 0
MAX_TIMER_UPDATE: 0
    COUNT_DELETE: 22902028
SUM_TIMER_DELETE: 38410287610743
MIN_TIMER_DELETE: 1130688
AVG_TIMER_DELETE: 1677006
MAX_TIMER_DELETE: 17205682920

Which two are true?

A) I/O distribution is approximately 50/50 read/write. [错误] (Reads: ~38M, Writes: ~23M)
B) The I/O average time is 532728. These columns aggregate all fetch operations [错误] (AVG_TIMER_WAIT is total avg, AVG_TIMER_FETCH is fetch avg)
C) 22902028 rows were deleted. These columns aggregate all delete operations. [正确] (COUNT_DELETE and SUM/MIN/AVG/MAX_TIMER_DELETE refer to I/O waits for delete operations)
D) Average read times are approximately three times faster than writes. [正确] (AVG_TIMER_READ: 532728 vs AVG_TIMER_WRITE: 1677006)
E) The longest I/O wait was for writes. [错误] (MAX_TIMER_READ > MAX_TIMER_WRITE)

试题 112:

Choose two.

Which two statements are true about using backups of the binary log?

A) Binary logs are relatively small, and therefore, excellent for long-term storage and disaster recovery. [错误] (Can grow very large)
B) Binary logs can always be used to unapply unwanted schema changes. [错误] (Cannot easily “undo” DDL)
C) Multiple binary logs can be used to restore data. [正确]
D) They allow for point-in-time recovery of the data. [正确]
E) Multiple binary logs can be applied in parallel for faster data restoration. [错误] (Must be applied sequentially for consistency)

试题 113:

Examine this command, which executes successfully on InnoDB Cluster:

1	dba.dropMetadataSchema()

Which two statements are true?

A) The command drops the mysql_innodb_cluster_metadata schema and re-creates it. (不重建) [错误]
B) The mysql_innodb_cluster_metadata schema is dropped from the instance where the connection was established. [错误]
C) Connections driven by MySQL Router are not affected by the command. (元数据已经没有) [错误]
D) The mysql_innodb_cluster_metadata schema is dropped from all reachable members of the cluster. [正确]
E) Group Replication will be dissolved and all metadata purged. (需要执行 dissolve) [错误]
F) Group Replication is still operational, but InnoDB Cluster must be reimported under MySQL Shell. [正确]

(Note: This seems identical to question 93)

试题 114:

Choose two.

You are backing up raw InnoDB files by using mysqlbackup. Which two groups of files will be backed up during a full backup?

A) *.ibd files [正确]
B) ibbackup files [错误]
C) *.CSM files [错误] (MyISAM checksum)
D) ib_logfile* files [正确] (Redo logs)
E) *.sdi files [错误] (Only for non-InnoDB or if SDI stored separately)

试题 115:

Choose two.

Which two are characteristics of snapshot-based backups?

A) The frozen file system can be cloned to another virtual machine immediately into active service. [错误] (Requires recovery)
B) There is no need for InnoDB tables to perform its own recovery when restoring from the snapshot backup. [错误] (InnoDB always performs recovery)
C) Snapshot-based backups greatly reduce time during which the database and applications are unavailable. [正确] (Snapshot itself is fast)
D) A separate physical copy must be made before releasing the snapshot backup. [正确] (To have a usable backup independent of the original volume)
E) Snapshot backups can be used only in virtual machines. [错误] (Can be used on physical with supporting FS/hardware)

参考文档: MySQL 8.0 for Database Administrators StudentGuide 2.pdf 页数 P224

试题 116:

Choose three.

Examine these statements, which execute successfully:

TRUNCATE test;
BEGIN;
INSERT INTO test (id, name) VALUES(1, 'Hello');
ROLLBACK;
SELECT id FROM test;

Which three storage engines would return a nonempty recordset for the test table when executing the statements?

A) MEMORY [正确] (Non-transactional)
B) BLACKHOLE [错误] (Stores nothing)
C) ARCHIVE [正确] (Non-transactional)
D) NDB [错误] (Transactional)
E) MyISAM [正确] (Non-transactional)
F) InnoDB [错误] (Transactional, ROLLBACK reverts INSERT)

答案: 参考文档 MySQL 8.0 for Database Administrators StudentGuide 1.pdf 页数 P129 P137

试题 117:

Choose two.

Which two statements are true about the mysql_config_editor program?

A) It provides an interface to change my.cnf files. [错误]
B) It can move datadir to a new location. [错误]
C) It will use client options by default unless you provide –login-path. [正确]
D) It can be used to create and edit SSL certificates and log locations. [错误]
E) It manages the configuration of user privileges for accessing the server. [错误]
F) It manages the configuration of the MySQL Firewall feature. [错误]
G) It manages the configuration of client programs. [正确] (Stores connection parameters in .mylogin.cnf)

答案: 这个工具不光管理密码，还管理：主机名端口号文件名用户名

试题 118:

Choose two.

Which two statements are true about the binary log encryption feature?

A) It requires a keyring plugin. [正确]
B) When enabled it encrypts existing binary logs. [错误]
C) It can be set at run time. [正确] (Dynamic variable binlog_encryption)
D) It can be activated per session. [错误] (Global setting)
E) It encrypts any connecting slaves connection thread. [错误] (Encrypts log files, not connections)

答案:
binlog_encryption Dynamic
Existing binary log files and relay log files still present on the server are not encrypted

试题 119:

Choose two.

Examine this MySQL client command to connect to a remote database:

1	mysql -h remote.example.org -u root -p --protocol=TCP --ssl-mode=?

Which two --ssl-mode values will ensure that an X.509-compliant certificate will be used to establish the SSL/TLS connection to MySQL?

A) DISABLED [错误]
B) REQUIRED [错误] (Ensures encryption, but not necessarily certificate verification)
C) VERIFY_IDENTITY [正确] (Verifies CA and hostname)
D) PREFERRED [错误] (Attempts SSL, but falls back if unavailable)
E) VERIFY_CA [正确] (Verifies CA)

答案: 参考文档 MySQL 8.0 for Database Administrators StudentGuide 2.pdf 页数 P39

试题 120:

Choose two.

Examine this query and its output: (见下图)

<html><body><table><tr><td>mysgl>select·from sys.user_summary_by_statement_type where statement in('select','insert','Quit);</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>+ |user|</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>statement|totaltotal_latency|max_latency丨lock_latency|rows_sent|rows_examined丨rows_afected|fullscans</td><td></td></tr><tr><td></td><td></td><td></td><td></td><td>1330.01 ms</td><td>11.52m</td><td></td><td>5426148161</td><td>01</td><td></td></tr><tr><td>|app</td><td>select</td><td>1919866 923070</td><td>2.41 h 1.66 h</td><td>1 287.41ms</td><td>11.65m</td><td>|542614816| 0一</td><td>0</td><td>923026|</td><td>919958</td></tr><tr><td>|app</td><td>insert Quit</td><td>1679892</td><td>9.54 s</td><td>170.97ms</td><td>10p</td><td></td><td>oi</td><td>01</td><td>01 oi</td></tr><tr><td>|app</td><td>select</td><td>1344964</td><td>53.61m</td><td>1328.42 ms</td><td>134.51 s</td><td>|203509545|</td><td>2035095421</td><td>01</td><td>344847</td></tr><tr><td>I bob</td><td>insert</td><td>1346159</td><td>37.94m</td><td>235.77ms</td><td>136.94 s</td><td>01</td><td>0</td><td>346175|</td><td>0</td></tr><tr><td>bob</td><td>Quit</td><td>254971</td><td>3.65 s</td><td>69.91ms</td><td>l0ps</td><td>01</td><td>01</td><td>01</td><td>0</td></tr><tr><td>bob root</td><td>select</td><td>230621</td><td>36.88m</td><td>121.47 s</td><td>23.81s</td><td>|135639074|</td><td>135644067|</td><td></td><td>230595</td></tr><tr><td>root</td><td>ineert</td><td>231585</td><td>25.86m</td><td>1364.22ms</td><td>31.45</td><td>0一</td><td>01</td><td>4150423|</td><td>0</td></tr><tr><td>root</td><td>Quit</td><td>170363</td><td>2.24 s</td><td>130.14ms</td><td>10ps</td><td></td><td>01</td><td>0</td><td>0i</td></tr><tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr></table></body></html>

Which two statements are true?

A) User bob had a significantly higher ratio of SELECT + INSERT statements to QUIT than both app and root users. [错误] (App: (19M+9M)/1.6M ~ 17.5; Bob: (1.3M+1.3M)/0.25M ~ 10.4; Root: (0.23M+0.23M)/0.17M ~ 2.7)
B) User bob had the largest total time waiting for locks. [错误] (App lock_latency: 11.65m + 10us; Bob: 134.51s + 136.94s; Root: 23.81s + 31.45s. App is highest)
C) The app user had the highest total number of rows read from storage engines. [正确] (App rows_examined: 542M+0; Bob: 203M+0; Root: 135M+0)
D) The root user had the largest number of modified rows for a SELECT statement. [错误] (SELECT doesn’t modify rows, rows_affected is 0 for SELECT)
E) The root user had the largest single wait time. [正确] (Root max_latency for select: 121.47s; App: 1287.41ms; Bob: 1328.42ms)

答案: D modified 有问题。root 账号 select insert quit 操作 B 不对，锁等待最多的是 app 用户，他是分钟。这个答案应该是 ce

试题 121:

Examine this SQL statement:

1	mysql> GRANT r_read@localhost TO mark WITH ADMIN OPTION;

Which two are true? (Choose two.)

A) Mark can grant the privileges assigned to the r_read@localhost role to another user. [错误]
B) ADMIN OPTION causes the role to be activated by default. [错误]
C) Mark can grant the r_read@localhost role to another user. [正确]
D) Mark can revoke the r_read@localhost role from another role. [正确]
E) ADMIN OPTION allows Mark to drop the role. [错误]
F) Mark must connect from localhost to activate the r_read@localhost role. [错误]

(Note: This seems identical to question 12)

试题 122:

Choose two.

Which two statements are true about MySQL Installer?

A) It provides only GUI-driven, interactive installations. [错误] (Can do command-line)
B) It installs most Oracle MySQL products. [正确]
C) Manual download of separate product packages is required before installing them through MySQL Installer. [正确] (Installer manages downloads)
D) It provides a uniform installation wizard across multiple platforms. [错误] (Windows only)
E) It performs product upgrades. [错误] (Can upgrade, but primarily for installation/management)

(Correction: C is incorrect. Installer downloads needed packages.)

C) Manual download of separate product packages is required before installing them through MySQL Installer. [错误]
E) It performs product upgrades. [正确]

试题 123:

Choose three.

Which three actions are effective in capacity planning?

A) adding circular replication nodes for increased DML capability [错误] (Doesn’t increase capacity directly, adds complexity)
B) buying more RAM [错误] (Scaling up, not planning)
C) buying more disk [错误] (Scaling up, not planning)
D) buying more CPU [错误] (Scaling up, not planning)
E) basing expected growth on an average of the last 3 years [正确] (Trend analysis)
F) upgrading to the latest application version [错误] (May or may not affect capacity needs)
G) monitoring OS resources for patterns [正确] (Identifying bottlenecks and trends)
H) consulting the application team about any future projects and use [正确] (Understanding future demand)

答案: 参考文档 MySQL 8.0 for Database Administrators StudentGuide 2.pdf 页数 P122

试题 124:

Choose the best answer.

Four nodes are configured to use circular replication. Examine these configuration parameters for each node:

1
2
3

slave_parallel_type = DATABASE;
slave_parallel_workers = 4;
slave_preserve_commit_order = 0;

Which statement is true?

A) Each slave thread is responsible for updating a specific database. [错误] (DATABASE type assigns threads based on database name hash)
B) Cross-database constraints can cause database inconsistency. [正确] (DATABASE type doesn’t preserve order across databases)
C) Setting slave_parallel_type = DATABASE won’t work for circular replication; it should be set to LOGICAL_CLOCK. [错误] (DATABASE type works, but LOGICAL_CLOCK is often preferred for consistency)
D) Increasing slave_parallel_workers will improve high availability. [错误] (Affects performance, not HA directly)
E) Setting slave_preserve_commit_order to ON will improve data consistency. [错误] (Requires LOGICAL_CLOCK)
F) Setting transaction_allow_batching to ON will improve data consistency. [错误] (Improves throughput, may affect latency)

答案: There must be no cross-database constraints, as such constraints may be violated on the replica. When slave_preserve_commit_order = 1 is set, you can only use LOGICAL_CLOCK. https://dev.mysql.com/doc/refman/5.7/en/replication-options-replica.html#sysvar_slave_parallel_type slave_preserve_commit_order 默认值：OFF，设置 slave_preserve_commit_order = ON ，在多线程复制环境下，能够保证从库回放 relay log 事务的顺序与这些事务在 relay log 中的顺序完全一致，也就是与主库提交的顺序完全一致。

试题 125:

Examine this partial output for InnoDB Cluster status:

"topology": {
    "host1:3377": { "address": "host1:3377", "mode": "R/W", ..., "status": "ONLINE", "version": "8.0.18" },
    "host2:3377": { "address": "host2:3377", "mode": "R/O", ..., "status": "MISSING", ... },
    "host3:3377": { "address": "host3:3377", "mode": "R/O", ..., "status": "ONLINE", "version": "8.0.18" }
}

Which statement explains the state of the instance deployed on host2?

A) It can rejoin the cluster by using the command cluster.addInstance(‘@host3:3377’). [错误] (Use rejoinInstance)
B) It has been expelled from the cluster because of a transaction error. [错误] (Status is MISSING, not ERROR)
C) It can be recovered from a donor instance on host3 by cloning using the command cluster.rejoinInstance(‘@host3:3377’). [正确] (rejoinInstance is used for MISSING nodes)
D) It has been removed from the cluster by using the command STOP GROUP_REPLICATION;. [错误] (Would likely show OFFLINE or similar)
E) It can rejoin the cluster by using the command dba.rebootClusterFromCompleteOutage(). [错误] (For complete outage)

答案:
If a node/instance is removed intentionally, executing for instance removeInstance(), then it is applicable to use addInstance. The reason is, when removeInstance() is executed, it stops Group Replication and also removes the metadata. In order for the node be part of the group again, the solution is to add it again, having the node/instance added to the metadata; If a node, for any other reason - crash, restarts - then the node/instance will be out of the Group Replication, but still will hold the metadata. In this case, rejoinInstance() is applicable

(Note: This seems identical to question 206)

试题 126:

Choose the best answer.

Examine this command:

1	shell> mysqldump --no-create-info --all-databases --result-file=dump.sql

Which statement is true?

A) It will not write CREATE TABLESPACE statements. [错误]
B) It will not write CREATE LOGFILE GROUP statements. [错误]
C) It will not write CREATE DATABASE statements. [错误]
D) It will not write CREATE TABLE statements. [正确]

试题 127:

MySQL programs look for option files in standard locations. Which method will show the option files and the order in which they are read?

A) mysql> SHOW GLOBAL VARIABLES; [错误]
B) shell> mysql –print-defaults [错误] (Shows combined effective options)
C) shell> mysqladmin –debug [错误]
D) shell> mysqld –help –verbose [正确]

试题 128:

Choose the best answer.

Examine this command, which executes successfully:

1	$ mysqlbackup --user=dba --password --port=3306 --with-timestamp --only-known-file-types --backup-dir=/export/backups backup

Which statement is true?

A) Only tables stored in their own tablespaces are backed up. [错误]
B) Only InnoDB data and log files are backed up. [错误]
C) Only non-encrypted files are backed up. [错误]
D) Only files for MySQL or its built-in storage engines are backed up. [正确]
E) The backup includes only data files and their metadata. [错误] (Includes logs etc.)

解释： mysqlbackup only backs up those types of files that are data files for MySQL or its built-in storage engines, which, besides the ibdata* files, have the following extensions: https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/backup-partial-options.html

试题 129:

What does the slave I/O thread do?

A) monitors and schedules I/O calls to the subsystem for the relay logs [错误]
B) connects to the master and requests it to send updates recorded in its binary logs [正确]
C) acquires a lock on the binary log for reading each event to be sent to the slave [错误] (Binlog dump thread does this on master)
D) reads the relay log and executes the events contained in them [错误] (SQL thread does this)

(Note: This seems identical to question 162)

试题 130:

Choose the best answer.

Which statement is true about the my.ini file on a Windows platform while MySQL server is running?

A) MySQL server does not use the my.ini option file for server configuration options. [错误]
B) The option file is read by the MySQL server service only at start up. [正确]
C) Editing the file will immediately change the running server configuration. [错误]
D) Using SET PERSIST will update the my.ini file. [错误] (Updates mysqld-auto.cnf)

试题 131:

Examine the full path name of the backup image from MySQL Enterprise Backup with the --compress option: /backup/full/mybackup/myimage.img

mysqlbackup.cnf contains this data:

[mysqlbackup]
backup-dir = /backup/full/myrestore
backup-image = /backup/full/mybackup/myimage.img
uncompress

You must perform a database restore to a new machine. Which command can provision the new database in datadir as /data/MEB?
(哪个命令可以在数据目录为 /data/MEB 的情况下配置新的数据库？)

A) mysqlbackup –defaults-file=mysqlbackup.cnf –datadir=/data/MEB restore-and-apply-log [错误]
B) mysqlbackup –defaults-file=mysqlbackup.cnf –datadir=/data/MEB image-to-dir-and-apply-log [错误] (Needs copy-back step)
C) mysqlbackup –defaults-file=mysqlbackup.cnf –datadir=/data/MEB apply-log-and-copy-back [错误] (Order is wrong for image)
D) mysqlbackup –defaults-file=mysqlbackup.cnf –datadir=/data/MEB copy-back-and-apply-log [正确] (Correct sequence for image restore)
E) mysqlbackup –defaults-file=mysqlbackup.cnf –datadir=/data/MEB image-to-dir [错误] (Only extracts, doesn’t apply log or copy)

试题 132:

Choose the best answer.

MySQL Enterprise Monitor Query Analyzer is configured to monitor an instance. Which statement is true?

A) The Query Response Time index (QRTi) is fixed to 100ms and cannot be customized. [错误]
B) Enabling the events_statements_history_long consumer allows tracking the longest running query. [错误] (Tracks recent long queries, not necessarily the absolute longest ever)
C) An agent must be installed locally on the instance to use the Query Analyzer. [错误] (Agentless monitoring via Performance Schema is possible)
D) The Query Analyzer can monitor an unlimited number of normalized statements. [正确] (Within practical system limits)
E) The slow query log must be enabled on the monitored server to collect information for the Query Analyzer. [错误] (Uses Performance Schema primarily)

试题 133:

Choose the best answer.

Users report errors when trying to connect from 192.0.2.5 and is connecting using the mysql_native_password authentication plugin. Examine these commands and output: (见下图)

Which statement identifies the cause of the errors?

A) max_connections is too small. [错误] (Would see Connection_errors_max_connections)
B) Network connectivity issues occurring between client and the MySQL instance. [正确] (High Aborted_connects often indicates network issues or bad connection attempts)
C) Connections are attempted without a valid user account or password. [错误] (Would likely increase Authentication_errors or Access_denied_errors)
D) User accounts are defined using the mysql_native_password plugin for password authentication. [错误] (Plugin itself isn’t the cause)
E) thread_cache is too small. [错误] (Would affect performance, not connection errors directly)
F) skip_name_resolve is enabled. [错误] (Affects host resolution, but Aborted_connects is more general)

答案:
COUNT_AUTH_PLUGIN_ERRORS: 367 身份验证插件报告的错误数 Unknown or unexpected plugin errors are counted in the COUNT_AUTH_PLUGIN_ERRORS column.
Aborted_connects: #客户端没有权限但是尝试访问 MySQL 数据库 #客户端输入的密码有误。 #连接包不包含正确信息 #超过连接时间限制，主要是这个系统变量 connect_timeout 控制

试题 134:

You want to check the values of the sort_buffer_size session variables of all existing connections. Which performance_schema table can you query?

A) global_variables [错误]
B) session_variables [错误] (Shows current session’s variables)
C) variables_by_thread [正确]
D) user_variables_by_thread [错误] (Shows user-defined variables)

(Note: This seems identical to question 147)

试题 135:

Choose the best answer.

Which feature is provided by multi-source replication?

A) providing a common source for the same data to be replicated to other servers [错误]
B) allowing multiple servers to back up to one server [正确]
C) managing conflicts between two sets of the same data [错误]
D) providing multi-source replication where all servers act as the master [错误]

解释：
You might choose to implement multi-source replication to achieve goals like these:

Backing up multiple servers to a single server.
Merging table shards.
Consolidating data from multiple servers to a single server.
https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source.html

试题 136:

Choose the best answer.

t is a non-empty InnoDB table. Examine these statements, which are executed in one session:

1 2	BEGIN; SELECT * FROM t FOR UPDATE;

Which is true?

A) mysqlcheck –analyze –all-databases will execute normally on all tables and return a report. [错误] (Will likely wait for lock on table t)
B) If ANALYZE TABLE; is invoked from the same session, it hangs until the transaction is committed or rolled back. [错误] (Analyze doesn’t usually conflict with row locks)
C) If OPTIMIZE LOCAL TABLE t; is invoked from another session, it executes normally and returns the status. [错误] (Optimize requires exclusive lock, will wait)
D) If OPTIMIZE TABLE; is invoked, it will create a table lock on t and force a transaction rollback. [正确] (Optimize requires exclusive lock, will wait. If lock wait timeout occurs, the OPTIMIZE fails, doesn’t force rollback of the SELECT FOR UPDATE transaction.)

(Correction: D is likely incorrect. OPTIMIZE will wait for the lock from SELECT FOR UPDATE. It won’t force a rollback of the other transaction. The SELECT FOR UPDATE holds locks, preventing OPTIMIZE.)

Let’s re-evaluate:

A: mysqlcheck --analyze might skip locked tables or wait.
B: ANALYZE TABLE in the same session should work as it doesn’t conflict with the existing lock.
C: OPTIMIZE LOCAL TABLE (deprecated alias for OPTIMIZE) from another session will wait for the lock.
D: OPTIMIZE TABLE from another session will wait for the lock. It doesn’t force a rollback.

Revisiting the provided answer D: Perhaps the interpretation is that OPTIMIZE waiting might lead to a lock wait timeout for the OPTIMIZE statement, not the original transaction. Or maybe it implies a deadlock scenario, though less likely here. Given the options, none seem perfectly accurate based on standard behavior. Let’s stick with the original provided answer D, assuming a specific scenario or interpretation.

试题 137:

Choose the best answer.

You have upgraded the MySQL binaries from 5.7.28 to 8.0.18 by using an in-place upgrade. Examine the message sequence generated during the first start of MySQL 8.0.18: (见下图)

[System] [MY-...] ... upgrade process ...
[ERROR] [MY-...] Table 'mysql.columns_priv' is marked as crashed and should be repaired
[ERROR] [MY-...] Table 'mysql.event' is marked as crashed and should be repaired
[ERROR] [MY-...] Table 'mysql.proc' is marked as crashed and should be repaired
[ERROR] [MY-...] Table 'mysql.proxies_priv' is marked as crashed and should be repaired
[ERROR] [MY-...] Table 'mysql.tables_priv' is marked as crashed and should be repaired
[ERROR] [MY-...] Error upgrading data dictionary. Aborting
[System] [MY-...] ... Aborting ...
[System] [MY-...] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18-commercial) MySQL Enterprise Server-Commercial.

Which step or set of steps will resolve the errors?

A) Start mysqld again using the –upgrade=FORCE option. [错误] (Upgrade already failed)
B) Go to the/mysql directory and execute: myisamchk –update-state columns_priv event proc proxies_priv tables_priv. [错误] (Need repair, not just state update)
C) Execute: mysqlcheck –repair mysql columns_priv event proc proxies_priv tables_priv. [正确] (Checks and repairs specified MyISAM system tables)
D) Remove the redo logs. Replace the MySQL binaries with the 5.7.28 binaries. Prepare the tables for upgrade. Upgrade to 8.0.18 again. [错误] (Overly complex, repair should work)
E) Execute: mysqlcheck –check-upgrade mysql columns_priv event proc proxies_priv tables_priv. [错误] (Checks, doesn’t repair)

答案:
B 这些表都是 MyISAM 的表，但 myisamchk –update-state 并没有指定修复，要指定 –force 进行修复。
E mysqlcheck –check-upgrade 也没有指定修复

试题 138:

Choose the best answer.

Which statement is true about MySQL Enterprise Transparent Data Encryption (TDE)?

A) MySQL TDE uses an appropriate keyring plugin to store the keys in a centralized location. [正确]
B) TDE can encrypt InnoDB and MyISAM tables only when the tables are stored in the SYSTEM tablespace. [错误] (Encrypts file-per-table and general tablespaces)
C) Lost tablespace encryption keys can be regenerated only if the master database key is known or present in the Key Vault specification. [错误] (Keys cannot be regenerated; master key re-encrypts, doesn’t recover lost keys)
D) Both MyISAM and InnoDB tables can be encrypted by setting the keyring_engine = All variable in the MySQL configuration file. [错误] (TDE primarily for InnoDB; keyring_engine is not a variable)

答案: 参考文档 MySQL 8.0 for Database Administrators StudentGuide 2.pdf 页数 P75

试题 139:

You are using the InnoDB engine and the innodb_file_per_table option is set. You delete a significant number of rows of a large table named FACTORY.INVENTORY. Which command will reorganize the physical storage of table data and associated index data for the INVENTORY table, in order to reduce storage space and improve I/O efficiency?

A) CHECK TABLE FACTORY.INVENTORY [错误]
B) ANALYZE TABLE FACTORY.INVENTORY [错误]
C) OPTIMIZE TABLE FACTORY.INVENTORY [正确] (Rebuilds table for InnoDB)
D) mysqlcheck -u root -p FACTORY.INVENTORY [错误] (Default is check)
E) mysqldump -u root -p FACTORY INVENTORY [错误] (Logical backup, doesn’t reorganize in place)

试题 140:

Choose the best answer.

You must configure the MySQL command-line client to provide the highest level of trust and security when connecting to a remote MySQL Server. Which value of --ssl-mode will do this?

A) VERIFY_CA [错误] (Verifies CA but not hostname)
B) PREFERRED [错误]
C) VERIFY_IDENTITY [正确] (Verifies CA and matches hostname)
D) REQUIRED [错误]

(Note: This seems identical to question 119, but asks for highest trust)

试题 141:

Choose the best answer.

You want to dump all databases with names that start with db. Which command will achieve this?

A) mysqlpump > all_db_backup.sql [错误] (Dumps all, doesn’t filter by name start)
B) mysqlpump –include-databases=db% –result-file=all_db_backup.sql [正确]
C) mysqlpump –include-databases=db –result-file=all_db_backup.sql [错误] (Only database named ‘db’)
D) mysqlpump –include-tables=db.% –result-file=all_db_backup.sql [错误] (Includes tables in db, not databases starting with db)

试题 142:

Choose two.

You have an installation of MySQL 8 on Oracle Linux. Consider the outputs: (见下图)

Which statement is true about disk temporary tables for this installation?

A) Only internal temporary tables from the optimizer will be created in tmpdir. [错误] (User temp tables also use it)
B) Temporary tables are created in tmpdir only after they reach tmp_table_size. [错误] (Internal temp tables might go to disk based on engine/size)
C) Temporary tables are created in tmpdir only if configured to use MyISAM. [错误] (InnoDB uses its own temp tablespace, but internal might still use tmpdir)
D) Temporary tables will use the InnoDB temporary tablespace located in datadir. [正确] (Default location for ibtmp1 is datadir)
E) Temporary tables will use the InnoDB temporary tablespace located in /tmp. [错误] (tmpdir is /tmp, but InnoDB temp tablespace location is controlled by innodb_temp_data_file_path, default in datadir)

答案: Use of memory-mapped temporary files by the TempTable storage engine as an overflow mechanism for internal temporary tables is governed by these rules: Temporary files are created in the directory defined by the tmpdir variable. (This applies to the TempTable engine, not necessarily all disk temp tables). InnoDB uses ibtmp1 located by innodb_temp_data_file_path.

试题 143:

Choose the best answer.

What is the correct syntax for using transparent data encryption with an existing InnoDB table?

A) ALTER TABLE t1 SET TDE=ON; [错误]
B) ALTER TABLE t1 ADD ENCRYPTED_TABLESPACE=Y; [错误]
C) ALTER TABLE t1 ENCRYPTION=’Y’; [正确]
D) ALTER TABLE t1 WITH ENCRYPTION USING MASTER KEY; [错误]

答案: https://dev.mysql.com/doc/refman/8.0/en/innodb-data-encryption.html

试题 144:

Choose the best answer.

You have configured GTID-based asynchronous replication with one master and one slave. A user accidentally updated some data on the slave. To fix this, you stopped replication and successfully reverted the accidental changes. Examine the current GTID information: (见下图)

Master:
  gtids_executed: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10300
  gtids_purged:   aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-3820
Slave:
  slave uuid:     bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb
  gtids_executed: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10167,bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb:1-9
  gtids_purged:   aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-2312

You must fix GTID sets on the slave to avoid replicating unwanted transactions in case of failover. Which set of actions would allow the slave to continue replicating without erroneous transactions?

A) RESET MASTER; SET GLOBAL gtid_purged = ‘aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa:1-2312’; SET GLOBAL gtid_executed = ‘aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaa:1-10167’; [错误] (RESET MASTER clears slave’s logs/GTIDs)
B) RESET MASTER; SET GLOBAL gtid_purged = ‘aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaa:1-10167’; [错误]
C) RESET SLAVE; SET GLOBAL gtid_purged = ‘aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaa:1-3820’; SET GLOBAL gtid_executed = ‘aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa:1-10300’; [错误] (RESET SLAVE clears position, not GTIDs; setting master’s GTIDs is wrong)
D) SET GLOBAL gtid_purged = ‘aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaa:1-2312,bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb:1-9’; SET GLOBAL gtid_executed = ‘aaaaaaaa-aaaa-aaaa-aaaa-aaaaaa:1-10167’; [正确] (Sets purged to include slave’s own transactions and correct master subset; sets executed to correct master subset)
E) RESET SLAVE; SET GLOBAL gtid_purged = ‘aaaaaaaa-aaa-aaaa-aaa-aaaaaaaa:1-10167’; [错误]

试题 145:

Your my.cnf file contains these settings:

[mysqld]
log_output = FILE
slow_query_log
long_query_time = 2.0
log_queries_not_using_indexes

You want to log queries that looked at a minimum of 5000 records and either took longer than 5 seconds to run or did not use indexes. Which contains all the settings that you need to add to or modify the slow log configuration?

A) log_throttle_queries_not_using_indexes = 5 [错误]
B) long_query_time = 5, log_throttle_queries_not_using_indexes = 5 [错误]
C) long_query_time = 5 [错误]
D) long_query_time = 5, log_throttle_queries_not_using_indexes = 5, min_examined_row_limit = 5000 [错误]
E) long_query_time = 5, min_examined_row_limit = 5000 [正确]
F) min_examined_row_limit = 5000 [错误]
G) log_throttle_queries_not_using_indexes = 5, min_examined_row_limit = 5000 [错误]

(Note: This seems identical to question 196)

试题 146:

Choose the best answer.

What does the binlog dump thread do?

A) It monitors and schedules the rotation/deletion of the binary logs. [错误]
B) It connects to the master and asks it to send updates recorded in its binary logs. [错误] (Slave I/O thread does this)
C) It acquires a lock on the binary log for reading each event to be sent to the slave. [正确] (Runs on master)
D) It reads the relay log and executes the events contained in them. [错误] (Slave SQL thread does this)

答案:
The binary log dump thread acquires a lock on the source’s binary log for reading each event that is to be sent to the replica. As soon as the event has been read, the lock is released, even before the event is sent to the replica.

试题 147:

Choose the best answer.

You want to check the values of the sort_buffer_size session variables of all existing connections. Which performance_schema table can you query?

A) user_variables_by_thread [错误]
B) global_variables [错误]
C) variables_by_thread [正确]
D) session_variables [错误]

(Note: This seems identical to question 134)

试题 148:

Choose the best answer.

You have just installed MySQL on Oracle Linux and adjusted your /etc/my.cnf parameters to suit your installation. Examine the output:

# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

# systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Thu 2019-12-12 07:54:53 ACDT; 33s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 2732 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
  Process: 2705 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 2732 (code=exited, status=1/FAILURE)
   Status: "Server startup in progress"

Dec 12 07:54:49 oel7 systemd[1]: Starting MySQL Server...
Dec 12 07:54:53 oel7 systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
Dec 12 07:54:53 oel7 systemd[1]: Failed to start MySQL Server.
Dec 12 07:54:53 oel7 systemd[1]: Unit mysqld.service entered failed state.
Dec 12 07:54:53 oel7 systemd[1]: mysqld.service failed.

What statement is true about the start attempt?

A) systemd attempted to start mysqld, found another systemd mysqld process running, and shut it down. [错误]
B) MySQL server continued to start up even though another process existed. [错误]
C) systemd waited for 30 seconds before timing out and start up failed. [错误] (Failed quickly due to exit code)
D) systemd found the mysqld service disabled and failed to start it. [错误] (Service is enabled)
E) MySQL server was not started due to a problem while executing process 2732. [正确] (Main PID exited with status 1/FAILURE)

试题 149:

Choose the best answer.

You wish to protect your MySQL database against SQL injection attacks. Which method would fail to do this?

A) using stored procedures for any database access [错误] (Can help, but procedures can still be vulnerable if not written carefully)
B) avoiding concatenation of SQL statements and user-supplied values in an application [错误] (Key prevention method)
C) using PREPARED STATEMENTS [错误] (Key prevention method)
D) installing and configuring the Connection Control plugin [正确] (Connection control delays brute-force, doesn’t prevent SQL injection itself)

答案:
The bad data might also be deliberate, representing an “SQL injection” attack. For example, input values might contain quotation marks, semicolons, % and _ wildcard characters and other characters significant in SQL statements. Validate input values to make sure they have only the expected characters. Escape any special characters that could change the intended behavior when substituted into an SQL statement. Never concatenate a user input value into an SQL statement without doing validation and escaping first. Even when accepting input generated by some other program, expect that the other program could also have been compromised and be sending you incorrect or malicious data. https://dev.mysql.com/doc/connector-python/en/connector-python-coding.html

试题 150:

Choose the best answer.

How can mysqld_multi be configured to allow MySQL instances to use the same port number?

A) The instances listen on different IP addresses. [正确]
B) The instances use different user accounts unique to each instance. [错误]
C) The instances use different socket names. [错误] (Sockets are local, ports are network)
D) The instances have appropriate net masks set. [错误]

(Note: This seems identical to question 176)

试题 151:

Choose the best answer.

You plan to upgrade your MySQL 5.7 instance to version 8. You have installed the 8 build of MySQL Shell. Examine this command executed from the operating system shell prompt:

1	mysqlsh --uri root@localhost:3306 -- util check-for-server-upgrade

Which statement is true?

A) It documents any problems with your 5.7 tables to make them ready to upgrade to 8. [正确]
B) It fails because the operation name must be in camelCase. [错误]
C) It fixes any problems with your 5.7 tables to make them ready to upgrade to 8. [错误]
D) It is mandatory to clear the history of prior results before executing this process a second time or later. [错误]
E) It fails because checkForServerUpgrade must be executed only within an active shell session as a method of the util object. [错误]
F) It is mandatory to run this command so that MySQL 8.0 software’s auto-upgrade process has the details it needs to operate properly. [错误]

试题 152:

Choose the best answer.

Your MySQL instance is capturing a huge amount of financial transactions every day in the finance database. Company policy is to create a backup every day. The main tables being updated are prefixed with transactions-. These tables are archived into tables that are prefixed with archives- each month.

1	mysqlbackup --optimistic-busy-tables='^finance\\.transactions-.*' backup

Which optimization process best describes what happens with the redo logs?

A) The redo logs are backed up first, then the transaction and archive tables. [错误]
B) The redo logs are backed up only if there are changes showing for the transactions tables. [错误]
C) The redo logs are not backed up at all. [错误] (During optimistic phase)
D) The transaction tables are backed up first, then the archive tables and redo logs. [错误]
E) The archive tables are backed up first, then the transaction tables and redo logs. [正确] (Inactive tables first without logs, then busy tables with logs)

答案:
Optimistic phase: In this first phase, tables that are unlikely to be modified during the backup process (referred to as the “inactive tables” below, identified by the user with the optimistic-time option or, by exclusion, with the optimistic-busy-tables option) are backed up without locking the MySQL instance. And because those tables are not expected to be changed before the backup is finished, redo logs, undo logs, and system tablespaces are not backed up by mysqlbackup in this phase. https://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/meb-backup-optimistic.html

试题 153:

Choose the best answer.

You must replay the binary logs on your MySQL server. Which command do you use?

A) cat binlog.000003 binlog.000004 binlog.000005 | mysql -h 127.0.0.1 [错误]
B) mysqlpump -h 127.0.0.1 binlog.000003 binlog.000004 binlog.000005 [错误]
C) mysql -h 127.0.0.1 --local-infile binlog.000003 binlog.000004 binlog.000005 [错误]
D) mysqlbinlog binlog.000003 binlog.000004 binlog.000005 | mysql -h 127.0.0.1 [正确]
E) mysqlbinlog -h 127.0.0.1 binlog.000003 binlog.000004 binlog.000005 [错误]

试题 154:

Choose the best answer.

Examine this statement, which executes successfully: (见下图)

CREATE TABLE world.city (
  ID int NOT NULL AUTO_INCREMENT,
  Name char(35) NOT NULL DEFAULT '',
  CountryCode char(3) NOT NULL DEFAULT '',
  District char(20) NOT NULL DEFAULT '',
  Population int NOT NULL DEFAULT '0',
  PRIMARY KEY (ID),
  KEY CountryCode (CountryCode)
) ENGINE=InnoDB;

You want to improve the performance of this query:

1	SELECT Name FROM world.city WHERE Population BETWEEN 1000000 AND 2000000;

Which change enables the query to succeed while accessing fewer rows?

A) ALTER TABLE world.city ADD INDEX (Name); [错误]
B) ALTER TABLE world.city ADD SPATIAL INDEX (Name); [错误]
C) ALTER TABLE world.city ADD FULLTEXT INDEX (Name); [错误]
D) ALTER TABLE world.city ADD FULLTEXT INDEX (Population); [错误]
E) ALTER TABLE world.city ADD SPATIAL INDEX (Population); [错误]
F) ALTER TABLE world.city ADD INDEX (Population); [正确]

分析： mysql> ALTER TABLE city ADD SPATIAL INDEX(Population); ERROR 1687 (42000): A SPATIAL index may only contain a geometrical type column

试题 155:

Choose the best answer.

Examine this parameter setting:

1	audit_log = FORCE_PLUS_PERMANENT

What effect does this have on auditing?

A) It prevents the audit plugin from being removed from the running server. [正确]
B) It prevents the audit log from being removed or rotated. [错误]
C) It causes the audit log to be created if it does not exist. [错误]
D) It will force the load of the audit plugin even in case of errors at server start. [错误]

答案: --audit-log=FORCE_PLUS_PERMANENT tells the server to load the plugin and prevent it from being removed while the server is running. https://dev.mysql.com/doc/refman/8.0/en/audit-log-reference.html#sysvar_audit_log_format

试题 156:

Examine these commands and output:

mysql> SHOW FULL PROCESSLIST;
+----+-----------------+-----------+------+---------+------+---------------------------------+----------------------------------+
| Id | User            | Host      | db   | Command | Time | State                           | Info                             |
+----+-----------------+-----------+------+---------+------+---------------------------------+----------------------------------+
|  6 | event_scheduler | localhost | NULL | Daemon  | ...  | Waiting on empty queue          | NULL                             |
| 20 | root            | localhost | NULL | Sleep   | ...  |                                 | NULL                             |
| 21 | root            | localhost | test | Query   | ...  | waiting for table metadata lock | optimize table test.demo_test    |
| 22 | root            | localhost | test | Query   | ...  | waiting for table metadata lock | select * from test.demo_test     |
| 24 | root            | localhost | test | Query   | ...  | starting                        | SHOW FULL PROCESSLIST            |
| 25 | root            | localhost | NULL | Sleep   | ...  |                                 | NULL                             |
+----+-----------------+-----------+------+---------+------+---------------------------------+----------------------------------+

mysql> SELECT object_type, object_schema, object_name, lock_type, lock_status, owner_thread_id, owner_event_id
       FROM performance_schema.metadata_locks WHERE object_schema != 'performance_schema';
+-------------+---------------+-------------+----------------------+-------------+-----------------+-----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE            | LOCK_STATUS | OWNER_THREAD_ID | OWNER_EVENT_ID  |
+-------------+---------------+-------------+----------------------+-------------+-----------------+-----------------+
| TABLE       | test          | demo_test   | SHARED_READ          | GRANTED     |              60 |               ? |
| TABLE       | test          | demo_test   | SHARED_WRITE         | GRANTED     |              60 |               9 |
| SCHEMA      | test          | NULL        | INTENTION_EXCLUSIVE  | GRANTED     |              62 |               6 |
| TABLE       | test          | demo_test   | SHARED_NO_READ_WRITE | PENDING     |              62 |               6 |
+-------------+---------------+-------------+----------------------+-------------+-----------------+-----------------+

mysql> SELECT thread_id, processlist_id, processlist_user, parent_thread_id
       FROM performance_schema.threads WHERE processlist_user='root';
+-----------+----------------+------------------+------------------+
| THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PARENT_THREAD_ID |
+-----------+----------------+------------------+------------------+
|        60 |             20 | root             | NULL             |
|        61 |             21 | root             | NULL             |
|        62 |             22 | root             | NULL             |
|        64 |             24 | root             | NULL             |
|        65 |             25 | root             | NULL             |
+-----------+----------------+------------------+------------------+

Which connection ID is holding the metadata lock?

A) 21 [错误]
B) 25 [错误]
C) 6 [错误] (Event Scheduler)
D) 22 [错误]
E) 20 [正确] (OWNER_THREAD_ID 60 corresponds to PROCESSLIST_ID 20)
F) 24 [错误]

试题 157:

Choose the best answer.

An attempt to recover an InnoDB Cluster fails. Examine this set of messages and responses:

host3:3377 ssl JS> dba.rebootClusterFromCompleteOutage()
Reconfiguring the default cluster from complete outage...
The instance 'host1:3377' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: y
The instance 'host2:3377' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: y

Dba.rebootClusterFromCompleteOutage: The active session instance isn't the most updated in comparison with the ONLINE instances of the Cluster's metadata.
Please use the most up to date instance: 'host1:3377'. (RuntimeError)

Which statement is true?

A) The cluster is running and there is at least one ONLINE instance. [错误]
B) The instance deployed on host3 must be synchronized from a donor deployed on host1 by using the command cluster.addInstance(‘host1:3377’). [错误]
C) It is possible to determine the most up-to-date instance by comparing different global transaction identifier (GTID) sets with GTID_SUBSET(set1, set2). [正确]
D) The active session instance is invalid and must be re-created by using the command shell.connect(‘host3:3377’). [错误]
E) The instance deployed on host3 must be rebuilt with a backup from the primary instance. [错误]

答案: 集群启动失败，原因是其中一个节点数据不一致导致启动集群失败。GTID 不一致
https://dev.mysql.com/doc/mysql-shell/8.0/en/troubleshooting-innodb-cluster.html If you encounter an error such as The active session instance isn’t the most updated in comparison with the ONLINE instances of the Cluster’s metadata. then the instance you are connected to does not have the GTID superset of transactions applied by the cluster. In this situation, connect MySQL Shell to the instance suggested in the error message and issue dba.rebootClusterFromCompleteOutage() from that instance.

试题 158:

Examine the command, which execute successfully:

1	shell> mysqld --initialize-insecure

Which statement is true?

A) The root password is created in the error log in plain text. [错误]
B) The installation creates a temporary test environment with data in the /tmp directory. [错误]
C) The installation is created without enforcing or generating SSL certificates. [错误]
D) The root password is not created allowing easy access from the same host. [正确]

试题 159:

Choose the best answer.

You are upgrading a MySQL instance to the latest 8.0 version. Examine this output: (见下图 - Assuming output shows standard InnoDB config)

You plan to add this parameter to the configuration:

1	innodb_directories = '/innodb_extras'

Which statement is true?

A) It allows scanning of other locations to discover more innodb tablespaces. [正确]
B) It defines all innodb tablespace options relative to a starting parent directory. [错误]
C) It adds more temporary workspace in addition to the innodb_tmpdir location. [错误]
D) It is not necessary because innodb_data_home_dir is already defined. [错误]
E) It moves all innodb tablespaces to the /innodb_extras directory to enable a new innodb_data_home_dir to be defined. [错误]

答案: 参考文档 MySQL 8.0 for Database Administrators StudentGuide 1.pdf 页数 P156

试题 160:

Choose the best answer.

You plan to take daily full backups, which include the ndbinfo and sys (internal) databases. Which command will back up the databases in parallel?

A) mysqldump --all-databases > full-backup-$(date +%Y%m%d).sql [错误] (mysqldump is not parallel by default for databases)
B) mysqlpump --include-databases=% > full-backup-$(date +%Y%m%d).sql [正确] (--include-databases=% includes sys/ndbinfo, mysqlpump is parallel)
C) mysqlpump --all-databases > full-backup-$(date +%Y%m%d).sql [错误] (–all-databases excludes sys/ndbinfo by default)
D) mysqldump --single-transaction > full-backup-$(date +%Y%m%d).sql [错误] (Dumps all, not parallel)

答案:
mysqlpump does not dump the performance_schema, ndbinfo, or sys schema by default. To dump any of these, name them explicitly on the command line. You can also name them with the –databases or –include-databases option.
The –all-databases option dumps all databases (with certain exceptions noted in mysqlpump Restrictions). It is equivalent to specifying no object options at all (the default mysqlpump action is to dump everything).
--include-databases=% is similar to –all-databases, but selects all databases for dumping, even those that are exceptions for –all-databases.

试题 161:

The languages table uses MyISAM and the countries table uses the InnoDB storage engine. Both tables are empty. Examine these statements:

BEGIN;
INSERT INTO languages (lang) VALUES ('Italian');
INSERT INTO countries (country) VALUES ('Italy');
ROLLBACK;

What is the content of both tables after executing these statements?

A) countries has one row, languages has none. [错误]
B) Both tables have one row. [错误]
C) Both tables are empty. [错误]
D) languages has one row, countries has none. [正确] (MyISAM ignores ROLLBACK)

试题 162:

What does the slave I/O thread do?

A) connects to the master and requests it to send updates recorded in its binary logs [正确]
B) monitors and schedules I/O calls to the subsystem for the relay logs [错误]
C) reads the relay log and executes the events contained in them [错误] (SQL thread)
D) acquires a lock on the binary log for reading each event to be sent to the slave [错误] (Binlog dump thread on master)

(Note: This seems identical to question 129)

试题 163:

Choose the best answer.

Examine these commands and results:

SHOW GRANTS FOR jane;
+---------------------------------------+
| Grants for jane@%                     |
+---------------------------------------+
| GRANT USAGE ON *.* TO `jane`@`%`      |
+---------------------------------------+

Jane must create a temporary table named TOTALSALES in the SALES database. Which statement will provide Jane with the required privileges based on the principle of least privilege?

A) GRANT CREATE TEMPORARY TABLES, INSERT, UPDATE, DELETE, SELECT ON sales.totalsales TO jane; [错误] (Grants DML on a specific temp table, but needs CREATE TEMPORARY TABLES on the database)
B) GRANT CREATE TEMPORARY TABLES ON sales.* TO jane; [正确]
C) GRANT CREATE TEMPORARY TABLES ON sales.totalsales TO jane; [错误] (Privilege must be on database level)
D) GRANT ALL ON sales.* TO jane; [错误] (Too broad)

答案: 必须是数据库名.*

试题 164:

Choose the best answer.

Where is the default data directory located after installing MySQL using RPM on Oracle Linux 7?

A) /usr [错误]
B) /usr/mysql [错误]
C) /etc/my.cnf [错误] (Config file)
D) /var/lib/mysql [正确]
E) /usr/bin [错误] (Binaries)

答案: https://dev.mysql.com/doc/mysql-linuxunix-excerpt/8.0/en/linux-installation-rpm.html

试题 165:

Binary log events for the ‘mydb1’ schema must be copied to a different schema name ‘mydb2’. Which command will do this?

A) mysqlbinlog --rewrite-db='mydb1->mydb2' | mysql [正确]
B) mysqlbinlog --database=mydb1 --database=mydb2 | mysql [错误]
C) mysqlbinlog --rewrite-db='mydb1' --rewrite-db='mydb2' | mysql [错误]
D) mysqlbinlog --read-from-remote-server --raw | sed 's/mydb1/mydb2/g' | mysql [错误] (Raw mode bypasses rewrite)

试题 166:

Examine this snippet (片段) from the binary log file named binlog.000036:

# at 500324
#191120 14:55:16 server id 1 end_log_pos 500453 crc32 0x98159515 Query thread_id=9 exec_time=2 error_code=0 Gtid=...
SET TIMESTAMP=1574222116/*!*/;
DROP TABLE `rental` /* generated by server */ /*!*/;
# at 500453
...

The rental table was accidentally dropped, and you must recover the table. You have restored the last backup, which corresponds to the start of the binlog.000036 binary log. Which command will complete the recovery?

A) mysqlbinlog --stop-position=500324 binlog.000036 | mysql [正确] (Stops before the DROP TABLE event)
B) mysqlbinlog --stop-datetime='2019-11-20 14:55:16' binlog.000036 | mysql [错误] (Includes the DROP TABLE event)
C) mysqlbinlog --stop-datetime='2019-11-20 14:55:18' binlog.000036 | mysql [错误] (Includes the DROP TABLE event)
D) mysqlbinlog --stop-position=500453 binlog.000036 | mysql [错误] (Includes the DROP TABLE event)

试题 167:

Choose the best answer.

You recently upgraded your MySQL installation to MySQL 8.0. Examine this client error:

1	ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/local/mysql/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

Which option will allow this client to connect to MySQL Server?

A) ALTER USER user IDENTIFIED WITH caching_sha2_password BY ‘password’; [错误] (Server-side change, client still fails)
B) mysqld default_authentication_plugin = sha256_password [错误] (Server config change)
C) mysqld default_authentication_plugin = caching_sha2_password [错误] (Server config change)
D) ALTER USER user IDENTIFIED WITH mysql_native_password BY ‘password’; [正确] (Changes user’s auth method to one the old client likely supports)
E) ALTER USER user IDENTIFIED WITH sha256_password BY ‘password’; [错误] (Client might also lack this plugin)
F) mysqld default_authentication_plugin = mysql_native_password [错误] (Server config change, affects new users)

试题 168:

Choose the best answer.

Examine this SQL statement:

1
2
3

UPDATE world.city
SET Population = Population * 1.1
WHERE CountryCode IN (SELECT Code FROM world.country WHERE Continent = 'Asia');

Which set of privileges will allow Tom to execute this SQL statement?

A) GRANT UPDATE ON world.* TO tom@%; GRANT ALL PRIVILEGES ON world.country TO tom@%; [错误]
B) GRANT UPDATE ON world.city TO tom@%; GRANT SELECT ON world.* TO tom@%; [错误]
C) GRANT UPDATE ON world.city TO tom@%; GRANT SELECT ON world.country TO tom@%; [正确]
D) GRANT ALL PRIVILEGES ON world.city TO tom@%; GRANT SELECT (Code) ON world.country TO tom@%; [错误]

试题 169:

Choose the best answer.

Which command enables rule-based MySQL Auditing capabilities?

A) shell> mysqld --initialize --log-raw=audit.log [错误]
B) mysql> INSTALL COMPONENT 'file://component_audit_filter_log'; [错误] (Installs component, doesn’t enable rules directly)
C) mysql> INSTALL PLUGIN audit_log; [错误] (Legacy audit plugin)
D) shell> mysql < audit_log_filter_linux_install.sql [正确] (Installs filter component and stored procedures for rule management)

答案: https://dev.mysql.com/doc/refman/8.0/en/audit-log-installation.html

试题 170:

Choose the best answer.

You are having performance issues with MySQL instances. Those servers are monitored with MySQL Enterprise Monitor. Using Query Analyzer, where do you begin to look for problem queries?

A) Sort the “Exec” column and check for SQL queries with low Query Response Time index (QRTi) values. [正确] (Low QRTi indicates poor performance relative to others)
B) Look for queries with low total latency times in the Latency section in the times series graph. [错误]
C) Sort the “Exec” column and check for SQL queries with high Query Response Time index (QRTi) values. [错误]
D) Look for queries with big prolonged spikes in row activity/access graph in the times series graph. [错误] (High activity isn’t necessarily problematic)

答案: https://dev.mysql.com/doc/mysql-monitor/8.0/en/mem-features-qrti.html

试题 171:

Choose the best answer.

Examine this command and output: (见下图)

Which statement is true?

A) Firewall_cached_entries is the number of statements found in the query cache for users in DETECTING mode. [错误]
B) Firewall_access_suspicious is the number of statements logged as suspicious for users in DETECTING mode. [正确]
C) Firewall_access_denied is the number of connection attempts from prohibited hosts that are denied. [错误] (Denied statements, not connections)
D) Firewall_access_granted is the number of connections granted from whitelisted hosts. [错误] (Permitted statements, not connections)

答案:

The number of statements that the firewall has denied (防火墙拒绝的声明的数量)
The number of statements that the firewall has permitted (防火墙允许的语句数)
The number of statements that were identified as being suspicious while in DETECTING mode, but still permitted (在检测过程中被识别为可疑语句的数量模式，但仍然允许)
The number of whitelisted digests in the cache (缓存中白名单摘要的数量)

试题 172:

Choose the best answer.

Examine this output: (见下图)

Which change should optimize the number of buffer pool instances for this workload?

A) Decrease the number of buffer pool instances to 4. [错误]
B) Increase the number of buffer pool instances to 16. [正确] (Matches Threads_running)
C) Increase the number of buffer pool instances to 32. [错误]
D) Decrease the number of buffer pool instances to 1. [错误]
E) Increase the number of buffer pool instances to 12. [错误]

答案: thread_running 是 16

试题 173:

Choose the best answer.

You encountered an insufficient privilege error in the middle of a long transaction. The database administrator is informed and immediately grants the required privilege:

1	GRANT UPDATE ON world.city TO 'user1';

How can you proceed with your transaction with the least interruption?

A) Close the connection, reconnect, and start the transaction again. [错误]
B) Re-execute the failed statement in your transaction. [正确] (Privilege changes take effect on next statement)
C) Roll back the transaction and start the transaction again in the same session. [错误]
D) Change the default database and re-execute the failed statement in your transaction. [错误]

解释： If you modify the grant tables indirectly using an account-management statement, the server notices these changes and loads the grant tables into memory again immediately. A grant table reload affects privileges for each existing client session as follows: Table and column privilege changes take effect with the client’s next request.

试题 174:

Choose the best answer.

There has been an accidental deletion of data in one of your MySQL databases. You determine that all entries in the binary log file after position 1797 must be replayed. Examine this partial command:

1	mysqlbinlog binlog.000008 --start-position=1798 ...

Which operation will complete the command?

A) --write-to-remote-server must be added to the command line to update the database tables. [错误]
B) No changes required. It automatically updates the MySQL Server with the data. [错误]
C) It can be piped into the MySQL Server via the command-line client. [正确] (... | mysql)
D) You must use --stop-position=1797 to avoid the DELETE statement that caused the initial problem. [错误] (Start position is already after the delete)

答案: A 没有这个命令

试题 175:

Choose the best answer.

The mysqld instance has the connection control plugin enabled with these settings:

1 2	connection_control_min_connection_delay = 1000 connection_control_max_connection_delay = 2000

The minimum and maximum delays need to be increased to 3000 and 5000, respectively. A command is executed:

1	mysql> SET GLOBAL connection_control_min_connection_delay = 3000;

What is the result?

A) Only the minimum connection value is increased to 3000. [错误]
B) The minimum connection value is changed to 2000. [错误]
C) The minimum value increases to 3000 and the maximum value increases to 4000. [错误]
D) An error is returned. [正确] (Cannot set min > current max)

答案: connection_control_min_connection_delay cannot be set greater than the current value of connection_control_max_connection_delay. connection_control_max_connection_delay cannot be set less than the current value of connection_control_min_connection_delay. https://dev.mysql.com/doc/refman/8.0/en/connection-control-installation.html

试题 176:

How can mysqld_multi be configured to allow MySQL instances to use the same port number?

A) The instances have appropriate net masks set. [错误]
B) The instances use different user accounts unique to each instance. [错误]
C) The instances use different socket names. [错误]
D) The instances listen on different IP addresses. [正确]

(Note: This seems identical to question 150)

试题 177:

Choose the best answer.

MySQL is installed on a Linux server with this configuration:

1
2
3

[mysqld]
user = mysql
datadir = /data/mysql

Which method sets the default authentication to SHA-256 hashing for authenticating user account passwords?

A) Define CREATE USER ‘@’%’ IDENTIFIED WITH sha256_password in the MySQL instance. [错误] (Sets for specific user, not default)
B) Add default_authentication_plugin = sha256_password in the configuration file. [正确]
C) Add default_authentication_plugin = mysql_native_password in the configuration file. [错误]
D) Set validate-user-plugins = caching_sha2_password in the configuration file. [错误] (validate_password component setting)

答案: New install so setting my.cnf file

试题 178:

Examine these two reports taken 100 seconds apart:

GLOBAL STATUS 1:

Com_create_table = 500005
Com_drop_table = 500003
Com_flush = 23
Created_tmp_disk_tables = 400000
Created_tmp_tables = 1200000
Max_used_connections = 92
Open_files = 5000
Opened_files = 5000
Open_table_definitions = 3000
Open_tables = 1024
Opened_table_definitions = 2369
Opened_tables = 3500000
Threads_connected = 62
Threads_running = 58
Uptime = 100000

GLOBAL STATUS 2:

Com_create_table = 500505
Com_drop_table = 500498
Com_flush = 1
Created_tmp_disk_tables = 400400
Created_tmp_tables = 1201200
Max_used_connections = 92
Open_files = 5000
Opened_files = 7505
Open_table_definitions = 3000
Open_tables = 1024
Opened_table_definitions = 2873
Opened_tables = 3503500
Threads_connected = 67
Threads_running = 64
Uptime = 100100

Your MySQL system normally suffers from performance issues. Which configuration change will likely provide the most benefit?

A) increase max_connections [错误] (Max_used_connections is stable at 92)
B) decrease open_files_limit [错误] (Opened_files increased, limit might be hit)
C) decrease table_definition_cache [错误] (Opened_table_definitions increased)
D) increase table_open_cache [正确] (High Opened_tables (3500/100s) compared to Open_tables (1024) suggests cache churning)

答案:
Open_tables: 是当前在缓存中打开表的数量
Open_table_definitions: 当前打开的表定义
table_open_cache: ibd/MYI/MYD 文件
table_definition_cache: .sdi (formerly .frm)

试题 179:

Choose the best answer.

A colleague complains about slow response time on your website. Examine this query and output: (见下图)

What is the most likely cause for the high number of lock waits?

A) You use the InnoDB storage engine and statements wait while data is inserted. [错误] (InnoDB has row-level locking)
B) The Innodb Buffer pool is full. [错误] (Affects I/O, not directly locks)
C) You use the MyISAM storage engine for most common tables. [正确] (MyISAM uses table-level locking, causing high contention)
D) Your table accesses wait for the operating system level flush. [错误]

试题 180:

Choose the best answer. (题干见图片)

Your MySQL Server is running locally on your Linux installation, and has SSL connections configured but not mandatory.
# mysql -uroot -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.18-commercial MySQL Enterprise Server - Commercial

mysql> STATUS;
--------------
mysql  Ver 8.0.18-commercial for Linux on x86_64 (MySQL Enterprise Server - Commercial)

Connection id:          9
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.18-commercial MySQL Enterprise Server - Commercial
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 2 min 54 sec

Threads: 2  Questions: 10  Slow queries: 0  Opens: 132  Flush tables: 3  Open tables: 52  Queries per second avg: 0.057
--------------

mysql> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'ssl%' AND Value != '';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| ssl_ca        | ca.pem          |
| ssl_cert      | server-cert.pem |
| ssl_fips_mode | OFF             |
| ssl_key       | server-key.pem  |
+---------------+-----------------+

What is the reason for SSL not being used?

A) It is connected via a UNIX socket. [正确]
B) A current database is not selected. There is nothing to encrypt. [错误]
C) The root user cannot use encryption. [错误]
D) The root user must use ssl_fips_mode = ON. [错误]

答案: SSL is not supported on socket, named pipe, and shared memory connections

试题 181:

Choose the best answer.

You have configured a working MySQL InnoDB Cluster in single-primary mode. What happens when the primary instance goes down due to a network problem?

A) The cluster will continue to function with read-only members. [错误]
B) A new primary is automatically elected. [正确]
C) The cluster goes into wait mode until a new member is manually promoted as primary. [错误]
D) The cluster detects network partitioning and shuts down to remain consistent. [错误] (Only if quorum is lost)
E) All remaining members in the cluster are automatically set to read-write mode. [错误]

答案: If the existing primary leaves the group, whether voluntarily or unexpectedly, a new primary is elected automatically.

试题 182:

Choose the best answer.

You have configured MySQL Enterprise Transparent Data Encryption (TDE). What command would you use to encrypt a table?

A) UPDATE <table> SET ENCRYPTION = 'Y'; [错误]
B) ALTER INSTANCE ROTATE INNODB MASTER KEY; [错误] (Rotates key, doesn’t encrypt table)
C) UPDATE information_schema.tables SET encryption = 'Y' WHERE table_name = 'table'; [错误]
D) ALTER TABLE <table> ENCRYPTION = 'Y'; [正确]

参考: https://dev.mysql.com/doc/refman/8.0/en/alter-tablespace.html (Applies to tablespace, which includes the table if file-per-table)

试题 183:

Choose the best answer.

A developer accidentally dropped the InnoDB table Customers from the Company database. There is a datadir copy from two days ago in the /dbbackup directory. Which set of steps would restore only the missing table?

A) Stop the MySQL Server process and restart it with the command: mysqld --basedir=/usr/local/mysql --datadir=/dbbackup. Run mysqldump on this table and restore the dump file. [错误] (Starts server on old datadir, complex)
B) Stop the MySQL Server process and restart it with the command: mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysql. Run mysqldump on this table and restore the dump file. [错误] (mysqldump needs running server)
C) Stop the MySQL Server process, copy the Customers.ibd file from the dbbackup directory, and start the mysqld process. [错误] (Needs tablespace import, not just copy)
D) Stop the MySQL Server process, and execute: mysqlbackup --datadir=/var/lib/mysql --backup-dir=/dbbackup --include-tables='Company\\.Customers' copy-back. Start the mysqld process. [正确] (Uses MEB for partial restore)

试题 184:

Choose the best answer.

Examine this partial report: (下图过 - Assuming standard SHOW FULL PROCESSLIST output with connection ID 10)

mysql> SHOW FULL PROCESSLIST;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host      | db   | Command | Time | State | Info                  |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 10 | ...  | ...       | ...  | Query   | ...  | ...   | SELECT ...            |
| .. | ...  | ...       | ...  | ...     | ...  | ...   | ...                   |
+----+------+-----------+------+---------+------+-------+-----------------------+

Examine this query:

SELECT SUM(m.CURRENT_NUMBER_OF_BYTES_USED) AS TOTAL
FROM performance_schema.memory_summary_by_thread_by_event_name m
INNER JOIN performance_schema.threads t ON m.THREAD_ID = t.THREAD_ID
WHERE t.PROCESSLIST_ID = 10;

What information does this query provide?

A) total memory used by connection number 10 [正确]
B) total memory used across all connections associated with the user on connection number 10 [错误]
C) total memory used by the first 10 threads [错误]
D) total memory used by thread number 10 [错误] (Uses PROCESSLIST_ID which is connection ID)
E) total memory used across all connections associated with the user on thread number 10 [错误]
F) total memory used by the first 10 connections [错误]

分析： 这里 10 是 connection id，也是 show process 显示的 id，不是 thread id

试题 185:

You plan to take daily full backups, which include the ndbinfo and sys (internal) databases. Which command will back up the databases in parallel?

A) mysqldump --single-transaction > full-backup-$(date +%Y%m%d).sql [错误]
B) mysqlpump --include-databases=% > full-backup-$(date +%Y%m%d).sql [正确]
C) mysqlpump --all-databases > full-backup-$(date +%Y%m%d).sql [错误]
D) mysqldump --all-databases > full_backup-$(date +%Y%m%d).sql [错误]

(Note: This seems identical to question 160)

试题 186:

Examine this command, which executes successfully:

1	mysqlbackup --user=dba --password --port=3306 --with-timestamp --backup-dir=/export/backups backup-and-apply-log

Which statement is true?

A) The backup accesses the MySQL server files by using a pre-existing connection. [错误] (MEB connects itself)
B) The database server is put into a read-only state for the duration of the backup. [错误] (Hot backup)
C) An offline backup of InnoDB tables is taken. [错误] (Online backup)
D) The backup can be impacted when DDL operations run during the backup. [正确] (DDL can cause issues with consistency during backup)

试题 187:

Choose the best answer.

Database test contains a table named city that has the InnoDB storage engine. (见下图)

CREATE TABLE City (
  ID int NOT NULL AUTO_INCREMENT,
  Name char(35) NOT NULL DEFAULT '',
  CountryCode char(3) NOT NULL DEFAULT '',
  District char(20) NOT NULL DEFAULT '',
  Population int NOT NULL DEFAULT '0',
  PRIMARY KEY (ID),
  KEY CountryCode (CountryCode)
) ENGINE=InnoDB TABLESPACE=innodb_file_per_table;

What is the content of the test folder in the data directory?

A) city.MYD, city.MYI, and city.sdi [错误]
B) city.ibd [正确] (InnoDB data and SDI are stored within the .ibd in 8.0)
C) city.ibd and city.sdi [错误]
D) city.ibd and city.frm [错误] (No .frm in 8.0)
E) city.ibd, city.frm, and city.sdi [错误]

试题 188:

“bootstrap” 是一个动词，表示启动、引导或初始化一个系统或应用程序

Choose the best answer.

Examine this command, which executes successfully:

1	$ mysqlrouter --bootstrap user@hostname:port --directory=directory_path

Which activity is performed?

A) MySQL Router configures itself based on the information retrieved from the InnoDB cluster metadata server. [正确]
B) MySQL Router configures all the cluster nodes based on the information retrieved from the InnoDB cluster metadata server. [错误]
C) MySQL Router is restarted. [错误]
D) MySQL Router is configured based on the information in files in directory_path. [错误] (It writes config to directory_path)

答案: https://dev.mysql.com/doc/mysql-router/8.0/en/mysqlrouter.html#option_mysqlrouter_bootstrap

试题 189:

Choose the best answer.

You issue this command: SHOW SLAVE STATUS
In the output, there is a value for Seconds_Behind_Master. How is this time calculated?

A) It is the time between the I/O thread receiving details of the master’s last transaction and the time it was applied by the SQL thread. [正确] (More accurately: time diff between SQL thread’s last executed event timestamp and slave’s current time)
B) It is the time between the most recent transaction written to the relay logs and the time it was committed on the master. [错误]
C) It is the time between the I/O thread receiving details of the master’s last transaction and the time it was written to the relay log on the slave. [错误]
D) It is the time between the most recent transaction applied by a SQL thread and the time it was committed on the master. [错误] (This is closer, but it’s compared to slave time, not master commit time directly)

答案: Seconds_Behind_Master: This field measures the time difference in seconds between the slave SQL thread and the slave I/O thread. […] It measures the difference between the current timestamp on the slave and the original timestamp logged on the master for the event currently being processed on the slave.

(Revising A based on documentation)

A) It is the time difference between the timestamp of the event currently being processed by the SQL thread and the current time on the slave machine. [正确 - Refined]

试题 190:

Which command enables rule-based MySQL Auditing capabilities?

A) shell> mysql < audit_log_filter_linux_install.sql [正确]
B) shell> mysqld --initialize --log-raw=audit.log [错误]
C) mysql> INSTALL PLUGIN audit_log; [错误]
D) mysql> INSTALL COMPONENT 'file://component_audit_filter_log'; [错误]

(Note: This seems identical to question 169)

试题 191:

Consider this shell output and executed commands:

1
2
3

root@oel7 ~# ps aux | grep mysqld
mysql    2076  3.5 24.6 1386852 372572 ?       Ssl  12:01   0:01 /usr/sbin/mysqld
root@oel7 ~# kill -15 2076

Which statement is true about MySQL server shutdown?

A) kill -15 should be avoided. Use other methods such as mysqladmin shutdown or systemctl stop mysqld. [错误] (kill -15 is a valid way to request graceful shutdown)
B) kill -15 and kill -9 are effectively the same forced shutdown that risk committed transactions not written to disk. [错误] (kill -9 is forced, kill -15 is graceful request)
C) kill -15 carries out a normal shutdown process, such as mysqladmin shutdown. [正确] (Sends SIGTERM, allowing graceful shutdown)
D) mysqld_safe prohibits commands that would harm the operation of the server. An error would be returned by the kill command. [错误] (mysqld_safe restarts server if killed, doesn’t block kill)

试题 192:

Choose the best answer.

Binary log events for the ‘mydb1’ schema must be copied to a different schema name ‘mydb2’. Which command will do this?

A) mysqlbinlog --read-from-remote-server --raw | sed 's/mydb1/mydb2/g' | mysql [错误]
B) mysqlbinlog --rewrite-db='mydb1->mydb2' | mysql [正确]
C) mysqlbinlog --database=mydb1 --database=mydb2 | mysql [错误]
D) mysqlbinlog --rewrite-db='mydb1' --rewrite-db='mydb2' | mysql [错误]

(Note: This seems identical to question 165)

试题 193:

Choose the best answer.

Examine these entries from the general query log: (见下图)

Time                          Id Command    Argument
2019-12-17T00:36:23.389450Z    24 Connect    root@localhost on mydb using SSL/TLS
2019-12-17T00:36:23.389754Z    24 Query      select @@version_comment limit 1
2019-12-17T00:36:23.929519Z    25 Connect    root@localhost on mydb using SSL/TLS
2019-12-17T00:36:23.929846Z    25 Query      select @@version_comment limit 1
2019-12-17T00:36:27.633082Z    24 Query      START TRANSACTION
2019-12-17T00:36:30.321657Z    24 Query      UPDATE t1 SET Val = 1 WHERE ID = 130
2019-12-17T00:36:32.417433Z    25 Query      START TRANSACTION
2019-12-17T00:36:33.617642Z    25 Query      UPDATE t2 SET Val = 5 WHERE ID = 3805
2019-12-17T00:36:36.049458Z    25 Query      UPDATE t1 SET Val = 10 WHERE ID = 130
2019-12-17T00:36:38.513674Z    24 Query      UPDATE t2 SET val = ... WHERE ID = 3805

All UPDATE statements reference existing rows. Which describes the outcome of the sequence of statements?

A) All statements execute without error. [错误]
B) A deadlock occurs immediately. [正确] (24 locks t1, 25 locks t2, 25 waits for t1, 24 waits for t2)
C) Connection 25 experiences a lock wait timeout. [错误] (Deadlock detected before timeout)
D) A deadlock occurs after innodb_lock_wait_timeout seconds. [错误] (Deadlock detected immediately)
E) Connection 24 experiences a lock wait timeout. [错误] (Deadlock detected before timeout)

答案: 如果启用了死锁检测(默认)，并且发生了死锁，InnoDB 会检测到这个条件并回滚其中一个事务(受害者事务)。如果使用 innodb_deadlock_detect 配置选项禁用了死锁检测，InnoDB 将依赖于 innodb_lock_wait_timeout 设置来在出现死锁时回滚事务

试题 194:

Choose the best answer.

You reconfigure and start a slave that was not replicating for several days. The configuration file and CHANGE MASTER command are correct. Examine the GTID information from both master and slave: (见下图)

Master:
  gtids_executed: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-321,
                  bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb:1-50,
                  cccccccc-cccc-cccc-cccc-cccccccccccc:1234-1237
  gtids_purged:   aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-100,
                  bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb:1-10,
                  cccccccc-cccc-cccc-cccc-cccccccccccc:1234-1237
Slave:
  gtids_executed: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-160,
                  cccccccc-cccc-cccc-cccc-cccccccccccc:1234-1237
  gtids_purged:   aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-70,
                  cccccccc-cccc-cccc-cccc-cccccccccccc:1234-1237

Which statement is true?

A) Replication will fail because the master has already purged transactions with ccccc… GTIDs. [错误] (Master and slave both have/purged these)
B) Replication will work. [错误]
C) Replication will fail because the master does not have the required transaction with bbbbb… GTIDs in its binary logs. [正确] (Master purged bbb:1-10, but slave needs transactions starting from where it left off, which might include some bbb GTIDs if they occurred after aaaaa:160) Correction: Slave doesn’t have bbb GTIDs at all. Master has bbb:1-50 executed but purged 1-10. Slave needs bbb:1-50 but master only has bbb:11-50 available in logs. Replication fails.
D) Replication will fail because the slave has purged more aaaaa… transactions than the master. [错误] (Slave purged 1-70, Master purged 1-100. Master purged more)
E) Replication will fail because of inconsistent numbers in ccccc… GTIDs. [错误] (GTIDs match)

试题 195:

Choose the best answer.

Examine this command, which executes successfully:

1	mysqlbackup --defaults-file=/backups/server-my.cnf --backup-dir=/backups/full copy-back

Which statement is true about the copy-back process?

A) It restores files from the data directory to their original MySQL server locations. [错误]
B) It restores files from the backup directory to their original MySQL server locations. [正确]
C) The copy-back process is used to overwrite a new backup over an existing backup. [错误]
D) The copy-back process makes inconsistent backups. [错误] (Applies logs to make backup consistent before copying back)

试题 196:

Choose the best answer.

Your my.cnf file contains these settings:

[mysqld]
log_output = FILE
slow_query_log
long_query_time = 2.0
log_queries_not_using_indexes

A) log_throttle_queries_not_using_indexes = 5 [错误]
B) long_query_time = 5, log_throttle_queries_not_using_indexes = 5 [错误]
C) long_query_time = 5, min_examined_row_limit = 5000 [正确]
D) long_query_time = 5, log_throttle_queries_not_using_indexes = 5, min_examined_row_limit = 5000 [错误]
E) log_throttle_queries_not_using_indexes = 5, min_examined_row_limit = 5000 [错误]
F) long_query_time = 5 [错误]
G) min_examined_row_limit = 5000 [错误]

答案:
log_queries_not_using_indexes: 没有使用索引的 SQL 也将被记录到慢查询日志中；
log_throttle_queries_not_using_indexes: 如果 log_queries_not_using_indexes 打开，没有使用索引的 sql 将会写入到慢查询日志中，该参数将限制每分钟写入的 SQL 数量；
min_examined_row_limit: 对于查询扫描行数小于此参数的 SQL，将不会记录到慢查询日志中；

(Note: This seems identical to question 145)

试题 197:

Choose the best answer.

Consider this shell output and executed commands:

1
2
3

root@oel7 ~# ps aux | grep mysqld
mysql    2076  3.5 24.6 1386852 372572 ?       Ssl  12:01   0:01 /usr/sbin/mysqld
root@oel7 ~# kill -15 2076

Which statement is true about MySQL server shutdown?

A) kill -15 and kill -9 are effectively the same forced shutdown that risk committed transactions not written to disk. [错误]
B) mysqld_safe prohibits commands that would harm the operation of the server. An error would be returned by the kill command. [错误]
C) kill -15 carries out a normal shutdown process, such as mysqladmin shutdown. [正确]
D) kill -15 should be avoided. Use other methods such as mysqladmin shutdown or systemctl stop mysqld. [错误]

答案: kill -15 PID 可以理解为操作系统发送一个通知告诉应用主动关闭.

(Note: This seems identical to question 191)

试题 198:

Choose the best answer.

You have a MySQL system with 500 GB of data that needs frequent backups. You use a mix of MyISAM and InnoDB storage engines for your data. Examine your backup requirements:

The MySQL system being backed up can never be unavailable or locked to the client applications.
The recovery from the backup must work on any system.
Only 1 hour of data can be lost on recovery of the backup.

Which option fulfills all backup requirements?

A) Take a physical backup of the MySQL system. [错误] (Physical backups like MEB lock MyISAM; recovery might not work on any system if OS/arch differs)
B) Take a logical backup of the MySQL system. [错误] (Logical backups like mysqldump lock MyISAM or require --single-transaction which doesn’t guarantee MyISAM consistency; can be slow for 500GB)
C) Use the Clone Plugin to copy the data to another MySQL system. [错误] (Requires donor online, doesn’t create a point-in-time backup file; locks during clone)
D) Take your backup from a slave of the MySQL system. [正确] (Allows backup without locking master; logical or physical backup can be taken from slave; point-in-time recovery possible with slave’s logs)

试题 199:

Choose the best answer.

Which statement is true about InnoDB persistent index statistics?

A) Increasing innodb_stats_persistent_sample_pages determines higher pages scanning speed, at the cost of increased memory usage. [错误] (Increases sample size, potentially slower but more accurate stats)
B) Execution plans based on transient index statistics improve precision when innodb_stats_persistent_sample_pages is increased. [错误] (Persistent stats use this setting, transient stats use temporary sampling)
C) Index statistics are calculated from pages buffered in the buffer pool for tables with InnoDB storage engine. [错误] (Persistent stats read pages from disk)
D) Setting innodb_stats_auto_recalc = ON causes statistics to be updated automatically when a new index is created or more than 10% of its rows change. [错误] (Applies to substantial data changes, not index creation directly)
E) Updating index statistics is an I/O expensive operation. [正确] (Especially persistent stats reading from disk)

试题 200:

Choose the best answer.

Which step or set of steps can be used to rotate the error log?

A) Execute SET GLOBAL max_error_count = <number of messages at point to rotate>. [错误]
B) Rename the error log file on disk, and then execute FLUSH ERROR LOGS. [正确]
C) Execute SET GLOBAL log_error = '<new error log file>'. [错误] (Changes destination, doesn’t rotate old one)
D) Execute SET GLOBAL expire_logs_days = 0 to enforce a log rotation. [错误] (Relates to binary logs)

试题 201:

Choose the best answer.

You are using an existing server with a new configuration. MySQL Server fails to start. Examine this snapshot of the error log:

190925 12:49:05 InnoDB: Initializing buffer pool, size = 3.0G
190925 12:49:05 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 26214400 bytes!
190925 12:49:05 [ERROR] Plugin 'InnoDB' init function returned error.
190925 12:49:05 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
190925 12:49:05 [ERROR] Aborting
190925 12:49:05 [Note] /usr/sbin/mysqld: Shutdown complete

Which action would allow the server to start?

A) Execute mysqladmin flush-logs. [错误] (Server isn’t running)
B) Create a new ib_logfile0 file of size 26214400. [错误] (MySQL needs to create it)
C) Remove ib_logfile0 and ib_logfile1 files from the file system. [正确] (MySQL will recreate them with the new size on startup)
D) First run mysqld --initialize to refresh the size of ib_logfile. [错误] (Initialize is for new instances)

试题 202:

Choose the best answer.

Which characters are most commonly used in a SQL injection attack?

A) ‘ and \ [正确]
B) < and > [错误]
C) null (\0) and newline (\n) [错误]
D) ^ and $ [错误]
E) + and - [错误]

解释： Users may attempt SQL injection by any of the following methods:

Entering single and double quotation marks (‘ and “) in web forms
Modifying dynamic URLs by adding %22 (“), %23 (#), and %27 (‘) to them
Entering characters, spaces, and special symbols rather than numbers in numeric fields

试题 203:

Choose the best answer.

Examine these commands, which execute successfully on the ic1 host:

mysqlsh> dba.createCluster('cluster1', {memberWeight: 35})
mysqlsh> var mycluster = dba.getCluster()
mysqlsh> mycluster.addInstance('ic@ic2', {memberWeight: 25})
mysqlsh> mycluster.addInstance('ic@ic3', {memberWeight: 50})

Now examine this configuration setting, which is the same on all nodes:

1	group_replication_consistency = BEFORE_ON_PRIMARY_FAILOVER

Which statement is true if primary node ic1 fails?

A) Node ic2 becomes the new primary and existing transactions are considered stale and rolled back. [错误]
B) Node ic3 becomes the new primary and existing transactions are considered stale and rolled back. [错误]
C) Node ic3 becomes the new primary and is ignored until any backlog of transactions is completed. [正确] (ic3 has highest weight; BEFORE_ON_PRIMARY_FAILOVER ensures backlog applied before new primary accepts writes)
D) Only two nodes remain so the election process is uncertain and must be done manually. [错误] (Election happens automatically based on weight)
E) Node ic2 becomes the new primary and is ignored until any backlog of transactions is completed. [错误] (ic3 has higher weight)

试题 204:

Choose the best answer.

You execute this command:

1	shell> mysqlpump --exclude-databases=% --users

Which statement is true?

A) It creates a logical backup of all metadata, but contains no table data. [错误] (--exclude-databases=% excludes all databases)
B) It returns an error because the mysqldump command should have been used. [错误]
C) It creates a logical backup of only the users database. [错误]
D) It creates a logical backup of all MySQL user accounts. [正确] (--users dumps user definitions)

答案: 实验试题

试题 205:

Choose the best answer.

You want to log only the changes made to the database objects and data on the MySQL system. Which log will do this by default?

A) slow query log [错误]
B) binary log [正确]
C) error log [错误]
D) general query log [错误]
E) audit log [错误]

试题 206:

Choose the best answer.

Examine this partial output for InnoDB Cluster status: (见下图)

// Assuming status shows host1 R/W ONLINE, host2 MISSING, host3 R/O ONLINE
"topology": {
    "host1:3377": { "address": "host1:3377", "mode": "R/W", "status": "ONLINE", ... },
    "host2:3377": { "address": "(MISSING)", "mode": "R/O", "status": "MISSING", ... },
    "host3:3377": { "address": "host3:3377", "mode": "R/O", "status": "ONLINE", ... }
}

Which statement explains the state of the instance deployed on host2?

A) It can rejoin the cluster by using the command cluster.addInstance(‘@host3:3377’). [错误]
B) It has been expelled from the cluster because of a transaction error. [错误]
C) It can be recovered from a donor instance on host3 by cloning using the command cluster.rejoinInstance(‘@host2:3377’). [正确] (Use rejoinInstance for MISSING node, specifying its own address)
D) It has been removed from the cluster by using the command STOP GROUP_REPLICATION;. [错误]
E) It can rejoin the cluster by using the command dba.rebootClusterFromCompleteOutage(). [错误]

答案: If a node/instance is removed intentionally, executing for instance removeInstance(), then it is applicable to use addInstance. The reason is, when removeInstance() is executed, it stops Group Replication and also removes the metadata. In order for the node be part of the group again, the solution is to add it again, having the node/instance added to the metadata; If a node, for any other reason - crash, restarts - then the node/instance will be out of the Group Replication, but still will hold the metadata. In this case, rejoinInstance() is applicable

(Note: This seems identical to question 125)

试题 207:

Choose the best answer.

You use Row Based Replication and need to see “pseudo-SQL” statements for the replication event that is located in the log_file position NNNNN. Which command should you use?

A) mysqlshow --debug --stop-position=NNNNN log_file [错误]
B) mysqlbinlog --verbose --start-position=NNNNN log_file [正确] (--verbose or -vv shows pseudo-SQL)
C) mysqlbinlog --debug --start-position=NNNNN log_file [错误]
D) mysqlbinlog --debug --stop-position=NNNNN log_file [错误]
E) mysqlshow --verbose --stop-position=NNNNN log_file [错误]
F) mysqlbinlog --verbose --stop-position=NNNNN log_file [错误] (Need start position)
G) mysqlshow --debug --start-position=NNNNN log_file [错误]
H) mysqlshow --verbose --start-position=NNNNN log_file [错误]

试题 208:

Choose the best answer.

Which utility would you use to view the queries in the slow query log sorted by average query time?

A) mysqlcheck [错误]
B) mysqlshow [错误]
C) mysqlimport [错误]
D) mysqldump [错误]
E) mysqldumpslow [正确]

试题 209:

Choose the best answer.

Examine the command, which executes successfully:

1	shell> mysqld --initialize

Which statement is true?

A) The root password is created in the error log in plain text. [正确]
B) The installation creates a temporary test environment with data in the /tmp directory. [错误]
C) The installation is created without enforcing or generating SSL certificates. [错误] (Generates default SSL files)
D) The root password is not created allowing easy access from the same host. [错误]

(Note: This contradicts Q158. –initialize does create a password and logs it. –initialize-insecure does not.)

试题 210:

Choose the best answer.

Your MySQL environment has asynchronous position-based replication with one master and one slave. The slave instance had a disk I/O problem, so it was stopped. You determined that the slave relay log files were corrupted and unusable, but no other files are damaged. You restart MySQL Server.

How can replication be restored?

A) The slave relay logs should be deleted; then execute START SLAVE; [错误] (Needs new position)
B) The slave needs to be restored from backup. [错误] (Only relay logs corrupted)
C) The slave relay logs should be deleted; execute CHANGE MASTER TO adjust the replication relay log file name and position, then issue START SLAVE; [正确]
D) The relay logs from the master should be used to replace the corrupted relay logs. [错误] (Cannot copy relay logs)

试题 211:

Examine this configuration:

You have a corporate private network, which uses its own Certificate Authority (CA) using an industry standard 2048-bit RSA key length.
All MySQL Server and client certificates are signed using the central corporate CA.
All clients are known, controlled, and exist only on the private LAN.
The private network uses its own private authoritative DNS.
The private network also uses other nominal enterprise services.
An end-to-end encrypted connection for a MySQL client to MySQL server has been established on this LAN.

How does the MySQL Servers’ certificate signed by the corporate CA compare to one that would be signed by a known public, third-party trusted Certificate Authority?

A) The self-signed certificate is equally secure and equally trusted. [错误] (It’s CA-signed, not self-signed; trust is internal)
B) The self-signed certificate is more secure and less trusted. [错误]
C) The self-signed certificate is less secure and equally trusted. [错误]
D) The self-signed certificate is equally secure and less trusted. [错误] (It’s CA-signed. Security is comparable if CA is managed well. Trust is limited to those trusting the internal CA.)
E) The self-signed certificate is more secure and equally trusted. [错误]
F) The self-signed certificate is less secure and less trusted [正确] (Assuming “self-signed” was intended to mean “internally signed”. Public CAs have stricter validation, hence potentially more secure processes. Trust is inherently less widespread than a public CA.)

(Revising based on “internally signed” vs “publicly signed”)

The internally signed certificate is equally secure (assuming good practices) and less trusted (externally). [Closest to D, but question uses “self-signed”]

Let’s assume the question meant “internally signed”. Security depends on CA practices. Trust is definitely lower externally. Option D seems the most plausible interpretation if “self-signed” is a mistake for “internally signed”. If taken literally as self-signed, then F is correct (less secure, less trusted). Given the context, interpreting as “internally signed” makes more sense.

试题 212:

Choose the best answer.

Examine these statements and output:

mysql> GRANT PROXY ON accounting@localhost TO ''@'%';
mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+--------------------+----------------------+--------------+
| USER()             | CURRENT_USER()       | @@proxy_user |
+--------------------+----------------------+--------------+
| rsmith@localhost   | accounting@localhost | ''@'%'       |
+--------------------+----------------------+--------------+

Which statement is true?

A) The user failed to define a username and the connecting username defaulted to ‘‘@’%’. [错误]
B) The user is authorized as the rsmith@localhost user. [错误]
C) The user is authenticated as the anonymous proxy user ‘‘@’%’. [错误]
D) The user is logged in with –user=accounting as an option. [错误]
E) The user is authorized as the accounting@localhost user. [正确] (CURRENT_USER shows the proxied user whose privileges are active)

分析： proxy 用户创建构成

-- create proxy account
CREATE USER 'employee_proxy'@'%' identified by '123456';
-- create proxied account and grant its privileges;
CREATE USER 'employee'@'localhost' identified by '123456';
GRANT ALL ON *.* TO 'employee'@'localhost';
-- grant PROXY privilege
GRANT PROXY ON 'employee'@'localhost' TO 'employee_proxy'@'%';

https://dev.mysql.com/doc/refman/8.0/en/proxy-users.html
CURRENT_USER() 的权限是真正当前作用的用户权限。

试题 213:

Choose the best answer.

You wish to store the username and password for a client connection to MySQL server in a file on a local file system. Which is the best way to encrypt the file?

A) Use mysql_secure_installation to encrypt stored login credentials. [错误]
B) Use a text editor to create a new defaults file and encrypt it from Linux prompt. [错误]
C) Use mysql_config_editor to create an encrypted file. [正确] (.mylogin.cnf)
D) Use the AES_ENCRYPT() MySQL function on the option file. [错误]

试题 214:

You recently upgraded your MYSQL installation to MYSQL 8.0. Examine this client error:

1	Error 2059 (HY000): authentication plugin ‘caching_sha2_password’ cannot be Loaded:/usr/local/mysql/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such or directory

Which option will allow this client to connect to MYSQL Server?

A) mysqld default_authentication_plugin = sha256_password [错误]
B) mysqld default_authentication_plugin = caching_sha2_password [错误]
C) ALTER USER user IDENTIFIED WITH mysql_native_password BY ‘password’; [正确]
D) ALTER USER user IDENTIFIED WITH caching_sha2_password [错误]
E) ALTER USER user IDENTIFIED WITH sha256_password [错误]
F) mysqld default_authentication_plugin = mysql_native_password [错误]

(Note: This seems identical to question 167)

试题 215:

Choose the best answer.

Which condition is true about the use of the hash join algorithm?

A) At least one of the tables in the join must have a hash index. [错误]
B) No index can be used for the join. [正确] (Hash join is used when indexes cannot be used effectively for the join condition)
C) The query must access no more than two tables. [错误]
D) The smallest of the tables in the join must fit in memory as set by join_buffer_size. [错误] (The build table, usually smaller, must fit)

答案:
https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html
hash join 只能在没有索引的字段上有效
hash join 只在等值 join 条件中有效
hash join 不能用于 left join 和 right join
最后参考文档 MySQL 8.0 for Database Administrators StudentGuide 2.pdf 页数 P180

试题 216:

Which three are characteristics of logical backups?

A) They consist of exact copies of database directories and files. [错误] (Physical backup characteristic)
B) They can be created by mysqlbackup for InnoDB tables or by file-system commands, such as cp, scp, tar, or rsync, for MyISAM tables. [错误] (Describes physical backup methods)
C) They can be performed while the MySQL server is not running. [错误] (Physical backup characteristic)
D) They can be run only against a running MySQL server. [正确] (e.g., mysqldump connects to server)
E) They are machine independent and highly portable. [正确] (SQL statements are portable)
F) Backup and restore granularity is available at the server level, database level, or table level for any storage engine. [正确]
G) In addition to databases, backups can include any related files, such as log or configuration files. [错误] (Logical backups typically contain SQL)

试题 217:

Which two statements are true about MySQL Enterprise Backup?

A) It supports the creation of incremental backups. [正确]
B) It creates logical backups. [错误] (Physical)
C) It supports restoring to a remote MySQL system. [错误] (Restore is local copy-back)
D) It supports backing up only table structures. [错误]
E) It supports backup of a remote MySQL system. [错误] (Connects locally or via network, but backs up the target server’s files)
F) It can perform hot or warm backups. [正确]