13.Skill Check Administering a Replication Topology

Which are functions of a slave SQL thread? (Choose two)

reading events from the binary log and sending them to the slave I/O thread

distributing relay log events between worker threads on a multithreaded slave

allowing transactions updating different databases to run in parallel on a slave

connecting with the master’s binlog dump thread

copying events from the master relay log to the slave’s relay log

Examine this parameter setting: SLAVE_PARALLEL_TYPE = DATABASE Which are true? (Choose two)

transactions that are part of the same binary log group commit on a master can to run in parallel on a slave

transactions that update different databases can run in parallel on a slave

master-slave data consistency is ensured when there are cross-database constraints

MASTER_AUTO_POSITION must be set to 1 for this setting to take effect

SLAVE_PARALLEL_WORKERS must be greater than zero for this setting to take effect

Which are required when troubleshooting a MySQL replication error? (Choose two)

Verifying that the slave I/O thread serializes all event updates in the relay log.

Verifying that the Seconds_Behind_Master column displayed by the SHOW SLAVE STATUS command is zero.

Reviewing the events in the master binary log file.

Reviewing the Last_IO_Error and Last_SQL_Error columns displayed by the SHOW SLAVE STATUS command.

Verifing that both master and slave have unique nonzero server ID values.

Querying the I/O thread status of a replication slave displays this status message: Waiting for the slave SQL thread to free enough relay log space What does this status signify?

The value of the relay_log_space_limit parameter is zero.

The combined size of the relay logs exceeds the value of relay_log_space_limit.

The I/O thread is executing a LOAD DATA statement and appending the data to a temporary file which will be processed by the slave.

The I/O thread is copying an event to the relay log for processing by the SQL thread.

Which replication table in a performance schema contains the status of the SQL thread in a multithreaded slave?

replication_applier_status

replication_applier_status_by_coordinator

replication_applier_configuration

replication_applier_status_by_worker

You used the SHOW SLAVE STATUS command on a slave and received this output: … Master_Log_File: server1-bin.000004 Read_Master_Log_Pos: 275 Relay_Log_File: server2-relay-bin.000011 Relay_Log_Pos: 453 Relay_Master_Log_File: server1-bin.000002 Exec_Master_Log_Pos: 1307 … What does this status signify? (Choose two)

The binary log was re-sent as the slave failed to acknowledge completion

Log events are being copied faster than they are being executed on the slave.

There is latency in the SQL thread rather than the I/O thread.

There is network latency when transferring events between master and slave.

The slave is still executing previous binary log events but has received another binary log from the master.

13.Skill Check Administering a Replication Topology

Which are functions of a slave SQL thread? (Choose two)

reading events from the binary log and sending them to the slave I/O thread

distributing relay log events between worker threads on a multithreaded slave

allowing transactions updating different databases to run in parallel on a slave

connecting with the master’s binlog dump thread

copying events from the master relay log to the slave’s relay log

答案: allowing transactions updating different databases to run in parallel on a slave

distributing relay log events between worker threads on a multithreaded slave

Examine this parameter setting: SLAVE_PARALLEL_TYPE = DATABASE Which are true? (Choose two)

transactions that are part of the same binary log group commit on a master can to run in parallel on a slave

transactions that update different databases can run in parallel on a slave

master-slave data consistency is ensured when there are cross-database constraints

MASTER_AUTO_POSITION must be set to 1 for this setting to take effect

SLAVE_PARALLEL_WORKERS must be greater than zero for this setting to take effect

答案: SLAVE_PARALLEL_WORKERS must be greater than zero for this setting to take effect transactions that update different databases can run in parallel on a slave

说明: Multithreaded Slaves • Use multithreaded slaves to reduce slave lag. • Set the slave_parallel_workers variable to a value greater than zero to create that number of worker threads. – With multiple worker threads, the slave SQL thread does not apply events directly, but delegates responsibility to worker threads. • Set the slave_parallel_type variable to specify the parallelization policy. – DATABASE (default): Transactions that update different databases are applied in parallel. – LOGICAL_CLOCK: Transactions that are part of the same binary log group commit on a master are applied in parallel on a slave. — The master server can configure the binary log to record commit timestamps or write sets using the binlog_transaction_dependency_tracking variable. — Set slave_preserve_commit_order variable to ON to preserve the commit order.

