MySQL 8.0版本 OCP证书题库(1Z0-908)

## Question 1

Choose the best answer.
Examine these statements and output:

```sql
mysql> GRANT PROXY ON accounting@localhost TO ' '@'%' ;
mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
```

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.

**Answer:** E

**解析：**
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 to proxy account the
GRANT PROXY ON 'employee'@'localhost' TO 'employee_proxy'@'%';
```

## Question 2

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.
  - C) Enable the innodb_print_all_deadlocks global parameter.
  - D) Run the SHOW ENGINE INNODB STA TUS command from the mysql client.
  - E) Use the sys.innodb_lock_waits view.

**Answer:** C,D

**解析：**
innodb_print_all_deadlocks_
When this option is enabled, information about all deadlocks in InnoDB user transactions is
recorded in the mysqlderror log. Otherwise, you see information about only the last deadlock,
using the SHOW ENGINE INNODB STA TUScommand.

## Question 3

Choose the best answer.
Examine this statement, which executes successfully:
You want to improve the performance of this query:
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 (Nam  - e) ;
  - B) ALTER TABLE world.city ADD SPA TIAL INDEX (Nam  - e) ;
  - C) ALTER TABLE world.city ADD FULLTEXT INDEX (Nam  - e) ;
  - D) ALTER TABLE world.city ADD FULLTEXT INDEX (Population) ;
  - E) ALTER TABLE world.city ADD SPA TIAL INDEX (Population) ;
F) ALTER TABLE world.city ADD INDEX (Population) ;
ERROR 1687 (42000): A SPA TIAL index may only contain a geometrical type column

```sql
SELECT Name
mysql> ALTER TABLE city ADD SPA TIAL INDEX(Population);
```

**Answer:** F

**解析：**
分析：

## Question 4

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:_
SCHEMA.MYSQL
FIREWALL
USERS__
WHERE USERHOST = 'fwuser@localhost' ;
INFORMATION_
SCHEMA.MYSQL_
WHERE USERHOST = 'fwuser@localhost';
FIREWALL
WHITELIST_
You then execute this command:
firewall__
Which two are true?
mode('fwuser@localhost'
,
'RESET') ;
  - 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。

```sql
mysql> SELECT MODE FROM INFORMATION
mysql> SELECT RULE FROM
mysql> CALL mysql.sp_
set
```

**Answer:** CG

**解析：**
待补充

## Question 5

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_
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
Which four changes provide the most performance improvement, without sacrificing data

**Answer:** B C E H

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

## Question 6

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.
  - 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.
https://dev.mysql.com/doc/mysql-monitor/8.0/en/mem-features-qrti.html

**Answer:** A

**解析：**
待补充

## Question 7

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

**Answer:** B

**解析：**
待补充

## Question 8

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.
administrative account，每个只有部分权限。

**Answer:** B E

**解析：**
分析：网络隔离 最好 ， D 的问题是 administrator 这个权限太大了，应该有多个

## Question 9

Choose two.
Which two statements are true about MySQL Enterprise Backup?
  - A) It creates logical backups.
  - B) It supports backing up only table structures.
  - C) It can perform hot or warm backups.
  - D) It supports backup of a remote MySQL system.
  - E) It supports restoring to a remote MySQL system.
F) It supports the creation of incremental backups.

**Answer:** C F

**解析：**
待补充

## Question 10

Choose the best answer.
Examine these commands, which execute successfully on the ic1 host:
, {memberWeight:35})
, {memberWeight:25})
, {memberWeight:50})
Now examine this configuration setting, which is the same on all nodes:
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.
  - D) Only two nodes remain so the election process is uncertain and must be done manually.
  - E) Node ic2 becomes the new primary and is ignored until any backlog of transactions is
completed.

```sql
mysqlsh> dba.createCluster( 'cluster1'
mysqlsh> var mycluster = dba.getCluster ()
mysqlsh> mycluster.addInstance ( 'ic@ic2'
mysqlsh> mycluster.addInstance( 'ic@ic3'
```

**Answer:** C

**解析：**
待补充

## Question 11

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:
Which step or set of steps will resolve the errors?
  - A) Start mysqld again using the --upgrade=FORCE option.
  - B) Go to the <datadir>/mysql directory and execute: myisamchk --update-state columns_priv
event proc proxies_priv tables_priv.
  - C) Execute: mysqlcheck --repair mysql columns_priv event proc proxies_priv tables_priv.
  - 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.
  - E) Execute: mysqlcheck --check-upgrade mysql columns_priv event proc proxies_priv
tables_priv.
B 这些表都是 MyISAM 的表，但 myisamchk --update-state 并没有指定修复，要指定--force
进行修复。
E mysqlcheck --check-upgrade 也没有指定修复

**Answer:** C

**解析：**
待补充

## Question 12

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:
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.

**Answer:** A

**解析：**
待补充

## Question 13

Choose the best answer.
Database test contains a table named city that has the InnoDB storage engine.
What is the content of the test folder in the data directory?
  - A) city.MYD, city.MYI, and city.sdi
  - B) city.ibd
  - C) city.ibd and city.sdi
  - D) city.ibd and city.frm
  - E) city.ibd, city.frm, and city.sdi

**Answer:** B

**解析：**
待补充

## Question 14

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() ##设置主节点
  - E) dba.configureInstance() ##检查配置
F) dba.createCluster () #创建集群
G) cluster.forceQuorumUsingPartitionOf () #将集群从 quorum 丢失场景恢复到可操作状态。
如果一个组被分区或发生的崩溃超过了可容忍的范围，则可能会出现这种情况。
https://dev.mysql.com/doc/mysql-shell/8.0/en/deploying-new-production-cluster.html
第一个节点就是主节点，不用设置。

**Answer:** D G

**解析：**
待补充

## Question 15

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}

**Answer:** B C D G

**解析：**
待补充

## Question 16

Choose two.
Which two authentication plugins require the plaintext 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

**Answer:** A D

**解析：**
待补充

## Question 17

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
  - D) /var/1ib/mysql
  - E) /usr/bin
https://dev.mysql.com/doc/mysql-linuxunix-excerpt/8.0/en/linux-installation-rpm.html

**Answer:** D

**解析：**
待补充

## Question 18

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.
  - B) The mysqlcheck --repair --all-databases command can repair an InnoDB corrupted table.
  - C) The mysqlcheck --analyze --all-databases command performs a series of checks to spot
eventual table corruptions.
  - 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.

**Answer:** D E

**解析：**
待补充

## Question 19

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;
  - B) ALTER USER baduser@hostname DEFAULT ROLE NONE;
  - C) ALTER USER baduser@hostname MAX
USER
CONNECTIONS 0;__
  - 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. I

**Answer:** D E

**解析：**
待补充

## Question 20

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.

**Answer:** E F

**解析：**
待补充

## Question 21

Choose two.
There are five MySQL instances configured with a working group replication.
Examine the output of the group members:
ID, MEMBER
STA TE 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.

```sql
mysql> SELECT MEMBER
```

**Answer:** B D

**解析：**
待补充

## Question 22

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.
  - 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.
F https://dev.mysql.com/doc/refman/8.0/en/using-systemd.html

**Answer:** D E F

**解析：**
待补充

## Question 23

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 (GTI  - D) sets with GTID_
SUBSET (set1, set2).
  - D) The active session instanceis 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.

**Answer:** C

**解析：**
待补充

## Question 24

Choose the best answer.
Examine this partial report:
Examine this query:
NUMBER
OF
BYTES____
USE  - D) 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
  - E) total memory used across all connections associated with the user on thread number 10
F) total memory used by the first 10 connections

```sql
mysql> SHOW FULL PROCESSLIST;
SELECT SUM(m.CURRENT
```

**Answer:** A

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

## Question 25

Choose three.
Which three settings control global buffers shared by all threads on a MySQL server?
  - A) tmp_
table
size_
  - B) innodb
buffer__pool
size_
  - C) table_
open
cache_
  - D) sort
buffer
size__
  - E) key_
buffer
size_
F) read
buffer
size__

**Answer:** B C E

**解析：**
待补充

## Question 26

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
  - B) mysqlpump --include-databases=% > full-backup-$(date + %Y%m%  - d).sql
  - C) mysqlpump --all-databases > full-backup-$(date + %Y%m%  - d).sql
  - D) mysqldump --single-transaction > full-backup-$(date + %Y%m%  - d).sql
mysqlpump does not dump the performance_
schema, ndbinfo, or sys schema by default.

**Answer:** A

**解析：**
待补充

## Question 27

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.
这个工具不光管理密码，还管理：主机名 端口号 文件名 用户名

**Answer:** C ,G

**解析：**
待补充

## Question 28

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
  - D) named pipes
  - E) X Protocol
F) TCP/IP
在 windows 上没有 socket

**Answer:** B D F

**解析：**
待补充

## Question 29

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_
  - B) PREFERRED
  - C) VERIFY
IDENTITY_
  - D) REQUIRED

**Answer:** C

**解析：**
待补充

## Question 30

Choose the best answer.
Which command enables rule-based MySQL Auditing capabilities?
  - A) shell> mysqld --initialize --log-raw=audit.log
  - B) mysql> INSTALL COMPONENT audit_
log;
  - C) mysql> INSTALL PLUGIN audit_
log;
  - D) shell> mysql < audit_
log_
filter
linux__
install.sql.
https://dev.mysql.com/doc/refman/8.0/en/audit-log-installation.html

**Answer:** D

**解析：**
待补充

## Question 31

Choose two.
A valid raw backup of the shop.customers MyISAM table was taken.
You must restore the table.
You begin with these steps:
1. Confirm that secure
file__priv= '/var/tmp '
2. mysql> DROP TABLE shop.customers;
3. shell> cp /backup/customers.MY* /var/1ib/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/1ib/mysql/shop/
  - D) mysql> ALTER TABLE shop.customers DISCARD TABLESPACE
  - E) shell> cp /backup/customers.frm /var/1ib/mysql/shop/
F) mysql> IMPORT TABLE FROM /var/1ib/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

**Answer:** A G

**解析：**
待补充

## Question 32

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.
  - D) It guarantees near real-time replication between a master and a slave.
  - E) It allows backup to be done on the slave without impacting the master.

**Answer:** A E

**解析：**
待补充

## Question 33

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.
  - B) Take a logical backup of the MySQL system.
  - C) Use the Clone Plugin to copy the data to another MySQL system.
  - D) Take your backup from a slave of the MySQL system.

**Answer:** D

**解析：**
待补充

## Question 34

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

**Answer:** D ,F ,G ,H

**解析：**
待补充

## Question 35

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.
  - 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

**Answer:** B C

**解析：**
待补充

## Question 36

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.
  - D) Use the AES_
ENCRYPT() MySQL function on the option file.

**Answer:** C

**解析：**
待补充

## Question 37

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' ) V ALUES
('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 ' ) V ALUES
('bob@example.com'
,
'secret') ;--
';
F 在注入的语句前面没有分号。

**Answer:** A B

**解析：**
待补充

## Question 38

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)

**Answer:** B D E

**解析：**
待补充

## Question 39

Choose two.
Examine this command, which executes successfully:
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.
  - B) It enforces consistent backups for all storage engines.
  - C) It is a cold backup.
  - D) The backup created is a consistent data dump.
  - E) It executes flush tables with read lock.

**Answer:** D ，E

**解析：**
待补充

## Question 40

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: disable  - d)
Active: failed (Result: exit-cod  - e) since Thu 2019-12-12 07:54:53 ACDT; 33s ago
Docs: man:mysqld(8)
http://dev.mysql.com/a/doc/refman/en/using-systend.html
Process: 2732 Execstart=/usr/ sbin/mysqld $MYSQLD_
OPTS (code=exited, status=1/ FAILUR  - E)
Process: 2705 ExecStartPre=/usr/bin/mysqld_pre_
systemd (code=exited, status=0/ SUCCESS)
Main PID: 2732 (code=exited, status=1/FAILUR  - E)
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.
  - D) systemd found the mysqld service disabled and failed to start it.
  - E) MySQL server was not started due to a problem while executing process 2732.

**Answer:** E

**解析：**
待补充

## Question 41

Choose two.
Examine this MySQL Shell command:
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

**Answer:** C D

**解析：**
待补充

## Question 42

Choose two.
Examine this statement and output:_
NUMBER() OVER() AS QN,
query, exec_
count, avg_
latency, lock_
latency
FROM sys.statement_
analysis
ORDER BY exec
count;_
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
latency，执行次数多的 sql 语句已经没有优化的空间了。

```sql
mysql> SELECT ROW
```

**Answer:** CE

**解析：**
解析：看 avg_

## Question 43

Choose two.
Which two are contained in the InnoDB system tablespace (ibdata1) by default?
  - A) doublewrite buffer
  - B) change buffer
  - C) InnoDB Data Dictionary
  - D) primary indexes
  - E) table data
F) user privileges
参考文档 MySQL 8.0 for Database Administrators StudentGuide 1.pdf 页数 152

**Answer:** A B

**解析：**
待补充

## Question 44

Choose three.
Examine this command, which executes successfully:
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.

**Answer:** B C D

**解析：**
待补充

## Question 45

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 multi-source 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.

**Answer:** E F

**解析：**
待补充

## Question 46

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.
  - 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.

**Answer:** D E

**解析：**
待补充

## Question 47

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:
ENABLED=on;_
  - B) On the slave, alter the MySQL master connection setting with:
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;_
  - E) Restart MySQL (master and slav  - e) with these options enabled:
mode=ON
--gtid_
--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;__

```sql
SET GLOBAL GTID
ALTER channel CHANGE MASTER TO MASTER
```

**Answer:** EF

**解析：**
待补充

## Question 48

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_

**Answer:** C

**解析：**
待补充

## Question 49

Examine these commands and output:_
type, object_
schema, object
name, lock__
type, lock_
owner
thread
id, owner
event
id___
-> FROM performance_
schema.metadata__
locks WHERE object_
schema !=
'performance
schema' ;_
status,_
id, processlist_
id, processlist_
user, parent_
-> FROM per formance_
schema.threads WHERE processlist_
thread
id_
user=' root';
Which connection ID is holding the metadata lock?
  - A) 21
  - B) 25
  - C) 6
  - D) 22
  - E) 20
F) 24

```sql
mysql> SHOW FULL PROCESSLIST;
mysql> SELECT object
mysql> SELECT thread
```

**Answer:** E

**解析：**
待补充

## Question 50

Choose the best answer.
t is a non-empty InnoDB table.
Examine these statements, which are executed in one session:
BEGIN
Which is true?
  - A) mysqlcheck --analyze --all-databases will execute normally on all tables and return a report.
  - B) If ANALYZE TABLE; is invoked from the same session, it hangs until the transaction is
committed or rolled back.
  - C) If OPTIMIZE LOCAL TABLE t; is invoked from another session, it executes normally and
returns the status.
  - D) If OPTIMIZE TABLE; is invoked, it will create a table lock on t and force a transaction
rollback.

```sql
SELECT * FROM t FOR UPDATE;
```

**Answer:** D

**解析：**
待补充

## Question 51

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=<value>._
  - B) Use SET GLOBAL binlog_
expire_
logs
seconds=<value> and restart the server._
  - C) Use PURGE BINARY LOGS to <binlog_
name>.
  - D) Set binlog_
expire_
logs_
seconds in my.cnf.
  - E) Use SET GLOBAL binlog_
expire_
logs
seconds=<value> and run the FLUSH BINARY LOGS_
command.
F) Set binlog_
expire_
logs_
seconds = 0 in my.cnf and restart the server.

**Answer:** CE

**解析：**
待补充

## Question 52

Choose two.
You have an InnoDB Cluster configured with three servers.
Examine this command, which executes successfully:_
backup.sql
Due to data loss, the cluster is initialized and a restore is attempted resulting in this error:
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.

```sql
mysqldump -uroot -p -d mydatabase > mydatabase
```

**Answer:** EF

**解析：**
待补充

## Question 53

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

**Answer:** AB

**解析：**
待补充

## Question 54

Choose three.
Which three actions are effective in capacity planning?
  - A) adding circular replication nodes for increased DML capability
  - B) buying more RAM
  - C) buying more disk
  - D) buying more CPU
  - E) basing expected growth on an average of the last 3 years
F) upgrading to the latest application version
G) monitoring OS resources for patterns
H) consulting the application team about any future projects and use
参考文档 MySQL 8.0 for Database Administrators StudentGuide 2.pdf 页数 P122

**Answer:** EGH

**解析：**
待补充

## Question 55

Choose the best answer.
Which statement is true about MySQL Enterprise Transparent Data Encryption (TD  - E)?
  - 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.
  - C) Lost tablespace encryption keys can be regenerated only if the master database key is known or
present in the Key Vault specification.
  - D) Both MyISAM and InnoDB tables can be encrypted by setting the keyring_
engine = All
variable in the MySQL configuration file.
参考文档 MySQL 8.0 for Database Administrators StudentGuide 2.pdf 页数 P75

**Answer:** A

**解析：**
待补充

## Question 56

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.

**Answer:** AB

**解析：**
待补充

## Question 57

Choose two.
Examine this command, which executes successfully:_
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 不存在数据库_

```sql
mysqlpump --user=root --password > full
```

**Answer:** BE

**解析：**
待补充

## Question 58

Choose three.
Examine these statements, which execute successfully:
BEGIN;
"Hello") ;
ROLLBACK;
Which three storage engines would return a nonempty recordset for the test table when executing
the statements?
  - A) MEMORY
  - B) BLACKHOLE
  - C) ARCHIVE
  - D) NDB
  - E) MyISAM
F) InnoDB
参考文档 MySQL 8.0 for Database Administrators StudentGuide 1.pdf 页数 P129 P137

```sql
TRUNCATE test;
INSERT INTO test (id, nam  - e) V ALUES(1,
SELECT id FROM test;
```

**Answer:** ACE

**解析：**
待补充

## Question 59

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.
  - B) The Innodb Buffer pool is full.
  - C) You use the MyISAM storage engine for most common tables.
  - D) Your table accesses wait for the operating system level flush.

**Answer:** C

**解析：**
待补充

## Question 60

Choose two.
Examine this statement:
role1, r__
Which two are true?
role2 ;
  - 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.
  - E) It fails if you do not have the ADMIN OPTION of the roles r
role1 and r
role2.__
F) It fails if any of the roles is specified in the mandatory_
roles variable.

```sql
mysql>DROP ROLE r
```

**Answer:** CF

**解析：**
待补充

## Question 61

Choose the best answer.
Examine this command, which executes successfully:
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.

**Answer:** B

**解析：**
待补充

## Question 62

Choose the best answer.
You are upgrading a MySQL instance to the latest 8.0 version.
Examine this output:.
You plan to add this parameter to the configuration:
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

**Answer:** A

**解析：**
待补充

## Question 63

Choose two.
moment?
  - A) InnoDB
  - B) ARCHIVE
  - C) MyISAM
  - D) MEMORY
  - E) NDB
Which two storage engines provide a view of the data consistent with the storage system at any

**Answer:** AE

**解析：**
待补充

## Question 64

Choose two.
Which two commands will display indexes on the parts table in the manufacturing schema?
  - A) EXPLAIN SELECT INDEXES FROM 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
STA TISTICS;__

**Answer:** CD

**解析：**
待补充

## Question 65

Choose two.
Examine this query and output:
Name ,
city.Name, city.District, city.Population
FROM world.city
INNER JOIN world.country ON country.Code = city.CountryCode
WHERE country.Continent = ' Asia '
AND city.Population > 1000000
ORDER BY city.Population DESC\G
Which two statements are true?
  - 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.

```sql
mysql> EXPLAIN ANALYZE
SELECT city.CountryCode, country.Name AS Country_
```

**Answer:** AE

**解析：**
待补充

## Question 66

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.

**Answer:** CFG

**解析：**
待补充

## Question 67

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
  - 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

**Answer:** AC

**解析：**
待补充

## Question 68

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,

**Answer:** BDF

**解析：**
待补充

## Question 69

Choose two.
Examine this SQL statement:_
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.
  - 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.

```sql
mysql> GRANT r
```

**Answer:** BE

**解析：**
待补充

## Question 70

Choose two.
Which two MySQL Server accounts are locked by default?
  - A) any new ROLE accounts
  - B) any internal system accounts
  - 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

**Answer:** A B

**解析：**
待补充

## Question 71

Choose the best answer.
Examine this SQL statement:
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
`@`%`
;
world`
`
.
country
` TO `tom
`@`%`
;
  - B) GRANT UPDATE ON `
world`
`
.
city
` TO `tom
`@`%`
world`
.
* TO `tom
`@`%`
  - C) GRANT UPDATE ON `
world`
`
.
city
` TO `tom
`@`%`
world`
`
.
country
` TO `tom
`@`%`
  - D) GRANT ALL PRIVILEGES ON `
world`
`
.
city
` TO `tom
`@`%`
code
`) ON `
world`
`
.
country
` TO `tom
`@`%`

```sql
UPDATE world.city
SET Population = Population * 1.1
GRANT ALL PRIVILEGES ON `
GRANT SELECT ON `
GRANT SELECT ON `
GRANT SELECT (`
```

**Answer:** C

**解析：**
待补充

## Question 72

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:
Which statement is true?
  - A) Replication will fail because the master has already purged transactions with
ccccc-cccc-cccc-ccc-cccccccccc GTIDs.
  - B) Replication will work.
  - C) Replication will fail because the master does not have the required transaction with
bbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbb GTIDs in its binary logs.
  - D) Replication will fail because the slave has purged more aaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa
