MySQL 8.0 Database Administration Exam 1Z0-908 Exam Questions

Preparing for the 1Z0-908 exam is simple with CertsGate. We offer easy-to-understand study materials that help you learn the most important exam topics. You can study using our PDF questions, practice online with a real exam-style test, or use the desktop practice software. Choose the study method that works best for you and prepare at your own pace.

At CertsGate, we keep our 1Z0-908 practice questions up to date. Whenever the exam syllabus or objectives change, we update our study materials so you always learn the latest topics. This helps you save time, avoid outdated content, and feel more confident when you take your exam.

Question #1 (Topic: Demo Questions)

Which three are types of information stored in the MySQL data dictionary? (Choose three.)

performance metrics

InnoDB buffer pool LRU management data

access control lists

view definitions

server runtime configuration

server configuration rollback

stored procedure definitions

Correct Answer: A, D, E

Question #2 (Topic: Demo Questions)

Your MySQL instance is capturing a huge amount of financial transactions every day in the finance database.

Company policy is to create a backup every day.

The main tables being updated are prefixed with transactions-.

These tables are archived into tables that are prefixed with archives- each month. mysqlbackup --optimistic-busy-tables= " ^finance\.transactions-.* " backup

Which optimization process best describes what happens with the redo logs?

The redo logs are backed up first, then the transaction and archive tables.

The redo logs are backed up only if there are changes showing for the transactions tables.

The redo logs are not backed up at all.

The archive tables are backed up first, then the transaction tables and redo logs.

E. The transaction tables are backed up first, then the archive tables and redo logs.

Correct Answer: D

Explanation:

Optimistic phase: In this first phase, tables that are unlikely to be modified during the backup process (referred to as the “inactive tables” below, identified by the user with the optimistic-time option or, by exclusion, with the optimistic-busy-tables option) are backed up without locking the MySQL instance. And because those tables are not expected to be changed before the backup is finished, redo logs, undo logs, and system table spaces are not backed up by mysqlbackup in this phase.

Question #3 (Topic: Demo Questions)

Examine this query and its output:

Which two statements are true? (Choose two.)

The root user had the largest number of modified rows for a SELECT statement.

User bob had the largest total time waiting for locks.

The root user had the largest single wait time.

The app user had the highest total number of rows read from storage engines.

User bob had a significantly higher ratio of SELECT + INSERT statements to QUIT than both app and root users.

Correct Answer: D, E

Question #4 (Topic: Demo Questions)

MySQL is installed on a Linux server with this configuration:

Which method sets the default authentication to SHA-256 hashing for authenticating user account passwords?

Set validate-user-plugins=caching_sha2_password in the configuration file.

Define CREATE USER ' ' @ ' % ' IDENTIFIED WITH sha256_password in the MySQL instance.

Add default_authentication_plugin=mysql_native_password in the configuration file.

Add default_authentication_plugin=sha256_password in the configuration file.

Correct Answer: D

Question #5 (Topic: Demo Questions)

Which statement is true about InnoDB persistent index statistics?

Updating index statistics is an I/O expensive operation.

Index statistics are calculated from pages buffered in the buffer pool for tables with InnoDB storage engine.

Setting innodb_stats_auto_recalc=ON causes statistics to be updated automatically when a new index is created.

Execution plans based on transient index statistics improve precision when innodb_stats_persistent_sample_pages is increased.

Increasing innodb_stats_persistent_sample_pages determines higher pages scanning speed, at the cost of increased memory usage.

Correct Answer: A

Explanation:

https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html

Index statistics are used by the optimizer to estimate the cost of different query execution plans and choose the most efficient one. InnoDB supports two types of index statistics: persistent and transient. Persistent statistics are stored on disk in the mysql.innodb_table_stats and mysql.innodb_index_stats tables, and are updated either automatically or manually. Transient statistics are stored in memory and are recalculated on server restart or table access 1 .

Updating index statistics involves sampling a number of pages from the index and estimating the cardinality and distribution of the values in the indexed columns. This process requires reading pages from disk, which can be I/O intensive, especially for large indexes or tables with many indexes. The number of pages sampled can be controlled by the innodb_stats_persistent_sample_pages variable, which defaults to 20 2 . Increasing this value can improve the accuracy of the statistics, but also increase the I/O cost.

Page: 1 / 1