1Z0-883 | The Secret of Oracle 1Z0-883 examcollection
Q31. What are three methods to reduce Mysql server exposure to remote connections?
A. Setting -- skip-networking when remote connections are not required
B. Using the sql_mode=STRICT_SECURE after connections are established for encrypted communications
C. Setting specific GRANT privilege to limit remote authentication
D. Setting – mysql_secure_configuration to enable paranoid mode
E. Using SSL when transporting data over remote networks
Answer: B,C,D
Q32. 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. MySQL cannot be run as the root user.
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. The mysqld process requires all permissions within datadir to be the same.
Answer: B
Q33. Which three are properties of the MyISAM storage engine?
A. Transaction support
B. FULLTEXT indexing for text matching
C. Table and page level locking support
D. Foreign key support
E. Geospatial indexing
F. HASH index support
G. Table level locking only
Answer: B,E,G
Q34. You have forgotten the root user account password. You decide to reset the password and execute the following:
Shell> /etc/init.d/mysql stop Shell> /etc/init.d/mysql start – skip-grant tables Which additional argument makes this operation safer?
A. --skip-networking, to prohibit access from remote locations
B. --reset-grant-tables, to start the server with only the mysql database accessible
C. --read-only,to set all data to read-only except for super users
D. --old-passwords, to start Mysql to use the old password format while running without the grant tables
Answer: B
Q35. Which two options describe how MySQL Server allocates memory?
A. Each thread allocates memory from a global pool.
B. Global memory resources are allocated at server startup.
C. Thread memory is pre-allocated up to thread_cache_size for performance.
D. Each connection may have its own per-thread memory allocations.
Answer: B,D
Q36. Which two are true regarding MySQL binary and text backups?
A. Binary backups are usually faster than text backups.
B. Binary backups are usually slower than text backups.
C. Text backups are human-readable while binary backups are not.
D. Binary backups are not portable across different operating systems.
Answer: C,D
Q37. Consider the Mysql Enterprise Audit plugin.
You are checking user accounts and attempt the following query:
Mysql> SELECT user, host, plugin FROM mysql.users;
ERROR 1146 (42S02): Table ‘mysql.users’ doesn’t exist
Which subset of event attributes would indicate this error in the audit.log file?
A. NAME=”Query”
STATUS=”1146”
SQLTEXT=”select user,host from users”/>
B. NAME=”Error”
STATUS=”1146”
SQLTEXT=”Error 1146 (42S02): Table ‘mysql.users’ doesn’t exist”/>
C. NAME=”Query”
STATUS=”1146”
SQLTEXT=” Error 1146 (42S02): Table ‘mysql.users’ doesn’t exist”/>
D. NAME=”Error”
STATUS=”1146”
SQLTEXT=”select user,host from users”/>
E. NAME=”Error”
STATUS=”0”
SQLTEXT=”Error 1146 (42S02): Table ‘mysql.users’ doesn’t exist”/>
Answer: C
Q38. 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: D
Q39. Mysqldump was used to create a single schema backup;
Shell> mysqldump –u root –p sakila > sakila2013.sql
Which two commands will restore the sakila database without interfering with other running database?
A. Mysql> USE sakila; LOAD DATA INFILE ‘sakila2013.sql’;
B. Shell> mysql –u root –p sakila sakila2013.sql
C. Shell> mysql import –u root –p sakila sakila2013.sql
D. Shell> mysql –u root -p –e ‘use sakila; source sakila2013.sql’
E. Shell> mysql –u root –p –silent < sakila2013.sql
Answer: B
Reference: http://mysql.livejournal.com/133572.html
Q40. Which three methods will show the storage engine for the Country table?
A. SHOW CREATE TABLE Country;
B. SHOW ENGINE Country STATUS;
C. SHOW TABLE STATUS LIKE ‘Country’;
D. SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= ‘Country’;
E. SELECT ENGINE FROM INFORMATION_SCHEMA.ENGINES WHERE TABLE_NAME= ‘Country’;
Answer: A,D,E