Oracle Oracle Certifications 1Z0-888 Questions & Answers

• Question 101:

  One of your colleagues is trying to make a change using the mysql command-line client for his or her application session.

  The colleague instant messages you this command:

  mysql> SET SESSION max_connections = 200;

  Why does the command fail?

  A. max_connections requires the GLOBAL scope.

  B. Its current user does not have the SUPER privilege.

  C. max_connections is not a dynamic variable. You need to change the config file and restart the database.

  D. Users can control only the max_user_connections variable.

  Correct Answer: A

• Question 102:

  MySQL is installed on a Linux server and has this configuration:

  [mysqld]

  user=mysql

  datadir=/data/mysql/

  As the ‘root’ user, you change the datadir location by executing:

  shell> cp –R /var/lib/mysql /data/mysql/

  shell> chown –R mysql /data/mysql

  What is the purpose of changing ownership of datadir to the ‘mysql’ user?

  A. MySQL needs to be run as the root user, but files cannot be owned by it.

  B. The mysqld process requires all permissions within datadir to be the same.

  C. MySQL cannot be run as the root user.

  D. MySQL requires correct file ownership while remaining secure.

  Correct Answer: A

• Question 103:

  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.

  Correct Answer: A

• Question 104:

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

  A. the PERFORMANCE_SCHEMA tables

  B. the SHOW PROCESSLIST command

  C. the INNODB Lock Monitor details

  D. the SHOW STATUS command

  E. the INFORMATION_SCHEMA tables

  Correct Answer: A

  Reference: https://dev.mysql.com/doc/refman/8.0/en/group-replication-monitoring.html

• Question 105:

  To satisfy a security requirement, you have created or altered some user accounts to include REQUIRE X509.

  Which additional task needs to be performed for those user accounts to fulfill the requirement to use X509?

  A. Install the X509 plug-in on the server.

  B. Set the X509 option in the [client] section of the MySQL server's configuration file.

  C. Restart the server with the --require-x509 option.

  D. Distribute client digital certificates to the client computers being used to log in by the user accounts.

  E. Provide users access to the server's private key.

  Correct Answer: B

• Question 106:

  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;

  Correct Answer: CD

• Question 107:

  How does the InnoDB storage engine handle deadlocks when they are detected?

  A. Both the affected transactions will be rolled back.

  B. The affected transactions wait for innodb_lock_wait_timeout seconds, and then roll back.

  C. One of the affected transactions will be rolled back, the other is allowed to proceed.

  D. The transaction isolation level determines which transaction is rolled back.

  E. The innodb_locks_unsafe_for_binlog setting determines which transaction is rolled back.

  Correct Answer: C

  Reference: https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks.html

• Question 108:

  Which two statements describe how InnoDB recovery works? (Choose two.)

  A. InnoDB handles most crash recoveries automatically.

  B. InnoDB blocks some operations when innodb_force_recovery is set to greater than 0.

  C. There will in general be lost committed transactions after a crash using the default settings.

  D. It is required to enable binlog_gtid_simple_recovery to perform a crash recovery.

  E. It is recommended to set innodb_force_recovery = 1 as part of normal operations.

  F. It is always required to enable innodb_force_recovery to perform a crash recovery.

  Correct Answer: BF

• Question 109:

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

  A. CREATE USER `webdesign'@'192.0.2.10' IDENTIFIED AS sha256_user WITH sha256_password `imbatman';

  B. CREATE USER `webdesign'@'192.0.2.10' IDENTIFIED BY `iambatman';

  C. CREATE USER `webdesign'@'192.0.2.10' IDENTIFIED WITH sha256_password BY `imbatman';

  D. CREATE USER WITH sha256_password `sha256_user'@'192.0.2.10' IDENTIFIED AS `webdesign' USING `imbatman';

  E. CREATE USER `webdesign'@'192.0.2.10' WITH mysql_native_password USING SHA265 BY `imbatman';

  F. CREATE USER `webdesign'@'192.0.2.10' IDENTIFIED BY SHA265 AS `imbatman';

  Correct Answer: BF

  Reference: https://dev.mysql.com/doc/refman/8.0/en/sha256-pluggable-authentication.html

• Question 110:

  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? (Choose two.)

  A. --skip-opt

  B. --lock-all-tables

  C. --create-apply-log

  D. --single-transaction

  E. --master-data

  Correct Answer: AB