MySQL 8.0 OCP 1Z0-908 151-160题

Q151.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 the AES_ENCRYPT () MySQL function on the option file.
B)Use mysql_secure_installation to encrypt stored login credentials.
C)Use a text editor to create a new defaults file and encrypt it from Linux prompt.
D)Use mysql_config_editor to create an encrypted file.
Answer:D

mysql_config_editor实用程序使您能够将身份验证凭据存储在名为.mylogin.cnf的登录路径文件中。

Q152.You are backing up raw InnoDB files by using mysqlbackup.
Which two groups of files will be backed up during a full backup? (Choose two.)
A)ibbackup files
B).CSM files
C).sdi files
D). ibd files
E)ib_logfile files
1.3.2 Files Backed up for InnoDB Data
The InnoDB-related data files that are backed up include the ibdata* files (which represent the system tablespace
and possibly the data for some user tables), any .ibd files(which contains data from user tables created with the file-per-table setting enabled), and the data extracted from the ib _logfile* files (the redo log information representing
changes that occur while the backup is running), which is stored in a new backup file ibbackup_logfile.
Answer:DE

备份与InnoDB 相关的数据文件包括 ibdata* 文件（这些文件表示系统表空间，也可能包含一些用户表的数据）、任何 .ibd 文件（这些文件包含启用了文件每表设置时创建的用户表的数据），以及从 ib_logfile* 文件（表示备份运行期间发生的更改的重做日志信息）中提取的数据，这些数据被存储在一个新的备份文件 ibbackup_logfile 中。

Q153.Examine this statement and output:
myaql> SELECT ROW_NUMBER() OVER() AS Qn,
query, exeo_,count, avg latency,lock_latency
FROM sys.statement_analysis
ORDER BY exec_count;

+---+-------------------------------------------------------+---------+---------+----------+
|QN |query |exec_count | avg_latency | lock_latency
|1 |SELECT SUM ( ‘k' ) FROM 'mysch ... (} * INTERVAL ? SQL _TSI _HOUR| 381268 |31.44 ms |1.01 m |
|2 |SELECT 'id' ,’val’,’a’ ,’b..."updated' WHERE ' created' <? | 150317 |358.34 us |30.06 s |
|3 |SELECT "emp_nof ,'val', ‘cre ... ated’ + INTERVAL ?SQL_TSI_DAY |600 | 523.32 ms |120.24 ms |
|4 | SELECT 'a',’b’,’c’ EROM 'm...?AND?CR ’k’ BETWEEN ?AND? |200 | 10.32 s | 40.19 ms |
|5 |SELECT ‘a’,’b’ FROM 'myschem ... @ ( "emp_no" ) WHERE 'val’ =? |1 |21.03s |274.00 us |
+---+-------------------------------------------------------+---------+---------+----------+

You must try to reduce query execution time.
Which two queries should you focus on? (Choose two.)
A)QN= 3
B)QN= 5
C)QN= 1
D)QN=4
E)QN=2
Answer:CD

列名	描述
query	归一化的SQL语句字符串。
db	语句的默认数据库，如果没有则为NULL。
full_scan	该语句执行时进行的全表扫描次数。
exec_count	该语句执行的总次数。
err_count	该语句执行时产生的错误总数。
warn_count	该语句执行时产生的警告总数。
total_latency	该语句执行的总等待时间。
max_latency	该语句单次执行的最大等待时间。
avg_latency	该语句每次执行的平均等待时间。
lock_latency	该语句执行时等待锁的总时间。
cpu_latency	当前线程在CPU上花费的时间。
rows_sent	该语句返回的总行数。
rows_sent_avg	该语句每次执行返回的平均行数。
rows_examined	该语句从存储引擎读取的总行数。
rows_examined_avg	该语句每次执行从存储引擎读取的平均行数。
rows_affected	该语句影响的总行数。
rows_affected_avg	该语句每次执行影响的平均行数。
tmp_tables	该语句创建的内部内存临时表总数。
tmp_disk_tables	该语句创建的内部磁盘临时表总数。
rows_sorted	该语句排序的总行数。
sort_merge_passes	该语句进行的排序合并次数。
max_controlled_memory	该语句使用的最大受控内存（字节）。<br>（MySQL 8.0.31中新增）
max_total_memory	该语句使用的最大总内存（字节）。<br>（MySQL 8.0.31中新增）
digest	语句的摘要。
first_seen	首次看到该语句的时间。
last_seen	最近一次看到该语句的时间。

该题可以参考：https://blog.csdn.net/lukeUnique/article/details/130165047

Q154.You plan to take daily full backups, which include the ndbinfo and sys (internal) databases. Which command
will back up the databases in parallel?
A)mysqldump --single-transaction > full-backup-$ (date +%Y%m%d) .sql
B)mysqlpump – include-databases=% > full-backup-$(date+$d) .sql
C)mysqlpump --all-databases > full-backup-$(date+D)mysqldump--all-databases>ful{l}_{b}ackup-$(date +%Y%m%d) .sql
Answer:B

-- 仅备份以aa结尾的数据库
mysqlpump --include-databases=%aa

