Page: 1
/ 7
Total 100 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
Reference: http://dev.mysql.com/doc/refman/5.5/en/innodb-backup.html
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;
Mysql> SHOW TABLES;
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: http://dev.mysql.com/doc/mysql-security-excerpt/5.5/en/audit-log-plugin- 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
Reference: http://planet.mysql.com/entry/?id=34077
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=0.0.0.0 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:
ALTER TABLE orders DROP PARTITION p1, p3;
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
Reference:
https://dev.mysql.com/doc/refman/5.5/en/partitioning-management.html
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
Reference: http://dev.mysql.com/doc/refman/5.0/en/table-locking.html
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: 14.6.5.2 Configurable InnoDB Auto-Increment Locking http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-configurable.html
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. 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
30 days access 