MySQL E

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

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 true?. All binary logs are deleted from the server. All databases, excluding master metadata, are backed up to the output file. All databases are backed up to the output file. All non-active binary logs are removed from the server. All details regarding deleted logs and master metadata are captured in the output file. All binary logs are backed up and then deleted.

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

Examine this statement and response: mysql> SET GLOBAL join_buffer_size = 1024*1024; Query OK, 0 rows affected (0.00 sec) Which two are true?. All connections will use the new join_buffer_size value. All connections will use the original join_buffer_size value after MySQL Server restarts. Only new connections will use the new join_buffer_size value. All connections will use the new join_buffer_size value after MySQL Server restarts. Only the current connection will use the new join_buffer_size value.

After installing MySQL 8.0 on Oracle Linux 7, you initialize the data directory with the mysqld --initialize command. Which two will assist in locating the root password?. the root password displayed on the screen via a [Warning] message. the root password inserted in the error log set by the log-error=[file_name] variable. The root_pw variable stored in the mysql.install table. The root password written to the /root/.my.cnf file. as root, executing the SHOW PASSWORD command by using the SHA-256 password encryption plugin.

Examine this query and its output: Which two statements are true?. The root user had the largest single wait time. User bob had a significantly higher ratio of SELECT + INSERT statements to QUIT than both app and root users. 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 app user had the highest total number of rows read from storage engines.

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

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 MySQL configuration file. TDE can encrypt Innoce and NcyISAM tables only when the tables are stored in the SYSTEM. Lost tablespace encryption keys can be regenerated only if the master database key is known or present in the Key Vault specification.

You have configured MySQL Enterprise Transparent Data Encryption (TDE). What command encrypts a table?. ALTER INSTANCE ROTATE INNODB MASTER KEY;. ALTER TABLE <table> ENCRYPTION='Y';. UPDATE <table> SET ENCRYPTION = 'Y';. UPDATE information_schema.tables SET encryption='Y' WHERE table_name='table';.

Examine this parameter setting: audit_log=FORCE_LOG_PERMANENT What effect does this have on auditing?. 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. It prevents the audit plugin from being removed from the running server. It prevents the audit log from being removed or rotated.

The my_table table has a primary key. You want my_id to become the new primary key. Which statement will replace the old primary key with the new one?. CREATE PRIMARY INDEX my_table (my_id) REPLACE PRIMARY KEY;. ALTER TABLE my_table REPLACE PRIMARY KEY WITH my_id;. ALTER TABLE my_table DROP PRIMARY KEY; CREATE PRIMARY INDEX my_table (my_id);. ALTER TABLE my_table DROP PRIMARY KEY; ADD PRIMARY KEY (my_id);.

Which is true about MySQL physical backups?. They consist of text files containing the database structure and content information. They are portable only to other machines that have identical or similar hardware characteristics. They take longer to create because the database must be queried. They are created by using tools such as mysqlpump or mysqldump.

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?. Increasing slave_parallel_workers will improve high availability. Cross-database constraints can cause database inconsistency. Setting slave_parallel_type=DATABASE won't work for circular replication; it should be s LOGICAL_CLOCK. Setting slave_preserve_commit_order to ON will improve data consistency. Each slave thread is responsible for updating a specific database. Setting transaction_allow_batching to ON will improve data consistency.

Which two queries are examples of successful SQL injection attacks?. SELECT id, name FROM user WHERE user.id=(SELECT members.ID FROM members). SELECT email, passwd FROM members WHERE email = 'INSERT INTO members('email', 'passwd') VALUES ('bob@example.com','secret');--'. 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;--'. SELECT id, name FROM user WHERE id = 23 OR id = 32 AND 1=1;. SELECT id, name FROM user WHERE id = 23 OR id = 32 OR 1=1;.

Which are two of the requirements for a secure MySQL Server environment?. Minimize the number of non-MySQL Server-related processes running on the server host. Restrict the number of OS users that have access at the OS level. Keep the entire software stack on one OS host. Run MySQL server as the root user to prevent incorrect sudo settings. Encrypt the file system to avoid needing exact file-system permissions.

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

