1Z0-883 Online Practice Questions and Answers

A simple master-to-slave replication is currently being used. The following information is extracted from the

SHOW SLAVE STATUS output:

Last_SQL_Error: Error 'Duplicate entry '8' for key 'PRIMARY' ' on query. Default database:

'mydb'. Query: 'insert into mytable VALUES ('8' , 'George') ' Skip_Counter: 0

Retrieved _Gtid_Set: 38f32e23480a7-32a1-c323f78067fd37821: 1-8 Auto _Position: 1

You execute a "SHOW CREATE TABLE mytable" on the slave:

CREATE TABLE `mytable' (

`ID' int(11) NOT NULL DEFAULT `0',

`name' char(10) DEFAULT NULL,

PRIMARY KEY (`ID')

)

The table mytable on the slave contains the following:

You have issued a STOP SLAVE command. One or more statements are required before you can issue a START SLAVE command to resolve the duplicate key error.

Which statement should be used?

A. SET GLOBAL SQL_SKIP_SLAVE_COUNTER=1

B. SET GTID_NEXT="CONSISTENCY"; BEGIN; COMMIT; SET GTID_NEXT=" AUTOMATIC';

C. SET GLOBAL enforce_gtid_consistency=ON

D. SET GTID_EXECUTED="38f32e23480a7-32a1-c323f78067fd37821 : 9";

E. SET GTID_NEXT="38f32e23480a7-32a1-c323f78067fd37821 : 9"; BEGIN; COMMIT; SET GTID_NEXT="AUTOMATIC";

Mysqldump was used to create a single schema backup; Shell> mysqldump u root p sakila > sakila2013.sql

Which two commands will restore the sakila database without interfering with other running database?

A. Mysql> USE sakila; LOAD DATA INFILE `sakila2013.sql';

B. Shell> mysql u root p sakila sakila2013.sql

C. Shell> mysql import u root p sakila sakila2013.sql

D. Shell> mysql u root -p e `use sakila; source sakila2013.sql'

E. Shell> mysql u root p silent < sakila2013.sql

What are three actions performed by the mysql_secure_installation tool?

A. It prompts you to set the root user account password.

B. It checks whether file permissions are appropriate within datadir.

C. It asks to remove the test database, which is generated at installation time.

D. It can delete any anonymous accounts.

E. It verifies that all users are configuration with the longer password hash.

Consider the query:

Mysql> SET @run = 15;

Mysql> EXPLAIN SELECT objective, stage, COUNT (stage) FROM iteminformation

WHERE run=@run AND objective='7.1'

GROUP BY objective,stage

ORDER BY stage;

The iteminformation table has the following indexes; Mysql> SHOW INDEXES FROM iteminformation:

This query is run several times in an application with different values in the WHERE clause in a growing data set.

What is the primary improvement that can be made for this scenario?

A. Execute the run_2 index because it has caused a conflict in the choice of key for this query.

B. Drop the run_2 index because it has caused a conflict in the choice of key for this query.

C. Do not pass a user variable in the WHERE clause because it limits the ability of the optimizer to use indexes.

D. Add an index on the objective column so that is can be used in both the WHERE and GROUP BY operations.

E. Add a composite index on (run,objective,stage) to allow the query to fully utilize an index.

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

Which two requirements would lead towards a high availability solution?

A. When uptime is critical

B. When data must be refactored

C. When application concurrency is static

D. When data loss is unacceptable

E. When application is a single point of failure

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, `--') AS `office_no'

`coalesce (ph2 .phone_no, '--`) 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:

Mysql> 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?

A. 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);

B. INSERT INTO employee_phone (emp_id, phone_no, type) VALUES (1, `555- 8888','mobile');

C. UPDATE phone_list SET cell_name `555-8888' WHERE first_name= `John' and last_name= `Doe';

D. UPDATE employee_phone SET phone_no= `555-8888' where emp_id=1;

Consider the three binary log files bin.00010, bin.00011, and bin.00012 from which you want to restore data.

Which method would use mysqlbinlog for greater consistency?

A. shell> mysqlbinlog bin.00010 | mysql shell> mysqlbinlog bin.00011 | mysql shell> mysqlbinlog bin.00012 | mysql

B. shell> mysqlbinlog bin.00010 bin.00011 bin.00012 | mysql

C. shell> mysqlbinlog restore bin.00010 bin.00011 bin.00012

D. shell> mysqlbinlog include-gtide=ALL bin.00010 bin.00011 bin.00012 | mysql

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

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.