MySQL2

Which feature is provided by multi-source replication?. providing a common source for the same data to be replicated to other servers. allowing multiple servers to back up to one server. managing conflicts between two sets of the same data. providing multi-source replication where all servers act as the master.

Which three commands can report all the current connections running on the MySQL server?. SELECT * FROM performance_schema.events_transactions_current. SELECT * FROM performance_schema.threads. SHOW FULL PROCESSLIST. SELECT * FROM information_schema.processlist. SHOW EVENTS. SELECT * FROM sys.metrics. SELECT * FROM information_schema.events. SELECT * FROM sys.statement_analysis.

You want to dump all databases with names that start with "db". Which command will achieve this?. 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. mysqlpump -- include-tables-db.% --result-file=all_db_backup.sql.

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.

Which three actions will secure a MySQL server from network-based attacks?. Construct a perimeter network to allow public traffic. Place the MySQL instance behind a firewall. Use network file system (NFS) for storing data. Change the listening port to 3307. Use MySQL Router to proxy connections to the MySQL server. Allow connections from the application server only.

Consider this shell output and executed commands: [root@oel7~] # ps aux | grep mysqldmysql 2076 3.5 24.6 1386852 372572 2 Ssl 12:01 0:01 /usr/sbin/mysqld[root@oel7 ~]# kill -15 2076 Which statement is true about MySQL server shutdown?. kill -15 and kill -9 are effectively the same forced shutdown that risk committed transactions not written to disk. mysqld safe prohibits commands that would harm the operation of the server. An error would be returned by the kill command. kill -15 carries out a normal shutdown process, such as mysqladmin shutdown. kill -15 should be avoided. Use other methods such as mysqladmin shutdowm or systemctl stop mysqld.

Which two statements are true about the data dictionary object cache?. The dictionary object caches use a Least Recently Used (LRU) algorithm to manage entries in each cache. Character set and collation definition objects are not cached. All dictionary object caches have a hard-coded size. If the dictionary object cache becomes full, MySQL server will be unable to create any more tables/objects. tablespace_definition_cache sets the number of tablespace objects that can be stored in the dictionary object cache.

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?. ALTER USER 'joe'@'%' ACCOUNT LOCK. ALTER USER 'joe'@'%' PASSWORD HISTORY 0. REVOKE ALL PRIVILEGES ON *.* FROM 'joe'@'%'. ALTER USER 'joe'@'%' SET password='*invalid*'. ALTER USER 'joe'@'%' IDENTIFIED BY '*invalid*' PASSWORD EXPIRE. REVOKE USAGE ON *.* FROM 'joe'@'%'.

You must replay the binary logs on your MySQL server. Which command do you use?. cat binlog.000003 binlog.000004 binlog.000005 | mysql -h 127.0.0.1. mysqlpump -h 127.0.0.1 binlog.000003 binlog.000004 binlog.000005. mysql -h 127.0.0.1 --local-infile binlog.000003 binlog.000004 binlog.000005. mysqlbinlog binlog.000003 binlog.000004 binlog.000005 | mysql -h 127.0.0.1. mysqlbinlog -h 127.0.0.1 binlog.000003 binlog.000004 binlog.000005.

There has been an accidental deletion of data in one of your MySQL databases.You determine that all entries in the binary log file after position 1797 must be replayed. Examine this partial command: mysqlbinlog binlog.000008 --start-position=1798 Which operation will complete the command?. --write-to-remote-server must be added to the command line to update the database tables. No changes required. It automatically updates the MySQL Server with the data. It can be piped into the MySQL Server via the command-line client. You must use --stop position=1797 to avoid the DELETE statement that caused the initial problem.

Which three requirements must be enabled for group replication?. replication filters. semi-sync replication plugin. slave updates logging. binary log checksum. primary key or primary key equivalent on every table. binary log MIXED format. binary log ROW format.

Which utility would you use to view the queries in the slow query log sorted by average query time?. mysqlcheck. mysqlshow. mysqlimport. mysqldump. mysqldumpslow.

You must store connection parameters for connecting a Linux-based MySQL client to a remote Windows-based MySQL server listening on port 3309. Which four methods can be used to configure user, host, and database parameters?. Embed login information into the SSH tunnel definition. Execute mysql_config_editor to configure the user connection. Configure ~/.my.cnf. Execute the mysqladmin command to configure the user connection. Execute the command in a bash script. Configure environment variables. Define a UNIX socket. Use the usermod program to store static user information. Configure ~/.ssh/config for public key authentication.

