2024年4月4日发(作者：)

MySQL 5.7 OCP 考试习题大全

Question: 1

A MySQL database uses all InnoDB tables and is configured as follows:

shell>cat/ etc/

[mysqld]

log-bin

server-id=1

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: DE

Question: 2

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 only.

Answer: A,C

Question: 3

You have a MySQL replication setup and you intentionally stop the SQL thread on the slave.

mysql> SHOW SLAVE STATUS G

...

Slave_IO_Running: Yes

Slave_SQL_Running: No

What are two reasons that you may stop the SQL thread on the slave while keeping the I/ O

thread running?

A. to allow the remaining events to be processed on the slave while not receiving new events

from the master

B. to allow a backup to be created under reduced load

C. to allow for point-in-time recovery on the slave

D. to prevent schema changes from propagating to the slave before they are validated

E. to prevent any transaction experiencing a deadlock

Answer: C，D

Question: 4

Which three statements correctly describe MySQL InnoDB Cluster?

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 nodes.

Answer: ABD

Question: 1

A MySQL database uses all InnoDB tables and is configured as follows:

shell>cat/ etc/

[mysqld]

log-bin

server-id=1

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

答案: DE

Question: 2

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 only.

答案: A,C

Question: 3

You have a MySQL replication setup and you intentionally stop the SQL thread on the slave.

mysql> SHOW SLAVE STATUS G

...

Slave_IO_Running: Yes

Slave_SQL_Running: No

What are two reasons that you may stop the SQL thread on the slave while keeping the I/ O

thread running?

A. to allow the remaining events to be processed on the slave while not receiving new events from the

master

B. to allow a backup to be created under reduced load

C. to allow for point-in-time recovery on the slave

D. to prevent schema changes from propagating to the slave before they are validated

E. to prevent any transaction experiencing a deadlock

答案: C，D

Question: 4

Which three statements correctly describe MySQL InnoDB Cluster?

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 nodes.

第 2页共 3页

答案: ABD

Question: 5（死锁）

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 back.

答案: C

Question: 6

/doc/refman/5.6/en/

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

答案: C,D,E

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: mysqld --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 tomysqld.

E. Create additional login accounts (so that everyone does not need to log in as root) and assign

them appropriate privileges.

答案: A

Question: 8

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 startup.

答案: A,D

Question: 9

Which two statements are true about InnoDB auto-increment locking?

A. InnoDB never uses table_level locks.

B. InnoDB always protects auto-increment updates with a table-level lock

C. InnoDB does not use locks to enforce auto-increment uniqueness.

D. The auto-increment lock can be a table-level lock.

E. Some settings for innodb_autoinc_lock_mode can help reduce locking.

答案: D,E

Question: 10

You have just executed a manual backup by using this command:

mysqlbackup -u root -p --socket=/tmp/ --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 = backup-dir-to-image

答案: B

Question: 11

Host slave1 has ip address 192.0.2.10.

Host slave2 has ip address 203.0.113.50

Examine these commands:

Why did this error occur?

A. The host on the command line is not defined in the login path.

B. The mysqld instance has not been restarted after creating the login path.

C. There is no password defined in the login path.

D. The DNS is not configured correctly for slave1 host.

E. The file is not readable.

答案: C

Question: 12

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_ file

B. Run the mysql_ file

C. Invoke mysqld with the --initialize option.

D. Invoke mysql with the --initialize option.

答案: C

Question: 13

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 … FOREXPORT

C. an up-to-date replication slave

D. a file system-level snapshot

E. a file system copy created while MySQL was shut down.

答案: A,B

Question: 14

You created a backup of the world database with this command: shell>

mysqldump --opt world >

Which two will import the data from ?

A. shell> mysqladmin recover test

B. shell> mysql test <

C. shell> mysqlimport test

D. mysql> USE test; mysql> LOAD DATA INFILE ‘’;

E. mysql>USE test; mysql>SOURCE ;

答案: BE

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 file (for example /etc/mysql/) 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/

答案: E

