Skip to main content

1Z0-883 Real Exam Questions

MySQL 5.6 Database Administrator

100 questions available · Page 1 of 10

Updated Exam DumpsVerified AnswersPass Guarantee

Get Complete Exam Dumps
Question 1 Multiple choice

Which two statements are true regarding partitioning in Mysql?

  1. A

    Tables with BLOB and TEXT columns cannot be partitioned.

  2. B

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

  3. C

    Partitioning allows different columns to be stored in separate files.

  4. D

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

  5. E

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

Show answer and explanation

Correct answers: C, D

Question 2 Single choice

While reviewing the MySQL error log, you see occasions where MySQL has exceeded the number of file handles allowed to it by the operating system.

Which method will reduce the number of file handles in use?

  1. A

    Disconnecting idle localhost client sessions

  2. B

    Implementing storage engine data compression options

  3. C

    Relocating your data and log files to separate storage devices

  4. D

    Activating the MySQL Enterprise thread pool plugin

Show answer and explanation

Correct answer: A

Question 3 Single choice

Consider the Mysql Enterprise Audit plugin.

You are checking user accounts and attempt the following query:

Mysql> SELECT user, host, plugin FROM mysql.users; ERROR 1146 (42S02): Table `mysql.users' doesn't
exist

Which subset of event attributes would indicate this error in the audit.log file?

  1. A

    NAME="Query"
    STATUS="1146"
    SQLTEXT="select user,host from users"/>

  2. B

    NAME="Error"
    STATUS="1146"
    SQLTEXT="Error 1146 (42S02): Table `mysql.users' doesn't exist"/>

  3. C

    NAME="Query"
    STATUS="1146"
    SQLTEXT=" Error 1146 (42S02): Table `mysql.users' doesn't exist"/>

  4. D

    NAME="Error"
    STATUS="1146"
    SQLTEXT="select user,host from users"/>

  5. E

    NAME="Error"
    STATUS="0"
    SQLTEXT="Error 1146 (42S02): Table `mysql.users' doesn't exist"/>

Show answer and explanation

Correct answer: C

Question 4 Single choice

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

  1. A

    SHOW FULL PROCESSLIST WHEER Time > 180;

  2. B

    SELECT * FROM INFORMATION_SCHEMA.EVENTS SHERE STARTS < (DATE_SUB (NOW ( ),
    INTERVAL 180 SECOND) );

  3. C

    SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE STATE < (DATE_SUB
    (NOW ( ), INTERVAL 180 SECOND) );

  4. D

    SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 180;

Show answer and explanation

Correct answer: A

Question 5 Single choice

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?

  1. A

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

  2. B

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

  3. C

    Enforce_gtid_consistency is set to ON.

  4. D

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

Show answer and explanation

Correct answer: B

Question 6 Multiple choice

Consider the Mysql Enterprise Audit plugin.

The following event detail is found in the audit log:

<AUDIT_RECORD
TIMESTAMP="2013-04-09t01:54:17"
NAME="Connect"
CONNECTION_ID="3"
STATUS="1045"
USER="kate"
PROXY_USER=""
HOST="localhost"
IP=""
DB=""/>

Which two points can be concluded from the given event?

  1. A

    A connection was blocked by a firewall or a similar security mechanism.

  2. B

    A connection was attempted via socket rather than TCP.

  3. C

    A connection failed because the proxy user privileges did not match the login user.

  4. D

    A connection as the user kate was successful.

  5. E

    A connection failed due to authentication being unsuccessful.

Show answer and explanation

Correct answers: B, D

Question 7 Multiple choice

You examine the output of SHOW GLOBAL STATUS and notice that the value of Created_tmp_disk_tables is consistently increasing.

Which two variables would likely fix this issue?

  1. A

    Table_open_cache

  2. B

    Table_open_cache_instancs

  3. C

    Table_definition_cache

  4. D

    Tmp_table_size

  5. E

    Max_heap_table_size

  6. F

    Max_tmp_tables

Show answer and explanation

Correct answers: D, E

Question 8 Multiple choice

Full Atomicity, Consistency, Isolation, Durability (ACID) compliance is a necessity for a new application, which heavily reads and writes data.

This requires the following config file options:

Sync_binlog=1
Innodb_flush_log_at_trx_commit=1
Innodb_doublewrite=1

However, this configuration is expected to introduce disk I/O overhead.

What three changes will reduce disk I/O overheads?

  1. A

    Use of soft links for database directories on the same physical disk

  2. B

    Use of separate directories on the same physical disk for log files and data files

  3. C

    Placement of InnoDB log files and datadir on separate physical disks

  4. D

    Allocation of RAM to the buffer pool such that more of the data can fit in RAM

  5. E

    Use of delay_key_write=ON for batch index update

Show answer and explanation

Correct answers: C, D, E

Question 9 Single choice

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?

  1. A

    MySQL cannot be run as the root user.

  2. B

    MySQL requires correct file ownership while remaining secure.

  3. C

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

  4. D

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

Show answer and explanation

Correct answer: B

Question 10 Single choice

Consider the following table:

REATE TABLE 'game' (
'id' int (10) unsigned NOT NULL AUTO_INCREMENT,
'keyword' varchar (45) DEFAULT NULL,
'date' datetime NOT NULL,
PRIMARY KEY ('id' , 'date'),
UNIQUE KEY 'keyword_idx' ('keyword' , 'date')
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (TO_DAYS (date) ) (
PARTITION g201301 VALUES LESS THAN (TO_DAYS ('2013-01-01 00:00:00') ),
PARTITION g201302 VALUES LESS THAN (TO_DAYS ('2013-02-01 00:00:00') ),
PARTITION g201303 VALUES LESS THAN (TO_DAYS ('2013-03-01 00:00:00') ),
PARTITION g201304 VALUES LESS THAN (TO_DAYS ('2013-04-01 00:00:00') ),
PARTITION gMORES VALUES LESS THAN (MAXVALUE) );

Which method should used to add a new g201305 partition to the table?

  1. A

    ALTER TABLE games
    REORGANIZE PARTITION (gMORES)
    INTO
    g01305 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') ), gMORES VALUES LESS THAN
    (MAXVALUE) );

  2. B

    ALTER TABLE games
    ADD PARTITION g201350 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') );

  3. C

    ALTER TABLE games
    COALESCE PARTITION (gMORES)
    INTO
    g01305 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') ), gMORES VALUES LESS THAN
    (MAXVALUE) );

  4. D

    ALTER TABLE games
    SPLIT PARTITION (gMORES)
    INTO
    g201305 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') ), gMORES VALUES LESS THAN
    (MAXVALUE) );

  5. E

    ALTHER TABLE games
    DROP PATITION gMORES,
    ADD PARTITION
    g201305 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') ), gMORES VALUES LESS THAN
    (MAXVALUE) );

Show answer and explanation

Correct answer: B