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

View Answer

Q2. Consider the events_% tables in performance Schema. 

Which two methods will clear or reset the collected events in the tables? 

A. Using DELETE statements, for example, DELETE FROM performance_schema.events_watis_current; 

B. Using the statement RESET PERFORMANCE CACHE; 

C. Using the statement FLUSH PERFORMANCE CACHE; 

D. Using TRUNCATE statements, for example, TRUNATE TABLE performance_schema.events_waits_current; 

E. Disabling and re-enabling all instruments 

F. Restarting Mysql 

View Answer

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

View Answer

Q4. You are attempting to secure a MySQL server by using SSL encryption. 

On starting MySQL, you get this error: 

130123 10:38:02 [ERROR] mysqld: unknown option ‘—ssl’ 

What is the cause of the error? 

A. The --- ssl level was not specified. 

B. The server was not started with the – enable--ssl-plugin option. 

C. -- ssl is not a valid server option. 

D. The mysqld binary was not compiled with SSL support. 

E. The server’s SSL certificate was invalid. 

View Answer

Q5. 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? 

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

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

C. Placement of InnoDB log files and datadir on separate physical disks 

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

E. Use of delay_key_write=ON for batch index update 

View Answer

Q6. Which three statements are characteristic of the MEMORY storage engine? 

A. Each table is represented on disk as an.frm file. 

B. Each table has a corresponding.MYI and .MYD file. 

C. It can support foreign keys. 

D. It cannot contain text or BLOB columns. 

E. Table contents are not saved if the server is restarted. 

F. It can support transactions 

View Answer

Q7. You need to replicate a table from a master to a slave. The master and slave copies of the 

table will have different number of columns. 

Which two conditions must be true? 

A. Each extra column in the copy with more columns must not have a default value. 

B. Columns that are common to both versions of the table must be defined in the same order on the master and the slave. 

C. The slave database cannot have more columns than the master. Only the master database can have more columns. 

D. Columns that are common to both versions of the table must come first in the table definition, before any additional columns are additional columns are defined on either server. 

E. The master database cannot have more columns than the slave. Only the slave deatbase can have more columns. 

View Answer

Q8. You use—login-path to access a MySQL server on a Linux installation. 

Which statement is true about the – login-path option that is created by using mysql_config_editor? 

A. All system users have access to the MySQL server via—login path local. 

B. __login-path can be used only for MySQL servers running on a local machine. 

C. __login-path allows you to provide login credentials without passing clear text passwords on the command line. 

D. When using – login-path to connect to a remote MySQL server, the remote server version must be 5.6 or later. 

View Answer

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

View Answer

Q10. Which two requirements would lead towards a high availability solution? 

A. When uptime is critical 

B. When data must be refactored 

C. When application concurrency is static 

D. When data loss is unacceptable 

E. When application is a single point of failure 

View Answer