1Z0-874 Online Practice Questions and Answers

The account 'ulf'@'localhost' has previously been created with the statement GRANT ALL PRIVILEGES ON *.* TO 'ulf'@'localhost' IDENTIFIED BY 'w01f'

This account should no longer be used, so you issue the following command:

REVOKE ALL PRIVILEGES ON *.* FROM 'ulf'@'localhost'

After executing this statement, you inspect the server and find that no clients are connected using that account. However, the next day you notice that the account 'ulf'@'localhost' is indeed connected to the server.

Why is it still possible for the account 'ulf'@'localhost' to connect to the server?

A. Specifying GRANT ALL PRIVILEGES ON *.* effectively created an administrator account, which cannot be disabled through the REVOKE statement

B. The REVOKE statement used does not remove the USAGE privilege from the account, allowing new connections with that account.

C. The REVOKE command was not followed by a FLUSH PRIVILEGES command, so the account was never actually disabled

D. The ON *.* clause should not be specified when disabling an account

When working with replication, why might you want to stop the SQL thread on the slave while keeping the I/O thread running?

A. So that no changes are made while making backups.

B. So that events for the slave do not get backlogged on the master.

C. So that remaining events can be processed while not receiving new events from the master.

Given the result of the following query:

mysql> SELECT Host, User FROM mysql.user WHERE User='joe';

+-------------+------+

| Host | User |

+-------------+------+

| % | joe |

| example.com | joe |

+-------------+------+

2 rows in set (0.00 sec)

A client connection is established with the username joe from the host example.com.

Assuming that the login is successful, which of the entries shown in the mysql.user table are used to authenticate the client connection for subsequent query executions, and why?

A. The 'joe'@'%' account is used for all authentication, as MySQL will always use the most general host name possible.

B. The 'joe'@'example.com' account is used for all authentication, as MySQL will always use the most specific hostname possible.

C. For every query, the 'joe'@'example.com' account is checked first. If that does not have the proper permissions, 'joe'@'%' will be used as MySQL will check all relevant accounts, with the most specific hostname first.

D. For every query, the 'joe'@'%' account is checked first. If that does not have the proper permissions, 'joe'@'example.com' will be used as MySQL will check all relevant accounts, with the most general hostname first.

Granting access to the mysql database...

A. Should never be done for any users.

B. Should always be done for all users.

C. Should only be done on administrator accounts.

D. Should be done for anyone needing to create views

Which of the following is/are valid reasons to consider using --skip-networking?

A. Your server is not networked to other servers.

B. You do not have a need for remote clients to connect.

C. You have a need for remote clients to connect.

D. You are not going to use replication or clustering.

Should all queries in the slow query log be optimized?

A. Yes.

B. Yes, unless they depend on tables in the mysql database

C. No; A query may have been entered into the slow query log because other, slow queries, prevented it from executing quickly

D. No; Queries that are run rarely are probably not worth the effort of optimization

E. No; Only the very slowest queries should be optimized

Consider the following:

mysql> DESC customers;

+--------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------+------------------+------+-----+---------+----------------+

| id | int(11) unsigned | NO | PRI | NULL | auto_increment |

| name_1 | char(20) | YES | MUL | NULL | |

| name_2 | varchar(20) | YES | | NULL | |

| name_3 | varchar(20) | YES | | NULL | |

| email | varchar(50) | YES | | NULL | |

+--------+------------------+------+-----+---------+----------------+

mysql> EXPLAIN SELECT * FROM customers WHERE name_1 = 'Johnny'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: customers type:

ref

possible_keys: i_name_1_01,i_name_1_02

key: i_name_1_01

key_len: 21

ref: const

rows: 1

Extra: Using where

What can be assumed by looking at the key_len column?

A. No indexes are being used.

B. The full index is being used.

C. The partial index is being used.

D. The longest index is 21 characters long.

Which of the following best describe the effects on performance for the dynamic-row format for MyISAM has?

A. Retrievals are more complex and slower.

B. Retrievals are less complex and are faster.

C. Rows generally take up less disk space than fixed-row format.

D. Rows generally take up more disk space than fixed-row format.

Which of the following best describes why one should use VARCHAR rather than CHAR in InnoDB?

A. The average amount of space used will be less.

B. Generally, there will be less disk I/O during processing.

C. VARCHAR columns provide better transaction support.

D. The optimizer always performs better with VARCHAR columns over CHAR columns.

The type of file system you chose may affect MySQL use and/or performance with regard to...

A. Table opening time.

B. Size limits of files.

C. Time to recover from a crash.

D. The number of users MySQL can handle.

E. The type of storage engines that can be used.