Q1. Consider the MySQL Enterprise Audit plugin,
You add the following lines to the my.cnf configuration tile:
[mysqld]
Plugin-load=audit_log.so
Audit-log=FORCE_PLUS_PERMANENT
You attempt to start up the MySQL service and notice that it fails to start.
Which two statements would explain why the service did not start?
A. FORCE_PLUS_PERMANENT is not valid for the audit-log option.
B. The audit_log.so library does not exist.
C. The audit_log.so library is in a location that is different from that defined by the plugin_dir option.
D. The audit plugin must be loaded dynamically by using the INSTALL PLUGIN command.
E. The audit log file does not exist in which to write audit events.
F. The audit_log.so library is not an executable file.
Answer: D
Reference: http://dev.mysql.com/doc/refman/5.6/en/audit-log-plugin-installation.html
Q2. 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.
Answer: B,D
Reference: http://dev.mysql.com/doc/refman/5.6/en/backup-methods.html
Q3. Compare a typical Distributed Replicated Block Device (DRBD) with MySQL Standard Replication using master-slave replication.
Which two statements are correct?
A. Both technologies use the TCP/IP stack as their primary transmission medium.
B. DRBD uses shared-disk technology.
C. Both technologies guarantee an identical copy of data on the secondary node.
D. Only MySQL can maintain a time-delayed copy of data on the secondary node.
Answer: B
Q4. 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.
Answer: B
Q5. Which two statements are true about setting the per-thread buffers higher than required?
A. More memory per thread is beneficial in all scenarios.
B. It causes increased overhead due to initial memory allocation.
C. It can affect system stability during peak load times, due to swapping.
D. It requires increasing the thread_cache_size variable.
Answer: C,D
Q6. Which three statements describe how the strict SQL mode provides added security?
A. It rejects statements that try to insert out-of-range values
B. It rejects invalid dates.
C. It limits the operations that the server can perform.
D. It rejects queries that produce out-of-range values.
E. It rejects dates with zero day or month values.
Answer: C,D,E
Q7. The ‘allplicationdb’ is using innoDB and consuming a large amount of file system space. You have a /backup partition available on NFS where backups are stored.
You investigate and gather the following information:
[mysqld] Datadir=/var/lib/mysql/ Innodb_file_per_table=0
Three tables are stored in the innoDB shared tablespace and the details are as follows:
The table data_current has 1,000,000 rows.
The table data_reports has 1,500,000 rows.
The table data_archive has 4,500,000 rows. Shell> is -1 /var/lib/mysql/ -rw-rw---- 1 mysql mysql 744G Aug 26 14:34 ibdata1 -rw-rw---- 1 mysql mysql 480M Aug 26 14:34 ib_logfile0 -rw-rw---- 1 mysql mysql 480M Aug 26 14:34 ib_logfile1 …
You attempt to free space from ibdata1 by taking a mysqldump of the data_archive table and storting it on your backup partition.
Shell> mysqldump – u root – p applicationdb data_archive > /backup/data_archive.sql Mysql> DROP TABLE data_archive;
Which set of actions will allow you to free disk space back to the file system?
A. Execute OPTIMIZE TABLE so that the InnoDB engine frees unused pages on disk back
to the file system:
Mysql> OPTIMIZE TABLE data_current, data_reports;
B. Set the server to use its own tablespace, and then alter the table so that data is moved
from the shared tablespace to its own:
Mysql> SET GLOBAL innodb_file_per_table=1;
Mysql> ALTER TABLE data_current ENGINE=InnoDB;
Mysql> ALTER TABLE data_repors ENGINE=InnoDB;
C. Take a backup, stop the server, remove the data files, and restore the backup:
Shell> mysqldump – u root –p applicationdb / > /backup/applicationdb.sql
Shell> /etc/init.d/mysql stop
Shell> cd /var/lib/mysql/
Shell> rm ibdata1 ib_logfile0 ib_logfile1
Shell> /etc/init.d/mysql start
Shell> mysql – u root – p applicationdb < /backup/applicationdb.sql
D. Enable compression on the table, causing InnoDB to release unused pages on disk to
the file system:
Mysql> SET GLOBLE innodb_file_per_table=1;
Mysql> SET GLOBLE innodb_file_format=Barramcuda;
Mysql> ALTER TABLE data_current ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;
Mysql> ALTER TABLE data_history ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;
Answer: D
Q8. Which query would you use to find connections that are in the same state for longer than 180 seconds?
A. SHOW FULL PROCESSLIST WHEER Time > 180; B. SELECT * FROM INFORMATION_SCHEMA.EVENTS SHERE STARTS < (DATE_SUB (NOW ( ), INTERVAL 180 SECOND) );
C. SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE STATE < (DATE_SUB (NOW ( ), INTERVAL 180 SECOND) );
D. SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 180;
Answer: A
Q9. 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.
Answer: B
Q10. Following a server crash, the automatic recovery of InnoDB fails.
How would you begin to manually repair the InnoDB tables?
A. Start the server with the – innodb_force_recovery option set to a non-zero value.
B. Start the server as usual, and then execute the REPAIR TABLE command.
C. Start the server as usual, and then execute the CHECK TABLE command.
D. Start the server with the – innodb_recover_options option set to FORCE.
Answer: A