MySQL 5.6 Database Administrator Version: 8.0 [ Total Questions: 100 ] Oracle 1z0-883 : Practice Test Question No : 1 A user executes the statement; PURGE BINARY LOGS TO mysql-bin.010; What is the r v‘5.6.13-enterprise-commercial-advaced’ socket: ‘/tmp/mysql.sock’ port; (1z0-883)

Page:    1 / 7   
Total 107 questions

In a design situation, there are multiple character sets that can properly encode your data.
Which three should influence your choice of character set?

  • A. Disk usage when storing data
  • B. Syntax when writing queries involving JOINS
  • C. Comparing the encoded data with similar columns on other tables
  • D. Memory usage when working with the data
  • E. Character set mapping index hash size

Answer : C,D,E

What are two methods of taking a binary backup of a Mysql Server using InnoDB storage engine?

  • A. Mysql Enterprise Backup
  • B. Mysqldump with – binary-data option
  • C. Mysqlhotcopy
  • D. File system snapshots
  • E. Mysqldumpslow

Answer : A,B


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?

  • A. Each extra column in the copy with more columns must not have a default value.
  • B. Columns that are common to both versions of the table must be defined in the same order on the master and the slave.
  • C. The slave database cannot have more columns than the master. Only the master database can have more columns.
  • 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.
  • E. The master database cannot have more columns than the slave. Only the slave deatbase can have more columns.

Answer : B,D

Assume that you want to know which Mysql Server options were set to custom values.
Which two methods would you use to find out?

  • A. Check the configuration files in the order in which they are read by the Mysql Server and compare them with default values.
  • B. Check the command-line options provided for the Mysql Server and compare them with default values.
  • C. Check the output of SHOW GLOBAL VARIABLES and compare it with default values.
  • D. Query the INFORMATION_SCHEMA.GLOBAL_VARIABLES table and compare the result with default values.

Answer : C,D

Consider the Mysql Enterprise Audit plugin.
A CSV file called data.csv has 100 rows of data.
The stored procedure prepare_db ( ) has 10 auditable statements.
You run the following statements in the mydb database:
Mysql> CALL prepare_db ( );
Mysql> LOAD DATA INFILE /tmp/data.cav INTO TABLE mytable;
How many events are added to the audit log as a result of the preceding statements?

  • A. 102; top-level statements are logged, but LOAD DATA INFILE is logged as a separate event.
  • B. 3; only the top-level statements are logged.
  • C. 111; top-level statements and all lower-level statements are logged.
  • D. 12; only top-level statements and stored procedure events are logged.

Answer : B

Reference: logging-control.html

You attempt to connect to a Mysql Server by using the mysql program. However, you receive the following notice:
ERROR 2059 (HY000): Authentication plugin mysql_clear_password connot be loaded: plugin not enabled
What would you run to fix the issue?

  • A. The mysql client with the – ignore-password-hashing option
  • B. The mysql_secure_installation script to update server security settings
  • C. The mysql client with the – enable-cleartext-plugin option
  • D. The mysql_upgrade script
  • E. The install plugin command for the mysql_cleartext_password plugin

Answer : C


A Mysql instance is running on a dedicated server. Developers access the server from the same network subnet. Users access the database through an application that is running on a separate server in a DMZ.
Which two will optimize the security of this setup?

  • A. Disabling connections from named pipes or socket files (depending on the operating system of the server)
  • B. Running the server with – skip-networking specified
  • C. Limiting logins to originate from the application server or the server’s subnet
  • D. Starting the server with – bind- address= specified
  • E. Installing Mysql on the application server, and running the database and application on the same server
  • F. Enabling and using SSL for connections to the Mysql database

Answer : E,F

Consider the following statement on a RANGE partitioned table:
What is the outcome of executing the above statement?

  • A. Only the first partition (p1) will be dropped as only one can be dropped at any time.
  • B. All data in p1 and p3 partitions are removed, but the table definition remains unchanged.
  • C. A syntax error will result as you cannot specify more than one partition in the same statement.
  • D. All data in pi and p3 partitions are removed and the table definition is changed.

Answer : D


What is true regarding InnoDB locking?

  • A. InnoDB row locks may be escalated to page or table-level locks.
  • B. InnoDB only uses row locks, not page or table-level locks,
  • C. InnoDB uses row and table-level locks, but row locks are not escalates,
  • D. InnoDB locks only those rows that are updated.
  • E. InnoDB uses row-level or table-level locks depending on the number of rows affected.

Answer : E


Compare a typical Distributed Replicated Block Device (DRBD) with MySQL Standard
Replication using master-slave replication.
Which two statements are correct?

  • A. Both technologies use the TCP/IP stack as their primary transmission medium.
  • B. DRBD uses shared-disk technology.
  • C. Both technologies guarantee an identical copy of data on the secondary node.
  • D. Only MySQL can maintain a time-delayed copy of data on the secondary node.

Answer : A,D

Explanation: A:
* Replication cannot use Unix socket files. You must be able to connect to the master
MySQL server using TCP/IP.
D: Time Delayed replication is however something quite helpful for some environments.
Though DRBD also could be extended to support one if needed.
Not B: DRBD (Distributed Replication Block Device), one of the leading solutions for
MySQL HA (High Availability), offering users:
/ An end-to-end, integrated stack of mature and proven open source technologies, fully supported by Oracle;
/ Automatic failover and recovery for service continuity;
/ Mirroring, via synchronous replication, to ensure failover between nodes without the risk of losing committed transactions;
/ Building of HA clusters from commodity hardware, without the requirement for shared- storage.

