Q1. Consider the Mysql Enterprise Audit plugin. 

You are checking user accounts and attempt the following query: 

Mysql> SELECT user, host, plugin FROM mysql.users; 

ERROR 1146 (42S02): Table ‘mysql.users’ doesn’t exist 

Which subset of event attributes would indicate this error in the audit.log file? 

A. NAME=”Query” 

STATUS=”1146” 

SQLTEXT=”select user,host from users”/> 

B. NAME=”Error” 

STATUS=”1146” 

SQLTEXT=”Error 1146 (42S02): Table ‘mysql.users’ doesn’t exist”/> 

C. NAME=”Query” 

STATUS=”1146” 

SQLTEXT=” Error 1146 (42S02): Table ‘mysql.users’ doesn’t exist”/> 

D. NAME=”Error” 

STATUS=”1146” 

SQLTEXT=”select user,host from users”/> 

E. NAME=”Error” 

STATUS=”0” 

SQLTEXT=”Error 1146 (42S02): Table ‘mysql.users’ doesn’t exist”/> 

View Answer

Q2. Which two statements describe the behavior of the server’s SQL mode? 

A. The server’s SQL mode determines how the server should behave when performing data validation check and interpreting different forms of syntax. 

B. The server’s SQL mode determines whether the server should be read-only or should accept commands such as INSERT and UPDATE. 

C. The server’s SQL mode can be changed at the session level with a SET SESSION sql_mode=”new_value” command. 

D. The server’s SQL mode, when globally set on a slave server, applies to events sent from the master. 

View Answer

Q3. You are creating a new server with the same accounts as an existing server. You do this by 

importing a mysqldump file of the mysql database. 

You test whether the import was successful by using the following commands: 

Mysql> select user, host, password from mysql.user; 

9 rows in set (0.00 sec) 

Mysql> show grants for ‘admin’@’%’; 

ERROR 1141 (42000): There is no such grant defined for user ‘admin’ on host ‘%’ 

Which command will fix this issue? 

A. CREATE USER ‘admin’ @’%’; 

B. GRANT USAGE ON *.* TO ‘admin’@’%’; 

C. FLUSH PRIVILEGES; 

D. FLUSH HOST CACHE; 

E. UPDATE mysql.user SET Create_user_priv = ‘Y’ WHERE user= ‘admin’; 

View Answer

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

View Answer

Q5. Which two statements are true about InnoDB auto-increment locking? 

A. The auto-increment lock can be a table-level lock. 

B. InnoDB never uses table-level locks. 

C. Some settings for innodb_autoinc_lock_mode can help reduce locking. 

D. InnoDB always protects auto-increment updates with a table-level lock. 

E. InnoDB does not use locks to enforce auto-increment uniqueness. 

View Answer

Q6. 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_SCHEMA. ROUTINES WHERE DEFINER=’bob@localhost’;. 

D. Execute SELECT * FROM INFORMATION_SCHEMA. PROCESSLIST WHERE USER=’bob’ and HOST=’ localhost’;. 

E. Examine the Mysql error log for other ERROR 1449 messages. 

View Answer

Q7. What are four capabilities of the mysql client program? 

A. Creating and dropping databases 

B. Creating, dropping, and modifying tables and indexes 

C. Shutting down the server by using the SHUTDOWN command 

D. Creating and administering users 

E. Displaying replication status information 

F. Initiating a binary backup of the database by using the START BACKUP command 

View Answer

Q8. In a design situation, there are multiple character sets that can properly encode your data. Which three should influence your choice of character set? 

A. Disk usage when storing data 

B. Syntax when writing queries involving JOINS 

C. Comparing the encoded data with similar columns on other tables 

D. Memory usage when working with the data 

E. Character set mapping index hash size 

View Answer

Q9. You are using replication and the binary log files on your master server consume a lot of disk space. 

Which two steps should you perform to safely remove some of the older binary log files? 

A. Ensure that none of the attached slaves are using any of the binary logs you want to delete. 

B. Use the command PURGE BINARY LOGS and specify a binary log file name or a date and time to remove unused files. 

C. Execute the PURGE BINARY LOGE NOT USED command. 

D. Remove all of the binary log files that have a modification date earlier than today. 

E. Edit the .index file to remove the files you want to delete. 

View Answer

Q10. Consider the three binary log files bin.00010, bin.00011, and bin.00012 from which you want to restore data. 

Which method would use mysqlbinlog for greater consistency? 

A. shell> mysqlbinlog bin.00010 | mysql shell> mysqlbinlog bin.00011 | mysql shell> mysqlbinlog bin.00012 | mysql 

B. shell> mysqlbinlog bin.00010 bin.00011 bin.00012 | mysql 

C. shell> mysqlbinlog – restore bin.00010 bin.00011 bin.00012 

D. shell> mysqlbinlog – include-gtide=ALL bin.00010 bin.00011 bin.00012 | mysql 

View Answer