Q1. What are three actions performed by the mysql_secure_installation tool? 

A. It prompts you to set the root user account password. 

B. It checks whether file permissions are appropriate within datadir. 

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

D. It can delete any anonymous accounts. 

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

View Answer

Q2. What is true regarding InnoDB locking? 

A. InnoDB row locks may be escalated to page or table-level locks. 

B. InnoDB only uses row locks, not page or table-level locks, 

C. InnoDB uses row and table-level locks, but row locks are not escalates, 

D. InnoDB locks only those rows that are updated. 

E. InnoDB uses row-level or table-level locks depending on the number of rows affected. 

View Answer

Q3. A general purpose MySQL instance is configured with the following options: 

-- log-slow-queries -- long-query-time=,0001 -- log-slow-admin-queries -- general-log -- log-bin -- binlog-format=STATEMENT -- innodb-flush-log-at-trx-commit=1 

Which three statements are true? 

A. The General Query Log records more data than the Binary Log. 

B. The binary Log records more data than the General Query Log. 

C. The Slow Query Log records more data than the General Query Log. 

D. The General Query Log records more data than the Slow Query Log. 

E. The Slow Query Log records more data than the Binary Log. 

F. The Binary Log records more data than the Slow Query Log. 

View Answer

Q4. 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; 

View Answer

Q5. You have a server that has very limited memory but has a very large table. 

You will use mysqldump to back up this table. 

Which option will ensure mysqldump will process a row at a time instead of buffering a set of rows? 

A. -- quick 

B. -- skip-buffer 

C. -- single-transaction 

D. -- tab 

View Answer

Q6. Which two are correct steps in taking a binary backup of MyISAM tables? 

A. Always stop the server prior to the backup. 

B. Stop the server or lock the tables prior to the backup. 

C. Stop the server or lock the databases prior to the backup. 

D. Make a copy of the .frm, .myd, and the .myi files. 

E. Make a copy of the binary log and tablespace files. 

View Answer

Q7. 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: 

… 

<AUDIT_RECORD 

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. 

View Answer

Q8. 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_master-bin. 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. 

View Answer

Q9. Which High Availability solution can provide a consistent, time-delayed (for example, one hour) snapshot of the live production database? 

A. MySQL Replication 

B. Distributed Replication Block Device 

C. Windows Server Failover Clustering 

D. MySQL Cluster 

View Answer

Q10. 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. 

View Answer