1Z0-883 | Replace 1Z0-883 Exam Study Guides With New Update Exam Questions


Q1. You adjust a default configuration to the following /etc/my.cnf on a Linux installation: 

[mysqld] 

Loq-bin 

Binrylog_format=ROW 

You do not notice the spelling error in binrylog_format and restart your production server. 

How does the MySQL server behave with incorrectly spelled options? 

A. Mysqld uses internal configuration versioning and reverts to the previous configuration. 

B. When using mysql_config_editor for configuration adjustments, it detects incorrect syntax and typing mistakes. 

C. The mysqld_safe script skips the unknown variable and starts using the remaining configuration changes. 

D. Mysqld prints to the error log about an unknown variable, and then exits. 

Answer:

Q2. 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:

Q3. Which query would you use to find connections that are in the same state for longer than 180 seconds? 

A. SHOW FULL PROCESSLIST WHEER Time > 180; B. SELECT * FROM INFORMATION_SCHEMA.EVENTS SHERE STARTS < (DATE_SUB (NOW ( ), INTERVAL 180 SECOND) ); 

C. SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE STATE < (DATE_SUB (NOW ( ), INTERVAL 180 SECOND) ); 

D. SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 180; 

Answer:

Q4. Consider typical High Availability (HA) solutions that do not use shared storage. 

Which three HA solutions do not use shared storage? 

A. Mysql Replication 

B. Distributed Replicated Block Device (DRBD) and Mysql 

C. Windows Cluster and Mysql 

D. Solaris Cluster and Mysql 

E. Mysql NDB Cluster 

Answer: A,C,D 

Q5. You have table ‘apps’,’userdata’ on server that uses MyISAM storage engine. You want to transfer this data to server but use InnoDB engine instead. 

You execute the following commands: 

ServerB commands: 

Shell> mysqldump –u root –h server –no-data apps userdata | mysql –u root –p apps 

Shell> mysql –u root –p –h server –e ‘ALTER TABLE ‘apps’,’userdata’ ENGINE=InnoDB;’ 

Shell> mysqldump –u root –p –h server –no-create-info –order-by-primary apps userdata | mysql –u root –p apps 

What effect does the – order-by-primary argument have on the mysqldump command? 

A. It exports tables with the most indexes first to assist with import speeds. 

B. It ensures that unique indexes have no conflicts when the data is dumped. 

C. It orders by primary key to assist in speeding up importing to InnoDB tables. 

D. It must be specified so index data is dumped correctly when –on-create-info is used. 

Answer:

Q6. 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 

Q7. Assume that you want to know which Mysql Server options were set to custom values. Which two methods would you use to find out? 

A. Check the configuration files in the order in which they are read by the Mysql Server and compare them with default values. 

B. Check the command-line options provided for the Mysql Server and compare them with default values. 

C. Check the output of SHOW GLOBAL VARIABLES and compare it with default values. 

D. Query the INFORMATION_SCHEMA.GLOBAL_VARIABLES table and compare the result with default values. 

Answer:

Q8. 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:

Q9. Which statement is true about FLUSH LOGS command? 

A. It requires the RELOAD, FILE, and DROP privileges. 

B. It closes and reopens all log files. 

C. It closes and sends binary log files to slave servers. 

D. It flushes dirty pages in the buffer pool to the REDO logs. 

Answer:

Reference: http://dev.mysql.com/doc/refman/5.5/en/flush.html 

Q10. In a test database, you issue the SELECT … INTO OUTFILE statement to create a file with your t1 table data. 

You then TRUNCATE this table to empty it. 

Mysql> SELECT * INTO OUTFILE ‘/tmp/t1.sql’ from t1; 

mysql> TRUNCATE t1; 

Which two methods will restore data to the t1 table? 

A. Mysql> LOAD DATA INFILE ‘/tmp/t1.sql’ INTO TABLE t1; 

B. $ mysqladmin – u root – p – h localhost test – restore /tmp/t1.sql 

C. $ mysql – u root – p – h localhost test < /tmp/t1.sql 

D. $ mysqlinport – u root – p – h localhost test /tmp/t1.sql 

E. Mysql> INSERT INTO t1 VALUES FROM ‘/tmp/t1.sql’; 

Answer: