1z1-908 PDF Exam Material 2023 Realistic 1z1-908 Dumps Questions [Q44-Q67] : Free Exam Dumps Collection : http://free.examcollectionpass.com

Q44. Your MySQL server is running on the Microsoft Windows platform.
Which three local connection protocols are available to you? (Choose three.)

named pipes

shared memory

SOCKET

X Protocol

UDP

TCP/IP

Q45. A colleague complains about slow response time on your website.
Examine this query and output:

What is the most likely cause for the high number of lock waits?

You use the MyISAM storage engine for most common tables.

You use the InnoDB storage engine and statements wait while data is inserted.

The Innodb Buffer pool is full.

Your table accesses wait for the operating system level flush.

Q46. You are using an existing server with a new configuration. MySQL Server fails to start.
Examine this snapshot of the error log:

Which action would allow the server to start?

Remove ib_logfile0 and ib_logfile1 files from the file system.

Execute mysqladmin flush-logs.

First run mysqld –initialize to refresh the Size of ib_logfile.

Create a new ib_logfile0 file of size 26214400.

Q47. t is a non-empty InnoDB table.
Examine these statements, which are executed in one session:
BEGIN;
SELECT * FROM t FOR UPDATE;
Which is true?

If OPTIMIZE TABLE; is invoked, it will create a table lock on t and force a transaction rollback.

If OPTIMIZE LOCAL TABLE t; is invoked from another session, it executes normally and returns the status.

mysqlcheck –analyze –all-databases will execute normally on all tables and return a report.

If ANALYZE TABLE; is invoked from the same session, it hangs until the transaction is committed or rolled back.

Q48. Examine the modified output:

Seconds_Behind_ Master value is steadily growing. What are two possible causes? (Choose two.)

The master is most probably too busy to transmit data and the slave needs to wait for more data.

One or more large tables do not have primary keys.

This value shows only I/O latency and is not indicative of the size of the transaction queue.

The master is producing a large volume of events in parallel but the slave is processing them serially.

The parallel slave threads are experiencing lock contention.

Q49. You plan to take daily full backups, which include the ndbinfo and sys (internal) databases.
Which command will back up the databases in parallel?

mysqldump –single-transaction > full-backup-$(date +%Y%m%d).sql

mysqlpump –include-databases=% > full-backup-$(date +%Y%m$d).sql

mysqlpump –all-databases > full-backup-$(date +%Y%m%d).sql

mysqldump –all-databases > full_backup-$(date +%Y%m%d).sql

Q50. 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.

Q51. Examine this SQL statement:
mysql> GRANT r_read@localhost TO mark WITH ADMIN OPTION;
Which two are true? (Choose two.)

Mark can grant the privileges assigned to the r_read@localhost role to another user.

ADMIN OPTION causes the role to be activated by default.

Mark can grant the r_read@localhost role to another user.

Mark can revoke the r_read@localhost role from another role.

ADMIN OPTION allows Mark to drop the role.

Mark must connect from localhost to activate the r_read@localhost role.

Q52. Examine this command, which executes successfully:

Which two statements are true? (Choose two.)

A single-file backup is created.

The backup operation will finish only when backup-and-apply-log is executed.

The –backup-dir option holds temporary output, status, and metadata files.

The backup operation will finish only when apply-log is executed.

A raw backup is created.

Q53. Examine this command, which executes successfully:

Which statement is true?

Only files for MySQL or Its built-in storage engines are backed

Only non-encrypted files are backed up.

The backup includes only data files and their metadata.

Only InnoDB data and log files are backed up.

Only tables stored in their own tablespaces are backed up.

Q54. Which two statements are true about the binary log encryption feature? (Choose two.)

It encrypts any connecting slaves connection thread.

It can be set at run time.

It requires a keyring plugin.

When enabled it encrypts existing binary logs.

It can be activated per session.

Q55. 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?

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

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

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

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

Q56. 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.

Tables are scanned and index statistics recalculated when an instance is restarted.

Q57. Examine this output:

Which change should optimize the number of buffer pool instances for this workload?

Increase the number of buffer pool instances to 16.

Increase the number of buffer pool instances to 32.

Decrease the number of buffer pool instances to 1.

Increase the number of buffer pool instances to 12.

Decrease the number of buffer pool instances to 4.

