Study guide questions Flashcards
MySQL indexes are for what purpose (pick 2):
- There are some internally created schemas.
- Optimize queries and data manipulation operations
- Store data in a sorted order
- Optimize queries and data manipulation operations
- Store data in a sorted order
Why MySQL will use index (pick 3):
- If the column specified in the WHERE clause is indexed, MySQL can use the index to quickly locate the rows that match the condition.
- If the index does not significantly reduce the number of rows scanned, MySQL will use it an index.
- Indexes can be used to efficiently sort or group results, as specified in ORDER BY and GROUP BY clauses.
- Indexes on the columns used in join conditions can speed up the process of matching rows from different tables.
- If the column specified in the WHERE clause is indexed, MySQL can use the index to quickly locate the rows that match the condition.
- Indexes can be used to efficiently sort or group results, as specified in ORDER BY and GROUP BY clauses.
- Indexes on the columns used in join conditions can speed up the process of matching rows from different tables.
Why MySQL will NOT use index (pick 2):
- For small tables, the overhead of using an index might outweigh the benefits
- If used in join statements
- If the index column is not listed first in the select statement
- If the index does not significantly reduce the number of rows scanned (i.e., low selectivity), MySQL might opt not to use it.
- For small tables, the overhead of using an index might outweigh the benefits
- If the index does not significantly reduce the number of rows scanned (i.e., low selectivity), MySQL might opt not to use it.
Why MySQL will NOT use index (pick 2):
- Using functions or calculations on the indexed column (e.g., WHERE UPPER(column) = ‘value’) can prevent MySQL from using the index.
- Hint Overrides: Index usage can be overridden by query hints. For example, using IGNORE INDEX in the query can prevent MySQL from using a specific index.
- If the index does significantly reduce the number of rows scanned (i.e., low selectivity), MySQL might opt not to use it.
- If the select statement is perfect
- Using functions or calculations on the indexed column (e.g., WHERE UPPER(column) = ‘value’) can prevent MySQL from using the index.
- Hint Overrides: Index usage can be overridden by query hints. For example, using IGNORE INDEX in the query can prevent MySQL from using a specific index.
Join that returns all records from left table and matched records from right table
- Left Join (Outer Join)
- Right Join (Outer Join)
- Join (Inner Join)
- Full Join
Left Join (Outer Join)
Which rows will this statement return:
SELECT * FROM emp LEFT JOIN orders ON emp.id=orders.emp_id;
- All orders that do not have an employee:
- All employees and their registered orders:
- All orders and employees who have registered them:
- Employees who have not registered any orders:
All employees and their registered orders:
Which rows will this statement return:
SELECT * FROM emp LEFT JOIN orders ON emp.id=orders.emp_id WHERE orders.emp_id IS NULL
- All orders that do not have an employee:
- All employees and their registered orders:
- All orders and employees who have registered them:
- Employees who have not registered any orders:
Employees who have not registered any orders:
Which rows will this statement return:
SELECT * FROM emp RIGHT JOIN orders ON emp.id=orders.emp_id;
- All orders that do not have an employee:
- All employees and their registered orders:
- All orders and employees who have registered them:
- Employees who have not registered any orders:
All orders and employees who have registered them:
Which rows will this statement return:
SELECT * FROM emp RIGHT JOIN orders ON emp.id=orders.emp_id WHERE emp.id IS NULL
- All orders that do not have an employee:
- All employees and their registered orders:
- All orders and employees who have registered them:
- Employees who have not registered any orders:
All orders that do not have an employee:
What is true about the EXPLAIN statement (pick 2):
- MySQL statement keyword used to see information about joined tables order
- Returns all records from the left table and the matched records from the right table
- Returns all records from the right table and the matched records from the left table
- Includes information about how tables are joined and in which order.
- MySQL statement keyword used to see information about joined tables order
- Includes information about how tables are joined and in which order.
What is true about the EXPLAIN statement (pick 2):
- Works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.
- Returns all records from the left table and the matched records from the right table
- Returns all records from the right table and the matched records from the left table
- EXPLAIN shows how MySQL will process a statement
Works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.
EXPLAIN shows how MySQL will process a statement
Statement used to assign privileges to a MySQL user
GRANT ALL PRIVILEGES ON . TO ‘test’@’%’ WITH GRANT OPTION;
Statement used to start MySQL server without using privilege system
mysqld –skip-grant-tables
Privilege category for SYSTEM_USER and FIREWALL_ADMIN
- Dynamic Privileges
- User Privileges
- System Privileges
- Plugin Privileges
Dynamic Privileges
MySQL user account elements (pick 2)
- privileges
- username@host ” identifier
- password
- hostname
- username@host ” identifier
- password
Statement to assign password to MySQL user account (pick 2):
CREATE USER ‘ jeffrey ‘@’localhost’ IDENTIFIED BY ‘password_value1’;
SET PASSWORD = ‘ mypass’;
Alter ‘password’ from ‘ jeffrey ‘@’localhost’ = ‘new_password’:
CREATE USER ‘ jeffrey ‘@’localhost’ IDENTIFIED BY ‘password_value1’;
SET PASSWORD = ‘ mypass’;
Statement to assign password to MySQL user account (pick 2):
Alter ‘password’ from ‘ jeffrey ‘@’localhost’ = ‘new_password’:
SET PASSWORD FOR jeffrey’@’localhost’ = ‘password_value2’;
CREATE fred@localhost IDENTIFIED WITH mysql_native_password BY ‘HisPa55w0rd!’;
SET PASSWORD FOR jeffrey’@’localhost’ = ‘password_value2’;
CREATE fred@localhost IDENTIFIED WITH mysql_native_password BY ‘HisPa55w0rd!’;
Statement to create one or more roles in MySQL
CREATE ROLE ‘role1’, ‘role2’, ‘role3’
MySQL Enterprise Authentication characteristics (pick 3)
- Integrates with Centralized Authentication Infrastructure
- Pluggable Authentication Modules (PAM)
- FIDO interface (passwordless authentication)
- Open source Authentication
- Integrates with Centralized Authentication Infrastructure
- Pluggable Authentication Modules (PAM)
- FIDO interface (passwordless authentication)
MySQL Enterprise Authentication characteristics (pick 3)
- Linux PAM Standard interface (Unix, LDAP, Kerberos, others)
- Plugin is available to access native LDAP service for authentication.
- Plugin is available to access native Windows service.
- Open source Authentication
- Linux PAM Standard interface (Unix, LDAP, Kerberos, others)
- Plugin is available to access native LDAP service for authentication.
- Plugin is available to access native Windows service.
MySQL Enterprise Masking functions that masks SSN?
mask_ssn(‘302-99-8097’);
mask(‘302-99-8097’);
dataMask_ssn(‘302-99-8097’);
mask_ssn(‘302-99-8097’);
Data Masking ‒ String ‒ Dictionary based ‒ Specific – Social Security Number – Payment card (strict/relaxed)
MySQL Enterprise Masking functions that masks payment card?
DataMask_pam(‘3002-9911-8097’);
mask(‘3002-9911-8097’);
mask_pam(‘3002-9911-8097’);
mask_pam(‘3002-9911-8097’);
Data Masking ‒ String ‒ Dictionary based ‒ Specific – Social Security Number – Payment card (strict/relaxed)
Why using the –initialize-insecure option to initialize the data directory insecure ?
- The root password is used on command line
- Has no root password generated with this option.
- Any 8 character password will work
Has no root password generated with this option.
This is an insecure option, it is assumed that you intend to assign a password to the account in a timely fashion before putting the server into production use.
List the mandatory MySQL variables (pick 2)
- basedir
- default_storage_engine
- datadir
- log_err
- basedir
- datadir
Data type used for a column to store large chunks of binary data such as images and audio files
- BLOB
- int
- binary_int
- varchar
BLOB
Data type to use for a column that contains only whole numbers (a number that is not a fraction)
- int
- Numeric Approximate Value
- num
- Numeric Exact Value
Numeric Exact Value
Int
Data type category of FLOAT and DOUBLE
- Numeric Approximate Value
- Numeric Exact Value
- Dynamic Value
- Miscellaneous Numeric Value
Numeric Approximate Value
Name the MySQL storage engine that is fully ACID compliant also the default when creating a table
- Memory
- MyISAM
- InnoDB
- Flexible
InnoDB
Write create table statement for mylist in sakila database (pick 1)
CREATE TABLE sakila.mylist (id INT, question INT, answer INT);
CREATE TABLE sakila.mylist INCLUDE (id INT, question INT, answer INT);
CREATE TABLE sakila.mylist VALUES (id INT, question INT, answer INT);
CREATE TABLE sakila.mylist (id INT, question INT, answer INT);
Another word for “schema” in MySQL
- project
- booklet
- database
- table
Schema and Database
Command used to disable autocommit mode for a single series of statements
- BEGIN
- START TRANSCATION
- ROLLBACK
- END
A “start transaction” statement temporarily disables autocommit (for DML).
True or false.
A transaction be rolled back if global AUTOCOMMIT = 1
False
You cannot use ROLLBACK to undo the effect; however, if an error occurs during statement execution, the statement is rolled back.
Characteristic of Enterprise Transparent Data Encryption (pick 2)
> Only the latest versions can encrypt tablespaces
> Data at Rest Encryption ‒ [System | General | Data Dictionary] Tablespaces, Undo/Redo, and Binary/Relay logs ‒ Strong encryption – AES 256
> Is transparent to applications and users ‒ No application code, schema, or data type changes
> Data at Rest Encryption ‒ [System | General | Data Dictionary] Tablespaces, Undo/Redo, and Binary/Relay logs ‒ Strong encryption – AES 256
> Is transparent to applications and users ‒ No application code, schema, or data type changes
Characteristic of Enterprise Transparent Data Encryption (pick 3)
> Is transparent to DBAs ‒ Keys are hidden from DBAs – no configuration changes.
> Does not require a master key
> Requires key management ‒ Protection, rotation, storage, recovery
> Protects against attacks on database files
> Is transparent to DBAs ‒ Keys are hidden from DBAs – no configuration changes.
> Requires key management ‒ Protection, rotation, storage, recovery ‒ Integrates with KMIP 1.1 and KMS
> Protects against attacks on database files
How MySQL Enterprise Firewall block SQL Injection attacks. (pick 2)
- Allow SQL Statements that match Whitelist/ Allow list.
‒ Block SQL statements that are not on Whitelist/ Allow List.
- Block SQL statements that contain keywords
- Allow SQL Statements that match Whitelist/ Allow list.
‒ Block SQL statements that are not on Whitelist/ Allow List.
MySQL Enterprise Security tool that allows DBAs to track user activities
- Audit Filtering
- MySQL Enterprise Firewall
- Automated Allow List
- Transparent Data Encryption (TDE)
- Audit Filtering (MySQL Enterprise Audit Workflow)
Characteristics of MySQL Enterprise Backup download (pick 2)
- Available as part of MySQL Enterprise Edition
- Trial release available for all versions
- Cross platform (Windows, Linux, Unix)
- Available as part of MySQL Enterprise Edition
- Cross platform (Windows, Linux, Unix)
Download from:
‒ http://edelivery.oracle.com (trial, only most recent release)
‒ http://support.oracle.com (all releases)
‒ And OTN for non-production us
Backup method allowing back up of only the data changed since the last FULL backup
- Incremental Backups
- Flexible Backups
- Snapshots
- Differential Backups
Differential Backups
MySQL Enterprise Backup keyword for restore (pick 2)
- copy-back
- mysqlbackup
- backup dir
- copy back and apply log
copy-back
copy back and apply log or
mysqldump and MySQL Shell backup types
- differential
- logical
- physical
- snapshot
Logical
Log file used to perform Point-in-time Recovery
- Binary Log
- Relay Log
- General Log
Binary Log
Choice for decreasing database back up time
- Physical Backup and Restore operations
- logical backup
- incremental backup
- differential backup
Physical Backup and Restore operations (MySQL Enterprise Backup)
MySQL replication default
- enabled
- synchronous
- asynchronous
- disabled
asynchronous
Advantages of MySQL replication (pick 2)
> Scale-out solutions for read-heavy environment
› Quorum consensus
› Failover is automatic
› Disaster recovery
> Scale-out solutions for read-heavy environment
- Scaling out doesn’t involve editing the
my.cnf file.
› Disaster recovery
Advantages of MySQL replication (pick 3)
› Quorum consensus
› Simple high availability (requires scripts and/or external tools)
› Analytics
› Long-distance data distribution
› Simple high availability (requires scripts and/or external tools)
› Analytics
› Long-distance data distribution
Characteristic of MySQL Replication (pick 2)
> Data is committed all at once.
› Replication is synchronous by default.
> Data is copied from source to replica.
› Replication is asynchronous by default.
> Data is copied from source to replica.
› Replication is asynchronous by default.
- Can be also semi-synchronous
Characteristic of MySQL Replication (pick 2)
› Multiple sources or replicas are possible.
› Additional replicas require
source configuration.
› Additional replicas don’t require
source configuration.
› At least 3 replicas are needed.
› Multiple sources or replicas are possible.
› Additional replicas don’t require
source configuration.
Command to connect replica to source and read updated records
- CHANGE REPLICATION SOURCE TO
- REPLICATION SOURCE = ‘source_ip’
- CHANGE BINLOGS TO
Configure replica server
- CHANGE REPLICATION
SOURCE TO
Replication topology that does NOT implement conflict detection or resolution
Source -Replica
Replica-Replica
Replica-Source
Source -Source
Source -Source
Minimum and maximum number of MySQL Servers in an InnoDB Cluster
- there is no min or max
- min 3, max 9
- min 2, max 9
- min 3, max 21
min 3, max 9
Function of InnoDB Cluster (pick 2)
> PIT Recovery
> Provides virtually synchronous replication with consistent reads
> Conflict detection and resolution
> Provides virtually synchronous replication with consistent reads
> Automates operations
‒ Conflict detection and resolution
Function of InnoDB Cluster (pick 2)
‒ Failure detection, failover, recovery
‒ Group membership management and creation
‒ Supports JavaScript, Python, and SQL
> Automates operations
‒ Failure detection, failover, recovery
‒ Group membership management and creation
Languages supported by MySQL Shell (pick 3)
- JavaScript
- Cobalt
- Python
- SQL
JavaScript, Python,
and SQL
Which component of MySQL InnoDB Cluster is a user interface that is used to achieve database high availability?
> MySQL Shell
MySQL Router
MySQL Group Replication
> MySQL Shell: User interface
Which component of MySQL InnoDB Cluster that is used to achieve database high availability and directs queries to the correct server?
> MySQL Shell
MySQL Router
MySQL Group Replication
> MySQL Router: Directs queries to
the correct server
Which component of MySQL InnoDB Cluster that is used to achieve database high availability and manages the continuity of service?
> MySQL Shell
MySQL Router
MySQL Group Replication
> MySQL Group Replication:
Manages the continuity of service
- Way to replicate between two InnoDB Clusters
MySQL InnoDB ClusterSet
Replication between InnoDB Cluster
- Schema with easy-to-understand views that contain information about IO hot spots, locking, and costly SQL statements
SYS Schema
- Schema that provides runtime statistics to monitor MySQL server execution at a low level
Performance Schema
55.Monitoring tool included with MySQL Enterprise Edition
Oracle Enterprise Manager for Monitoring MySQL
- Public clouds that offer HeatWave
Public clouds that offer HeatWave
Oracle Cloud
AWS
Azure
- MySQL edition HeatWave is based on
Powerful union of Oracle Cloud Infrastructure and MySQL Enterprise Edition
- Characteristics of HeatWave
Use cases:
‒ OLTP only
‒ Analytics
‒ Query accelerator (OLTP+OLAP)
‒ Machine Learning
‒ LakeHouse (direct access to Object Store)
‒ Generative AI
- MySQL Autopilot features that are helpful during System setup
Auto provision auto shape prediction
- Actions that can be performed in HeatWave cloud console without any SQL coding
Fully managed MySQL Service and very easy to use through HeatWave cloud console
Deploy, Backup and Restore, High Availability (RPO=0), Resize, Read Replicas, etc.
True or False:
MySQL Enterprise Backup cannot be used to perform upgrades
True
