Oracle

1Z0-908
MySQL 8.0 Database Administrator
QUESTION & ANSWERS

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
QUESTION: 1

Which statement is true about FLUSH LOGS command?

Option A : It requires the RELOAD, FILE, and DROP privileges.

Option B : It closes and reopens all log files.

Option C : It closes and sends binary log files to slave servers.

Option D : It flushes dirty pages in the buffer pool to the REDO logs.

Correct Answer: B

QUESTION: 2

On a master server that is using Statement-based replication a table of log data has become very large. You
decide to delete 100,000 rows.Which two methods can be independently invoked to ensure that the delete is
properly propagated to the slave? (Choose two.)

Option A : Use the LIMIT clause to limit the deletion to 100,000 rows.

Option B : Change the replication mode to MIXED before issuing any delete statements when the LIMIT
clause is used.

Option C : Use the LIMIT clause in conjunction with the ORDER BY clause.

Option D : Use the data modification is non-deterministic, the query optimizer will resolve any potential
issues.

Correct Answer: B,C

QUESTION: 3

When the backup is taken, there are no errors.Why are triggers missing from the backup?

Option A : The FILE privilege is required to back up the TRG files stored in the database directory.

Option B : The PROCESS privilege will allow the user access to process items such as triggers and
functions.

Option C : The ALL privilege is required to back up triggers because triggers have a wide variety of
abilities.

Option D : The TRIGGER privilege Is required to create, execute, and show triggers in a database.

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
Correct Answer: D

QUESTION: 4

You want to shutdown a running Mysql Server cleanly.Which three commands that are valid on either
Windows or Linux will achieve this?

Option A : Shell> pkill –u mysql mysqld_safe

Option B : Shell> service mysql safe_exit

Option C : Shell> /etc/init.d/mysql stop

Option D : Shell> mysqladmin –u root –p shutdown

Option E : Mysql> STOP PROCESS mysqld;

Option F : Shell> net stop mysql

Option G : Shell> nmc mysql shutdown

Correct Answer: A,C,D

QUESTION: 5

Which query would you use to find connections that are in the same state for longer than 100 seconds?

Option A : SHOW FULL PROCESSLIST WHEER Time > 100;

Option B : SELECT * FROM INFORMATION_SCHEMA.EVENTS SHERE STARTS < (DATE_SUB (NOW ( ),

INTERVAL 100 SECOND) );

Option C : SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 100;

Correct Answer: C

QUESTION: 6

Which three methods will show the storage engine for the Country table? (Choose three.)

Option A : SHOW CREATE TABLE Country;

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
Option B : SHOW ENGINE Country STATUS;

Option C : SHOW TABLE STATUS LIKE ‘Country’;

Option D : SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= ‘Country’;

Option E : SELECT ENGINE FROM INFORMATION_SCHEMA.ENGINES WHERE TABLE_NAME= ‘Country’;

Correct Answer: A,C,D

QUESTION: 7

You are attempting to secure a MySQL server by using SSL encryption.On starting MySQL, you get this
error:[ERROR] mysqld: unknown option ‘--ssl’What is the cause of the error?

Option A : The -- ssl level was not specified.

Option B : The server was not started with the --enable–ssl-plugin option.

Option C : --ssl is not a valid server option.

Option D : The mysqld binary was not compiled with SSL support.

Option E : The server’s SSL certificate was invalid.

Correct Answer: D

QUESTION: 8

You have a MySQL replication setup and you intentionally stop the SQL thread on the slave.mysql> SHOW
SLAVE STATUS\G... Slave_IO_Running: YesSlave_SQL_Running: NoWhat are two reasons that you may stop
the SQL thread on the slave while keeping the I/O thread running? (Choose two.)

Option A : to allow the remaining events to be processed on the slave while not receiving new events
from the master

Option B : to allow a backup to be created under reduced load

Option C : to allow for point-in-time recovery on the slave

Option D : to prevent schema changes from propagating to the slave before they are validated

Option E : to prevent any transaction experiencing a deadlock

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
Correct Answer: B,C

QUESTION: 9

