10.Skill Check Choosing a Backup Strategy

　Which techniques segregate data file backups from MySQL server data files? (Choose three)

replication

OS copy commands

binary logging

MySQL enterprise backup

distributed replicated block device

snapshots

Which are true about warm backups? (Choose two)

Updates are not possible during these backups.

When backups occurs, the server is in an inaccessible mode.

They do not impact system performance.

They permit applications to only read data.

They lock data using multiversion concurrency control (MVCC).

Which of these use full and binary log backups? (Choose two)

daily/hourly supplemental backups containing live data

backups using mysqldump with the –where option

weekly conditional backups of large transactional tables containing only fixed or historical data

multiple backups of data modifications taken each hour to minimize exposure during each day

backups using mysqlbackup from a replication slave

Which methods are used for logical backups? (Choose three)

MySQL enterprise backup

the mysqldump utility

the mysqlpump utility

snapshots

OS copy commands

SQL statements

Which are true about logical backups? (Choose three)

They use a snapshot capability that is external to MySQL.

They can be restored to MySQL databases on machines with different architectures than the one where the backup was created.

They can be taken to back up both local and remote MySQL servers.

They require the MySQL server to be running during the backups.

They generate SQL statements that can be used to recreate the orignal databases and tables.

They can identify objects that can cause damage and skip them during the backup.

Which are advantages of the RAID mirroring process? (Choose two)

It backs up both local and remote MySQL servers

It can reload a database on another server, running a different architecture

It recovers data in the event of a hardware failure

It enables the creation of live backups

It creates a SQL script that can be executed on a MySQL server

10.Skill Check Choosing a Backup Strategy

　Which techniques segregate data file backups from MySQL server data files? (Choose three)

replication

OS copy commands

binary logging

MySQL enterprise backup

distributed replicated block device

snapshots

答案: replication distributed replicated block device snapshots

说明: Physical Backup Conditions • The server must not modify data files during backup. – If you copy the live data files, prevent writes to those files: — For InnoDB: MySQL server shutdown is required. — For MyISAM: Lock tables to allow reads but not changes. • You can also minimize the effect to MySQL and applications by using techniques that separate the data files being backed up from the MySQL server: – Snapshots – Replication – DRBD or other methods that copy the whole filesystem

Which are true about warm backups? (Choose two)

Updates are not possible during these backups.

When backups occurs, the server is in an inaccessible mode.

They do not impact system performance.

They permit applications to only read data.

They lock data using multiversion concurrency control (MVCC).

答案: They permit applications to only read data. Updates are not possible during these backups.

说明: Warm Backups • Take place while the data is being accessed – In most cases, the data cannot be modified while the backup is taking place. • Have the advantage of not having to completely lock out end users – However, the disadvantage of not being able to modify the data while the backup is taking place can make this type of backup not suitable for certain applications. • Might result in performance issues because you cannot modify data during the backup. – Updating users may be blocked for a long duration of time.

Which of these use full and binary log backups? (Choose two)

daily/hourly supplemental backups containing live data

backups using mysqldump with the –where option

weekly conditional backups of large transactional tables containing only fixed or historical data

multiple backups of data modifications taken each hour to minimize exposure during each day

backups using mysqlbackup from a replication slave

答案: multiple backups of data modifications taken each hour to minimize exposure during each day

backups using mysqlbackup from a replication slave

说明: More Complex Strategies Combine multiple backup techniques to create more complex strategies: • Example using full and binary log backups: – Nightly backups using mysqlbackup from a replication slave – Multiple binary log backups each hour to minimize exposure during each day • Example using partial backups: – Technique: — mysqldump with –where option — SELECT INTO OUTFILE – Weekly conditional backups of large transactional tables containing only fixed or historical data — Data that does not change, for example, fulfilled orders that have passed the “return by” date – Daily/hourly supplemental backups containing live data

Which methods are used for logical backups? (Choose three)

MySQL enterprise backup

the mysqldump utility

the mysqlpump utility

snapshots

OS copy commands

