Q1. Consider the Mysql Enterprise Audit plugin.
A CSV file called data.csv has 100 rows of data.
The stored procedure prepare_db ( ) has 10 auditable statements.
You run the following statements in the mydb database:
Mysql> CALL prepare_db ( );
Mysql> LOAD DATA INFILE ‘/tmp/data.cav’ INTO TABLE mytable;
Mysql> SHOW TABLES;
How many events are added to the audit log as a result of the preceding statements?
A. 102; top-level statements are logged, but LOAD DATA INFILE is logged as a separate event.
B. 3; only the top-level statements are logged.
C. 111; top-level statements and all lower-level statements are logged.
D. 12; only top-level statements and stored procedure events are logged.
Answer: B
Reference: http://dev.mysql.com/doc/mysql-security-excerpt/5.5/en/audit-log-plugin-logging-control.html
Q2. Consider typical High Availability (HA) solutions that do not use shared storage.
Which three HA solutions do not use shared storage?
A. Mysql Replication
B. Distributed Replicated Block Device (DRBD) and Mysql
C. Windows Cluster and Mysql
D. Solaris Cluster and Mysql
E. Mysql NDB Cluster
Answer: A,C,D
Q3. You are using the performance Schema to investigate replication on a slave:
Mysql> SELECT THREAD_ID threads.NAME, SUM (COUNT_STAR) AS Totalcount, SUM (SUM_TIMER_WAIT) AS Totaltime
FROM performance_schema.events_waits_summary_by_thread_by_event_name
INNER JOIN performance_schema,threads USING (THREAD_ID)
WHERE threads .NAME LIKE ‘thread/sql/slave\-%’
GROUP BY THREAD_ID, threads.NAME;
Assume that all instruments and consumers are enabled and all threads are instrumented.
Which two facts can be concluded from the given output?
A. At most two schemas are being updated concurrently.
B. The server needs more cores to use all slave threads.
C. The slave cannot process the relay log fast enough to use all threads.
D. The slave is configured with slave_parallel_workers = 4.
Answer: C
Q4. You execute the following statement in a Microsoft Windows environment. There are no conflicts in the path name definitions.
C: \> mysqld – install Mysql56 – defaults – file = C : \my –opts.cnf
What is the expected outcome?
A. Mysqld acts as an MSI installer and installs the Mysql 5.6 version, with the c: \my-opts.cnf configuration file.
B. Mysql is installed as the Windows service name Mysql56, and uses c: \my-opts.cnf as the configuration file
C. An error message is issued because – install is not a valid option for mysqld.
D. A running Mysql 5.6 installation has its runtime configuration updated with the server variables set in c: \my-opts.cnf.
Answer: B
Q5. What are three facts about backups with mysqldump?
A. Can back up a remote database server
B. Allow a consistent backup to be taken
C. Are always faster to restore than binary backups
D. Are able to back up specific items within a database
E. Create automatically compressed backups
F. Will lock all storage engines for duration of backup
Answer: D,E,F
Q6. You have table ‘apps’,’userdata’ on server that uses MyISAM storage engine. You want to transfer this data to server but use InnoDB engine instead.
You execute the following commands:
ServerB commands:
Shell> mysqldump –u root –h server –no-data apps userdata | mysql –u root –p apps
Shell> mysql –u root –p –h server –e ‘ALTER TABLE ‘apps’,’userdata’ ENGINE=InnoDB;’
Shell> mysqldump –u root –p –h server –no-create-info –order-by-primary apps userdata | mysql –u root –p apps
What effect does the – order-by-primary argument have on the mysqldump command?
A. It exports tables with the most indexes first to assist with import speeds.
B. It ensures that unique indexes have no conflicts when the data is dumped.
C. It orders by primary key to assist in speeding up importing to InnoDB tables.
D. It must be specified so index data is dumped correctly when –on-create-info is used.
Answer: C
Q7. A user executes the statement;
PURGE BINARY LOGS TO ‘mysql-bin.010’;
What is the result?
A. It deletes all binary log files, except ‘mysql-in.010’.
B. It deletes all binary log files up to and including ‘mysql-bin.010’.
C. It deletes all binary log files before ‘mysql-bin.010’.
D. It deletes all binary log files after ‘mysql-bin.010’.
Answer: B
Reference: http://dev.mysql.com/doc/refman/5.5/en/purge-binary-logs.html
Q8. Assume that you want to know which Mysql Server options were set to custom values. Which two methods would you use to find out?
A. Check the configuration files in the order in which they are read by the Mysql Server and compare them with default values.
B. Check the command-line options provided for the Mysql Server and compare them with default values.
C. Check the output of SHOW GLOBAL VARIABLES and compare it with default values.
D. Query the INFORMATION_SCHEMA.GLOBAL_VARIABLES table and compare the result with default values.
Answer: C
Q9. You are investigating the performance of the server and see the following information:
Events_waits_summary_global_by_event_name in the performance schema
shows that the wait/synch/mutex/sql/LOCK_table_cache event is dominating other
wait events.
The table_open_cache_overflows status variable is 0.
Which action should be taken to remove the performance bottleneck described here?
A. Decrease the value of table_definition_cache.
B. Increase the value of table_definition_cache.
C. Decrease the value of table_open_cache.
D. Increase the value of table_open_cache.
E. Decrease the value of table_open_cache_instances.
F. Increase the value of table_open_cache_instances.
Answer: A,D
Q10. Which two statements are true regarding partitioning in Mysql?
A. Tables with BLOB and TEXT columns cannot be partitioned.
B. Partitioning allows easier management of smaller data sets for certain queries.
C. Partitioning allows different columns to be stored in separate files.
D. The partitioning expression is an integer or function that returns an integer value or NULL value.
E. Partitioning is only available for those storage engines that implements it natively.
Answer: C,D