MySQL 5.7 Database Administrator (1Z0-888) Free Practice Test

Question 1

old_alter_table is disabled as shown.
mysql> SELECT @@old_alter_table;

Consider this statement on a RANGE-partitioned table:
mysql> ALTER TABLE orders DROP PARTITION p1, p3;
What is the outcome of executing this statement?

A. All data in p1 and p3 partitions is removed and the table definition is changed.

B. It results in a syntax error because you cannot specify more than one partition in the same statement.

C. Only the first partition (p1) will be dropped because only one partition can be dropped at any time.

D. All data in p1 and p3 partitions is removed, but the table definition remains unchanged.

Correct Answer: D

Question 2

Which three are key advantages of standard MySQL replication?

A. provides arbitrary geographic redundancy with minimal overhead to master

B. supports native automatic failover

C. enables automatic resync of databases when discrepancies are detected

D. synchronously guarantees identical slave copy

E. is easy to configure and has low performance overhead

F. can easily add slaves for read scaling

Correct Answer: C,E,F

Question 3

Which three allocate memory per thread in MySQL?

A. InnoDB buffer pool instance

B. read buffer

C. query cache

D. sort buffer

E. internal temporary table

F. thread cache

Correct Answer: A,B,D

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 4

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 GLOBAL SQL_SKIP_SLAVE_COUNTER=1

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

C. SET GLOBAL enforce_gtid_consistency=ON

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

E. SET GTID_NEXT="CONSISTENCY";
BEGIN; COMMIT;
SET GTID_NEXT="AUTOMATIC";

Correct Answer: A

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 5

You have a scheduled task on Linux that executes mysqldumpagainst the localhost server periodically.
When checking the logs of this event to ensure that things are working and that backups will restore, you notice an output that is concerning.
The command the scheduled task is executing as follows:
$ mysqldump -u backupuser -h 127.0.0.1 -pt100043va living --protocol=TCP
> /backups/latest.sql
Warning: Using a password on the command-line interface can be insecure.
Which two methods are available to avoid the warning? (Choose two.)

A. Connect through the --socket rather than the default --protocol=TCP for local connection.
$ mysqldump -u backupuser -h 127.0.0.1 -pt100043va living --socket=/tmp/mysql.sock > /backups/ latest.sql

B. Use mysql_config_editor, which allows you to store encrypted login credentials in your home directory.

C. Store your password in an option file eg: ~/.my.cnf and use --defaults-file so that it is read and used.
[client]
password=t100043va

D. Use the password validation plugin available to improve user name and password strength.

Correct Answer: B,D

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 6

While attempting to set up a new replication slave on host `192.168.0.25' with the user `replication', you encounter this error:

What should you do to resolve this error?

A. Add the user [email protected] with the correct password to the slave.

B. Add the user [email protected] with the correct password to the master.

C. Edit the DNS table on the master to include the domain name for the IP address of 192.168.0.25.

D. Edit the my.ini file on the slave so that the master-host variable is equal to the IP address of the master, and restart the slave.

Correct Answer: B

Question 7

The account [email protected] has been created with privileges, as shown in the output:

The account [email protected] exists.
The account [email protected] does not exist.
Which three statements will succeed when executed by [email protected]? (Choose three.)

A. GRANT DELETE ON world.* TO [email protected];

B. GRANT SELECT ON audit.* TO [email protected];

C. GRANT SELECT ON world.* TO [email protected] IDENTIFIED BY '^jkK2@9bqLm4!jlaN';

D. GRANT SELECT ON world.* TO [email protected];

E. GRANT SELECT ON world.* TO [email protected];

F. CREATE USER [email protected] IDENTIFIED BY '^jkK2@9bqLm4!jlaN';

G. GRANT INSERT ON audit.* TO [email protected];

Correct Answer: A,F,G

Question 8

Consider the table people with the definition:

The application uses a query such as:
SELECT * FROM people WHERE YEAR(Birthday) = 1980;
The query is not using an index.
Which two methods can be used to allow the query to use an index?

A. Change the WHERE clause to Birthday BETWEEN 1980-01-01 AND 1980-12-31.

B. Execute ANALYZE TABLE to update the index statistics.

C. Add a functional index for YEAR(Birthday).

D. Add FORCE INDEX (Birthday) to the query.

E. Add a generated column calculating YEAR(Birthday) and index that column.

Correct Answer: A,D

Question 9

Which two statements can be used to adjust the sort_buffer_size variable on a running MySQL server?
(Choose two.)

A. mysql> CONF T; do global sort_buffer_size=256000;

B. mysql> SET SESSION sort_buffer_size=256000;

C. mysql> SET GLOBAL sort_buffer_size=256000;

D. mysql> system sort_buffer_size=256000;

E. mysql> SET VARIABLE sort_buffer_size=256000;

Correct Answer: B,C

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 10

You have installed MySQL Server for the first time on your system. However, the data directory along with the tables in the mysql system database are missing. Which step do you perform to create the contents of the data directory?

A. Invoke mysql with the --initialize option.

B. Run the mysql_unpack.sql file

C. Run the create_system_tables.sql file

D. Invoke mysqld with the --initialize option.

Correct Answer: D

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 11

An administrator installs MySQL to run under a mysql OS account. The administrator decides to disable logins to the mysql account by using /nologin or /bin/false as the user's shell setting.
Which statement is true?

A. The mysql user needs a login and its home directory must be the base directory of the installation.

B. This prevents mysqld from starting when standard startup scripts are used.

C. This prevents creation of a command shell with the mysql account, while allowing mysqld to run.

D. The OS needs to allow logging in as mysql so that administrative tasks can be performed.

Correct Answer: A

Question 12

Which statement best describes the purpose of the InnoDB buffer pool?

A. It caches only the indexes for InnoDB tables.

B. It is amount of buffers available during a transaction.

C. It caches data and indexes for InnoDB tables.

D. It is a pool of memory for SQL query sort operations from within the InnoDB engine.

E. It holds changes made during a transaction before they are written to the log.

Correct Answer: C

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 13

You back up by using mysqldump.
Which configuration is required on the MySQL Server to allow point-in-time recovery?

A. bonlog_format=ROW

B. binlog_format=STATEMENT

C. gtid_enable

D. apply-log

E. log-bin

Correct Answer: E

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 14

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

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

B. Logical backups are always larger than physical backups.

C. Physical backups can be used to recover from data corruption.

D. Physical backups are usually faster than text backups.

E. Physical backups are usually slower than text backups.

Correct Answer: C,D

Question 15

Consider these global status variables:

Which two conclusions can be made from the output?

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

Correct Answer: B,D

Welcome to TestSimulate

Oracle MySQL 5.7 Database Administrator (1Z0-888) Free Practice Test