Which statement is true about the my.ini file on a Windows platform while MySQL server is running?. MySQL server does not use the my.ini option file for server configuration options. The option file is read by the MySQL server service only at start up. Editing the file will immediately change the running server configuration. Using SET PERSIST will update the my.ini file.

You administer a three node, single primary InnoDB Cluster. Examine cluster.status() displayed here: "statusText": "Cluster is ONLINE and can tolerate up to ONE failure." Which two statements are true?. If two instances are unreachable because of network failure, the cluster will reconfigure to work with a single instance. Reconfiguring the cluster as multi-primary, will increase tolerance to two failures. There is a quorum and transactions can be committed normally. If two instances crash, it will produce an outage. Restarting an arbitrary instance will always provoke primary instance failover. Shutting down two instances with the SHUTDOWN command will produce an outage.

Which three statements are true about MySQL Enterprise Firewall?. On Windows systems, it is controlled and managed using the Windows Internet Connection Firewall control panel. System tables named firewall_users and firewall_whitelist in the mysql database provide persistent storage of firewall data. It is available only in MySQL Enterprise versions. It provides INFORMATION_SCHEMA tables that enable views into firewall data. Firewall functionality is dependent on SHA-256 and ANSI-specific functions built in to the mysql.firewall table. It shows only notifications for blocked connections, which originated outside of your network's primary domain.

Which two statements are true about general tablespaces?. General tablespaces support temporary tables. Dropping a table from a general tablespace releases the space back to the operating system. An existing table can be moved into a general tablespace. A general tablespace can have multiple data files. A new table can be created explicitly in a general tablespace.

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?. 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. Reads from the slave can return outdated data until the value of the rpl_semi_sync_master_timeout variable is reached. No committed transactions are lost. 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. As soon as the incident happens, application can read data from the slave and re.

You have configured a working MySQL InnoDB Cluster in single-primary mode. What happens when the primary instance goes down due to a network problem?. The cluster will continue to function with read-only members. A new primary is automatically elected. The cluster goes into wait mode until a new member is manually promoted as primary. The cluster detects network partitioning and shuts down to remain consistent. All remaining members in the cluster are automatically set to read-write mode.

Which two statements are true about raw binary backups?. They are converted to a highly compressible binary format. They are required to obtain FIPS security compliance. The resulting files are easily human readable. The data format is identical to how MySQL stores the data on disk. They are faster than logical backups because the process is a simple file or file system copy.

Which characters are most commonly used in a SQL injection attack?. ' and ". < and >. null (\0) and newline (\n). ^ and $. + and -.

You are investigating performance problems in a MySQL database; all data fits in memory. You determine that SELECT queries to one table is the main cause for poor response times. Which two have the biggest potential for eliminating the problem?. high concurrency. operating system resources. column definitions. innodb mutexes. non-transaction storage engine. table indexes.

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 slave needs to be restored from backup. The slave relay logs should be deleted; execute CHANGE MASTER to adjust the replication relay log file name, then issue START SLAVE;. The relay logs from the master should be used to replace the corrupted relay logs.

Which two statements are true about the mysqld-auto.cnf file?. It is always updated with changes to system variables. This file is for logging purposes only and is never processed. It is read and processed at the end of startup configuration. This file is for storing MySQL Server configuration options in JSON format. It is read and processed at the beginning of startup configuration. This file is for storing MySQL server_uuid values only.

Which three are types of InnoDB tablespaces?. data tablespaces. schema tablespaces. redo tablespaces. temporary table tablespaces. undo tablespaces. encryption tablespaces.

Which two are true about differences between logical and physical upgrades of MySQL databases?. Logical upgrades are much faster because they do not require restarting the mysqld process. Physical upgrades are much faster because they do not require restarting the mysqld process. Physical upgrades are performed for current instances on bare metal deployments, whereas logical upgrades are used for virtual machines or containerized instances. Post-upgrade table storage requirements after physical upgrades are usually smaller than that after logical upgrades. Post-upgrade table storage requirements after logical upgrades are usually smaller than that after physical upgrades. Physical upgrades leave data in place, whereas logical upgrades require data to be restored from mysqldump-type backups taken before the upgrades.

MySQL is installed on a Linux server with this configuration: [mysqld]user=mysqldatadir=/data/mysql Which method sets the default authentication to SHA-256 hashing for authenticating user account passwords?. Define CREATE USER ' '@'%' IDENTIFIED WITH sha256_password in the MySQL instance. Add default_authentication_plugin=sha256_password in the configuration file. Add default_authentication_plugin=mysql_native_password in the configuration file. Set validate-user-plugins=caching_sha2_password in the configuration file.

