1Z0-888 Online Practice Questions and Answers

Which three allocate memory per thread in MySQL? (Choose three.)

A. query cache

B. thread cache

C. read buffer

D. internal temporary table

E. sort buffer

F. InnoDB buffer pool instance

A crucial database, `db_prod', just disappeared from your production MySQL instance.

In reviewing the available MySQL logs (General, Audit, or Slow) and your own application-level logs, you

identified this command from a customer facing application:

SELECT id FROM users WHERE login='payback!';DROP DATABASE db_prod;'

Which three methods could have been used to prevent this SQL injection attack from happening? (Choose

three.)

A. writing your client code to properly escape all user input

B. giving limited privileges to accounts used by application servers to interact with their backing databases

C. using SSL/TLS on your outward facing web servers (https://) to encrypt all user sessions

D. using a hashing or encryption method to secure all user passwords in your MySQL tables

E. removing any remaining anonymous accounts from your MySQL instance

F. validating all user input before sending it to the database server

G. changing all passwords for the MySQL account `root'@'%' immediately after losing an employee who knew the current password

The /myfolder/my.cnf file has option set:

[mysqld]

skip-log-bin

/myfolder2/my.cnf has this option set:

[mysqld]

log-bin = /valid/path/to/mysqlbinlog

All mentioned paths are accessible to the account that you are currently using. Assume that any other

options mentioned in either file are valid and legal option definitions.

You start an instance by using this command line:

mysqld --defaults-file=/myfolder/my.cnf --defaults-extra-file=/myfolder2/my.cnf

What is the outcome?

A. MySQL starts and Binary Logging is enabled.

B. MySQL fails to start due to the conflicting options in the configuration files.

C. MySQL fails to start due to conflicting options on the command line.

D. MySQL starts but Binary Logging is disabled.

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.

You have a config file for a running DB with this excerpt:

[mysqld]

tmp_table_size=16M

sort_buffer_size=256k

To address a query performance problem of connecting to the DB from an application on another host, you

log in and make these changes to the DB:

mysql> SET GLOBAL tmp_table_size=32000000;

mysql> SET sort_buffer_size=2000000;

This solves the problem with your queries. However, later the DB instance is restarted and the

performance problem returns.

Which three best describe this scenario?

A. Global variables are not persistent across server restarts.

B. Session variables are not persistent across server restarts.

C. The query benefited from sort_buffer_size increase.

D. sort_buffer_size should match tmp_table_size to be optimal.

E. The query benefited from tmp_table_size increase.

F. The query benefited from sort_buffer_size and tmp_table_size increases.

Multiversion Concurrency Control (MVCC) enables what type of backup?

A. Hot

B. Binary

C. Logical

D. Incremental

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

What three are correct statements about MySQL backups? (Choose three.)

A. They are used to set up the initial binary log files.

B. They are used to prevent data corruption.

C. They are used to prevent data theft.

D. They are used to set up a replication slave.

E. They are used to recover data.

F. They are used to set up a test system.

At which stage during query processing does MySQL create a query's execution plan?

A. Optimizing

B. Parsing

C. Executing

D. Authorizing