Consider the three binary log files bin.00010, bin.00011, and bin.00012 from which you want to restore
data.Which method would use mysqlbinlog for greater consistency?

Option A : shell> mysqlbinlog bin.00010 | mysql shell> mysqlbinlog bin.00011 | mysql shell> mysqlbinlog
bin.00012 | mysql

Option B : shell> mysqlbinlog bin.00010 bin.00011 bin.00012 | mysql

Option C : shell> mysqlbinlog – restore bin.00010 bin.00011 bin.00012

Option D : shell> mysqlbinlog – include-gtide=ALL bin.00010 bin.00011 bin.00012 | mysql

Correct Answer: B

QUESTION: 10

You need to replicate a table from a master to a slave. The master and slave copies of the table will have
different number of columns.Which two conditions must be true? (Choose two.)

Option A : Each extra column in the copy with more columns must not have a default value.

Option B : Columns that are common to both versions of the table must be defined in the same order on
the master and the slave.

Option C : The slave database cannot have more columns than the master. Only the master database can
have more columns.

Option D : Columns that are common to both versions of the table must come first in the table definition,
before any additional columns are additional columns are defined on either server.

Option E : The master database cannot have more columns than the slave. Only the slave deatbase can
have more columns.

Correct Answer: B,D

QUESTION: 11

Which three statements are true about memory buffer allocation by a MySQL Server? (Choose three.)

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
Option A : Global buffers such as the InnoDB buffer pool are allocated after the server starts, and are
never freed.

Option B : Thread buffers are allocated when a client connects, and are freed when the client disconnects.

Option C : Buffers that are needed for certain operation are allocated when the operation starts, and freed
when it ends.

Option D : User buffers are allocated at server startup and freed when the user is dropped.

Option E : All dynamic buffers that are set with a SET GLOBAL statement immediately get allocated
globally, and are never freed.

Correct Answer: A,C

QUESTION: 12

You want to create a temporary table named OLD_INVENTORY in the OLD_INVENTORY database on the
master server. This table is not to be replicated to the slave server.Which two changes would ensure that the
temporary table does not propagate to the slave? (Choose two.)

Option A : Set binlog_format=MIXED with the –replicate-ignore-temp-table option.

Option B : Use the –replicate-do-db, –replicate-do-table, or –replicate-wild-do-table option with the value
equal to OLD_INVENTORY.

Option C : Change the binlog_format option to ROW and restart mysqld before you create the
OLD_INVENTORY table.

Option D : Stop SQL_THREAD on the slave until you have finished using the OLD_INVENTORY temporary
table.

Option E : Use the –replicate-ignore-table option with the value equal to

OLD_INVENTORY.OLD_INVENTORY and restart mysqld before creating the temporary table.

Correct Answer: A,C

QUESTION: 13

How can we start MySQL when it was installed using RPM in Oracle Enterprise Linux 7?

Option A : mysqld_safe --user=mysql &

Option B : systemctl start mysqld

Option C : service start mysql

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
Option D : mysqladmin -u root start

Correct Answer: B

QUESTION: 14

In a test database, you issue the SELECT … INTO OUTFLLE statement to create a file with your t1 table
data.You then TRUNCATE this table to empty it.mysql> SELECT * INTO OUTFILE ‘/tmp/t1.sql’ from t1;mysql>
TRUNCATE t1;Which two methods will restore data to the t1 table? (Choose two.)

Option A : mysql> LOAD DATA INFILE ‘/tmp/t1.sql’ INTO TABLE t1;

Option B : $ mysqladmin -u root -p -h locathost test –restore /tmp/t1.sql

Option C : $ mysql -u root -p -h localhost test < /tmp/t1.sql

Option D : $ mysqlimport -u root -p -h localhost test /tmp/t1.sgl

Option E : mysql> INSERT INTO t1 VALUES FRON ‘tmp/t1.sql’;

Correct Answer: A,D

QUESTION: 15

You adjust a default configuration to the following /etc/my.cnf on a Linux installation:[mysqld]Loq-

binBinrylog_format=ROWYou do not notice the spelling error in binrylog_format and restart your production
server.How does the MySQL server behave with incorrectly spelled options?