Examine this statement, which executes successfully: CREATE USER mary@192.0.2.100 IDENTIFIED BY 'P@SSw0rd' REQUIRE NONE PASSWORD EXPIRE; Which two are true?. Mary must connect using the username 'mary@192.0.2.100'. Mary requires no password to connect to the MySQL server. Mary must connect from the client machine 192.0.2.100. Mary cannot connect to the MySQL server until the DBA resets her password. Mary cannot query data until she changes her password.

MySQL Enterprise Monitor Query Analyzer is configured to monitor an instance. Which statement is true?. The Query Response Time index (QRTi) is fixed to 100ms and cannot be customized. Enabling the events_statements_history_long consumer allows tracking the longest running query. An agent must be installed locally on the instance to use the Query Analyzer. The Query Analyzer can monitor an unlimited number of normalized statements. The slow query log must be enabled on the monitored server to collect information for the Query Analyzer.

Which two are true about binary logs used in asynchronous replication?. The master connects to the slave and initiates log transfer. They contain events that describe all queries run on the master. They contain events that describe database changes on the master. They are pulled from the master to the slave. They contain events that describe only administrative commands run on the master.

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?. Use PURGE BINARY LOGS to <binlog_name>. Use SET GLOBAL binlog_expire_logs_seconds=<value> and run the FLUSH BINARY LOGS command. Use SET GLOBAL binlog_expire_logs_seconds=<value> and restart the server. Use SET PERSIST binlog_expire_logs_seconds=<value>. Set binlog_expire_logs_seconds = 0 in my.cnf and restart the server. Set binlog_expire_logs_seconds in my.cnf.

Which three methods display the complete table definition of an InnoDB table?. hexdump -v -C table.frm. REPAIR TABLE table USE_FRM. mysqldump --no-data schema table. Query the Information Schema. SELECT * FROM table 1\G. SHOW CREATE TABLE.

Which two methods allow a DBA to reset a user's password?. SET PASSWORD statement. mysql_secure_installation utility. ALTER USER statement. GRANT statement. mysqladmin client program.

You use Row Based Replication and need to see "pseudo-SQL" statements for the replication event that is located in the log_file position NNNNN file. Which command should you use?. mysqlshow --debug --stop-position=NNNNN log_file. mysqlbinlog --verbose --start-position=NNNN log_file. mysqlbinlog --debug --start-position=NNNNN log_file. mysqlbinlog --debug -- stop-position=NNNNN log_file. mysqlshow --verbose --stop-position=NNNNN log_file. mysqlbinlog --verbose - - stop-position=NNNNN log_file. mysqlshow --debug --start-position=NNNNN log_file. mysqlshow --verbose --start-position=NNNNN log_file.

Examine the command, which executes successfully: shell> mysqld --initialize Which statement is true?. The root password is created in the error log in plain text. The installation creates a temporary test environment with data in the /tmp directory. The installation is created without enforcing or generating SSL certificates. The root password is not created allowing easy access from the same host.

Which two statements are true about general tablespaces?. General tablespaces support temporary tables. Dropping a table from a general tablespace releases the space back to the operating system. A new table can be created explicitly in a general tablespace. An existing table can be moved into a general tablespace. A general tablespace can have multiple data files.

A developer accidentally dropped the InnoDB table Customers from the Company database. There is a datadir copy from two days ago in the dbbackup directory. Which set of steps would restore only the missing table?. Stop the MySQL Server process and restart it with the command:mysqld --basedir=/usr/local/mysql --datadir=/dbbackupRun mysqldump on this table and restore the dump file. Stop the MySQL Server process and restart it with the command:mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysqlRun mysqldump on this table and restore the dump file. Stop the MySQL Server process, copy the Customers.ibd file from the dbbackup directory, and start the mysqld process. Stop the MySQL Server process, and execute:mysqlbackup --datadir=/var/lib/mysql --backup-dir=/dbbackup -- include-tables= 'Company\. Customers' copy-backStart the mysqld process.

Binary log events for the 'mydb1' schema must be copied to a different schema name 'mydb2'. Which command will do this?. mysqlbinlog --read- from-remote-server --raw | sed 's/mydb1/mydb2/g' | mysql. mysqlbinlog --rewrite-db= 'mydb1->mydb2' | mysql. mysqlbinlog --datebase=mydb1 --database=mnydb2 | mysql. mysqlbinlog --rewrite-db='mydb1' --rewrite-db='mydb2' | mysql.

