Excellent 1Z0-888 PDF Dumps With 100% Prep4sureGuide Exam Passing Guaranted [Sep-2021]
100% Pass Your 1Z0-888 MySQL 5.7 Database Administrator at First Attempt with Prep4sureGuide
NEW QUESTION 18
The MySQL error log shows:
InnoDB: Warning: a long semaphore wait:
The relevant parts of the InnoDB monitor output shows:
Which two options would help avoid the long wait in the future?
- A. Set the value of innodb_adaptive_hash_index to zero.
- B. Increase the value of the innodb_lock_wait_timeout option.
- C. Increase the value of the innodb_read_io_threads option.
- D. Increase the size of the InnoDB buffer pool.
- E. Change the table to use HASH indexes instead of BTREE indexes.
- F. Deactivate the query cache.
Answer: C,D
NEW QUESTION 19
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 validate_passwordplug-in has not been loaded.
- B. validate_password_policycannot be set without restarting the MySQL instance.
- C. The dictionary file is an insecure location.
- D. The name of the dictionary file is too obvious.
- E. The dictionary file word list is too short.
- F. validate_password_dictionary_filecannot be set without restarting the MySQL instance.
Answer: C,D
NEW QUESTION 20
Consider these global status variables:
Which two conclusions can be made from the output?
- A. There are 510 connections to MySQL at the time of the output.
- B. All max_connections were in use at 2018-03-22 14:54:06
- C. The thread cache has been configured with thread_cache_size set to at least 6.
- D. There are 140 Performance Schema threads at the time of the output.
- E. There are more connections being idle than executing queries.
Answer: A,E
NEW QUESTION 21
Consider the CHECK TABLEcommand.
In which two situations should this command be used? (Choose two.)
- A. to make sure that no table indexes are corrupted
- B. to find out why a query takes a long time to execute on a given table
- C. to improve performance by updating index distributing statistics on InnoDB tables
- D. to make sure a table has no structural problems
- E. to repair table structure problem
Answer: A,D
Explanation:
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.
NEW QUESTION 22
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.)
- A. Start the MySQL Server in --safe-mode, which only loads the privilege system for changes as data is inaccessible.
- B. Start the MySQL Server with --skip-grant-tables and execute SQL, which will update the root password.
- C. Start the MySQL Server with -initialize-insecureto force a password reset procedure on the command line.
- D. Start the MySQL Server with --init-filepointing to SQL that executes an ALTER USER statement to change the root user password.
- E. Start the MySQL Server with reset-root-password in my.cnf, which will prompt you to enter a new root user password.
Answer: B,D
Explanation:
Explanation/Reference:
Reference: https://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html
NEW QUESTION 23
You have a MySQL instance with the following variables in the /etc/my.cnf file:
You issue these statements:
USE prices;
UPDATE sales.january SET amount=amount+1000;
An hour after excluding the statements, you realize that you made a mistake and you want to go to the binary log and look at the statements again.
Which statement is true? (Choose two.)
- A. The statement would fail because you cannot update more than one row at a time when using -binlogformat = ROW.
- B. Nothing is logged because you are executing an UPDATE statement that will cause changes to more than one row, and you do not have the --binlog-format value set to STATEMENT.
- C. Nothing was written to the binary log because you cannot perform a calculation in a query without enclosing the statement in single quotation marks.
- D. You would receive an error on the statement because you cannot update a different database that what is specified with the USE statement.
- E. The changes caused by the UPDATE statement are logged to the binary log because the instance is using --binlog-format = ROW
Answer: B,C
NEW QUESTION 24
Consider the join_buffer_sizeparameter in MySQL Server.
Which two statements are true about the join buffer?
- A. The value should be increased from the default if the query joins large rows without using an index.
- B. The join buffer is global and can be changed only by restarting the server.
- C. The join buffer is used to process sorts when complex joins are being performed.
- D. The value should be increased if the client performs several SELECToperations.
- E. The join buffer is set per connection.
Answer: A,C
NEW QUESTION 25
You are using the Performance Schema to investigate replication on a slave which has a single master. The option slave-parallel-type is set to DATABASE.
Assume that all instruments and consumers are enabled and all threads are instrumented.
Which two facts can be concluded from the given output? (Choose two.)
- A. At most, two schemas are being updates concurrently.
- B. The salve has two intermediate relay slaves connected to it.
- C. The slave cannot process the relay log fast enough to use all threads.
- D. The server needs more cores to use all slave threads.
- E. THREAD_ID 21 has stopped running.
- F. The slave is configured with slave_parallel_workers = 4
Answer: C,F
NEW QUESTION 26
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. After the restore, log in to the database and execute FLUSH PRIVILEGES.
- B. Add a second dump for the 'mysql' database; --all-databases does not include it.
- C. Use the --flush-privileges with mysqldump.
- D. Use the --grants option to include GRANT statements in the dump.
Answer: B
NEW QUESTION 27
Consider:
Which statement best describes the meaning of the value for the key_len column?
- A. It shows how many bytes will be used from each index row.
- B. It shows the total size of the index row.
- C. It shows how many columns in the index are examined.
- D. It shows the number of characters indexed in the key.
Answer: A
NEW QUESTION 28
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. --lock-all-tables
- B. --single-transaction
- C. --master-data
- D. --skip-opt
- E. --create-apply-log
Answer: A,D
NEW QUESTION 29
You will configure a MySQL Server to act as a replication master. Which two options must be configured correctly to allow this?
- A. master-logging
- B. server-id
- C. log-master-updates
- D. log_bin
- E. enable-master-start
- F. rpl-recovery-rank
Answer: B,D
NEW QUESTION 30
You created a backup of the worlddatabase with this command:
shell> mysqldump --opt world > dump.sql
Which two will import the data from dump.sql? (Choose two.)
- A. mysql> USE test; mysql> LOAD DATA INFILE 'dump.sql';
- B. shell> mysql test < dump.sql
- C. shell> mysqladmin recover test dump.sql
- D. shell> mysqlimport test dump.sql
- E. mysql>USE test; mysql>SOURCE dump.sql;
Answer: A,D
Explanation:
Explanation/Reference:
NEW QUESTION 31
The MySQL error log shows:
InnoDB: Warning: a long semaphore wait:
The relevant parts of the InnoDB monitor output shows:
Which two options would help avoid the long wait in the future?
- A. Set the value of innodb_adaptive_hash_index to zero.
- B. Increase the value of the innodb_lock_wait_timeout option.
- C. Increase the value of the innodb_read_io_threads option.
- D. Increase the size of the InnoDB buffer pool.
- E. Change the table to use HASH indexes instead of BTREE indexes.
- F. Deactivate the query cache.
Answer: C,D
NEW QUESTION 32
You enable binary logging on MySQL Server with the configuration:
binlog-format=STATEMENT
log-bin
Which database updates are logged on the master server to the binary log by default?
- A. all updates except to the TEMPDB database
- B. all updates not involving temporary tables
- C. all updates except to the PERFORMANCE_SCHEMA database
- D. all updates to all databases
- E. all updates to the default database, except temporary tables
Answer: E
NEW QUESTION 33
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? (Choose two.)
- A. Set binlog_format=MIXED with the --replicate-ignore-temp-table option.
- B. Use the --replicate-ignore-table option with the value equal to OLD_INVENTORY.OLD_INVENTORY and restart mysqld before creating the temporary table.
- C. Stop SQL_THREAD on the slave until you have finished using the OLD_INVENTORY temporary table.
- D. Change the binlog_format option to ROW and restart mysqld before you create the OLD_INVENTORY table.
- E. Use the --replicate-do-db, --replicate-do-table, or --replicate-wild-do-table option with the value equal to OLD_INVENTORY.
Answer: B,E
NEW QUESTION 34
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. Add a functional index for YEAR(Birthday).
- B. Add FORCE INDEX (Birthday)to the query.
- C. Add a generated column calculating YEAR(Birthday)and index that column.
- D. Change the WHEREclause to Birthday BETWEEN 1980-01-01 AND 1980-12-31.
- E. Execute ANALYZE TABLEto update the index statistics.
Answer: B,D
NEW QUESTION 35
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 max_connections variable on the slave needs to be increased.
- B. The version of the slave is newer that the version of the master.
- C. The log_bin variable is set to OFF on the slave.
- D. server_id is not unique.
- E. The shared_memory_base_name variable must match the master.
Answer: C
NEW QUESTION 36
After analysis on the slow query log on a high-end OLTP service, the table identified in the slow queries is:
What are the two most likely reasons for the slowness given this output? (Choose two.)
- A. No indexes are defined.
- B. The User field is too long for most names.
- C. Using default values for DATETIME causes table scans.
- D. Date should be a TIMESTAMP field for better performance.
- E. The engine type is not appropriate to the application use.
Answer: A,E
NEW QUESTION 37
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?
- A. max_heap_table_size = 2G
tmp_table_size=2G - B. query_cache_size = 2G
query_cache_enabled=1 - C. log_bin=mysql -bin
Innodb_flush_log_at_trx_commit=1 - D. sync_binlog=10
- E. table_open_cache = 64
- F. innodb_flush_log_at_trx_commit=0
- G. innodb_flush_method=O_DIRECT
- H. innodb_buffer_pool_instances=64
innodb_buffer_pool_size=200G
Answer: B,E,G,H
NEW QUESTION 38
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?
- A. Start the MySQL Server with -initialize-insecure to force a password reset procedure on the command line.
- B. Start the MySQL Server in --safe-mode, which only loads the privilege system for changes as data is inaccessible.
- C. Start the MySQL Server with --skip-grant-tables and execute SQL, which will update the root password.
- D. Start the MySQL Server with reset-root-password in my.cnf, which will prompt you to enter a new root user password.
- E. Start the MySQL Server with --init-file pointing to SQL that executes an ALTER USER statement to change the root user password.
Answer: C,E
Explanation:
Reference: https://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html
NEW QUESTION 39
You have just executed a manual backup by using this command:
mysqlbackup -u root -p --socket=/tmp/my.sock --backup-dir=/my/backup/ backup The operation completed without error.
What is the state of this backup and operation required before it is ready to be restored?
- A. Backup State = Raw Backup
Operation = backupdir-to-image - B. Backup State = Raw Backup
Operation = apply-log - C. Backup State = Compressed Backup
Operation = copy-back - D. Backup State = Prepared Backup
Operation = apply-log - E. Backup State = Prepared Backup
Operation = validate
Answer: A
NEW QUESTION 40
After analysis on the slow query log on a high-end OLTP service, the table identified in the slow queries is:
What are the two most likely reasons for the slowness given this output?
- A. No indexes are defined.
- B. The User field is too long for most names.
- C. Using default values for DATETIMEcauses table scans.
- D. Dateshould be a TIMESTAMPfield for better performance.
- E. The engine type is not appropriate to the application use.
Answer: A,E
NEW QUESTION 41
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?
- A. the mysql_secure_installation script to update server security settings
- B. the mysql client with the --ignore-password-hashing option
- C. the mysql client with the --enable-cleartext-plugin option
- D. the install plugin command for the mysql_cleartext_password plugin
- E. the mysql_upgrade script
Answer: A
Explanation:
Reference: http://planet.mysql.com/entry/?id=34077
NEW QUESTION 42
......
Trend for 1Z0-888 pdf dumps before actual exam: https://www.prep4sureguide.com/1Z0-888-prep4sure-exam-guide.html
Real Exam Questions & Answers - Oracle 1Z0-888 Dump is Ready: https://drive.google.com/open?id=1nvTTl3GmPLiRFgEguEgSsgCKhBiUJjQI