MySQL 8.0 OCP 1Z0-908 161-170題

Q161.Examine this command, which executes successfully:
cluster.addInstance ( ‘@:’,{recoveryMethod: ‘clone’ 1})
Which three statements are true? (Choose three.)
A)The account used to perform this recovery needs the BACKUP_ ADMIN privilege.
B)A target instance must exist, then it will be provisioned with data from an instance already in the cluster and
joined to the cluster,
C)InnoDB tablespaces outside the datadir are able to be cloned.
D)It ls always slower than {recoveryMethod:‘incremental’ }.
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.
Answer:ABC

cluster.addInstance ( '<user>@<host>:<port>',{recoveryMethod: 'clone' 1})使用clone的方式添加實例，
選項A：需要BACKUP_ADMIN權限，參考：https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-cluster-working-with-clone.html
選項B：目標實例已存在，也就是說被添加的實例已經初始化完成了，然后就準備從集群中的某個節點clone數據
選項C：在datadir之外的表空間的數據也會被clone
選項D：增量不一定比clone快
選項E：參考B，要求實例已存在，而不是新實例安裝初始化等等
選項F：CLONE復制會對redo進行歸檔，所以在clone運行期間，redo日志輪轉了也不影響，該答案參考：https://blog.csdn.net/weixin_43756308/article/details/140490714

Q162.Examine this set of messages and responses:
host3:3377 ssl JS > dba. rebootClusterFromCompleteOutage ()
Reconfiguring the default cluster from completeoutage…
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 instance deployed on host3 must be rebuilt with a backup from the primary instance.
B)The cluster is running and there is at least one ONLINE instance.
C)The instance deployed on host3 must be synchronized from a donor deployed on hos t1 by using thecommand
cluster.addInstance ( ‘host1:3377’ ).
D)It is possible to determine the most up-to-date instance by comparing different global transaction identifier (GTID)
sets with GTID SUBSET (set1, set2) .
E)The active session instance is invalid and must be re-created by using the command shell.connect( ‘host3:3377’) .
Answer:D
前面博客有這個題，可以往前翻翻

Q163.Consider an OLTP system with a high volume of concurrent INSERTS and UPDATES.
The overall MySQL Server performance has degraded with the addition of more users performing the same tasks.
What do you recommend?
A) Decrease innodb_lock_wait_timeout.
B) Enable innodb_api_disable_rowlock.
C) Set innodb_autoinc_lock_mode to 1.
D) Disable innodb_rollback_on_timeout.
Answer:C

選項A：innodb_lock_wait_timeout 減小 innodb_lock_wait_timeout 參數會縮短事務等待鎖的時間，導致更多事務因超時而回滾，從而降低系統的吞吐量。對于高并發系統，這可能會增加鎖競爭和回滾的開銷，進一步降低性能。
選項B：啟用 innodb_api_disable_rowlock 會禁用InnoDB的行級鎖，退化為表級鎖。這會顯著增加鎖的粒度，導致更多的鎖沖突，降低并發性能。
選項C：通過將 innodb_autoinc_lock_mode 設置為1，可以減少自增鍵的鎖競爭，提高高并發插入操作的性能。https://www.cnblogs.com/lizhaolong/p/16437320.html
選項D：禁用 innodb_rollback_on_timeout 參數會使得事務在等待鎖超時后不會自動回滾。這可能導致數據庫處于不一致狀態，違反了事務的ACID特性，通常不推薦這樣做
參考：https://www.cnblogs.com/lizhaolong/p/16437320.html

Q164.Examine this command and output:
root@dbhost: /var/lib/mysql# ls -al
total 540
drwxrwxr-x 1 mysql mysql 4096 Aug 22 14:07 .
drwxr-xr-x 1 root root 4096 May 22 00:42…
-rw-r- - ---- 1 mysql mysql 56 Aug 20 13:58 auto. cnf
drwxr-xr-x 1 mysql mysql 4096 Aug 21 10:28 accounting
-rw-r–r-- 1 mysql mysql 1112 Aug 20 13:58 ca.pem
-rw-r----- 1 mysql mysql 172040 Aug 22 14:07 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582919 Aug 22 14:07 ibdata1
-rw-r----- 1 mysql mysql 50331648 Aug 22 14:07 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Aug 20 13:47 ib_ ogfile1
-rw-r----- 1 mysql mysql 292292 Aug 22 14:07 ibtmp1
drwxr-x— 1 mysql users 4096 Aug 20 13:59 mysql
-rw-r----- 1 mysql mysql 64064 Aug 22 15:18 mysql-error.log
drwxr-x— 1 mysql mysql 4096 Aug 20 13:59 performance_schema
-rw-rw---- 1 mysql mysql 1680 Aug 20 13:59 private_key. pem
-rw-r–r-- 1 mysql mysql 452 Aug 20 13:59 public_key.pem
-rw-r–r-- 1 mysql mysql 1112 Aug 20 13:58 server-cert.pem
-rw------- 1 mysql mysql 1680 Aug 20 13:58 server-key.pem
drwxr-x— 1 mysql mysql 4096 Aug 20 13:59 sys
Which two options will improve the security of the MySQL instance? (Choose two.)
A)Remove group read/write privileges from the private_key.pem file.
B)Remove world read privileges from the server-cert.pem certificate file.
C)Change the group ownership of the mysql directory to the mysql user group.
D)Remove world read privileges from the public_key.pem file.
E)Change the parent directory owner and group to mysql.
F)Remove the world read/execute privilege from the accounting directory.
Answer:AF