Question: 16

The following grants were executed:

GRANT CREATE ROUTING ON sales.* TO ‘webadmin’@’%’;

GRANT ALTER ON PROCEDURE 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

procedures.

答案: C

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 applicationlevel 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 () 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 losingan

employee who knew the current password

答案: ABF

Question: 18

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

答案: A

Question: 19

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 InnoDBengine.

答案: C

Question: 20

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’

答案: A

Question: 21

Consider that local disk files are accessible via MySQL with commands such as:

mysql> LOAD DATA LOCAL INFILE ‘/etc/passwd’ INTO TABLE mypasswords;

What change could be made to stop any breach via this insecurity?

A. executing REVOKE LOAD FROM *.*

B. setting the --local-service=0 option when starting mysqld

C. executing REVOKE FILE FROM *_*

D. executing REVOKE FILE ON *_* FROM ‘ ‘@’%’

E. setting the --local-infile=0 option when startingmysqld

F. setting the --open-files-limit=0 option when starting mysqld

答案: E

Question: 22

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

答案: CEF

Question: 23

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

答案: A

Question: 24

You are using the Performance Schema to investigate replication on a slave which has a single

master. The option slave-parallel-type is set to DATABASE.

Assume that all instruments and consumers are enabled and all threads are instrumented.

Which two facts can be concluded from the given output?

A. The salve has two intermediate relay slaves connected to it.

B. The slave is configured with slave_parallel_workers = 4

C. At most, two schemas are being updates concurrently.

D. THREAD_ID 21 has stopped running.

E. The slave cannot process the relay log fast enough to use all threads.

F. The server needs more cores to use all slave threads.

答案: B,C

Question: 25

A master-slave replication setup has the slave showing this error:

What could explain this error? (Choose two.)

A. binlog_cache_size=1024 is too small and transactions are lost.

B. binlog_format=STATEMENT and a non-deterministic query was executed.

C. enforce_gtid_consistency=ON and consistency is broken between the master and the slave.

D. The sync_relay_log=1000 setting on the slave is too small.

E. sync_binlog=0 and the master server crashed.

答案: CE

如果是单选，只选 E

Question: 26

What two statements are true regarding FLUSH TABLES FOR EXPORT?

A. It can be used to export TEMPORARY tables.

B. Table only exports when the table has its own tablespace.

C. The InnoDB Storage engine must be used for the table being exported.

D. It is the safest way to extract tables from the shared tablespace.

E. Partitioned tables are not supported.

答案: BC

Question: 27

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 master.

答案: B

Question: 28

The MySQL error log shows:

InnoDB: Warning: a long semaphore wait:

The relevant parts of the InnoDB monitor output shows:

A. Increase the value of the innodb_lock_wait_timeout option.

B. Increase the value of the innodb_read_io_threads option.

Which two options would help avoid the long wait in the future?

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 pool.

答案: DE

Question: 29

Examine the mydata table and SELECT statements:

You issue:

mysql> begin;

mysql> update mydata set a=0 where b=3;

How many rows are now protected by locks with the default InnoDB configuration?

A. one

B. one row and a next-key lock for supremum

C. one row and a gap-lock

D. five

答案: D

Question: 30

You have a consistent InnoDB backup created with mysqldump, the largest table is 50 GB in size.

You start to restore your backup with this command;

shell> mysql –u root –p <

After 30 minutes, you notice that the rate of restore seems to have slowed down. No other

processes or external factors are affecting server performance.

Which is the most likely explanation for this slowdown?

A. The MySQL server has stopped inserting data to check index consistency.

B. InnoDB is doing CRC32 checks over the tablespace data as it grows.

C. The MySQL server is taking a periodical snapshot of data so it can resume the restore if it is

interrupted mid-way.

D. InnoDB has filled the redo log and now must flush the pages.

E. Secondary indexes no longer fit into the buffer pool.

答案: D

Question: 31

A simple master-to-slave replication is currently being used. This information is extracted from

the SHOW SLAVE STATUS output:

