Q1. MySQL is installed on a Linux server and has the following configuration:
[mysqld]
User=mysql
Datadir=/data/mysql
As the ‘root’ user, change the datadir location by executing:
Shell> cp –R /var/lib/mysql/data/mysql/
Shell> chown –R mysql /data/mysql/
What is the purpose of changing ownership of datadir to the ‘mysql’ user?
A. MySQL cannot be run as the root user.
B. MySQL requires correct file ownership while remaining secure.
C. MySQL needs to be run as the root user, but file cannot be owned by it.
D. The mysqld process requires all permissions within datadir to be the same.
Answer: B
Q2. 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”/>
Answer: C
Q3. 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
Answer: A
Q4. You want to create a temporary table named OLD_INVENTORY in the OLD_INVENTORY database on the master server. This table is not to be replicated to the slave server.
Which two changes would ensure that the temporary table does not propagate to the slave?
A. Use the – replicate-do-db, -- replicate-do-table, or – replicate-wild-do-table option with the value equal to OLD_INVENTORY.
B. Change the binlog_format option to ROW and restart mysqld before you create the OLD_INVENTORY table.
C. Stop SQL_THREAD on the slave until you have finished using the OLD_INVENTORY temporary table.
D. Set binlog_format=MIXED with the – replicate-ignore-temp-table option.
E. Use the – replicate-ignore-table option with the value equal to OLD_INENTORY.OLD_INVENTORY and restart mysqld before creating the temporary table.
Answer: A,D
Q5. What are three methods to reduce Mysql server exposure to remote connections?
A. Setting -- skip-networking when remote connections are not required
B. Using the sql_mode=STRICT_SECURE after connections are established for encrypted communications
C. Setting specific GRANT privilege to limit remote authentication
D. Setting – mysql_secure_configuration to enable paranoid mode
E. Using SSL when transporting data over remote networks
Answer: B,C,D
Q6. You adjust a default configuration to the following /etc/my.cnf on a Linux installation:
[mysqld]
Loq-bin
Binrylog_format=ROW
You do not notice the spelling error in binrylog_format and restart your production server.
How does the MySQL server behave with incorrectly spelled options?
A. Mysqld uses internal configuration versioning and reverts to the previous configuration.
B. When using mysql_config_editor for configuration adjustments, it detects incorrect syntax and typing mistakes.
C. The mysqld_safe script skips the unknown variable and starts using the remaining configuration changes.
D. Mysqld prints to the error log about an unknown variable, and then exits.
Answer: D
Q7. 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
Answer: D,E
Reference: http://dev.mysql.com/doc/refman/5.5/en/performance-schema-timing.html
Q8. 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.
Answer: B
Q9. Review the definition of the phone_list view.
CHEATE OR REPLACE ALGORITHM=MERGE DEFINER= ‘root’@localhost’ SQL SECURITY DEFINER VIEW ‘phone_list’ AS SELECT
e . id as id
‘e . first_name AS ‘first_name’
‘e . last_name AS ‘last_name’
‘coalesce ( ph1.phone_no, ‘--') AS ‘office_no’
‘coalesce (ph2 .phone_no, '--‘) AS ‘cell_no’
FROM employees e
LEFT JOIN employee_phone ph1
ON ph1.emp_id = e.id AND ph1.type = ‘office’
LEFT JOIN employee_phone ph2
ON ph2 .emp_id = e.id AND ph2 .type = ‘mobile’
The tables employees and employee_phone are InnoDB tables; all columns are used in this view.
The contents of the phone_list view are as follows:
Mysql> select * from phone_list;
1 row in set (0.00 sec)
Which method can you use to change the cell_no value to ‘555-8888’ for John Doe?
A. DELETE FROM phone_list WHERE first_name= ‘John’ and last_name= ‘Doe’; INSERT INTO phone_list (first_name, last_name, office_no, cell_no) VALUES (‘John’ , ‘Doe’ , ‘x1234’ , ‘555-8888);
B. INSERT INTO employee_phone (emp_id, phone_no, type) VALUES (1, ‘555-8888’,’mobile’);
C. UPDATE phone_list SET cell_name ‘555-8888’ WHERE first_name= ‘John’ and last_name= ‘Doe’;
D. UPDATE employee_phone SET phone_no= ‘555-8888’ where emp_id=1;
Answer: B
Q10. Consider the Mysql Enterprise Audit plugin.
Which statement is true when you identify a connection event that has used external authentication?
A. The attribute “STATUS” is set to the string EXTERNAL_AUTH.
B. The attribute “PRIV_USER” contains the username.
C. The event type that is given in the attribute “NAME” is EXTERNAL_AUTH.
D. There is no differentiation between native and external authentication events.
E. External authentication is managed through external auditing logs.
F. The “PROXY_PRIV” user shows a username if external authentication is used.
Answer: C