1Z0-888 Online Practice Questions and Answers

Which two options describe how MySQL Server allocates memory? (Choose two.)

A. Each connection may have its own per-thread memory allocations.

B. Thread memory is pre-allocated up to thread_cache_size for performance.

C. Each thread allocates memory from a global pool.

D. Global memory resources are allocated at server startup.

Consider the index information for the dept_emp table in the employee's schema:

Which two conclusions can be made based on the output of the query? (Choose two.)

A. There are three indexes on the table.

B. There is a redundant index on the dept_no column.

C. The secondary indexes are optimized for unique key look-ups.

D. The values on the emp_no column must be unique.

E. The selectivity of the dept_no column is the best of the indexed columns.

F. There is a redundant index on the emp_no column.

Which are three facts about backups with mysqldump? (Choose three.)

A. will lock all storage engines for duration of backup

B. can back up a remote database server

C. allow a consistent backup to be taken

D. are able to back up specific items within a database

E. create automatically compressed backups

F. are always faster to restore than binary backups

Which three tasks are handled by the optimizer? (Choose three.)

A. Decide which indexes to use.

B. Rewrite the WHERE clause.

C. Parse the query.

D. Change the order in which the tables are joined.

E. Validate the query.

F. Execute the query.

G. Verify that the user is allowed to execute the query.

You back up by using mysqldump.

Which configuration is required on the MySQL Server to allow point-in-time recovery?

A. binlog_format=STATEMENT

B. log-bin

C. apply-log

D. bonlog_format=ROW

E. gtid_enable

A simple master-to-slave replication is currently being used. This information is extracted from the SHOW SLAVE STATUS output:

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

The table mytable on the slave contains:

You have issued a STOP SLAVE command. You have determined that it is safe to skip the transaction in

this case. 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 GTID_NEXT="CONSISTENCY"; BEGIN; COMMIT; SET GTID_NEXT="AUTOMATIC";

B. SET GTID_NEXT="5da6b4f5-6f60-11e8-b2d6-0010e05f3e06:8"; BEGIN; COMMIT; SET GTID_NEXT="AUTOMATIC";

C. SET GLOBAL SQL_SKIP_SLAVE_COUNTER=1

D. SET GLOBAL enforce_gtid_consistency=ON

E. SET GTID_EXECUTED="5da6b4f5-6f60-11e8-b2d6-0010e05f3e06:8";

The MySQL error log shows:

InnoDB: Warning: a long semaphore wait:

The relevant parts of the InnoDB monitor output shows: Which two options would help avoid the long wait in the future? (Choose two.)

A. Increase the value of the innodb_lock_wait_timeout option.

B. Increase the value of the innodb_read_io_threads option.

C. Change the table to use HASH indexes instead of BTREE indexes.

D. Set the value of innodb_adaptive_hash_index to zero.

E. Deactivate the query cache.

F. Increase the size of the InnoDB buffer pool.

You attempt to connect to a MySQL Server by using the mysql client program. However, you receive this notice:

What would you run to fix the issue?

A. the mysql_upgrade script

B. the mysql client with the --ignore-password-hashing option

C. the mysql_secure_installation script to update server security settings

D. the mysql client with the --enable-cleartext-plugin option

E. the install plugin command for the mysql_cleartext_password plugin

You are remotely logged in to MySQL as the user "backup".

The account has temporarily been granted full privileges WITH GRANT to perform the actions required.

You want to change the password for the locally existing "backup" user.

Select the three commands which will perform the required action. (Choose three.)

A. SET PASSWORD FOR `backup'@'localhost' = `password';

B. SET PASSWORD = `password';

C. SET PASSWORD FOR `backup'@'localhost' = PASSWORD (`password')

D. ALTER USER USER() IDENTIFIED BY `password';

E. CREATE USER `backup'@'localhost' IDENTIFIED BY `password';

F. ALTER USER `backup' IDENTIFIED BY `password';

G. ALTER USER `backup'@'localhost' IDENTIFIED BY `password';

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? (Choose two.)

A. enabling and using SSL for connections to the MySQL database

B. running the server with –-skip-networking specified

C. disabling connections from named pipes or socket files (depending on the operating system of the server)

D. starting the server with –-bind-address=0.0.0.0 specified

E. limiting logins to originate from the application server or the server's subnet

F. installing MySQL on the application server, and running the database and application on the same server

You are using GTIDS in replication. You need to skip a transaction with the GTID of aaa-bbb-ccc-dddeee:3 on a slave.

Which procedure would you execute from a MySQL prompt?

A. Option A

B. Option B

C. Option C

D. Option D

Consider a replication setup where a snapshot of the replication status shows: Which statement is true?

A. The melbourne channel is resolving a GTID conflict.

B. The sydney channel is keeping up with the received events.

C. The melbourne channel is keeping up with the received events.

D. The last event received from the sydney channel was at 2018-05-08 14:55:01.

E. The replication slave uses multisource replication with eight sources.

The InnoDB tablespace is corrupted and you start the server with option --innodb_force_recover=4. Which backup method would you use to reload the corrupted InnoDB tables?

A. a binary backup that uses transportable tablespaces and allows you to reload a corrupted table

B. A text backup. A binary backup will still contain the corrupted segments.

C. A text backup. InnoDB does not allow you to make binary backups while the server is running.

D. a binary backup taken with MySQL Enterprise Backup that can back up even corrupted tables

You have installed MySQL on Oracle Linux 7 using the official RPM distribution, and SELinux is set to "permissive".

Examine this extract of the my.cnf file:

You issue this command to start MySQL Server:

systemctl start mysqld@replica

On which port is the server listening?

A. 3301

B. 3303

C. 3302

D. 3304

Given these table definitions and the explain plan: Which ALTER TABLE statement will improve the performance of the query?

A. ALTER TABLE `CountryLanguage` ADD INDEX `idx_Lang` (`Language`);

B. ALTER TABLE `Country` ADD INDEX `idx_Code` (`Code`);

C. ALTER TABLE `CountryLanguage` ADD INDEX `idx_OffLang` (`IsOfficial`, `Language`);

D. ALTER TABLE `Country` ADD INDEX `idx_NameCont` (`Name`, `CountryCode`);