Last_SQL_Error: Error 'Duplicate entry '8' for key 'PRIMARY' ' on query. Default database: 'mydb'.

Query: 'insert into mytable VALUES ('8', 'George') '

Skip_Counter: 0

Retrieved_Gtid_Set: 5da6b4f5-6f60-11e8-b2d6-0010e05f3e06: 1-8

Executed_Gtid_Set: 0 25da6b4f5-6f60-11e8-b2d6-0010e05f3e06: 0 21-7

62706329-6f60-11e8-b64f-0010e05f3e06: 1

Auto-Position: 1

You execute a ‘SHOW CREATE TABLE mytable” on the slave:

CREATE TABLE 'mytable' (

'ID' int (11) NOT NULL DEFAULT '0' ,

'name' char (10) DEFAULT NULL,

PRIMARY KEY ('ID')

)

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 GLOBALenforce_gtid_consistency=ON

E. SET GTID_EXECUTED=”5da6b4f5-6f60-11e8-b2d6-0010e05f3e06:8”;

答案: B

Question: 32

The MySQL installation includes the mysql_config_editor utility for managing login paths stored

in a . 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 ..

B. A . file can store at most one login path.

C. It provides a FIPS-compliant keyring for storing MySQL login details.

D. A . 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 file.

F. It provides means to help avoid accidentally exposing the MySQL login details.

答案：EF

Question: 33

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

答案: C

Question: 34

Which two statements are true regarding the creating of new MySQL physical and logical

backups?

A. Physical backups can be used to recover from data corruption.

B. Logical backups are human-readable whereas physical backups are not.

C. Logical backups are always larger than physical backups.

D. Physical backups are usually slower than text backups.

E. Physical backups are usually faster than text backups.

答案: BE

Question: 35

Is it true that binary backups always take less space than text backups?

A. Yes, because binary backups only contain data, and not statements required to insert data into

the tables.

B. No, because text backups can have optimizations, which make them smaller, such as updating

many rows at once.

C. No, because if InnoDB tables contain many empty pages, they could take more space than the

INSERT statements.

D. Yes, because even if InnoDB tables contain many empty pages, text backups have empty

INSERT statements for them.

答案: C

Question: 36

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 secure.

答案: D

Question: 37

You have the following in your 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';

答案: BC

Question: 38

You have created a new user with this statement:

CREATE USER ‘erika’@’localhost’ IDENTIFIED BY ‘first#1Pass’ PASSWORD EXPIRE;

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, theuser 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 time.

答案:

C

Question: 39

What is the order of tables shown in an EXPLAIN output?

A. It lists tables from the smallest to the largest.

B. It lists tables in the order in which their data will be read.

C. It lists tables from the most optimized to the least optimized.

D. It lists tables in the order in which they are specified in the statement that is being explained.

答案:

B

Question: 40

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 query.

答案: ABD

Question: 41

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 becinstallmes 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 available.

答案: B

Question: 42

Consider the table people with the definition:

CREATE TABLE 'people' (

'id' int (10) unsigned NOT NULL AUTO_INCREMENT,

'FirstName' varchar (40) NOT NULL,

'Surname' Varchar (40) NOT NULL,

'Birthday' date NOT NULL,

PRIMARY KEY ('id'),

KEY 'Surname' ('Surname', 'FirstName'),

KEY 'FirstName' ('FirstName'),

KEY 'Birthday' ('Birthday')

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

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 query.

答案: AD

Question: 43

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

答案: B

Question: 44

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 column.

答案: AF

Question: 45

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

statement.

答案: A

*******

Question: 46

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 examined.

答案: A

Question: 47

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 datA.

答案: AC

Question: 48

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 key.

答案: D

Question: 49

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 used.

答案: B,E

log_queries_not_using_indexes

long_query_time

Question: 50

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 threads.

答案:BFG

Question: 51

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 of 2G.

. 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

答案:ACFG

Question: 52

A MySQL Server has been running an existing application successfully for six months. The

is adjusted to contain this additional configuration:

The MySQL Server is restarted without error.

What effect will the new configuration have on existing account?

[mysqld]

default-authentication-plgin=sha256_password

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 server.

答案: A

Question: 53

You want to create a temporary table named OLD_INVENTORY in the OLD_INVENTORY database

on the master server. This table is not to be replicated to the slave server.

Which two changes would ensure that the temporary table does not propagate to the slave?

A. Set binlog_format=MIXED with the --replicate-ignore-temp-table option.

B. Use the --replicate-do-db, --replicate-do-table, or --replicate-wild-do-table option with the

value equal to OLD_INVENTORY.

C. Change the binlog_format option to ROW and restart mysqld before you create the

OLD_INVENTORY table.

D. Stop SQL_THREAD on the slave until you have finished using the OLD_INVENTORY temporary

table.

E. Use the --replicate-ignore-table option with the value equal to

OLD__INVENTORY and restart mysqld before creating the temporary table.

答案: CE

Question: 54

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:

2013-08-27 13:55:44 9056 [EROR] Slave SQL: Cloud not execute Write rows event on table test.t1;

Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error

HA_ERR_FOUND_DUPP_KEY; the event's master log 56_master-bin.000003, end_log_pos 653,

Error_code: 1062

2013-08-27 13:55:44 9056 [Warning] Slave: Duplicate entry '3' for key 'PRIMARY' Error code:

1062

2013-08-27 13:55:44 9056 [ERROR] Error running query, slave SQL thread aborted. Fix the

problem, and restart the slave SQL thread with 'SLAVE START'. We stopped at log

'56_master-bin.000003' position 496

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/

E. The slave user does not have INSERT, UPDATE, or DELETE permission and cannot execute the

Write_rows function.

答案: AD

Question: 55

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 , 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 line.

答案: CD

Question: 56

You are contacted by a user who does not have permission to access a database table. You

determine after investigation that this user should be permitted to have access and so you

execute a GRANT statement to enable the user to access the table.

Which statement describes the activation of that access for the user?

A. The access does not take effect until the user logs out and back in.

B. The access does not take effect until the next time the server is started.

C. The access is available immediately.

D. The access does not take effect until you issue the FLUSH PRIVILEGES statement.

答案: C

Question: 57

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 dats.

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; CHANGE MASTER TO MASTER_DELAY=0; RELAY_LOG_FILE

'hostname-relay-bin.00004', RELAY_LOG_POS = 1383;

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; SET GLOBAL master_delay=0; START SLAVE;

答案: C

Question: 58

You enable binary logging on MySQL Server with the configuration:

binlog-format=STATEMENT

log-bin

Which database updates are logged on the master server to the binary log by default?

A. all updates except to the TEMPDB database

B. all updates except to the PERFORMANCE_SCHEMA database

C. all updates not involving temporary tables

D. all updates to the default database, except temporary tables

E. all updates to all databases

答案: B

Question: 59

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 location.

答案：CF

Question: 60

One of your colleagues is trying to make a change using the mysql command-line client

for his or her application session.

The colleague instant messages you this command:

mysql> SET SESSION max_connections = 200;

Why does the command fail?

A. max_connections requires the GLOBAL scope.

B. Its current user does not have the SUPER privilege.

C. max_connections is not a dynamic variable. You need to change the config file and

restart the database.

D. Users can control only the max_user_connections variable.

答案: A

Question: 61

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. Yes. Shut down the mysqld process, enable the transportable_tablespace option, and move

the UNDO directory to the SSD.

B. Yes. Shut down, copy the UNDO tablespaces to the new location, and change the

innodb_undo_directory value in your .

C. No. The UNDO tablespaces must remain on the same file system as the system tablespaces.

D. No. The sequential write pattern of the UNDO tablespaces is not supported on modern SSD

block devices.

答案: B

Question: 62

Which two conclusions can be made from the output?

A. There are 140 Performance Schema threads at the time of the output.

B. There are 510 connections to MySQL at the time of the output.

C. The thread cache has been configured with thread_cache_size set to at least 6.

D. There are more connections being idle than executing queries.

E. All max_connections were in use at 2018-03-22 14:54:06

答案: CD

Question: 63

You have a MySQL instance with the following variables in the /etc/ file:

You issue these statements:

USE prices;

UPDATE y 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 one.)

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.