Which are required when troubleshooting a MySQL replication error? (Choose two)

Verifying that the slave I/O thread serializes all event updates in the relay log.

Verifying that the Seconds_Behind_Master column displayed by the SHOW SLAVE STATUS command is zero.

Reviewing the events in the master binary log file.

Reviewing the Last_IO_Error and Last_SQL_Error columns displayed by the SHOW SLAVE STATUS command.

Verifing that both master and slave have unique nonzero server ID values. 答案: Verifing that both master and slave have unique nonzero server ID values. Reviewing the Last_IO_Error and Last_SQL_Error columns displayed by the SHOW SLAVE STATUS command.

说明： Troubleshooting MySQL Replication • View the error log. – The error log can provide you with enough information to identify and correct problems in replication. • Issue a SHOW MASTER STATUS statement on the master. – Logging is enabled if the position value is non-zero. • Verify that both the master and slave have a unique non-zero server ID value. – The master and the slave must have different server IDs. • Issue a SHOW SLAVE STATUS command on the slave or query the replication tables in Performance Schema. – Slave_IO_Running and Slave_SQL_Running display Yes when the slave is functioning correctly. – Last_IO Error and Last

Querying the I/O thread status of a replication slave displays this status message: Waiting for the slave SQL thread to free enough relay log space What does this status signify?

The value of the relay_log_space_limit parameter is zero.

The combined size of the relay logs exceeds the value of relay_log_space_limit.

The I/O thread is executing a LOAD DATA statement and appending the data to a temporary file which will be processed by the slave.

The I/O thread is copying an event to the relay log for processing by the SQL thread.

答案:

The combined size of the relay logs exceeds the value of relay_log_space_limit.

说明： Replication Slave I/O Thread States • Reconnecting after a failed master event read – The thread is trying to reconnect to the master. – When the thread reconnects, the state becomes Waiting for master to send event. • Waiting for the slave SQL thread to free enough relay log space – The combined size of the relay logs exceeds the value of relay_log_space_limit. — Only if non-zero. A zero value means there is no limit imposed on the size of the relay logs. – The I/O thread is waiting until the SQL thread frees enough space in the relay logs by processing and then deleting their contents.

Which replication table in a performance schema contains the status of the SQL thread in a multithreaded slave?

replication_applier_status

replication_applier_status_by_coordinator

replication_applier_configuration

replication_applier_status_by_worker

答案:

replication_applier_status_by_coordinator

说明： Replication Tables in Performance Schema Table Name Contains replication_connection_configuration Configuration parameters for connecting to the master replication_connection_status Status of the current connection to the master replication_applier_configuration Configuration parameters for the transaction applier on the slave replication_applier_status Current status of the transaction applier on the slave replication_applier_status_by_coordinator Status of the coordinator thread in a multithreaded slave replication_applier_status_by_worker Status of applier thread in a single-threaded slave or worker threads in a multithreaded slave replication_applier_filters Information about the replication filters configured on specific replication channels replication_applier_global_filters Information about global replication filters (all channels) replication_group_members Network and status information for group members replication_group_member_stats Statistical information about group members and the transactions they participate in

You used the SHOW SLAVE STATUS command on a slave and received this output: … Master_Log_File: server1-bin.000004 Read_Master_Log_Pos: 275 Relay_Log_File: server2-relay-bin.000011 Relay_Log_Pos: 453 Relay_Master_Log_File: server1-bin.000002 Exec_Master_Log_Pos: 1307 … What does this status signify? (Choose two)

The binary log was re-sent as the slave failed to acknowledge completion

Log events are being copied faster than they are being executed on the slave.

There is latency in the SQL thread rather than the I/O thread.

There is network latency when transferring events between master and slave.

The slave is still executing previous binary log events but has received another binary log from the master.

答案:

Log events are being copied faster than they are being executed on the slave. There is latency in the SQL thread rather than the I/O thread.

12.Skill Check Configuring a Replication Topology

　Which are true for semisynchronous replication? (Choose two)

It improves query execution performance of the master and at least one slave.