SQL statements

答案: the mysqldump utility the mysqlpump utility SQL statements

说明: Logical Backups Perform a complete data dump by using SQL statements, mysqldump or mysqlpump. • These data dumps are based on a specific point in time but are the slowest of all the backup techniques. • Advantage: – The process creates a SQL script that you can execute on a MySQL server or data files that you can import into database tables. – You can use the script to reload the database on another host, running a different architecture or different version of MySQL Server. • Disadvantage: – By default (and always for non-InnoDB tables), mysqldump and mysqlpump lock tables during the dump, which prevents users from modifying data during the backup.

Which are true about logical backups? (Choose three)

They use a snapshot capability that is external to MySQL.

They can be restored to MySQL databases on machines with different architectures than the one where the backup was created.

They can be taken to back up both local and remote MySQL servers.

They require the MySQL server to be running during the backups.

They generate SQL statements that can be used to recreate the orignal databases and tables.

They can identify objects that can cause damage and skip them during the backup.

答案: They can be taken to back up both local and remote MySQL servers.

They require the MySQL server to be running during the backups.

They generate SQL statements that can be used to recreate the orignal databases and tables.

Which are advantages of the RAID mirroring process? (Choose two)

It backs up both local and remote MySQL servers

It can reload a database on another server, running a different architecture

It recovers data in the event of a hardware failure

It enables the creation of live backups

It creates a SQL script that can be executed on a MySQL server

答案: It recovers data in the event of a hardware failure

It enables the creation of live backups

9.Skill Check Optimizing Query Performance

What does the FORCE command do? (Choose three)

analyzes and stores key distribution statistics of a table

reorganizes data evenly across data pages

reclaims file system space used by empty pages

provides the optimizer with extra information about data distribution

forces an index rebuild even though the SQL statement does not change the table structure

produces multiple output rows when analyzing complex queries

Which logs contain queries that the MySQL server executes? (Choose two)

the Slow query log

the General query log

the Binary log

the Relay log

the Audit log

Examine this command which executes successfully: mysql> EXPLAIN SELECT emp_no, title FROM titles \G It generates this output: id: 1 select_type: SIMPLE table: titles partitions: NULL type: eq_ref … Which type of comparison plan is used to access rows?

matching a primary or unique key against a constant at the start of a query

matching one or more referenced values for equality

matching rows in a range that the named index supports

matching a single referenced value for equality

Which storage engines support the mysqlcheck client program? (Choose three)

MEMORY

ARCHIVE

MyISAM

InnoDB

MERGE

BLACKHOLE

Which materially improves overall system performance? (Choose two)

A query that executes once per minute has its response time reduced from 20 ms to 15 ms.

A query that executes once per minute has its response time reduced from 2 seconds to 1 second.

A query that executes several thousand times per minute has its response time reduced from 50 ms to 40 ms.

A query that executes several thousand times per minute has its response time reduced from 40 ms to 25 ms.

A query that executes once per minute has its response time reduced from 2 seconds to 800 ms.

Which properties are true about Invisible Indexes? (Choose three)

They provide an approximation of data distribution in indexed columns.

They analyze and store key distribution statistics of a table.

They can test the effect of removing an index on performance without dropping the index.

They can be applied to primary keys.

They can be used to facilitate a staged rollout of changes caused by index removal.

They are updated when indexed columns in their associated table is modified by DML statements.

What does the EXPLAIN command do? (Choose two)

It generates a query execution plan.

It performs data modification in a query.

It examines SQL statements beginning with the SELECT, INSERT, REPLACE, UPDATE, and DELETE statements.

It enables efficient access to rows.

It returns data from tables.

9.Skill Check Optimizing Query Performance

What does the FORCE command do? (Choose three)

analyzes and stores key distribution statistics of a table

reorganizes data evenly across data pages

reclaims file system space used by empty pages

provides the optimizer with extra information about data distribution

forces an index rebuild even though the SQL statement does not change the table structure

produces multiple output rows when analyzing complex queries

