1z0-908 MySQL 8.0 Database Administrator Questions and Answers

ibtmp1

undo_001

ib_buffer_pool

ibdata1

mysql.ibd

ib_logfile0

If OPTIMIZE TABLE; is invoked, it will create a table lock on t and force a transaction rollback.

If OPTIMIZE LOCAL TABLE t; is invoked from another session, it executes normally and returns the status.

mysqlcheck --analyze --all-databases will execute normally on all tables and return a report.

If ANALYZE TABLE; is invoked from the same session, it hangs until the transaction is committed or rolled back.

ibdatal:12M;ibdata2:12M;ibdata3:12M

ibdatal:12M:autoextend;ibdata2:12M:autoextend

ibdatal:12M:autoextend

ibdatal:12M;ibdata2:12M:autoextend

ibdatal:12M

ibdatal:12M;/tmp/ibdata2:12M:autoextend

buying more RAM

monitoring OS resources for patterns

adding circular replication nodes for increased DML capability

buying more CPU

buying more disk

basing expected growth on an average of the last 3 years

consulting the application team about any future projects and use

The lock is at the metadata object level.

The lock is a shared lock.

The lock is an intentional lock.

The lock is at the table object level.

The lock is a row-level lock.

The lock is an exclusive lock.

MySQL TDE uses an appropriate keyring plugin to store the keys in a centralized location.

Both MyISAM and InnoDB tables can be encrypted by setting the keyring_engine = ALL variable in the MySQL configuration file.

Lost tablespace encryption keys can be regenerated only if the master database key is known or present in the Key Vault specification.

TDE can encrypt InnoDB and MyISAM tables only when the tables are stored in the SYSTEM tablespace.

MySQL server was not started due to a problem while executing process 2732.

MySQL server continued to start up even though another process existed.

systemd found the mysqld service disabled and failed to start it.

systemd waited for 30 seconds before timing out and start up failed.

systemd attempted to start mysqld, found another systemd mysqld process running, and shut it down.

shell> mysqladmin --debug

shell> mysql --print-defaults

shell> mysqld --help --verbose

mysql> SHOW GLOBAL VARIABLES;

Firewall_cached_entries is the number of statements found in the query cache for users in DETECTING mode.

Firewall_access_denied is the number of connection attempts from prohibited hosts that are denied.

Firewall_access_suspicious is the number of statements logged as suspicious for users in DETECTING mode.

Firewall_access_granted is the number of connections granted from whitelisted hosts.

Execute SET GLOBAL log_error = ‘’.

Execute SET GLOBAL max_error_count = .

Execute SET GLOBAL expire_logs_days=0 to enforce a log rotation.

Rename the error log file on disk, and then execute FLUSH ERROR LOGS.

It reconfigures InnoDB Cluster if the cluster was stopped.

It performs InnoDB Cluster instances rolling restart.

It only starts all InnoDB Cluster instances.

It is not mandatory that all instances are running and reachable before running the command.

It stops and restarts all InnoDB Cluster instances and initializes the metadata.

It only stops and restarts all InnoDB Cluster instances.

It picks the minimum number of instances necessary to rebuild the quorum and reconfigures InnoDB Cluster.

ALTER TABLE world.city ADD SPATIAL INDEX (Name);

ALTER TABLE world.city ADD SPATIAL INDEX (Population);

ALTER TABLE world.city ADD INDEX (Population);

ALTER TABLE world.city ADD INDEX (Name);

ALTER TABLE world.city ADD FULLTEXT INDEX (Name);

ALTER TABLE world.city ADD FULLTEXT INDEX (Population);

It manages the configuration of the MySQL Firewall feature.

It manages the configuration of client programs.

It can move datadir to a new location.

It manages the configuration of user privileges for accessing the server.

It will use [client] options by default unless you provide --login-path.

It can be used to create and edit SSL certificates and log locations.

It provides an interface to change my.cnf files.

installing and configuring the Connection Control plugin

avoiding concatenation of SQL statements and user-supplied values in an application

using stored procedures for any database access

using PREPARED STATEMENTS

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’).

[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

dba.configureInstance()

cluster.setPrimaryInstance()

dba.configureLocalInstance()

cluster.forceQuorumUsingPartitionOf()

cluster.addInstance()

dba.createCluster()

dba.checkInstanceConfiguration()

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.