Examine this command, which executes successfully: $ mysqlbackup --user=dba --password --port=3306 --with-timestamp --only-known-file-types --backup-dir=/export/backups backup Which statement is true?. Only tables stored in their own tablespaces are backed up. Only InnoDB data and log files are backed up. Only non-encrypted files are backed up. Only files for MySQL or its built-in storage engines are backed up. The backup includes only data files and their metadata.

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?. ib_buffer_pool. ib_logfile0. mysql.ibd. ibdata1. ibtmp1. undo_001.

Examine this command: shell> mysqldump --no-create-info --all-databases --result-file=dump.sql Which statement is true?. It will not write CREATE TABLESPACE statements. It will not write CREATE LOGFILE GROUP statements. It will not write CREATE DATABASE statements. It will not write CREATE TABLE statements.

You are considering using file-system snapshots to back up MySQL. Which three statements are true?. There is a slight performance cost while the snapshot is active. The backup window is almost zero from the perspective of the application. They allow direct copying of table rows with operating system copy commands. They do not back up views, stored procedures, or configuration files. They take roughly twice as long as logical backups. They work best for transaction storage engines that can perform their own recovery when restored. They do not use additional disk space.

Examine this parameter setting: audit_log=FORCE_LOG_PERMANENT What effect does this have on auditing?. It prevents the audit plugin from being removed from the running server. It prevents the audit log from being removed or rotated. It causes the audit log to be created if it does not exist. It will force the load of the audit plugin even in case of errors at server start.

Which are three benefits of using mysqlbackup instead of mysqldump?. mysqlbackup can perform partial backup of stored programs. mysqlbackup allows logical backups with concurrency resulting in faster backups and restores. mysqlbackup integrates tape backup and has the virtual tape option. mysqlbackup can back up tables with the InnoDB engine without blocking reducing wait times due to contention. mysqlbackup does not back up MySQL system tables, which shortens backup time. mysqlbackup restores data from physical backups, which are faster than logical backups.

Which two statements are true about MySQL Installer?. It provides only GUI-driven, interactive installations. It installs most Oracle MySQL products. Manual download of separate product packages is required before installing them through MySQL Installer. It provides a uniform installation wizard across multiple platforms. It performs product upgrades.

Examine these statements and output: mysql> GRANT PROXY ON accounting@localhost TO ' '@'%' ; mysql> SELECT USER(), CURRENT_USER(), @@proxy_user; (image) Which statement is true? (Choose one). The user failed to define a username and the connecting username defaulted to ' '@'%'. The user is authorized as the rsmith@localhost user. The user is authenticated as the anonymous proxy user ' '@'%'. The user is logged in with --user=accounting as an option. The user is authorized as the accounting@localhost user.

User `fwuser`@`localhost` is registered with the MySQL Enterprise Firewall and has been granted privileges for the SAKILA database. Examine these commands that you executed and the results: mysql> SELECT MODE FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS WHERE USERHOST = 'fwuser@localhost' ; (image) mysql> SELECT RULE FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST WHERE USERHOST = 'fwuser@localhost' ; (image) You then execute this command: mysql> CALL mysql.sp_set_firewall_mode('fwuser@localhost' , 'RESET') ; Which two are true?. The fwuser@localhost account is removed from the mysql.user table. The information_schema.MYSQL_FIREWALL_WHITELIST table is truncated. The whitelist of the fwuser@localhost account is truncated. The mysql.firewall_users table is truncated. The firewall resets all options to default values. The fwuser@localhost account mode is set to DETECTING. The fwuser@localhost.

A newly deployed replication master database has a 10/90 read to write ratio. The complete dataset is currently 28G but will never fluctuate beyond +-10%. The database storage system consists of two locally attached PCI- E Enterprise grade disks (mounted as /data1 and /data2). The server is dedicated to this MySQL Instance. System memory capacity is 64G. The my.cnf file contents are displayed here: [mysqld] datadir=/data1/ innodb_buffer_pool_size=28G innodb_log_file_size=150M Which four changes provide the most performance improvement, without sacrificing data integrity?. innodb-doublewrite=off. innodb_log_group_home_dir=/data2/. innodb_log_file_size=1G. innodb_undo_directory=/dev/shm. log-bin=/data2/. innodb_flush_log_at_trx_commit=0. sync_binlog=0. innodb_buffer_pool_size=32G. disable-log-bin.