答案： reorganizes data evenly across data pages reclaims file system space used by empty pages forces an index rebuild even though the SQL statement does not change the table structure

说明： Rebuilding Indexes • Use FORCE: – Forces a rebuild even though the statement does not change the table structure – Reorganizes data more evenly across data pages – Reclaims file system space used by empty pages • Rebuild full text indexes by using OPTIMIZE TABLE: – By default, OPTIMIZE TABLE rebuilds the entire table. – The innodb_optimize_fulltext_only option ensures that OPTIMIZE TABLE rebuilds only the full text index

Which logs contain queries that the MySQL server executes? (Choose two)

the Slow query log

the General query log

the Binary log

the Relay log

the Audit log

答案： the Slow query log the General query log

说明： Identifying Slow Queries • Identify the queries that the server executes: – Use the general query log and slow query log. • Identify the queries that take a long time: – Use the slow query log. – Prioritize these over outlier queries or queries that you execute infrequently. • Find queries that execute many times: – Regularly execute SHOW PROCESSLIST or query the Performance Schema threads table to see currently executing statements and their durations and to identify emerging patterns. – Use sys.statement_analysis to view normalized statements with aggregated statistics. – Use the slow query log with a low threshold to record most statements

Examine this command which executes successfully: mysql> EXPLAIN SELECT emp_no, title FROM titles \G It generates this output: id: 1 select_type: SIMPLE table: titles partitions: NULL type: eq_ref … Which type of comparison plan is used to access rows?

matching a primary or unique key against a constant at the start of a query

matching one or more referenced values for equality

matching rows in a range that the named index supports

matching a single referenced value for equality

答案： matching a single referenced value for equality

说明：

Common type Values The type column indicates the type of comparison used by the optimizer to access rows. • ALL: Full table scan • index: Full index scan • const: Matching a primary or unique key against a constant at the start of a query • eq_ref: Matching a single referenced value (identified by the ref column) for equality • ref: Matching one or more referenced values for equality • range: Matching rows in a range that the named index (key) supports

Which storage engines support the mysqlcheck client program? (Choose three)

MEMORY

ARCHIVE

MyISAM

InnoDB

MERGE

BLACKHOLE

答案： MyISAM InnoDB ARCHIVE

说明： mysqlcheck Client Program • Can be more convenient than issuing SQL statements • Works with InnoDB, MyISAM, and ARCHIVE tables • Three levels of checking: – Table specific – Database specific – All databases • Some mysqlcheck maintenance options: – –analyze: Performs ANALYZE TABLE – –check: Performs CHECK TABLE (default) – –optimize: Performs OPTIMIZE TABLE • Examples: mysqlcheck -uroot -p employees employees salaries mysqlcheck –login-path=admin –analyze –all-databases

Which materially improves overall system performance? (Choose two)

A query that executes once per minute has its response time reduced from 20 ms to 15 ms.

A query that executes once per minute has its response time reduced from 2 seconds to 1 second.

A query that executes several thousand times per minute has its response time reduced from 50 ms to 40 ms.

A query that executes several thousand times per minute has its response time reduced from 40 ms to 25 ms.

A query that executes once per minute has its response time reduced from 2 seconds to 800 ms.

答案： A query that executes several thousand times per minute has its response time reduced from 50 ms to 40 ms.

A query that executes several thousand times per minute has its response time reduced from 40 ms to 25 ms.

Which properties are true about Invisible Indexes? (Choose three)

They provide an approximation of data distribution in indexed columns.

They analyze and store key distribution statistics of a table.

They can test the effect of removing an index on performance without dropping the index.

They can be applied to primary keys.

They can be used to facilitate a staged rollout of changes caused by index removal.

They are updated when indexed columns in their associated table is modified by DML statements.

答案： They can test the effect of removing an index on performance without dropping the index. They can be used to facilitate a staged rollout of changes caused by index removal.

They are updated when indexed columns in their associated table is modified by DML statements.

