Authentic 1Z1-888 Dumps - Free PDF Questions to Pass [Q73-Q92]

5/5 - (1 vote)

Authentic 1Z1-888 Dumps – Free PDF Questions to Pass

Guaranteed Accomplishment with Newest Jun-2022 FREE 1Z1-888

For more info visit:

MySQL 5.7 Database Administrator

Person VUE

Oracle Education

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

Decide which indexes to use.

Rewrite the WHEREclause.

Parse the query.

Change the order in which the tables are joined.

Validate the query.

Execute the query.

Verify that the user is allowed to execute the query.

Q74. 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 STATUSindicates 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;

Q75. A MySQL database uses all InnoDB tables and is configured as follows;

You will be setting up a replication slave by using mysqldump. You will need a consistent backup taken from your running production server. The process should have minimal impact to active database connections.
Which two arguments will you pass to mysqldump to achieve this?

–skip-opt

–lock-all-tables

–create-apply-log

–single-transaction

–master-data

Q76. Which three options are most likely to be changed for production form their default values?

innodb_buffer_pool_size

max_connections

join_buffer_size

character_set_system

innodb_log_file_size

max_user_connections

port

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

binlog_format=STATEMENT

log-bin

apply-log

bonlog_format=ROW

gtid_enable

Q78. A MySQL Server has been running an existing application successfully for six months. The my.cnf is adjusted to contain this additional configuration:

The MySQL Server is restarted without error.
What effect will the new configuration have on existing account?

They are not affected by this configuration change.

They all connect via the secure sha256_passwordalgorithm without any configuration change.

They will have their passwords updated on start-up to sha256_passwordformat.

They will have to change their password the next time they login to the server.

Q79. You inherited a busy InnoDB OLTP Instance with 100 schemas and 100 active users per schema.
Total dataset size is 200G with an average schema size of 2G.

The data is transient and is not backed up and can be repopulated easily.

Performance and responsiveness of the DB is paramount.

The query pattern for the DB instance is split 90/10 read/write.

DB host is dedicated server with 256G RAM and 64 cores.

One of your colleagues made some recent changes to the system and users are now complaining of performance impacts.
Which four configuration file edits might your colleague have performed to cause the negative DB performance? (Choose four.)

table_open_cache = 64

innodb_buffer_pool_instances=64
innodb_buffer_pool_size=200G

log_bin=mysql -bin
Innodb_flush_log_at_trx_commit=1

sync_binlog=10

innodb_flush_method=O_DIRECT

max_heap_table_size = 2G
tmp_table_size=2G

query_cache_size = 2G
query_cache_enabled=1

innodb_flush_log_at_trx_commit=0

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

Change the WHEREclause to Birthday BETWEEN 1980-01-01 AND 1980-12-31.

Add a functional index for YEAR(Birthday).

Execute ANALYZE TABLEto update the index statistics.

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

Add FORCE INDEX (Birthday)to the query.

Q81. Consider the CHECK TABLE command.
In which two situations should this command be used? (Choose two.)

to find out why a query takes a long time to execute on a given table

to make sure a table has no structural problems

to improve performance by updating index distributing statistics on InnoDB tables

to repair table structure problem

to make sure that no table indexes are corrupted
The CHECK TABLE statement performs an integrity check on table structure and contents. It works for MyISAM and InnoDB tables. For MyISAM tables, it also updates the index statistics. If the table is a view, CHECK TABLE verifies the view definition. If the output from CHECK TABLE indicates that a table has problems, the table should be repaired.

Q82. What is the best method for monitoring Group Replication conflict resolution?

the PERFORMANCE_SCHEMA tables

the SHOW PROCESSLIST command

the INNODB Lock Monitor details

the SHOW STATUS command

the INFORMATION_SCHEMA tables

Q83. An employee cannot access the company database. You check the connection variables:

A look at the user privileges shows:

What is a valid explanation for why one of the users is unable to connect to the database?

Bob has max_user_connectionsset to zero, which blocks all his connections.

All users are blocked because max_user_connectionsis accumulated over the host account information.

connect_timeout is too small to allow a connection to occur.

Joe has exceeded the max_user_connectionsglobal limit.

Kay is already connected elsewhere and attempting to log in again.

Q84. You have successfully provisioned the latest MySQL 5.7 database instance on a physical host, to be added to an existing farm for use in a modern, high volume, ACID-compliant, OLTP website, which serves hundreds of DML transactions per second.
The default values of which two key variables do you change to ensure seamless operation of the database?
(Choose two.)

Key Buffer Size

InnoDB Redo Log Size

Binary Log Size

Buffer Pool Size

Sort Buffer size

Query Cache Size

Q85. A MySQL instance has this configuration values set:

A transaction involving a single InnoDB INSERT statement commits.
Which list of locations is in the sequence of disk writes for this transaction?

binary log, redo log, doublewrite buffer, and tablespace

