1Z0-883 Online Practice Questions and Answers

Which three statements are characteristic of the MEMORY storage engine?

A. Each table is represented on disk as an.frm file.

B. Each table has a corresponding.MYI and .MYD file.

C. It can support foreign keys.

D. It cannot contain text or BLOB columns.

E. Table contents are not saved if the server is restarted.

F. It can support transactions

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

Which two statements are true regarding partitioning in Mysql?

A. Tables with BLOB and TEXT columns cannot be partitioned.

B. Partitioning allows easier management of smaller data sets for certain queries.

C. Partitioning allows different columns to be stored in separate files.

D. The partitioning expression is an integer or function that returns an integer value or NULL value.

E. Partitioning is only available for those storage engines that implements it natively.

You want to shutdown a running Mysql Server cleanly.

Which three commands that are valid on either Windows or Linux will achieve this?

A. Shell> pkill u mysql mysqld_safe

B. Shell> service mysql safe_exit

C. Shell> /etc/init.d/mysql stop

D. Shell> mysqladmin u root p shutdown

E. Mysql> STOP PROCESS mysqld;

F. Shell> net stop mysql

G. Shell> nmc mysql shutdown

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

Consider the following table:

REATE TABLE ‘game’ (

‘id’ int (10) unsigned NOT NULL AUTO_INCREMENT,

‘keyword’ varchar (45) DEFAULT NULL,

‘date’ datetime NOT NULL,

PRIMARY KEY (‘id’ , ‘date’),

UNIQUE KEY ‘keyword_idx’ (‘keyword’ , ‘date’)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

PARTITION BY RANGE (TO_DAYS (date) ) (

PARTITION g201301 VALUES LESS THAN (TO_DAYS (‘2013-01-01 00:00:00’) ),

PARTITION g201302 VALUES LESS THAN (TO_DAYS (‘2013-02-01 00:00:00’) ),

PARTITION g201303 VALUES LESS THAN (TO_DAYS (‘2013-03-01 00:00:00’) ),

PARTITION g201304 VALUES LESS THAN (TO_DAYS (‘2013-04-01 00:00:00’) ),

PARTITION gMORES VALUES LESS THAN (MAXVALUE) );

Which method should used to add a new g201305 partition to the table?

A. ALTER TABLE games REORGANIZE PARTITION (gMORES) INTO g01305 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') ), gMORES VALUES LESS THAN (MAXVALUE) );

B. ALTER TABLE games ADD PARTITION g201350 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') );

C. ALTER TABLE games COALESCE PARTITION (gMORES) INTO g01305 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') ), gMORES VALUES LESS THAN (MAXVALUE) );

D. ALTER TABLE games SPLIT PARTITION (gMORES) INTO g201305 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') ), gMORES VALUES LESS THAN (MAXVALUE) );

E. ALTHER TABLE games DROP PATITION gMORES, ADD PARTITION g201305 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') ), gMORES VALUES LESS THAN (MAXVALUE) );

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.

Consider the MySQL Enterprise Audit plugin.

On attempting to start the MySQL service after a crash, notice the following error:

[ERROR] Plugin `audit_log' init function returned error.

In the audit log file, you notice the final entry:

...

TIMESTAMP="2013-07-09T02:12:35"

NAME="Connect"

CONNECTION_ID="98"

STATUS="0"

USER="Kate"

PRIV_USER="kate"

OS_LOGIN=""

HOST="localhost"

DB=""/>

What action should you take to fix the error and allow the service to start?

A. Re-install the audit plugin.

B. Execute the command FLUSH LOGS.

C. Execute the command SET GLOBAL audit_log_fiush= ON.

D. Move or rename the existing audit.log file.

You use--login-path to access a MySQL server on a Linux installation.

Which statement is true about the login-path option that is created by using mysql_config_editor?

A. All system users have access to the MySQL server via--login path local.

B. __login-path can be used only for MySQL servers running on a local machine.

C. __login-path allows you to provide login credentials without passing clear text passwords on the command line.

D. When using login-path to connect to a remote MySQL server, the remote server version must be 5.6 or later.

Which three statements are true about memory buffer allocation by a MySQL Server?

A. Global buffers such as the InnoDB buffer pool are allocated after the server starts, and are never freed.

B. Thread buffers are allocated when a client connects, and are freed when the client disconnects.

C. Buffers that are needed for certain operation are allocated when the operation starts, and freed when it ends.

D. User buffers are allocated at server startup and freed when the user is dropped.

E. All dynamic buffers that are set with a SET GLOBAL statement immediately get allocated globally, and are never freed.