Q58. Examine these commands and output:

Which connection ID is holding the metadata lock?

Q59. Which two statements are true about MySQL Enterprise Backup? (Choose two.)

It supports backing up only table structures.

It can perform hot or warm backups.

It creates logical backups.

It supports the creation of incremental backups.

It supports backup of a remote MySQL system.

It supports restoring to a remote MySQL system.

Q60. Which two are true about binary logs used in asynchronous replication? (Choose two.)

They are pushed from the master to the slave.

They contain events that describe database changes on the master.

They contain events that describe all queries run on the master.

They contain events that describe only administrative commands run on the master.

They are pulled from the master to the slave.

Q61. An attempt to recover an InnoDB Cluster fails.
Examine this set of messages and responses:
host3:3377 ssl JS > dba.rebootClusterFromCompleteOutage() Reconfiguring the default cluster from complete 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.rebootClusterFromCompleteOutage: The active session instance isn’t the most updated in comparison with the ONLINE instances of the Cluster’s metadat a. Please use the most up to date instance: ‘host1:3377’. (RuntimeError) Which statement is true?

The instance deployed on host3 must be rebuilt with a backup from the primary instance.

The cluster is running and there is at least one ONLINE instance.

The instance deployed on host3 must be synchronized from a donor deployed on host1 by using the command cluster.addInstance(‘host1:3377’).

It is possible to determine the most up-to-date instance by comparing different global transaction identifier (GTID) sets with GTID_SUBSET(set1,set2).

The active session instance is invalid and must be re-created by using the command shell.connect (‘host3:3377’).

Q62. Which two are use cases of MySQL asynchronous replication? (Choose two.)

You can scale writes by creating a replicated mesh.

It guarantees near real-time replication between a master and a slave.

You can scale reads by adding multiple slaves.

MySQL Enterprise Backup will automatically back up from an available slave.

It allows backup to be done on the slave without impacting the master.

Q63. Which two commands will display indexes on the partstable in the manufacturing schema? (Choose two.)

DESCRIBE manufacturing.parts;

SELECT * FROM information_schema.statistics WHERE table_schema=’manufacturing’
AND TABLE_NAME=’parts’;

SHOW INDEXES FROM manufacturing.parts;

SELECT * FROM information_schema.COLUMN_STATISTICS;

EXPLAIN SELECT INDEXES FROM manufacturing.parts;

Q64. Which two statements are true about using MySQL Enterprise Monitor Query Analyzer? (Choose two.)

The single query QRTi pie chart in the Query Analyzer view is based on the average execution of all statements.

It is possible to retrieve a normalized statement, but never the exact statement that was executed.

It is possible to configure the Query Analysis built-in advisor to get notified about slow query execution.

It is possible to list and analyze statements in an arbitrary graph range selection from timeseries graphs.

It is possible to import data into the Query Analyzer from heterogeneous sources, such as CSV.

Q65. Which three are characteristics of a newly created role? (Choose three.)

It can be dropped using the DROP ROLEstatement.

It is stored in the mysql.roletable.

It is created as a locked account.

It can be renamed using the RENAME ROLEstatement.

It can be granted to user accounts.

It can be protected with a password.

Q66. Examine this command, which executes successfully on InnoDB Cluster: dba.dropMetadataSchema() Which two statements are true? (Choose two.)

Group Replication will be dissolved and all metadata purged.

Group Replication is still operational, but InnoDB Cluster must be reimported under MySQL Shell.

The mysql_innodb_cluster_metadata schema is dropped from the instance where the connection was established.

The command drops the mysql_innodb_cluster_metadata schema and re-creates it.

The mysql_innodb_cluster_metadata schema is dropped from all reachable members of the cluster.

Connections driven by MySQL Router are not affected by the command.

Q67. You are upgrading a MySQL instance to the latest 8.0 version.
Examine this output:

You plan to add this parameter to the configuration: innodb_directories=’/innodb_extras’ Which statement is true?

It defines all innodb tablespace options relative to a starting parent directory.

It is not necessary because innodb_data_home_dir is already defined.

It allows scanning of other locations to discover more innodb tablespaces.

It moves all innodb tablespaces to the /innodb_extras directory to enable a new innodb_data_home_dir to be defined.

It adds more temporary workspace in addition to the innodb_tmpdir location.