MySQL 8.0 OCP 1Z0-908 31-40题

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

参考：MySQL :: MySQL 8.0 Reference Manual :: 17.13 InnoDB Data-at-Rest Encryption
To alter the encryption of an existing file-per-table tablespace, an ENCRYPTION clause must be specified.

要修改一个已存在的按表存储（file-per-table）表空间的加密方式，必须指定一个加密子句（ENCRYPTION clause）。

A)ALTER TABLE t1 ENCRYPTION='Y';
B)ALTER TABLE t1 WITH ENCRYPTION USING MASTER KEY;
C)ALTER TABLE t1 SET TDE = 'ON';
D)ALTER TABLE t1 ADD ENCRYPTED_TABLESPACE = 'Y';
Answer:A
 

Q32.Examine these statements and output:
Mysql>GRANT PROXY ON accounting@locahost TO ‘’@’%’;
Mysql>SELECT USER(),CURRENT_USER(),@@PROXY_USER;
+-------------------+---------------------+----------------+
|USER() |CURRENT_USER() |@@proxy_user|
+-------------------+---------------------+----------------+
|jsmith@loaclhost | accounting@localhost |’’@’% |
+-------------------+---------------------+----------------+
select user()//返回登录的用户信息，username@'登录数据库的IP，也就是客户端的IP'，本题目是从localhost登录
select current_user() //返回的是授权账户，也就是从mysql.user表中查到的用户信息
select @@proxy_user //返回的是代理用户信息
Which statement is true?
A)The user failed to define a username and the connecting username defaulted to ' '@'%'. //连接用户是jsmith@loaclhost，所以不是没定义，也不是使用默认的代理用户' '@'%'去链接
B)The user is authorized as the rsmith@localhost user. //输出中没有提到 rsmith@localhost。
C)The user is authenticated as the anonymous proxy user ' '@'%'. //虽然代理用户是 ' '@'%'，但实际登录用户是 jsmith@localhost，不是匿名用户。
D)The user is authorized as the accounting@localhost user. //CURRENT_USER() 显示用户被授权为 accounting@localhost。
E)The user is logged in with --user=accounting as an option. 登录用户是jsmith@locahost
Answer:D

Q33.Which are three benefits of using mysqlbackup instead of mysqldump?
A)mysqlbackup restores data from physical backups, which are faster than logical backups. //mysqlbackup 物理备份直接备份的是文件，所以恢复起来更快
B)mysqlbackup can back up tables with the InnoDB engine without blocking reducing wait times due to contention.//mysqlbackup 支持热备份，可以在不阻塞写操作的情况下备份 InnoDB 表
C)mysqlbackup allows logical backups with concurrency resulting in faster backups and restores.//物理备份，所以并发不是mysqlbackup的优势
D)mysqlbackup does not back up MySQL system tables, which shortens backup time. //物理备份，全部都备
E)mysqlbackup can perform partial backup of stored programs. //物理备份，备份的时候是全备拷贝，所以不是mysqlbackup的优势
F)mysqlbackup integrates tape backup and has the virtual tape option.  //mysqlbackup 集成了磁带备份，并具有虚拟磁带选项正确的
Answer:ABF

Q34.Which two statements are true about the mysqld-auto.cnf file?
A)This file is for storing MySQL Server configuration options in JSON format. //该文件以json格式存储
B)This file is for logging purposes only and is never processed. //错误，该文件用来加载这些持久化的参数
C)This file is for storing MySQL server_uuid values only.
D)It is read and processed at the beginning of startup configuration.
E)It is read and processed at the end of startup configuration. //读取顺序为my.nf，完事儿在读取该文件
F)It is always updated with changes to system variables.//SET PERSIST 和 SET PERSIST_ONLY 才会 update 它
Answer:AE

一般设置参数，会在my.cnf,set或者set global进行参数设置，但是这部分参数要么在会话中生效，要么需要重启生效
但是使用SET PERSIST(会在内存中修改变量的值并且写入mysqld-auto.cnf文件)或者SET PERSIST_ONLY(只写入mysqld-auto.cnf文件不修改内存中的变量)，并且重启后也生效

参考：MySQL :: MySQL 8.0 Reference Manual :: 7.1.9.3 Persisted System Variables

 Q35.Examine this command, which executes successfully:
$mysqlbackup --user=dba --password --port=3306 --with-timestamp --only-know-file-types
--backup-dir=/exprot/backups backup
Which statement is true?
A)Only tables stored in their own tablespaces are backed up.
B)Only non-encrypted files are backed up.
C)The backup includes only data files and their metadata.
D)Only InnoDB data and log files are backed up.
E)Only files for MySQL or its built-in storage engines are backed up.
Answer:E

参考：MySQL :: MySQL Enterprise Backup 8.0 User's Guide :: 20.8 Partial Backup and Restore Options
--only-known-file-types：

这是一个用于备份操作的选项。当指定此选项时，mysqlbackup 仅会备份那些已知的、与 MySQL 或其内置存储引擎相关的数据文件类型。
默认情况下：在没有指定 --only-known-file-types 选项时，mysqlbackup 会将服务器数据目录下的所有数据库子目录中的文件都包含在备份中。这意味着备份会包含各种文件，不仅仅是数据文件，还包括可能的临时文件、日志文件等。

 Q36.Examine this statement and output:
