1Z0-883 MySQL 5.6 Database Administrator

Exam Number: 1Z0-883
Exam Title: MySQL 5.6 Database Administrator
Associated Certification Paths : Oracle Certified Professional, MySQL 5.6 Database Administrator
Duration: 150 minutes
Number of Questions: 100
Passing Score: 60%%
Validated Against: Exams are based on General Availability (GA) versions of MySQL 5.6.
Format: Multiple Choice

Complete Recommended Training
MySQL for Database Administrators

Additional Preparation and Information
A combination of Oracle training and hands-on experience (attained via labs and/or field experience) provides the best preparation for passing the exam.

MySQL 5.6 Reference Manual

MySQL Architecture
Use MySQL client programs to interface with the MySQL Server interactively and in batch
Describe how MySQL uses disk and memory resources
List and describe key characteristics of standard MySQL storage engines including InnoDB, NDB, MyISAM, MEMORY, FEDERATED

MySQL Server Installation, Configuration and Maintenance
Select, deploy, start and stop MySQL using appropriate binary packages for Windows and Linux platforms
Configure MySQL Server deployments using options files, command-line options and server variables.
Identify, configure, locate and describe appropriate use cases for MySQL the error, binary, general query and slow query log.

MySQL Security
Describe appropriate steps to secure a MySQL deployment at the operating system, filesystem and network levels.
Create and maintain user accounts with appropriate privileges and configuration.
Deploy and configure MySQL Enterprise Audit

Diagnostic Data and Metadata Sources in MySQL
Configure and leverage PERFORMANCE_SCHEMA tables to identify and diagnose performance problems.
Obtain MySQL metadata from INFORMATION_SCHEMA tables

Optimizing MySQL Performance
Demonstrate ability to diagnose and optimize poorly-performing queries.
Tune MySQL Server configuration for best performance.
Apply best practices in optimizing schema objects
Demonstrate understanding of locking concepts as applied to MySQL Server and storage engines.
Create and utilize table PARTITIONING

Backups and Recovery in MySQL
Create and restore logical backups using mysqldump
Create and restore binary backups
Use backups for data recovery

High Availability Techniques for MySQL
Describe, configure, deploy and troubleshoot MySQL replication
Describe and contrast characteristics of common MySQL high availability solutions (MySQL Cluster, DRBD, Windows Failover Clustering, Solaris Cluster, OVM Template for MySQL Enterprise)

ET GLOBAL enforce_gtid_consistency=ON
D. SET GTID_EXECUTED=”38f32e23480a7-32a1-c323f78067fd37821 : 9”;
E. SET GTID_NEXT=”38f32e23480a7-32a1-c323f78067fd37821 : 9”;
BEGIN; COMMIT;
SET GTID_NEXT=”AUTOMATIC”;

Answer: A

Explanation:

QUESTION 2
Consider the following statement on a RANGE partitioned table:
ALTER TABLE orders DROP PARTITION p1, p3;
What is the outcome of executing the above statement?

A. Only the first partition (p1) will be dropped as only one can be dropped at any time.
B. All data in p1 and p3 partitions are removed, but the table definition remains unchanged.
C. A syntax error will result as you cannot specify more than one partition in the same statement.
D. All data in pi and p3 partitions are removed and the table definition is changed.

Answer: B
Reference: https://docs.oracle.com/cd/F49540_01/DOC/server.815/a67772/partiti.htm

QUESTION 3
You inherit a legacy database system when the previous DBA, Bob, leaves the company. You are
notified that users are getting the following error:
mysql> CALL film_in_stock (40, 2, @count);
ERROR 1449 (HY000): The user specified as a definer (‘bon’@’localhost’) does not exist
How would you identify all stored procedures that pose the same problem?

A. Execute SELECT * FROM mysql.routines WHERE DEFINER=’bob@localhost’;.
B. Execute SHOW ROUTINES WHERE DEFINER=’bob@localhost’.
C. Execute SELECT * FROM INFORMATION_SCHEM
A. ROUTINES WHERE
DEFINER=’bob@localhost’;.
D. Execute SELECT * FROM INFORMATION_SCHEM
A. PROCESSLIST WHERE USER=’bob’
and HOST=’ localhost’;.
E. Examine the Mysql error log for other ERROR 1449 messages.

Answer: D

Explanation:

QUESTION 4
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 >>. The other
integer types cannot.

Answer: B

Explanation:

QUESTION 5
ROW-based replication has stopped working. You investigate the error log file and find the
following entries:
2013-08-27 14:15:47 9056 [ERROR] Slave SQL: Could not execute Delete_rows event on table
test.t1; Can’t find record in ‘t1’, Error_code: 1032; handler error
HA_ERR_KEY_NOT_FOUND; the event’s master log 56_master-bin. 000003, end_log_pos 851,
Error_code: 1032
2013-08-27 14:15:47 9056 [warning] Slave: Can’t find record in ‘t1’ Error_code: 1032
2013-08-27 14:15:47 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_masterbin.
000003’ position 684
Why did you receive this error?

A. The slave SQL thread does not have DELETE privileges to execute on test.t1 table.s
B. The table definition on the slave -litters from the master.
C. Multi-threaded replication slaves can have temporary errors occurring for cross database
updates.
D. The slave SQL thread attempted to remove a row from the test.t1 table, but the row did not
exist.

Answer: D

Explanation:

QUESTION 6
Mysqldump was used to create a single schema backup;
Shell> mysqldump –u root –p sakila > sakila2013.sql
Which two commands will restore the sakila database without interfering with other running database?

A. Mysql> USE sakila; LOAD DATA INFILE ‘sakila2013.sql’;
B. Shell> mysql –u root –p sakila sakila2013.sql
C. Shell> mysql import –u root –p sakila sakila2013.sql
D. Shell> mysql –u root -p –e ‘use sakila; source sakila2013.sql’
E. Shell> mysql –u root –p –silent < sakila2013.sql

Answer: B
Reference: https://mysql.livejournal.com/133572.html

Click here to view complete Q&A of 1Z0-883 exam
Certkingdom Review, Certkingdom PDF Torrents