Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. You have forgotten the root user account password. You decide to reset the password and execute the following:
Shell> /etc/init.d/mysql stop Shell> /etc/init.d/mysql start – skip-grant tables Which additional argument makes this operation safer?
A. --skip-networking, to prohibit access from remote locations
B. --reset-grant-tables, to start the server with only the mysql database accessible
C. --read-only,to set all data to read-only except for super users
D. --old-passwords, to start Mysql to use the old password format while running without the grant tables
Answer: B
Q2. Your developers have created table to store some of their program’s data. After examining the slow Query Log, you see that they are using the LIKE operator and SUBSTER () functions against a VARCHAR (10000) column quite often.
An example of the start of one row of data:
‘GREEN01020495888331993-12-10/2…’
What should you do to improve the overall performance?
A. Convert the column to TEXT and add a fulltext index to the table.
B. Create multiple prefix indexes of differing lengths.
C. Convert their column to BINARY.
D. Redesign the table so that the most commonly searched for string patterns are in their own columns.
Answer: D
Q3. Which three statements are characteristic of the MEMORY storage engine?
A. Each table is represented on disk as an.frm file.
B. Each table has a corresponding.MYI and .MYD file.
C. It can support foreign keys.
D. It cannot contain text or BLOB columns.
E. Table contents are not saved if the server is restarted.
F. It can support transactions
Answer: A,D,E
Q4. A database exists as a read-intensive server that is operating with query_cachek_type = DEMAND.
The database is refreshed periodically, but the resultset size of the queries does not fluctuate.
Note the following details about this environment:
A web application uses a limited set of queries.
The Query Cache hit rate is high.
All resultsets fit into the Query Cache.
All queries are configured to use the Query Cache successfully.
The response times for queries have recently started to increase. The cause for this has correctly been identified as the increase in the number of concurrent users accessing the web service.
Based solely on the information provided, what is the most likely cause for this slowdown at the database level?
A. The Query Cache is pruning queries due to an increased number of requests.
B. Query_cache_min_res_unit has been exceeded, leading to an increased performance overhead due to additional memory block lookups.
C. Mutex contention on the Query Cache is forcing the queries to take longer due to its single-threaded nature.
D. The average resultset of a query is increasing due to an increase in the number of users requiring SQL statement execution.
Answer: C
Q5. 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
Q6. You execute the following statement in a Microsoft Windows environment. There are no conflicts in the path name definitions.
C: \> mysqld – install Mysql56 – defaults – file = C : \my –opts.cnf
What is the expected outcome?
A. Mysqld acts as an MSI installer and installs the Mysql 5.6 version, with the c: \my-opts.cnf configuration file.
B. Mysql is installed as the Windows service name Mysql56, and uses c: \my-opts.cnf as the configuration file
C. An error message is issued because – install is not a valid option for mysqld.
D. A running Mysql 5.6 installation has its runtime configuration updated with the server variables set in c: \my-opts.cnf.
Answer: B
Q7. You are using replication and the binary log files on your master server consume a lot of disk space.
Which two steps should you perform to safely remove some of the older binary log files?
A. Ensure that none of the attached slaves are using any of the binary logs you want to delete.
B. Use the command PURGE BINARY LOGS and specify a binary log file name or a date and time to remove unused files.
C. Execute the PURGE BINARY LOGE NOT USED command.
D. Remove all of the binary log files that have a modification date earlier than today.
E. Edit the .index file to remove the files you want to delete.
Answer: D
Q8. You need to dump the data from the master server and import it into a new slave server.
Which mysqldump option can be used when dumping data from the master server in order to include the master server’s binary log information?
A. Include-master-info
B. Master-binlog
C. Include-log-file
D. Master-data
Answer: D
Q9. Which two events will cause a slave server to create a new relay log file?
A. Starting of the I/O thread
B. Execution of the FLUSH LOGS statement
C. Starting of the SQL thread
D. Reaching the slave_pendign _jobs_size_max limit
E. Execution of FULSH TABLES WITH READ LOCK
Answer: A,B
Reference: http://dev.mysql.com/doc/refman/5.1/en/slave-logs-relaylog.html
Q10. An employee cannot access the company database. You check the connection variables: Mysql> SHOW GLOBAL VARIABLES LIKE ‘%connect%’;
8 rows in set (0.00 sec)
A look at the user privileges shows:
GRANT… TO ‘bob’@’%, example.com’ WITH MAX_USER_CONNECTIONS 0;
GRANT… TO ‘key’@’%, example.com’ WITH MAX_USER_CONNECTIONS 1;
GRANT… TO ‘joe’@’%, example.com’ WITH MAX_USER_CONNECTIONS 50;
What is a valid explanation for why one of the users is unable to connect to the database?
A. Bob has max_user_connections set to zero, which blocks all his connections
B. Joe has exceeded the max_user_connections global limit.
C. All users are blocked because max_user_connections is accumulated over the host account information.
D. Kay is already connected elsewhere and attempting to log in again.
E. Connect_timeout is too small to allow a connection to occur.
Answer: A
Q11. 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?
A. SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE HOST=’192.168.1.15’;
B. SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE HOST=’ 192.168.1.15’;
C. SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE HOST=’ 192.168.1.15’;
D. SELECT * FROM INFORMATION_SCHEMA.INNODB_METEICS WHERE HOST=’ 192.168.1.15’;
Answer: C
Q12. What are three facts about backups with mysqldump?
A. Can back up a remote database server
B. Allow a consistent backup to be taken
C. Are always faster to restore than binary backups
D. Are able to back up specific items within a database
E. Create automatically compressed backups
F. Will lock all storage engines for duration of backup
Answer: D,E,F
Q13. A MySQL replication slave is set up as follows:
User 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 the following entries:
2013-08-27 13:55:44 9056 [ERROR] Slave SQL: Could not execute Write_rows event on table test.tl; Duplicate entry ‘3’ for key’PRIMARY’ , Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event’s master log 56_master-bin.000003, end_log_pas 653,
Error_code: 1062
2013-08-27 13:55:44 9056 [Warning] Salve: Duplicate entry ‘3’ for key ‘PRIMARY’
Error_code: 1062 2013-08-27 13:55:44 9056 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”, We stopped at log ‘56_master-bin.000003’ position 496
What are two possible causes for this error to occur?
A. The slave was created with mysqldump –u root –p – skip-lock-table—all-databases > /data/data.sql
B. The slave user does have INSERT, UPDATE, or DELETE permission and cannot execute the write_rows function.
C. For tables with UNIQUE keys, statement-based replication must be used maintain integrity.
D. The root user on the slave has executed FLUSH LOGS, causing the relay-log to doublewrite.
E. The applications have the SUPER privilege, which allows them to update rows.
Answer: A,E
Q14. Full Atomicity, Consistency, Isolation, Durability (ACID) compliance is a necessity for a new
application, which heavily reads and writes data.
This requires the following config file options:
Sync_binlog=1
Innodb_flush_log_at_trx_commit=1
Innodb_doublewrite=1
However, this configuration is expected to introduce disk I/O overhead.
What three changes will reduce disk I/O overheads?
A. Use of soft links for database directories on the same physical disk
B. Use of separate directories on the same physical disk for log files and data files
C. Placement of InnoDB log files and datadir on separate physical disks
D. Allocation of RAM to the buffer pool such that more of the data can fit in RAM
E. Use of delay_key_write=ON for batch index update
Answer: D,E
Q15. Consider the Mysql Enterprise Audit plugin.
You are checking user accounts and attempt the following query:
Mysql> SELECT user, host, plugin FROM mysql.users;
ERROR 1146 (42S02): Table ‘mysql.users’ doesn’t exist
Which subset of event attributes would indicate this error in the audit.log file?
A. NAME=”Query”
STATUS=”1146”
SQLTEXT=”select user,host from users”/>
B. NAME=”Error”
STATUS=”1146”
SQLTEXT=”Error 1146 (42S02): Table ‘mysql.users’ doesn’t exist”/>
C. NAME=”Query”
STATUS=”1146”
SQLTEXT=” Error 1146 (42S02): Table ‘mysql.users’ doesn’t exist”/>
D. NAME=”Error”
STATUS=”1146”
SQLTEXT=”select user,host from users”/>
E. NAME=”Error”
STATUS=”0”
SQLTEXT=”Error 1146 (42S02): Table ‘mysql.users’ doesn’t exist”/>
Answer: C