It restricts masters and slaves to use only statement-based binary logging.

It ensures data integrity between the master and at least one slave.

If the master is configured as semisynchronous, then all slaves must be configured as semisynchronous.

After committing a transaction, the master blocks until at least one slave acknowledges the transaction.

　Which are advantages of MySQL replication? (Choose two)

Another server, either master or slave, can take over the duties of a crashed server to prevent application outages.

Applications can scale-out SQL queries to multiple servers to reduce the response time.

Clients can run queries on different servers simultaneously to prevent data conflicts.

Same-level slave servers can share updates in case of the master’s failure to enable data coherency.

When the master is down, a slave server can be configured to automatically become the master to prevent database inconsistencies.

Which are requirements for enabling multisource replication? (Choose two)

The slave must create a replication channel for each master.

The slave must use FILE-based repositories.

All masters and slaves must be in a star topology.

All masters and slaves must be configured to prevent data conflicts.

The slave must use TABLE-based repositories.

Which steps are required to configure Server2 as slave of Server1? (Choose two)

Execute this command on Server2: CHANGE MASTER TO -> MASTER_HOST=, -> MASTER_PORT=, -> MASTER_LOG_FILE=’server1-bin.000001′, -> MASTER_LOG_POS=;

Add these settings to the Server1 my.sql file: [mysqld] log-bin=server1-bin

Add these settings to the Server2 my.sql file: [mysqld] log-bin=server2-bin

Execute this command on Server1: CHANGE MASTER TO -> MASTER_HOST=, -> MASTER_PORT=, -> MASTER_LOG_FILE=’server2-bin.000001′, -> MASTER_LOG_POS=;

Create a user on Server2 and grant them the REPLICATION SLAVE permission.

Which statement uses the global transaction identifier (GTID) replication protocol?

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3313, MASTER_AUTO_POSITION=1;

CHANGE MASTER TO RELAY_LOG_FILE=’slave-relay-bin.006′, RELAY_LOG_POS=4025;

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3312, MASTER_LOG_FILE=’server2-bin.000001′, MASTER_LOG_POS=723074;

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3311, MASTER_LOG_FILE=’server1-bin.000001′, MASTER_LOG_POS=155;

Which parameter settings enable and enforce logging based on global transaction identifier (GTID) for all transactions? (Choose two)

gtid-mode=OFF_PERMISSIVE enforce-gtid-consistency=OFF

gtid-mode=OFF enforce-gtid-consistency=WARN

gtid-mode=ON enforce-gtid-consistency=ON

gtid-mode=ON_PERMISSIVE enforce-gtid-consistency=ON

gtid-mode=ON enforce-gtid-consistency=WARN

12.Skill Check Configuring a Replication Topology

　Which are true for semisynchronous replication? (Choose two)

It improves query execution performance of the master and at least one slave.

It restricts masters and slaves to use only statement-based binary logging.

It ensures data integrity between the master and at least one slave.

If the master is configured as semisynchronous, then all slaves must be configured as semisynchronous.

After committing a transaction, the master blocks until at least one slave acknowledges the transaction.

答案： It ensures data integrity between the master and at least one slave. After committing a transaction, the master blocks until at least one slave acknowledges the transaction.

说明： Advantages and Disadvantages of Semisynchronous Replication Semisynchronous replication: • Ensures data integrity • Results in reduced performance – The master waits for the slave to respond before committing the transaction. — The timeout period is controlled by the rpl_semi_sync_master_timeout variable value. The default is 10,000 ms (10 seconds). — If no response is received, the master still commits the transaction but reverts to asynchronous mode. – The extra time taken by each transaction includes: — The TCP/IP roundtrip to send the commit to the slave — The slave recording the commit in its relay log — The master waiting for the slave’s acknowledgment of that commit • Is suitable primarily for servers that are physically co-located, communicating over fast networks

　Which are advantages of MySQL replication? (Choose two)

Another server, either master or slave, can take over the duties of a crashed server to prevent application outages.

Applications can scale-out SQL queries to multiple servers to reduce the response time.

Clients can run queries on different servers simultaneously to prevent data conflicts.

Same-level slave servers can share updates in case of the master’s failure to enable data coherency.

