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";

Which two statements are true about InnoDB auto-increment locking?

A. The auto-increment lock can be a table-level lock.

B. InnoDB never uses table-level locks.

C. Some settings for innodb_autoinc_lock_mode can help reduce locking.

D. InnoDB always protects auto-increment updates with a table-level lock.

E. InnoDB does not use locks to enforce auto-increment uniqueness.

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.

Consider the events_% tables in performance Schema.

Which two methods will clear or reset the collected events in the tables?

A. Using DELETE statements, for example, DELETE FROM performance_schema.events_watis_current;

B. Using the statement RESET PERFORMANCE CACHE;

C. Using the statement FLUSH PERFORMANCE CACHE;

D. Using TRUNCATE statements, for example, TRUNATE TABLE performance_schema.events_waits_current;

E. Disabling and re-enabling all instruments

F. Restarting Mysql

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.

You want to start monitoring statistics on the distribution of storage engines that are being used and the

average sizes of tables in the various databases.

Some details are as follows:

The Mysql instance has 400 databases.

Each database on an average consists of 25-50 tables.

You use the query:

SELECT TABLE_SCHEMA, ‘ENGINE’, COUNT (*), SUM (data_length) total_size

FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ GROUP BY TABLE_SCHEMA, ‘ENGINE’ ;

Why is this query slow to execute?

A. Counting and summarizing all table pages in the InnoDB shared tablespace is time consuming.

B. Collecting information requires various disk-level operations and is time consuming.

C. Aggregating details from various storage engine caches for the final output is time consuming.

D. Collecting information requires large numbers of locks on various INFORMATION_SCHEMA tables.

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';

You are creating a new server with the same accounts as an existing server. You do this by importing a

mysqldump file of the mysql database.

You test whether the import was successful by using the following commands:

Mysql> select user, host, password from mysql.user;

9 rows in set (0.00 sec)

Mysql> show grants for `admin'@'%';

ERROR 1141 (42000): There is no such grant defined for user `admin' on host `%'

Which command will fix this issue?

A. CREATE USER `admin' @'%';

B. GRANT USAGE ON *.* TO `admin'@'%';

C. FLUSH PRIVILEGES;

D. FLUSH HOST CACHE;

E. UPDATE mysql.user SET Create_user_priv = `Y' WHERE user= `admin';

You have a server that has very limited memory but has a very large table.

You will use mysqldump to back up this table.

Which option will ensure mysqldump will process a row at a time instead of buffering a set of rows?

A. -- quick

B. -- skip-buffer

C. -- single-transaction

D. -- tab

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