Linux文件權限如下：
rwx 	rwx 	  rwx(讀 寫 執行)
所有者 所屬組	其他用戶選項A：私鑰文件 private_key.pem 目前具有組讀寫權限（rw-）。私鑰應當保持高度機密，只允許所有者（mysql 用戶）讀取和寫入。移除組讀寫權限可以防止其他用戶或組訪問私鑰，從而提高安全性
選項B： 服務器證書文件 server-cert.pem 目前其他用戶也具有讀權限（r--）。該文件可以公開，通常不需要限制世界讀權限。
選項C：mysql 目錄的所屬組已經是 mysql 用戶組
選項D：公鑰文件 public_key.pem 目前其他用戶也有的讀權限（r--）。公鑰可以公開，通常不需要限制世界讀權限
選項E： 父目錄的所有者已經是 root，所屬組是 root，這對系統目錄來說是正常的。更改父目錄的所有者和所屬組到 mysql 是不合理的
選項F：accounting 目錄其他用戶也具有讀和執行權限（r-x）。移除這些權限可以防止未授權用戶訪問該目錄中的內容，從而提高安全性

Q165.A MySQL server is monitored using MySQL Enterprise Monitor’s agentless installation.
Which three features are available with this installation method? (Choose three.)
A)MySQL Replication monitoring
B)network-related information and network characteristics
C)MySQL Query Analysis data
D)CPU utilization
E)security-related advisor warnings
F)operating system memory utilization
G)disk usage and disk characteristics including disk advisors warnings
Answer:ACE

選項A：無代理安裝方式可以監控 MySQL 復制狀態和性能，屬于基本監控功能。
選項B：無代理安裝方式主要監控 MySQL 實例本身，不涉及網絡相關信息
選項C：無代理安裝方式支持查詢分析功能，可收集和分析執行的 SQL 語句
選項D：無代理安裝方式不監控操作系統層面的 CPU 使用率
選項E：無代理安裝方式可以提供與安全性相關的建議和警告，幫助識別潛在安全問題
選項F：無代理安裝方式不監控操作系統層面的內存使用情況
選項G：無代理安裝方式不直接監控磁盤使用情況和磁盤特征

Q166.You have a MySQL system with 500 GB of data that needs frequent backups.
You use a mix of MylSAM and InnoDB storage engines for your data.
Examine your backup requirement:
? 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)Use the Clone Plugin to copy the data to another MySQL system.
C)Take a logical backup of the MySQL system.
D)Take your backup from a slave of the MySQL system.
Answer:D

選項A：物理備份通常需要短暫鎖定數據庫以確保備份一致性，這可能違反“系統不能不可用或對客戶端應用加鎖”的要求。
選項B：克隆插件創建數據庫副本，但需要復制整個數據，對于500GB的數據量較大，且未提及如何保證在恢復時只丟失1小時數據
選項C：邏輯備份不會鎖定數據庫，可滿足"零停機"要求。恢復時結合二進制日志可控制數據丟失在1小時內。但備份和恢復過程可能較慢，尤其對于大容量數據。
選項D：從從服務器備份不會影響主服務器的可用性，可滿足"零停機'要求。結合二進制日志，可確保恢復時數據丟失不超過1小時。

Q167.A colleague complains about slow response time on your website.
Examine this query and output:

mysql> show global status like 'Table_ lock%' ;
+------------------------------+---------+
IVariable_name | Value l 
+------------------------------+---------+
|Table_locks_immediate l 53148 | 
| Table_locks_waited |17716 |
+------------------------------+---------+
2 rows in set (0.00 3ec)

What is the most likely cause for the high number of lock waits?
A)You use the MyISAM storage engine for most common tables.
B)You use the InnoDB storage engine and statements wait while data is inserted.
C)The Innodb Buffer pool is full.
D)Your table accesses wait for the operating system level flush.
Answer:A

Table_locks_immediate：產生表級鎖定的次數，表示可以立即獲取鎖的查詢次數，每立即獲取鎖，值加1
Table_locks_waited：出現表級鎖定爭用而發生等待的次數，此值較高說明存在較嚴重的表級鎖爭用情況。

Q168.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? (Choose two.)
A)Use SET GLOBAL binlog_expire_logs_seconds= and restart the server.
B)Set binlog_expire_logs_seconds in my. cnf.
C)Set binlog_expire_logs_seconds = 0 in my. cnf and restart the server.
D)Use SET PERSIST binlog_expire_logs_seconds=.
E)Use PURGE BINARY LOGS to <binlog_ name>. .
F)Use SET GLOBAL binlog_expire_logs_seconds= and run the FLUSH BINARY LOGS command.
Answer:EF

根據題目需要緊急的減少日志使用的磁盤空間，所以在設置了binlog_expire_logs_seconds之后，需要立馬進行flush。或者直接使用purge binary logs進行清理

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

NDB和innodb提供了一致性視圖，這兩是事務型存儲引擎

Q170.Examine Joe’s account:
CREATE USER ‘joe’@‘%‘IDENTIFIED BY ’ secret ，
GRANT ALL PRIVILEGES ON*.* TO ‘joe’@’%’
All existing connections for joe are killed.
Which two commands will stop joe establishing access to the MySQL instance
A)Alter USER ‘joe’@‘%‘ACCOUNT LOCK
B)AITER USER ‘joe’@’%’ SET password=‘invalid ’
C)REVOKE All PRIVGESON ON . FROM ‘joe’@’%’
D)REVOKE USAGE on . FROM ’ joe’@‘%’
E)ALTER USER ‘joe’@‘%’ IDENTIFIED BY ‘invalid’ PASSWORD EXPIRE
F)AlTER USER ‘joe’@‘%’ PASSWORD HISTORY 0
Answer:AE