Myswl> SHOW GRANTS FOR jsmith;
+------------------------------------------------------------------------------+
|Grants for jsmith@% |
+------------------------------------------------------------------------------+
|GRANT USAGE ON *.* TO jsmith’@’%’ |
|GRANT UPDATE(Name)ON ‘world’. ‘country’TO ‘jsmith ‘@’%’|.
+-------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Which two SQL statement can jsmith execute?
A)UPDATE world.country SET Name='one' LIMIT 1;
B)UPDATE world.country SET Name='new' WHERE Name='old'; (where 需要 select 权限)
C)UPDATE world.country SET Name='first' ORDER BY Name LIMIT 1;(order by 需要 select 权限)
D)UPDATE world.country SET Name=CONCAT('New ', Name); (concat 函数中的 name 列同样需要 select 权限)
E)UPDATE world.country SET Name='all';
Answer:AE

Q37.You have semi-synchronous replication configured and working with one slave. rpl_semi_sync_master_timeout
has never been reached.
You find that the disk system on the master has failed and as a result, the data on the master is completely
unrecoverable.
Which two statements are true?
A)Reads from the slave can return outdated data for some time, until it applies all transactions from its relay log.//因为主库提交后，从库的relay log的回放是在从库异步进行的，只要接收到了会自主的进行回放，所以主库磁盘挂掉后，从库这部分事务可能还没回放完成，所以会有时读取到过时的数据
B)A small amount of committed transactions may be lost in case they were committed just before the disk failure.
//个人理解，既然是磁盘故障前提交的事务，从库binlog和relay log已经写入了，此时这部分事务可以从从库上进行恢复，除非从库再写这部分事务的时候也出现了故障，所以该答案不太理解，有明白的小伙伴麻烦帮忙解答下如何理解这个答案
C)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. //故障发生后，应用程序可以立即(因为可能存在复制延迟，所以这个是错误的)从从服务器读取数据，并依赖它返回完整且最新的数据集
D)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. //需要另外的插件进行重新选主
E)Reads from the slave can return outdated data until the value of the rpl_semi_sync_master_timeout variable is
reached.  //rpl_semi_sync_master_timeout 是主服务器上的超时设置，决定主服务器在切换到异步模式前等待从服务器确认的时间，这个时间跟数据如何返回无关，从库读取的数据是否过时，跟relay log的回放速度有关。

数据更新大概的过程：
连接器，分析器，优化器负责，客户端的连接信息，权限等信息的验证，在对SQL进行分析，分析其语法/此法是否正确，然后进入执行器
执行器过程：
    1.写undo log，存储回滚段指针和事务ID：通过回滚段指针找到对应的undo记录，通过事务ID判断事务可见性。undolog可以回滚/崩溃恢复/MVCC
    2.记录所在的目标页存在于内存中
        2.1  唯一索引：找到数据并判断数据是否冲突，然后更新内存
                唯一索引：将数据从磁盘读入内存，判断是否冲突，然后更新
        2.2  普通索引：找到数据并更新内存
                在change buffer中更新记录，change buffer会异步的将更新同步到磁盘，change buffer是为了降低IO的次数
    3.写入redo log(prepare)
    4.写入binlog 
    5.提交事务(commit)
    6.刷redo log到磁盘 处于commit-prepare阶段
    7.刷binlog到磁盘 处于commit-commit阶段
两阶段提交
    先写redo log(prepare)
    写入binlog
    写入redolog(commit)

Q38.Which two are true about binary logs used in asynchronous replication?
A)They are pulled from the master to the slave.
B)They are pushed from the master to the slave.
C)They contain events that describe all queries run on the master.
D)They contain events that describe only administrative commands run on the master.
E)They contain events that describe database changes on the master.
Answer：AE

这个binlog 是从库主动从主库拉去的

异步复制：主库提交不受从库影响
同步复制:主库提交前需要确保所有从库都接收到binlog并写入relay log
半同步：主库提交前需要确保至少一个从库接受到binlog并写入relay log

Q39. Which three actions will secure a MySQL server from network-based attacks?
A) Allow connections from the application server only. //设置某些IP，所以是从网络层的
B) Use network file system(NFS) for storing data.
C) Construct a perimeter network to allow public traffic. //构建一个周边网络以允许公共流量，所以也是网络层
D) Use MysQL Router to proxy connections to the MySQL server. //从服务层
E) Change the listening port to 3307. //从服务层
F) Place the MySQL instance behind a firewall.  //防火墙是网络层
Answer:ACF

Q40.Which three sets of item information are visible in the mysql system database?
A)performance monitoring information //在performance_schema库中
B)plugins //在mysql库中
C)audit log events  //默认是在mysql 数据库中，具体再那个位置取决于配置。
D)rollback segments //INFORMATION_SCHEMA.FILES 表
E)information about table structures //在informance_schem库中
F)time zone information and definitions //在mysql库中
G)help topics //在mysql库中