1Z0-883 Online Practice Questions and Answers

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?

A. Execute the run_2 index because it has caused a conflict in the choice of key for this query.

B. Drop the run_2 index because it has caused a conflict in the choice of key for this query.

C. Do not pass a user variable in the WHERE clause because it limits the ability of the optimizer to use indexes.

D. Add an index on the objective column so that is can be used in both the WHERE and GROUP BY operations.

E. Add a composite index on (run,objective,stage) to allow the query to fully utilize an index.

Consider the Mysql Enterprise Audit plugin.

Which statement is true when you identify a connection event that has used external authentication?

A. The attribute "STATUS" is set to the string EXTERNAL_AUTH.

B. The attribute "PRIV_USER" contains the username.

C. The event type that is given in the attribute "NAME" is EXTERNAL_AUTH.

D. There is no differentiation between native and external authentication events.

E. External authentication is managed through external auditing logs.

F. The "PROXY_PRIV" user shows a username if external authentication is used.

You want to start monitoring statistics on the distribution of storage engines that are being used and the

average sizes of tables in the various databases.

Some details are as follows:

The Mysql instance has 400 databases.

Each database on an average consists of 25-50 tables.

You use the query:

SELECT TABLE_SCHEMA, ‘ENGINE’, COUNT (*), SUM (data_length) total_size

FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ GROUP BY TABLE_SCHEMA, ‘ENGINE’ ;

Why is this query slow to execute?

A. Counting and summarizing all table pages in the InnoDB shared tablespace is time consuming.

B. Collecting information requires various disk-level operations and is time consuming.

C. Aggregating details from various storage engine caches for the final output is time consuming.

D. Collecting information requires large numbers of locks on various INFORMATION_SCHEMA tables.

You are using CTIDS in replication. You need to skip a transaction with the CTID of aaa-bbb- ccc-ddd-eee : 3 on a slave.

Which command would you execute from a Mysql prompt?

A. STOP SLAVE; SETGTID_NEXT="aaa-bbb-ccc-ddd-eee: 3"; BEGIN; COMMIT; SET GTID_NEXT="AUTOMATIC"; START SLAVE

B. STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

C. STOP SLAVE; BEGIN; SET GTID_IGNORE="aaa-bbb-ccc-ddd-eee: 3"; COMMIT; START SLAVE;

D. STOP SLAVE;

RESET SLAVE;

BEGIN;

SKIP NEXT GTID;

COMMIT;

START SLAVE;

You want to shutdown a running Mysql Server cleanly.

Which three commands that are valid on either Windows or Linux will achieve this?

A. Shell> pkill u mysql mysqld_safe

B. Shell> service mysql safe_exit

C. Shell> /etc/init.d/mysql stop

D. Shell> mysqladmin u root p shutdown

E. Mysql> STOP PROCESS mysqld;

F. Shell> net stop mysql

G. Shell> nmc mysql shutdown

A MySQL replication slave is set up as follows: User all InnoDB tables Receives ROW-based binary logs Has the read-only option

The replication slave has been found in an error state.

You check the MySQL error log file and find the following entries:

2013-08-27 13:55:44 9056 [ERROR] Slave SQL: Could not execute Write_rows event on table test.tl; Duplicate entry `3' for key'PRIMARY' , Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log 56_master-bin.000003, end_log_pas 653, Error_code: 1062 2013-08-27 13:55:44 9056 [Warning] Salve: Duplicate entry `3' for key `PRIMARY' Error_code: 1062 2013-08-27 13:55:44 9056 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START", We stopped at log `56_master- bin.000003' position

What are two possible causes for this error to occur?

A. The slave was created with mysqldump u root p skip-lock-table--all-databases > /data/data.sql

B. The slave user does have INSERT, UPDATE, or DELETE permission and cannot execute the write_rows function.

C. For tables with UNIQUE keys, statement-based replication must be used maintain integrity.

D. The root user on the slave has executed FLUSH LOGS, causing the relay-log to doublewrite.

E. The applications have the SUPER privilege, which allows them to update rows.

Which two options describe how MySQL Server allocates memory?

A. Each thread allocates memory from a global pool.

B. Global memory resources are allocated at server startup.

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

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

MySQL is installed on a Linux server and has the following configuration:

[mysqld]

User=mysql

Datadir=/data/mysql

As the `root' user, change the datadir location by executing:

Shell> cp R /var/lib/mysql/data/mysql/

Shell> chown R mysql /data/mysql/

What is the purpose of changing ownership of datadir to the `mysql' user?

A. MySQL cannot be run as the root user.

B. MySQL requires correct file ownership while remaining secure.

C. MySQL needs to be run as the root user, but file cannot be owned by it.

D. The mysqld process requires all permissions within datadir to be the same.

You have table `apps','userdata' on server that uses MyISAM storage engine. You want to transfer this data to server but use InnoDB engine instead.

You execute the following commands:

ServerB commands: Shell> mysqldump u root h server no-data apps userdata | mysql u root p apps Shell> mysql u root p h server e `ALTER TABLE `apps','userdata' ENGINE=InnoDB;' Shell> mysqldump u root p h server no-create-info order-by-primary apps userdata | mysql u root p apps

What effect does the order-by-primary argument have on the mysqldump command?

A. It exports tables with the most indexes first to assist with import speeds.

B. It ensures that unique indexes have no conflicts when the data is dumped.

C. It orders by primary key to assist in speeding up importing to InnoDB tables.

D. It must be specified so index data is dumped correctly when on-create-info is used.

Which three data components are needed for point-in-time recovery?

A. The error log

B. The backup log

C. The general query log

D. Binary logs

E. The data backup

F. Configuration files