Consider the following:
Mysql> EXPLAIN SELECT * FROM City WHERE Name = Jacksonville AND CountryCode
= USA \G
******************************** 1. row ********************************

Id: 1 -

Select_type: SIMPLE -

Table: City -

Type: ref -
Possible_keys: name_country_index

Key: name_country_index -

Ref: const, const -

Rows: 1 -

Extra: Using where -
Which statement best describes the meaning of the value for the key_len column?

  • A. It shows the total size of the index row.
  • B. It shows how many columns in the index are examined.
  • C. It shows the number of characters indexed in the key.
  • D. It shows how many bytes will be used from each index row.

Answer : D

Consider the query:
Mysql> SET @run = 15;
Mysql> EXPLAIN SELECT objective, stage, COUNT (stage)

FROM iteminformation -
WHERE run=@run AND objective=7.1

GROUP BY objective,stage -
ORDER BY stage;

The iteminformation table has the following indexes;
Mysql> SHOW INDEXES FROM iteminformation:

This query is run several times in an application with different values in the WHERE clause in a growing data set.
What is the primary improvement that can be made for this scenario?

  • A. Execute the run_2 index because it has caused a conflict in the choice of key for this query.
  • B. Drop the run_2 index because it has caused a conflict in the choice of key for this query.
  • C. Do not pass a user variable in the WHERE clause because it limits the ability of the optimizer to use indexes.
  • D. Add an index on the objective column so that is can be used in both the WHERE and GROUP BY operations.
  • E. Add a composite index on (run,objective,stage) to allow the query to fully utilize an index.

Answer : B

Which two statements are true about setting the per-thread buffers higher than required?

  • A. More memory per thread is beneficial in all scenarios.
  • B. It causes increased overhead due to initial memory allocation.
  • C. It can affect system stability during peak load times, due to swapping.
  • D. It requires increasing the thread_cache_size variable.

Answer : C,D

Which two statements are true about InnoDB auto-increment locking?

  • A. The auto-increment lock can be a table-level lock.
  • B. InnoDB never uses table-level locks.
  • C. Some settings for innodb_autoinc_lock_mode can help reduce locking.
  • D. InnoDB always protects auto-increment updates with a table-level lock.
  • E. InnoDB does not use locks to enforce auto-increment uniqueness.

Answer : A,D

Explanation: A (not B): InnoDB uses a special lock called the table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns.
D (Not E): This lock is normally held to the end of the statement (not to the end of the transaction), to ensure that auto-increment numbers are assigned in a predictable and repeatable order for a given sequence of INSERT statements.
Reference: Configurable InnoDB Auto-Increment Locking

The allplicationdb is using innoDB and consuming a large amount of file system space.
You have a /backup partition available on NFS where backups are stored.
You investigate and gather the following information:

Innodb_file_per_table=0 -
Three tables are stored in the innoDB shared tablespace and the details are as follows:
-> The table data_current has 1,000,000 rows.
-> The table data_reports has 1,500,000 rows.
-> The table data_archive has 4,500,000 rows.
Shell> is -1 /var/lib/mysql/
-rw-rw---- 1 mysql mysql 744G Aug 26 14:34 ibdata1
-rw-rw---- 1 mysql mysql 480M Aug 26 14:34 ib_logfile0
-rw-rw---- 1 mysql mysql 480M Aug 26 14:34 ib_logfile1

You attempt to free space from ibdata1 by taking a mysqldump of the data_archive table and storting it on your backup partition.
Shell> mysqldump u root p applicationdb data_archive > /backup/data_archive.sql
Mysql> DROP TABLE data_archive;
Which set of actions will allow you to free disk space back to the file system?

  • A. Execute OPTIMIZE TABLE so that the InnoDB engine frees unused pages on disk back to the file system: Mysql> OPTIMIZE TABLE data_current, data_reports;
  • B. Set the server to use its own tablespace, and then alter the table so that data is moved from the shared tablespace to its own: Mysql> SET GLOBAL innodb_file_per_table=1; Mysql> ALTER TABLE data_current ENGINE=InnoDB; Mysql> ALTER TABLE data_repors ENGINE=InnoDB;
  • C. Take a backup, stop the server, remove the data files, and restore the backup: Shell> mysqldump u root p applicationdb / > /backup/applicationdb.sql Shell> /etc/init.d/mysql stop Shell> cd /var/lib/mysql/ Shell> rm ibdata1 ib_logfile0 ib_logfile1 Shell> /etc/init.d/mysql start Shell> mysql u root p applicationdb < /backup/applicationdb.sql
  • D. Enable compression on the table, causing InnoDB to release unused pages on disk to the file system: Mysql> SET GLOBLE innodb_file_per_table=1; Mysql> SET GLOBLE innodb_file_format=Barramcuda; Mysql> ALTER TABLE data_current ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; Mysql> ALTER TABLE data_history ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

Answer : C

Page:    1 / 7   
Total 107 questions