image


005-002 Dumps

005-002 Braindumps

005-002 Real Questions

005-002 Practice Test

005-002 Actual Questions


mySQL


005-002


Certified MySQL 5.0 DBA Part I


https://killexams.com/pass4sure/exam-detail/005-002


QUESTION: 116

Which of the following correctly defines the general difference between a read lock and a

write lock?


  1. A read lock allows other clients to read the same data, however will prevent any modification of the data until the lock is released.

  2. A read lock prevents any other client from reading the same data, until the lock is released.

  3. A write lock only prevents any other client from modifying the locked data until the lock is released.

  4. A write lock prevents any other client from reading or writing the locked data until the lock is released.


Answer: A, D


QUESTION: 117

Which of the following describes how deadlocks may occur and how InnoDB resolves

them?


  1. Deadlocks happen anytime when more than one lock is placed in a table.

  2. Deadlocks happen when two transactions both have locks that the other is waiting for

    the release of the lock that the other holds.

  3. InnoDB resolves deadlocks by terminating and rolling back one of the deadlocking

    transactions.

  4. InnoDB resolves deadlocks by terminating and rolling back both of the deadlocking

transactions.


Answer: B, C


QUESTION: 118

When working with InnoDB, for which of the following reasons should you use the FOR UPDATE locking modifier?


  1. You intend to run more than ten UPDATE statements in one transaction.

  2. You intend to execute an UPDATE statement on any row.

  3. You intend to SELECT a set of rows, then modify those rows.


Answer: C


QUESTION: 119


Which of the following are characteristics of the MyISAM compressed-row storage format?


  1. Tables are packed to save space.

  2. Rows cannot be looked up as efficiently.

  3. Storage is optimized for quick retrieval.

  4. Tables are read-only.

  5. All rows have the same size.


Answer: A, C, D


QUESTION: 120

Which of the following are requirements for InnoDB binary portability?


  1. Both machines must use the same operating system.

  2. Database and table names must use lowercase format.

  3. Both machines must use two's-complement integer arithmetic.

  4. Both machines must use IEEE floating-point format or contain no floating-point columns.


Answer: B, C, D


QUESTION: 121

Which of the following (series of) statements will leave the three tables A, B and C locked

for reading, writing and reading respectively once all statements have been executed?


  1. Mysql> LOCK TABLES A; mysql> LOCK TABLES B; mysql> LOCK TABLES C;

  2. Mysql> LOCK TABLES A READ; mysql> LOCK TABLES B WRITE; mysql> LOCK

    TABLES C READ;

  3. Mysql> LOCK TABLES A READ, B WRITE, C READ;

  4. LOCK TABLES A, B, C READ, WRITE, READ;


Answer: C


QUESTION: 122

The MySQL server host crashes at 10:00 in the morning, and is brought back online at 10:30. In order to ensure that all data are consistent, a copy is first made of the table, tablespace and log files currently on the server host, and these files are then restored from a backup made at 03:00 the same morning. What should be done in order to bring the database to the state it was at just before the server host crashed?


  1. The mysql_restore utility should be used to update the server to its last known state.

  2. The binary logs recorded after the backup at 03:00 should be re-applied to make the database file consistent with the state just before the crash.

  3. The procedure described is wrong; instead, the mysqlcheck utility should be used and only if that fails should backup copies be restored.

  4. Once the backup files from 03:00 have been restored, there is nothing more that can be done to restore the database files.


Answer: B


QUESTION: 123

What will the following statement do in a Windows environment? Assume that there are no

conflicts in the pathname definitions. C: mysqld --install MySQL50 --defaults- file=C:opts.cnf


  1. MySQL 5.0 will be installed using default configuration file C:\my-opts.cnf

  2. MySQL will be installed as Windows service name MySQL50 and will use C:\my- opts.cnf as configuration file

  3. An error message will be issued as --install is not a valid option for mysqld


Answer: B


QUESTION: 124

Which of the following describes how READ LOCAL locking works?


  1. It locks a table for reading only by connections on localhost.

  2. It locks a table for reading but allows concurrent inserts.

  3. It locks a table for writing, preventing reads until it is released.


Answer: B


QUESTION: 125

Suppose your are adding rows to a MyISAM table and the server runs out of disk space. What will happen?


  1. The server will crash.

  2. An error message will be returned to the client.

  3. The server suspends that INSERT operation until space becomes available.

  4. The server suspends operations on all MyISAM tables until space becomes available.


Answer: C


QUESTION: 126

When you upgrade from one version of MySQL to another which of these steps are

considered necessary?


  1. Check the MySQL Reference Manual upgrading section and read the parts that concern your upgrade

  2. Backup your databases

  3. Stop the MySQL server

  4. Install the new version of MySQL on top of the existing version

  5. Start the new server

  6. None of the above


Answer: A, B, C, D, E


QUESTION: 127

When you acquire an advisory lock using GET_LOCK(), the lock isreleased if


  1. You issue another GET_LOCK() statement

  2. You issue a RELEASE_LOCK() statement

  3. Your connection to the server terminates

  4. None of the above


Answer: A, B, C


