1z0-908 Sample Questions Answers

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 USER statement

mysql_secure_installation utility

SET PASSWORD statement

mysqladmin client program

GRANT statement

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.

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

named pipes

shared memory

SOCKET

X Protocol

UDP

TCP/IP

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.

The instances use different user accounts unique to each instance.

The instances listen on different IP addresses.

The instances use different socket names.

The instances have appropriate net masks set.

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

performance metrics

InnoDB buffer pool LRU management data

access control lists

view definitions

server runtime configuration

server configuration rollback

stored procedure definitions

sort_buffer_size

key_buffer_size

table_open_cache

read_buffer_size

innodb_buffer_pool_size

tmp_table_size

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

They are converted to a highly compressible binary format.

The data format is identical to how MySQL stores the data on disk.

They are required to obtain FIPS security compliance.

The resulting files are easily human readable.

They are faster than logical backups because the process is a simple file or file system copy.

It monitors and schedules the rotation/deletion of the binary logs.

It reads the relay log and executes the events contained in them.

It acquires a lock on the binary log for reading each event to be sent to the slave.

It connects to the master and asks it to send updates recorded in its binary logs.

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

information_schema

world

employee

sys

mysql

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.

Increase the number of buffer pool instances to 16.

Increase the number of buffer pool instances to 32.

Decrease the number of buffer pool instances to 1.

Increase the number of buffer pool instances to 12.

Decrease the number of buffer pool instances to 4.

connects to the master and requests it to send updates recorded in its binary logs

monitors and schedules I/O calls to the subsystem for the relay logs

acquires a lock on the binary log for reading each event to be sent to the slave

reads the relay log and executes the events contained in them

You use the MyISAM storage engine for most common tables.

You use the InnoDB storage engine and statements wait while data is inserted.

The Innodb Buffer pool is full.

Your table accesses wait for the operating system level flush.

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

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;

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.

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

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

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.

The user is logged in with --user=accounting as an option.

The user is authenticated as the anonymous proxy user ‘’@’%’.

The user is authorized as the accounting@localhost user.

The user is authorized as the rsmith@localhost user.

The user failed to define a username and the connecting username defaulted to ‘’@’%’.

The master is most probably too busy to transmit data and the slave needs to wait for more data.

One or more large tables do not have primary keys.

This value shows only I/O latency and is not indicative of the size of the transaction queue.

The master is producing a large volume of events in parallel but the slave is processing them serially.

The parallel slave threads are experiencing lock contention.

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.

MyISAM

NDB

MEMORY

ARCHIVE

InnoDB

mysqlbinlog --rewrite-db=’mydb1->mydb2’ | mysql

mysqlbinlog --datebase=mydb1 --database=mydb2 | mysql

mysqlbinlog --rewrite-db=’mydb1’ --rewrite-db=’mydb2’ | mysql

mysqlbinlog --read-from-remote-server --raw | sed ‘s/mydb1/mydb2/g’ | mysql

Executing SET GLOBAL innodb_row_format=COMPRESSED and then ALTER TABLE transactions

will free up disk space.

Executing ALTER TABLE transactions will enable you to free up disk space.

Truncating the sales and leads table will free up disk space.

Truncating the transactions table will free up the most disk space.

The transactions table was created with innodb_file_per_table=OFF.

REQUIRED

VERIFY_CA

VERIFY_IDENTITY

PREFERRED

DISABLED

It defines all innodb tablespace options relative to a starting parent directory.

It is not necessary because innodb_data_home_dir is already defined.

It allows scanning of other locations to discover more innodb tablespaces.

It moves all innodb tablespaces to the /innodb_extras directory to enable a new

innodb_data_home_dir to be defined.

It adds more temporary workspace in addition to the innodb_tmpdir location.

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.

RESET MASTER;

SET GLOBAL gtid_purged=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10167;

SET GLOBAL gtid_purged=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-2312,bbbbbbbb- bbbb-bbbb-bbbb-bbbbbbbbbbbb:1-9;

SET GLOBAL gtid_executed=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10167;

RESET SLAVE;

SET GLOBAL gtid_purged=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-3820;

SET GLOBAL gtid_executed=aaaaaaaa-aaaa-aaaa-aaaa—aaaaaaaaaaaa:1-10300;

RESET MASTER;

SET GLOBAL gtid_purged-aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-2312;

SET GLOBAL gtid_executed=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10167;

RESET SLAVE;

SET GLOBAL gtid_purged=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10167;

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.

The minimum value increases to 3000 and the maximum value increases to 4000.

Only the minimum connection value is increased to 3000.

The minimum connection value is changed to 2000.

An error is returned.

Use SET GLOBAL binlog_expire_logs_seconds= and restart the server.

Set binlog_expire_logs_seconds in my.cnf.

Set binlog_expire_logs_seconds = 0 in my.cnf and restart the server.

Use SET PERSIST binlog_expire_logs_seconds=.

Use PURGE BINARY LOGS to .

Use SET GLOBAL binlog_expire_logs_seconds= and run the FLUSH BINARY LOGS

command.