1z0-883: MySQL 5.6 Database Administrator - Free download from Exam Files

Which query would you use to find connections that are in the same state for longer than 180 seconds?

SHOW FULL PROCESSLIST WHEER Time > 180;
SELECT * FROM INFORMATION_SCHEMA.EVENTS SHERE STARTS < (DATE_SUB (NOW ( ), INTERVAL 180 SECOND) );
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE STATE < (DATE_SUB (NOW ( ), INTERVAL 180 SECOND) );
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 180;

Correct answer: D

Explanation:

The show processlist cannot use where clause. mysql> show full processlist where time>0; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where time>0' at line 1

The show processlist cannot use where clause.

mysql> show full processlist where time>0;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where time>0' at line 1

A database exists as a read-intensive server that is operating with query_cachek_type = DEMAND.

The database is refreshed periodically, but the resultset size of the queries does not fluctuate.

Note the following details about this environment:

A web application uses a limited set of queries.
The Query Cache hit rate is high.
All resultsets fit into the Query Cache.
All queries are configured to use the Query Cache successfully.

The response times for queries have recently started to increase. The cause for this has correctly been identified as the increase in the number of concurrent users accessing the web service.

Based solely on the information provided, what is the most likely cause for this slowdown at the database level?

The Query Cache is pruning queries due to an increased number of requests.
Query_cache_min_res_unit has been exceeded, leading to an increased performance overhead due to additional memory block lookups.
Mutex contention on the Query Cache is forcing the queries to take longer due to its single-threaded nature.
The average resultset of a query is increasing due to an increase in the number of users requiring SQL statement execution.

Correct answer: C

You have a login-path named “adamlocal” that was created by using the mysql_config_editor command.

You need to check what is defined for this login_path to ensure that it is correct for you deployment.

You execute this command:

$ mysql_config_editor print –login-path=adamlocal

What is the expected output of this command?

The command prints all parameters for the login-path. The password is printed in plain text.
The command prints all parameters for the login-path. The password is shown only when you provide the –password option.
The command prints all parameter for the login-path. The password is replaced with stars.
The command prints the encrypted entry for the login-path. The is only possible to see if an entry exists.

Correct answer: C

Which two statements are true about InnoDB auto-increment locking?

The auto-increment lock can be a table-level lock.
InnoDB never uses table-level locks.
Some settings for innodb_autoinc_lock_mode can help reduce locking.
InnoDB always protects auto-increment updates with a table-level lock.
InnoDB does not use locks to enforce auto-increment uniqueness.

Correct answer: AD

Explanation:

A (not B): InnoDB uses a special lock called the table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns.D (Not E): This lock is normally held to the end of the statement (not to the end of the transaction), to ensure that auto-increment numbers are assigned in a predictable and repeatable order for a given sequence of INSERT statements.Reference: 14.6.5.2 Configurable InnoDB Auto-Increment Lockinghttp://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-configurable.html

A (not B): InnoDB uses a special lock called the table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns.

D (Not E): This lock is normally held to the end of the statement (not to the end of the transaction), to ensure that auto-increment numbers are assigned in a predictable and repeatable order for a given sequence of INSERT statements.

Reference: 14.6.5.2 Configurable InnoDB Auto-Increment Locking

http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-configurable.html

Consider the Mysql Enterprise Audit plugin.

A CSV file called data.csv has 100 rows of data.

The stored procedure prepare_db ( ) has 10 auditable statements.

You run the following statements in the mydb database:

Mysql> CALL prepare_db ( );

Mysql> LOAD DATA INFILE ‘/tmp/data.cav’ INTO TABLE mytable;

Mysql> SHOW TABLES;

How many events are added to the audit log as a result of the preceding statements?

102; top-level statements are logged, but LOAD DATA INFILE is logged as a separate event.
3; only the top-level statements are logged.
111; top-level statements and all lower-level statements are logged.
12; only top-level statements and stored procedure events are logged.

Correct answer: B

Explanation:

Reference: http://dev.mysql.com/doc/mysql-security-excerpt/5.5/en/audit-log-plugin-logging-control.html

You execute the following statement in a Microsoft Windows environment. There are no conflicts in the path name definitions.

C: \> mysqld – install Mysql56 – defaults – file = C : \my –opts.cnf

What is the expected outcome?

Mysqld acts as an MSI installer and installs the Mysql 5.6 version, with the c: \my-opts.cnf configuration file.
Mysql is installed as the Windows service name Mysql56, and uses c: \my-opts.cnf as the configuration file
An error message is issued because – install is not a valid option for mysqld.
A running Mysql 5.6 installation has its runtime configuration updated with the server variables set in c: \my-opts.cnf.

Correct answer: B

You install a copy of Mysql 5.6.13 on a brand new Linux server by using RPM packages. The server starts successfully as verified by the following commands:

$ pidof mysqld

3132

$tail - n2 /var/lib.mysql/hostname.err

2013-08-18 08:18:38 3132 [Note] /usr/sbin/mysqld: ready for connections.

Version: ‘5.6.13-enterprise-commercial-advaced’ socket: ‘/tmp/mysql.sock’ port;

3306 Mysql Enterprise Server – Advanced Edition (Commercial)

You attempt to log in as the root user with the following command:

$mysql –u root

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

Which statement is true about this scenario?

The RPM installation script sets a default password of password for new installations.
The local root user must log in with a blank password initially: mysql –u root –p.
New security measures mean that the mysql_secure_installation script must be run first on all new installations.
The mysql_install_bd post-installation script used – random-password.

Correct answer: B

A Mysql Server has been running an existing application successfully for six months.

The my.cnf is adjusted to contain the following additional configuration:

[mysqld]

Default-authentication-plugin=sha256_password

The Mysql Server is restarted without error.

What effect will the new configuration have in existing accounts?

They will have their passwords updated on start-up to sha256_password format.
They will have to change their password the next time they login to the server.
They are not affected by this configuration change.
They all connect via the secure sha256_password algorithm without any configuration change.

Correct answer: D

Explanation:

Reference: http://dev.mysql.com/doc/refman/5.6/en/sha256-authentication-plugin.html

In a design situation, there are multiple character sets that can properly encode your data.

Which three should influence your choice of character set?

Disk usage when storing data
Syntax when writing queries involving JOINS
Comparing the encoded data with similar columns on other tables
Memory usage when working with the data
Character set mapping index hash size

Correct answer: CDE

Consider the query:

Mysql> SET @run = 15;

Mysql> EXPLAIN SELECT objective, stage, COUNT (stage)

→FROM iteminformation

→WHERE run=@run AND objective=’7.1’

→GROUP BY objective,stage

→ORDER BY stage;

The iteminformation table has the following indexes;

Mysql> SHOW INDEXES FROM iteminformation:

This query is run several times in an application with different values in the WHERE clause in a growing data set.

What is the primary improvement that can be made for this scenario?

Execute the run_2 index because it has caused a conflict in the choice of key for this query.
Drop the run_2 index because it has caused a conflict in the choice of key for this query.
Do not pass a user variable in the WHERE clause because it limits the ability of the optimizer to use indexes.
Add an index on the objective column so that is can be used in both the WHERE and GROUP BY operations.
Add a composite index on (run,objective,stage) to allow the query to fully utilize an index.

Correct answer: B

Vendor:	Oracle
Exam Code:	1z0-883
Exam Name:	MySQL 5.6 Database Administrator
Date:	Oct 25, 2018
File Size:	353 KB

Download MySQL 5.6 Database Administrator.1z0-883.PracticeTest.2018-10-25.52q.vcex

How to open VCEX files?

Demo Questions

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

ProfExam at a 20% markdown