Question: 1
A MySQL database uses all InnoDB tables and is configured as follows:
shell>cat/ etc/ my.cnf
[mysqld]
log-bin
server-id=1
You will be setting up a replication slave by using mysqldump. You will need a consistentbackup
taken from your running production server. The process should have minimal impact toactive
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: B,C
Question: 4
Which three statements correctly describe MySQL InnoDB Cluster?
A. The cluster can be operated in multimaster mode with conflict detection for DMLstatements.
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: 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.
Answer: C
Question: 6
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: 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 to mysqld.
E. Create additional login accounts (so that everyone does not need to log in as root) and assign
them appropriate privileges.
Answer: 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.
Answer: 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.
Answer: D,E
Question: 10
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 = backup-dir-to-image
Answer: 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 mylogin.cnf file is not readable.
Answer: 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_tables.sql file
B. Run the mysql_unpack.sql file
C. Invoke mysqld with the --initialize option.
D. Invoke mysql with the --initialize option.
Answer: 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 … 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 down.
Answer: A,B
Question: 14
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 mysqlimport test dump.sql
D. mysql> USE test; mysql> LOAD DATA INFILE ‘dump.sql’;
E. mysql>USE test; mysql>SOURCE dump.sql;
Answer: 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
instanceshaving their own separate my.cnf file (for example /etc/mysql/instanceN.cnf) 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/instance2.cnf
Answer: E
Question: 16
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
procedures.
Answer: 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 fromhappening?
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 MySQLtables
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
anemployee who knew the current password
Answer: ABF
Question: 18
What is the best method for monitoring Group Replication conflict resolution?
A. the PERFORMANCE_SCHEMA tables
B. the SHOW PROCESSLIST command