You must review configuration options for a new, large, busy reporting data warehousing MYSQL instance. Examine this parameter: [mysql] innodb_data_file_path= Which are two configurations either of which would satisfy long-term storage demands?. ibdatal:12M;ibdata2:12M:autoextend. ibdatal:12M; ibdata2:12M; ibdata3:12M. ibdata1:12M:autoextend. ibdatal:12M:/tmp/ibdata2:12M:autoextend. ibdatal:12M.

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

Which two situations will cause the binary log to rotate?. FLUSH HOSTS executed. max_binlog_size exceeded. max_binlog_cache_size exceeded. SET sync_binlog=1 executed. SET sql_log_bin=1 executed. FLUSH LOGS executed.

Examine these two reports taken 100 seconds apart: GLOBAL STATUS 1: Com_create_table = 500005 Com_drop_table = 500003 Com_flush = 23 Created_tmp_disk_tables = 400000 Created_tmp_tables = 1200000 Max_used_connections = 92 Open_files = 5000 Opened_files = 5000 Open_table_definitions = 3000 Open_tables = 1024 Opened_table_definitions = 2869 Opened tables = 3500000 Threads_connected = 62 Threads_running = 58 Uptime = 100000 GLOBAL STATUS 2: Com_create_table = 500505 Com_drop_table = 500498 Com_flush = 31 Created_tmp_disk_tables = 400400 Created_tmp_tables = 1201200 Max_used_connections = 92 Open_files = 5000 Here's the text from the image you provided: Opened_files = 7505 Open_table_definitions = 3000 Open_tables = 1024 Opened_table_definitions = 2873 Opened_tables = 3503500 Threads_connected = 67 Threads_running = 64 Uptime = 100100 Your MySQL system normally supports 50-75 concurrent connections. Which configuration change will improve performance?. decrease open_files_limit. decrease table_definition_cache. increase max_connections. increase table_open_cache.

Which are two reasons for running MySQL server as a dedicated user on the operating system?. The root account has insufficient database privileges to run the database server. It prevents the server from running under the system root account. It protects database files from non-DBA users. It keeps database files organized by that user account and makes them easier to read. It encourages use of different passwords for the root and mysql accounts.

