MySQL 8.0 OCP 1Z0-908认证考试题库（7-20）

Q7.
Examine the command, which execute successfully:
shell> mysqld --initialize-insecure
Which statement is true?
A)The installation creates a temporary test environment with data in the /tmp directory.
B)The installation is created without enforcing or generating SSL certificates.
C)The root password is created in the error log in plain text.
D)The root password is not created allowing easy access from the same host.
Answer:D

Q8.
Examine this command:
shell> mysqldump --no-create-info --all-databases --result-file=dump.sql
Which statement is true?
A)It will not write CREATE TABLESPACE statements.
B)It will not write CREATE LOGFILE GROUP statements.
C)It will not write CREATE TABLE statements.
D)It will not write CREATE DATABASE statements.
Answer:C

Q9.
The mysqld instance has the connection control plugin enabled with these settings: Connection_control_min_connection_delay=1000 ，默认1000微秒，1秒–
Connection_control_max_connection_delay=2000
The minimum and maximum delays need to be increased to 3000 and 5000, respectively. A command is executed:
Mysql> SET GLOBAL connection_control_min_connection_delay=3000;
What is the result?
A)The minimum connection value is changed to 2000.
B)Only the minimum connection value is increased to 3000.
C)The minimum value increases to 3000 and the maximum value increases to 4000.
D)An error is returned.
Answer:D
min不能设置成超过max。

Q10.
You must export data from a set of tables in the world_x database.
Examine this set of tables: Tables ( country, countryinfo, location)
Which two option will export data into one or more files?
A)mysql> CLONE LOCAL DATA DIRECTORY = ‘/var/lib/mysql/world_x/country’;
mysql> CLONE LOCAL DATA DIRECTORY = ‘/var/lib/mysql/world_x/countryinfo’;
mysql> CLONE LOCAL DATA DIRECTORY = ‘/var/lib/mysql/world_x/location’;
B)mysql>SELECT * INTO OUTFILE ‘/output/contry.txt’ FROM world_x.country;
mysql>SELECT * INTO OUTFILE ‘/output/contryinfo.txt’ FROM world_x.countryinfo;
mysql>SELECT * INTO OUTFILE ‘/output/location.txt’ FROM world_x.location;
C)shell> mysqlexport world_x country countryinfo location > mydump.sql
D)shell> mysql --batch world_x.country world_x.countryinfo world_x.location > mydump.sql
E)shell> mysqldump world_x country countryinfo location > mydump.sql
Answer:BE
只有mysqlimport没有mysqlexport工具

Q11.
Examine this command and output:

Mysql>SELECT *
FROM performance_schema.table_io_waits_summary_by_table WHERE COUNT_STAR >0\G
********************************2.row *************************************
OBJECT_TYPE:TABLE
OBJECT_SCHEMA:test
OBJECT_NAME:demo_test
COUNT_STAR:61567093
SUM_TIMER_WAIT:59009007572922
MIN_TIMER_WAIT:395922 AVG_TIMER_WAIT:958095 MAX_TIMER_WAIT:558852005358
COUNT_READ:38665056 SUM_TIMER_READ:20598719962188 MIN_TIMER_READ:395922 AVG_TIMER_READ:532728 MAX_TIMER_READ:558852005358
COUNT_WRITE:22902028 SUM_TIMER_WRITE:38410287610743 MIN_TIMER_WRITE:1130688 AVG_TIMER_WRITE:1677006 MAX_TIMER_WRITE:17205682920
COUNT_FETCH:38665056 SUM_TIMER_FETCH:20598719962188 MIN_TIMER_FETCH:395922 AVG_TIMER_FETCH:532728 MAX_TIMER_FETCH:558852005358
… COUNT_DELETE:22902028
SUM_TIMER_DELETE:38410287610743 MIN_TIMER_DELETE:1130688 AVG_TIMER_DELETE:1677006 MAX_TIMER_DELETE:17205682920
Which two are true?
A)The longest I/O wait was for writes.(从记录中看是读)
B)I/O distribution is approximately 50/50 read/write.（读比写多近一倍）
C)22902028 rows were deleted.
D)Average read times are approximately three times faster than writes. E)The I/O average time is 532728.
Answer:CD

