Exact 1z0-888 Testing Bible 2021

NEW QUESTION 1
Which two statements are true about InnoDB auto-increment locking?

• A. InnoDB never uses table_level locks.
• B. InnoDB always protects auto-increment updates with a table-level lock
• C. InnoDB does not use locks to enforce auto-increment uniqueness.
• D. The auto-increment lock can be a table-level lock.
• E. Some settings for innodb_autoinc_lock_mode can help reduce lockin

Answer: DE

NEW QUESTION 2
You are using replication and the binary log files on your master server consume a lot of disk space. Which two steps should you perform to safely remove some of the older binary log files? (Choose two.)

• A. Execute the PURGE BINARY LOGS NOT USED command.
• B. Edit the .index file to remove the files you want to delete.
• C. Ensure that none of the attached slaves are using any of the binary logs you want to delete.
• D. Remove all of the binary log files that have a modification date earlier than today.
• E. Use the command PURGE BINARY LOGS and specify a binary log file name or a date and time to remove unused files.

Answer: CE

Explanation:
A: To safely purge binary log files, follow this procedure:
1. On each slave server, use SHOW SLAVE STATUS to check which log file it is reading.
2. Obtain a listing of the binary log files on the master server with SHOW BINARY LOGS.
3. Determine the earliest log file among all the slaves. This is the target file. If all the slaves are up to date, this is the last log file on the list.
4. Make a backup of all the log files you are about to delete. (This step is optional, but always advisable.)
5. Purge all log files up to but not including the target file. Syntax:
PURGE { BINARY | MASTER } LOGS
{ TO ‘log_name’ | BEFORE datetime_expr }

NEW QUESTION 3
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 except to the PERFORMANCE_SCHEMA database
• C. all updates not involving temporary tables
• D. all updates to the default database, except temporary tables
• E. all updates to all databases

Answer: D

NEW QUESTION 4
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?

• 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;

Answer: CD

NEW QUESTION 5
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 in --safe-mode, which only loads the privilege system for changes as data is inaccessible.
• B. Start the MySQL Server with reset-root-password in my.cnf, which will prompt you to enter a new root user password.
• C. Start the MySQL Server with --init-file pointing to SQL that executes an ALTER USER statement to change the root user password.
• D. Start the MySQL Server with --skip-grant-tables and execute SQL, which will update the root password.
• E. Start the MySQL Server with –initialize-insecure to force a password reset procedure on the command lin

Answer: CD

NEW QUESTION 6
The following grants were executed:
GRANT CREATE ROUTING ON sales.* TO ‘webadmin’@’%’; GRANT ALTER ON PROCEDURE sales.myproc TO ‘webadmin’@’%’;
A user successfully connects to the database as webadmin and created a stored procedure named get_reports. The next day, the user logs in again as webadmin and wants to delete the stored procedure named get_reports, and therefore, issues the following statement:
USE sales;
DROP PROCEDURE IF EXISTS get_reports; What is the result of executing the statement?

• A. The user will get an error because he or she did not use the ALTER statement to drop the stored procedure.
• B. The user will get an error because he or she did not put the database name in front of the stored procedure name.
• C. The stored procedure named get_reports will be dropped.
• D. The user will get an error because he or she does not have the permission to drop stored procedure

Answer: C

NEW QUESTION 7
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 = Compressed Backup Operation = copy-back
• B. Backup State = Raw Backup Operation = apply-log
• C. Backup State = Prepared Backup Operation = validate
• D. Backup State = Prepared Backup Operation = apply-log
• E. Backup State = Raw Backup Operation = backupdir-to-image

Answer: E

NEW QUESTION 8
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. There is no password defined in the login path.
• D. The DNS is not configured correctly for slave1 host.
• E. The .mylogin.cnf file is not readabl

Answer: E

NEW QUESTION 9
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?

• 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’;

Answer: BF

NEW QUESTION 10
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?

• A. Implement a failover strategy to another geographic location.
• B. Create a master-master pair for each service.
• C. Have a second data centre in a different region or country.
• D. Keep software updated to the latest version.
• E. Use RAID 10 storage for datA.
• F. Use on-site network-attached storage to separate service from dat

Answer: AC

NEW QUESTION 11
Which MySQL utility program should you use to process and sort the Slow Query Log based on query time or average query time?

• A. mysqldumpslow
• B. mysqldump
• C. mysqlaccess
• D. mysqlshow
• E. mysqlslow

Answer: A

NEW QUESTION 12
Which statement is true about using Microsoft Windows Cluster as a platform for MySQL?

• A. It relies on the shared disk architecture being visible to both servers.
• B. It is provided by means of IP-level disk replication.
• C. It implements High Availability by using the NET Connector's load balancing capabilities.
• D. It is a shared-nothing architectur

