1z0-883 MySQL 5.6 Database Administrator Questions and Answers

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.

By enabling the Slow Query Log because all queries that are not using indexes will be logged automatically

By enabling the Error Log because not using indexes is an error

By enabling the Slow Query Log and using the – log-queries-not-using-indexes option

By enabling the Error Log and using the – log-queries-not-using-indexes option

Starting of the I/O thread

Execution of the FLUSH LOGS statement

Starting of the SQL thread

Reaching the slave_pendign _jobs_size_max limit

Execution of FULSH TABLES WITH READ LOCK

Allowing a client to kill other client connections

Allowing a client to shut down the server

Allowing change of the server runtime configuration

Allowing client accounts to take over the account of another user

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.

It prompts you to set the root user account password.

It checks whether file permissions are appropriate within datadir.

It asks to remove the test database, which is generated at installation time.

It can delete any anonymous accounts.

It verifies that all users are configuration with the longer password hash.

Tables with BLOB and TEXT columns cannot be partitioned.

Partitioning allows easier management of smaller data sets for certain queries.

Partitioning allows different columns to be stored in separate files.

The partitioning expression is an integer or function that returns an integer value or NULL value.

Partitioning is only available for those storage engines that implements it natively.

More memory per thread is beneficial in all scenarios.

It causes increased overhead due to initial memory allocation.

It can affect system stability during peak load times, due to swapping.

It requires increasing the thread_cache_size variable.

Include-master-info

Master-binlog

Include-log-file

Master-data

Disconnecting idle localhost client sessions

Implementing storage engine data compression options

Relocating your data and log files to separate storage devices

Activating the MySQL Enterprise thread pool plugin

STOP SLAVE; CHANGE MASTER TO MASTER_DELAY=0; START SLAVE;

STOP SLAVE; CHANGE MASTER TO MASTER_DELAY =0, RELAY_LOG_FILE = ‘hostname-relay-bin.00004’ , RELAY_LOG_POS = 1383;

STOP SLAVE; CHANGE MASTER TO RELAY_LOG_FILE = ‘hostname-relay-bin.00004’, RELAY_LOG_POS = 1383;

STOP SLAVE; SET GLOBAL master_delay =0; START SLAVE;

Each extra column in the copy with more columns must not have a default value.

Columns that are common to both versions of the table must be defined in the same order on the master and the slave.

The slave database cannot have more columns than the master. Only the master database can have more columns.

Columns that are common to both versions of the table must come first in the table definition, before any additional columns are additional columns are defined on either server.

The master database cannot have more columns than the slave. Only the slave deatbase can have more columns.