Latest Exam Prep

Q55. Which three options are most likely to be changed for production form their default values?

Q56. A MySQL replication slave is set up as follows:
Uses all InnoDB tables

Receives ROW-based binary logs

Has the read-only option

The replication slave has been found in an error state.
You check the MySQL error log file and find these entries:

What are two possible causes for this error to occur? (Choose two.)

Q57. Which three allocate memory per thread in MySQL? (Choose three.)

Q58. You are investigating the performance of the server and see this 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?

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

Q60. Which MySQL utility program should you use to process and sort the Slow Query Log based on query time or average query time?

Q61. Consider the key buffer in a MySQL server. Which two statements are true about this feature?

Q62. The /myfolder/my.cnffile has option set:
[mysqld]
skip-log-bin
myfolder2/my.cnfhas this option set:
/
[ mysqld]
log-bin = /valid/path/to/mysqlbinlog
All mentioned paths are accessible to the account that you are currently using. Assume that any other options mentioned in either file are valid and legal option definitions.
You start an instance by using this command line:
mysqld –defaults-file=/myfolder/my.cnf –defaults-extra-file=/myfolder2/my.cnf What is the outcome?

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

Q64. Consider:

Which statement best describes the meaning of the value for the key_len column?

Q65. Due to an authentication plug-in that is used on the server, passwords are required to be sent as clear text as opposed to the usual encrypted format.
Which two methods would allow the mysql client to connect to the server and send clear text passwords?

Q66. You will configure a MySQL Server to act as a replication master. Which two options must be configured correctly to allow this?

Q67. You have a config file for a running DB with this excerpt:
[mysqld]
tmp_table_size=16M
sort_buffer_size=256k
To address a query performance problem of connecting to the DB from an application on another host, you log in and make these changes to the DB:
mysql> SET GLOBAL tmp_table_size=32000000;
mysql> SET sort_buffer_size=2000000;
This solves the problem with your queries. However, later the DB instance is restarted and the performance problem returns.
Which three best describe this scenario?

Q68. You have installed MySQL Server for the first time on your system. However, the data directory along with the tables in the mysql system database are missing. Which step do you perform to create the contents of the data directory?

Q69. Consider the table people with this definition:

The application uses a query such as:
SELECT * FROM people WHERE YEAR(Birthday) = 1980;
The query is not using an index.
Which two methods can be used to allow the query to use an index? (Choose two.)

Q70. The /myfolder/my.cnf file has option set:
[mysqld]
skip-log-bin
/myfolder2/my.cnf has this option set:
[mysqld]
log-bin = /valid/path/to/mysqlbinlog
All mentioned paths are accessible to the account that you are currently using. Assume that any other options mentioned in either file are valid and legal option definitions.
You start an instance by using this command line:
mysqld –defaults-file=/myfolder/my.cnf –defaults-extra-file=/myfolder2/my.cnf What is the outcome?

Q71. An admin attempts to enforce stronger security by using these commands:

The admin then leaves the system running with the specified changes. What are two remaining security concerns? (Choose two.)

Q72. There are multiple instances of MySQL Server running on a single OS that is backed up using the mysqlbackupcommand.
The /etc/my/cnf contains default values, for example, datadir=/var/lib/mysql/,with extra instances having their own separate my.cnffile (for example /etc/mysql/instanceN.cnf) overriding the defaults.
A restore of the second instance is attempted from the mysqlbackup archive using this command:
mysqlbackup –backup-dir=/opt/backup/mysql/instance2 copy-back
Upon starting the second MySQL instance, you notice that the data does not match the expected backup.
Which command-line option is required to successfully update the second instance?

Q73. You attempt to connect to a MySQL Server by using the mysql client program. However, you receive this notice:

What would you run to fix the issue?

Q74. You have forgotten the root user account password. You decide to reset the password and execute:

Which additional argument makes this operation safer?

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

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

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

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

The table mytable on the slave contains:

You have issued a STOP SLAVE command. 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 SLAVE command to resolve the duplicate key error. Which statement should be used?

Q77. You are investigating the performance of a query which selects data from an InnoDB table.
Consider this Performance Schema diagnostics output for the query:

Which statement is true about the output?

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

Q79. An existing master-slave setup is currently using a delayed replication of one hour. The master has crashed and the slave must be “rolled forward” to provide all the latest data.
The SHOW SLAVE STATUSindicates these values:
RELAY_LOG_FILE=hostname-relay-bin.00004

RELAY_LOG_POS=1383

Which command set would make the slave current?