1z0-908 Practice Exam Questions with Answers MySQL 8.0 Database Administrator Certification

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.

Remove group read/write privileges from the private_key.pem file.

Remove world read privileges from the server-cert.pem certificate file.

Change the group ownership of the mysql directory to the mysql user group.

Remove world read privileges from the public_key.pem file.

Change the parent directory owner and group to mysql.

Remove the world read/execute privilege from the accounting directory.

ALTER USER baduser@hostname PASSWORD DISABLED;

ALTER USER baduser@hostname MAX_USER_CONNECTIONS 0;

ALTER USER baduser@hostname ACCOUNT LOCK;

ALTER USER baduser@hostname IDENTIFIED WITH mysql_no_login;

ALTER USER baduser@hostname DEFAULT ROLE NONE;

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

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

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.

named pipes

shared memory

SOCKET

X Protocol

UDP

TCP/IP

This file is for storing MySQL Server configuration options in ISON format.

This file is for logging purposes only and is never processed.

It is read and processed at the beginning of startup configuration.

It is read and processed at the end of startup configuration.

It is always updated with changes to system variables.

This file is for storing MySQL server_uuid values only.

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.

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

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.

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.

You can scale writes by creating a replicated mesh.

It guarantees near real-time replication between a master and a slave.

You can scale reads by adding multiple slaves.

MySQL Enterprise Backup will automatically back up from an available slave.

It allows backup to be done on the slave without impacting the master.

UPDATE world.country SET Name='all';

UPDATE world.country SET Name='one' LIMIT 1;

UPDATE world.country SET Name='new' WHERE Name='old';

UPDATE world.country SET Name=CONCAT('New ',Name);

UPDATE world.country SET Name='first' ORDER BY Name LIMIT

ALTER USER 'joe'@'%' ACCOUNT LOCK

ALTER USER 'joe'@'%' SET password='*invalid*'

REVOKE ALL PRIVILEGES ON *.* FROM 'joe'@'%'

ALTER USER 'joe'@'%' PASSWORD HISTORY 0

ALTER USER 'joe'@'%' IDENTIFIED BY '*invalid*' PASSWORD EXPIRE

REVOKE USAGE ON *.* FROM 'joe'@'%'

Take a physical backup of the MySQL system.

Use the Clone Plugin to copy the data to another MySQL system.

Take a logical backup of the MySQL system.

Take your backup from a slave of the MySQL system.