transactions than the master.
  - E) Replication will fail because of inconsistent numbers in ccccccc-cccc-cccc-ccccccccccccc
GTIDs.

**Answer:** C

**解析：**
待补充

## Question 73

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> '
.
  - D) Execute SET GLOBAL expire_
logs_
days=0 to enforce a log rotation.

**Answer:** B

**解析：**
待补充

## Question 74

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
F) operating system memory utilization
G) network-related information and network characteristics

**Answer:** ABE

**解析：**
待补充

## Question 75

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
  - D) ib_
logfile* files
  - E) *
.sdi files

**Answer:** AD

**解析：**
待补充

## Question 76

Choose two.
Examine this command and output:
Which two statements are true?
  - A) The lock is an exclusive lock.
  - B) The lock is a shared lock.
  - C) The lock is a row-level lock.
  - 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.

**Answer:** AC

**解析：**
待补充

## Question 77

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.
  - B) There is no need for InnoDB tables to perform its own recovery when restoring from the
snapshot backup.
  - C) Snapshot-based backups greatly reduce time during which the database and applications are
unavailable.
  - D) A separate physical copy must be made before releasing the snapshot backup.
  - E) Snapshot backups can be used only in virtual machines.
参考文档 MySQL 8.0 for Database Administrators StudentGuide 2.pdf 页数 P224