redo log, binary log, doublewrite buffer, and tablespace

doublewrite buffer, redo log, tablespace, and binary log

redo log, doublewrite buffer, tablespace, and binary log

Q86. You have forgotten the root user account password. You decide to reset the password and execute:

Which additional argument makes this operation safer?

-old-passwords, to start MySQL to use the old password format while running without the grant tables.

-reset-grant-tables, to start the server with only the mysql database accessible.

-read-only, to set all data to read-only except for super users

-skip-networking, to prohibit access from remote locations

Q87. You have installed the validate_password plug-in and set the validate_password_policy variable.
Which validation is affected by the validate_password_policy setting?

whether a new password is rejected if it contains a word found in a dictionary file.

whether a new password is rejected if it contains the current user’s username.

the amount of delay after an incorrect password is entered

the length of time before a newly created password expires

Q88. You are setting up a new installation of MySQL Server 5.7 (a GA release.) You have used a ZIP or TAR package to ensure that the mysqld binary, along with its support files, such as plug-ins and error messages, now exist on the host. Assume that the default datadir exists on the host. You installed the binary in the default location (the default –basedirvalue) for your operating system.
Which step should you perform before defining your own databases and database tables?

Execute a command with a minimal form of: mysql –initialize

Create a configuration file containing default-storage-engine=InnoDB.

Set an exception in the host machine’s firewall to allow external users to talk to mysqld.

Create additional login accounts (so that everyone does not need to log in as root) and assign them appropriate privileges.

Q89. 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.)

Implement a failover strategy to another geographic location.

Create a master-master pair for each service.

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

Keep software updated to the latest version.

Use RAID 10 storage for data.

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

Q90. There are multiple instances of MySQL Server running on a single OS that is backed up using the mysqlbackupcommand.
The /etc/my/cnf contains default values, for example, datadir=/var/lib/mysql/,with extra instances having their own separate my.cnffile (for example /etc/mysql/instanceN.cnf) overriding the defaults.
A restore of the second instance is attempted from the mysqlbackup archive using this command:
mysqlbackup –backup-dir=/opt/backup/mysql/instance2 copy-back
Upon starting the second MySQL instance, you notice that the data does not match the expected backup.
Which command-line option is required to successfully update the second instance?

–restore=2

–copy-back-from-log

–backup-instance=/var/lib/mysql/instance2

–instance=/var/lib/mysql/instance2

–defaults-file=/etc/mysql/instance2.cnf

Q91. You have the following in your my.cnf configuration file:
[mysqld]
default_authentication_plugin=sha256_password
You want to create a new user who will be connecting from the IP address 192.0.2.10, and you want to use the authentication plug-in that implements SHA-256 hashing for user account passwords.
Which two statements would create a user named webdesign for this IP address with the password of imbatman using a SHA_256 password hash? (Choose two.)

CREATE USER ‘webdesign’@’192.0.2.10’ IDENTIFIED AS sha256_user WITH

sha256_password ‘imbatman’;
CREATE USER ‘webdesign’@’192.0.2.10’ IDENTIFIED BY ‘iambatman’;

CREATE USER ‘webdesign’@’192.0.2.10’ IDENTIFIED WITH sha256_password BY

‘imbatman’;
CREATE USER WITH sha256_password ‘sha256_user’@’192.0.2.10’ IDENTIFIED AS

‘webdesign’ USING ‘imbatman’;
CREATE USER ‘webdesign’@’192.0.2.10’ WITH mysql_native_password USING SHA265 BY

‘imbatman’;
CREATE USER ‘webdesign’@’192.0.2.10’ IDENTIFIED BY SHA265 AS ‘imbatman’;

Q92. You have the following in your my.cnf configuration file:
[mysqld]
default_authentication_plugin=sha256_password
You want to create a new user who will be connecting from the IP address 192.0.2.10, and you want to use the authentication plug-in that implements SHA-256 hashing for user account passwords.
Which two statements would create a user named webdesign for this IP address with the password of imbatman using a SHA_256 password hash? (Choose two.)

CREATE USER ‘webdesign’@’192.0.2.10’ IDENTIFIED AS sha256_user WITH sha256_password
‘imbatman’;

CREATE USER ‘webdesign’@’192.0.2.10’ IDENTIFIED BY ‘iambatman’;

CREATE USER ‘webdesign’@’192.0.2.10’ IDENTIFIED WITH sha256_password BY ‘imbatman’;

CREATE USER WITH sha256_password ‘sha256_user’@’192.0.2.10’ IDENTIFIED AS ‘webdesign’ USING ‘imbatman’;

CREATE USER ‘webdesign’@’192.0.2.10’ WITH mysql_native_password USING SHA265 BY
‘imbatman’;

CREATE USER ‘webdesign’@’192.0.2.10’ IDENTIFIED BY SHA265 AS ‘imbatman’;