NEW QUESTION 1
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 2
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 3
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 4
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 5
An existing master-slave setup is currently using a delayed replication of one hour. The master has crashed and the slave
must be “rolled forward” to provide all the latest datA. The SHOW SLAVE STATUS indicates these values: RELAY_LOG_FILE=hostname-relay-bin.00004 RELAY_LOG_POS=1383
Which command set would make the slave current?

• A. STOP SLAVE; SET GLOBAL master_delay=0; START SLAVE;
• B. STOP SLAVE; CHANGE MASTER TO RELAY_LOG_FILE = ‘hostname-relay-bin.00004’, RELAY_LOG_POS = 1383;
• C. STOP SLAVE; CHANGE MASTER TO MASTER_DELAY=0; START SLAVE;
• D. STOP SLAVE; CHANGE MASTER TO MASTER_DELAY=0; RELAY_LOG_FILE = ‘hostname-relay-bin.00004’, RELAY_LOG_POS = 1383;

Answer: C

NEW QUESTION 6
Which are three facts about backups with mysqldump?

• A. will lock all storage engines for duration of backup
• B. can back up a remote database server
• C. allow a consistent backup to be taken
• D. are able to back up specific items within a database
• E. create automatically compressed backups
• F. are always faster to restore than binary backups

Answer: BCD

NEW QUESTION 7
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 number of characters indexed in the key.
• C. It shows the total size of the index row.
• D. It shows how many columns in the index are examine

Answer: A

NEW QUESTION 8
You back up by using mysqldump.
Which configuration is required on the MySQL Server to allow point-in-time recovery?

• A. binlog_format=STATEMENT
• B. log-bin
• C. apply-log
• D. bonlog_format=ROW
• E. gtid_enable

Answer: B

NEW QUESTION 9
You have created a new user with this statement:
CREATE USER ‘erika’@’localhost’ IDENTIFIED BY ‘first#1Pass’ PASSWORDEXPIRE; What is the outcome?

• A. When ‘erika’@’localhost’ tries to log in with the MySQL command-line client, the user will have to change the password before seeing the mysql> prompt.
• B. When ‘erika’@’localhost’ tries to log in with the MySQL command-line client, the user will not be permitted to log in because the password is expired.
• C. When ‘erika’@’localhost’ tries to log in with the MySQL command-line client, the user will be permitted to log in but will not be able to issue ant statements until the user changes the password.
• D. You receive a syntax error that indicates that you cannot set a password and expire it at the same tim

Answer: A

NEW QUESTION 10
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 11
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. Change the WHERE clause to Birthday BETWEEN 1980-01-01 AND 1980-12-31.
• B. Add a functional index for YEAR(Birthday).
• C. Execute ANALYZE TABLE to update the index statistics.
• D. Add a generated column calculating YEAR(Birthday) and index that column.
• E. Add FORCE INDEX (Birthday) to the quer

Answer: AE

NEW QUESTION 12
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 13
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 14
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 G.
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. table_open_cache = 64
• B. innodb_buffer_pool_instances=64 innodb_buffer_pool_size=200G
• C. log_bin=mysql –bin Innodb_flush_log_at_trx_commit=1
• D. sync_binlog=10
• E. innodb_flush_method=O_DIRECT
• F. max_heap_table_size = 2G tmp_table_size=2G
• G. query_cache_size = 2G query_cache_enabled=1
• H. innodb_flush_log_at_trx_commit=0

Answer: ABEG

NEW QUESTION 15
You have installed MySQL Server for the first time on your system. However, the data directory along with the tables in the mysql system database are missing. Which step do you perform to create the contents of the data directory?

• A. Run the create_system_tables.sql file
• B. Run the mysql_unpack.sql file
• C. Invoke mysqld with the --initialize option.
• D. Invoke mysql with the --initialize optio

Answer: C

NEW QUESTION 16
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. Increase the value of the innodb_lock_wait_timeout option.
• B. Increase the value of the innodb_read_io_threads option.
• C. Change the table to use HASH indexes instead of BTREE indexes.
• D. Set the value of innodb_adaptive_hash_index to zero.
• E. Deactivate the query cache.
• F. Increase the size of the InnoDB buffer poo

Answer: BF

NEW QUESTION 17
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 18
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 19
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 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
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 22
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 23
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 24
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 25
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 26
......