**Answer:** CD

**解析：**
待补充

## Question 78

Choose two.
Examine this MySQL client command to connect to a remote database:
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
  - C) VERIFY
IDENTITY ._
  - D) PREFERED
  - E) VERIFY
CA_
参考文档 MySQL 8.0 for Database Administrators StudentGuide 2.pdf 页数 P39

**Answer:** CE

**解析：**
待补充

## Question 79

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.
  - C) SQL injections could be used to insert bad data into the database.
  - D) Data files could be deleted.
  - E) Users could overwrite configuration files.

**Answer:** DE

**解析：**
待补充

## Question 80

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:_
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

```sql
mysqldump --delete-master-logs --all-databases > /backup/db
```

**Answer:** AE

**解析：**
待补充

## Question 81

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.
  - B) Binary logs can always be used to unapply unwanted schema changes.
  - 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.

**Answer:** CD

**解析：**
待补充

## Question 82

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.
  - C) It acquires a lock on the binary log for reading each event to be sent to the slave.
  - D) It reads the relay log and executes the events contained in them.
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.

**Answer:** C

**解析：**
待补充

## Question 83

Choose two.
Examine this command and output:
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.

**Answer:** A C F?

**解析：**
待补充

## Question 84

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.
  - 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.
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

**Answer:** AD

