Q41.Examine these entries from the general query log:

Time                                Id   Command    Argument

2019-12-17T00:36:23.389450z        24  Connect      root@localhost on mydb using SSL/TLS

2019-12-17T00:36:23.389450z        24  Query        select @@version_comment limit 1

2019-12-17T00:36:23.929519z        25  Connect      root@localhost on mydb using SSL/TLS

2019-12-17T00:36:23.929846z        25  Query        select @@version_comment limit 1

2019-12-17T00:36:27.633082z        24  Query        START TRANSACTION

2019-12-17T00:36:30.321657z        24  Query        UPDATE t1 SET val =1 WHERE ID=130

2019-12-17T00:36:32.417433z        25  Query        START TRANSACTION

2019-12-17T00:36:33.617642z        25  Query        UPDATE t2 SET val =5 WHERE ID=3805

2019-12-17T00:36:36.045498z        25  Query        UPDATE t1 SET val =10 WHERE ID=130

2019-12-17T00:36:33.617642z        25  Query        UPDATE t2 SET val =5 WHERE ID=3805

2019-12-17T00:36:38.513674z        24  Query        UPDATE t2 SET val =42 WHERE ID=3805

All UPDATE statements reference existing rows.

Which describes the outcome of the sequence of statements?

A)A deadlock occurs after innodb_lock_wait_timeout seconds.

B)Connection 24 experiences a lock wait timeout.

C)Connection 25 experiences a lock wait timeout.

D)All statements execute without error.

E)A deadlock occurs immediately.

Answer:E

Q42.Examine this output:

Mysql>SELET FORMAT_BYTES(@@global.innodb_buffer_pool_size)AS BufferPoolSize,

       @@global.innodb_buffer_pool_instances AS NumInstances,

       FORMAT_BYTES(@@global.innodb_buffer_buffer_poof-chunk-size) AS Chunksize;

+----------------------+--------------------+----------------+

|BufferPoolSize      |  NumInstances   | ChunkSize     |

+----------------------+--------------------+----------------+

|12.00 GiB           |          8       | 128.00 MiB    |

+----------------------+--------------------+----------------+

Mysql> SELECT * FROM sys.metrics WHERE Variable_name LIKE ‘Threads%’;

+----------------------+--------------------+----------------+-------------+

|Variable_name      |Variable_b=value   |Type          |Enabled    |

+----------------------+--------------------+----------------+-------------+

|Threads_cached      | 4                 |Global Status  |YES        |

|Threads_cached      | 32                |Global Status  |YES        |

|Threads_cached      | 112               |Global Status  |YES        |

|Threads_cached      | 16                |Global Status  |YES        |

+----------------------+--------------------+----------------+-------------+

4 rows in set (0.00 sec)

Which change should optimize the number of buffer pool instances for this workload?

A)Decrease the number of buffer pool instances to 4.

B)Increase the number of buffer pool instances to 16.（thread_running 是 16）

C)Increase the number of buffer pool instances to 12.

D)Decrease the number of buffer pool instances to 1.

E)Increase the number of buffer pool instances to 32.

Answer:B

Q43,Which three requirements must be enabled for group replication?

A)primary key or primary key equivalent on every table

B)semi-sync replication plugin

C)binary log ROW format

D)binary log MIXED format

E)replication filters

F)binary log checksum

G)slave updates logging

Answer:ACG

Q44.Examine this command, which executes successfully on InnoDB Cluster: dba.dropMetadataSchema()

Which two statements are true?

A)The command drops the mysql_innodb_cluster_metadata schema and re-creates it.(不重建)

B)The mysql_innodb_cluster_metadata schema is dropped from the instance where the connection was established.

C)Connections driven by MySQL Router are not affected by the command.（元数据已经没有）

D)The mysql_innodb_cluster_metadata schema is dropped from all reachable members of the cluster.

E)Group Replication will be dissolved and all metadata purged.（需要执行 dissolve）

F)Group Replication is still operational, but InnoDB Cluster must be reimported under MySQL Shell.

Answer:DF

Q45.Which three commands can report all the current connections running on the MySQL server?

A)SELECT * FROM sys.metrics

B)SELECT * FROM information_schema.events

C)SELECT * FROM sys.statement_analysis

D)SELECT * FROM information_schema.processlist

E)SHOW EVENTS

F)SHOW FULL PROCESSLIST

G)SELECT * FROM performance_schema.threads

H)SELECT * FROM performance_schema.events_transactions_current

Answer:DFG

Q46.Examine this statement, which executes successfully:

CREATE TABLE employess(

emp_no int unsigned NOT NULL,

Birth_date date NOT NULL,

First_name varchar(14) NOT NULL,

Last_name varvhar(16) NOT NULL,

Hire_date date NOT ULL,

PRIMARY KEY(emp_no)

)ENGIN=InnoDB;

Now examine this query:

SEECR emp_no,first_name,last_name,bitrh_date

FRON employees

WHERE MONTH(birth)date)=4;

You must add an index that can reduce the number of rows processed by the query. Which two statements can do this?

A)ALTER TABLE employees ADD INDEX ((MONTH(birth_date)));

B)ALTER TABLE employees ADD INDEX (birth_date);

C)ALTER TABLE employees ADD COLUMN birth_month tinyint unsigned GENERATED ALWAYS AS (MONTH(birth_date)) VIRTUAL NOT NULL, ADD INDEX (birth_month);

D)ALTER TABLE employees ADD INDEX (birth_month);

E)ALTER TABLE employees ADD INDEX ((CAST(birth_date->>'$.month' AS unsigned)));

F)ALTER TABLE employees ADD COLUMN birth_month tinyint unsigned GENERATED ALWAYS AS (birth_date->>'$month') VIRTUAL NOT NULL, ADD INDEX (birth_month);

Answer:AC

Q47.Which three are types of InnoDB tablespaces?

A)schema tablespaces

B)temporary table tablespaces

C)encryption tables

D)data tablespaces

E)redo tablespaces

F)undo tablespaces

Answer:BDF

Q48.On examination, your MySQL installation datadir has become recursively world read/write/executable. What are two major concerns of running an installation with incorrect file privileges?

A)Data files could be deleted. (datafiles)    

B)Users could overwrite configuration files.(mysqld-auto.ccnf)

C)SQL injections could be used to insert bad data into the database.

D)Extra startup time would be required for the MySQL server to reset the privileges.（没有这个功能）

E)MySQL binaries could be damaged, deleted, or altered.（basedir 中才有 MySQL binaries）

Answer:AB

Q49.Which two tools are available to monitor the global status of innodb locking?

A)INFORMATION_SCHEMA.INNODB_METRICS

B)SHOW ENGINE INNODB STATUS;

C)INFORMATION_SCHEMA.STATISTICS

D)SHOW STATUS;

E)SHOW TABLE STATUS;

F)INFORMATION_SCHEMA.INNODB_TABLESTATS

Answer:AB

Q50.You want to dump all databases with names that start with "db". Which command will achieve this?

A)mysqldump --include-tables=db.% --result-file=all_db_backup.sql

B)mysqldump --include-databases=db --result-file=all_db_backup.sql

C)mysqldump --include-databases=db% --result-file=all_db_backup.sql

D)mysqldump > all_db_backup.sql

Answer:D