说明： Invisible Indexes • Enable you to “hide” indexes from the optimizer • Test the effect of removing indexes on query performance — Without making destructive changes to drop the index — Avoids expensive operation to re-create the index if it is found to be required • Continue to be updated by the server when data is modified by DML statements • Cannot be applied to primary keys • Should be marked as INVISIBLE in CREATE TABLE, ALTER TABLE, or CREATE INDEX statements – Can be “unhidden” by using the VISIBLE keyword

What does the EXPLAIN command do? (Choose two)

It generates a query execution plan.

It performs data modification in a query.

It examines SQL statements beginning with the SELECT, INSERT, REPLACE, UPDATE, and DELETE statements.

It enables efficient access to rows.

It returns data from tables.

答案： It generates a query execution plan. It examines SQL statements beginning with the SELECT, INSERT, REPLACE, UPDATE, and DELETE statements.

说明： EXPLAIN Command • Generates a query execution plan

Skill Check Maintaining a Stable System

Which are true about MySQL behaviour when attempting to access a table for which an Exclusive (X) table-level lock is held? (Choose two)

It locks the table to allow shared row-level locking

it permits the transaction owning the lock to read and write rows

it prevents other transaction from locking any rows in the table.

it allows other transactions to acquire shared locks on the table’s rows

it allows other sessions only to read rows.

Which actions are included in scaling out a database server? (Choose two)

increasing the network bandwidth

adding more servers to the environment

adding more CPU, storage, or main memory resources

writing software (application or storage engine) to use multiple locations

increasing the processing capacity of any single node

Examine this statement which executes successfully: “#mysqladmin –uroot –p kill 14” What does 14 represent in the output?

waiting_trx_id

blocking_trx_id

blocking_pid

waiting_pid

Which are server-level data locks? (Choose two)

Table locks

Storage engine data locks

Row-level locks

Locks that apply to internal resources

Metadata locks

You observe poor system performance. Which commands will display currently executing queries? (Choose two)

SHOW GLOBAL STATUS\G

SHOW PROCESSLIST\G

SHOW MASTER STATUS\G

SELECT * FROM sys.session\G

SELECT * FROM performance_schema.session_status;

Which conditions force an update to the performance baseline? (Choose two)

changes in server connection credentials

increasing data volumes

changes in application usage patterns

server migration to a new subnet

changes in the exploratory configuration

Skill Check Maintaining a Stable System

Which are true about MySQL behaviour when attempting to access a table for which an Exclusive (X) table-level lock is held? (Choose two)

It locks the table to allow shared row-level locking

it permits the transaction owning the lock to read and write rows

it prevents other transaction from locking any rows in the table.

it allows other transactions to acquire shared locks on the table’s rows

it allows other sessions only to read rows.

答案： it permits the transaction owning the lock to read and write rows

it prevents other transaction from locking any rows in the table.

Which actions are included in scaling out a database server? (Choose two)

increasing the network bandwidth

adding more servers to the environment

adding more CPU, storage, or main memory resources

writing software (application or storage engine) to use multiple locations

increasing the processing capacity of any single node

答案： adding more servers to the environment writing software (application or storage engine) to use multiple locations

说明: Scaling Up and Scaling Out • Scaling up: – Add more CPU, storage, or main memory resources to increase the processing capacity of any single node. – In general, scaling up is less complicated than scaling out because of the difficulty in writing software that performs well in parallel. • Scaling out: – Add more servers to the environment to enable more parallel processing. – Software (application or storage engine) needs to be written to use multiple locations. – Examples: — Sharded database — Replication for analytics or backups — InnoDB Cluster — NDB storage engine in MySQL Cluster

Examine this statement which executes successfully: “#mysqladmin –uroot –p kill 14” What does 14 represent in the output?

waiting_trx_id

blocking_trx_id

blocking_pid

waiting_pid

答案： blocking_pid

Which are server-level data locks? (Choose two)

Table locks

Storage engine data locks

Row-level locks

Locks that apply to internal resources

Metadata locks

答案： Table locks Metadata locks

