1Z0-883 Online Practice Questions and Answers

When designing an InnoDB table, identify an advantage of using the BIT datatype Instead of one of the integer datatypes.

A. BIT columns are written by InnoDB at the head of the row, meaning they are always the first to be retrieved.

B. Multiple BIT columns pack tightly into a row, using less space.

C. BIT (8) takes less space than eight TINYINT fields.

D. The BIT columns can be manipulated with the bitwise operators and, |, ~, ^, <<, and >>. The other integer types cannot.

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?

A. They will have their passwords updated on start-up to sha256_password format.

B. They will have to change their password the next time they login to the server.

C. They are not affected by this configuration change.

D. They all connect via the secure sha256_password algorithm without any configuration change.

You are having problems with connections from a specific host (192.168.1.15) not closing down correctly. You want to find the state of the threads from that host check for long-running queries.

Which statement will accomplish this?

A. SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE HOST='192.168.1.15';

B. SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE HOST=' 192.168.1.15';

C. SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE HOST=' 192.168.1.15';

D. SELECT * FROM INFORMATION_SCHEMA.INNODB_METEICS WHERE HOST=' 192.168.1.15';

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 a record of all queries that are not using indexes. How would you achieve this?

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

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

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

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

Which statement is true about the log-output variable?

A. It is a static variable and can be set only at MySQL server startup.

B. It enables and starts the General Query Log.

C. It sets the target location for the binary logs generated by the MySQL sever.

D. It specifies output destinations for the slow and General Query logs.

Consider the MySQL Enterprise Audit plugin.

On attempting to start the MySQL service after a crash, notice the following error:

[ERROR] Plugin `audit_log' init function returned error.

In the audit log file, you notice the final entry:

...

TIMESTAMP="2013-07-09T02:12:35"

NAME="Connect"

CONNECTION_ID="98"

STATUS="0"

USER="Kate"

PRIV_USER="kate"

OS_LOGIN=""

HOST="localhost"

DB=""/>

What action should you take to fix the error and allow the service to start?

A. Re-install the audit plugin.

B. Execute the command FLUSH LOGS.

C. Execute the command SET GLOBAL audit_log_fiush= ON.

D. Move or rename the existing audit.log file.

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.

Which three methods will show the storage engine for the Country table?

A. SHOW CREATE TABLE Country;

B. SHOW ENGINE Country STATUS;

C. SHOW TABLE STATUS LIKE `Country';

D. SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= `Country';

E. SELECT ENGINE FROM INFORMATION_SCHEMA.ENGINES WHERE TABLE_NAME= `Country';