1z0-908 Questions and Answers

Only internal temporary tables from the optimizer will be created in tmpdir.

Temporary tables will use the InnoDB temporary tablespace located in datadir.

Temporary tables are created in tmpdir only if configured to use MyISAM.

Temporary tables are created in tmpdir only after they reach tmp_tabie_size.

Temporary tables will use the InnoDB temporary tablespace located in /tmp.

Run MySQL Server docker containers.

Use systemd with different settings for each instance.

Use system tools to lock each instance to its own CPU.

Start mysqld or mysqld_safe using different option files for each instance.

Run mysqld with --datadir defined for each instance.

Use resource groups to lock different instances on separate CPUs.

All databases are backed up to the output file.

All non-active binary logs are removed from the master.

All binary logs are deleted from the master.

All binary logs are backed up and then deleted.

All databases, excluding master metadata, are backed up to the output file.

All details regarding deleted logs and master metadata are captured in the output file.

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.

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);

redo tablespaces

encryption tablespaces

schema tablespaces

data tablespaces

undo tablespaces

temporary table tablespaces

Users could overwrite configuration files.

Data files could be deleted.

SQL injections could be used to insert bad data into the database.

MySQL binaries could be damaged, deleted, or altered.

Extra startup time would be required for the MySQL server to reset the privileges.

The statement was blocked by MySQL Firewall.

John has not activated the role.

John needs to reconnect to the database.

The DBA needs to execute FLUSH PRIVILEGES.

MySQL Replication monitoring

network-related information and network characteristics

MySQL Query Analysis data

CPU utilization

security-related advisor warnings

operating system memory utilization

disk usage and disk characteristics including disk advisors warnings

recording the order in which queries are issued

audit of all queries

point-in-time recovery

replication

logging the duration and locks for all queries

Replication can use only TCP/IP connections.

Any instance can have multiple slaves, but it can have only one master.

Each instance in a replication topology must have a unique server ID.

Binary logs contain only transactions originating from a single MySQL instance.

Each slave must have its own MySQL user for replication.

Binary logging must be enabled on the master in order to replicate to other instances.

A replication user must have the SELECT privilege for all tables that need to be replicated.

Set validate-user-plugins=caching_sha2_password in the configuration file.

Define CREATE USER ''@'%' IDENTIFIED WITH sha256_password in the MySQL instance.

Add default_authentication_plugin=mysql_native_password in the configuration file.

Add default_authentication_plugin=sha256_password in the configuration file.

Stop all instances except the primary read/write master instance and run the restore.

Remove the @@GLOBAL.gtid_purged statement from the dump file.

Create the backup by using the --set-gtid-purged=OFF option.

Remove the group replication plugin from each instance before restoring.

Remove the @@GLOBAL.gtid_executed statement from the dump file.

Restore using the --set-gtid-purged=OFF option.

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

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.

Tables are scanned and index statistics recalculated when an instance is restarted.

Group Replication will be dissolved and all metadata purged.

Group Replication is still operational, but InnoDB Cluster must be reimported under MySQL Shell.

The mysql_innodb_cluster_metadata schema is dropped from the instance where the connection was established.

The command drops the mysql_innodb_cluster_metadata schema and re-creates it.

The mysql_innodb_cluster_metadata schema is dropped from all reachable members of the cluster.

Connections driven by MySQL Router are not affected by the command.