说明： How MySQL Locks Rows MySQL locks resources in the following ways: • Server-level data locks: – Table locks – Metadata locks • Storage engine data locks: – Row-level locks – Handled at the InnoDB layer • Mutexes: – Lower-level locks that apply to internal resources rather than to data — Examples: Log files, AUTO_INCREMENT counters, and InnoDB buffer pool mutexes – Used for synchronizing low-level code operations, ensuring that only one thread can access each resource at a time

You observe poor system performance. Which commands will display currently executing queries? (Choose two)

SHOW GLOBAL STATUS\G

SHOW PROCESSLIST\G

SHOW MASTER STATUS\G

SELECT * FROM sys.session\G

SELECT * FROM performance_schema.session_status;

答案： SHOW PROCESSLIST\G SELECT * FROM sys.session\G

说明: Establishing a Baseline • Define what is normal: – The baseline is something to compare against when you encounter a problem. – Over time, changes in the baseline provide you with useful information for capacity planning. • Record operating system metrics: filesystem, memory, and CPU usage – top, iostat, vmstat , sysstat , sar on Linux- or UNIX-based systems – Resource Monitor and Performance Monitor on Windows • Record MySQL status and configuration: – SHOW PROCESSLIST or sys.session to see the running processes – mysqladmin extended-status to see status variables — Use -iseconds –relative to record value deltas. • Profile application use-case response times: – Log in, search, create, read, update, and delete.

Which conditions force an update to the performance baseline? (Choose two)

changes in server connection credentials

increasing data volumes

changes in application usage patterns

server migration to a new subnet

changes in the exploratory configuration

答案： increasing data volumes changes in application usage patterns

说明： Measuring What You Manage • Establish a performance baseline to measure the system’s normal variable values. • After every configuration change, measure the variables again and compare against your baseline. – Hardware and software upgrades – Exploratory configuration changes – Changes in the infrastructure • Measure variables regularly to update the baseline. – Changes in application usage patterns – Data growth over time • Whenever you encounter a problem, compare values with the baseline. – When you precisely define a problem, the solution often becomes obvious obvious

Skill Check Securing MySQL

Examine this command and output: SHOW STATUS LIKE ‘Connection_control%’; | Variable_name | Value | | Connection_control_delay_generated | 7 | 1 row in set (#.## sec) Which is true?

MySQL server added a delay for failed connection attempts seven times.

The maximum possilbe added delay is seven milliseconds.

A seven millisecond delay is added for each consecutive connection failure.

Seven successive failures are permitted before adding a delay.

Which command displays the name of the file containing a server’s digital certificate?

mysql> SHOW GLOBAL VARIABLES LIKE ‘ssl_cipher’;

mysql> SHOW SESSION STATUS LIKE ‘Ssl_cipher%’\G

mysql> SHOW GLOBAL VARIABLES LIKE ‘ssl_%’;

mysql> SHOW SESSION STATUS LIKE ‘Ssl_version’;

Which command registers the appuser@apphost account for firewall training?

CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘PROTECTING’)

CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘OFF’)

CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘RECORDING’)

CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘RESET’)

Which statements are true about Brute Force attacks? (Choose two)

They are slow as they require lots of CPU.

They perform hashing operations on combinations of dictionary words and characters.

They match target password hashes against rainbow tables.

They perform hashing operations on the characters to find matching hashes.

They compare password hashes against the stored hashes in the MySQL database.

After firewall training is complete, which modes will make the statement digest persistent in the account’s whitelist cache? (Choose two)

RECORDING

PROTECTING

OFF

DETECTING

RESET

The -ssl-mode option in your configuration is VERIFY_CA. What does this do?(Choose two)

It establishes secure connections or fails if unable to do so.

It checks whether host names match the Common Name value in the server certificate.

It establishes secure connections if it can but if not then unsecure connections are eastablished.

It verifies server digital certificates with the Certificate Authority.

It verifies that server digital certificates match the MySQL server hosts.

Skill Check Securing MySQL

