NEW QUESTION 1
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 2
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 3
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 4
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 5
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 6
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 7
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 8
A single InnoDB table has been dropped by accident. You are unable to use an additional intermediate MySQL instance to restore the table. Which two backup methods can be used to restore the single table without stopping the MySQL instance?

• A. a backup created with mysqldump --all-databases
• B. a backup created using FLUSH TABLES … FOR EXPORT
• C. an up-to-date replication slave
• D. a file system-level snapshot
• E. a file system copy created while MySQL was shut dow

Answer: AD

NEW QUESTION 9
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. validate_password_policy cannot be set without restarting the MySQL instance.
• B. The name of the dictionary file is too obvious.
• C. The dictionary file word list is too short.
• D. validate_password_dictionary_file cannot be set without restarting the MySQL instance.
• E. The validate_password plug-in has not been loaded.
• F. The dictionary file is an insecure locatio

Answer: BF

NEW QUESTION 10
A MySQL Server has been running an existing application successfully for six months. The my.cnf is adjusted to contain this additional configuration:

The MySQL Server is restarted without error.
What effect will the new configuration have on existing account?

• A. They are not affected by this configuration change.
• B. They all connect via the secure sha256_password algorithm without any configuration change.
• C. They will have their passwords updated on start-up to sha256_password format.
• D. They will have to change their password the next time they login to the serve

Answer: A

NEW QUESTION 11
On a master server that is using statement-based replication, a table of log data has become very large. You decide to delete 100.000 rows.
Which two methods can be independently invoked to ensure that the delete is properly propagated to the slave? (Choose two.)

• A. Change the replication mode to mixed before issuing any delete statements when the limit clause is used.
• B. If the data modification is non-deterministic, the query optimizer will resolve any potential issues.
• C. Use the limit clause to limit the deletion to 100.000 rows.
• D. Use the limit clause in conjunction with the order 3Y claus

Answer: AD

NEW QUESTION 12
old_alter_table is disabled as shown. mysql> SELECT @@old_alter_table;

Consider this statement on a RANGE-partitioned table: mysql> ALTER TABLE orders DROP PARTITION p1, p3; What is the outcome of executing this statement?

• A. All data in p1 and p3 partitions is removed and the table definition is changed.
• B. All data in p1 and p3 partitions is removed, but the table definition remains unchanged.
• C. Only the first partition (p1) will be dropped because only one partition can be dropped at any time.
• D. It results in a syntax error because you cannot specify more than one partition in the same statemen

Answer: B

NEW QUESTION 13
Examine the mysqldumpslow output:

Which two options could explain the slow query?

• A. There is network congestion between client and server.
• B. No index has been defined on the filtered column.
• C. There are 108 queries still being executed.
• D. A table lock is causing delays.
• E. A full table scan is being use

Answer: AE

NEW QUESTION 14
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 15
These details are shown when logged in to an account:

Which set of statements would match the accounts shown?

• A. mysql> CREATE USER ‘employee’@’localhost’ IDENTIFIED BY ‘more_secrets’; mysql> CREATE USER ’’@’’ IDENTIFIED BY ‘valid_password’ WITH PROXY ‘employee’@’localhost’;
• B. mysql> CREATE USER ‘employee’@’localhost’ IDENTIFIED BY ‘more_secrets’; mysql> GRANT PROXY ON ‘employee’@’localhost’ TO ‘robert’@’localhost’;
• C. mysql> CREATE USER ‘robert’@’localhost’ IDENTIFIED BY ‘secret_password’; mysql>CREATE USER ‘employee’@’localhost’ IDENTIFIED BY ‘more_secrets’;
• D. mysql> CREATE_USER ’’@’’ IDENTIFIED WITH authentication_pam ACCOUNT LOCK; mysql> CREATE USER ‘employee’@’localhost’ IDENTIFIED BY ‘more_secrets’;mysql> GRANT PROXY ON ‘employee’@’localhost’ TO ’’@’’;

Answer: D

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
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 18
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 19
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 20
Consider the CHECK TABLE command.
In which two situations should this command be used? (Choose two.)

• A. to find out why a query takes a long time to execute on a given table
• B. to make sure a table has no structural problems
• C. to improve performance by updating index distributing statistics on InnoDB tables
• D. to repair table structure problem
• E. to make sure that no table indexes are corrupted

Answer: BE

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 21
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 22
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 23
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 24
You have a server that has very limited memory but has a very large table. You will use mysqldump to back up this table.
Which option will ensure mysqldump will process a row at a time instead of buffering a set of rows?

• A. --tab
• B. --single-transaction
• C. --quick
• D. --skip-buffer

Answer: C

NEW QUESTION 25
......