Q111.You recently upgraded your MySQL installation to MySQL 8.0
Examine this client error:
ERROR 2059 (HY000):
Which option will allow this client to connect to MySQL Server ?
A)[mysqld]
Default_authentication_plugin=caching sha2_password
B)AITER USER user IDENTIFIED WITH mysql_ native_ password BY ‘password’ ;
C)AITER USER user IDENTIFIED WITH caching_ sha2_password BY ‘password’ ;
D)[mysqld] Default_authentication_plugin=sha256_password;
E)ALTER USER user IDENTIFIED WITH sha256_ password BY 'password;
F)[mysqld] Default_authentication_plugin=mysql_native_password
Answer：B
1.升级到8.0之后，默认的验证插件由原来的mysql_native_password 变成了caching_sha2_password ，所以如果要使用之前的密码验证，那么就要将密码修改为mysql_native_password 验证
2.遇到题目的报错这通常意味着客户端库或者连接器不兼容或者没有正确配置以支持这个插件。所以只能使用B，先将密码插件修改会原来支持的mysql_native_password 验证插件
Q112.Which three methods are part of a ‘scale up’ approach to capacity planning?
A)adding additional MySQL servers to the existing host
B)adding more CPU power
C)adding a replication slave
D)adding more RAM
E)adding more storage to your disk array
F)sharding the server into a parallel server farm
G)adding a new node to InnoDB Cluster
Answer:BDE
选项A：在已存在MySQL服务的节点上新增实例无法达到该要求
选项C：添加复制从节点，无法对实例容量进行扩容
选项F：这道选项没看懂啥意思
选项G：给innodb cluster添加节点和选项C一样，无法达到扩容的目的
容量扩展计划，CPU,MEM,磁盘都算，所以BDE是正确选项
Q113.You planned an upgrade of your MySQL Server from version 5.7 to version 8.
You created a full backup and successfully tested the upgrade process on a test server.
You then upgraded the production environment successfully.
Soon after the upgrade, the application team reported a problem and asked you to roll back the upgrade.
Which statement is true?
A) You must downgrade the data dictionary using the mysqlfrm utility.
B) You can easily switch between using MySQL 5.7 and MySQL 8 binaries after upgrading, because
both sets of metadata are maintained.
C) You must set --skip-networking and run mysqld --dd-downgrade to prepare for rollback.
D) You must restore to your backup created in MySQL 5.7.
Answer:D
选项A：mysqlfrm是用来读取frm文件并从该文件中找出表的定义，无法达到回滚到5.7的版本的目的
选项B：两个版本之间有一些数据的字符集等有不同的变化，所以无法直接使用binlg达到降级的目的
选项C:如果开启了 skip-networking配置查询为on，那么你的mysql远程连接不了，你的mysql状态可能是正常的但是你的监听端口是没有的,这个选项可以暂停客户端连接，这个答案中的--dd-downgrade没有这个选项？我使用mysqld --verbose --help没找到这个选项
选项D：升级出现问题回滚，由于有备份就可以直接使用备份恢复一个出来，所以选D
Q114.You want to install and configure MySQL on Linux server with tarball binaries in the /app/mysq1/directory,
where the bin directory is found at /app/mysql/bin and the data directory at /app/data.
Which two parameters are required to configure the MySQL instance?
A)The configuration basedir=/ app/mysql is needed.
B)The configuration datadir=/app/data is needed.
C)The configuration log-bin=/app/data is needed.
D)The configuration datadir=/ app/mysql/data is needed
E)The configuration innodb_log_ group home dir=/datadir is needed.
F)The configuration basedir=/app/mysql/bin is needed.
Answer:AB
初始化MySQL服务需要datadir和basedir，由于bin路径需要在/app/mysql/bin下，所以basedir=/app/mysql
Q115.MySQL programs look for option files in standard locations.
Which method will show the option files and the order in which they are read?
A)mysql> SHOW GLOBAL VARIABLES;
B)shell> mysql --print-defaults
C)shell> mysqladmin --debug
D)shell> mysqld --help --verbose
Answer:D
Q116.You must run multiple instances of MySQL Server on a single host.
Which three methods are supported?
A)Use system tools to lock each instance to its own CPU.
B)Use resource groups to lock different instances on separate CPUs.
C)Run mysq1d with --datadir defined for each instance.
D)Run MySQL Server docker containers.
E)Start mysqld or mysq1d_ safe using different option files for each instance.
F)Use systemd with different settings for each instance.
Answer:CEF
在同一台主机上运行多实例，需要设置不同的datadir,启动时使用不同的选项文件，也可以为每一个实例配置不同的systemd
Q117.An attempt to recover an InnoDB Cluster fails.
Examine this set of messages and responses:
host3:3377 ss1 JS > dba. rebootClusterFromCompleteOutage ()
Reconfiguring the defau1t cluster from comp1ete outage. ·
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. rebootClusterEromCompleteOutage: 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 cluster is running and there is at least one ONLINE instance.
B)The instance deployed on host3 must be synchronized from a donor deployed on host1 by using the
command c1uster.addInstance ( ’ host1 :3377’).
C)It is possible to determine the most up-to-date instance by comparing different global transaction identifier
(GTID) sets with GTID_ SUBSET (set1, set2);
D)The active session instanceis invalid and must be re-created by using the command shell.connect
( ‘host3:3377’) .
E)The instance deployed on host3 must be rebuilt with a backup from the primary instance.
Answer:C
选项A：因为使用了 dba.rebootClusterFromCompleteOutage()，这意味着所有实例都处于离线状态。
选项B：c1uster.addInstance是往集群中添加一个实例，而不是从其他节点同步数据过来，因为实例之前已经是集群的一部分。
选项C：可以通过比较不同的全局事务标识符（GTID）集合，使用 GTID_SUBSET(set1, set2) 来确定最新的实例
选项D：错误信息指出活动会话实例不是最新的，但并未说明会话无效。可以切换到正确的实例（如 host1:3377）继续操作。
选项E：这种情况下，建议使用 host1:3377 作为同步源来恢复其他实例，而不是从备份重建。
Q118.Examine this partial report:
Mysql> SHOW FULL PROCESSLIST
±-------±------------------------±---------------------±------
| Id | User | Host |…
±-------±------------------------±---------------------±------
|4 | event_scheduler | localhost |…
|9 | root | localhost:51502 |…
|10 | root | localhost:51670 |…
Examine this query:
SELECT SUM (m.CURRENT_NUMBER_OF_BYTES_USED)AS TOTAL
FROM performance_schema.memory_summary_by_thread_by_event_name m
INNER JOIN performance_schema.threads t
ON m.THREAD_ID = t.THREAD_ID
WHERE t. PROCESSLIST_ID = 10;
What information does this query provide?
A)total memory used by connection number 10
B)total memory used across all connections associated with the user on connection number 10
C)total memory used by the first 10 threads
D)total memory used by thread number 10
E)total memory used across all connections associated with the user on thread number 10
F)total memory used by the first 10 connections
Answer:A
题目中的SQL查询的是processlist_id=10对应线程当前使用的并且还未释放的内存大小
A和C选项：这里的是processlist_id=10，所以是连接标识符是10，所以A正确，C错误
D选项其中连接的ID=10，但是thread_id并不等于10，所以D错误，
B和E选项：memory_summary_by_thread_by_event_name表会为每一个连接进来的线程插入一行数据，该数据为该线程的内存使用统计信息他也不是统计的所有的连接使用的内存统计信息，所以B和E错误
F：选项
可参考：https://dev.mysql.com/doc/refman/8.0/en/performance-schema-memory-summary-tables.html
Q119.Which three settings control global buffers shared by all threads on a MySQL server?
A)tmp_table_size
B)innodb_buffer_poo1_size
C)table_open_cache
D)sort_buffer_size
E)key_buffer_size
Answer:BCE
选项A：定义了在使用memory存储引擎创建的临时表的最大的大小，这是每个会话（线程）私有的缓冲区，用于存储临时表数据。每个线程有自己的 tmp_table_size 缓冲区，而不是全局共享的
选项B：缓冲池的大小（以字节为单位），缓冲池是 InnoDB 缓存表和索引数据的内存区域,这是 InnoDB 存储引擎的核心缓冲区，用于缓存表数据和索引
选项C：所有线程的开启表的数量。增加这个值会增加mysqld所需的文件描述符数量。这个参数控制表文件句柄的缓存，允许 MySQL 重用打开的表文件句柄。它是一个全局资源，所有线程共享这个缓存来访问表文件
选项D：这个缓冲区用于排序操作，每个线程在需要排序时会分配自己的排序缓冲区。它是每个线程私有的，而不是全局共享的
选项E：这是 MyISAM 存储引擎的索引缓冲区，用于缓存 MyISAM 表的索引块。它是全局共享的，所有线程都会使用这个缓冲区来访问 MyISAM 表的索引。
Q120.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 --all-databases > full-backup-$ (date +%Y%m%d) .sql
B)mysqlpump --include-databases=%> full-backup-$ (date +%Y%m%d) .sql
C)mysqlpump --all-databases > full-backup-$ (date +%Y%m%d) .sql
D)mysqldump --single-transaction > ful1_backup-$ (date +%Y%m%d) .sql
Answer:B
选项A和选项C：mysqldump和mysqlpump默认不会导出系统库
选项B：mysqldump --include-databases=PATTERN [其他选项] 此处的PATTERN可以使用通配符，%匹配任意字符，_匹配单个字符
选项D：--single-transaction参数，会在FLUSH TABLES WITH READ LOCK 后添加START TRANSACTION 语句，用来开启 单一事务 ， 这个时候的加锁，仅仅是为了确定master-data中的binlog的具体位置和开启事务，开启事务后，就已经把读锁释放了
参考：https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html