MYSQL 8.0 OCP

各位好，今天分享mysql 8.0 ocp 后续试题。

本人会一直根据考试题目更新OCP原题，有需要的朋友，可以留言。

由于制作不易，有偿分享哈！

1、You are using the InnoDB engine and the innodb_file_per_table option is set. You delete a significant number of rows of a large table named FACTORY.INVENTORY.
Which command will reorganize the physical storage of table data and associated index data for the INVENTORY table, in order to reduce storage space and improve I/O efficiency?

A、CHECK TABLE FACTORY.INVENTORY

B、ANALYZE TABLE FACTORY.INVENTORY

C、OPTIMIZE TABLE FACTORY.INVENTORY

D、mysqlcheck -u root -p FACTORY.INVENTORY

E、mysqldump -u root -p FACTORY INVENTORY

本题选C。

2、The data in this instance transient;no backup or replication will be required.It is currently under performing.
The database size is static and including indexes is 19G
Total system memory is 32G
After profiling the system,you highlight these MySQL status and global variables:
Com_rollback                   |85408355 |
Com_commit                   |1234342  |
Innodb_buffer_pool_pages_free  |163840   |
[mysqld]
Buffer_pool_size=20G
Innodb_flush_log_at_trx_commit=2
Disable-log-bin
The OS metrics indicate that disk is a bottleneck.
Other variables retain their default values.
Which two changes will provide the most benefit to the instance? 

A、Max_connections=10000
      Innodb_log_file_size=1G

B、Sync_binlog=0

C、Innodb_doublewrite=0 

D、Buffer_pool_size=24G

E、Innodb_flush_log_at_trx_commit=1

本题选CD。

3、You are having performance issues with MySQL instances. 
Those servers are monitored with MySQL Enterprise Monitor.
Using Query Analyzer, where do you begin to look for problem queries?

A、Look for queries with big prolonged spikes in row activity/access graph in the times series graph.

B、Sort the "Exec" column and check for SQL queries with high Query Response Time index (QRTi) values.

C、Look for queries with low total latency times in the Latency section in the time series graph.

D、Sort the "Exec" column and check for SQL queries with low Query Response Time index (QRTi) values.

本题选D。

4、Examine the full path name of the backup image from MySQL Enterprise Backup with the --compress option:
/backup/full/mybackup/myimage.img
mysqlbackup.cnf contains this data:
[mysqlbackup]
backup-dir=/backup/full/myrestore
backup-image=/backup/full/mybackup/myimage.img uncompress
You must perform a database restore to a new machine.
which command can provision the new database in datadir as /data/MEB?

A、mysqlbackup  --defaults-file=mysqlbackup.cnf  --datadir=/data/MEB  restore-and-apply-log

B、mysqlbackup  --defaults-file=mysqlbackup.cnf  --datadir=/data/MEB  image-to-dir-and-apply-log

C、mysqlbackup  --defaults-file=mysqlbackup.cnf  --datadir=/data/MEB  apply-log-and-copy-back

D、mysqlbackup  --defaults-file=mysqlbackup.cnf  --datadir=/data/MEB  copy-back-and-apply-log

E、mysqlbackup  --defaults-file=mysqlbackup.cnf  --datadir=/data/MEB  image-to-dir

本题选D。

5、You want to check the values of the sort_buffer_size session variables of all existing connections. Which performance_schema table can you query?

A、global_variables

B、session_variables

C、variables_by_thread

D、user_variables_by_thread

本题选C。

6、Your my.cnf file contains these settings: 
[mysqld]
log_output=FILE 
slow_query_log 
long_query_time=2.01 
log_queries_not_using_indexes
You want to log queries that looked at a minimum of 5000 records and either took longer than 5 seconds to run or did not use indexes.
Which contains all the settings that you need to add to or modify the slow log configuration?

A、log_throttle_queries_not_using_indexes=5

B、long_query_time=5 log_throttle_queries_not_using_indexes=5

C、long_query_time=5

D、long_query_time=5 log_throttle_quries_not_using_indexes=5 min_examined_row_limit=5000

E、long_query_time=5 min_examined_row_limit=5000

F、min_examined_row_limit=5000

G、log_throttle_quries_not_using_indexes=5 min_examined_row_limit=5000

本题选E。

7、All mysql server instances belonging to innodb cluster have SSL configured and enabled. You must configure innodb cluster to use SSL for group communication.
Which two statements are ture?

A、Configuring SSL group communication also configures SSL distributed recovery.

B、SSL group communication requires the use of an additional set of parameters group_replication_recovery_*.

C、If only some innoDB cluster members are enabled for SSL group communication, and
--ssl-mode=PREFERED, communication will fail back to unencrypted connection. 

D、An existing innoDB cluster must be dissolved and created form scratch to enable SSL for group communication.

E、SSL group communication can be enabled for an existing cluster,one instance at time,
by setting group_replication_ssl_mode. 

F、SSL group communication must be enable at cluster creation time 
by pecifying createCluster{{membersslmode:’REQUIRED’}}

本题选DF。

8、Which two are true about differences between logical and physical upgrades of MySQL databases?

A、Post-upgrade table storage requirements after physical upgrades are usually smaller than that after logical upgrades.

B、Physical upgrades leave data in place, whereas logical upgrades require data to be restored from mysqldump-type backups taken before the upgrades.

C、Physical upgrades are much faster because they do not require restarting the mysqld process.

D、Physical upgrades are performed for current instances on bare metal deployments, whereas logical upgrades are used for virtual machines or containerized instances.

E、Logical upgrades are much faster because they do not require starting the mysqld process.

F、Post-upgrade table storage requirements after logical upgrades are usually smaller than that after physical upgrades.

本题选BF。

9、An existing asynchronous replication setup is running MySQL 8. 
Which two steps are a part of implementing GTID replication?

A、On the slave, alter the MySQL master connection setting with: 
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;

B、Execute this on the slave to enable GTID: 
RESET SLAVE; START SLAVE GTID_NEXT = AUTOMATIC;

C、Enable GTID by executing this on the master and the slave: 
SET GLOBAL GTID_ENABLED = on;

D、Restart MySQL (master and slave) with these options enabled:
--gtid_mode=ON
--log-bin
--log-slave-updates
--enforce-gtid-consistency

E、On the slave, alter the MySQL master connection setting with: 
ALTER channel CHANGE MASTER TO MASTER_AUTO_POSITION = 1;

F、Execute this on the slave to enable GTID: START SLAVE IO_THREAD WITH GTID;

本题选AD。

10、Examine this statement:
mysql> DROP ROLE r_role1, r_role2; 
Which two are true?

A、It fails if at least one of the roles does not exist.

B、You must revoke all privileges from r_role1 and r_role2 before dropping the roles.

C、Existing connections can continue to use the roles' privileges until they reconnect.

D、You must revoke r_role1 and r_role2 from all users and other roles before dropping the roles.

E、It fails if you do not have the ADMIN OPTION of the roles r_role1 and r_role2.

F、It fails if any of the roles is specified in the mandatory_roles variable. 

本题选AF。

今日共分享10题，后续会在下一次空余时间更新。