**解析：**
待补充

## Question 85

Choose the best answer.
You execute this command:
shell> mysqlpump --exclude-databases=% --users
Which statement is true?
  - A) It creates a logical backup of all metadata, but contains no table data.
  - 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.

**Answer:** D

**解析：**
解析：实验

## Question 86

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:
1.Backing up multiple servers to a single server.
2.Merging table shards.
3.Consolidating data from multiple servers to a single server.
https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source.html

**Answer:** B

**解析：**
待补充

## Question 87

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

**Answer:** BCD

**解析：**
待补充

## Question 88

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.

**Answer:** BCD

**解析：**
待补充

## Question 89

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
  - B) mysqlpump --include-databases=db% --result-file=all
db__
backup.sql
  - C) mysqlpump --include-databases=db -- result-file=all
db__
backup.sql
  - D) mysqlpump -- include-tables-db.% --result-file=all
db__
backup.sql

**Answer:** B

**解析：**
待补充

## Question 90

Choose the best answer.
You issue this command:
In the output, there is a value for seconds
behind__
How is this time calculated?
master.
  - 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.
  - 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.
Seconds
Behind__
Master: This column provides the number of seconds between the timestamp
(on the master) of the most recent event in the relay log executed by the SQL thread and the real
time of the slave machine.

```sql
SHOW SLA VE STA TUS
```