Examine these commands, which execute successfully on the ic1 host: mysqlsh> dba.createCluster( 'cluster1' , {memberWeight:35}) mysqlsh> var mycluster = dba.getCluster () mysqlsh> mycluster.addInstance ( 'ic@ic2' , {memberWeight:25}) mysqlsh> mycluster.addInstance( 'ic@ic3' , {memberWeight:50}) Now examine this configuration setting, which is the same on all nodes: group_replication_consistency=BEFORE_ON_PRIMARY_FAILOVER Which statement is true if primary node ic1 fails?. Node ic2 becomes the new primary and existing transactions are considered stale and rolled back. Node ic3 becomes the new primary and existing transactions are considered stale and rolled back. Node ic3 becomes the new primary and is ignored until any backlog of transactions is completed. Only two nodes remain so the election process is uncertain and must be done manually. Node ic2 becomes the new primary and is ignored until any back.

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: (image) Which step or set of steps will resolve the errors?. Start mysqld again using the --upgrade=FORCE option. Go to the /mysql directory and execute: myisamchk --update-state columns_priv event proc proxies_priv tables_priv. Execute: mysqlcheck --repair mysql columns_priv event proc proxies_priv tables_priv. 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.

Database test contains a table named city that has the InnoDB storage engine. (image) What is the content of the test folder in the data directory?. city.MYD, city.MYI, and city.sdi. city.ibd. city.ibd and city.sdi. city.ibd and city.frm. city.ibd, city.frm, and city.sdi.

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

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?. shell> cp /backup/customers.sdi /var/tmp. mysql> SOURCE '/var/tmp/customers.sdi. shell> cp /backup/customers.sdi /var/lib/mysql/shop/. mysql> ALTER TABLE shop.customers DISCARD TABLESPACE. shell> cp /backup/customers.frm /var/lib/mysql/shop/. mysql> IMPORT TABLE FROM /var/lib/mysql/shop/customers.sdi. mysql> IMPORT TABLE FROM /var/tmp/customers.sdi. mysql> ALTER TABLE shop.customers IMPORT TABLESPACE.

Which two queries are examples of successful SQL injection attacks?. SELECT user, passwd FROM members WHERE user = ' ? ' ; INSERT INTO members ('user' , 'passwd' ) VALUES ('bob@example.com' , 'secret' ) ;-- ';. SELECT user, phone FROM customers WHERE name = ' \; DROP TABLE users; -- '; C. SELECT id, name FROM user WHERE user.id= (SELECT members.id FROM members). 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 email, passwd FROM members WHERE email = 'INSERT INTO members('email' , ' passwd ' ) VALUES ('bob@example.com' , 'secret') ;-- ';.

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: (image) Which two statements are true?. The transactions table was created with innodb_file_per_table=OFF. Truncating the sales and leads table will free up disk space. 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 transactions table will free up the most disk space.

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?. MEMORY. BLACKHOLE. ARCHIVE. NDB. MyISAM. InnoDB.

A colleague complains about slow response time on your website. Examine this query and output: (image) What is the most likely cause for the high number of lock waits?. You use the InnoDB storage engine and statements wait while data is inserted. The Innodb Buffer pool is full. You use the MyISAM storage engine for most common tables. Your table accesses wait for the operating system level flush.

Examine this SQL statement: UPDATE world.city SET Population = Population * 1.1 WHERE CountryCode IN (SELECT Code FROM world.country WHERE Continent = ' Asia ' ) Which set of privileges will allow Tom to execute this SQL statement?. 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`@`%`. GRANT ALL PRIVILEGES ON `world`.`city` TO `tom`@`%` GRANT SELECT (`code`) ON `world`.`country` TO `tom`@`%.

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?. All databases are backed up to the output file. All non-active binary logs are removed from the master. All binary logs are backed up and then deleted. All binary logs are deleted from the master. 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.

Examine this command and output: (image) Which two options will improve the security of the MySQL instance?. Remove the world read/execute privilege from the accounting directory. Remove world read privileges from the public_key.pem file. Change the group ownership of the mysql directory to the mysql user group. Change the parent directory owner and group to mysql. Remove world read privileges from the server-cert.pem certificate file. Remove group read/write privileges from the private_key.pem file.

Users report errors when trying to connect from 192.0.2.5 and is connecting using the mysql_native password authentication plugin. Examine these commands and output: (image) (image) Which statement identifies the cause of the errors?. max_connections is too small. Network connectivity issues occurring between client and the MySQL instance. Connections are attempted without a valid user account or password. User accounts are defined using the mysql_native_pasword plugin for password authentication. thread_cache is too small. skip_name_resolve is enabled.