答案: d

Question: 64

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 defaultsettings.

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 recovery.

答案: AB

Question: 65

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

答案: BCD

Question: 66

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 server.

答案: BD

Question: 67

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 defined.

答案: CE

Question: 68

Due to an authentication plug-in that is used on the server, passwords are required to be sent as

clear text as opposed to the usual encrypted format.

Which two methods would allow the mysql client to connect to the server and send clear text

passwords?

A. mysql --protocol=PLAIN –uroot –p –h

B. INSTALL PLUGIN mysql_cleartext_password SONAME ‘mysql_cleartext_’;

C. export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=’Y’

D. SET GLOBAL mysql_cleartext_passwords=1;

E. mysql --enable-cleartext-plugin –uroot –p –h

答案: CE

Question: 69

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

答案: AEG

(如果没有 port，选 max_connections)

Question: 70

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 ’’@’’;

答案: D

Question: 71

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 file in the MySQL /tmp directory was not removed after the reboot, so mysqld

still thinks there is an active server running.

答案: A

Question: 72

The /myfolder/ file has option set:

[mysqld]

skip-log-bin

/myfolder2/ 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/ --defaults-extra-file=/ myfolder2/

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 disabled.

答案: D

Question: 73

You will configure a MySQL Server to act as a replication master. Which two options must

be configured

correctly to allow this?

A. log-master-updates

B. rpl-recovery-rank

C. server-id

D. enable-master-start

E. log_bin

F. master-logging

答案: CE

Question: 74

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 log.

答案: DF

Question: 75

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_ table

D. monitoring datadir size in the operating system

E. monitoring cumulative Innodb_page_size increase

F. monitoring the performance_schema_hosts_size variable

答案: CD

Question: 76

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

答案: D

Question: 77

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 by clause.

答案: AD

Question: 78

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

run.

答案: D

Question: 79

You have a server that has very limited memory but has a very large table.

You will use mysqldump to backup 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

答案: C

Question: 80

Which statement is true about using Microsoft Windows Cluster as a platform for MySQL?

A. It relies on the shared disk architecture being visible to both servers.

B. It is provided by means of IP-level disk replication.

C. It implements High Availability by using the NET Connector's load balancing capabilities.

D. It is a shared-nothing architecture.

答案: A

Question: 81

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

答案: BE

Question: 82

You are using replication and the binary log files on your master server consume a lot of disk

space.

Which two steps should you perform to safely remove some of the older binary log files? (Choose

two.)

A. Execute the PURGE BINARY LOGS NOT USED command.

B. Edit the .index file to remove the files you want to delete.

C. Ensure that none of the attached slaves are using any of the binary logs you want to delete.

D. Remove all of the binary log files that have a modification date earlier than today.

E. Use the command PURGE BINARY LOGS and specify a binary log file name or a date and time

to remove unused files.

答案: CE

Question: 83

What are three methods to reduce MySQL server exposure to remote connections?

A. Setting --skip-networking when remote connections are not required

B. Using the sql_mode=STRICT_SECURE after connections are established for encrypted

communications

C. Setting specific GRANT privilege to limit remote authentication

D. Setting --mysql_secure_configuration to enable paranoid mode

E. Using SSL when transporting data over remote networks

答案: ACE

Question: 84

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 words.

答案: AE

2019 年 2 月份新题

Question: 85

This output is from a SHOW SLAVE STATUS:

What would cause the SQL_Delay variable to have a value of 360?

答案为 B

B) the slave was configured for delayed replication with a delay of six minutes

答案: B

Question: 86

Which MySQL utility copies the master instance to a slave instance on the same host?

A) Mysqlserverclone