Q155.Which two commands will display indexes on the parts table in the manufacturing schema? (Choose two.)
A)DESCRIBE manufacturing.parts;
B)SELECT * FROM information_schema.statistics WHERE table_schema=’ manufacturing’ AND TABLE_NAME=’
parts’ ;
C)SHOW INDEXES FROM manufacturing.parts;
D)SELECT * FROM information_schema.COLUMN_STATISTICS;
E)EXPLAIN SELECT INDEXES FROM manufacturing. parts ;
Answer:BC

选项A：相当于DESC 表名，只打印表结构信息，以及那个字段上有索引，不会显示详细的索引信息
选项B：比如表名，索引名称，以及是第几个字段是索引的第几位等等这些信息
选项C：该命令可以直接查询索引信息
选项D：COLUMN_STATISTICS该表只有表名，schema_name，列名，HISTOGRAM信息
选项E：查看执行计划

Q156.Pre-production testing has revealed that your client programs and libraries are currently incompatible
with a staging environment upgrade to MysQL 8.0.
You decide to downgrade to MySQL 5.7 to work on your code.
Which two methods will achieve this?
A) Reinstall the 5.7 binaries.Execute the 5.7’s version of mysql_upgrade with the–force option.
B) Reinstall the 5.7 binaries and reinitialize --datadir.Restore the 5.7 physical backup, which you
took before upgrading to 8.0.
C) Reinstall the 5.7 binaries and reinitialize --datadir.Restore a physical backup of your 8.0
tables.
D) Reinstall the 5.7 binaries and reinitialize --datadir.Restore a logical backup of your 8.0 non-system tables and use a sequence of CREATE USER and GRANT commands to re-create the user
accounts.
E) Reinstall the 5.7 binaries. Execute mysqlcheck --repair.
Answer:BD

降版本：1.重新初始化一个实例，并使用之前的备份进行恢复
		2.使用逻辑备份，物理备份在每个版本中恢复后，元数据不太一样，所以会有些问题，恢复完成后，账户什么的可以重新创建

Q157.Which two are use cases of MySQL asynchronous replication? (Choose two.)
A)You can scale writes by creating a replicated mesh.
B)It guarantees near real-time replication between a master and a slave
C)You can scale reads by adding multiple slaves.
D)MySQL Enterprise Backup will automatically back up from an available slave.
E)It allows backup to be done on the slave without impacting the master.
Answer:CE

选项A：异步复制不能扩展主节点
选项B：主从复制延迟这个保证不了，至少有一个从节点接收到并写入到relay log后，才允许主节点事务提交，最多就是保证了主节点有的数据，必须传送到了从节点
选项C：可以添加多个读从节点，正确
选项D：这个不太了解，个人理解，再那个节点上备份可以自己配置
选项E：在从节点备份不影响主节点，这个是对的

Q158.Examine this statement and output:

Which two are true?
A. The plan contains a full table scan of the city table.
B. There is a problem with the statement reported as a warning.
C. The output suggests adding an index on the countrycode column.
D. 33.33% of rows in the country table will be accessed.
E. The plan contains a full table scan of the country table.
F. It is estimated that 33.33% of rows in the country table match the WHERE clause.
Answer:BE

可以看到country表中的key=NULL，所以该表没有走索引，是全表扫描，city表是走的countrycode字段的索引
选项A：错误
选项B：查看输出是有一个WARNING，不懂为啥会放一个这个答案
选项C：应该建议在coutry表的code字段加索引，所以错误
选项D：filter表示返回结果的行数占需读取行数的百分比，值越大越好，说明百分比高，查询到的数据准确，值小的话，百分比小，说明查询的数据量大，结果集少。Filtered列的值依赖于统计信息。所以不是全表的33.33%需要被访问
选项E:正确
选项F：据估计，coutry表中有 33.33% 的行符合 WHERE 子句的条件，fileter指的是读取的行数，占扫描行数，population不知道是否有索引，索引这个返回的结果行数，占扫描行数为百分之33.33，而占全表的百分之多少就不知道了，所以该选项错误

Q159.Which step or set of steps can be used to rotate the error log?
A)Execute SET GLOBAL log error = ‘’.
B)Execute SET GLOBAL max error count = .
C)Execute SET GLOBAL expire_logs_days=0 to enforce a log rotation.
D)Rename the error log file on disk, and then execute FLUSH ERROR LOGS.
Answer:D

在MySQL中无法直接轮转日志，需要手动在系统层面操作，完成后在数据库中flush一下，重新创建一个新的日志文件

Q160.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/lib/mysql/shop/
   Which two actions are required to complete the restore? (Choose two.)
   A)shell> cp /backup/ customers.sdi /var/ tmp
   B)shell>cp /backup/ customers.sdi /var/lib/ mysql/ shop/
   C)mysql>SOURCE ’ /var/ tmp/ customers. sdi ’
   D)mysql> IMPORT TABLE FROM /var/tmp/customers.sdi
   E)shell> cp /backup/ customers.frm /var/lib/mysql/shop/
   F)mysql> IMPORT TABLE FROM /var/l ib/mysql/ shop/ customers. Sdi
   G)mysql>ALTER TABLE shop. customers IMPORT TABLESPACE
   H)mysql> ALTER TABLE shop. customers DISCARD TABLES PACE
   Answer:AD

因为是myiasm引擎，所以需要再把sdi文件拷贝回来，其中存储这元数据
在执行imoort table from  sdi

具体参考：https://zhuanlan.zhihu.com/p/7752228264