1z0-888 | Update 1z0-888 Preparation Exams 2021

NEW QUESTION 1
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 2
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 3
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?

• A. --skip-opt
• B. --lock-all-tables
• C. --create-apply-log
• D. --single-transaction
• E. --master-data

Answer: AB

NEW QUESTION 4
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 variabl
• D. You need to change the config file and restart the database.
• E. Users can control only the max_user_connections variabl

Answer: A

NEW QUESTION 5
After rebooting the host, you attempt to start the mysqld service. 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 mysql service has already been started on the same port.
• B. The network service process in the server is frozen, so all TCP/IP connections are paused and cannot be reused.
• C. You failed to specify the port number 3306 to the command to start the server, so it is defaulting to port 80, which is in use by the built-in web server.
• D. The /etc/hosts file does not have a valid IP entry for mysqld localhost, so it is binding to 127.0.0.1, which is already in use.
• E. The mysql.sock file in the MySQL /tmp directory was not removed after the reboot, so mysqld still thinks there is an active server running.

Answer: E

NEW QUESTION 6
An administrator installs MySQL to run under a mysql OS account. The administrator decides to disable logins to the mysql account by using /nologin or /bin/false as the user’s shell setting.
Which statement is true?

• A. The mysql user needs a login and its home directory must be the base directory of the installation.
• B. The OS needs to allow logging in as mysql so that administrative tasks can be performed.
• C. This prevents mysqld from starting when standard startup scripts are used.
• D. This prevents creation of a command shell with the mysql account, while allowing mysqld to ru

Answer: A

NEW QUESTION 7
You are contacted by a user who does not have permission to access a database table. You determine after investigation that this user should be permitted to have access and so you execute a GRANT statement to enable the user to access the table.
Which statement describes the activation of that access for the user?

• A. The access does not take effect until the user logs out and back in.
• B. The access does not take effect until the next time the server is started.
• C. The access is available immediately.
• D. The access does not take effect until you issue the FLUSH PRIVILEGES statemen

Answer: C

NEW QUESTION 8
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 -- basedir value) for your operating system.
Which step should you perform before defining your own databases and database tables?

• A. Execute a command with a minimal form of: mysql --initialize
• B. Register mysqld as a service that will start automatically on this host machine.
• C. Create a configuration file containing default-storage-engine=InnoDB.
• D. Set an exception in the host machine’s firewall to allow external users to talk to mysqld.
• E. Create additional login accounts (so that everyone does not need to log in as root) and assign them appropriate privileges.

Answer: C

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

• A. writing your client code to properly escape all user input
• B. giving limited privileges to accounts used by application servers to interact with their backing databases
• C. using SSL/TLS on your outward facing web servers (https://) to encrypt all user sessions
• 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. validating all user input before sending it to the database server
• G. changing all passwords for the MySQL account ‘root’@’%’ immediately after losing an employee who knew the current password

Answer: DEG

NEW QUESTION 11
A particular government’s security policy is to have very strict data encryption and safety settings. This is done by restricting access based on their own CA authority and limiting access to particular users within a department. Which method could be used to restrict access as required?

• A. using GRANT … REQUIRE X509 AND REQUIRE ISSUER ‘/C=…..’ AND REQUIRE SUBJECT ‘/C=…..’
• B. using GRANT USAGE, X509, …….ON *.* TO user@remotehost IDENTIFIED BY ‘secret_password’
• C. using GRANT … REQUIRE SSL for a secure connection
• D. using GRANT USAGE, SSL, …..ON *.* TO user@remotehost IDENTIFIED BY ‘secret_password’

Answer: A

NEW QUESTION 12
Which three tasks are handled by the optimizer?

• A. Decide which indexes to use.
• B. Rewrite the WHERE clause.
• C. Parse the query.
• D. Change the order in which the tables are joined.
• E. Validate the query.
• F. Execute the query.
• G. Verify that the user is allowed to execute the quer

Answer: BCF

NEW QUESTION 13
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 14
Which three options are most likely to be changed for production form their default values?

• A. innodb_buffer_pool_size
• B. max_connections
• C. join_buffer_size
• D. character_set_system
• E. innodb_log_file_size
• F. max_user_connections
• G. port

Answer: EFG

NEW QUESTION 15
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 16
Consider the key buffer in a MySQL server. Which two statements are true about this feature?

• A. It caches index blocks for MyISAM tables only.
• B. It caches index blocks for all storage engine tables.
• C. It is a global buffer.
• D. It is set on a per-connection basis.
• E. It caches index blocks for InnoDB tables onl

Answer: AD

NEW QUESTION 17
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 18
Group Replication uses global transaction identifiers to track executed transactions and are fundamental in avoiding transaction conflict. Which additional three steps help in avoiding conflicts in group replication?

• A. Set isolation level to be SERIALIZABLE.
• B. Use the binary log row format.
• C. Set isolation level to be READ COMMITTED.
• D. Configure IPv6 network for hosts.
• E. Guarantee a secondary index on every table.
• F. Guarantee a primary key on every table.
• G. Set multiple slave parallel worker thread

Answer: ABF

NEW QUESTION 19
Which two statements describe how InnoDB recovery works?

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

Answer: BF

NEW QUESTION 20
There are multiple instances of MySQL Server running on a single OS that is backed up using the mysqlbackup command.
The /etc/my/cnf contains default values, for example, datadir=/var/lib/mysql/, with extra instances having their own separate my.cnf file (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?

• A. --restore=2
• B. --copy-back-from-log
• C. --backup-instance=/var/lib/mysql/instance2
• D. --instance=/var/lib/mysql/instance2
• E. --defaults-file=/etc/mysql/instance2.cnf

Answer: E

NEW QUESTION 21
......