Q1. What are four capabilities of the mysql client program?
A. Creating and dropping databases
B. Creating, dropping, and modifying tables and indexes
C. Shutting down the server by using the SHUTDOWN command
D. Creating and administering users
E. Displaying replication status information
F. Initiating a binary backup of the database by using the START BACKUP command
Answer: B,D,E,F
Q2. A Mysql instance is running on a dedicated server. Developers access the server from the same network subnet. Users access the database through an application that is running on a separate server in a DMZ.
Which two will optimize the security of this setup?
A. Disabling connections from named pipes or socket files (depending on the operating system of the server)
B. Running the server with – skip-networking specified
C. Limiting logins to originate from the application server or the server’s subnet
D. Starting the server with – bind- address=0.0.0.0 specified
E. Installing Mysql on the application server, and running the database and application on the same server
F. Enabling and using SSL for connections to the Mysql database
Answer: E,F
Q3. You inherit a legacy database system when the previous DBA, Bob, leaves the company. You are notified that users are getting the following error:
mysql> CALL film_in_stock (40, 2, @count);
ERROR 1449 (HY000): The user specified as a definer (‘bon’@’localhost’) does not exist
How would you identify all stored procedures that pose the same problem?
A. Execute SELECT * FROM mysql.routines WHERE DEFINER=’bob@localhost’;.
B. Execute SHOW ROUTINES WHERE DEFINER=’bob@localhost’.
C. Execute SELECT * FROM INFORMATION_SCHEMA. ROUTINES WHERE DEFINER=’bob@localhost’;.
D. Execute SELECT * FROM INFORMATION_SCHEMA. PROCESSLIST WHERE USER=’bob’ and HOST=’ localhost’;.
E. Examine the Mysql error log for other ERROR 1449 messages.
Answer: D
Q4. You examine the output of SHOW GLOBAL STATUS and notice that the value of Created_tmp_disk_tables is consistently increasing.
Which two variables would likely fix this issue?
A. Table_open_cache
B. Table_open_cache_instancs
C. Table_definition_cache
D. Tmp_table_size
E. Max_heap_table_size
F. Max_tmp_tables
Answer: D,E
Q5. 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
Q6. In a design situation, there are multiple character sets that can properly encode your data. Which three should influence your choice of character set?
A. Disk usage when storing data
B. Syntax when writing queries involving JOINS
C. Comparing the encoded data with similar columns on other tables
D. Memory usage when working with the data
E. Character set mapping index hash size
Answer: C,D,E
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. 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
Q9. While reviewing the MySQL error log, you see occasions where MySQL has exceeded the number of file handles allowed to it by the operating system.
Which method will reduce the number of file handles in use?
A. Disconnecting idle localhost client sessions
B. Implementing storage engine data compression options
C. Relocating your data and log files to separate storage devices
D. Activating the MySQL Enterprise thread pool plugin
Answer: A
Q10. 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