[Aug-2022] The Best MySQL Database Administration Study Guide for the 1Z0-888 Exam
1Z0-888 certification guide Q&A from Training Expert Fast2test
Target Audience
The Oracle 1z0-888 Certification Exam is targeted to the candidates having experience of working with MySQL Database technology and looking to pursue a career as an Oracle Certified Professional MySQL 5.7 Database Administrator OCP specialist in the field of MySQL Database technology.
NEW QUESTION 21
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. 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.
- 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. Add the user [email protected] with the correct password to the slave.
Answer: B
NEW QUESTION 22
You have just created a replication slave from a backup of the master made with mysqldump:
You try to log in to the slave with the application user, but fail as follows:
The login works on the master.
Which two changes to the process can fix the issue?
- A. Add a second dump for the 'mysql' database; --all-databases does not include it.
- B. Use the --grants option to include GRANT statements in the dump.
- C. Use the --flush-privileges with mysqldump.
- D. After the restore, log in to the database and execute FLUSH PRIVILEGES.
Answer: A
NEW QUESTION 23
Which three options are most likely to be changed for production form their default values? (Choose three.)
- A. character_set_system
- B. innodb_log_file_size
- C. port
- D. max_connections
- E. join_buffer_size
- F. max_user_connections
- G. innodb_buffer_pool_size
Answer: B,C,F
NEW QUESTION 24
When you examine a new MySQL installation with default configuration, you find a file called ibdata1 in the database directory. Which two statements are true about this file? (Choose two.)
- A. it is the default location for all new tables that you create.
- B. it contains the binary log.
- C. it contains the redo log.
- D. it contains a general tablespace.
- E. it contains the undo log.
- F. it contains the system tablespace.
Answer: A,F
NEW QUESTION 25
What is the order of tables shown in an EXPLAIN output?
- A. It lists tables in the order in which they are specified in the statement that is being explained.
- B. It lists tables in the order in which their data will be read.
- C. It lists tables from the most optimized to the least optimized.
- D. It lists tables from the smallest to the largest.
Answer: A
Explanation:
Reference: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
NEW QUESTION 26
Is it true that binary backups always take less space than text backups?
- A. No, because if InnoDB tables contain many empty pages, they could take more space than the INSERT statements.
- B. No, because text backups can have optimizations, which make them smaller, such as updating many rows at once.
- C. Yes, because binary backups only contain data, and not statements required to insert data into the tables.
- D. Yes, because even if InnoDB tables contain many empty pages, text backups have empty INSERT statements for them.
Answer: A
NEW QUESTION 27
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. validating all user input before sending it to the database server
- B. giving limited privileges to accounts used by application servers to interact with their backing databases
- C. changing all passwords for the MySQL account 'root'@'%'immediately after losing an employee who knew the current password
- 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. writing your client code to properly escape all user input
- G. using SSL/TLS on your outward facing web servers (https://) to encrypt all user sessions
Answer: C,D,E
NEW QUESTION 28
What is the order of tables shown in an EXPLAINoutput?
- A. It lists tables in the order in which they are specified in the statement that is being explained.
- B. It lists tables in the order in which their data will be read.
- C. It lists tables from the most optimized to the least optimized.
- D. It lists tables from the smallest to the largest.
Answer: A
Explanation:
Explanation/Reference: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
NEW QUESTION 29
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?
- A. For tables with UNIQUE keys, statement-based replication must be used to maintain integrity.
- B. The root user on the slave has executed FLUSH LOGS, causing the relay-log to doublewrite.
- C. The applications have the SUPER privilege, which allows them to update rows.
- D. The slave was created with mysqldump -u root -p --skip-lock-tables -all-databases > /data/data.sql
- E. The slave user does not have INSERT, UPDATE, or DELETE permission and cannot execute the Write_rows function.
Answer: A,D
NEW QUESTION 30
Due to an authentication plug-in that is used on the server, passwords are required to be sent as clear text as opposed to the usual encrypted format.
Which two methods would allow the mysql client to connect to the server and send clear text passwords?
- A. mysql --protocol=PLAIN -uroot -p -h dbhost.example.com
- B. SET GLOBAL mysql_cleartext_passwords=1;
- C. mysql --enable-cleartext-plugin -uroot -p -h dbhost.example.com
- D. INSTALL PLUGIN mysql_cleartext_password SONAME 'mysql_cleartext_password.so';
- E. export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN='Y'
Answer: B,C
Explanation:
Reference: https://dev.mysql.com/doc/refman/5.7/en/cleartext-pluggable- authentication.html
NEW QUESTION 31
Which statement is true about using Microsoft Windows Cluster as a platform for MySQL?
- A. It is provided by means of IP-level disk replication.
- B. It is a shared-nothing architecture.
- C. It implements High Availability by using the NET Connector's load balancing capabilities.
- D. It relies on the shared disk architecture being visible to both servers.
Answer: D
NEW QUESTION 32
A master-slave replication setup has the slave showing this error:
On the master server, the binary logs show:
What could explain this error? (Choose two.)
- A. binlog_cache_size=1024is too small and transactions are lost.
- B. sync_binlog=0and the master server crashed.
- C. binlog_format=STATEMENTand a non-deterministic query was executed.
- D. The sync_relay_log=1000setting on the slave is too small.
- E. enforce_gtid_consistency=ONand consistency is broken between the master and the slave.
Answer: A,E
NEW QUESTION 33
Host slave1 has ip address 192.0.2.10.
Host slave2 has ip address 203.0.113.50
Examine these commands:
Why did this error occur?
- A. The host on the command line is not defined in the login path.
- B. The mysqld instance has not been restarted after creating the login path.
- C. The DNS is not configured correctly for slave1 host.
- D. There is no password defined in the login path.
- E. The .mylogin.cnf file is not readable.
Answer: E
NEW QUESTION 34
On a master server that is using statement-based replication, a table of log data has become very large.
You decide to delete 100,000 rows.
Which two methods can be independently invoked to ensure that the delete is properly propagated to the slave? (Choose two.)
- A. Use the LIMIT clause to limit the deletion to 100,000 rows.
- B. Use the LIMIT clause in conjunction with the ORDER BY clause.
- C. If the data modification is non-deterministic, the query optimizer will resolve any potential issues.
- D. Change the replication mode to MIXED before issuing any delete statements when the LIMIT clause is used.
Answer: B,D
NEW QUESTION 35
An admin attempts to enforce stronger security by using these commands:
The admin then leaves the system running with the specified changes. What are two remaining security concerns?
- A. The dictionary file word list is too short.
- B. validate_password_dictionary_file cannot be set without restarting the MySQL instance.
- C. validate_password_policy cannot be set without restarting the MySQL instance.
- D. The validate_password plug-in has not been loaded.
- E. The dictionary file is an insecure location.
- F. The name of the dictionary file is too obvious.
Answer: E,F
NEW QUESTION 36
You have just created a replication slave from a backup of the master made with mysqldump:
You try to log in to the slave with the application user, but fail as follows:
The login works on the master.
Which two changes to the process can fix the issue?
- A. Use the --flush-privilegeswith mysqldump.
- B. After the restore, log in to the database and execute FLUSH PRIVILEGES.
- C. Add a second dump for the 'mysql' database; --all-databasesdoes not include it.
- D. Use the --grantsoption to include GRANTstatements in the dump.
Answer: C
Explanation:
Explanation/Reference:
NEW QUESTION 37
After rebooting the host, you attempt to start the mysqldservice. You get the following error:
Can't start the server: Bind on TCP/IP port: Address already in use
What is the most likely cause of this error?
- A. The /etc/hostsfile does not have a valid IP entry for mysqld localhost, so it is binding to
127.0.0.1, which is already in use. - B. The mysql.sockfile in the MySQL /tmpdirectory was not removed after the reboot, so mysqldstill thinks there is an active server running.
- C. The network service process in the server is frozen, so all TCP/IPconnections are paused and cannot be reused.
- D. You failed to specify the port number 3306to the command to start the server, so it is defaulting to port 80, which is in use by the built-in web server.
- E. The mysqldservice has already been started on the same port.
Answer: B
NEW QUESTION 38
Which two statements are true about InnoDB auto-increment locking?
- A. The auto-increment lock can be a table-level lock.
- B. InnoDB always protects auto-increment updates with a table-level lock
- C. Some settings for innodb_autoinc_lock_mode can help reduce locking.
- D. InnoDB never uses table_level locks.
- E. InnoDB does not use locks to enforce auto-increment uniqueness.
Answer: A,C
NEW QUESTION 39
The MySQL installation includes the mysql_config_editor utility for managing login paths stored in a
.mylogin.cnf file.
Which two are true about the login path feature? (Choose two.)
- A. It is an alternative to storing the MySQL login details in a my.cnf file.
- B. A .mylogin.cnf file can store at most one login path.
- C. A .mylogin.cnf file can be edited using a text editor, such as vim or Notepad++.
- D. It provides means to help avoid accidentally exposing the MySQL login details.
- E. It provides a FIPS-compliant keyring for storing MySQL login details.
- F. mysql_config_editor is the only MySQL-provided utility that can print the values stored in .mylogin.cnf.
Answer: A,D
NEW QUESTION 40
You have set innodb_stats_auto_recalc to OFF.
For tables using persistent statistics, what is the outcome of this change?
- A. InnoDB no longer automatically updates index statistics after a CREATE TABLE statement.
- B. InnoDB no longer automatically updates index statistics after an ANALYZE TABLE statement.
- C. InnoDB no longer automatically updates index statistics after the table structure is altered.
- D. InnoDB no longer automatically updates index statistics after 10% of the rows in a table change.
Answer: D
Explanation:
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html
NEW QUESTION 41
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?
- A. Joe has exceeded the max_user_connections global limit.
- B. Kay is already connected elsewhere and attempting to log in again.
- C. All users are blocked because max_user_connections is accumulated over the host account information.
- D. Bob has max_user_connections set to zero, which blocks all his connections.
- E. connect_timeout is too small to allow a connection to occur.
Answer: A
NEW QUESTION 42
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?
- A. SET GLOBAL enforce_gtid_consistency=ON
- B. SET GTID_NEXT="CONSISTENCY";
BEGIN; COMMIT;
SET GTID_NEXT="AUTOMATIC"; - C. SET GLOBAL SQL_SKIP_SLAVE_COUNTER=1
- D. SET GTID_EXECUTED="5da6b4f5-6f60-11e8-b2d6-0010e05f3e06:8";
- E. SET GTID_NEXT="5da6b4f5-6f60-11e8-b2d6-0010e05f3e06:8";
BEGIN; COMMIT;
SET GTID_NEXT="AUTOMATIC";
Answer: C
Explanation:
Explanation/Reference:
Reference: https://dev.mysql.com/doc/refman/8.0/en/replication-problems.html
NEW QUESTION 43
Host slave1 has ip address 192.0.2.10.
Host slave2 has ip address 203.0.113.50
Examine these commands:
Why did this error occur?
- A. The host on the command line is not defined in the login path.
- B. The mysqldinstance has not been restarted after creating the login path.
- C. The DNS is not configured correctly for slave1 host.
- D. There is no password defined in the login path.
- E. The .mylogin.cnffile is not readable.
Answer: E
NEW QUESTION 44
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?
- A. Register mysqldas a service that will start automatically on this host machine.
- B. Create a configuration file containing default-storage-engine=InnoDB.
- C. Execute a command with a minimal form of: mysql --initialize
- D. Create additional login accounts (so that everyone does not need to log in as root) and assign them appropriate privileges.
- E. Set an exception in the host machine's firewall to allow external users to talk to mysqld.
Answer: B
NEW QUESTION 45
......
Exam Preparation
Questions in the exam are based on official training material provided by Oracle over different official platforms. Also the information in the following pieces of training is the source for the exam. Hence, candidates should complete the following training courses before appearing for the certification exam.
Candidates should complete one of the below-mentioned pieces of training for the proper understanding of content and topics. On successful completion of the training, the candidate will get the exam voucher for the certification exam.
There are two main types of resources for preparation of certification exams first there are the study guides and the books that are detailed and suitable for building knowledge from the ground up then there are video tutorial and lectures that can somehow ease the pain of through study and are comparatively less boring for some candidates yet these demand time and concentration from the learner.
Candidates who want to build a solid foundation in all exam topics and related technologies usually combine video lectures with study guides to reap the benefits of both but there is one crucial preparation tool as often overlooked by most candidates the practice exams.
Practice exams are built to make students comfortable with the real exam environment. Statistics have shown that most students fail not due to that preparation but due to exam anxiety the fear of the unknown. It is recommended to prepare notes and practice 1z0-888 Exam exam dumps.
Questions in the certification examination are based on the real-world scenarios and cater to the real-time problems implementation difficulties, hence along with all the training material and tutorial it is of utmost importance for the candidate to possess practical knowledge and experience of working in the field of MySQL Database Technology.
The Best Oracle 1Z0-888 Study Guides and Dumps of 2022: https://www.fast2test.com/1Z0-888-premium-file.html