QUESTION: 128

Which of the following statements are true for the MERGE storage engine?


  1. It uses table-level locking.

  2. It uses row-level locking.

  3. Underlying MyISAM tables are read-locked when you issue a SELECT statement on a

    MERGE table.

  4. Underlying MyISAM tables are write-locked when you issue a SELECT statement on a

    MERGE table.

  5. Underlying MyISAM tables are read-locked when you issue a statement that modifies a

    MERGE table.

  6. Underlying MyISAM tables are write-locked when you issue a statement that modifies a

    MERGE table.


  7. To LOCK a MERGE table, it is sufficient to lock just that table.

  8. To LOCK a MERGE table, you need to lock all underlying MyISAM tables as well.


Answer: A, C, F, G


QUESTION: 129

When installing a RPM based distribution, the data directory will be set to which of the

following locations?


  1. /Var/lib/mysql

  2. /Usr/mysql/data

  3. /Var/mysql/data

  4. /Usr/local/mysql/data


Answer: A


QUESTION: 130

Which of the following are reasons to not just enable all logging?


  1. Security risks.

  2. More disk space is used.

  3. More memory is used.

  4. Slower performance.


Answer: B, D


QUESTION: 131

Where is the data stored for a table that is defined as using the FEDERATED Storage Engine?


  1. The data will always be stored on the local host.

  2. The data will always be stored on a remote host.

  3. The data can be stored on any host depending on the definition of the table.

  4. The data will always be stored on disk.

  5. The data will always be stored in memory.

  6. The data will be stored according to the storage engine of the referenced table.


Answer: C, F


QUESTION: 132

The my.cnf file contains the following entries: innodb_data_home_dir =

innodb_data_file_path = /ibdata/ibdata1:50M;/disk2/ibdata2:50M:autoextend.Which of the following statements are true?


  1. The data files will be stored below the default data directory

  2. There are two InnoDB data files

  3. There are three InnoDB data files

  4. The total minimum size of the InnoDB data files is 100MB

  5. The total maximum size of the InnoDB data files is 100MB

  6. The initial size of the InnoDB data files on server startup will be set to 50MB. If more space is needed, another 50MB will be allocated.


Answer: B, D


QUESTION: 133

Which of the following statements are true regarding the data directory on a Windows

binary installation?


  1. A script needs to be run to initialize it after installation.

  2. It comes pre-initialized.

  3. You can choose to pre-initialize it or initialize it manually during the installation.


Answer: B


QUESTION: 134

Which types of startup options can be configured for the server?


  1. Location of important directories and files

  2. Logging settings

  3. Backup intervals

  4. Storage Engine dependent options

  5. Performance related settings

  6. The root password


Answer: A, B, D, E


QUESTION: 135

When will you be able to copy InnoDB table space files to other systems and use the data

there?


  1. You can always use them, because InnoDB files are platform independent.

  2. Both systems need to be either 32 Bit or 64 Bit platforms.

  3. Both systems need to run the same operating system.

  4. Both systems must be either little endian or big endian architecture.


Answer: A


QUESTION: 136

Isolation levels can be set...


  1. Per transaction

  2. Per user name

  3. Per session

  4. Globally


Answer: A, C, D


QUESTION: 137

Which of the following statements are true?


  1. InnoDB tables will be automatically recovered after a crash.

  2. MyISAM tables will be automatically recovered after a crash.

  3. InnoDB tables will be recovered after a crash if the innodb-recover option is configured.

  4. MyISAM tables will be recovered after a crash if the myisam-recover option is configured.

  5. InnoDB tables cannot be recovered after a crash, you have to restore data from backup.

  6. MyISAM tables cannot be recovered after a crash, you have to restore data from backup.


Answer: A, D


QUESTION: 138

When working with the InnoDB engine, which of the following correctly defines the

READ UNCOMMITTED isolation level?


  1. It allows a transaction to only see its uncommitted changes.

  2. It allows a transaction to see committed changes made by other transactions.

  3. It allows a transaction to see uncommitted changes made by other transactions.

  4. It allows a transaction to see both committed/uncommitted changes made by other

transactions.


Answer: C


QUESTION: 139

When choosing a storage engine for each of your tables, which things are to consider?


  1. Locking Characteristics: Some storage engines lock on row level, some on page level, some on table level.

  2. Transactions support: Some storage engines support transactions, some don't.

  3. Storage media: Some storage engines store data on disk, some in memory.

  4. Licenses: Some storage engines cannot be used in commercial environments, others can.

  5. Backup methods: Some storage engines support online backup and point in time recovery, some don't.


Answer: A, B, C, E


QUESTION: 140

Which two of the following statements best describe the purpose of the slow query log and

how you enable it?


  1. The slow log records the timestamps of when the server is performing slowly and when it is low on resources.

  2. The slow log records the text of all queries that exceed the long_query_time variable.

  3. The slow log is enabled with the --log-slow-queries or --log-slow-queries=file_name

    option.

  4. The slow log is enabled with the --log-slow or --log-slow=file_name option.


Answer: B, C


image