Option A : Mysqld uses internal configuration versioning and reverts to the previous configuration.

Option B : When using mysql_config_editor for configuration adjustments, it detects incorrect syntax and
typing mistakes.

Option C : The mysqld_safe script skips the unknown variable and starts using the remaining
configuration changes.

Option D : Mysqld prints to the error log about an unknown variable, and then exits.

Correct Answer: D

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
QUESTION: 16

You are setting up a new server and you want to reserve 50GB of RAM for InnoDB use. What option would
you set

Option A : innodb_ram_size=50GB

Option B : innodb_buffer_pool_size=50GB

Option C : innodb_buffer_pool=50G

Correct Answer: B

QUESTION: 17

You enable binary logging on MySQL Server with the configuration: binlog-format=STATEMENT log-binWhich
database updates are logged on the master server to the binary log by default?

Option A : all updates except to the TEMPDB database

Option B : all updates except to the PERFORMANCE_SCHEMA database

Option C : all updates not involving temporary tables

Option D : all updates to the default database, except temporary tables

Option E : all updates to all databases

Correct Answer: A

QUESTION: 18

Force Majeure is a catastrophic failure on a major level of the database operation. Regular backups are key
to helping avoid data loss in such situations.Which two other steps can help avoid data loss in a major
catastrophe? (Choose two.)

Option A : Implement a failover strategy to another geographic location.

Option B : Create a master-master pair for each service.

Option C : Have a second data centre in a different region or country.

Option D : Keep software updated to the latest version.

Option E : Use RAID 10 storage for data.

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
Option F : Use on-site network-attached storage to separate service from data.

Correct Answer: A,C

QUESTION: 19

When using MySQL Group Replication you can use the following storage engines:

Option A : InnoDB and MyISAM

Option B : InnoDB

Option C : MyISAM

Option D : All the engines included with MySQL

Correct Answer: B

QUESTION: 20

Which three are properties of the MyISAM storage engine? (Choose three.)

Option A : Transaction support

Option B : FULLTEXT indexing for text matching

Option C : Table and page level locking support

Option D : Foreign key support

Option E : Geospatial indexing

Option F : HASH index support

Option G : Table level locking only

Correct Answer: A,B

QUESTION: 21

Why should you be selective when granting the PROCESS privilege to an account?

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
Option A : It allows a client to process scripts.

Option B : It allows the use of stored routines.

Option C : It allows a client to see another user's queries with the SHOW PROCESSLIST command.

Option D : It allows a client to control running processes on a server.

Correct Answer: C

Explanation/Reference:

https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_process

QUESTION: 22

Which two methods will provide the total number of partitions on a table? (Choose two.)

Option A : Query the INFORMATION_SCHEMA.PARTITIONS table

Option B : Use the command: SHOW TABLE STATUS

Option C : Query the performance_schema.objects_summary_global_by_type table

Option D : Query the INFORMATION_SCHEMA.TABLES table for the partition_count

Option E : Use the command: SHOW CREATE TABLE

Correct Answer: A

QUESTION: 23

You have forgotten the root user account password. You decide to reset the password and execute:

Which additional argument makes this operation safer?

Option A : --old-passwords, to start MySQL to use the old password format while running without the grant
tables.

Option B : --reset-grant-tables, to start the server with only the mysql database accessible.

Option C : --read-only, to set all data to read-only except for super users

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
Option D : --skip-networking, to prohibit access from remote locations

Correct Answer: D

QUESTION: 24

You have just created a replication slave from a backup of the master made with mysqldump:

You try to log in to the slave with the application user, but fail as follows:

The login works on the master.Which two changes to the process can fix the issue?

Option A : After the restore, log in to the database and execute FLUSH PRIVILEGES.

Option B : Use the --flush-privileges with mysqldump.

Option C : Add a second dump for the 'mysql' database; --all-databases does not include it.

Option D : Use the --grants option to include GRANT statements in the dump.

Correct Answer: B

Explanation/Reference:

https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_flush-privileges

QUESTION: 25

