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

When designing an InnoDB table, identify an advantage of using the BIT datatype Instead of one of the integer datatypes.

• A. Multiple BIT columns pack tightly into a row, using less space.
• B. BIT (8) takes less space than eight TINYINT fields.
• C. BIT columns are written by InnoDB at the head of the row, meaning they are always the first to be retrieved.
• D. The BIT columns can be manipulated with the bitwise operators &, |, ~, ^, <<, and >>. The other integer types cannot.

Which three statements are characteristic of the MEMORY storage engine?

• A. Each table is represented on disk as an.frm file.
• B. It cannot contain text or BLOB columns.
• C. Each table has a corresponding.MYI and .MYD file.
• D. It can support transactions
• E. It can support foreign keys.
• F. Table contents are not saved if the server is restarted.

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. Add an index on the objective column so that is can be used in both the WHERE and GROUP BY operations.
• D. Add a composite index on (run,objective,stage) to allow the query to fully utilize an index.
• E. Do not pass a user variable in the WHERE clause because it limits the ability of the optimizer to use indexes.

Which two capabilities are granted with the SUPER privilege?

• A. Allowing change of the server runtime configuration
• B. Allowing a client to shut down the server
• C. Allowing client accounts to take over the account of another user
• D. Allowing a client to kill other client connections

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