When the master is down, a slave server can be configured to automatically become the master to prevent database inconsistencies.

答案： Another server, either master or slave, can take over the duties of a crashed server to prevent application outages.

Applications can scale-out SQL queries to multiple servers to reduce the response time.

Which are requirements for enabling multisource replication? (Choose two)

The slave must create a replication channel for each master.

The slave must use FILE-based repositories.

All masters and slaves must be in a star topology.

All masters and slaves must be configured to prevent data conflicts.

The slave must use TABLE-based repositories.

答案： The slave must create a replication channel for each master. The slave must use TABLE-based repositories.

说明： Backing Up from Multiple Sources to a Single Server • Multisource replication can be used to: – Back up multiple servers to a single server – Merge table shards – Consolidate data from multiple servers to a single server • The slave creates a replication channel for each master from which it receives transactions. – Use the CHANGE MASTER TO … FOR CHANNEL channelname syntax: • Replicate from all channels concurrently or start and stop individual channels: CHANGE MASTER TO … MASTER_LOG_FILE=’binlog.000006′, MASTER_LOG_POS=143 FOR CHANNEL ‘shard-1’; START SLAVE IO_THREAD FOR CHANNEL ‘shard-1’;

Configuring Multi-Source Replication for a Binary Log Based Master

1. Enable binary logging on the master by using –log-bin.
2. Create a replication user.
3. Note the current binary log file and position. – MASTER_LOG_FILE and MASTER_LOG_POSITION
4. Verify that the slave is using TABLE-based replication repositories.
5. Execute CHANGE MASTER TO… to add a new master to a channel by using a FOR CHANNEL clause. – Example: Add a new master with host name master1 using port 3451 to channel master-1. CHANGE MASTER TO MASTER_HOST=’master1′, MASTER_PORT=3451, MASTER_USER=’rpl’, MASTER_PASSWORD=’pass’, MASTER_LOG_FILE=’master1-bin.000003′, MASTER_LOG_POS=719, FOR CHANNEL ‘master-1’;

Which steps are required to configure Server2 as slave of Server1? (Choose two)

Execute this command on Server2: CHANGE MASTER TO -> MASTER_HOST=, -> MASTER_PORT=, -> MASTER_LOG_FILE=’server1-bin.000001′, -> MASTER_LOG_POS=;

Add these settings to the Server1 my.sql file: [mysqld] log-bin=server1-bin

Add these settings to the Server2 my.sql file: [mysqld] log-bin=server2-bin

Execute this command on Server1: CHANGE MASTER TO -> MASTER_HOST=, -> MASTER_PORT=, -> MASTER_LOG_FILE=’server2-bin.000001′, -> MASTER_LOG_POS=;

Create a user on Server2 and grant them the REPLICATION SLAVE permission.

答案： Execute this command on Server2: CHANGE MASTER TO -> MASTER_HOST=, -> MASTER_PORT=, -> MASTER_LOG_FILE=’server1-bin.000001′, -> MASTER_LOG_POS=;

Add these settings to the Server1 my.sql file: [mysqld] log-bin=server1-bin

Which statement uses the global transaction identifier (GTID) replication protocol?

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3313, MASTER_AUTO_POSITION=1;

CHANGE MASTER TO RELAY_LOG_FILE=’slave-relay-bin.006′, RELAY_LOG_POS=4025;

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3312, MASTER_LOG_FILE=’server2-bin.000001′, MASTER_LOG_POS=723074;

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3311, MASTER_LOG_FILE=’server1-bin.000001′, MASTER_LOG_POS=155;

答案： CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3313, MASTER_AUTO_POSITION=1;

说明：

Configuring Replication Slaves • Restore the backup from the master. – Verify that the gtid_purged variable is set if you are using GTIDs. • Issue a CHANGE MASTER TO statement on each slave with the: – Network location of the master — MASTER_HOST and MASTER_PORT values — Optionally, use MASTER_SSL and related options to encrypt network traffic between masters and slaves during replication. – Replication account username and password (with the REPLICATION SLAVE privilege) — MASTER_USER and MASTER_PASSWORD values – Binary log coordinates from which to start replicating (if you are not using GTIDs) — The MASTER_LOG_FILE and MASTER_LOG_POS values store the binary log position from which the slave starts replicating. — Specify MASTER_AUTO_POSITION=1 if you are using GTIDs.