Q12.
A developer accidentally dropped the InnoDB table Customers from the Company database. There is a datadir copy from two days ago in the /dbbackup directory.
Which set of steps would restore only the missing table?
A)Stop the MySQL Server process, and execute:
mysqlbackup --datadir=/var/lib/mysql --backup-dir=/dbbackup
–include-tables=‘Company.Customers’
copy-back Start the mysqld process.
B)Stop the MySQL Server process and restart it with the command:
mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysql
Run mysqldump on this table and restore the dump file.
C)Stop the MySQL Server process and restart it with the command:
mysqld --basedir=/usr/local/mysql --datadir=/dbbackup
Run mysqldump on this table and restore the dump file.
D)Stop the MySQL Server process, copy the Customers.ibd file from the dbbackup directory, and start the mysqld process.
Answer:C

A 不对在8.20中，使用全备集，mysqlbackup的Table-Level Recovery (TLR)也可以进行表恢复，但company表可能存在未提交的数据
For non-TTS backups only, these additional limitations apply:
After partial restores, tables could contain changes from uncommitted transactions.
而且这个题目可能是对8.20之前的版本

Q13.
There has been an accidental deletion of data in one of your MySQL databases.
You determine that all entries in the binary log file after position 1797 must be replayed. Examine this partial command:
mysqlbinlog binlog.000008 --start-position=1798
Which operation will complete the command?
A)–write-to-remote-server must be added to the command line to update the database tables.
B)It can be piped into the MySQL Server via the command-line client. |mysql
C)You must use --stop-position=1797 to avoid the DELETE statement that caused the initial problem.
D)No changes required. It automatically updates the MySQL Server with the data.
Answer:B

Q14.
Table t is an InnoDB table. Examine these statements and output:
Selet count(1) from t;
±-----------+
| count(1) |
±-----------+
| 72 |
±-----------+
Mysql show indexes from t\G
1.row
Table: t
Non_unique:0
Key_name:PRIMARY
Seq_in_index: 1
Column_name: a
Collation:A
Cardinality: 72
Sub_part:NULL
Packed:NULL
Null:
Index_type:STREE
Comment: Index_comment:
Visible:YES
Expression:NULL
2.row
Table: t
Non_unique:1
Key_name:b_idx
Seq_in_index: 1
Column_name: b
Collation:A
Cardinality: 1
Sub_part:NULL
Packed:NULL
Null:YES
Index_type:BTREE
Comment:
Index_comment:
Visible:NO – 不可见索引
Expression:NULL
2 row in set (0.00 sec)
Which two are true?
ANALYZE TABLE t would update index statistics uniquely for the PRIMARY index.
B)Table t has two viable indexes to be used for queries.
C)SELECT b from t would perform a table scan.
D)Index b_idx has a low number of unique values.
E)SELECT a FROM t would perform a table scan.
Answer:CD

Q15.
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
Answer:C

Q16.
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
B)Innodb_log_file_size=1G
C)Sync_binlog=0
D)Innodb_doublewrite=0
E)Buffer_pool_size=24G
F)Innodb_flush_log_at_trx_commit=1
Answer:B D
E不对，实际数据19g，buffer设置成20或24没有区别。

Q17.
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.
Answer:D

QRTi越低越慢

Q18.
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
Answer:D

Q19.
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’}}
Answer:DF

在mysqlshell中有这样的话：
The SSL mode of a cluster can only be set at the time of creation.
但在mysql的官方文档中并没有，因为8.0.27后的版本增加了用mysql自身通讯加密方式，因此并不需要重新创建集群，这里的答案是按旧的文档编写的。

Q20.
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.
Answer:BF

关于作者

姚远：Oracle ACE（Oracle和MySQL数据库方向），华为云MVP，曾任IBM公司数据库部门经理。拥有包括Oracle 10g和12c OCM在内的20多项数据库相关技术认证，数据库类畅销书《MySQL 8.0运维与优化》的作者。
需要更多OCP认证考试资料的请加我的微信