6$03/( 48(67,216


7KHVH TXHVWLRQV DUH IRU GHPR SXUSRVH RQO\ )XOO YHUVLRQ LV XS WR GDWH DQG FRQWDLQV DFWXDO TXHVWLRQV DQG DQVZHUV


.LOOH[DPV FRP LV DQ RQOLQH SODWIRUP WKDW RIIHUV D ZLGH UDQJH RI VHUYLFHV UHODWHG WR FHUWLILFDWLRQ H[DP SUHSDUDWLRQ 7KH SODWIRUP SURYLGHV DFWXDO TXHVWLRQV H[DP GXPSV DQG SUDFWLFH WHVWV WR KHOS LQGLYLGXDOV SUHSDUH IRU YDULRXV FHUWLILFDWLRQ H[DPV ZLWK FRQILGHQFH +HUH DUH VRPH NH\ IHDWXUHV DQG VHUYLFHV RIIHUHG E\ .LOOH[DPV FRP


$FWXDO ([DP 4XHVWLRQV .LOOH[DPV FRP SURYLGHV DFWXDO H[DP TXHVWLRQV WKDW DUH H[SHULHQFHG LQ WHVW FHQWHUV 7KHVH TXHVWLRQV DUH XSGDWHG UHJXODUO\ WR HQVXUH WKH\ DUH XS WR GDWH DQG UHOHYDQW WR WKH ODWHVW H[DP V\OODEXV %\ VWXG\LQJ WKHVH DFWXDO TXHVWLRQV FDQGLGDWHV FDQ IDPLOLDUL]H WKHPVHOYHV ZLWK WKH FRQWHQW DQG IRUPDW RI WKH UHDO H[DP


([DP 'XPSV .LOOH[DPV FRP RIIHUV H[DP GXPSV LQ 3') IRUPDW 7KHVH GXPSV FRQWDLQ D FRPSUHKHQVLYH FROOHFWLRQ RI TXHVWLRQV DQG DQVZHUV WKDW FRYHU WKH H[DP WRSLFV %\ XVLQJ WKHVH GXPSV FDQGLGDWHV FDQ HQKDQFH WKHLU NQRZOHGJH DQG LPSURYH WKHLU FKDQFHV RI VXFFHVV LQ WKH FHUWLILFDWLRQ H[DP


3UDFWLFH 7HVWV .LOOH[DPV FRP SURYLGHV SUDFWLFH WHVWV WKURXJK WKHLU GHVNWRS 9&( H[DP VLPXODWRU DQG RQOLQH WHVW HQJLQH 7KHVH SUDFWLFH WHVWV VLPXODWH WKH UHDO H[DP HQYLURQPHQW DQG KHOS FDQGLGDWHV DVVHVV WKHLU UHDGLQHVV IRU WKH DFWXDO H[DP 7KH SUDFWLFH WHVWV FRYHU D ZLGH UDQJH RI TXHVWLRQV DQG HQDEOH FDQGLGDWHV WR LGHQWLI\ WKHLU VWUHQJWKV DQG ZHDNQHVVHV


*XDUDQWHHG 6XFFHVV .LOOH[DPV FRP RIIHUV D VXFFHVV JXDUDQWHH ZLWK WKHLU H[DP GXPSV 7KH\ FODLP WKDW E\ XVLQJ WKHLU PDWHULDOV FDQGLGDWHV ZLOO SDVV WKHLU H[DPV RQ WKH ILUVW DWWHPSW RU WKH\ ZLOO UHIXQG WKH SXUFKDVH SULFH 7KLV JXDUDQWHH SURYLGHV DVVXUDQFH DQG FRQILGHQFH WR LQGLYLGXDOV SUHSDULQJ IRU FHUWLILFDWLRQ H[DPV


8SGDWHG &RQWHQW .LOOH[DPV FRP UHJXODUO\ XSGDWHV LWV TXHVWLRQ EDQN DQG H[DP GXPSV WR HQVXUH WKDW WKH\ DUH FXUUHQW DQG UHIOHFW WKH ODWHVW FKDQJHV LQ WKH H[DP V\OODEXV 7KLV KHOSV FDQGLGDWHV VWD\ XS WR GDWH ZLWK WKH H[DP FRQWHQW DQG LQFUHDVHV WKHLU FKDQFHV RI VXFFHVV


7HFKQLFDO 6XSSRUW .LOOH[DPV FRP SURYLGHV IUHH [ WHFKQLFDO VXSSRUW WR DVVLVW FDQGLGDWHV ZLWK DQ\ TXHULHV RU LVVXHV WKH\ PD\ HQFRXQWHU ZKLOH XVLQJ WKHLU VHUYLFHV 7KHLU FHUWLILHG H[SHUWV DUH DYDLODEOH WR SURYLGH JXLGDQFH DQG KHOS FDQGLGDWHV WKURXJKRXW WKHLU H[DP SUHSDUDWLRQ MRXUQH\


'PS .PSF FYBNT WJTJU IUUQT LJMMFYBNT DPN WFOEPST FYBN MJTU

.LOO \RXU H[DP DW )LUVW $WWHPSW *XDUDQWHHG