Which parameter settings enable and enforce logging based on global transaction identifier (GTID) for all transactions? (Choose two)

gtid-mode=OFF_PERMISSIVE enforce-gtid-consistency=OFF

gtid-mode=OFF enforce-gtid-consistency=WARN

gtid-mode=ON enforce-gtid-consistency=ON

gtid-mode=ON_PERMISSIVE enforce-gtid-consistency=ON

gtid-mode=ON enforce-gtid-consistency=WARN

答案： gtid-mode=ON enforce-gtid-consistency=ON

gtid-mode=ON_PERMISSIVE enforce-gtid-consistency=ON

说明： Global Transaction Identifiers (GTIDs) Global Transaction Identifiers (GTIDs) uniquely identify each transaction in a replication topology. • Each GTID is of the form :. For example: • A GTID set contains a range of GTIDs: • Enable GTID mode with the following options: – gtid-mode=ON: Logs a unique GTID along with each transaction – enforce-gtid-consistency: Disallows events that cannot be logged in a transactionally safe way – log-slave-updates: Records replicated events to the slave’s binary log

GTID_MOD OFF :不产生GTID,Slave只接受不带GTID的事务 OFF_PERMISSIVE :不产生GTID,Slave即接受不带GTID的事务,也接受带GTID的事务 ON_PERMISSIVE :产生GTID,Slave即接受不带GTID的事务,也接受带GTID的事务 ON :产生GTID,Slave只能接受带GTID的事务。

11.Skill Check Performing Backups

How does the – – master-data option of the mysqldump command ensure data consistency during a database backup? (Choose two)

It starts a new binary log.

It acquires a global lock at the beginning of the backup operation.

It records the binlog position in the backup file.

It creates database and table structures but does not dump the data.

It locks tables during backup.

Which are features of the mysqlpump utility? (Choose three)

dumping InnoDB CREATE TABLESPACE statements

restoring databases using dumped data files

parallel processing of the database dump process

dumping user accounts as CREATE USER/GRANT statements

taking physical backups of databases

faster reloading of secondary indexes for InnoDB tables

Which command forces the current binary log to close and the next incremental binary log to open?

mysql> GRANT RELOAD ON . TO ‘backupuser’@’localhost’;

mysql> FLUSH LOGS;

mysql> PURGE BINARY LOGS TO ‘binlog.000048’;

mysql> FLUSH BINARY LOGS;

Which are features or capabilities of multisource replication? (Choose three)

consolidating data from multiple servers to a single server

preventing slaves from making backups

backing up files not requiring additional storage or processing on the master

merging table shards

adding processing load on the master

backing up multiple servers to a single server

Which privileges does a user need to execute the mysqldump command? (Choose three)

SELECT for dumped tables

CREATE on each of the dumped objects

TRIGGER for dumped triggers

SHOW VIEW for dumped views

RELOAD on all databases and tables

ALTER on the database

Which commands back up the “employees” database to a single SQL script? (Choose two)

# mysql -uemployee -p employees < /labs/firewall_training.sql

# mysqldump -uroot -p –tab=/backups employees

# mysqlpump -uroot -p –databases employees \ > /backups/pump/pump.sql

# mysqldump -uroot -p –single-transaction –master-data=2 \ employees > /backups/employees_full.sql

# mysqlimport -uroot -p emps2 /backups/dept_manager.txt

11.Skill Check Performing Backups

How does the – – master-data option of the mysqldump command ensure data consistency during a database backup? (Choose two)

It starts a new binary log.

It acquires a global lock at the beginning of the backup operation.

It records the binlog position in the backup file.

It creates database and table structures but does not dump the data.

It locks tables during backup.

答案：

It records the binlog position in the backup file.

It locks tables during backup.