You are using the Performance Schema to investigate replication on a slave which has a single master.The
option slave-parallel-type is set to DATABASE.Assume that all instruments and consumers are enabled and
all threads are instrumented.Which two facts can be concluded from the given output? (Choose two.)

Option A : The salve has two intermediate relay slaves connected to it.

Option B : The slave is configured with slave_parallel_workers= 4.

Option C : At most, two schemas are being updates concurrently.

Option D : THREAD_ID 21 has stopped running.

Option E : The slave cannot process the relay log fast enough to use all threads.

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
Option F : The server needs more cores to use all slave threads.

Correct Answer: A,B

Explanation/Reference:

https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#sysvar_slave_parallel_type

QUESTION: 26

You execute mysqladmin shutdown on a MySQL server that is decribed as follows:- It is running with both
active and idle client connections.- It has open transactions against InnoDB.- It has active multi-row inserts
into MyISAM engine tables.- Innodb_fast_shitdown is set to 1.What are three effects of shutting down the
server by using a mysqladmin shutdown command?

Option A : Connections that have an open transaction are rolled back.

Option B : Connections with statements running against MyISAM tables wait for completion.

Option C : New client connections are not accepted after shutdown is initiated.

Option D : Connections are accepted during the shutdown process because a graceful shutdown can take
large amounts of time.

Option E : Partial updates may occur for non-transactional tables.

Correct Answer: A,C

QUESTION: 27

A MySQL replication master is set up by using global reansaction identifiers(GTIDs). You execute this
statement:mysql> CREATE TABLE datatarget SELECT * FROM datasource;ERROR 1786 (HY000): CREATE
TABLE ... SELECT is forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1.Why is this statment
forbiden?

Option A : GTIDs have detected thar a slave is inconsistent and therefore, disallow upadtes until the
problem is resolved.

Option B : The datasource table is using an older InnoDB tablespace format, which is not compatible with
GTIDs.

Option C : The datasource table is using the MyISAM engine, which is not compatible with GTIDs.

Option D : The statement cannot be logged in a transactionally safe manner.

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
Correct Answer: D

Explanation/Reference:

https://dev.mysql.com/doc/refman/8.0/en/replication-options-gtids.html

QUESTION: 28

User bob@% has the following roles granted: app_read and app_write.How would you show the privileges for
bob@% when it uses app_write? (Choose one).

Option A : SHOW GRANTS FOR bob@'%';

Option B : SHOW GRANTS FOR bob@'%' USING 'app_write';

Correct Answer: B

Explanation/Reference:

Roles can be not enabled by default, you will need to usie the SHOW GRANTS FOR… USING syntax to show privileges for non-

default roles.

QUESTION: 29

Your MySQL server was upgraded from an earlier major version.The sales database contains three tables,
one of which is the transactions table, which has 4 million rows.You are running low on disk space on the
datadir partition and begin to investigate.Examine these commands and output:

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
Which two statements are true? (Choose two.)

Option A : Executing SET GLOBAL innodb_row_format=COMPRESSED and then ALTER TABLE

transactionswill free up disk space.

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

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

Option D : Truncating the transactions table will free up the most disk space.

Option E : The transactions table was created with innodb_file_per_table=OFF.

Correct Answer: A

QUESTION: 30

Which two statements are true about using MySQL Enterprise Monitor Query Analyzer? (Choose two.)

Option A : The single query QRTi pie chart in the Query Analyzer view is based on the average execution
of all statements.

Option B : It is possible to retrieve a normalized statement, but never the exact statement that was
executed.

Option C : It is possible to configure the Query Analysis built-in advisor to get notified about slow query
execution.

Option D : It is possible to list and analyze statements in an arbitrary graph range selection from
timeseries graphs.

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
Option E : It is possible to import data into the Query Analyzer from heterogeneous sources, such as CSV.

Correct Answer: C,D

QUESTION: 31

You execute this command:shell> mysqlpump --exclude-databases=% --usersWhich statement is true?

Option A : It creates a logical backup of all MySQL user accounts.

Option B : It creates a logical backup of all metadata, but contains no table data.

Option C : It returns an error because the mysqldump command should have been used.

Option D : It creates a logical backup of only the users database.

