MySQL 5.6 Database Administrator (1z0-883) Free Practice Test

Question 1

Consider the events_% tables in performance Schema.
Which two methods will clear or reset the collected events in the tables?

A. Using TRUNCATE statements, for example, TRUNCATE TABLE performance_schema.events_waits_current;

B. Restarting Mysql

C. Disabling and re-enabling all instruments

D. Using DELETE statements, for example, DELETE FROM performance_schema.events_watis_current;

E. Using the statement FLUSH PERFORMANCE CACHE;

F. Using the statement RESET PERFORMANCE CACHE;

Correct Answer: A,C

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 2

You attempt to connect to a Mysql Server by using the mysql program. However, you receive the following notice:
ERROR 2059 (HY000): Authentication plugin 'mysql_clear_password' connot be loaded: plugin not enabled
What would you run to fix the issue?

A. The install plugin command for the mysql_cleartext_password plugin

B. The mysql_secure_installation script to update server security settings

C. The mysql_upgrade script

D. The mysql client with the - enable-cleartext-plugin option

E. The mysql client with the - ignore-password-hashing option

Correct Answer: D

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 3

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
$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?

A. The local root user must log in with a blank password initially: mysql -u root -p.

B. The RPM installation script sets a default password of password for new installations.

C. The mysql_install_bd post-installation script used - random-password.

D. New security measures mean that the mysql_secure_installation script must be run first on all new installations.

Correct Answer: A

Question 4

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?

A. The average resultset of a query is increasing due to an increase in the number of users requiring SQL statement execution.

B. Query_cache_min_res_unit has been exceeded, leading to an increased performance overhead due to additional memory block lookups.

C. The Query Cache is pruning queries due to an increased number of requests.

D. Mutex contention on the Query Cache is forcing the queries to take longer due to its single-threaded nature.

Correct Answer: D

Question 5

Consider the following:
Mysql> EXPLAIN SELECT * FROM City WHERE Name = 'Jacksonville' AND CountryCode = 'USA' \G ******************************** 1. row ******************************** Id: 1 Select_type: SIMPLE Table: City Type: ref Possible_keys: name_country_index Key: name_country_index Ref: const, const Rows: 1 Extra: Using where
Which statement best describes the meaning of the value for the key_len column?

A. It shows the total size of the index row.

B. It shows the number of characters indexed in the key.

C. It shows how many columns in the index are examined.

D. It shows how many bytes will be used from each index row.

Correct Answer: D

Question 6

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?

A. 3; only the top-level statements are logged.

B. 111; top-level statements and all lower-level statements are logged.

C. 102; top-level statements are logged, but LOAD DATA INFILE is logged as a separate event.

D. 12; only top-level statements and stored procedure events are logged.

Correct Answer: A

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 7

An employee cannot access the company database. You check the connection variables:
Mysql> SHOW GLOBAL VARIABLES LIKE '%connect%';

8 rows in set (0.00 sec)
A look at the user privileges shows:
GRANT... TO 'bob'@'%, example.com' WITH MAX_USER_CONNECTIONS 0; GRANT... TO 'key'@'%, example.com' WITH MAX_USER_CONNECTIONS 1; GRANT... TO 'joe'@'%, example.com' WITH MAX_USER_CONNECTIONS 50; What is a valid explanation for why one of the users is unable to connect to the database?

A. Connect_timeout is too small to allow a connection to occur.

B. Joe has exceeded the max_user_connections global limit.

C. Bob has max_user_connections set to zero, which blocks all his connections

D. Kay is already connected elsewhere and attempting to log in again.

E. All users are blocked because max_user_connections is accumulated over the host account information.

Correct Answer: D

Question 8

A user has deleted the wrong row in a table and you are preparing a point-in-time recovery skipping the DELETE event.
The server is configured with:

You have identified that the DELETE statement to skip has the Global Transaction Identifier (GTID) 'dbbe7da-fe25-11e2-b6c7-0800274aa49e:5 and you replay the binary log with:
Mysqlbinlog - exclude-gtides=' dbbe07da-fe25-11e2-b6c7-0800274aa49e:5' binlog.00000.2 | mysql
However all events were skipped instead of just the one deleting the wrong row.
What is the reason for this?

A. Enforce_gtid_consistency is set to ON.

B. Mysqlbinlog ignores arguments to - exclude-gtids-it means ignore all events with GTIDs.

C. Gtid_mode must be set to AUTO during point in time recoveries.

D. The server keeps track of which GTIDs have already been executed and skips those.

Correct Answer: D

Question 9

Which two options describe how MySQL Server allocates memory?

A. Global memory resources are allocated at server startup.

B. Each thread allocates memory from a global pool.

C. Thread memory is pre-allocated up to thread_cache_size for performance.

D. Each connection may have its own per-thread memory allocations.

Correct Answer: A,D

Question 10

Which two are true regarding MySQL binary and text backups?

A. Text backups are human-readable while binary backups are not.

B. Binary backups are usually slower than text backups.

C. Binary backups are not portable across different operating systems.

D. Binary backups are usually faster than text backups.

Correct Answer: A,C

Welcome to TestSimulate

Oracle MySQL 5.6 Database Administrator (1z0-883) Free Practice Test