Examine this command and output: SHOW STATUS LIKE ‘Connection_control%’; | Variable_name | Value | | Connection_control_delay_generated | 7 | 1 row in set (#.## sec) Which is true?

MySQL server added a delay for failed connection attempts seven times.

The maximum possilbe added delay is seven milliseconds.

A seven millisecond delay is added for each consecutive connection failure.

Seven successive failures are permitted before adding a delay.

答案： MySQL server added a delay for failed connection attempts seven times.

说明：

• Inspects the value of the Connection_control_delay_generated status variable – Counts the number of times the server added a delay for a failed connection attempt – Example: • Considers installing the CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS plugin – Creates a table in the Information Schema to maintain more detailed information about failed connection attempts — The Connection-Control plugin populates the table.

Which command displays the name of the file containing a server’s digital certificate?

mysql> SHOW GLOBAL VARIABLES LIKE ‘ssl_cipher’;

mysql> SHOW SESSION STATUS LIKE ‘Ssl_cipher%’\G

mysql> SHOW GLOBAL VARIABLES LIKE ‘ssl_%’;

mysql> SHOW SESSION STATUS LIKE ‘Ssl_version’;

答案： mysql> SHOW GLOBAL VARIABLES LIKE ‘ssl_%’;

说明：

The following is an example displaying the current name of the file in the data directory that contains the list of trusted Certificate Authorities: mysql> SHOW GLOBAL VARIABLES LIKE ‘ssl_ca’; +—————+——–+ | Variable_name | Value | +—————+——–+ | ssl_ca | ca.pem | +—————+——–+ 1 row in set (0.00 sec)

Which command registers the appuser@apphost account for firewall training?

CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘PROTECTING’)

CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘OFF’)

CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘RECORDING’)

CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘RESET’) 答案：
CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘RECORDING’)

说明： Registering Accounts with the Firewall Register an account by setting its initial firewall mode. • The account name is in the full user@host format, stored as a single string. • To register an account that is not initially controlled by the firewall, set the mode to OFF. • To register an account for firewall training, set the initial mode to RECORDING. – If you set an initial mode of PROTECTING, the account cannot execute any statements because its whitelist is empty.

Which statements are true about Brute Force attacks? (Choose two)

They are slow as they require lots of CPU.

They perform hashing operations on combinations of dictionary words and characters.

They match target password hashes against rainbow tables.

They perform hashing operations on the characters to find matching hashes.

They compare password hashes against the stored hashes in the MySQL database.

答案： They are slow as they require lots of CPU. They perform hashing operations on the characters to find matching hashes.

说明： CHow Attackers Derive Passwords Attackers can derive plain text passwords from hashed passwords by using the following techniques: • Brute force algorithms perform the hashing algorithm on many combinations of characters to find matching hashes. – These attacks are very slow and require large amounts of computation. • Dictionary attacks perform hashing operations on combinations of dictionary words and other characters. – These are fast if the password is not secure. • Rainbow tables are made up of the first and last hashes in long chains of repeatedly hashed and reduced passwords. – When you run a target password hash through the same algorithm chain and find a match to the end of a stored chain, you can derive the password by replaying that chain

After firewall training is complete, which modes will make the statement digest persistent in the account’s whitelist cache? (Choose two)

RECORDING

PROTECTING

OFF

DETECTING

RESET

答案： OFF PROTECTING

说明： Training the Firewall • Register the account in RECORDING mode. • The firewall creates a normalized statement digest for each statement and places the digest in the account’s whitelist cache. • Switch the mode to PROTECTING or OFF when training is complete to persist the whitelist. – The firewall persists the cache when you change the account’s mode. – If you restart the mysqld process while in RECORDING mode, any changes to that account’s whitelist cache are lost. • Return to RECORDING mode to learn new statements if the application changes. – Changing mode from OFF or PROTECTING to RECORDING does not clear the account’s whitelist.

The -ssl-mode option in your configuration is VERIFY_CA. What does this do?(Choose two)

It establishes secure connections or fails if unable to do so.