Correct Answer: B

QUESTION: 32

Examine this MySQL client command to connect to a remote database:mysql -h remote.example.org -u root -
p --protocol=TCP --ssl-mode=Which two --ssl-mode values will ensure that an X.509-compliant certificate will
be used to establish theSSL/TLS connection to MySQL?

Option A : REQUIRED

Option B : VERIFY_CA

Option C : VERIFY_IDENTITY

Option D : PREFERRED

Option E : DISABLED

Correct Answer: A

QUESTION: 33

On examination, your MySQL installation datadir has become recursively world read/write/executable.What
are two major concerns of running an installation with incorrect file privileges? (Choose two.)

Option A : Users could overwrite configuration files.

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
Option B : Data files could be deleted.

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

Option D : MySQL binaries could be damaged, deleted, or altered.

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

Correct Answer: A,B

QUESTION: 34

You have an InnoDB Cluster configured with three servers.Examine this command, which executes
successfully:mysqldump -uroot -p -d mydatabase > mydatabase_backup.sqlDue to data loss, the cluster is
initialized and a restore is attempted resulting in this error:ERROR 13176 (HY000) at line 23: Cannot update
GTID_PURGED with the Group Replication plugin runningWhich two actions, either one of which, can fix this
error and allow a successful restore of the cluster? (Choose two.)

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

Option B : Remove the @@GLOBAL.gtid_purged statement from the dump file.

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

Option D : Remove the group replication plugin from each instance before restoring.

Option E : Remove the @@GLOBAL.gtid_executed statement from the dump file.

Option F : Restore using the --set-gtid-purged=OFF option.

Correct Answer: B,C

QUESTION: 35

You have just installed MySQL on Oracle Linux and adjusted your /etc/my.cnf parameters to suit your
installation.Examine the output:

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
What statement is true about the start attempt?

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

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

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

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

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

Correct Answer: A

QUESTION: 36

Examine this statement, which executes successfully:

You want to improve the performance of this query:

Which change enables the query to succeed while accessing fewer rows?

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

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
Option B : ALTER TABLE world.city ADD SPATIAL INDEX (Population);

Option C : ALTER TABLE world.city ADD INDEX (Population);

Option D : ALTER TABLE world.city ADD INDEX (Name);

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

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

Correct Answer: B

QUESTION: 37

Your MySQL environment has asynchronous position based-replication with one master and one slave.The
slave instance had a disk I/O problem, so it was stopped.You determined that the slave relay log files were
corrupted and unusable, but no other files are damaged.You restart MySQL Server.How can replication be
restored?

Option A : The slave relay logs should be deleted; then execute START SLAVE;

Option B : The relay logs from the master should be used to replace the corrupted relay logs.

Option C : The slave relay logs should be deleted; execute CHANGE MASTER to adjust the replication relay
log file name, then issue start SLAVE;

Option D : The slave needs to be restored from backup.

Correct Answer: B

QUESTION: 38

You are considering using file-system snapshots to back up MySQL.Which three statements are true?
(Choose three.)

Option A : They take roughly twice as long as logical backups.

Option B : They allow direct copying of table rows with operating system copy commands.

Option C : They work best for transaction storage engines that can perform their own recovery when
restored.

Option D : The backup window is almost zero from the perspective of the application.

Option E : They do not back up views, stored procedures, or configuration files.

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html
Option F : There is a slight performance cost while the snapshot is active.

Option G : They do not use additional disk space.

Correct Answer: A,C

QUESTION: 39

Examine this command and output:Which statement is true?

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

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

Option C : Firewall_access_suspicious is the number of statements logged as suspicious for users in

DETECTING mode.

Option D : Firewall_access_granted is the number of connections granted from whitelisted hosts.

Correct Answer: C

QUESTION: 40

Which two are valid uses for binary logs on a MySQL instance? (Choose two.)

Option A : recording the order in which queries are issued

Option B : audit of all queries

Option C : point-in-time recovery

Option D : replication

Option E : logging the duration and locks for all queries

Correct Answer: A,C

https://www.oracledumpsfree.com/1Z0-908-exam-dumps.html