1z0-908 MySQL 8.0 Database Administrator Questions and Answers

[mysqld]

default_authentication_plugin=sha256_password

ALTER USER user -

IDENTIFIED WITH mysql_native_password

BY 'password';

[mysqld]

default_authentication_plugin=caching_sha2_password

ALTER USER user -

IDENTIFIED WITH caching_sha2_password

BY 'password';

ALTER USER user -

IDENTIFIED WITH sha256_password -

BY 'password';

[mysqld]

default_authentication_plugin=mysql_native_password

recording incoming SQL statement to facilitate the creation of a whitelist of permitted commands

blocking of potential threats by configuring pre-approved whitelists

modifying SQL statement dynamically with substitutions

automatic locking of user accounts who break your firewall

provides stateless firewall access to TCP/3306

ALTER TABLE t1 ADD ENCRYPTED_TABLESPACE = 'Y';

ALTER TABLE t1 ENCRYPTION='Y';

ALTER TABLE t1 WITH ENCRYPTION USING MASTER KEY;

ALTER TABLE t1 SET TDE = 'ON';

slave updates logging

semi-sync replication plugin

primary key or primary key equivalent on every table

binary log checksum

binary log ROW format

binary log MIXED format

replication filters

The country table is accessed as the first table, and then joined to the city table.

It takes more than 8 milliseconds to sort the rows.

The optimizer estimates that 51 rows in the country table have Continent = ‘Asia’.

35 rows from the city table are included in the result.

The query returns exactly 125 rows.

You can provide the root password interactively.

You must manually initialize the data directory.

The MySQL RPM package installation supports deploying multiple MySQL versions on the same host.

MySQL uses the RPM relocatable installation target feature.

The functionality is split among several RPM package files.

You can find the root password in the error log after the first start.

Updating index statistics is an I/O expensive operation.

Index statistics are calculated from pages buffered in the buffer pool for tables with InnoDB storage engine.

Setting innodb_stats_auto_recalc=ON causes statistics to be updated automatically when a new index is created.

Execution plans based on transient index statistics improve precision when innodb_stats_persistent_sample_pages is increased.

Increasing innodb_stats_persistent_sample_pages determines higher pages scanning speed, at the cost of increased memory usage.

performance metrics

table definitions

access control lists

view definitions

server runtime configuration

server configuration rollback

stored procedure definitions

ibbackup files

*.CSM files

*.sdi files

*.ibd files

ib_logfile* files

They are pushed from the master to the slave.

They contain events that describe database changes on the master.

They contain events that describe all queries run on the master.

They contain events that describe only administrative commands run on the master.

They are pulled from the master to the slave.

The instance deployed on host3 must be rebuilt with a backup from the primary instance.

The cluster is running and there is at least one ONLINE instance.

The instance deployed on host3 must be synchronized from a donor deployed on host1 by using the command cluster.addInstance(‘host1:3377’).

It is possible to determine the most up-to-date instance by comparing different global transaction identifier (GTID) sets with GTID_SUBSET(set1,set2).

The active session instance is invalid and must be re-created by using the command shell.connect (‘host3:3377’).

Restrict the number of OS users that have access at the OS level.

Ensure appropriate file system privileges for OS users and groups.

Minimize the number of non-MySQL Server-related processes running on the server host.

Encrypt the file system to avoid needing exact file-system permissions.

Keep the entire software stack on one OS host.

Run MySQL server as the root user to prevent incorrect sudo settings.

$HOME/.my.cnf file

$MYSQL_HOME/my.cnf file

DATADIR/mysqld-auto.cnf file

$HOME/.mylogin.cnf file

$HOME/.mysql/auth/login file

/etc/my.cnf file

$HOME/.mysqlrc file

TCP

SOCKET

PIPE

DIRECT

IPv6

FILE

IPv4

The redo logs are backed up first, then the transaction and archive tables.

The redo logs are backed up only if there are changes showing for the transactions tables.

The redo logs are not backed up at all.

The archive tables are backed up first, then the transaction tables and redo logs.

The transaction tables are backed up first, then the archive tables and redo logs.

MyISAM

NDB

MEMORY

ARCHIVE

InnoDB

total memory used across all connections associated with the user on connection number 10

total memory used by the first 10 connections

total memory used by thread number 10

total memory used across all connections associated with the user on thread number 10

total memory used by connection number 10

total memory used by the first 10 threads

shell> mysqladmin --debug

shell> mysql --print-defaults

shell> mysqld --help --verbose

mysql> SHOW GLOBAL VARIABLES;