**Answer:** A

**解析：**
待补充

## Question 91

Choose three.
Which three actions will secure a MySQL server from network-based attacks?
  - A) Construct a perimeter network to allow public traffic
  - 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) Use MySQL Router to proxy connections to the MySQL server.
F) Allow connections from the application server only.

**Answer:** BEF

**解析：**
待补充

## Question 92

Choose the best answer.
Consider this shell output and executed commands:
[root@oel7~] # ps aux | grep mysqld
mysql 2076 3.5 24.6 1386852 372572 2 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 shutdowm or systemctl stop
mysqld.
kill -15 PID 可以理解为操作系统发送一个通知告诉应用主动关闭.

**Answer:** C

**解析：**
待补充

## Question 93

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.
  - D) If the dictionary object cache becomes full, MySQL server will be unable to create any more
tables/objects.
  - 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

**Answer:** AE

**解析：**
待补充

## Question 94

choose two
Examine Joe's account:
.
* 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 :
  - C) REVOKE ALL PRIVILEGES ON *
.
* FROM 'joe'@'%'
  - D) ALTER USER 'joe'@'%' SET password='*invalid*'
  - E) ALTER USER 'joe'@'%' IDENTIFIED BY '*invalid*' PASSWORD EXPIRE
F) REVOKE USAGE ON *
.
* FROM 'joe'@'%'
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

```sql
CREATE USER 'joe'@'%' IDENTIFIED BY '*secret*'
GRANT ALL PRIVILEGES ON *
```

**Answer:** AE

**解析：**
待补充

## Question 95

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

**Answer:** D

**解析：**
待补充

## Question 96

choose two.
Examine the modified output:
******************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.

```sql
mysql> SHOW SLA VE STA TUS\G
```

**Answer:** AD

**解析：**
待补充

## Question 97

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 time-zone data.
All the InnoDB tables in the mysql system database are stored in the mysql general tablespace
(mysql.ib  - d) 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

**Answer:** ABC

**解析：**
待补充

## Question 98

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.
  - B) Create a new ib_
logfile0 file of size 26214400.
  - C) Remove ib_
logfile0 and ib_
logfile1 files from the file system.
  - D) First run mysqld --initialize to refresh the size of ib_
logfile.

**Answer:** C

**解析：**
待补充

## Question 99

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._
  - B) Network connectivity issues occurring between client and the MySQL instance.
  - C) Connections are attempted without a valid user account or password.
  - D) User accounts are defined using the mysql
native__pasword plugin for password authentication.
  - E) thread
cache is too small._
F) skip_
name
resolve is enabled._
COUNT
AUTH
PLUGIN
ERRORS:367 身份验证插件报告的错误数 Unknown or unexpected plugin errors are counted in___
the COUNT
AUTH
PLUGIN
ERRORS column.___
Aborted
connects :_
#客户端没有权限但是尝试访问 MySQL 数据库
#客户端输入的密码有误。
#连接包不包含正确信息
#超过连接时间限制，主要是这个系统变量 connect_
timeout 控制

**Answer:** B

**解析：**
待补充

## Question 100

Choose the best answer
You have configured MySQL Enterprise Transparent Data Encryption (TD  - E).
What command would you use to encrypt a table?
  - A) UPDATE <table> SET ENCRYPTION= 'Y';
  - B) ALTER INSTANCE ROTA TE INNODB MASTER KEY;
  - C) UPDATE information_
schema.tables SET encryption='Y' WHERE table_
  - D) ALTER TABLE <table> ENCRYPTION='Y';
name='table';
https://dev.mysql.com/doc/refman/8.0/en/alter-tablespace.html

**Answer:** D

**解析：**
待补充

## Question 101

Choose two.
Examine this statement and output:
Grants for jsmith@%
.
* TO 'jsmith@'%'
2 rows in set (0.00 se  - c)
Which two SQL statements can j smith execute?
  - A) UPDATE world.country SET Name=CONCAT ('New '
,Nam  - e) ;
  - 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 语句

```sql
mysql> SHOW GRANTS FOR jsmith;
GRANT USAGE ON *
GRANT UPDATE (Nam  - e) ON 'world.country' TO 'jsmith'@'%';
```

**Answer:** BD

**解析：**
待补充

## Question 102

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:
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.
  - D) You must use --stop position=1797 to avoid the DELETE statement that caused the initial
problem.
A 没有这个命令

**Answer:** C

**解析：**
待补充

## Question 103

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

**Answer:** CEG

**解析：**
待补充

## Question 104

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

**Answer:** E

**解析：**
待补充

## Question 105

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.

**Answer:** BCEF

**解析：**
待补充

## Question 106

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.
  - B) Mary connected to the database server whose IP address is 192.0.2.101.
  - C) Mary has the privileges of account mary@%.
  - D) Mary connected using a UNIX socket.
  - E) Mary authenticated to the account mary@192.0.2.101.

**Answer:** AC

**解析：**
待补充

## Question 107

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.

**Answer:** B

**解析：**
待补充

## Question 108

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
,
,
cluster.setOption ("expelTimeout"
,1)
Now examine the partial status:_
SERVER")
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
re-established.
  - B) Failure of the instance deployed on host1 provokes an outage.
  - C) The instance deployed on host3 is expelled from the cluster and must be rejoined using
cluster.addInstance('host3:3377').
  - D) The primary instance can be specified by using the command cluster.setPrimaryInstance
(<host>:<port>).
  - E) The instance deployed on host2 is elected as the new primary instance.
