MySQL 8.0 OCP 1Z0-908 171-180题

Q171.Examine this MySQL client command to connect to a remote database:
mysql-h remote-example.org-u root–protocol=TCP–ssl-mode=
Which two–ss1-mode values will ensure that an X.509-compliant certificate will be used to establish the
SSL/TLS connection to MySQL?
A)VERIFY_CA
B)REQUIRED
C)DISABLED
D)PREFERRED
E)VERIEY_ IDENTITY
Answer:AE

选项A：VERIFY_CA：要求必须使用 SSL，并验证证书颁发机构（CA）。这确保了使用的证书符合 X.509 标准，因为它会检查证书是否由受信任的 CA 签名。
选项B：REQUIRED：要求必须使用 SSL，但不一定会验证服务器的证书是否符合 X.509 标准。
选项C：DISABLED：禁用 SSL，显然不能满足要求。
选项D：PREFERRED：表示优先使用 SSL，但不会强制使用，也不确保证书符合 X.509。
选项E：VERIFY_IDENTITY：要求必须使用 SSL，并验证服务器的证书身份。这不仅验证了证书是否由受信任的 CA 签名，还确保服务器的证书身份与连接的主机名匹配，符合 X.509 标准

Q172.Many connects to a Linux MySQL Server from a client on a Windows machine.Examine this statement and
output:
mysql> SELECT USER () , CURRENT USER () ;
±-------------------------±--------------------+
|USER () | CURRENT_USER ( ) |
±-------------------------±--------------------+
|mary@192 .0.2.101 | mary@ % |
±-------------------------±--------------------+
row in set (0.00 sec)
Which two are true?
A)Mary connected using a UNIX socket.
B)Mary connected from a client machine whose IP address is 192.0.2.101.
C)Mary connected to the database server whose IP address is 192.0.2.101.
D)Mary has the privileges of account mary@%.
E)Mary authenticated to the account marye192.0.2.101.
Answer:BD

user():登录的用户
current_user()：验证的权限用户

Q173.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 backup directory to their original MySQL server locations .
B)The copy-back process makes inconsistent backups.
C)The copy-back process is used to overwrite a new backup over an existing backup.
D)It restores files from the data directory to their original MySQL server locations.
Answer:D

--backup_dir:mysql的备份文件路径
-copy-back:从MySQL的备份文件恢复到数据库原始的数据目录，也就是--default-file所指向的配置文件中的datadir
官方文档原文:Restores files from a directory backup to their original locations within the MySQL server.
官方原文可参考：https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/backup-commands-restore.html
选项A：正确
选项B：copy-back 过程本身不会导致备份不一致，备份的一致性取决于备份时的设置和 MySQL 的状态。
选项C：可查看官方文档，copy-back不会覆盖已存在的备份
选项D：错误
个人理解该答案应该选A

Q174.You are planning to take a full MySQL instance backup .
Which two factors will help to determine the backup strategy?
A)the expected size of the backup
B)how much down time is planned
C)the OS super user rights
D)the number of user accounts for the MySQL instance
E)the location of the physical server
Answer:AB

全备策略的指定：备份的所需要的磁盘空间，然后就是恢复时所需要停机的时间，所以AB是正确的，CDE对于备份策略的指定没什么影响

Q175.Which three are characteristics of logical backups?
A)They consist of exact copies of database directories and files.
B)They can be created by mysqlbackup for InnoDB tables or by file-system commands, such as cp,
scp, tar, or rsync, for MyISAM tables .
C)They can be performed while the MySQL server is not running.
D)They can be run only against a running MySQL server.
E)They are machine independent and highly portable.
F)Backup and restore granularity is available at the server level, database level, or table level for any storage engine.
G)In addition to databases, backups can include any related files, such as log or configuration files.
Answer:DEF

选项A：逻辑备份不会拷贝数据路径的文件
选项B：逻辑备份对于innodb可以用mysqlback进行备份，对于MyISAM使用的是cp，scp,tar或者rsync的方式
选项C：逻辑备份必须在数据库运行期间执行
选项D：它们只能在正在运行的 MySQL 服务器上执行。所以正确
选项E：他们与机器无关，并且有很高的移植性，正确
选项F：对于任何存储引擎备份和恢复的粒度可以是服务级别，数据库或者表级别
选项G：逻辑备份不会拷贝redo,或者配置文件等

Q176.What does the slave SQL thread do?
A)reads the relay log and executes the events contained in them
B)connects to the master and asks it to send updates recorded in its binary logs
C)acquires a lock on the binary log for reading each event to be sent to the slave
D)monitors and maintains channel connections if multiple replication channels are used
Answer:A

选项A：读取relay log并执行里面的事务，正确
选项B：sql线程并不会连接到master并且求其更新的记录
选项C：在读取每个要发送给从属服务器的事件时，对二进制日志获取锁，这个是错误的，SQL线程读取的是relay log，而不是binlog，所以不会对binlog加锁
选项D：他不会监控和维护通道的连接

Q177.Examine this statement and output:

Which two SQL statements can jsmith execute?
A.UPDATE world.country SET Name=‘first’ ORDER BY Name LIMIT 1;
B.UPDATE world. country SET Name=‘new’ WHERE Name=‘old’;
C.UPDATE world.country SET Name=‘one’ LIMIT 1;
D.UPDATE world. country SET Name=CONCAT ('New ',Name);
E.UPDATE world.country SET Name='all’;
Answer:CE

其中ORDER BY,WHERE和CONCAT(('New ',Name)都需要select权限，所以无法执行。jsmith能执行的仅仅是update xxx set name=xxx。所以CE正确

Q178.Which three components comprise MySQL InnoDB Cluster to achieve database high availability?
A.MySQL Servers with Group Replication to replicate data to all members of the cluster
B.MySQL X Plugin to enable MySQL to use the X Protocol to speed up and monitor data replication
C.MySQL Semi-Sync replication plugin is used to provide cluster consistency
D.MySQL Shell to create and administer InnoDB Clusters using the built-in AdminAPI
E.MySQL Enterprise Backup to keep data consistent and always ready to be used.
F.MySQL Router to ensure that client requests are load balanced and routed to the correct servers
Answer:ABF

实现MySQL InnoDB Cluster的三个组件是MySQL router ，MySQL shell，MySQL MGR
所以答案应该是ADF

Q179.Examine these statements, which execute successfully:

What is the reason for the error?
A.John needs to reconnect to the database.
B.The statement was blocked by MySQL Firewall.
C.John has not activated the role.
D.The DBA needs to execute FLUSH PRIVILEGES.
Answer:D

从执行过程来看，john已经具有了r_world_rd@'%'角色的权限，而该角色可以对world库进行select，此处报错就是因为没有进行flush privileges进行生效

Q180.Which three are features of MySQL Enterprise Backup?
A.the ability to use the output file, which contains sQL statements, as input to a MySQL session
B.the ability to edit the output file produced by MySQL Enterprise Backup
C.the ability to back up individual tables or individual tablespaces while your server instance is online
E.the ability to extract and restore individual rows from a backup
F.the ability to perform incremental backups
Answer:ACF

选项C：MySQL Enterprise Backup 支持在线备份个别表或表空间，这是其核心功能之一。
选项F：支持增量备份，即只备份自上次备份以来更改的数据，有助于减少备份时间和存储需求。
A和B选项不太理解，暂时没了解过，有懂得大佬麻烦帮忙解答下