Mary connects to a Linux MySQL Server from a client on a Windows machine. Examine this statement and output: (image) Which two are true?. Mary connected from a client machine whose IP address is 192.0.2.101. Mary connected to the database server whose IP address is 192.0.2.101. Mary has the privileges of account mary@%. Mary connected using a UNIX socket. Mary authenticated to the account mary@192.0.2.101.

Examine these InnoDB Cluster parameter settings: cluster.setInstanceOption('host1:3377', 'memberWeight', 40) cluster.setInstanceOption('host2:3377', 'memberWeight', 30) cluster.setInstanceOption('host3:3377', 'memberweight', 40) cluster.setInstanceOption('host3:3377', 'exitstateAction', "ABORT_SERVER") cluster.setOption ("expelTimeout",1) Now examine the partial status: (image) A permanent network failure isolates host3. Which two statements are true?. 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 instance deployed on host3 is expelled from the cluster and must be rejoined using cluster.addInstance('host3:3377'). The primary instance can be specified by using the command cluster.setPrimaryInstance (:). The instance deployed on host2 is elected as the new primary instance.

Examine this query and its output: (image) Which two statements are true?. User bob had a significantly higher ratio of SELECT + INSERT statements to QUIT than both app and root users. User bob had the largest total time waiting for locks. The app user had the highest total number of rows read from storage engines. The root user had the largest number of modified rows for a SELECT statement. The root user had the largest single wait time.

Examine this snippet from the binary log file named binlog.000036: 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-11-20 14:55:16' binlog.000036 | mysql. mysqlbinlog --stop-datetime='2019-11-20 14:55:18' binlog.000036 | mysql. mysqlbinlog --stop-position=500453 binlog.000036 | mysql.

Four nodes are configured to use circular replication. Examine these configuration parameters for each each node: slave_parallel_type=DATABASE ; slave_parallel_workers=4 slave_preserve_commit_order=0 Which statement is true?. Each slave thread is responsible for updating a specific database. Cross-database constraints can cause database inconsistency. Setting slave_parallel_type=DATABASE won't work for circular replication; it should be set to LOGICAL_CLOCK. Increasing slave_parallel_workers will improve high availability. Setting slave_preserve_commit_order to ON will improve data consistency. Setting transaction_allow_batching to ON will improve data consistency.

Examine these two reports taken 100 seconds apart: (image) Your MySQL system normally supports 50-75 concurrent connections. Which configuration change will improve performance?. increase max_connections. decrease open_files_limit. decrease table_definition_cache. increase table_open_cache.

Examine these commands and results: (image) Jane must create a temporary table named TOTALSALES in the SALES database. Which statement will provide Jane with the required privileges based on the principle of least privilege?. GRANT CREATE TEMPORARY TABLES, INSERT, UPDATE, DELETE, SELECT ON sales.totalsales TO jane;. GRANT CREATE TEMPORARY TABLES ON sales.* TO jane;. GRANT CREATE TEMPORARY TABLES ON sales.totalsales TO jane;. GRANT ALL ON sales.* TO jane;.

Your MySQL Server is running locally on your Linux installation, and has SSL connections configured but not mandatory: (image) What is the reason for SSL not being used?. It is connected via a UNIX socket. A current database is not selected. There is nothing to encrypt. The root user cannot use encryption. The root user must use ssl_fips_mode = ON.

A scientific data gathering application uses a MySQL instance back end for data management. There is a high concurrency of transactions at thousands of transactions per second of volatile data. A restore from binary logs is planned using the command: (image) Which two characteristics cause the restore to be inconsistent to the original data?. Transaction rate is too high to get a consistent restore. Multiple binary logs cannot be specified on the command line. Temporary tables cannot persist across binary logs. The temporal values do not offer high enough precision. The time span of binary logs is too long to restore.

Examine this command and output: select *from table_lock_waits_summary_by_table where COUNT_STAR>0 limit 1\G (image) Which two are true?. I/O distribution is approximately 50/50 read/write. The I/O average time is 532728. 22902028 rows were deleted. Average read times are approximately three times faster than writes. The longest I/O wait was for writes.

Examine this statement, which executes successfully: CREATE TABLE employees ( emp_no int unsigned NOT NULL, birth_date date NOT NULL, first_name varchar(14) NOT NULL, last_name varchar(16) NOT NULL, hire_date date NOT NULL, PRIMARY KEY (emp_no) )ENGINE=InnoDB; Now examine this query: SELECT emp_no, first_name, last_name, birth_date FROM employees WHERE MONTH (birth_date) = 4; You must add an index that can reduce the number of rows processed by the query. Which two statements can do this?. 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 DESC);. ALTER TABLE employees - ADD INDEX ((MONTH(birth_date)));. ALTER TABLE employees - ADD INDEX (birth_date);.

