1Z0-874 Online Practice Questions and Answers

Which of the following statements are required to create a key cache of 4 MB, assign the MyISAM table world. City to it and preload the index?

A. mysql> SET GLOBAL city_cache.key_buffer_size = 4194304;mysql> CACHE INDEX world.City IN city_cache;mysql> LOAD INDEX INTO CACHE world.City;

B. mysql> ALTER TABLE world.city KEY_CACHE = 4194304;

C. mysql> CREATE CACHE FOR world.City SIZE = 4194304;

D. It is not possible to create a key cache for a specific MyISAM table, only the global key cache can be used.

Which of the following describe the correct single-statement syntax for allowing the existing user 'kofi'@'%' to perform SELECT, INSERT, DELETE and UPDATE actions on all tables in the world database?

A. GRANT SELECT, INSERT, DELETE, UPDATE ON world.* TO 'kofi'@'%';

B. GRANT SELECT INSERT DELETE UPDATE ON world.* TO 'kofi'@'%'

C. GRANT world.* (SELECT, INSERT, DELETE, UPDATE) TO 'kofi'@'%'

D. GRANT (SELECT, INSERT, DELETE, UPDATE) ON world.* TO 'kofi'@'%'/TT>

E. Unless using ALL PRIVILEGES, it is not possible to assign more than one type of privilege at any one time.

You need to allow the user 'joe'@'example.com' the privilege to create and alter stored routines in the world database, but you do not want that user to be able to change any table definitions. The user already has the required permissions to access table data in the database.

Which of the following will achieve that?

A. mysql> GRANT ALL ROUTINE PRIVILEGES ON world.* TO 'joe'@'example.com';

B. mysql> GRANT ALL PRIVILEGES ON world.* TO 'joe'@'example.com';

C. mysql> GRANT ALL PRIVILEGES ON PROCEDURE world.* TO 'joe'@'example.com';mysql> GRANT ALL PRIVILEGES ON FUNCTION world.* TO 'joe'@'example.com';

D. mysql> GRANT CREATE ROUTINE ON world.* TO 'joe'@'example.com';mysql> GRANT ALTER ROUTINE ON world.* TO 'joe'@'example.com';

E. You must use GRANT ALL PRIVILEGES ON world.routine1_name, world.routine2_name ... TO 'joe'@'example.com', replacing routine1_name etc. with the names of each of the stored routines the user may want to create or alter

F. This cannot be done; permissions on tables and stored routines are always shared.

In replication the master server logs updates to this/these databases by default: A. No database.

B. The default database

C. The mysql database

D. All databases

E. All databases but mysql

What will be the effect of executing the following statement? REVOKE ALL PRIVILEGES FROM 'web'@'localhost'

A. The account 'web'@'localhost' will have all privileges removed, and the account deleted from the server.

B. The account 'web'@'localhost' will no longer be usable for login, but since there is no ON clause, privileges are retained for re-enabling the account at a later time.

C. The account 'web'@'localhost' will lose all privileges except for USAGE

D. Nothing. A REVOKE statment without an ON clause is invalid

Which of the following best describes the order in which MySQL considers host names and user names when a client attempts connection?

A. Hostnames then usernames

B. Usernames then hostnames

C. Both hostnames and usernames are checked concurrently.

Why is --bind-address an important security option?

A. On a server with multiple network interfaces, you can have mysqld accept connections only on the given interface.

B. mysqld will only accept connections from the given address.

C. mysqld will turn off networking and will only accept local connections

D. mysqld won't accept any connections at all.

How can the strict SQL mode provide added security?

A. By restricting out of range values.

B. By restricting incorrect data types.

C. By limiting the operations that the server can perform.

D. By rejecting actions that do not have values for columns without a default.

Is it possible to tell the MySQL server to throw an error on illegal dates rather than converting it to zero-date or legal dates?

A. Yes, by updating a system variable.

B. No, it has to be done programmatically.

C. Yes, there is a SQL mode that exists for that.

D. No, all illegal date are converted regardless of any setting.

Which of the following statements are true regarding the slow query log?

A. The slow query log can be switched on with the log_slow_queries option.

B. The slow query log is always enabled.

C. The slow query log contains queries that don't utilize any index.

D. To have the slow query log contain queries that don't utilize any index, the log-queries-not- using-indexes option needs to be set.

E. The slow query log will contain queries that take more than 10 seconds.

F. The slow query log will contain queries that take more seconds than configured in the long_query_time option.

G. You can use the mysqldumpslow command line tool to summarize the content of the slow query log.

H. You can use the SHOW SLOW QUERIES statement to display all entries in the slow queries log.

In the following query, the Population column is indexed: mysql> EXPLAIN SELECT Name -> FROM Country -> WHERE Code LIKE '%B%' AND Population > 10000 \G

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

id: 1

select_type: SIMPLE

table: Country type:

ALL possible_keys:

i_pop key: NULL

key_len: NULL

ref: NULL

rows: 239

Extra: Using where

Which of the following best describes how to deal with the key value of the EXPLAIN output?

A. Use FORCE KEY.

B. Use USE INDEX.

C. Use FORCE INDEX.

D. Use USE POSSIBLE_KEY.

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.

Consider the following:

ALTER TABLE City ROW_FORMAT = FIXED

What would be the effect of this statement?

A. Nothing as this is not a valid statement.

B. The table City is changed to a fixed row format.

C. The columns in the table City are changed from VARCHAR to CHAR.

Which of the following optimizations could be made based on data collected in the slow query log?

A. Tune server parameters.

B. Change the storage engines in use.

C. Make changes to the network infrastructure.

D. Rewrite queries to perform more efficiently.

E. Normalize data or add indexes to the tables that the queries use.

Which of the following best describe some possible benefits of using RAID?

A. Better user-level security.

B. Data redundancy.

C. Read performance gains.

D. Write performance gains. E. Hot-swappable disk drives.

E. Better memory and processor utilization.