MySQL 5.6 Database Administrator (1z1-883) Free Practice Test

Question 1

You are creating a new server with the same accounts as an existing server. You do this by importing a mysqldump file of the mysql database.
You test whether the import was successful by using the following commands:
Mysql> select user, host, password from mysql.user;

9 rows in set (0.00 sec)
Mysql> show grants for 'admin'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'admin' on host '%'
Which command will fix this issue?

A. UPDATE mysql.user SET Create_user_priv = 'Y' WHERE user= 'admin';

B. GRANT USAGE ON *.* TO 'admin'@'%';

C. FLUSH HOST CACHE;

D. FLUSH PRIVILEGES;

E. CREATE USER 'admin' @'%';

Correct Answer: D

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 2

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:
[mysqld] Datadir=/var/lib/mysql/ 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. 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

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

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

Correct Answer: B

Question 3

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. Query the INFORMATION_SCHEMA.GLOBAL_VARIABLES table and compare the result with default values.

B. Check the output of SHOW GLOBAL VARIABLES and compare it with default values.

C. Check the command-line options provided for the Mysql Server and compare them with default values.

D. Check the configuration files in the order in which they are read by the Mysql Server and compare them with default values.

Correct Answer: A,B

Question 4

Which two are true regarding MySQL binary and text backups?

A. Text backups are human-readable while binary backups are not.

B. Binary backups are usually slower than text backups.

C. Binary backups are not portable across different operating systems.

D. Binary backups are usually faster than text backups.

Correct Answer: A,C

Question 5

MySQL is installed on a Linux server and has the following configuration:
[mysqld]
User=mysql
Datadir=/data/mysql
As the 'root' user, change the datadir location by executing:
Shell> cp -R /var/lib/mysql/data/mysql/
Shell> chown -R mysql /data/mysql/
What is the purpose of changing ownership of datadir to the 'mysql' user?

A. The mysqld process requires all permissions within datadir to be the same.

B. MySQL requires correct file ownership while remaining secure.

C. MySQL needs to be run as the root user, but file cannot be owned by it.

D. MySQL cannot be run as the root user.

Correct Answer: B

Question 6

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

A. Both technologies guarantee an identical copy of data on the secondary node.

B. Both technologies use the TCP/IP stack as their primary transmission medium.

C. Only MySQL can maintain a time-delayed copy of data on the secondary node.

D. DRBD uses shared-disk technology.

Correct Answer: B,C

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 7

Identify a performance impact when using the Performance Schema.

A. There is a constant overhead regardless of settings and workload.

B. The overhead depends on the settings of the Performance Schema.

C. There is an overhead for querying the Performance Schema but not for having it enabled.

D. There is no impact on performance.

Correct Answer: B

Question 8

Consider the events_% tables in performance Schema.
Which two methods will clear or reset the collected events in the tables?

A. Using TRUNCATE statements, for example, TRUNCATE TABLE performance_schema.events_waits_current;

B. Restarting Mysql

C. Disabling and re-enabling all instruments

D. Using DELETE statements, for example, DELETE FROM performance_schema.events_watis_current;

E. Using the statement FLUSH PERFORMANCE CACHE;

F. Using the statement RESET PERFORMANCE CACHE;

Correct Answer: A,C

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 9

You adjust a default configuration to the following /etc/my.cnf on a Linux installation:
[mysqld] Loq-bin Binrylog_format=ROW
You do not notice the spelling error in binrylog_format and restart your production server.
How does the MySQL server behave with incorrectly spelled options?

A. When using mysql_config_editor for configuration adjustments, it detects incorrect syntax and typing mistakes.

B. Mysqld uses internal configuration versioning and reverts to the previous configuration.

C. Mysqld prints to the error log about an unknown variable, and then exits.

D. The mysqld_safe script skips the unknown variable and starts using the remaining configuration changes.

Correct Answer: C

Question 10

An existing master-slave setup is currently using a delayed replication of one hour. The master has crashed and the slave must be "rolled forward" to provide all the latest data.
The SHOW SLAVE STATUS indicates the following values:
RELAY_LOG_FILE = hostname-relay-bin.00004
RELAY_LOG_POS = 1383
Which command set would make the slave current?

A. STOP SLAVE; CHANGE MASTER TO MASTER_DELAY =0, RELAY_LOG_FILE = 'hostnamerelay-bin.00004' , RELAY_LOG_POS = 1383;

B. STOP SLAVE; SET GLOBAL master_delay =0; START SLAVE;

C. STOP SLAVE; CHANGE MASTER TO RELAY_LOG_FILE = 'hostname-relay-bin.00004', RELAY_LOG_POS = 1383;

D. STOP SLAVE; CHANGE MASTER TO MASTER_DELAY=0; START SLAVE;

Correct Answer: D

Welcome to TestSimulate

Oracle MySQL 5.6 Database Administrator (1z1-883) Free Practice Test