Q31. You have the following in your my.cnf configuration file:
[ mysqld]
default_authentication_plugin=sha256_password
You want to create a new user who will be connecting from the IP address 192.0.2.10, and you want to use the authentication plug-in that implements SHA-256 hashing for user account passwords.
Which two statements would create a user named webdesign for this IP address with the password of imbatman using a SHA_256 password hash?

Q32. You are asked to examine user accounts and find:

Which two statements will best secure this environment? (Choose two.)

Q33. Which two statements describe how InnoDB recovery works?

Q34. A MySQL instance has this configuration values set:

A transaction involving a single InnoDB INSERT statement commits.
Which list of locations is in the sequence of disk writes for this transaction?

Q35. A MySQL database uses all InnoDB tables and is configured as follows;

You will be setting up a replication slave by using mysqldump. You will need a consistent backup taken from your running production server. The process should have minimal impact to active database connections.
Which two arguments will you pass to mysqldump to achieve this? (Choose two.)

Q36. Consider that local disk files are accessible via MySQL with commands such as:
mysql> LOAD DATA LOCAL INFILE ‘/etc/passwd’ INTO TABLE mypasswords;
What change could be made to stop any breach via this insecurity?

Q37. An employee cannot access the company database. You check the connection variables:

A look at the user privileges shows:

What is a valid for why one of the users is unable to connect to the database?

Q38. You have just created a replication slave from a backup of the master made with mysqldump:

You try to log in to the slave with the application user, but fail as follows:

The login works on the master.
Which two changes to the process can fix the issue?

Q39. A simple master-to-slave replication is currently being used. This information is extracted from the SHOW SLAVE STATUSoutput:

You execute a ‘SHOW CREATE TABLE mytable” on the slave:

The table mytable on the slave contains:

You have issued a STOP SLAVEcommand. You have determined that it is safe to skip the transaction in this case. One or more statements are required before you can issue a START SLAVEcommand to resolve the duplicate key error. Which statement should be used?

Q40. You need to dump the data from the master server and import it into a new slave server.
Which mysqldump option can be used when dumping data from the master server in order to include the master server’s binary log information?

Q41. You have a MySQL replication setup and you intentionally stop the SQL thread on the slave.

What are two reasons that you may stop the SQL thread on the slave while keeping the I/O thread running?

Q42. You are setting up a new installation of MySQL Server 5.7 (a GA release.) You have used a ZIP or TAR package to ensure that the mysqld binary, along with its support files, such as plug-ins and error messages, now exist on the host. Assume that the default datadir exists on the host. You installed the binary in the default location (the default –basedir value) for your operating system. Which step should you perform before defining your own databases and database tables?

Q43. The MySQL error log shows:
InnoDB: Warning: a long semaphore wait:
The relevant parts of the InnoDB monitor output shows:

Which two options would help avoid the long wait in the future? (Choose two.)

Q44. Exhibit:

What does the possible_keys column in this output denote?

Q45. What are three methods to reduce MySQL server exposure to remote connections?
(Choose three.)

Q46. Consider the CHECK TABLE command.
In which two situations should this command be used? (Choose two.)

Q47. Consider that local disk files are accessible via MySQL with commands such as:
mysql> LOAD DATA LOCAL INFILE `/etc/passwd’ INTO TABLE mypasswords; What change could be made to stop any breach via this insecurity?

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

Q49. A MySQL Server has been running an existing application successfully for six months. The my.cnf is adjusted to contain this additional configuration:

The MySQL Server is restarted without error.
What effect will the new configuration have on existing account?

Q50. How does the InnoDB storage engine handle deadlocks when they are detected?