MySQL 8.0 OCP 1Z0-908 81-90题

Q81.The replication for master ad slave MySQL Server is up and running .The disk space occupied by the binary log
files continues to grow.
Which two methods manage this issue?
A)Execute the FLUSH LOGS statement.
B)Delete all binary log files manually on the file system to release storage space.
C)Execute the PURGE BINARY LOGS statement.
D)On the master server,disable binary logging by removing the --log-bin option
E)Set the binlog_expire_logs_seconds variable.
Answer:CE

选项A:flush logs会新建一个binlog，不会进行清理
选项B:手动删除，如果有延迟，主从复制就会挂掉
选项C:可以根据指定的选项选择性的清理一部分日志
选项D:禁用binlog，主从复制就挂掉了
选项E：设置过期时间，可以清理掉日志

Q82.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:
Mysqldump --delete-master-logs --all-databases>/backup/db_backup.sql
Which two are true?
A)All databases are backed up to the output file.（不会备份 sys、information_schema、performace_schema）
B)All details regarding deleted logs and master metadata are captured in the output file.（不会记录 deleted logs）
C)All binary logs are backed up and then deleted.（不会备份 binlog）
D)All databases, excluding master metadata, are backed up to the output file.
E)All binary logs are deleted from the master.（会生成一个新的 binlog 使用）
F)All non-active binary logs are removed from the master.
Answer:DF

–delete-master-logs 在master上备份后删除其二进制日志。该选项会自动激活—

master-data选项。
选项A：mysqldump不会备份sys,information_schema、performace_schema这几个系统库
选项B：已删除的日志不会记录在输出文件中
选项C：mysqldump不会备份binlog
选项D：--all-databases就是备份所有数据
选项E：会生成一个新的 binlog 使用
选项F：不活跃的，个人理解只要不是当前正在写入的binlog都会被清理

Q83.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 relay logs from the master should be used to replace the corrupted relay logs.
B)The slave relay logs should be deleted; then execute START SLAVE;
C)The slave needs to be restored from backup.
D)The slave relay logs should be deleted; execute CHANGE MASTER to adjust the replication relay log file name, then
issue START SLAVE;
Answer:D

基于position的复制，可以将从库损坏的relay log删掉，然后使用change master to调增到相应的relay log文件名，然后启动复制
个人想法：其实可以修改参数，直接启动，会自动从主节点拉去binlog，重新生成relay log.
具体可以参考：https://blog.csdn.net/weixin_34580939/article/details/113208484
D选项：change master to relay_log_file可以删除其他的，通过清除损坏的relay log并重新配置复制，确保从服务器能够从主服务器重新获取数据，恢复复制的正常运行。

Q84.How can mysqld_multi be configured to allow MySQL instances to use the same port number?
A)The instances have appropriate net masks set.
B)The instances use different user accounts unique to each instance.
C)The instances use different socket names.
D)The instances listen on different IP addresses. //因为题干上要允许多实例配置为相同的端口，所以实例需要监听在不同的IP上
Answer:D

Q85.Which three are requirements for a secure MySQL Server environment?
A)Encrypt the file system to avoid needing exact file-system permissions.
B)Minimize the number of non-MySQL Server-related processes running on the server host.
C)Restrict the number of OS users that have access at the OS level.
D)Run MySQL server as the root user to prevent incorrect sudo settings.
E)Ensure appropriate file system privileges for OS users and groups.
F)Keep the entire software stack on the OS host.
Answer:BCE

选项A：对文件系统加密，跟权限没什么关系
选项B：减少非MySQL服务相关的程序运行在MySQL服务主机上，这样可以减少其他程序对MySQL服务运行的影响
选项C：限制登录OS系统的用户，这个也可以防止其他用户误操作之类的，对于MySQL服务环境的安全也是有益的
选项D：用root用户运行MySQL服务，权限太大了，容易导致误操作或其他问题
选项E：确保用户和组有合适的权限，不要给太大或者太小，对服务运行环境安全有益
选项F：整个软件栈都在这个主机上，不同的软件互相可能会有一定的影响

Q86.What does the slave I/O thread do?
A)monitors and schedules I/O calls to the subsystem for the relay logs
B)connects to the master and requests 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)reads the relay log and executes the events contained in them
Answer:B

IO线程的作用是连接到主节点，并且请求主节点更新记录的binlog

Q87.The languages table uses MyISAM and the countries table uses the InnoDB storage engine.
Both tables are empty.
Examine these statements:
BEGIN;
INSERT INTO languages(lang) VALUES (“Italian”);
INSERT INTO countries(country) VALUES(“Italy”);
ROLLBACK;
What is the content of both tables after executing these statements?
A)countries has one row, languages has none.
B)Both tables have one row.
C)Both tables are empty.
D)languages has one row, coutries has none.
Answer:D

MyISAM是非事务引擎，所以不具备回滚的能力，所以languages表会有一行数据，而innodb是事务型存储引擎，所以回滚后countries表为空

Q88.Which two MySQL Server accounts are locked by default?
A)any new ROLE accounts
B)any user created without a password
C)any internal system accounts
D)any user created with a username, but missing the host name
E)any user set as DEFINER for stored programs
Answer:AC

新建的role角色账户，和MySQL的内置的系统账号默认都是锁定的，可以通过如下SQL查看：
SELECT user, host, account_locked FROM mysql.user;

Q89.After installing MySQL 8.0 on Oracle Linux 7, you initialize the data directory with the mysqld --initialize
command.
Which two will assist in locating the root password?
A)the root_pw variable stored in the mysql.install table
B)the root password displayed on the screen via a [Warning] message
C)the root password inserted in the error log set by the --log-error=[file_name] variable
D)the root password written to the /root/.my.cnf file
E)as root, executing the SHOW PASSWORD command by using the SHA-256 password encryption plugin
Answer:BC
如下图所示，MySQL初始化时会将密码打印在warning消息中，如果在初始化的时候指定了–log-error选项，那么密码将会输出在该参数指定的文件中

Q90.You have appropriate privileges and are about to shut down a running MySQL server process on Oracle Linux
7.
Which three are valid methods that will shut down the MySQL server?
A)mysqld_safe -S /tmp/mysql.sock SHUTDOWN
B)kill mysqld_safe
C)mysqladmin shutdown
D)mysql -S /tmp/mysql.sock --shutdown
E)mysqld_safe --shutdown
F)systemctl stop mysqld
G)mysql> SHUTDOWN;
Answer:CFG

mysqld_safe启动：相当于多了一个守护进程,mysqld挂了会自动把mysqld进程拉起
选项D：使用mysql -S连接数据库，需要加上-u -p或者没有密码的时候也可以默认连接上，但是应该加上-e 'shutdown'执行关闭操作