You must export data from a set of tables in the world_x database. Examine this set of tables: Tables (country, countryinfo, location) Which two options will export data into one or more files?. shell> mysqldump world_x country countryinfo location > mydump.sql. mysql> SELECT * INTO OUTFILE '/output/country. txt' FROM world_x.country; mysql> SELECT * INTO OUTFILE '/output/countryinfo. txt' FROM world_x.countryinfo; mysql> SELECT * INTO OUTFILE '/output/location. txt' FROM world_x.location;. shell> mysqlexport world_x country countryinfo location > mydump.sql. mysql> CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql/world_x/country' ; mysql> CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql/world_x/countryinfo' ; mysql> CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql/world_x/location' ;. shell> mysql --batch world_x.country world_x.countryinfo world_x.1ocation > mydump.sql.

You have an installation of MySQL 8 on Oracle Linux. Consider the outputs: (image) 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 are created in tmpdir only after they reach tmp_table_size. Temporary tables are created in tmpdir only if configured to use MyISAM. Temporary tables will use the InnoDB temporary tablespace located in datadir. Temporary tables will use the InnoDB temporary tablespace located in /tmp.

Examine this output: (image) Which change should optimize the number of buffer pool instances for this workload?. Decrease the number of buffer pool instances to 4. 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.

You recently upgraded your MySQL installation to MySQL 8.0. Examine this client error: ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/local/mysql/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory Which option will allow this client to connect to MySQL Server?. ALTER USER user IDENTIFIED WITH caching_sha2_password BY 'password';. [mysqld] default_authentication_plugin=sha256_password. [mysqld] default_authentication_plugin=caching_sha2_password. ALTER USER user IDENTIFIED WITH mysql_native_password BY 'password' ;. ALTER USER user IDENTIFIED WITH sha256_password BY 'password' ;. [mysqld] default_authentication_plugin=mysql_native_password.

Which two actions can obtain information about deadlocks?. Run the SHOW ENGINE INNODB MUTEX command from the mysql client. Enable the innodb_status_output_locks global parameter. Enable the innodb_print_all_deadlocks global parameter. Run the SHOW ENGINE INNODB STATUS command from the mysql client. Use the sys.innodb_lock_waits view.

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. Sort the "Exec" column and check for SQL queries with high Query Response Time index (QRTi) values. Look for queries with big prolonged spikes in a row activity/access graph in the times series graph.

Identify two ways to significantly improve data security. Configure mysqld to run as the system admin account, such as root. Use a private network behind a firewall. Configure mysqld to use only networked disks. Configure MySQL to have only one administrative account. Configure mysqld to use only local disks or attached disks and to have its own account in the host system.

You plan to upgrade your MySQL 5.7 instance to version 8.You have installed the 8 build of MySQL Shell. Examine this command executed from the operating system shell prompt: mysqlsh --uri root@localhost:3306 -- util check-for-server-upgrade Which statement is true?. It documents any problems with your 5.7 tables to make them ready to upgrade to 8. It fails because the operation name must be in camelCase. It fixes any problems with your 5.7 tables to make them ready to upgrade to 8. It is mandatory to clear the history of prior results before executing this process a second time or later. It fails because checkForServerUpgrade must be executed only within an active shell session as a method of the util object. It is mandatory to run this command so that MySQL 8.0 software's auto-upgrade process has the details it needs to operate properly.

Which two authentication plugins require the plaintext client plugin for authentication to work?. LDAP authentication. SHA256 authentication. Windows Native authentication. PAM authentication. MySQL Native Password. LDAP SASL authentication.

You are using mysqlcheck for server maintenance. Which two statements are true?. The mysqlcheck --check --all-databases command takes table write locks while performing a series of checks. The mysqlcheck --repair --all-databases command can repair an InnoDB corrupted table. The mysqlcheck --analyze --all-databases command performs a series of checks to spot eventual table corruptions. The mysqlcheck command can be renamed mysqlrepair so that it repairs tables by default. The mysqlcheck --optimize --all-databases command reclaims free space from table files.

User account baduser@hostname on your MySQL instance has been compromised. Which two commands stop any new connections using the compromised account?. ALTER USER baduser@hostname PASSWORD DISABLED;. ALTER USER baduser@hostname DEFAULT ROLE NONE;. ALTER USER baduser@hostname MAX_USER_CONNECTIONS 0;. ALTER USER baduser@hostname IDENTIFIED WITH mysql_no_login;. ALTER USER baduser@hostname ACCOUNT LOCK;.

