NEW QUESTION 1
A MySQL replication slave is set up as follows: Uses all InnoDB tables
Receives ROW-based binary logs Has the read-only option
The replication slave has been found in an error state. You check the MySQL error log file and find these entries:

What are two possible causes for this error to occur?

• A. The applications have the SUPER privilege, which allows them to update rows.
• B. The root user on the slave has executed FLUSH LOGS, causing the relay-log to doublewrite.
• C. For tables with UNIQUE keys, statement-based replication must be used to maintain integrity.
• D. The slave was created with mysqldump –u root –p --skip-lock-tables –all-databases > /data/datA.sql
• E. The slave user does not have INSERT, UPDATE, or DELETE permission and cannot execute the Write_rows function.

Answer: CD

NEW QUESTION 2
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 3
A MySQL server was initialized with separate UNDO tablespaces. Users complain that when they roll back large transactions, the time to process the request takes too long. The DBA would like to move the MySQL InnoDB UNDO tablespace to a solid-state drive (SSD) for better performance. Is this possible and how?

• A. Ye
• B. Shut down the mysqld process, enable the transportable_tablespace option, and move the UNDO directory to the SSD.
• C. Ye
• D. Shut down, copy the UNDO tablespaces to the new location, and change the innodb_undo_directory value in your my.cnf.
• E. N
• F. The UNDO tablespaces must remain on the same file system as the system tablespaces.
• G. N
• H. The sequential write pattern of the UNDO tablespaces is not supported on modern SSD block device

Answer: C

NEW QUESTION 4
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 5
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 6
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 7
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 8
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 9
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 10
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 11
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 12
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 13
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 14
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 15
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 16
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 17
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 18
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