F) The issuing command cluster.switchToMultiPrimaryMode() will fail to enable multi-primary
mode.
rejoinInstance()
exitstateAction =group_
replication
exit
state___
action
ABORT
SERVER：实例将关闭 MySQL_
OFFLINE
MODE 这种模式下，已连接的客户端用户将在下一个请求时断开连接，连接将不再被接受，拥有 CONNECTION__
ADMIN 特权(或已弃用
的 SUPER 特权)的客户端用户除外。
READ
ONLY_

**Answer:** DE

**解析：**
待补充

## Question 109

Choose two.
You administer a three node, single primary InnoDB Cluster.
Examine cluster.status() displayed here:
"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.
  - B) Reconfiguring the cluster as multi-primary, will increase tolerance to two failures.
  - C) There is a quorum and transactions can be committed normally.
  - D) If two instances crash, it will produce an outage.
  - E) Restarting an arbitrary instance will always provoke primary instance failover.
F) Shutting down two instances with the SHUTDOWN command will produce an outage.
https://dev.mysql.com/doc/refman/8.0/en/group-replication-fault-tolerance.html
Cluster is ONLINE and can tolerate up to ONE failure:容错节点

**Answer:** DF

**解析：**
待补充

## Question 110

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

**Answer:** BCD

**解析：**
待补充

## Question 111

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

**Answer:** CE

**解析：**
待补充

## Question 112

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:
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;_purged=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa:1-2312;
executed=aaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaa:1-10167;_
  - B) RESET MASTER;_purged=aaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaa:1-10167;
  - C) RESET SLA VE;_purged=aaaaa-aaa-aaaa-aaaa-aaaaaaaa:1-3820;
executed=aaaaaa-aaaa-aaa-aaaa-aaaaaaaaaaa:1-10300;_
  - D) SET GLOBAL gtid_purged=aaaaaa-aaa-aaa-aaaa-aaaaaaaa:1-2312,
bbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbb:1-9;
executed=aaaaaaa-aaaa-aaaa-aaaa-aaaaaa:1-10167;_
  - E) RESET SLAVE;_purged=aaaaaaa-aaa-aaaa-aaa-aaaaaaaa:1-10167;

```sql
SET GLOBAL gtid
SET GLOBAL gtid
SET GLOBAL gtid
SET GLOBAL gtid
SET GLOBAL gtid
SET GLOBAL gtid
SET GLOBAL gtid
```

**Answer:** D

**解析：**
待补充

## Question 113

Choose two.
You have semi-synchronous replication configured and working with one slave.
timeout has never been reached.
rpl
semi__
sync
master__
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__
change to async mode.
timeout has never been reached,还没有 timeout，if reaches then it will

**Answer:** CD

**解析：**
待补充

## Question 114

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.
  - 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.

**Answer:** B

**解析：**
待补充

## Question 115

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.

**Answer:** DE

**解析：**
待补充

## Question 116

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 -
Anwser:A
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

**解析：**
待补充

## Question 117

Choose the best answer.
The mysqld instance has the connection control plugin enabled with these settings:
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:
control
min
connection____
What is the result?
delay=3000;
  - 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.
Anser:D
connection_
connection
control
min
connection___
delay cannot be set greater than the current value of
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

```sql
mysql> SET GLOBAL connection
```

**解析：**
待补充

## Question 118

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

**Answer:** AE 数据都在内存里

**解析：**
待补充

## Question 119

Choose the best answer.
Examine these commands and results:
.
* TO ‘’
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;
  - B) GRANT CREATE TEMPORARY TABLES ON sales.
* TO jane;
  - C) GRANT CREATE TEMPORARY TABLES ON sales.totalsales TO jane;
  - D) GRANT ALL ON sales.
* TO jane;
Anser:B
必须是 数据库名.
*

```sql
SHOW GRANTS FOR jane;
GRANT USAGE ON *
```

**解析：**
待补充

## Question 120

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 <binlog_
name>.
  - B)Use SET GLOBAL binlog_
expire_
logs
seconds=<value> and run the FLUSH BINARY LOGS_
command.
  - C) Use SET GLOBAL binlog_
expire_
logs
seconds=<value> and restart the server._
  - D) Use SET PERSIST binlog_
expire_
logs
seconds=<value>._
  - E) Set binlog_
expire_
logs_
seconds = 0 in my.cnf and restart the server.
F) Set binlog_
expire_
logs_
seconds in my.cnf.

**Answer:** AB

**解析：**
待补充

## Question 121

Choose three.
Which three methods display the complete table definition of an InnoDB table?
  - A) hexdump -v -C table.frm
  - B) REPAIR TABLE table USE
FRM_
  - C) mysqldump --no-data schema table
  - D) Query the Information Schema.
  - E) SELECT * FROM table 1\G
F) SHOW CREATE TABLE

**Answer:** CDF 8 里面没有 frm

**解析：**
待补充

## Question 122

Choose the best answer.
Your MySQL Server is running locally on your Linux installation, and has SSL connections
configured but not mandatory.
# mysql -u root -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
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
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

```sql
mysql> STA TUS;
mysql> SHOW GLOBAL V ARIABLES WHERE Variable
```

**Answer:** A

**解析：**
待补充

## Question 123

Choose the best answer.
Your my.cnf file contains these settings:
[mysqld]
log_
output=FILE
slow_query_
log
long_query_
time=2.01
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
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，将不会记录到慢查询日志中；___

**Answer:** C

**解析：**
待补充

## Question 124

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.
  - 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.
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
：

**Answer:** B

**解析：**
待补充

## Question 125

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:
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.
  - 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
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.

```sql
GRANT UPDATE ON world.city TO 'user1' ;
```

**Answer:** B immediately.

**解析：**
待补充

## Question 126

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.

**Answer:** ACH

**解析：**
待补充

## Question 127

Choose two.
Which two methods allow a DBA to reset a user's password?
  - A) SET PASSWORD statement
  - B) mysql
secure__
installation utility
  - C) ALTER USER statement
  - D) GRANT statement
  - E) mysqladmin client program

**Answer:** AC mysqladmin 只能改自己的

**解析：**
待补充

## Question 128

