You want to dump all databases with names that start with "db".
Which command will achieve this? mysqlpump --include-tables=db.% --result-file=all_db_backup.sql mysqlpump > all_db_backup.sql mysqlpump --include-databases=db --result-file=all_db_backup.sql mysqlpump --include-databases=db% --result-file=all_db_backup.sql.
An attempt to recover an InnoDB Cluster fails.
Examine this set of messages and responses:
host3:3377 ssl JS > dba.rebootClusterFromCompleteOutage() Reconfiguring the default
cluster from complete outage…
The instance ‘host1:3377'’ was part of the cluster configuration. Would you like to rejoin it
to the cluster? [y/N]: y
The instance ‘host2:3377’ was part of the cluster configuration. Would you like to rejoin it to
the cluster? [y/N]: y
Dba.rebootClusterFromCompleteOutage: The active session instance isn’t the most
updated in comparison with the ONLINE instances of the Cluster’s metadata. Please use
the most up to date instance: ‘host1:3377’. (RuntimeError)
Which statement is true? The instance deployed on host3 must be rebuilt with a backup from the primary instance. The cluster is running and there is at least one ONLINE instance. The instance deployed on host3 must be synchronized from a donor deployed on host1 by using the command cluster.addInstance(‘host1:3377’). It is possible to determine the most up-to-date instance by comparing different global transaction identifier (GTID) sets with GTID_SUBSET(set1,set2). The active session instance is invalid and must be re-created by using the command shell.connect (‘host3:3377’).
An existing asynchronous replication setup is running MySQL 8.
Which two steps are a part of implementing GTID replication? (Choose two.) Enable GTID by executing this on the master and the slave: SET GLOBAL GTID_ENABLED=on; Execute this on the slave to enable GTID: START SLAVE IO_THREAD WITH GTID; Restart MySQL (master and slave) with these options enabled:
--gtid_mode=ON
--log-bin
--log-slave-updates
--enforce-gtid-consistency Execute this on the slave to enable GTID: RESET SLAVE; START SLAVE GTID_NEXT=AUTOMATIC; On the slave, alter the MySQL master connection setting with: ALTER channel CHANGE MASTER TO MASTER_AUTO_POSITION = 1; On the slave, alter the MySQL master connection setting with: CHANGE MASTER TO MASTER_AUTO_POSITION = 1;.
You want to check the values of the sort_buffer_size session variables of all existing
connections.
Which performance_schema table can you query? user_variables_by_thread global_variables variables_by_thread session_variables.
You plan to install MySQL Server by using the RPM download.
Which two statements are true? (Choose two.) You can provide the root password interactively. You must manually initialize the data directory. The MySQL RPM package installation supports deploying multiple MySQL versions on
the same host. MySQL uses the RPM relocatable installation target feature. The functionality is split among several RPM package files. You can find the root password in the error log after the first start.
Which two statements are true about MySQL Enterprise Backup? (Choose two.) It supports backing up only table structures. It can perform hot or warm backups. It creates logical backups. It supports the creation of incremental backups. It supports backup of a remote MySQL system. It supports restoring to a remote MySQL system.
Binary log events for the ‘mydb1’ schema must be copied to a different schema name
‘mydb2’.
Which command will do this? mysqlbinlog --rewrite-db=’mydb1->mydb2’ | mysql mysqlbinlog --datebase=mydb1 --database=mydb2 | mysql mysqlbinlog --rewrite-db=’mydb1’ --rewrite-db=’mydb2’ | mysql mysqlbinlog --read-from-remote-server --raw | sed ‘s/mydb1/mydb2/g’ | mysql.
You encountered an insufficient privilege error in the middle of a long transaction.
The database administrator is informed and immediately grants the required privilege:
GRANT UPDATE ON world.city TO ‘user1’;
How can you proceed with your transaction with the least interruption? Roll back the transaction and start the transaction again in the same session. Re-execute the failed statement in your transaction. Change the default database and re-execute the failed statement in your transaction. Close the connection, reconnect, and start the transaction again.
Which step or set of steps can be used to rotate the error log? Execute SET GLOBAL log_error = ‘<new error log file>’. Execute SET GLOBAL max_error_count = <number of messages at point to rotate>. Execute SET GLOBAL expire_logs_days=0 to enforce a log rotation. Rename the error log file on disk, and then execute FLUSH ERROR LOGS.
Examine these commands and output:
(imagen)
Which connection ID is holding the metadata lock? 20 24 21 25 22 6.
Examine this command, which executes successfully:
(imagen)
Which two statements are true? (Choose two.) A single-file backup is created. The backup operation will finish only when backup-and-apply-log is executed. The --backup-dir option holds temporary output, status, and metadata files. The backup operation will finish only when apply-log is executed. A raw backup is created.
Which three statements are true about MySQL replication? (Choose three.) Replication can use only TCP/IP connections. Any instance can have multiple slaves, but it can have only one master. Each instance in a replication topology must have a unique server ID. Binary logs contain only transactions originating from a single MySQL instance. Each slave must have its own MySQL user for replication. Binary logging must be enabled on the master in order to replicate to other instances. A replication user must have the SELECT privilege for all tables that need to be replicated.
Examine this statement, which executes successfully:
(imagen)
You want to improve the performance of this query:
(imagen)
Which change enables the query to succeed while accessing fewer rows? ALTER TABLE world.city ADD SPATIAL INDEX (Name); ALTER TABLE world.city ADD SPATIAL INDEX (Population); ALTER TABLE world.city ADD INDEX (Population); ALTER TABLE world.city ADD INDEX (Name); ALTER TABLE world.city ADD FULLTEXT INDEX (Name); ALTER TABLE world.city ADD FULLTEXT INDEX (Population);.
You issue this command:
SHOW SLAVE STATUS -
In the output, there is a value for Seconds_behind_master.
How is this time calculated? It is the time between the I/O thread receiving details of the master's last transaction and the time it was applied by the SQL thread. It is the time between the most recent transaction written to the relay logs and the time it was committed on the master. It is the time between the I/O thread receiving details of the master’s last transaction and the time it was written to the relay log on the slave. It is the time between the most recent transaction applied by a SQL thread and the time it was committed on the master.
The data in this instance is transient; no backup or replication will be required. It is currently
under performing.
The database size is static and including indexes is 19G.
Total system memory is 32G.
After profiling the system, you highlight these MySQL status and global variables:
(imagen)
The OS metrics indicate that disk is a bottleneck. Other variables retain their default values.
Which three changes will provide the most benefit to the instance? (Choose three.) innodb_flush_log_at_trx_commit=1 buffer_pool_size=24G innodb_log_file_size=1G sync_binlog=0 innodb_doublewrite=0 max_connections=10000 innodb_undo_directory=/dev/shm.
You have configured MySQL Enterprise Monitor to monitor your MySQL server.
Which four features are available? (Choose four.) starting and stopping the MySQL instance tracing import and export with mysqidump deploying the MySQL agent on supported target operating system creating e-mail alerts and SNMP traps for MySQL warnings monitoring the availability of the MySQL instance analyzing executed MySQL queries monitoring of NDB Cluster API nodes.
Examine the modified output:
(imagen)
Seconds_Behind_ Master value is steadily growing. What are two possible causes?
(Choose two.) The master is most probably too busy to transmit data and the slave needs to wait for more data. One or more large tables do not have primary keys. This value shows only I/O latency and is not indicative of the size of the transaction queue. The master is producing a large volume of events in parallel but the slave is processing them serially. The parallel slave threads are experiencing lock contention.
You execute this command:
shell> mysqlpump --exclude-databases=% --users
Which statement is true? It creates a logical backup of all MySQL user accounts. It creates a logical backup of all metadata, but contains no table data. It returns an error because the mysqldump command should have been used. It creates a logical backup of only the users database.
All MySQL Server instances belonging to InnoDB Cluster have SSL configured and
enabled.
You must configure InnoDB Cluster to use SSL for group communication.
Which two statements are true? (Choose two.) SSL group communication must be enabled at cluster creation time by specifying createCluster ({memberSslMode: 'REQUIRED'}). Configuring SSL group communication also configures SSL distributed recovery. An existing InnoDB Cluster must be dissolved and created from scratch to enable SSL for group communication. SSL group communication can be enabled for an existing cluster, one instance at time, by setting group_replication_ssl_mode. If only some InnoDB Cluster members are enabled for SSL group communication, and -- ssl-mode=PREFERRED, communication will fall back to unencrypted connection. SSL group communication requires the use of an additional set of parameters group_replication_recovery_*.
Which three sets of item information are visible in the mysql system database? (Choose
three.) help topics performance monitoring information plugins information about table structures audit log events rollback segments time zone information and definitions.
Examine these statements and output:
(imagen)
Which statement is true? The user is logged in with --user=accounting as an option. The user is authenticated as the anonymous proxy user ‘’@’%’. The user is authorized as the accounting@localhost user. The user is authorized as the rsmith@localhost user. The user failed to define a username and the connecting username defaulted to ‘’@’%’.
The mysqld instance has the connection control plugin enabled with these settings:
connection_control_min_connection_delay=1000
connection_control_max_connection_delay=2000
The minimum and maximum delays need to be increased to 3000 and 5000, respectively.
A command is executed:
mysql> SET GLOBAL connection_control_min_connection_delay=3000;
What is the result? The minimum value increases to 3000 and the maximum value increases to 4000. Only the minimum connection value is increased to 3000. The minimum connection value is changed to 2000. An error is returned.
Which two MySQL Shell commands are excluded from the InnoDB Cluster creation
procedure? (Choose two.) dba.configureInstance() cluster.setPrimaryInstance() dba.configureLocalInstance() cluster.forceQuorumUsingPartitionOf() cluster.addInstance() dba.createCluster() dba.checkInstanceConfiguration().
Examine this command, which executes successfully on InnoDB Cluster:
dba.dropMetadataSchema()
Which two statements are true? (Choose two.) Group Replication will be dissolved and all metadata purged. Group Replication is still operational, but InnoDB Cluster must be reimported under MySQL Shell. The mysql_innodb_cluster_metadata schema is dropped from the instance where the connection was established. The command drops the mysql_innodb_cluster_metadata schema and re-creates it. The mysql_innodb_cluster_metadata schema is dropped from all reachable members of the cluster. Connections driven by MySQL Router are not affected by the command.
What is the correct syntax for using transparent data encryption with an existing InnoDB
table? ALTER TABLE t1 ADD ENCRYPTED_TABLESPACE = 'Y'; ALTER TABLE t1 ENCRYPTION='Y'; ALTER TABLE t1 WITH ENCRYPTION USING MASTER KEY; ALTER TABLE t1 SET TDE = 'ON';.
Which two are characteristics of snapshot-based backups? (Choose two.) Snapshot-based backups greatly reduce time during which the database and applications are unavailable. There is no need for InnoDB tables to perform its own recovery when restoring from the snapshot backup. The frozen file system can be cloned to another virtual machine immediately into active service. A separate physical copy must be made before releasing the snapshot backup. Snapshot backups can be used only in virtual machines.
Examine Joe's account:
CREATE USER 'joe'@'%' IDENTIFIED BY '*secret*'
GRANT ALL PRIVILEGES ON *.* TO 'joe'@'%'
All existing connections for joe are killed.
Which two commands will stop joe establishing access to the MySQL instance? (Choose
two.) ALTER USER 'joe'@'%' ACCOUNT LOCK ALTER USER 'joe'@'%' SET password='*invalid*' REVOKE ALL PRIVILEGES ON *.* FROM 'joe'@'%' ALTER USER 'joe'@'%' PASSWORD HISTORY 0 ALTER USER 'joe'@'%' IDENTIFIED BY '*invalid*' PASSWORD EXPIRE REVOKE USAGE ON *.* FROM 'joe'@'%'.
Examine this command, which executes successfully:
mysqlpump -–user=root --password > full_backup.sql
Which two databases will be excluded from this dump? (Choose two.) information_schema world employee sys mysql.
A colleague complains about slow response time on your website.
Examine this query and output:
(imagen)
What is the most likely cause for the high number of lock waits? You use the MyISAM storage engine for most common tables. You use the InnoDB storage engine and statements wait while data is inserted. The Innodb Buffer pool is full. Your table accesses wait for the operating system level flush.
Your MySQL server was upgraded from an earlier major version.
The sales database contains three tables, one of which is the transactions table, which has
4 million rows.
You are running low on disk space on the datadir partition and begin to investigate.
Examine these commands and output:
(imagen)
Which two statements are true? (Choose two.) Executing SET GLOBAL innodb_row_format=COMPRESSED and then ALTER TABLE transactions will free up disk space. Executing ALTER TABLE transactions will enable you to free up disk space. Truncating the sales and leads table will free up disk space. Truncating the transactions table will free up the most disk space. The transactions table was created with innodb_file_per_table=OFF.
Which two tools are available to monitor the global status of InnoDB locking? (Choose two.) SHOW ENGINE INNODB STATUS; INFORMATION_SCHEMA.INNODB_METRICS SHOW TABLE STATUS; INFORMATION_SCHEMA.STATISTICS INFORMATION_SCHEMA.INNODB_TABLESTATS SHOW STATUS;.
Which three requirements must be enabled for group replication? (Choose three.) slave updates logging semi-sync replication plugin primary key or primary key equivalent on every table binary log checksum binary log ROW format binary log MIXED format replication filters.
Which three settings control global buffers shared by all threads on a MySQL server?
(Choose three.) sort_buffer_size key_buffer_size table_open_cache read_buffer_size innodb_buffer_pool_size tmp_table_size.
Which two methods allow a DBA to reset a user's password? (Choose two.) ALTER USER statement mysql_secure_installation utility SET PASSWORD statement mysqladmin client program GRANT statement.
Your MySQL server is running on the Microsoft Windows platform.
Which three local connection protocols are available to you? (Choose three.) named pipes shared memory SOCKET X Protocol UDP TCP/IP.
Examine this query and output:
(imagen)
Which two statements are true? (Choose two.) The country table is accessed as the first table, and then joined to the city table. It takes more than 8 milliseconds to sort the rows. The optimizer estimates that 51 rows in the country table have Continent = ‘Asia’. 35 rows from the city table are included in the result. The query returns exactly 125 rows.
Examine this statement and output:
(imagen)
Which two SQL statements can jsmith execute? (Choose two.) UPDATE world.country SET Name='all'; UPDATE world.country SET Name='one' LIMIT 1; UPDATE world.country SET Name='new' WHERE Name='old'; UPDATE world.country SET Name=CONCAT('New ',Name); UPDATE world.country SET Name='first' ORDER BY Name LIMIT.
Which two statements are true about using backups of the binary log? (Choose two.) Multiple binary logs can be used to restore data. Multiple binary logs can be applied in parallel for faster data restoration. Binary logs are relatively small, and therefore, excellent for long-term storage and disaster recovery. Binary logs can always be used to unapply unwanted schema changes. They allow for point-in-time recovery of the data.
Examine this command and output:
(imagen)
Which two statements are true? (Choose two.) The lock is at the metadata object level. The lock is a shared lock. The lock is an intentional lock. The lock is at the table object level. The lock is a row-level lock. The lock is an exclusive lock.
You are using mysqlcheck for server maintenance.
Which two statements are true? (Choose two.) The mysqlcheck --check --all-databases command takes table write locks while performing a series of checks. The mysqlcheck --optimize --all-databases command reclaims free space from table files. The mysqlcheck --repair --all-databases command can repair an InnoDB corrupted table. The mysqlcheck command can be renamed mysqlrepair so that it repairs tables by default. The mysqlcheck --analyze --all-databases command performs a series of checks to spot eventual table corruptions.
Which two statements are true about MySQL Installer? (Choose two.) It installs most Oracle MySQL products. It performs product upgrades. It provides only GUI-driven, interactive installations. Manual download of separate product packages is required before installing them through MySQL Installer. It provides a uniform installation wizard across multiple platforms.
Which two are true about differences between logical and physical upgrades of MySQL
databases? (Choose two.) Post-upgrade table storage requirements after logical upgrades are usually smaller than that after physical upgrades. Physical upgrades are performed for current instances on bare metal deployments, whereas logical upgrades are used for virtual machines or containerized instances. Logical upgrades are much faster because they do not require restarting the mysqld process. Post-upgrade table storage requirements after physical upgrades are usually smaller than that after logical upgrades. Physical upgrades are much faster because they do not require restarting the mysqld process. Physical upgrades leave data in place, whereas logical upgrades require data to be restored from mysqldump-type backups taken before the upgrades.
Examine this statement:
mysql> DROP ROLE r_role1, r_role2;
Which two are true? (Choose two.) It fails if any of the roles is specified in the mandatory_roles variable. You must revoke r_role1 and r_role2 from all users and other roles before dropping the roles. Existing connections can continue to use the roles’ privileges until they reconnect. You must revoke all privileges from r_role1 and r_role2 before dropping the roles. It fails if you do not have the ADMIN OPTION of the roles r_role1 and r_role2. It fails if at least one of the roles does not exist.
Examine this statement and output:
(imagen)
You must try to reduce query execution time.
Which two queries should you focus on? (Choose two.) QN = 3 QN = 5 QN = 1 QN = 4 QN = 2.
Which two are contained in the InnoDB system tablespace (ibdata1) by default? (Chose
two.) table data primary indexes user privileges InnoDB Data Dictionary change buffer doublewrite buffer.
Which two are use cases of MySQL asynchronous replication? (Choose two.) You can scale writes by creating a replicated mesh. It guarantees near real-time replication between a master and a slave. You can scale reads by adding multiple slaves. MySQL Enterprise Backup will automatically back up from an available slave. It allows backup to be done on the slave without impacting the master.
Which statement is true about InnoDB persistent index statistics? Updating index statistics is an I/O expensive operation. Index statistics are calculated from pages buffered in the buffer pool for tables with InnoDB storage engine. Setting innodb_stats_auto_recalc=ON causes statistics to be updated automatically when a new index is created. Execution plans based on transient index statistics improve precision when innodb_stats_persistent_sample_pages is increased. Increasing innodb_stats_persistent_sample_pages determines higher pages scanning speed, at the cost of increased memory usage.
Examine this snippet from the binary log file named binlog.000036:
(imagen)
The rental table was accidentally dropped, and you must recover the table.
You have restored the last backup, which corresponds to the start of the binlog.000036
binary log.
Which command will complete the recovery? mysqlbinlog --stop-position=500324 binlog.000036 | mysql mysqlbinlog --stop-datetime='2019-ll-20 14:55:18' binlog.000036 | mysql mysqlbinlog --stop-position=5004S3 binlog.000036 | mysql mysqlbinlog --stop-datetime='2019-ll-20 14:55:16' binlog.000036 | mysql.
Which three are types of InnoDB tablespaces? (Choose three.) redo tablespaces encryption tablespaces schema tablespaces data tablespaces undo tablespaces temporary table tablespaces.
Examine this command, which executes successfully:
cluster.addInstance(‘<user>@<host>:<port>’, {recoveryMethod: ‘clone’})
Which three statements are true? (Choose three.)
The account used to perform this recovery needs the BACKUP_ADMIN privilege. A target instance must exist, then it will be provisioned with data from an instance already in the cluster and joined to the cluster. InnoDB tablespaces outside the datadir are able to be cloned. It is always slower than {recoveryMethod: ‘incremental’}. A new instance is installed, initialized, and provisioned with data from an instance already in the cluster and joined to the cluster. InnoDB redo logs must not rotate for the duration of the execution; otherwise, the recovery will fail.
You plan to take daily full backups, which include the ndbinfo and sys (internal) databases.
Which command will back up the databases in parallel? mysqldump --single-transaction > full-backup-$(date +%Y%m%d).sql mysqlpump --include-databases=% > full-backup-$(date +%Y%m$d).sql mysqlpump --all-databases > full-backup-$(date +%Y%m%d).sql mysqldump --all-databases > full_backup-$(date +%Y%m%d).sql.
You wish to store the username and password for a client connection to MySQL server in a
file on a local file system.
Which is the best way to encrypt the file? Use the AES_ENCRYPT() MySQL function on the option file. Use mysql_secure_installation to encrypt stored login credentials. Use a text editor to create a new defaults file and encrypt it from Linux prompt. Use mysql_config_editor to create an encrypted file.
Examine this MySQL Shell command:
dba.rebootClusterFromCompleteOutage()
Which two statements are true? (Choose two.) It reconfigures InnoDB Cluster if the cluster was stopped. It performs InnoDB Cluster instances rolling restart. It only starts all InnoDB Cluster instances. It is not mandatory that all instances are running and reachable before running the command. It stops and restarts all InnoDB Cluster instances and initializes the metadata. It only stops and restarts all InnoDB Cluster instances. It picks the minimum number of instances necessary to rebuild the quorum and reconfigures InnoDB Cluster.
User account baduser@hostname on your MySQL instance has been compromised.
Which two commands stop any new connections using the compromised account?
(Choose two.) ALTER USER baduser@hostname PASSWORD DISABLED; ALTER USER baduser@hostname MAX_USER_CONNECTIONS 0; ALTER USER baduser@hostname ACCOUNT LOCK; ALTER USER baduser@hostname IDENTIFIED WITH mysql_no_login; ALTER USER baduser@hostname DEFAULT ROLE NONE;.
Which two are features of MySQL Enterprise Firewall? (Choose two.) recording incoming SQL statement to facilitate the creation of a whitelist of permitted
commands blocking of potential threats by configuring pre-approved whitelists modifying SQL statement dynamically with substitutions automatic locking of user accounts who break your firewall provides stateless firewall access to TCP/3306.
A clean shutdown was performed with innodb_fast_shutdown=0.
While you were manipulating files, all files were accidentally deleted from the top-level data
directory.
Which two files must be restored from backup to allow the DB to restart cleanly? (Choose
two.) ibtmp1 undo_001 ib_buffer_pool ibdata1 mysql.ibd ib_logfile0.
Which statement is true about InnoDB persistent index statistics? Updating index statistics is an I/O expensive operation. Index statistics are calculated from pages buffered in the buffer pool for tables with InnoDB storage engine. Setting innodb_stats_auto_recalc=ON causes statistics to be updated automatically when a new index is created. Execution plans based on transient index statistics improve precision when innodb_stats_persistent_sample_pages is increased. Increasing innodb_stats_persistent_sample_pages determines higher pages scanning speed, at the cost of increased memory usage. Tables are scanned and index statistics recalculated when an instance is restarted.
You are using an existing server with a new configuration. MySQL Server fails to start.
Examine this snapshot of the error log:
(imagen)
Which action would allow the server to start? Remove ib_logfile0 and ib_logfile1 files from the file system. Execute mysqladmin flush-logs. First run mysqld --initialize to refresh the Size of ib_logfile. Create a new ib_logfile0 file of size 26214400.
Which condition is true about the use of the hash join algorithm? No index can be used for the join. The query must access no more than two tables. The smallest of the tables in the join must fit in memory as set by join_buffer_size. At least one of the tables in the join must have a hash index.
You have just installed MySQL on Oracle Linux and adjusted your /etc/my.cnf parameters
to suit your installation.
Examine the output:
(imagen)
What statement is true about the start attempt? MySQL server was not started due to a problem while executing process 2732. MySQL server continued to start up even though another process existed. systemd found the mysqld service disabled and failed to start it. systemd waited for 30 seconds before timing out and start up failed. systemd attempted to start mysqld, found another systemd mysqld process running, and shut it down.
Your MySQL installation is running low on space due to binary logs. You need to reduce
your log space usage urgently.
Which two sets of actions when completed will accomplish this? (Choose two.) Use SET GLOBAL binlog_expire_logs_seconds=<value> and restart the server. Set binlog_expire_logs_seconds in my.cnf. Set binlog_expire_logs_seconds = 0 in my.cnf and restart the server. Use SET PERSIST binlog_expire_logs_seconds=<value>. Use PURGE BINARY LOGS to <binlog_name>. Use SET GLOBAL binlog_expire_logs_seconds=<value> and run the FLUSH BINARY LOGS command.
Which two queries are examples of successful SQL injection attacks? (Choose two.) SELECT user,passwd FROM members WHERE user = ‘?’;INSERT INTO members(‘user’,’passwd’) VALUES
(‘bob@example.com’,‘secret’);--‘; SELECT id, name FROM user WHERE user.id=(SELECT members.id FROM members); SELECT id, name FROM user WHERE id=23 OR id=32 OR 1=1; SELECT id, name FROM user WHERE id=23 OR id=32 AND 1=1; SELECT email,passwd FROM members WHERE email = ‘INSERT INTO members(‘email’,’passwd’) VALUES (‘bob@example.com’, ‘secret’);--‘; SELECT user, phone FROM customers WHERE name = ‘\; DROP TABLE users; --‘;.
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?
(Choose two.) Users could overwrite configuration files. Data files could be deleted. SQL injections could be used to insert bad data into the database. MySQL binaries could be damaged, deleted, or altered. Extra startup time would be required for the MySQL server to reset the privileges.
A MySQL server is monitored using MySQL Enterprise Monitor’s agentless installation.
Which three features are available with this installation method? (Choose three.) MySQL Replication monitoring network-related information and network characteristics MySQL Query Analysis data CPU utilization security-related advisor warnings operating system memory utilization disk usage and disk characteristics including disk advisors warnings.
Examine this partial output for InnoDB Cluster status:
(imagen)
Which statement explains the state of the instance deployed on host2? It can be recovered from a donor instance on host3 by cloning using the command cluster.rejoinInstance ('<user>@host3:3377') It can rejoin the cluster by using the command cluster.addInstance('<user>@host3:3377') It has been removed from the cluster by using the command STOP GROUP_REPLICATION; It can rejoin the cluster by using the command dba.rebootClusterFromCompleteOutage() It has been expelled from the cluster because of a transaction error.
MySQL programs look for option files in standard locations.
Which method will show the option files and the order in which they are read? shell> mysqladmin --debug shell> mysql --print-defaults shell> mysqld --help --verbose mysql> SHOW GLOBAL VARIABLES;.
Examine this command, which executes successfully:
$ mysqlrouter --bootstrap user@hostname:port --directory=directory_path
Which activity is performed? MySQL Router is configured based on the information in files in directory_path. MySQL Router configures itself based on the information retrieved from the InnoDB cluster metadata server. MySQL Router is restarted. MySQL Router configures all the cluster nodes based on the information retrieved from the InnoDB cluster metadata server.
You made some table definition changes to a schema in your MySQL Server.
Which two statements reflect how MySQL Server handles the table definition changes?
(Choose two.) MySQL writes SDI to the binary log for distributed backups. MySQL keeps InnoDB metadata changes in .sdi files in datadir. The metadata is serialized in JSON format in Serialized Dictionary Information (SDI). MySQL Server stores a copy of the serialized data in the InnoDB user tablespace. MySQL implicitly executes FLUSH TABLES and stores a snapshot backup of the metadata.
Four nodes are configured to use circular replication.
Examine these configuration parameters for each node:
(imagen)
Which statement is true? Each slave thread is responsible for updating a specific database. Cross-database constraints can cause database inconsistency. Increasing slave_parallel_workers will improve high availability. Setting slave_preserve_commit_order to on will improve data consistency. Setting slave_parallel_type=DATABASE won't work for circular replication; it should be set to LOGICAL_CLOCK. Setting transaction_allow_batching to on will improve data consistency.
Examine this output:
(imagen)
Which change should optimize the number of buffer pool instances for this workload? Increase the number of buffer pool instances to 16. Increase the number of buffer pool instances to 32. Decrease the number of buffer pool instances to 1. Increase the number of buffer pool instances to 12. Decrease the number of buffer pool instances to 4.
You have an installation of MySQL 8 on Oracle Linux.
Consider the outputs:
(imagen)
Which statement is true about disk temporary tables for this installation? Only internal temporary tables from the optimizer will be created in tmpdir. Temporary tables will use the InnoDB temporary tablespace located in datadir. Temporary tables are created in tmpdir only if configured to use MyISAM. Temporary tables are created in tmpdir only after they reach tmp_tabie_size. Temporary tables will use the InnoDB temporary tablespace located in /tmp.
Which three actions are effective in capacity planning? (Choose three.) buying more RAM monitoring OS resources for patterns adding circular replication nodes for increased DML capability buying more CPU buying more disk basing expected growth on an average of the last 3 years consulting the application team about any future projects and use upgrading to the latest application version.
Examine this SQL statement:
(imagen)
Which set of privileges will allow Tom to execute this SQL statement? GRANT ALL PRIVILEGES ON ‘world’.‘city’ TO ‘tom’@’%’;
GRANT SELECT (‘code’) ON ‘world’.‘country’ TO ‘tom’@’%’; GRANT UPDATE ON ‘world’.* TO ‘tom’@’%’;
GRANT ALL PRIVILEGES ON ‘world’.‘country’ TO ‘tom’@’%’; GRANT UPDATE ON ‘world’.‘city’ TO ‘tom’@’%’; GRANT SELECT ON ‘world’.* TO
‘tom’@’%’; GRANT UPDATE ON ‘world’.‘city’ TO ‘tom’@’%’; GRANT SELECT ON ‘world’.‘country’
TO ‘tom’@’%’;.
Examine this command and output:
(imagen)
Which statement is true? Firewall_cached_entries is the number of statements found in the query cache for users in DETECTING mode. Firewall_access_denied is the number of connection attempts from prohibited hosts that are denied. Firewall_access_suspicious is the number of statements logged as suspicious for users in DETECTING mode. Firewall_access_granted is the number of connections granted from whitelisted hosts.
Examine this MySQL client command to connect to a remote database:
mysql -h remote.example.org -u root -p --protocol=TCP --ssl-mode=
Which two --ssl-mode values will ensure that an X.509-compliant certificate will be used to
establish the
SSL/TLS connection to MySQL? REQUIRED VERIFY_CA VERIFY_IDENTITY PREFERRED DISABLED.
Your MySQL environment has asynchronous position based-replication with one master
and one slave.
The slave instance had a disk I/O problem, so it was stopped.
You determined that the slave relay log files were corrupted and unusable, but no other
files are damaged.
You restart MySQL Server.
How can replication be restored? The slave relay logs should be deleted; then execute START SLAVE; The relay logs from the master should be used to replace the corrupted relay logs. The slave relay logs should be deleted; execute CHANGE MASTER to adjust the replication relay log file name, then issue start SLAVE; The slave needs to be restored from backup.
Examine this query and its output:
(imagen)
Which two statements are true? (Choose two.) The root user had the largest number of modified rows for a SELECT statement. User bob had the largest total time waiting for locks. The root user had the largest single wait time. The app user had the highest total number of rows read from storage engines. User bob had a significantly higher ratio of SELECT + INSERT statements to QUIT than both app and root users.
Examine these statements, which execute successfully:
CREATE ROLE r_world_rd;
GRANT SELECT ON world.* TO r_world_rd;
CREATE USER john IDENTIFIED BY ‘P@ssw0rd’;
GRANT r_world_rd TO john;
Examine these statements issued by user John:
(imagen)
What is the reason for the error? The statement was blocked by MySQL Firewall. John has not activated the role. John needs to reconnect to the database. The DBA needs to execute FLUSH PRIVILEGES.
Examine these statements, which execute successfully:
TRUNCATE test; BEGIN;
INSERT INTO test(id, name) VALUES(1, “Hello”); ROLLBACK;
SELECT id FROM test;
Which three storage engines would return a nonempty recordset for the test table when
executing the statements? (Choose three.) NDB ARCHIVE InnopB BLACKHOLE MEMORY MyISAM.
A valid raw backup of the shop.customers MyISAM table was taken.
You must restore the table. You begin with these steps:
1.Confirm that secure_file_priv=’/var/tmp’
2.mysql> DROP TABLE shop.customers;
3.shell> cp /backup/customers.MY* /var/lib/mysql/shop/
Which two actions are required to complete the restore? (Choose two.) shell> cp /backup/customers.sdi /var/tmp shell> cp /backup/customers.sdi /var/lib/mysql/shop/ mysql> SOURCE ‘/var/tmp/customers.sdi’ mysql> IMPORT TABLE FROM /var/tmp/customers.sdi shell> cp /backup/customers.frm /var/lib/mysql/shop/ mysql> IMPORT TABLE FROM /var/lib/mysql/shop/customers.sdi mysql> ALTER TABLE shop.customers IMPORT TABLESPACE mysql> ALTER TABLE shop.customers DISCARD TABLESPACE.
MySQL is installed on a Linux server with this configuration:
(imagen)
Which method sets the default authentication to SHA-256 hashing for authenticating user
account passwords? Set validate-user-plugins=caching_sha2_password in the configuration file. Define CREATE USER ''@'%' IDENTIFIED WITH sha256_password in the MySQL instance. Add default_authentication_plugin=mysql_native_password in the configuration file. Add default_authentication_plugin=sha256_password in the configuration file.
Which statement is true about MySQL Enterprise Transparent Data Encryption (TDE)? MySQL TDE uses an appropriate keyring plugin to store the keys in a centralized location. Both MyISAM and InnoDB tables can be encrypted by setting the keyring_engine = ALL variable in the MySQL configuration file. Lost tablespace encryption keys can be regenerated only if the master database key is known or present in the Key Vault specification. TDE can encrypt InnoDB and MyISAM tables only when the tables are stored in the SYSTEM tablespace.
Which two statements are true about the mysql_config_editor program? (Choose two.) It manages the configuration of the MySQL Firewall feature. It manages the configuration of client programs. It can move datadir to a new location. It manages the configuration of user privileges for accessing the server. It will use [client] options by default unless you provide --login-path. It can be used to create and edit SSL certificates and log locations. It provides an interface to change my.cnf files.
Examine this list of MySQL data directory binary logs:
binlog.000001 binlog.000002
.....
binlog.000289
binlog.000300
binlog.000301
binlog.index
Now examine this command, which executes successfully:
mysqldump --delete-master-logs --all-databases > /backup/db_backup.sql
Which two are true? (Choose two.) All databases are backed up to the output file. All non-active binary logs are removed from the master. All binary logs are deleted from the master. All binary logs are backed up and then deleted. All databases, excluding master metadata, are backed up to the output file. All details regarding deleted logs and master metadata are captured in the output file.
You are asked to review possible options for a new MySQL instance. It will be a large, busy
reporting data warehousing instance.
[mysql]
innodb_data_file_path=
Which two configurations would satisfy long-term storage demands? (Choose two.) ibdatal:12M;ibdata2:12M;ibdata3:12M ibdatal:12M:autoextend;ibdata2:12M:autoextend ibdatal:12M:autoextend ibdatal:12M;ibdata2:12M:autoextend ibdatal:12M ibdatal:12M;/tmp/ibdata2:12M:autoextend.
Which three are characteristics of a newly created role? (Choose three.) It can be dropped using the DROP ROLE statement. It is stored in the mysql.role table. It is created as a locked account. It can be renamed using the RENAME ROLE statement. It can be granted to user accounts. It can be protected with a password.
Examine this query:
(imagen)
What information does this query provide? total memory used across all connections associated with the user on connection number 10 total memory used by the first 10 connections total memory used by thread number 10 total memory used across all connections associated with the user on thread number 10 total memory used by connection number 10 total memory used by the first 10 threads.
Your my.cnf file contains these settings:
(imagen)
You want to log queries that looked at a minimum of 5000 records and either took longer
than 5 seconds to run or did not use indexes.
Which contains all the settings that you need to add to or modify the slow log
configuration? min_examined_row_limit=5000 long_query_time=5
log_throttle_queries_not_using_indexes=5 log_throttle_queries_not_using_indexes=5
min_examined_ row_limit=5000 long_query_time=5 long_query_time=5
min_examined_row_limit=5000 log_throttle_queries_not_using_indexes=5 long_query_time=5
log_throttle_queries_not_using_indexes=5
min_examined_row_limit=5000.
Which two MySQL Server accounts are locked by default? (Choose two.) any user set as DEFINER for stored programs any internal system accounts any new ROLE accounts any user created without a password any user created with a username, but missing the host name.
Which statement is true about cold backups? They are backups taken from snapshots of a running database. They are backups taken from OS copy commands. They are good to use if only data structures must be backed up but not log files. They are good to use when many users are online accessing the database.
You want to log only the changes made to the database objects and data on the MySQL
system.
Which log will do this by default? general query log audit log slow query log binary log error log.
You are backing up raw InnoDB files by using mysqlbackup.
Which two groups of files will be backed up during a full backup? (Choose two.) ibbackup files *.CSM files *.sdi files *.ibd files * files.
Which two statements are true about using MySQL Enterprise Monitor Query Analyzer?
(Choose two.) The single query QRTi pie chart in the Query Analyzer view is based on the average execution of all statements. It is possible to retrieve a normalized statement, but never the exact statement that was executed. It is possible to configure the Query Analysis built-in advisor to get notified about slow query execution. It is possible to list and analyze statements in an arbitrary graph range selection from timeseries graphs. It is possible to import data into the Query Analyzer from heterogeneous sources, such as CSV.
Examine this command, which executes successfully:
shell> mysqldump --master-data=2 --single-transaction --result-file=dump.sql mydb
Which two statements are true? (Choose two.) It executes flush tables with read lock. It enforces consistent backups for all storage engines. The backup created is a consistent data dump. This option uses the READ COMMITTED transaction isolation mode. It is a cold backup.
Which two statements are true about MySQL server multi-source replication? (Choose
two.) It is not compatible with auto-positioning. It needs to be re-instanced after a crash to maintain consistency. It uses only time-based replication conflict resolution. It relies on relay_log_recovery for resilient operations. It does not attempt to detect or resolve replication conflicts. It must use GTID replication.
Examine this parameter setting:
audit_log=FORCE_LOG_PERMANENT
What effect does this have on auditing? It will force the load of the audit plugin even in case of errors at server start. It causes the audit log to be created if it does not exist. It prevents the audit plugin from being removed from the running server. It prevents the audit log from being removed or rotated.
You have a MySQL client installed on your Linux workstation with a default installation. You
have your admin login credentials to connect to a MySQL server running Microsoft
Windows on remote host 192.0.2.1:3306. You wish to connect directly to the world
database.
Which four options need to be specified to complete this task with a single command?
(Choose four.) --shared-memory-base-name=world --protocol=UDP --protocol=pipe --password --user=admin --host=192.0.2.1 --socket=/tmp/mysql.sock --port=3306 --database=world.
Your MySQL instance is capturing a huge amount of financial transactions every day in the
finance database.
Company policy is to create a backup every day.
The main tables being updated are prefixed with transactions-.
These tables are archived into tables that are prefixed with archives- each month.
mysqlbackup --optimistic-busy-tables="^finance\.transactions-.*" backup
Which optimization process best describes what happens with the redo logs? The redo logs are backed up first, then the transaction and archive tables. The redo logs are backed up only if there are changes showing for the transactions tables. The redo logs are not backed up at all. The archive tables are backed up first, then the transaction tables and redo logs. The transaction tables are backed up first, then the archive tables and redo logs.
Which four are types of information stored in the MySQL data dictionary? (Choose four.) performance metrics table definitions access control lists view definitions server runtime configuration server configuration rollback stored procedure definitions InnoDB buffer pool LRU management data.
Consider this shell output and executed commands:
[root@oel7 ~]# ps aux | grep mysqld
mysql 2076 3.5 24.6 1386852 372572 ? Ssl 12:01 0:01 /usr/sbin/mysqid
[root@oel7 ~]# kill -15 2076
Which statement is true about MySQL server shutdown? kill -15 should be avoided. Use other methods such as mysqladmin shutdown or systemctl stop mysqld. kill -15 and kill -9 are effectively the same forced shutdown that risk committed transactions not written to disk. kill -15 carries out a normal shutdown process, such as mysqladmin shutdown. mysqld_safe prohibits commands that would harm the operation of the server. An error would be returned by the kill command.
t is a non-empty InnoDB table.
Examine these statements, which are executed in one session:
BEGIN;
SELECT * FROM t FOR UPDATE;
Which is true? If OPTIMIZE TABLE; is invoked, it will create a table lock on t and force a transaction rollback. If OPTIMIZE LOCAL TABLE t; is invoked from another session, it executes normally and returns the status. mysqlcheck --analyze --all-databases will execute normally on all tables and return a report. If ANALYZE TABLE; is invoked from the same session, it hangs until the transaction is committed or rolled back.
You have an InnoDB Cluster configured with three servers.
Examine this command, which executes successfully:
mysqldump -uroot -p -d mydatabase > mydatabase_backup.sql
Due to data loss, the cluster is initialized and a restore is attempted resulting in this error:
ERROR 13176 (HY000) at line 23: Cannot update GTID_PURGED with the Group
Replication plugin running
Which two actions, either one of which, can fix this error and allow a successful restore of
the cluster? (Choose two.) Stop all instances except the primary read/write master instance and run the restore. Remove the @@GLOBAL.gtid_purged statement from the dump file. Create the backup by using the --set-gtid-purged=OFF option. Remove the group replication plugin from each instance before restoring. Remove the @@GLOBAL.gtid_executed statement from the dump file. Restore using the --set-gtid-purged=OFF option.
You are having performance issues with MySQL instances. Those servers are monitored
with MySQL Enterprise Monitor.
Using Query Analyzer, where do you begin to look for problem queries? Sort the “Exec" column and check for SQL queries with low Query Response Time index (QRTi) values. Look for queries with low total latency times in the Latency section in the times series graph. Look for queries with big prolonged spikes in row activity/access graph in the times series graph. Sort the “Exec" column and check for SQL queries with high Query Response Time index (QRTi) values.
Which two statements are true about raw binary backups? (Choose two.) They are converted to a highly compressible binary format. The data format is identical to how MySQL stores the data on disk. They are required to obtain FIPS security compliance. The resulting files are easily human readable. They are faster than logical backups because the process is a simple file or file system copy.
You are considering using file-system snapshots to back up MySQL.
Which three statements are true? (Choose three.) They take roughly twice as long as logical backups. They allow direct copying of table rows with operating system copy commands. They work best for transaction storage engines that can perform their own recovery when restored. The backup window is almost zero from the perspective of the application. They do not back up views, stored procedures, or configuration files. There is a slight performance cost while the snapshot is active. They do not use additional disk space.
You must configure the MySQL command-line client to provide the highest level of trust
and security when connecting to a remote MySQL Server.
Which value of --ssl-mode will do this? PREFERRED VERIFY_CA REQUIRED VERIFY_IDENTITY.
Where is the default data directory located after installing MySQL using RPM on Oracle
Linux 7? /usr/mysql /usr/bin /etc/my.cnf /var/lib/mysql /usr.
Which two statements are true about InnoDB data-at-rest encryption? (Choose two.) It supports only non-blob datatypes. It does not support the transportable tablespaces feature. It supports all indexes transparently. It decrypts data for use in memory. It enforces encryption from disk to memory and over network transmission.
Which two commands will display indexes on the parts table in the manufacturing schema?
(Choose two.) DESCRIBE manufacturing.parts; SELECT * FROM information_schema.statistics WHERE table_schema=’manufacturing’ AND TABLE_NAME=’parts’; SHOW INDEXES FROM manufacturing.parts; SELECT * FROM information_schema.COLUMN_STATISTICS; EXPLAIN SELECT INDEXES FROM manufacturing.parts;.
What does the binlog dump thread do? It monitors and schedules the rotation/deletion of the binary logs. It reads the relay log and executes the events contained in them. It acquires a lock on the binary log for reading each event to be sent to the slave. It connects to the master and asks it to send updates recorded in its binary logs.
Examine this SQL statement:
mysql> GRANT r_read@localhost TO mark WITH ADMIN OPTION;
Which two are true? (Choose two.) Mark can grant the privileges assigned to the r_read@localhost role to another user. ADMIN OPTION causes the role to be activated by default. Mark can grant the r_read@localhost role to another user. Mark can revoke the r_read@localhost role from another role. ADMIN OPTION allows Mark to drop the role. Mark must connect from localhost to activate the r_read@localhost role.
Which four connection methods can MySQL clients specify with the --protocol option when
connecting to a MySQL server? (Choose four.) TCP SOCKET PIPE DIRECT IPv6 FILE IPv4 MEMORY.
Which command enables rule-based MySQL Auditing capabilities? shell> mysql < audit_log_filter_linux_install.sql shell> mysqld --initialize --log-raw=audit.log mysql> INSTALL PLUGIN audit_log; mysql> INSTALL COMPONENT audit_log;.
A user wants to connect without entering his or her username and password on the Linux
command prompt.
Which three locations can be used to store the user’s mysql credentials to satisfy this
requirement? (Choose three.) $HOME/.my.cnf file $MYSQL_HOME/my.cnf file DATADIR/mysqld-auto.cnf file $HOME/.mylogin.cnf file $HOME/.mysql/auth/login file /etc/my.cnf file $HOME/.mysqlrc file.
Which two statements are true about the binary log encryption feature? (Choose two.) It encrypts any connecting slaves connection thread. It can be set at run time. It requires a keyring plugin. When enabled it encrypts existing binary logs. It can be activated per session.
You must run multiple instances of MySQL Server on a single host.
Which three methods are supported? (Choose three.) Run MySQL Server docker containers. Use systemd with different settings for each instance. Use system tools to lock each instance to its own CPU. Start mysqld or mysqld_safe using different option files for each instance. Run mysqld with --datadir defined for each instance. Use resource groups to lock different instances on separate CPUs.
You have configured GTID-based asynchronous replication with one master and one slave.
A user accidentally updated some data on the slave.
To fix this, you stopped replication and successfully reverted the accidental changes.
Examine the current GTID information:
(imagen)
You must fix GTID sets on the slave to avoid replicating unwanted transactions in case of
failover. Which set of actions would allow the slave to continue replicating without
erroneous transactions? RESET MASTER;
SET GLOBAL gtid_purged=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10167; SET GLOBAL gtid_purged=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-
2312,bbbbbbbb- bbbb-bbbb-bbbb-bbbbbbbbbbbb:1-9;
SET GLOBAL gtid_executed=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10167; RESET SLAVE;
SET GLOBAL gtid_purged=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-3820;
SET GLOBAL gtid_executed=aaaaaaaa-aaaa-aaaa-aaaa—aaaaaaaaaaaa:1-10300; RESET MASTER;
SET GLOBAL gtid_purged-aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-2312;
SET GLOBAL gtid_executed=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10167; RESET SLAVE;
SET GLOBAL gtid_purged=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10167;.
Which two are true about binary logs used in asynchronous replication? (Choose two.) They are pushed from the master to the slave. They contain events that describe database changes on the master. They contain events that describe all queries run on the master. They contain events that describe only administrative commands run on the master. They are pulled from the master to the slave.
You have a MySQL system with 500 GB of data that needs frequent backups.
You use a mix of MyISAM and InnoDB storage engines for your data. Examine your
backup requirement:
The MySQL system being backed up can never be unavailable or locked to the
client applications.
The recovery from the backup must work on any system.
Only 1 hour of data can be lost on recovery of the backup.
Which option fulfills all backup requirements? Take a physical backup of the MySQL system. Use the Clone Plugin to copy the data to another MySQL system. Take a logical backup of the MySQL system. Take your backup from a slave of the MySQL system.
Examine these entries from the general query log:
(imagen)
All UPDATE statements reference existing rows.
Which describes the outcome of the sequence of statements? Connection 24 experiences a lock wait timeout. Connection 25 experiences a lock wait timeout. A deadlock occurs immediately. All statements execute without error. A deadlock occurs after innodb_lock_wait_timeout seconds.
You recently upgraded your MySQL installation to MySQL 8.0.
Examine this client error:
Which option will allow this client to connect to MySQL Server? [mysqld] default_authentication_plugin=sha256_password ALTER USER user - IDENTIFIED WITH mysql_native_password BY 'password'; [mysqld] default_authentication_plugin=caching_sha2_password ALTER USER user - IDENTIFIED WITH caching_sha2_password BY 'password'; ALTER USER user - IDENTIFIED WITH sha256_password - BY 'password'; [mysqld] default_authentication_plugin=mysql_native_password.
Which two statements are true about the mysqld-auto.cnf file? (Choose two.) This file is for storing MySQL Server configuration options in ISON format. This file is for logging purposes only and is never processed. It is read and processed at the beginning of startup configuration. It is read and processed at the end of startup configuration. It is always updated with changes to system variables. This file is for storing MySQL server_uuid values only.
Examine this command, which executes successfully:
(imagen)
Which statement is true? Only files for MySQL or Its built-in storage engines are backed Only non-encrypted files are backed up. The backup includes only data files and their metadata. Only InnoDB data and log files are backed up. Only tables stored in their own tablespaces are backed up.
You are upgrading a MySQL instance to the latest 8.0 version.
Examine this output:
(imagen)
You plan to add this parameter to the configuration: innodb_directories=’/innodb_extras’
Which statement is true? It defines all innodb tablespace options relative to a starting parent directory. It is not necessary because innodb_data_home_dir is already defined. It allows scanning of other locations to discover more innodb tablespaces. It moves all innodb tablespaces to the /innodb_extras directory to enable a new innodb_data_home_dir to be defined. It adds more temporary workspace in addition to the innodb_tmpdir location.
You wish to protect your MySQL database against SQL injection attacks.
Which method would fail to do this? installing and configuring the Connection Control plugin avoiding concatenation of SQL statements and user-supplied values in an application using stored procedures for any database access using PREPARED STATEMENTS.
There are five MySQL instances configured with a working group replication.
Examine the output of the group members:
(imagen)
Which two statements are true about network partitioning in the cluster? (Choose two.) The cluster will shut down to preserve data consistency. The cluster has built-in high availability and updates group_replication_ip_whitelist to remove the unreachable nodes. The group replication will buffer the transactions on the online nodes until the unreachable nodes return online. There could be both a 2 node and 3 node group replication still running, so shutting down group replication and diagnosing the issue is recommended. A manual intervention to force group members to be only the working two instances is required.
Examine this command and output:
(imagen)
Which two options will improve the security of the MySQL instance? (Choose two.) Remove group read/write privileges from the private_key.pem file. Remove world read privileges from the server-cert.pem certificate file. Change the group ownership of the mysql directory to the mysql user group. Remove world read privileges from the public_key.pem file. Change the parent directory owner and group to mysql. Remove the world read/execute privilege from the accounting directory.
What does the slave I/O thread do? connects to the master and requests it to send updates recorded in its binary logs monitors and schedules I/O calls to the subsystem for the relay logs acquires a lock on the binary log for reading each event to be sent to the slave reads the relay log and executes the events contained in them.
You have upgraded the MySQL binaries from 5.7.28 to 8.0.18 by using an in-place
upgrade.
Examine the message sequence generated during the first start of MySQL 8.0.18:
(imagen)
Which step or set of steps will resolve the errors? Remove the redo logs. Replace the MySQL binaries with the 5.7.28 binaries. Prepare the tables for upgrade. Upgrade to 8.0.18 again. Execute: mysqlcheck --check-upgrade mysql columns_priv event proc proxies_priv tables_priv. Start mysqld again using the --upgrade=FORCE option. Execute: mysqlcheck --repair mysql columns_priv event proc proxies_priv tables_priv. Go to the <datadir>/mysql directory and execute: myisamchk --update-state columns_priv event proc proxies_priv tables_priv.
Which two storage engines provide a view of the data consistent with the storage system at
any moment? (Choose two.) MyISAM NDB MEMORY ARCHIVE InnoDB.
Which statement is true about displaying and retrieving data with MySQL Enterprise
Monitor Query Analyzer? The Query Analyzer graph view range selector can extend to cover the same hour over multiple days. It is possible to filter a Query Analyzer view graph by database and by table. The Query Analyzer can plot a CPU utilization graph for remote hosts with a MySQL Enterprise Service Manager's built-in Agent installation. It is possible to export statements included in a graph selection in CSV format.
How can mysql_multi be configured to allow MySQL instances to use the same port
number? The instances use different user accounts unique to each instance. The instances listen on different IP addresses. The instances use different socket names. The instances have appropriate net masks set.
Which three are requirements for a secure MySQL Server environment? (Choose three.) Restrict the number of OS users that have access at the OS level. Ensure appropriate file system privileges for OS users and groups. Minimize the number of non-MySQL Server-related processes running on the server host. Encrypt the file system to avoid needing exact file-system permissions. Keep the entire software stack on one OS host. Run MySQL server as the root user to prevent incorrect sudo settings.
You have replication configured, which consists of one master and one slave on different
hosts with an asynchronous replication channel between them.
Your goal is to decrease the amount of data that is transferred between these two hosts.
It is confirmed that the slave instance does not need to have data from the example
database.
Which replication filter contributes to your goal? on slave: --replicate-wild-ignore=example.% on slave: --replicate-ignore-db=example on master: --replicate-ignore-db=example on master: --binlog-ignore-db=example on slave: --binlog-ignore-db=example.
You reconfigure and start a slave that was not replicating for several days.
The configuration file and CHANGE MASTER command are correct. Examine the GTID
information from both master and slave:
(imagen)
Which statement is true? Replication will fail because the slave has purged more aaaaaaaa-aaaa-aaaa-aaaaaaaaaaaaaaaa transactions than the master. Replication will fail because the master does not have the required transaction with bbbbbbbb-bbbb- bbbb-bbbb-bbbbbbbbbbbb GTIDs in its binary logs. Replication will fail because the master has already purged transactions with cccccccccccc- cccc- cccc-cccccccccccc GTIDs. Replication will fail because of inconsistent numbers in cccccccc-cccc-cccc-cccccccccccccccc GTIDs. Replication will work.
Examine these InnoDB Cluster parameter settings:
(imagen)
Now examine the partial status:
(imagen)
A permanent network failure isolates host3.
Which two statements are true? (Choose two.) The instance deployed on host2 is elected as the new primary instance. The instance deployed on host3 is expelled from the cluster and must be rejoined using cluster.addInstance ('host3:3377') The instance deployed on host3 will automatically rejoin the cluster when connectivity is re-established. Failure of the instance deployed on host1 provokes an outage. The issuing command cluster.switchToMuitiPrimaryMode() will fail to enable multiprimary mode. The primary instance can be specified by using the command cluster.setPrimaryInstance(<host>:<port>).
Examine this command, which executes successfull
mysqlbackup --defaults-file=/backups/server-my.cnf --backup-dir=/backups/full
copy-back
Which statement is true about the copy-back process? The copy-back process is used to overwrite a new backup over an existing backup. It restores files from the data directory to their original MySQL server locations. It restores files from the backup directory to their original MySQL server locations. The copy-back process makes inconsistent backups.
Which two are valid uses for binary logs on a MySQL instance? (Choose two.) recording the order in which queries are issued audit of all queries point-in-time recovery replication logging the duration and locks for all queries.
Which three are types of information stored in the MySQL data dictionary? (Choose three.) performance metrics InnoDB buffer pool LRU management data access control lists view definitions server runtime configuration server configuration rollback stored procedure definitions.
Examine this statement, which executes successfully:
(imagen)
Now examine this query:
(imagen)
Which two statements can do this? (Choose two.) ALTER TABLE employees ADD INDEX (birth_date DESC); ALTER TABLE employees ADD INDEX ((MONTH(birth_date))); ALTER TABLE employees ADD COLUMN birth_month tinyint unsigned GENERATED ALWAYS AS (MONTH(birth_date)) VIRTUAL NOT NULL, ADD INDEX (birth_month); ALTER TABLE employees ADD COLUMN birth_month tinyint unsigned GENERATED ALWAYS AS (birth_date>>'$.month') VIRTUAL NOT NULL, ADD INDEX (birth_month); ALTER TABLE employees ADD INDEX ((CAST(birth_date->>'$.month' AS unsigned))); ALTER TABLE employees ADD INDEX (birth_date);.
You have semi-synchronous replication configured and working with one slave.
rpl_semi_sync_master_timeout has never been reached.
You find that the disk system on the master has failed and as a result, the data on the
master is completely unrecoverable.
Which two statements are true? (Choose two.) No committed transactions are lost. The slave automatically identifies that the master is unreachable and performs any required actions so that applications can start using the slave as the new master. As soon as the incident happens, application can read data from the slave and rely on it to return a full and current set of data. Reads from the slave can return outdated data until the value of the rpi_semi_sync_master_timeout variable is reached. Reads from the slave can return outdated data for some time, until it applies all transactions from its relay log. A small amount of committed transactions may be lost in case they were committed just before the disk failure.
|