It checks whether host names match the Common Name value in the server certificate.

It establishes secure connections if it can but if not then unsecure connections are eastablished.

It verifies server digital certificates with the Certificate Authority.

It verifies that server digital certificates match the MySQL server hosts.

答案： It establishes secure connections or fails if unable to do so. It verifies server digital certificates with the Certificate Authority.

说明： Setting Client Options for Secure Connections Use the –ssl-mode option, which accepts the following values: • PREFERRED: Establishes a secure connection if possible or falls back to an unsecure connection. This is the default if –ssl-mode is not specified. • DISABLED: Establishes an insecure connection • REQUIRED: Establishes a secure connection if possible or fails if unable to establish a secure connection • VERIFY_CA: As for REQUIRED, but also verifies the server digital certificate with the Certificate Authority • VERIFY_IDENTITY: As for VERIFY_CA, but also verifies that the server digital certificate matches the MySQL server host

6. Skill Check Managing MySQL Users

　You must disable automatic password expiration on the ‘erika’@’localhost’ account. Which command will do this?

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE NEVER;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE DEFAULT;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE INTERVAL 30 DAY;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE;

　You must grant SELECT privilege on the salaries table in the employees’ database to user jan@localhost? Which command will do this?

GRANT SELECT ON employees.salaries TO jan@localhost;

REVOKE GRANT OPTION ON employees.salaries FROM ‘jan’@’localhost’;

REVOKE GRANT OPTION ON salaries.employees FROM ‘jan’@’localhost’;

GRANT SELECT ON salaries.employees TO jan@localhost;

Which command activates roles at the session level?

SET ROLE ALL;

ALTER USER kari@localhost DEFAULT ROLE ALL;

SET DEFAULT ROLE ALL TO kari@localhost;

SET PERSIST activate_all_roles_on_login = ON;

　You plan to use the test authentication plug-in test_plugin_server. Which statements are true? (Choose two)

It authenticates against operating system users and groups.

It is intended for use during testing and development.

It only allows MySQL users to log in via a UNIX socket.

It sends a plain text password to the server.

It implements native and old password authentication.

Which command removes DDL privileges from a role?

REVOKE DELETE, INSERT, UPDATE ON world.* FROM r_dev;

REVOKE GRANT OPTION ON world.* FROM r_dev

REVOKE CREATE, DROP ON world.* FROM r_dev;

REVOKE r_updater FROM r_dev;

You are using the caching_sha2_password plug-in? Which statements are true (Choose two)

It prevents clients from hashing passwords.

It authenticates MySQL accounts against the operating system.

It sends plain text passwords to a server.

It is the default authentication plug-in in MySQL 8.0.

It implements SHA-256 authentication and uses server side caching for better performance.

===

6. Skill Check Managing MySQL Users

　You must disable automatic password expiration on the ‘erika’@’localhost’ account. Which command will do this?

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE NEVER;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE DEFAULT;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE INTERVAL 30 DAY;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE;

答案： ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE NEVER;

说明： Configuring Password Expiration • The default_password_lifetime global variable specifies the number of days after which passwords must be changed. – The default value is 0, which indicates that passwords do not expire. • Set per-account password lifetime with the PASSWORD EXPIRE clause of CREATE USER or ALTER USER: • Apply the default password lifetime to an account: • Disable automatic password expiration on an account: CREATE USER ‘consultant’@’laptop3’ IDENTIFIED BY ‘change%me’ PASSWORD EXPIRE INTERVAL 30 DAY; ALTER USER ‘consultant’@’laptop3’ PASSWORD EXPIRE NEVER;

　You must grant SELECT privilege on the salaries table in the employees’ database to user jan@localhost? Which command will do this?

GRANT SELECT ON employees.salaries TO jan@localhost;

REVOKE GRANT OPTION ON employees.salaries FROM ‘jan’@’localhost’;

REVOKE GRANT OPTION ON salaries.employees FROM ‘jan’@’localhost’;

