MySQL 5.6 Database Administrator
Question No: 11
The InnoDB engine has a feature known as clustered indexes.
Which three statements are true about clustered indexes as used in InnoDB?
A primary key must exist for creation of a clustered index.
A primary key is used as a clustered index.
A clustered index is a grouping of indexes from different tables into a global index for faster searching.
If no indexes exist, a hidden clustered index is generated based on row IDs.
A clustered index provides direct access to a page containing row data.
The first unique index is always used as a clustered index and not a primary key.
A clustered index allows fulltext searching within InnoDB,
Question No: 12
Which hardware storage option, when set up with redundant disks, offers the least stability,
availability, and reliability for Mysql data?
SAN (Storage Area Network)
NFS (Networked File System)
Question No: 13
What are three actions performed by the mysql_secure_installation tool?
It prompts you to set the root user account password.
It checks whether file permissions are appropriate within datadir.
It asks to remove the test database, which is generated at installation time.
It can delete any anonymous accounts.
It verifies that all users are configuration with the longer password hash.
Reference: http://prefetch.net/blog/index.php/2006/06/18/securing-mysql-installations-with- mysql_secure_installation/
Question No: 14
You have forgotten the root user account password. You decide to reset the password and execute the following:
Shellgt; /etc/init.d/mysql stop
Shellgt; /etc/init.d/mysql start – skip-grant tables
Which additional argument makes this operation safer?
-skip-networking, to prohibit access from remote locations
-reset-grant-tables, to start the server with only the mysql database accessible
-read-only,to set all data to read-only except for super users
-old-passwords, to start Mysql to use the old password format while running without the grant tables
Explanation: https://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html under section – B.126.96.36.199 Resetting the Root Password: Generic Instructions
Question No: 15
Which statement is true about FLUSH LOGS command?
It requires the RELOAD, FILE, and DROP privileges.
It closes and reopens all log files.
It closes and sends binary log files to slave servers.
It flushes dirty pages in the buffer pool to the REDO logs.
Question No: 16
Which two requirements would lead towards a high availability solution?
When uptime is critical
When data must be refactored
When application concurrency is static
When data loss is unacceptable
When application is a single point of failure
Question No: 17
You are having problems with connections from a specific host (192.168.1.15) not closing down correctly. You want to find the state of the threads from that host check for long- running queries.
Which statement will accomplish this?
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE HOST=’192.168.1.15′;
SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE HOST=’ 192.168.1.15′;
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE HOST=’ 192.168.1.15′;
SELECT * FROM INFORMATION_SCHEMA.INNODB_METEICS WHERE HOST=’ 192.168.1.15′;
Question No: 18
Consider the MySQL Enterprise Audit plugin.
On attempting to start the MySQL service after a crash, notice the following error: [ERROR] Plugin ‘audit_log’ init function returned error.
In the audit log file, you notice the final entry:
NAME=”Connect” CONNECTION_ID=”98″ STATUS=”0″ USER=”Kate”
What action should you take to fix the error and allow the service to start?
Re-install the audit plugin.
Execute the command FLUSH LOGS.
Execute the command SET GLOBAL audit_log_fiush= ON.
Move or rename the existing audit.log file.
Question No: 19
You have enabled the Slow Query Log for a short period.
When you process the Slow Query Log, you receive the following snip of output:
Count: 100 Time=0 .22a (22s) Lock=0.00s (0s) Rows=0.0 (0), root[root] @localhost CREATE TABLE ‘t1’ (id serial,id0 varchar(N) unique key,intcaoll INT (N)
,intco12 INT(N) ,intco13 INT(N) ,intco14 INT(N) ,intco15 INT(N)
,charcol1 VARVHAR(N) ,charcol2 VARCHAR(N) charcol3 VARCHAR (N)
,charcol4 VARVHAR(N) ,charcol5 VARCHAR(N) charcol6 VARCHAR (N)
,charcol7 VARVHAR(N) ,charcol8 VARCHAR(N) charcol9 VARCHAR (N) .charcol 10 VACHAR (N) )
Count: 64000 Time-0.02s (1213s) Lock=0.00s (6s) Rows=1.0 (64000), root [root]@ localhost
SELECT intocl1, intco12, intco13, intco14, intco15, intco16,intco17, intco18
,intcol9, intcol10, charcol1, charcol2, charcol3, charcol4, charcol5, charcol6
,charcol7, charcol8, charcol9, charcol10 FROM t1 WHERE id = ‘s’
Count: 1 Time=0.02s (0s) Lock=0.00s (0s) Rows=1.0 (1) agent [agent] @localhost
SELECT Select_priv, Repl_client_priv, Show_db_priv, Super_priv,
Process_priv FROM mysql.user WHERE CONCAT (user, ‘s’, host) = CURRENT_USER ()
Count: 48000 Time=0.02s (778s) Lock=0.00 (3s) Rows=1.0 (48000), root[root]@localhost SELECT intocl1,intcol2,intcol3, intcol4, intcol5, charcol1, charcol2, charcol3
,charcol4, charcol5, charcol6, charcol7, charcol8, charcol9, charcol10 FROM t1 WHERE id
You want to tune the query such that it provides the greatest overall time savings. Which query will accomplish this?
CHEATE TABLE ‘t1’ (id serial, id0 varchar (N) unique key, intcol1 INT (N)
,intcol2 INT (N), intcol3 INT(N) ,intcol4 INT(N), intcol5 INT(N), charool1 VARCHAR (N)
,charcol2 VARCHAR (N), charcol3 VARCHAR(N), charcol4 VARCHAR(N), charcol5 VARCHAR (N)
,charcol6 VARCHAR (N), charcol7 VARCHAR(N), charcol8 VARCHAR(N), charcol9 VARCHAR (N)
,charcol10 VARCHAR (N);
SELECT intcol1, intcol2, intcol3, intcol4, intcol5, intcol6, intcol7, intcol8, intcol9, Intcol10, intcol11, intcol12, intcol13, intcol14, intcol15, intcol16, intcol17, intcol18, intcol19, charcol10
FROM t1 WHERE id = ‘s’;
SELECT Select_priv, Repl_client_priv, Show_db_priv, Super_priv, Process_priv FROM mysql.user
WHERE CONCAT (user,’s’, host) = CURRENT_USER();
SELECT intcol1, intcol2, intcol3, intcol4, intcol5, charcol1, charcol2, charcol3, charcol4, charcol5, charcol6, charcol7, charcol8, charcol9, charcol10
FROM t1 WHERE id = ‘s’;
Question No: 20
Consider the Mysql Enterprise Audit plugin.
The following event detail is found in the audit log:
NAME=”Connect” CONNECTION_ID=”3″ STATUS=”1045″
Which two points can be concluded from the given event?
A connection was blocked by a firewall or a similar security mechanism.
A connection was attempted via socket rather than TCP.
A connection failed because the proxy user privileges did not match the login user.
A connection as the user kate was successful.
A connection failed due to authentication being unsuccessful.
Answer: B,E Explanation: B: lt;IPgt;
A string representing the client IP address. This element appears only if the lt;NAMEgt; value is Connect, Change user, or Query.
E: ERROR 1045 (28000): Access denied for user
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|