Q89. Examine the mydata table and SELECT statements:

You issue:
mysql> begin;
mysql> update mydata set a=0 where b=3;
How many rows are now protected by locks with the default InnoDB configuration?

Q90. You are no longer able to log in to an existing MySQL Server because the root password credentials not working. You need to reset the root password to complete various administrative tasks. What are the two major methods that will achieve this? (Choose two.)

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

Q92. 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 STATUS indicates these values:
* RELAY_LOG_FILE=hostname-relay-bin.00004
* RELAY_LOG_POS=1383
Which command set would make the slave current?

Q93. At which stage during query processing does MySQL create a query’s execution plan?

Q94. Which two are considered good security practices when using passwords? (Choose two.)

Q95. Suppose you are adding rows to a MyISAM table and the –datadirlocation runs out of disk space.
What will happen when this occurs?

Q96. You have a server that has very limited memory but has a very large table.
You will use mysqldump to back up this table.
Which option will ensure mysqldump will process a row at a time instead of buffering a set of rows?

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

Q98. You have set innodb_stats_auto_recalc to OFF.
For tables using persistent statistics, what is the outcome of this change?

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

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

Q101. What is the order of tables shown in an EXPLAINoutput?

Q102. Consider the two partial outputs of the SHOW GLOBAL VARIABLES command from a master and slave server:
Master:

Slave:

There is a problem with the slave replicating from the master. Which statement describes the cause of the problem?

Q103. Consider the table people with the 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?

Q104. Multiversion Concurrency Control (MVCC) enables what type of backup?