1z0-908 MySQL 8.0 Database Administrator Questions and Answers

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.

They are backups taken from snapshots of a running database.

They are backups taken from OS copy commands.

They are good to use if only data structures must be backed up but not log files.

They are good to use when many users are online accessing the database.

It is not compatible with auto-positioning.

It needs to be re-instanced after a crash to maintain consistency.

It uses only time-based replication conflict resolution.

It relies on relay_log_recovery for resilient operations.

It does not attempt to detect or resolve replication conflicts.

It must use GTID replication.

performance metrics

table definitions

access control lists

view definitions

server runtime configuration

server configuration rollback

stored procedure definitions

The copy-back process is used to overwrite a new backup over an existing backup.

It restores files from the data directory to their original MySQL server locations.

It restores files from the backup directory to their original MySQL server locations.

The copy-back process makes inconsistent backups.

PREFERRED

VERIFY_CA

REQUIRED

VERIFY_IDENTITY

/usr/mysql

/usr/bin

/etc/my.cnf

/var/lib/mysql

/usr

mysqlbinlog --stop-position=500324 binlog.000036 | mysql

mysqlbinlog --stop-datetime='2019-ll-20 14:55:18' binlog.000036 | mysql

mysqlbinlog --stop-position=5004S3 binlog.000036 | mysql

mysqlbinlog --stop-datetime='2019-ll-20 14:55:16' binlog.000036 | mysql

Multiple binary logs can be used to restore data.

Multiple binary logs can be applied in parallel for faster data restoration.

Binary logs are relatively small, and therefore, excellent for long-term storage and disaster recovery.

Binary logs can always be used to unapply unwanted schema changes.

They allow for point-in-time recovery of the data.

It can be dropped using the DROP ROLE statement.

It is stored in the mysql.role table.

It is created as a locked account.

It can be renamed using the RENAME ROLE statement.

It can be granted to user accounts.

It can be protected with a password.

ALTER TABLE employees -

ADD INDEX (birth_date DESC);

ALTER TABLE employees -

ADD INDEX ((MONTH(birth_date)));

ALTER TABLE employees -

ADD COLUMN birth_month tinyint unsigned GENERATED ALWAYS AS (MONTH(birth_date)) VIRTUAL NOT NULL,

ADD INDEX (birth_month);

ALTER TABLE employees -

ADD COLUMN birth_month tinyint unsigned GENERATED ALWAYS AS (birth_date->>'$.month') VIRTUAL NOT NULL,

ADD INDEX (birth_month);

ALTER TABLE employees -

ADD INDEX ((CAST(birth_date->>'$.month' AS unsigned)));

ALTER TABLE employees -

ADD INDEX (birth_date);

SELECT user,passwd FROM members

WHERE user = ‘?’;INSERT INTO members(‘user’,’passwd’) VALUES

(‘bob@example.com’,‘secret’);--‘;

SELECT id, name FROM user WHERE user.id=(SELECT members.id FROM members);

SELECT id, name FROM user WHERE id=23 OR id=32 OR 1=1;

SELECT id, name FROM user WHERE id=23 OR id=32 AND 1=1;

SELECT email,passwd FROM members

WHERE email = ‘INSERT INTO members(‘email’,’passwd’) VALUES (‘bob@example.com’,

‘secret’);--‘;

SELECT user, phone FROM customers WHERE name = ‘\; DROP TABLE users; --‘;

The cluster will shut down to preserve data consistency.

The cluster has built-in high availability and updates group_replication_ip_whitelist to remove the unreachable nodes.

The group replication will buffer the transactions on the online nodes until the unreachable nodes return online.

There could be both a 2 node and 3 node group replication still running, so shutting down group replication and diagnosing the issue is recommended.

A manual intervention to force group members to be only the working two instances is required.

mysqlpump --include-tables=db.% --result-file=all_db_backup.sql

mysqlpump > all_db_backup.sql

mysqlpump --include-databases=db --result-file=all_db_backup.sql

mysqlpump --include-databases=db% --result-file=all_db_backup.sql

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.

[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

named pipes

shared memory

SOCKET

X Protocol

UDP

TCP/IP

any user set as DEFINER for stored programs

any internal system accounts

any new ROLE accounts

any user created without a password

any user created with a username, but missing the host name