[Jul-2023] The Best MySQL Database Administration Study Guide for the 1Z0-888 Exam [Q89-Q104]

Rate this post

[Jul-2023] The Best MySQL Database Administration Study Guide for the 1Z0-888 Exam

1Z0-888 certification guide Q&A from Training Expert PrepAwayExam

Oracle 1Z1-888 exam is a highly respected certification for database administrators who specialize in MySQL 5.7. Passing 1Z0-888 exam demonstrates the candidate’s proficiency in managing and securing MySQL 5.7 databases. With the increasing demand for database administrators, obtaining this certification can lead to better job opportunities and career growth in the IT industry.

Q89. Examine the mydata table and SELECT statements:

You issue:
mysql> begin;
mysql> update mydata set a=0 where b=3;
How many rows are now protected by locks with the default InnoDB configuration?

one

one row and a next-key lock for supremum

one row and a gap-lock

five

Q90. You are no longer able to log in to an existing MySQL Server because the root password credentials not working. You need to reset the root password to complete various administrative tasks. What are the two major methods that will achieve this? (Choose two.)

Start the MySQL Server in –safe-mode, which only loads the privilege system for changes as data is inaccessible.

Start the MySQL Server with reset-root-password in my.cnf, which will prompt you to enter a new root user password.

Start the MySQL Server with –init-filepointing to SQL that executes an ALTER USER statement to change the root user password.

Start the MySQL Server with –skip-grant-tables and execute SQL, which will update the root password.

Start the MySQL Server with -initialize-insecureto force a password reset procedure on the command line.

Explanation/Reference:
Reference: https://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html

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

You execute a ‘SHOW CREATE TABLE mytable” on the slave:

The table mytable on the slave contains:

You have issued a STOP SLAVEcommand. 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 SLAVEcommand to resolve the duplicate key error. Which statement should be used?

SET GTID_NEXT=”CONSISTENCY”;
BEGIN; COMMIT;
SET GTID_NEXT=”AUTOMATIC”;

SET GTID_NEXT=”5da6b4f5-6f60-11e8-b2d6-0010e05f3e06:8″;
BEGIN; COMMIT;
SET GTID_NEXT=”AUTOMATIC”;

SET GLOBAL SQL_SKIP_SLAVE_COUNTER=1

SET GLOBAL enforce_gtid_consistency=ON

SET GTID_EXECUTED=”5da6b4f5-6f60-11e8-b2d6-0010e05f3e06:8″;

Explanation/Reference: https://dev.mysql.com/doc/refman/8.0/en/replication-problems.html

Q92. An existing master-slave setup is currently using a delayed replication of one hour. The master has crashed and the slave must be “rolled forward” to provide all the latest datA.
The SHOW SLAVE STATUS indicates these values:
* RELAY_LOG_FILE=hostname-relay-bin.00004
* RELAY_LOG_POS=1383
Which command set would make the slave current?

STOP SLAVE; SET GLOBAL master_delay=0; START SLAVE;

STOP SLAVE; CHANGE MASTER TO RELAY_LOG_FILE = ‘hostname-relay-bin.00004’, RELAY_LOG_POS = 1383;

STOP SLAVE; CHANGE MASTER TO MASTER_DELAY=0; START SLAVE;

STOP SLAVE; CHANGE MASTER TO MASTER_DELAY=0; RELAY_LOG_FILE
‘hostname-relay-bin.00004’, RELAY_LOG_POS = 1383;

Q93. At which stage during query processing does MySQL create a query’s execution plan?

Optimizing

Parsing

Executing

Authorizing

https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html

Q94. Which two are considered good security practices when using passwords? (Choose two.)

Use one-way encryption for storage of passwords.

Store passwords external to the database.

Choose short passwords to save on storage space.

Use simple keyboard actions that give mixed letters.

Do not use dictionary-based words.

Reference:
https://stackoverflow.com/questions/14798275/best-way-to-store-passwords-in-mysql-database

Q95. Suppose you are adding rows to a MyISAM table and the –datadirlocation runs out of disk space.
What will happen when this occurs?

The server will crash.

The server suspends that INSERT operation until space becomes available.

An error message will be returned to the client .Server Error: ER_IO

The server suspends operations for all storage engines until space becomes available.

Q96. 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?

–tab

–single-transaction

–quick

–skip-buffer

Q97. A MySQL replication slave is set up as follows:
Uses all InnoDB tables

Receives ROW-based binary logs

Has the read-only option

The replication slave has been found in an error state.
You check the MySQL error log file and find these entries:

What are two possible causes for this error to occur?

The applications have the SUPERprivilege, which allows them to update rows.

The root user on the slave has executed FLUSH LOGS, causing the relay-log to doublewrite.

For tables with UNIQUE keys, statement-based replication must be used to maintain integrity.

The slave was created with mysqldump -u root -p –skip-lock-tables -all-databases
> /data/data.sql

The slave user does not have INSERT, UPDATE, or DELETEpermission and cannot execute the Write_rowsfunction.

Q98. You have set innodb_stats_auto_recalc to OFF.
For tables using persistent statistics, what is the outcome of this change?

InnoDB no longer automatically updates index statistics after a CREATE TABLE statement.

InnoDB no longer automatically updates index statistics after the table structure is altered.

InnoDB no longer automatically updates index statistics after 10% of the rows in a table change.

InnoDB no longer automatically updates index statistics after an ANALYZE TABLE statement.

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

Q99. You want to create a temporary table named OLD_INVENTORY in the OLD_INVENTORY database on the master server. This table is not to be replicated to the slave server. Which two changes would ensure that the temporary table does not propagate to the slave?

Set binlog_format=MIXED with the –replicate-ignore-temp-table option.

Use the –replicate-do-db, –replicate-do-table, or –replicate-wild-do-table option with the value equal to OLD_INVENTORY.

Change the binlog_format option to ROW and restart mysqld before you create the OLD_INVENTORY table.

Stop SQL_THREAD on the slave until you have finished using the OLD_INVENTORY temporary table.

Use the –replicate-ignore-table option with the value equal to OLD_INVENTORY.OLD_INVENTORY and restart mysqld before creating the temporary table.

Q100. 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?

the mysql_upgrade script

the mysql client with the –ignore-password-hashing option

the mysql_secure_installation script to update server security settings

the mysql client with the –enable-cleartext-plugin option

the install plugin command for the mysql_cleartext_password plugin

Reference: http://planet.mysql.com/entry/?id=34077

Q101. What is the order of tables shown in an EXPLAINoutput?

It lists tables from the smallest to the largest.

It lists tables in the order in which their data will be read.

It lists tables from the most optimized to the least optimized.

It lists tables in the order in which they are specified in the statement that is being explained.

Explanation/Reference:
Reference: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

Q102. 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?

The log_bin variable is set to OFF on the slave.

server_id is not unique.

The max_connections variable on the slave needs to be increased.

The shared_memory_base_name variable must match the master.

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

Q103. 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?