"topology": { "host1:3377": { "address": "host1:3377", "mode": "R/W", [...] "status": "ONLINE", "version": "8.0.18" }, "host2:3377": { "address": "host2:3377", "mode": "R/O", [...] "status": "(MISSING)" }, "host3:3377": [ "address": "host3:3377", "mode": "R/O", [...] "status": "ONLINE", "version": "8.0.18" } } Which statement is true about the instance deployed on host2?. It can rejoin the cluster by using the command cluster.addInstance('<use:>@host3:3877). It has been removed from the cluster by using the command STOP GROUP REPLICATION. It can rejoin the cluster by using the command dba.reboot@clusterFromCompleteOutage();. It can be recovered from a donor instance on host 3 by cloning using the command cluster.rejoinInstance(''). It has been expelled from the cluster because of a transaction error.

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?. mysqlcheck -analyze --all-databases will execute normally on all tables and return a report. If OPTIMIZE LOCAL TABLE t; is invoked from another session, it executes normally and returns the status. If OPTIMIZE TABLE; is invoked, it will create a table lock on t and force a transaction rollback. If ANALYZE TABLE; is invoked from the same session, it hangs until the transaction is committed or rolled back.

Examine this statement and output: Which two queries offer the greatest potential for reducing the query execution time?. QN = 2. QN = 3. QN = 4. QN = 5. QN = 1.

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

Examine these statements, which execute successfully: TRUNCATE test; BEGIN; INSERT INTO test (id, name) VALUES (1, "Hello"); ROLLBACK; SELECT id FROM test; Which are two of the storage engines that would return a nonempty recordset for the test table when executing the statements?. ARCHIVE. BLACKHOLE. NDB. MEMORY. InnoDB.

Which three sets of item information are visible in the mysql system database?. time zone information and definitions. rollback segments. information about table structures. audit log events. performance monitoring information. plugins. help topics.

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

A MySQL client is installed on a Linux workstation with a default installation. The admin login credentials are known for connecting to a MySQL server running Microsoft Windows on remote host 192.0.2.1:3306. A direct connection to the world database is required using --user=admin. Which are three of the options that must be specified to complete this task with a single command?. --protocol=UDP. -host=192.0.2.1. --shared-memory-base-name=world. --port=3306. --password. --database=world. --protocol=pipe.

Examine these three statements, which are running concurrently. Session 1: select count(*) from t1; Session 2: update t1 set int=10 where PK=7; Session 3: update t1 set int=10 where PK=4; Which session or sessions must wait to complete?. Session 3 must wait only for Session 2 to complete. Session 2 and Session 3 must wait for Session 1 to complete. Neither Session 2 nor Session 3 waits for Session 1 to complete. Session 3 must wait for Session 1 and Session 2 to complete.

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

Examine these commands, which execute successfully on the icl 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 Primary node ic1 fails. Which statement is true?. 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 backlog of transactions is completed. Node ic3 becomes the new primary and is ignored until any backlog of transactions is completed. Node ic3 becomes the new primary and current transactions are considered stale and rolled back. Node ic2 becomes the new primary and current transactions are considered stale and rolled back.

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

A MySQL server is monitored using MySQL Enterprise Monitor's agentless installation. Which are two of the features that are available with this installation method?. security-related advisor warnings. operating system memory utilization. disk usage and disk characteristics including disk advisors warnings. MySQL Replication monitoring. CPU utilization.

Which are three functions of MySQL Enterprise Monitor?. centrally managing server configurations. Determining the availability of monitored MySQL servers. creating customized alerts and providing notification alerts. analyzing query performance. starting and stopping MySQL Server. starting MySQL Enterprise backups. starting logical backups.

Examine these InnoDB Cluster parameter settings: Some time after you retrieve this status, a permanent network failure isolates host3. Which two statements are true?. The primary instance can be specified by using the command cluster.setPrimaryInstance(<host>:<port>). The issuing command cluster.switchToMuitiPrimaryMode() will fail to enable multi-primary mode. The instance deployed on host3 will automatically rejoin the cluster when connectivity is re-established. The instance deployed on host3 is expelled from the cluster and must be rejoined using cluster.addInstance ('host3:3377'). The instance deployed on host2 is elected as the new primary instance. Failure of the instance deployed on host1 provokes an outage.

Which statement enables all roles granted to all users automatically?. SET PERSIST activate_all_roles_on_login=ON;. SET DEFAULT ROLE ALL TO '*'@'%';. SET ROLE ALL;. SET PERSIST mandatory_roles=ALL;.

Examine this statement and output: mysql> SHOW GRANTS FOR jsmith; +------------------------------------------+ | Grants for jsmith@% | +------------------------------------------+ | GRANT USAGE ON *.* TO 'jsmith'@'%' | | GRANT UPDATE (Name) ON 'world'.'country' TO 'jsmith'@'%' | +------------------------------------------+ 2 rows in set (0.00 sec) Which two SQL statements can jsmith execute?. UPDATE world.country SET Name='all';. UPDATE world.country SET Name='one' LIMIT 1;. UPDATE world.country SET Name CONCAT('New', Name);. UPDATE world.country SET Name='first' ORDER BY Name LIMIT 1;. UPDATE world.country SET Name='new' WHERE Name='old';.

Examine this command: shell> mysqldump-single-transaction-flush-logs-master-data-2-all-databases > backup_sunday.sql What additional action does the server take with the-flush-logs option?. It starts a new binary log. It deletes old error logs. It starts a new undo log. It deletes old binary logs.

You plan to take daily full backups, which include the ndbinfo and sys (internal) databases. Which command will back up the databases in parallel?. mysqlpump --include-databases=% > full-backup-$(date +%Y%m%d).sql OK. mysqlpump --all-databases > full-backup-$(date +%Y%m%d).sql. mysqldump --all-databases > full-backup-$(date +%Y%m%d).sql. mysqldump --single-transaction > full-backup-$(date +%Y%m%d).sql.

You are upgrading a MySQL instance to the latest 8.0 version. Examine this output You plan to add this parameter to the configuration: innodb_directories='/innodb_extras' Which statement is true?. It adds more temporary workspace in addition to the innodb_tmpdir location. It is not necessary because innodb_data_home_dir is already defined. It defines all innodb tablespace options relative to a starting parent directory. It moves all innodb tablespaces to the /innodb_extras directory to enable a new innodb_data_home_dir to be defined. It allows scanning of other locations to discover more innodb tablespaces.

You are backing up raw InnoDB files by using mysqlbackup. Which two groups of files will be backed up during a full backup?. *.sdi files. ibbackup files. *.CSM files. ib_logfile* files. *.ibd files.

How can mysql_multi be configured to allow MySQL instances to use the same port number?. The instances listen on different IP addresses. The instances use different socket names. The instances have appropriate net masks set. The instances use different user accounts unique to each instance.

Which two statements are true about MySQL Enterprise Backup?. It supports backing up only table structures. It supports restoring to a remote MySQL system. It supports backup of a remote MySQL system. It can perform hot or warm backups. It creates logical backups. It supports the creation of incremental backups.

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

Your current system has users with accounts used for development on a MySQL Server host. The development team requests access without passwords, but this violates company policy. Which plugin would allow access without passwords while still remaining secure?. connection control plugin. PAM authentication plugin. native LDAP authentication plugin. MySQL firewall plugin. socket authentication plugin.

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

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

Which two MySQL Server accounts are locked by default?. any user created without a password. any user set as DEFINER for stored programs. any new ROLE accounts. any user created with a username, but missing the host name. any internal system accounts.

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 MAX USER CONNECTIONS 0;. ALTER USER baduser@hostname ACCOUNT LOCK;. ALTER USER baduser@hostname IDENTIFIED WITH mysql_no_login;.

Which two MySQL Shell commands are excluded from the InnoDB Cluster creation procedure?. dba.configureInstance(). cluster.setPrimaryInstance(). dba.configureLocalInstance(). cluster.forceQuorumUsingPartitionOf(). cluster.addInstance(). dba.createCluster(). dba.checkInstanceConfiguration().

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: The OS metrics indicate that disk is a bottleneck. Other variables retain their default values. Which two changes will provide the most benefit to the instance?. sync_binlog=0. max_connections=10000. innodb_log_file_size=1G. innodb_doublewrite=0. innodb_flush_log_at_trx_commit=1. buffer_pool_size=24G.

Which three are types of information stored in the MySQL data dictionary?. access control lists. server configuration rollback. InnoDB buffer pool LRU management data. server runtime configuration. stored procedure definitions. view definitions. performance metrics.

An InnoDB Cluster is 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 are two actions, either one of which, can fix this error and allow a successful restore of the cluster?. Restore using the --set-gtid-purged=OFF option. Remove the @@GLOBAL.gtid_purged statement from the dump file. Stop all instances except the primary read/write master instance and run the restore. Create the backup by using the --set-gtid-purged=OFF option. Remove the @@GLOBAL.gtid_executed statement from the dump file. Remove the group replication plugin from each instance before restoring.

Examine this statement and output: Which two statements are true?. Only records required for the result will be examined. The result will be found without reading full data rows. The result is a single row. The plan contains a full index scan of the CountryCode index. Three index records will be read.

The languages table uses InnoDB and the countries table uses the MyIS Both tables are empty. Examine these statements: BEGIN; INSERT INTO languages (lang) VALUES ("Italian"); INSERT INTO countries (country) VALUES ("Italy"); ROLLBACK; What is the content of both tables after executing the statements?. countries has one row, languages has none. both tables have one row. languages has one row, countries has none. both tables are empty.

Where is the default data directory located after installing MySQL using. /usr/mysql. /usr/bin. /etc/my.cnf. /var/lib/mysql. /usr.

You have an installation of MySQL 8 on Oracle Linux. Consider the outputs: Which statement is true about disk temporary tables for this installation?. 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. Temporary tables are created in tmpdir only after they reach tmp_table_size. Only internal temporary tables from the optimizer will be created in tmpdir.

Which two statements are true about raw binary backups?. They are required to obtain FIPS security compliance. The data format is identical to how MySQL stores the data on disk. They are converted to a highly compressible binary format. 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?. They allow direct copying of table rows with operating system copy commar. 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. They do not back up views, stored procedures, or configuration files. There is a slight performance cost while the snapshot is active. The backup window is almost zero from the perspective of the application.

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?. MySQL binaries could be damaged, deleted, or altered. Extra startup time would be required for the MySQL server to reset the privileges. Users could overwrite configuration files. SQL injections could be used to insert bad data into the database. Data files could be deleted.

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

A developer accidentally dropped the innodB table customers from the c There is a datadiz copy from two days ago in the dhbackup directory. Which set of steps would restore only the missing table?. Stop the MySQL Server process, and execute: mysqlbackup --datadir=/var/lib/mysql --backup-dir=/dbbackup --include-tables='Company\.Customers' copy-back Start the mysqld process. Stop the MySQL Server process and restart it with the command: mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysql Run 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=/dbbackup Run 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.

You need to reduce log space usage urgently. Which two alternate methods will do this?. Use SET GLOBAL binlog_expire_logs_seconds =<VALUE> and restart the server. Set binlog_expire_logs_seconds in my.cnf. SET PERSISTS binlog_expire_logs_seconds=<value>. USE PURGE BINARY LOGS to <binlog_name>. Use SET GLOBAL binlog_expire_logs_seconds=<value> and run FLUSH BINARY LOGS.

Replication for master and slave MySQL servers is running. Disk space occupied by binary log files continues to grow. Which two methods fix this problem?. Execute the PURGE BINARY LOGS statement. Execute the FLUSH LOGS statement. Set the binlog_expire_logs_seconds system variable. On the master server, disable binary logging by removing the --log-bin option. Delete all binary log files manually on the file system to release storage space.

Which two are requirements for multiple MySQL servers started by systemd?. Each must have a unique server-uuid configured. Each must have a unique data directory. Each must have a unique socket file and TCP/IP port. Each must have unique passwords. Each require separate server-id numbers.

Which two tasks are performed by the mysql_secure_installation pro. checking whether the hash value on downloaded software from MySC official count. setting file permissions and file ownership for MySQL server files pro. removing anonymous accounts. downloading the latest MySQL software over a secure connection and. setting a password for the root account.

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.

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.

Examine this command and output: mysql> SELECT * FROM performance_schema.table_io_waits_summary_by_table WHERE COUNT_STAR >0/G *************************** 2. row *************************** MAX TIMER WAIT: 558852005358 COUNT READ: 38665065 SUM TIMER READ: 20598719962188 MIN TIMER READ: 395922 AVG TIMER READ: 532728 MAX TIMER READ: 558852005358 COUNT WRITE: 22902028 SUM TIMER WRITE: 38410287610734 MIN TIMER WRITE: 1130688 AVG TIMER WRITE: 1677006 MAX TIMER WRITE: 17205682920 COUNT FETCH: 38665065 SUM TIMER FETCH: 20598719962188 MIN TIMER FETCH: 395922 AVG TIMER FETCH: 532728 MAX TIMER FETCH: 558852005358 ... COUNT DELETE: 22902028 SUM TIMER DELETE: 38410287610734 MIN TIMER DELETE: 1130688 AVG TIMER DELETE: 1677006 MAX TIMER DELETE: 17205682920 Which two are true?. 22902028 rows were deleted. The longest I/O wait was for writes. The I/O average time is 532728. Average read times are approximately three times faster than writes. I/O distribution is approximately 50/50 read/write.

Which three statements are true about MySQL replication?. 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 command and output: mysql> SELECT 1; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. Which option will fix this problem?. ALTER USER USER () IDENTIFIED BY 'password';. ALTER USER USER() EXPIRE PASSWORD DEFAULT;. ALTER USER USER () REQUIRE CURRENT DEFAULT;. ALTER USER USER() EXPIRE PASSWORD NEVER;.

Examine these settings for the mysqld instance 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. Now examine this command which is invoked: 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.

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

Examine these statements and output: mysql> GRANT PROXY ON accounting@localhost TO ''@'%'; mysql> SELECT USER(), CURRENT_USER(), @proxy_user; +-------------------+----------------------+---------------------+ | USER() | CURRENT_USER() | @proxy_user | +-------------------+----------------------+---------------------+ | rsmith@localhost | accounting@localhost | ''@'%' | +-------------------+----------------------+---------------------+ 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 ''@'%'.