1z0-888 Exam - MySQL 5.7 Database Administrator

NEW QUESTION 1
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 2
Which two options describe how MySQL Server allocates memory?

• A. Each connection may have its own per-thread memory allocations.
• B. Thread memory is pre-allocated up to thread_cache_size for performance.
• C. Each thread allocates memory from a global pool.
• D. Global memory resources are allocated at server startu

Answer: AD

NEW QUESTION 3
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 4
Which three allocate memory per thread in MySQL?

• A. query cache
• B. thread cache
• C. read buffer
• D. internal temporary table
• E. sort buffer
• F. InnoDB buffer pool instance

Answer: CEF

NEW QUESTION 5
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_upgrade script
• B. the mysql client with the --ignore-password-hashing option
• C. the mysql_secure_installation script to update server security settings
• D. the mysql client with the --enable-cleartext-plugin option
• E. the install plugin command for the mysql_cleartext_password plugin

Answer: C

NEW QUESTION 6
Which three statements correctly describe MySQL InnoDBCluster?

• A. The cluster can be operated in multimaster mode with conflict detection for DML statements.
• B. All MySQL client programs and connectors can be used for executing queries.
• C. It provides fully synchronous replication between the nodes.
• D. There is support for automatic failover when one node fails.
• E. The data is automatically shared between the nodes.
• F. Each query will be executed in parallel across the node

Answer: BDF

NEW QUESTION 7
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 8
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. You would receive an error on the statement because you cannot update a different database that what is specified with the USE statement.
• B. The changes caused by the UPDATE statement are logged to the binary log because the instance is using --binlog-format = ROW
• C. The statement would fail because you cannot update more than one row at a time when using –binlogformat = ROW.
• D. 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.
• E. Nothing was written to the binary log because you cannot perform a calculation in a query without enclosing the statement in single quotation marks.

Answer: DE

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

Answer: A

NEW QUESTION 10
What are three methods to reduce MySQL server exposure to remote connections? (Choose three.)

• A. using SSL when transporting data over remote networks
• B. using the sql_mode=STRlCT_SECURE after connections are established for encrypted communications
• C. setting —skip-networking when remote connections are not required
• D. setting specific grant privileges to limit remote authentication
• E. setting —mysql_secure_configuration to enable paranoid mode

Answer: ACD

NEW QUESTION 11
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

Answer: A

NEW QUESTION 12
Exhibit:

What does the possible_keys column in this output denote?

• A. if it is possible for you to include any indexes in your query
• B. whether there are any indexes on the tables that you are querying
• C. if there are any indexes that may be used to solve this query
• D. whether there are any indexes in your query

Answer: A

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

• A. it contains the binary log.
• B. it contains a general tablespace.
• C. it is the default location for all new tables that you create.
• D. it contains the system tablespace.
• E. it contains the redo log.
• F. it contains the undo lo

Answer: CD

NEW QUESTION 14
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 15
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 16
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 ke

Answer: B

NEW QUESTION 17
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 18
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 19
Which two are considered good security practices when using passwords? (Choose two.)

• A. Use one-way encryption for storage of passwords.
• B. Store passwords external to the database.
• C. Choose short passwords to save on storage space.
• D. Use simple keyboard actions that give mixed letters.
• E. Do not use dictionary-based word

Answer: AE

NEW QUESTION 20
Which three are key advantages of standard MySQL replication?

• A. supports native automatic failover
• B. enables automatic resync of databases when discrepancies are detected
• C. provides arbitrary geographic redundancy with minimal overhead to master
• D. synchronously guarantees identical slave copy
• E. is easy to configure and has low performance overhead
• F. can easily add slaves for read scaling

Answer: BEF

NEW QUESTION 21
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 22
Which two methods accurately monitor the size of your total database size over time?

• A. monitoring the Innodb_rows_inserted status variable
• B. monitoring the innodb_redo_log_size variable
• C. monitoring the information_schemA.TABLES table
• D. monitoring datadir size in the operating system
• E. monitoring cumulative Innodb_page_size increase
• F. monitoring the performance_schema_hosts_size variable

Answer: CF

NEW QUESTION 23
Consider the index information for the dept_emp table in the employee’s schema:

Which two conclusions can be made based on the output of the query?

• A. There are three indexes on the table.
• B. There is a redundant index on the dept_no column.
• C. The secondary indexes are optimized for unique key look-ups.
• D. The values on the emp_no column must be unique.
• E. The selectivity of the dept_no column is the best of the indexed columns.
• F. There is a redundant index on the emp_no colum

Answer: CF

NEW QUESTION 24
Suppose you are adding rows to a MyISAM table and the --datadir location runs out of disk space. What will happen when this occurs?

• A. The server will crash.
• B. The server suspends that INSERT operation until space becomes available.
• C. An error message will be returned to the client .Server Error: ER_IO
• D. The server suspends operations for all storage engines until space becomes availabl

Answer: B

NEW QUESTION 25
......