Q1. Which two capabilities are granted with the SUPER privilege?
A. Allowing a client to kill other client connections
B. Allowing a client to shut down the server
C. Allowing change of the server runtime configuration
D. Allowing client accounts to take over the account of another user
Answer: A,B
Reference: http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html
Q2. Which statement is true about the log-output variable?
A. It is a static variable and can be set only at MySQL server startup.
B. It enables and starts the General Query Log.
C. It sets the target location for the binary logs generated by the MySQL sever.
D. It specifies output destinations for the slow and General Query logs.
Answer: D
Q3. Identify a performance impact when using the Performance Schema.
A. There is no impact on performance.
B. There is an overhead for querying the Performance Schema but not for having it enabled.
C. There is a constant overhead regardless of settings and workload.
D. The overhead depends on the settings of the Performance Schema.
Answer: B
Q4. You install a copy of Mysql 5.6.13 on a brand new Linux server by using RPM packages. The server starts successfully as verified by the following commands:
$ pidof mysqld
$tail - n2 /var/lib.mysql/hostname.err
2013-08-18 08:18:38 3132 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘5.6.13-enterprise-commercial-advaced’ socket: ‘/tmp/mysql.sock’ port;
3306 Mysql Enterprise Server – Advanced Edition (Commercial)
You attempt to log in as the root user with the following command:
$mysql –u root
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)
Which statement is true about this scenario?
A. The RPM installation script sets a default password of password for new installations.
B. The local root user must log in with a blank password initially: mysql –u root –p.
C. New security measures mean that the mysql_secure_installation script must be run first on all new installations.
D. The mysql_install_bd post-installation script used – random-password.
Answer: B
Q5. Which two options describe how MySQL Server allocates memory?
A. Each thread allocates memory from a global pool.
B. Global memory resources are allocated at server startup.
C. Thread memory is pre-allocated up to thread_cache_size for performance.
D. Each connection may have its own per-thread memory allocations.
Answer: B,D
Q6. Consider the following:
Mysql> EXPLAIN SELECT * FROM City WHERE Name = ‘Jacksonville’ AND CountryCode = ‘USA’ \G ******************************** 1. row ******************************** Id: 1 Select_type: SIMPLE Table: City Type: ref Possible_keys: name_country_index Key: name_country_index Ref: const, const Rows: 1 Extra: Using where
Which statement best describes the meaning of the value for the key_len column?
A. It shows the total size of the index row.
B. It shows how many columns in the index are examined.
C. It shows the number of characters indexed in the key.
D. It shows how many bytes will be used from each index row.
Answer: C
Q7. Consider the following table:
CREATE TABLE ‘game’ (
‘id’ int (10) unsigned NOT NULL AUTO_INCREMENT,
‘keyword’ varchar (45) DEFAULT NULL,
‘date’ datetime NOT NULL,
PRIMARY KEY (‘id’ , ‘date’),
UNIQUE KEY ‘keyword_idx’ (‘keyword’ , ‘date’)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (TO_DAYS (date) ) (
PARTITION g201301 VALUES LESS THAN (TO_DAYS (‘2013-01-01 00:00:00’) ),
PARTITION g201302 VALUES LESS THAN (TO_DAYS (‘2013-02-01 00:00:00’) ),
PARTITION g201303 VALUES LESS THAN (TO_DAYS (‘2013-03-01 00:00:00’) ),
PARTITION g201304 VALUES LESS THAN (TO_DAYS (‘2013-04-01 00:00:00’) ),
PARTITION gMORES VALUES LESS THAN (MAXVALUE) );
Which method should used to add a new g201305 partition to the table?
A. ALTER TABLE games
REORGANIZE PARTITION (gMORES)
INTO
g01305 VALUES LESS THAN (TO_DAYS (‘2013-05-01 00:00:00’) ),
gMORES VALUES LESS THAN (MAXVALUE) );
B. ALTER TABLE games
ADD PARTITION g201350 VALUES LESS THAN (TO_DAYS (‘2013-05-01 00:00:00’) );
C. ALTER TABLE games
COALESCE PARTITION (gMORES)
INTO
g01305 VALUES LESS THAN (TO_DAYS (‘2013-05-01 00:00:00’) ),
gMORES VALUES LESS THAN (MAXVALUE) );
D. ALTER TABLE games
SPLIT PARTITION (gMORES)
INTO
g201305 VALUES LESS THAN (TO_DAYS (‘2013-05-01 00:00:00’) ),
gMORES VALUES LESS THAN (MAXVALUE) );
E. ALTHER TABLE games
DROP PATITION gMORES,
ADD PARTITION
g201305 VALUES LESS THAN (TO_DAYS (‘2013-05-01 00:00:00’) ),
gMORES VALUES LESS THAN (MAXVALUE) );
Answer: B
Q8. Consider the Mysql Enterprise Audit plugin.
The following event detail is found in the audit log:
<AUDIT_RECORD
TIMESTAMP=”2013-04-09t01:54:17”
NAME=”Connect”
CONNECTION_ID=”3”
STATUS=”1045”
USER=”kate”
PROXY_USER=””
HOST=”localhost”
IP=””
DB=””/>
Which two points can be concluded from the given event?
A. A connection was blocked by a firewall or a similar security mechanism.
B. A connection was attempted via socket rather than TCP.
C. A connection failed because the proxy user privileges did not match the login user.
D. A connection as the user kate was successful.
E. A connection failed due to authentication being unsuccessful.
Answer: D
Q9. 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?
A. -- quick
B. -- skip-buffer
C. -- single-transaction
D. -- tab
Answer: C
Reference: http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html
Q10. You are creating a new server with the same accounts as an existing server. You do this by
importing a mysqldump file of the mysql database.
You test whether the import was successful by using the following commands:
Mysql> select user, host, password from mysql.user;
9 rows in set (0.00 sec)
Mysql> show grants for ‘admin’@’%’;
ERROR 1141 (42000): There is no such grant defined for user ‘admin’ on host ‘%’
Which command will fix this issue?
A. CREATE USER ‘admin’ @’%’;
B. GRANT USAGE ON *.* TO ‘admin’@’%’;
C. FLUSH PRIVILEGES;
D. FLUSH HOST CACHE;
E. UPDATE mysql.user SET Create_user_priv = ‘Y’ WHERE user= ‘admin’;
Answer: C
Reference: http://lists.mysql.com/mysql/218268