Choose two.
A scientific data gathering application uses a MySQL instance back end for data management.
There is a high concurrency of transactions at thousands of transactions per second of volatile
data.
A restore from binary logs is planned using the command:
mysqlbinlog
--start-datetime='2019-08-01 11:00:00'
--stop-datetime='2019-08-10 08:30:25'
binlog.000238 binlog.000239 binlog.000240 | mysql
Which two characteristics cause the restore to be inconsistent to the original data?
  - A) Transaction rate is too high to get a consistent restore.
  - B) Multiple binary logs cannot be specified on the command line.
  - C) Temporary tables cannot persist across binary logs.
  - D) The temporal values do not offer high enough precision.
  - E) The time span of binary logs is too long to restore.

**Answer:** AD

**解析：**
待补充

## Question 129

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
  - B) --protocol=pipe
  - C) --host=192.0.2.1
  - D) --protocol=UDP
  - E) --user=admin
F) --password
G) --socket=/tmp/mysql.sock
H) --shared-memory-base-name=world
I) --database=world
130
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 file.
Which command should you use?
  - A) mysqlshow --debug --stop-position=NNNNN log_
file
  - B) mysqlbinlog --verbose --start-position=NNNN log_
file
  - 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
G) mysqlshow --debug --start-position=NNNNN log_
file
H) mysqlshow --verbose --start-position=NNNNN log_
file
Amswer: B
131
Choose two.
Examine this command and output:
lock
waits___
summary_
by_
table where COUNT_
STAR>0 limit 1\G
Which two are true?
  - A) I/O distribution is approximately 50/50 read/write.
  - B) The I/O average time is 532728. These columns aggregate all fetch operations
  - C) 22902028 rows were deleted. These columns aggregate all delete operations.
  - D) Average read times are approximately three times faster than writes.
  - E) The longest I/O wait was for writes.
CD
132
Choose the best answer.
Examine the command, which executes successfully:
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.
  - D) The root password is not created allowing easy access from the same host.
A
133
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.
CD
134
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.
  - 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.
  - C) Stop the MySQL Server process, copy the Customers.ibd file from the dbbackup directory, and
start the mysqld process.
  - 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.
D
135
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 --datebase=mydb1 --database=mnydb2 | mysql
  - D) mysqlbinlog --rewrite-db='mydb1'
--rewrite-db='mydb2' | mysql
B
136
Choose two.
Examine this statement, which executes successfully:
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:
no, first
name, last
name, birth
date___
FROM employees
WHERE MONTH (birth_
dat  - e) = 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 unsigne  - d)));
  - B) ALTER TABLE employees
ADD INDEX (birth_
date DES  - C) ;
  - C) ALTER TABLE employees
ADD COLUMN birth_
month tinyint unsigned GENERATED ALW AYS AS (MONTH
(birth_
dat  - e)) VIRTUAL NOT NULL,
ADD INDEX (birth_
month) ;
  - D) ALTER TABLE employees
ADD INDEX (birth_
dat  - e);
  - E) ALTER TABLE employees
ADD COLUMN birth_
month tinyint unsigned GENERATED ALW AYS AS (birth
date_
->>'$.month') VIRTUAL NOT NULL,
ADD INDEX (birth_
month) ;
F) ALTER TABLE employees
ADD INDEX ((MONTH (birth_
dat  - e)));
137
Choose two.
You must export data from a set of tables in the world
x database._
Examine this set of tables:
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;_
x.countryinfo;
x.location;_
  - C) shell> mysqlexport world_
x country countryinfo location > mydump.sql
  - D) mysql> CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql/world_
x/country' ;_
x/countryinfo' ;
x/location' ;_
  - E) shell> mysql --batch world_
x.country world_
x.countryinfo world_
x.1ocation > mydump.sql
没有 mysqlexport 这个工具
138
Choose two.
Examine this command, which executes successfully on InnoDB Cluster:
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.
  - 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.
Drops the Metadata Schema.
139
Choose the best answer.
Examine this command, which executes successfully:
$ 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.
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
140
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
141
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
  - B) avoiding concatenation of SQL statements and user-supplied values in an application
  - C) using PREPARED STA TEMENTS
  - D) installing and configuring the Connection Control plugin
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
142
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.
C E
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.
143
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
  - B) ib_
logfile0
  - C) mysql.ibd
  - D) ibdata1
  - E) ibtmp1
F) undo
001_
C D
144
Choose the best answer.
Examine this command, which executes successfully:
$ 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.
A
https://dev.mysql.com/doc/mysql-router/8.0/en/mysqlrouter.html#option_
mysqlrouter_
bootstrap
145
Choose the best answer.
Examine this command:
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.
146
Choose the best answer.
How can mysql_
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 teach instance.
  - C) The instances use different socket names.
  - D) The instances have appropriate net masks set.
A
147
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.
148
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. tablespace
  - B) Temporary tables are created in tmpdir only after they reach tmp_
table
size. 不对_
  - C) Temporary tables are created in tmpdir only if configured to use MyISAM.
  - D) Temporary tables will use the InnoDB temporary tablespace located in datadir. 参数 innodb_
tmpdir
  - E) Temporary tables will use the InnoDB temporary tablespace located in /tmp. 参数 innodb_
tmpdir
D
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.
149
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.
  - 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.
E
每个缓冲池保证 1G 的情况下 128MB 的倍数
150
Choose the best answer.
Examine this parameter setting:
audit_
log=FORCE
LOG
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.
A
--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
151
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.
  - 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.
  - 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.
C D F
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
152
Choose two.
Which two statements are true about MySQL Installer?
  - A) It provides only GUI-driven, interactive installations.
  - B) It installs most Oracle MySQL products.
  - C) Manual download of separate product packages is required before installing them through
MySQL Installer.
  - D) It provides a uniform installation wizard across multiple platforms.
  - E) It performs product upgrades.
BC
153
Choose the best answer.
Examine these entries from the general query log:
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.
  - C) Connection 25 experiences a lock wait timeout.
  - D) A deadlock occurs after innodb
lock
wait
timeout seconds.___
  - E) Connection 24 experiences a lock wait timeout.
B
如果启用了死锁检测(默认)，并且发生了死锁，InnoDB 会检测到这个条件并回滚其中一个事务(受害者事务)。如果使用
innodb
deadlock
detect 配置选项禁用了死锁检测，InnoDB 将依赖于 innodb
lock
wait
timeout 设置来在出现死锁时回滚事务_____
154
Choose the best answer.
You recently upgraded your MySQL installation to MySQL 8.0.
Examine this client error:
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';
  - B) [mysqld]