You plan to install MySQL Server by using the RPM download. Which two statements are true?. You must manually initialize the data directory. You can provide the root password interactively. The MySQL RPM package installation supports deploying multiple MySQL versions on the same host. MySQL uses the RPM relocatable installation target feature. You can find the root password in the error log after the first start. The functionality is split among several RPM package files.

Which three settings control global buffers shared by all threads on a MySQL server?. tmp_table_size. innodb_buffer_pool_size. table_open_cache. sort_buffer_size. key_buffer_size. read_buffer_size.

Which command enables rule-based MySQL Auditing capabilities?. shell> mysqld --initialize --log-raw=audit.log. mysql> INSTALL COMPONENT audit_log;. mysql> INSTALL PLUGIN audit_log;. shell> mysql < audit_log_filter_linux_install.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 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. Use the AES_ENCRYPT() MySQL function on the option file.

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?. $HOME/.mysqlrc file. /etc/my.cnf file. DATADIR/mysqld-auto.cnf file. $HOME/.my.cnf file. $HOME/.mylogin.cnf file. $MYSQL_HOME/my.cnf file. $HOME/.mysql/auth/login file.

Which two are contained in the InnoDB system tablespace (ibdata1) by default?. doublewrite buffer. change buffer. InnoDB Data Dictionary. primary indexes. table data. user privileges.

Examine this command, which executes successfully: cluster.addInstance( ' @:' , {recoveryMethod: ' clone '}) Which three statements are true?. It is always slower than {recoveryMethod: ' incremental ' }. InnoDB tablespaces outside the datadir are able to be cloned. A target instance must exist, then it will be provisioned with data from an instance already in the cluster and joined to the cluster. The account used to perform this recovery needs the BACKUP_ADMIN privilege. 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.

Which two statements are true about using MySQL Enterprise Monitor Query Analyzer?. It is possible to retrieve a normalized statement, but never the exact statement that was executed. The single query QRTi pie chart in the Query Analyzer view is based on the average execution of all statements. It is possible to import data into the Query Analyzer from heterogeneous sources, such as CSV. It is possible to list and analyze statements in an arbitrary graph range selection from timeseries graphs. It is possible to configure the Query Analysis built-in advisor to get notified about slow query execution.

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?. Use SET PERSIST binlog_expire_logs_seconds=. Use SET GLOBAL binlog_expire_logs_seconds= and restart the server. Use PURGE BINARY LOGS to . Set binlog_expire_logs_seconds in my.cnf. Use SET GLOBAL binlog_expire_logs_seconds= and run the FLUSH BINARY LOGS command. Set binlog_expire_logs_seconds = 0 in my.cnf and restart the server.

Which three actions are effective in capacity planning?. adding circular replication nodes for increased DML capability. buying more RAM. buying more disk. buying more CPU. basing expected growth on an average of the last 3 years. upgrading to the latest application version. monitoring OS resources for patterns. consulting the application team about any future projects and use.

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. TDE can encrypt InnoDB and MyISAM tables only when the tables are stored in the SYSTEM tablespace. Lost tablespace encryption keys can be regenerated only if the master database key is known or present in the Key Vault specification. Both MyISAM and InnoDB tables can be encrypted by setting the keyring_engine = All variable in the MySQL configuration file.

Examine this command, which executes successfully: mysqlpump --user=root --password > full_backup.sql Which two databases will be excluded from this dump?. mysql. information_schema. world. employee. sys.

Examine this statement: mysql>DROP ROLE r_role1, r_role2; Which two are true?. You must revoke r_role1 and r_role2 from all users and other roles before dropping the roles. You must revoke all privileges from r_role1 and r_role2 before dropping the roles. It fails if at least one of the roles does not exist. Existing connections can continue to use the roles' privileges until they reconnect. It fails if you do not have the ADMIN OPTION of the roles r_role1 and r_role2. It fails if any of the roles is specified in the mandatory_roles variable.

Which two storage engines provide a view of the data consistent with the storage system at any moment?. InnoDB. ARCHIVE. MyISAM. MEMORY. NDB.

Which two methods can be used to determine whether a query uses the hash join algorithm?. EXPLAIN FORMAT=JSON. EXPLAIN FORMAT=TRADITIONAL. EXPLAIN without any formatting argument. EXPLAIN ANALYZE. EXPLAIN FORMAT=TREE.