GRANT SELECT ON salaries.employees TO jan@localhost;

答案： GRANT SELECT ON employees.salaries TO jan@localhost;

说明： GRANT Statement • The GRANT statement assigns privileges or roles to MySQL user accounts and roles. – Example GRANT syntax: • Statement clauses: – Privileges to be granted — Example: SELECT, UPDATE, DELETE – Privilege level: — Global: . — Database: db_name.* — Table: db_name.table_name — Stored routine: db_name.routine_name – Account or role to which you are granting the privilege

Which command activates roles at the session level?

SET ROLE ALL;

ALTER USER kari@localhost DEFAULT ROLE ALL;

SET DEFAULT ROLE ALL TO kari@localhost;

SET PERSIST activate_all_roles_on_login = ON;

答案： SET ROLE ALL;

说明: Activating Roles at Session Level • Use SET ROLE statement to modify the list of active roles in the current session. It accepts a list of roles or one of the following role specifiers: – DEFAULT: Activate the account default roles. – NONE: Disable all roles. – ALL: Activate all roles granted to the account. – ALL EXCEPT: Activate all roles granted to the account except those named. • Use CURRENT_ROLE() function to determine which roles are active in the current session. SET ROLE ALL; SET ROLE r_viewer, r_updater; SELECT CURENT_ROLE();

　You plan to use the test authentication plug-in test_plugin_server. Which statements are true? (Choose two)

It authenticates against operating system users and groups.

It is intended for use during testing and development.

It only allows MySQL users to log in via a UNIX socket.

It sends a plain text password to the server.

It implements native and old password authentication.

答案：

It implements native and old password authentication. It is intended for use during testing and development.

说明： Loadable Authentication Plugins • Test Authentication plugin (test_plugin_server): Implements native and old password authentication – This plugin uses the auth_test_plugin.so file and is intended for testing and development purposes. • Socket Peer-Credential (auth_socket): Allows only MySQL users who are logged in via a UNIX socket from a UNIX account with the same name – This plugin uses the auth_socket.so file.

Which command removes DDL privileges from a role?

REVOKE DELETE, INSERT, UPDATE ON world.* FROM r_dev;

REVOKE GRANT OPTION ON world.* FROM r_dev

REVOKE CREATE, DROP ON world.* FROM r_dev;

REVOKE r_updater FROM r_dev; 答案： REVOKE CREATE, DROP ON world.* FROM r_dev;

说明：

REVOKE: Examples • Assume that Amon has SELECT, DELETE, INSERT, and UPDATE privileges on the world database. – You want to change the account so that he has SELECT access only. • Revoke Jan’s ability to grant to other users any privileges that he holds for the world database, by revoking the GRANT OPTION privilege from his account. • Assume that the r_dev role has the CREATE, DROP, SELECT, DELETE, INSERT, and UPDATE privileges on the world database. – You want to remove all DDL privileges from the role. • Revoke r_updater role from Kari user account. REVOKE GRANT OPTION ON world. FROM ‘Jan’@’localhost’; REVOKE DELETE, INSERT, UPDATE ON world. FROM ‘Amon’@’localhost’; REVOKE CREATE, DROP ON world.* FROM r_dev; REVOKE r_updater FROM kari@localhost;

You are using the caching_sha2_password plug-in? Which statements are true (Choose two)

It prevents clients from hashing passwords.

It authenticates MySQL accounts against the operating system.

It sends plain text passwords to a server.

It is the default authentication plug-in in MySQL 8.0.

It implements SHA-256 authentication and uses server side caching for better performance.

答案： It is the default authentication plug-in in MySQL 8.0. It implements SHA-256 authentication and uses server side caching for better performance.

说明：

Pluggable Authentication • In MySQL 8.0, caching_sha2_password is the default authentication plugin. • During connection using the caching_sha2_password plugin, MySQL uses the following to authenticate an account: – Username – Password – Client host • When specifying host names, remember the proper perspective: – Specify the server’s host name when connecting using a client. – Specify the client’s host name when adding a user to the server