MySQL 5.6 Database Administrator
Question No: 1
A user executes the statement;
PURGE BINARY LOGS TO ‘mysql-bin.010’;
What is the result?
It deletes all binary log files, except ‘mysql-in.010’.
It deletes all binary log files up to and including ‘mysql-bin.010’.
It deletes all binary log files before ‘mysql-bin.010’.
It deletes all binary log files after ‘mysql-bin.010’.
Question No: 2
You use-login-path to access a MySQL server on a Linux installation.
Which statement is true about the – login-path option that is created by using mysql_config_editor?
All system users have access to the MySQL server via-login path local.
login-path can be used only for MySQL servers running on a local machine.
login-path allows you to provide login credentials without passing clear text passwords on the command line.
When using – login-path to connect to a remote MySQL server, the remote server version must be 5.6 or later.
Question No: 3
Review the definition of the phone_list view.
CHEATE OR REPLACE ALGORITHM=MERGE
DEFINER= ‘root’@localhost’ SQL SECURITY DEFINER
VIEW ‘phone_list’ AS SELECT
e . id as id
‘e . first_name AS ‘first_name’ ‘e . last_name AS ‘last_name’
‘coalesce ( ph1.phone_no, ‘-#39;) AS ‘office_no’ ‘coalesce (ph2 .phone_no, #39;-‘) AS ‘cell_no’ FROM employees e
LEFT JOIN employee_phone ph1
ON ph1.emp_id = e.id AND ph1.type = ‘office’ LEFT JOIN employee_phone ph2
ON ph2 .emp_id = e.id AND ph2 .type = ‘mobile’
The tables employees and employee_phone are InnoDB tables; all columns are used in this view.
The contents of the phone_list view are as follows: Mysqlgt; select * from phone_list;
1 row in set (0.00 sec)
Which method can you use to change the cell_no value to ‘555-8888’ for John Doe?
DELETE FROM phone_list WHERE first_name= ‘John’ and last_name= ‘Doe’; INSERT INTO phone_list (first_name, last_name, office_no, cell_no) VALUES (‘John’ , ‘Doe’ , ‘x1234’ , ‘555-8888);
INSERT INTO employee_phone (emp_id, phone_no, type) VALUES (1, ‘555- 8888′,’mobile’);
UPDATE phone_list SET cell_name ‘555-8888’ WHERE first_name= ‘John’ and last_name= ‘Doe’;
UPDATE employee_phone SET phone_no= ‘555-8888’ where emp_id=1;
Question No: 4
You are investigating the performance of the server and see the following information:
->Events_waits_summary_global_by_event_name in the performance schema shows that the wait/synch/mutex/sql/LOCK_table_cache event is dominating other wait events.
->The table_open_cache_overflows status variable is 0.
Which action should be taken to remove the performance bottleneck described here?
Decrease the value of table_definition_cache.
Increase the value of table_definition_cache.
Decrease the value of table_open_cache.
Increase the value of table_open_cache.
Decrease the value of table_open_cache_instances.
Increase the value of table_open_cache_instances.
Explanation: The table_open_cache variable was simply not set high enough. Reference: MySQL Performance: Table Open Cache in 5.6
Question No: 5
Which statement is true about using Microsoft Windows Cluster as a platform for Mysql?
It is provided by means of IP- level disk replication.
It is shared-nothing architecture.
It implements High Availability by using the .NET Connector’s load balancing capabilities.
It relies on the shared disk architecture being visible to both servers.
Question No: 6
Which three statements describe how the strict SQL mode provides added security?
It rejects statements that try to insert out-of-range values
It rejects invalid dates.
It limits the operations that the server can perform.
It rejects queries that produce out-of-range values.
It rejects dates with zero day or month values.
Question No: 7
You want to create a temporary table named OLD_INVENTORY in the OLD_INVENTORY database on the master server. This table is not to be replicated to the slave server.
Which two changes would ensure that the temporary table does not propagate to the slave?
Use the – replicate-do-db, – replicate-do-table, or – replicate-wild-do-table option with the value equal to OLD_INVENTORY.
Change the binlog_format option to ROW and restart mysqld before you create the OLD_INVENTORY table.
Stop SQL_THREAD on the slave until you have finished using the OLD_INVENTORY temporary table.
Set binlog_format=MIXED with the – replicate-ignore-temp-table option.
Use the – replicate-ignore-table option with the value equal to OLD_INENTORY.OLD_INVENTORY and restart mysqld before creating the temporary table.
Question No: 8
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 3132
$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?
The RPM installation script sets a default password of password for new installations.
The local root user must log in with a blank password initially: mysql -u root -p.
New security measures mean that the mysql_secure_installation script must be run first on all new installations.
The mysql_install_bd post-installation script used – random-password.
Question No: 9
Which three statements are characteristic of the MEMORY storage engine?
Each table is represented on disk as an.frm file.
Each table has a corresponding.MYI and .MYD file.
It can support foreign keys.
It cannot contain text or BLOB columns.
Table contents are not saved if the server is restarted.
It can support transactions
Question No: 10
You are using the performance Schema to investigate replication on a slave:
Mysqlgt; SELECT THREAD_ID threads.NAME, SUM (COUNT_STAR) AS Totalcount, SUM (SUM_TIMER_WAIT) AS Totaltime
FROM performance_schema.events_waits_summary_by_thread_by_event_name INNER JOIN performance_schema_threads USING (THREAD_ID)
WHERE threads .NAME LIKE ‘thread/sql/slave\-%’ GROUP BY THREAD_ID, threads.NAME;
Assume that all instruments and consumers are enabled and all threads are instrumented. Which two facts can be concluded from the given output?
At most two schemas are being updated concurrently.
The server needs more cores to use all slave threads.
The slave cannot process the relay log fast enough to use all threads.
The slave is configured with slave_parallel_workers = 4.
Explanation: * To see which instruments have been executed the most times or have taken the most wait time, sort the events_waits_summary_global_by_event_name table on the COUNT_STAR or SUM_TIMER_WAIT column, which correspond to a COUNT(*) or SUM(TIMER_WAIT) value, respectively, calculated over all events
Sets the number of slave worker threads for executing replication events (transactions) in parallel. Setting this variable to 0 (the default) disables parallel execution. The maximum is 1024.
Reference: 22.1 Performance Schema Quick Start ; 220.127.116.11 Replication Slave Options and Variables
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|