Answer: A

NEW QUESTION 13
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?

• A. The salve has two intermediate relay slaves connected to it.
• B. The slave is configured with slave_parallel_workers = 4
• C. At most, two schemas are being updates concurrently.
• D. THREAD_ID 21 has stopped running.
• E. The slave cannot process the relay log fast enough to use all threads.
• F. The server needs more cores to use all slave thread

Answer: BE

NEW QUESTION 14
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 maste

Answer: A

NEW QUESTION 15
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. INSTALL PLUGIN mysql_cleartext_password SONAME ‘mysql_cleartext_password.so’;
• C. export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=’Y’
• D. SET GLOBAL mysql_cleartext_passwords=1;
• E. mysql --enable-cleartext-plugin –uroot –p –h dbhost.example.com

Answer: DE

NEW QUESTION 16
The /myfolder/my.cnf file has option set: [mysqld] skip-log-bin
/myfolder2/my.cnf has this option set: [mysqld] log-bin = /valid/path/to/mysqlbinlog
All mentioned paths are accessible to the account that you are currently using. Assume that any other options mentioned in either file are valid and legal option definitions.
You start an instance by using this command line:
mysqld --defaults-file=/myfolder/my.cnf --defaults-extra-file=/myfolder2/my.cnf What is the outcome?

• A. MySQL starts and Binary Logging is enabled.
• B. MySQL fails to start due to the conflicting options in the configuration files.
• C. MySQL fails to start due to conflicting options on the command line.
• D. MySQL starts but Binary Logging is disable

Answer: C

NEW QUESTION 17
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?

• A. mysql_config_editor is the only MySQL-provided utility that can print the values stored in.mylogin.cnf.
• B. A .mylogin.cnf file can store at most one login path.
• C. It provides a FIPS-compliant keyring for storing MySQL login details.
• D. A .mylogin.cnf file can be edited using a text editor, such as vim or Notepad++.
• E. It is an alternative to storing the MySQL login details in a my.cnf file.
• F. It provides means to help avoid accidentally exposing the MySQL login detail

Answer: EF

NEW QUESTION 18
A simple master-to-slave replication is currently being used. This information is extracted from the SHOW SLAVE STATUS output:

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

The table mytable on the slave contains:

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

• A. SET GTID_NEXT=”CONSISTENCY”; BEGIN; COMMIT;SET GTID_NEXT=”AUTOMATIC”;
• B. SET GTID_NEXT=”5da6b4f5-6f60-11e8-b2d6-0010e05f3e06:8”; BEGIN; COMMIT; SET GTID_NEXT=”AUTOMATIC”;
• C. SET GLOBAL SQL_SKIP_SLAVE_COUNTER=1
• D. SET GLOBAL enforce_gtid_consistency=ON
• E. SET GTID_EXECUTED=”5da6b4f5-6f60-11e8-b2d6-0010e05f3e06:8”;

Answer: C

NEW QUESTION 19
You have a MySQL replication setup and you intentionally stop the SQL thread on the slave.

What are two reasons that you may stop the SQL thread on the slave while keeping the I/O thread running?

• A. to allow the remaining events to be processed on the slave while not receiving new events from the master
• B. to allow a backup to be created under reduced load
• C. to allow for point-in-time recovery on the slave
• D. to prevent schema changes from propagating to the slave before they are validated
• E. to prevent any transaction experiencing a deadlock

Answer: BC

NEW QUESTION 20
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. Date should be a TIMESTAMP field for better performance.
• B. The User field is too long for most names.
• C. The engine type is not appropriate to the application use.
• D. Using default values for DATETIME causes table scans.
• E. No indexes are define

Answer: CE

NEW QUESTION 21
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 bac

Answer: C

NEW QUESTION 22
Consider the join_buffer_size parameter in MySQL Server. Which two statements are true about the join buffer?

• A. The value should be increased if the client performs several SELECT operations.
• B. The join buffer is set per connection.
• C. The join buffer is used to process sorts when complex joins are being performed.
• D. The value should be increased from the default if the query joins large rows without using an index.
• E. The join buffer is global and can be changed only by restarting the serve

Answer: CD

NEW QUESTION 23
Which statement best describes the purpose of the InnoDB buffer pool?

• A. It is amount of buffers available during a transaction.
• B. It caches only the indexes for InnoDB tables.
• C. It caches data and indexes for InnoDB tables.
• D. It holds changes made during a transaction before they are written to the log.
• E. It is a pool of memory for SQL query sort operations from within the InnoDB engin

Answer: C

NEW QUESTION 24
......