default
authentication__plugin=sha256_password
  - C) [mysqld]
default
authentication__plugin=caching_
sha2_password
  - D) ALTER USER user
IDENTIFIED WITH mysql
native__password
BY 'password' ;
  - E) ALTER USER user
IDENTIFIED WITH sha256_password
BY 'password' ;
F) [mysqld]
default
authentication__plugin=mysql
native__password
D
155
Choose two.
Which two tools are available to monitor the global status of InnoDB locking?
  - A) SHOW ENGINE INNODB STA TUS;
  - B) SHOW TABLE STA TUS;
  - C) INEORMATION
SCHEMA.INNODB
TABLESTA TS .__
  - D) SHOW STA TUS;
  - E) INFORMATION
SCHEMA.STA TISTICS_
F) INFORMATION
SCHEMA.INNODB
METRICS__
AD
156
Choose the best answer.
Examine this partial output for InnoDB Cluster status:
Which statement explains the state of the instance deployed on host2?
  - A) It can rejoin the cluster by using the command cluster.addInstance ('<user>@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 ('<user>@host3:3377').
  - 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().
C
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
157
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.
  - D) It can be activated per session.
  - E) It encrypts any connecting slaves connection thread.
AC
binlog_
encryption Dynamic
Existing binary log files and relay log files still present on the server are not encrypted
158
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 SLA VE;
  - B) The slave needs to be restored from backup.
  - C) The slave relay logs should be deleted; execute CHANGE MASTER to adjust the replication
relay log file name, then issue START SLAVE;
  - D) The relay logs from the master should be used to replace the corrupted relay logs.
C
159
Choose two.
Which two statements are true about the mysqld-auto.cnf file?
  - A) It is always updated with changes to system variables.
  - 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.
CD
160
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
ADE
161
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.
  - B) Execution plans based on transient index statistics improve precision when
innodb
stats__persistent_
sample_pages is increased.
  - C) Index statistics are calculated from pages buffered in the buffer pool for tables with InnoDB
storage engine.
  - D) Setting innodb
stats
auto___
recalc=ON causes statistics to be updated automatically when a new
index is created. more than 10% of its rows
  - E) Updating index statistics is an I/O expensive operation.
E
162
Choose two.
Examine this query and its output:
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
  - B) User bob had the largest total time waiting for locks.
  - C) The app user had the highest total number of rows read from storage engines.
  - D) The root user had the largest number of modified rows for a SELECT statement.
  - E) The root user had the largest single wait time.
BC
D modifid 有问题。root 账号 select insert quit 操作
163
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_
innodb
flush
size=20G_
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_
DF
164
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.
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.
  - 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.
E
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 table spaces are not backed up
by mysqlbackup in this phase.
https://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/meb-backup-optimistic.html
165
Choose two.
You are asked to review possible options for a new MySQL instance. It will be a large, busy
reporting data warehousing instance.
[mysql]
innodb
data
file___path=
Which two 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
A B
Theautoextend attribute can be specified only for the last data file in
the innodb
data
file___path setting.
166
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.
  - C) Physical upgrades are performed for current instances on bare metal deployments, whereas
logical upgrades are used for virtual machines or containerized instances.
  - 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.
F) Physical upgrades leave data in place, whereas logical upgrades require data to be restored from
mysqldump-type backups taken before the upgrades.
EF
167
Choose the best answer.
Examine this snippet from the binary log file named binlog.000036:
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
  - B) mysqlbinlog --stop-datetime='2019-11-20 14:55:16' binlog.000036 | mysql
  - C) mysqlbinlog --stop-datetime='2019-11-20 14:55:18' binlog.000036 | mysql
  - D) mysqlbinlog --stop-position=500453 binlog.000036 | mysql
A
168
Choose the best answer.
Four nodes are configured to use circular replication.
Examine these configuration parameters for each each node:
slave_parallel_
type=DATABASE ;
slave_parallel
workers=4_
slave_preserve
commit__
Which statement is true?
order=0
  - A) Each slave thread is responsible for updating a specific database.
  - B) Cross-database constraints can cause database inconsistency.
  - C) Setting slave_parallel_
type=DATABASE won't work for circular replication; it should be set to
LOGICAL
CLOCK._
  - D) Increasing slave_parallel_
workers will improve high availability.
  - E) Setting slave_preserve
commit__
order to ON will improve data consistency.
F) Setting transaction
allow__
batching to ON will improve data consistency.
B
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
ype
t_
169
Choose the best answer.
MySQL is installed on a Linux server with this configuration:
[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.
  - 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.
B
New install so setting my.cnf file
170
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
denied.
access__
denied is the number of connection attempts from prohibited hosts that are
  - D) Firewall
access__granted is the number of connections granted from whitelisted hosts.
B
• 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 缓存中白名单摘要的数量
171
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
C E
172
Choose the best answer.
Examine these two reports taken 100 seconds apart:
Your MySQL system normally supports 50-75 concurrent connections.
Which configuration change will improve performance?
  - A) increase max
connections_
  - B) decrease open
files
limit__
  - C) decrease table
definition
cache__
  - D) increase table_
open
cache_
D
open
tables:是当前在缓存中打开表的数量_
'Open
table__
definitions'; //当前打开的表定义
table_
open_
cache:ibd/MYI/MYD 文件
table
definition
cache=__
.frm
173
Choose two.
Examine this statement, which executes successfully:
PASSWORD EXPIRE;
Which two are true?
  - A) Mary must connect using the username 'mary@192.0.2.100'
.
  - 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.
  - E) Mary cannot query data until she changes her password.
CE
174
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.
  - C) An agent must be installed locally on the instance to use the Query Analyzer.
  - D) The Query Analyzer can monitor an unlimited number of normalized statements.
  - E) The slow query log must be enabled on the monitored server to collect information for the
Query Analyzer.
D
175
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.

```sql
select *from table
CREATE TABLE employees (
SELECT emp_
mysql> SELECT * INTO OUTFILE '/output/countryinfo. txt' FROM world
mysql> SELECT * INTO OUTFILE '/output/location. txt' FROM world
mysql> CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql/world
mysql> CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql/world
CREATE USER mary@192.0.2.100 IDENTIFIED BY 'P@SSw0rd' REQUIRE NONE
```

**Answer:** C D

**解析：**
待补充