Black Friday Special Sale Limited Time 70% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: buysanta

Exact2Pass Menu

Login / Register

Question # 4

You want to immediately stop access to a database server for remote user ‘mike’@’client.example.com’. This user is currently not connected to the server.

Which two actions can you take to stop any access from the user?

A.

Use ALTER USER ‘mike’@’client.example.com’ PASSWORD EXPIRE;

B.

Use REVOKE ALL PRIVILEGES FROM ‘mike’@’client.example.com’;

C.

Execute the mysql_secure_installation command.

D.

Use DROP USER ‘mike’@’client.example.com’;

E.

Use GRANT USAGE ON *.* TO ‘mike’@’client.example.com’ MAX_USER_CONNECTIONS=0;

F.

Use ALTER USER ‘mike’@’client.example.com’ ACCOUNT LOCK;

Full Access
Question # 5

Consider these global status variables:

Which two conclusions can be made from the output? (Choose two.)

A.

There are 140 Performance Schema threads at the time of the output.

B.

There are 510 connections to MySQL at the time of the output.

C.

The thread cache has been configured with thread_cache_size set to at least 6.

D.

There are more connections being idle than executing queries.

E.

All max_connections were in use at 2018-03-22 14:54:06

Full Access
Question # 6

After analysis on the slow query log on a high-end OLTP service, the table identified in the slow queries is:

What are the two most likely reasons for the slowness given this output? (Choose two.)

A.

Date should be a TIMESTAMP field for better performance.

B.

The User field is too long for most names.

C.

The engine type is not appropriate to the application use.

D.

Using default values for DATETIME causes table scans.

E.

No indexes are defined.

Full Access
Question # 7

You are investigating the performance of the server and see this 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?

A.

Increase the value of table_open_cache_instances.

B.

Decrease the value of table_definition_cache.

C.

Decrease the value of table_open_cache.

D.

Increase the value of table_definition_cache.

E.

Increase the value of table_open_cache.

F.

Decrease the value of table_open_cache instances.

Full Access
Question # 8

Which two statements are true regarding the creating of new MySQL physical and logical backups? (Choose two.)

A.

Physical backups can be used to recover from data corruption.

B.

Logical backups are human-readable whereas physical backups are not.

C.

Logical backups are always larger than physical backups.

D.

Physical backups are usually slower than text backups.

E.

Physical backups are usually faster than text backups.

Full Access
Question # 9

Consider the two partial outputs of the SHOW GLOBAL VARIABLES command from a master and slave server:

Master:

Slave:

There is a problem with the slave replicating from the master. Which statement describes the cause of the problem?

A.

The log_bin variable is set to OFF on the slave.

B.

server_id is not unique.

C.

The max_connections variable on the slave needs to be increased.

D.

The shared_memory_base_name variable must match the master.

E.

The version of the slave is newer that the version of the master.

Full Access
Question # 10

Examine the mysqldumpslow output:

Which two options could explain the slow query? (Choose two.)

A.

There is network congestion between client and server.

B.

No index has been defined on the filtered column.

C.

There are 108 queries still being executed.

D.

A table lock is causing delays.

E.

A full table scan is being used.

Full Access
Question # 11

You are using the Performance Schema to investigate replication on a slave which has a single master. The option slave-parallel-type is set to DATABASE.

Assume that all instruments and consumers are enabled and all threads are instrumented.

Which two facts can be concluded from the given output? (Choose two.)

A.

The slave has two intermediate relay slaves connected to it.

B.

The slave is configured with slave_parallel_workers = 4

C.

At most, two schemas are being updates concurrently.

D.

THREAD_ID 21 has stopped running.

E.

The slave cannot process the relay log fast enough to use all threads.

F.

The server needs more cores to use all slave threads.

Full Access
Question # 12

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.

Full Access
Question # 13

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?

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.

Full Access
Question # 14

Force Majeure is a catastrophic failure on a major level of the database operation. Regular backups are key to helping avoid data loss in such situations.

Which two other steps can help avoid data loss in a major catastrophe? (Choose two.)

A.

Implement a failover strategy to another geographic location.

B.

Create a master-master pair for each service.

C.

Have a second data centre in a different region or country.

D.

Keep software updated to the latest version.

E.

Use RAID 10 storage for data.

F.

Use on-site network-attached storage to separate service from data.

Full Access
Question # 15

You are asked to examine user accounts and find:

Which two statements will best secure this environment? (Choose two.)

A.

DROP USER ‘’@’localhost’;

B.

ALTER USER ‘’@’localhost’ PASSWORD = ‘secret_password’;

C.

ALTER USER bob@’%’ IDENTIFIED BY ‘secret_password’;

D.

ALTER USER ‘root’@’localhost’ ACCOUNT LOCK;

E.

ALTER USER bob PASSWORD = ‘secret_password’;

F.

REVOKE ALL PRIVILEGES FROM ‘’@’localhost’;

Full Access
Question # 16

Which statement is correct about how InnoDB storage engine uses disk space?

A.

It stores data in .MYD files and its index and undo information in the common tablespace.

B.

It stores data in .MYD files, index information in .MYI files, and undo information the common tablespace.

C.

It stores its data in tablespace file(s). Index and data dictionary details are stored in .FRM files.

D.

It stores its data, index and undo information in .MYD and .MYI files.

E.

It stores data, index and undo information in tablespace file(s).

Full Access
Question # 17

You created a backup of the world database with this command:

shell> mysqldump --opt world > dump.sql

Which two will import the data from dump.sql? (Choose two.)

A.

shell> mysqladmin recover test dump.sql

B.

shell> mysql test < dump.sql

C.

shell> mysqlimport test dump.sql

D.

mysql> USE test;

mysql> LOAD DATA INFILE ‘dump.sql’;

E.

mysql>USE test;

mysql>SOURCE dump.sql;

Full Access
Question # 18

A single InnoDB table has been dropped by accident. You are unable to use an additional intermediate MySQL instance to restore the table. Which two backup methods can be used to restore the single table without stopping the MySQL instance? (Choose two.)

A.

a backup created with mysqldump --all-databases

B.

a backup created using FLUSH TABLES … FOR EXPORT

C.

an up-to-date replication slave

D.

a file system-level snapshot

E.

a file system copy created while MySQL was shut down.

Full Access