说明: Ensuring Data Consistency with mysqldump Ensuring consistency: • –master-data option alone – Locks all tables during backup with FLUSH TABLES WITH READ LOCK – Records the binlog position as a CHANGE MASTER TO statement in the backup file — –master-data=2 records the position as a comment • –master-data and –single-transaction options used together – Does not lock tables – only InnoDB table consistency is guaranteed – Acquires a global lock at the beginning of the backup operation to obtain a consistent binary log position • –lock-all-tables – Satisfies consistency by locking all tables for the duration of the whole dump • –flush-logs – Starts a new binary log

Which are features of the mysqlpump utility? (Choose three)

dumping InnoDB CREATE TABLESPACE statements

restoring databases using dumped data files

parallel processing of the database dump process

dumping user accounts as CREATE USER/GRANT statements

taking physical backups of databases

faster reloading of secondary indexes for InnoDB tables

答案: parallel processing of the database dump process

dumping user accounts as CREATE USER/GRANT statements

faster reloading of secondary indexes for InnoDB tables

说明: mysqlpump mysqlpump is very similar to mysqldump, but with the following enhancements: • Provides better performance than mysqldump by extracting data in parallel threads – mysqlpump divides the dump process into several subtasks and then adds these subtasks to a multithreaded queue. – This queue is then processed by N threads (two by default). • Enables better control over which database objects to dump on each thread • Dumps users as CREATE USER/GRANT statements instead of as INSERTs into the mysql system database • Enables compressed output • Displays a progress indicator • Provides faster secondary index reloading for InnoDB tables

Which command forces the current binary log to close and the next incremental binary log to open?

mysql> GRANT RELOAD ON . TO ‘backupuser’@’localhost’;

mysql> FLUSH LOGS;

mysql> PURGE BINARY LOGS TO ‘binlog.000048’;

mysql> FLUSH BINARY LOGS;

答案： mysql> FLUSH LOGS;

Which are features or capabilities of multisource replication? (Choose three)

consolidating data from multiple servers to a single server

preventing slaves from making backups

backing up files not requiring additional storage or processing on the master

merging table shards

adding processing load on the master

backing up multiple servers to a single server

答案： consolidating data from multiple servers to a single server merging table shards backing up multiple servers to a single server

说明： Backing Up from Multiple Sources to a Single Server • Multisource replication can be used to: – Back up multiple servers to a single server – Merge table shards – Consolidate data from multiple servers to a single server • The slave creates a replication channel for each master from which it receives transactions. – Use the CHANGE MASTER TO … FOR CHANNEL channelname syntax: • Replicate from all channels concurrently or start and stop individual channels: CHANGE MASTER TO … MASTER_LOG_FILE=’binlog.000006′, MASTER_LOG_POS=143 FOR CHANNEL ‘shard-1’; START SLAVE IO_THREAD FOR CHANNEL ‘shard-1’;

Which privileges does a user need to execute the mysqldump command? (Choose three)

SELECT for dumped tables

CREATE on each of the dumped objects

TRIGGER for dumped triggers

SHOW VIEW for dumped views

RELOAD on all databases and tables

ALTER on the database

答案： SELECT for dumped tables

TRIGGER for dumped triggers

SHOW VIEW for dumped views

说明： Privileges Required for mysqldump You must have the following privileges to use mysqldump: • SELECT for dumped tables • SHOW VIEW for dumped views • TRIGGER for dumped triggers • LOCK TABLES (unless you use the –-single-transaction option) • Other options might require extra privileges. For example: – To use the –flush-logs or –master-data options, you must have the RELOAD privilege. – To create a tab-delimited output with the –tab option, you must have the FILE privilege. – To use the –routines option to back up stored functions and procedures, you must have the global SELECT privilege.

Which commands back up the “employees” database to a single SQL script? (Choose two)

# mysql -uemployee -p employees < /labs/firewall_training.sql

# mysqldump -uroot -p –tab=/backups employees

# mysqlpump -uroot -p –databases employees \ > /backups/pump/pump.sql

# mysqldump -uroot -p –single-transaction –master-data=2 \ employees > /backups/employees_full.sql

# mysqlimport -uroot -p emps2 /backups/dept_manager.txt

答案： # mysqlpump -uroot -p –databases employees \ > /backups/pump/pump.sql

# mysqldump -uroot -p –single-transaction –master-data=2 \ employees > /backups/employees_full.sql

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