Ensurepass.com : Ensure you pass the IT Exams
2018 Jan Oracle Official New Released 1z0-883
100% Free Download! 100% Pass Guaranteed!
http://www.EnsurePass.com/1z0-883.html
MySQL 5.6 Database Administrator
Question No: 51
You want to start monitoring statistics on the distribution of storage engines that are being used and the average sizes of tables in the various databases.
Some details are as follows:
->The Mysql instance has 400 databases.
->Each database on an average consists of 25-50 tables.
You use the query:
SELECT TABLE_SCHEMA, ‘ENGINE’,
COUNT (*),
SUM (data_length) total_size
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ GROUP BY TABLE_SCHEMA, ‘ENGINE’
;
Why is this query slow to execute?
-
Counting and summarizing all table pages in the InnoDB shared tablespace is time consuming.
-
Collecting information requires various disk-level operations and is time consuming.
-
Aggregating details from various storage engine caches for the final output is time consuming.
-
Collecting information requires large numbers of locks on various INFORMATION_SCHEMA tables.
Answer: B
Question No: 52
When designing an InnoDB table, identify an advantage of using the BIT datatype Instead of one of the integer datatypes.
-
BIT columns are written by InnoDB at the head of the row, meaning they are always the first to be retrieved.
-
Multiple BIT columns pack tightly into a row, using less space.
-
BIT (8) takes less space than eight TINYINT fields.
-
The BIT columns can be manipulated with the bitwise operators amp;, |, ~, ^, lt;lt;, and gt;gt;.
The other integer types cannot.
Answer: B
Question No: 53
You are using CTIDS in replication. You need to skip a transaction with the CTID of aaa- bbb-ccc-ddd-eee : 3 on a slave.
Which command would you execute from a Mysql prompt?
-
STOP SLAVE;
SETGTID_NEXT=”aaa-bbb-ccc-ddd-eee: 3″; BEGIN;
COMMIT;
SET GTID_NEXT=”AUTOMATIC”; START SLAVE
-
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
-
STOP SLAVE; BEGIN;
SET GTID_IGNORE=”aaa-bbb-ccc-ddd-eee: 3″; COMMIT;
START SLAVE;
-
STOP SLAVE; RESET SLAVE; BEGIN;
SKIP NEXT GTID; COMMIT;
START SLAVE;
Answer: B
Reference: http://blog.secaserver.com/2011/11/resync-mysql-masterslave-replication/
Question No: 54
ROW-based replication has stopped working. You investigate the error log file and find the
following entries:
2013-08-27 14:15:47 9056 [ERROR] Slave SQL: Could not execute Delete_rows event on table test.t1; Can’t find record in ‘t1’, Error_code: 1032; handler error
HA_ERR_KEY_NOT_FOUND; the event’s master log 56_master-bin. 000003, end_log_pos 851, Error_code: 1032
2013-08-27 14:15:47 9056 [warning] Slave: Can’t find record in ‘t1’ Error_code: 1032
2013-08-27 14:15:47 9056 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log ’56_master-bin. 000003′ position 684
Why did you receive this error?
-
The slave SQL thread does not have DELETE privileges to execute on test.t1 table.s
-
The table definition on the slave -litters from the master.
-
Multi-threaded replication slaves can have temporary errors occurring for cross database updates.
-
The slave SQL thread attempted to remove a row from the test.t1 table, but the row did not exist.
Answer: D
Question No: 55
Full Atomicity, Consistency, Isolation, Durability (ACID) compliance is a necessity for a new application, which heavily reads and writes data.
This requires the following config file options:
Sync_binlog=1 Innodb_flush_log_at_trx_commit=1 Innodb_doublewrite=1
However, this configuration is expected to introduce disk I/O overhead. What three changes will reduce disk I/O overheads?
-
Use of soft links for database directories on the same physical disk
-
Use of separate directories on the same physical disk for log files and data files
-
Placement of InnoDB log files and datadir on separate physical disks
-
Allocation of RAM to the buffer pool such that more of the data can fit in RAM
-
Use of delay_key_write=ON for batch index update
Answer: C,D,E
Explanation: C (not A, not B): Use separate disk Not A: Using symbolic links
This means that, for MyISAM tables, you symlink the index file and data files from their usual location in the data directory to another disk (that may also be striped). This makes both the seek and read times better, assuming that the disk is not used for other purposes as well.
Reference: 8.11.3 Optimizing Disk I/O
Question No: 56
Mysqldump was used to create a single schema backup; Shellgt; mysqldump -u root -p sakila gt; sakila2013.sql
Which two commands will restore the sakila database without interfering with other running database?
-
Mysqlgt; USE sakila; LOAD DATA INFILE ‘sakila2013.sql’;
-
Shellgt; mysql -u root -p sakila lt;sakila2013.sql
-
Shellgt; mysqlimport -u root -p sakila sakila2013.sql
-
Shellgt; mysql -u root -p -e ‘use sakila; source sakila2013.sql’
-
Shellgt; mysql -u root -p -silent lt; sakila2013.sql
Answer: B,D
Explanation: C: If you need to restore a database that already exists, you#39;ll need to use mysqlimport command. The syntax for mysqlimport is as follows:
mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql] E: Basic syntax to restore:
mysql -u root -p[root_password] [database_name] lt; dumpfilename.sql
Reference: How to Back Up and Restore a MySQL Database
Question No: 57
Following a server crash, the automatic recovery of InnoDB fails. How would you begin to manually repair the InnoDB tables?
-
Start the server with the – innodb_force_recovery option set to a non-zero value.
-
Start the server as usual, and then execute the REPAIR TABLE command.
-
Start the server as usual, and then execute the CHECK TABLE command.
-
Start the server with the – innodb_recover_options option set to FORCE.
Answer: A
Question No: 58
Which two statements are true regarding partitioning in Mysql?
-
Tables with BLOB and TEXT columns cannot be partitioned.
-
Partitioning allows easier management of smaller data sets for certain queries.
-
Partitioning allows different columns to be stored in separate files.
-
The partitioning expression is an integer or function that returns an integer value or NULL value.
-
Partitioning is only available for those storage engines that implements it natively.
Answer: C,D
Question No: 59
Which two capabilities are granted with the SUPER privilege?
-
Allowing a client to kill other client connections
-
Allowing a client to shut down the server
-
Allowing change of the server runtime configuration
-
Allowing client accounts to take over the account of another user
Answer: A,B
Reference: http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html
Question No: 60
Which query would you use to find connections that are in the same state for longer than 180 seconds?
-
SHOW FULL PROCESSLIST WHEER Time gt; 180;
-
SELECT * FROM INFORMATION_SCHEMA.EVENTS SHERE STARTS lt; (DATE_SUB (NOW ( ), INTERVAL 180 SECOND) );
-
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE STATE lt; (DATE_SUB (NOW ( ), INTERVAL 180 SECOND) );
-
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME gt; 180;
Answer: D
Explanation: The show processlist cannot use where clause. mysqlgt; show full processlist where timegt;0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near #39;where timegt;0#39; at line 1
100% Ensurepass Free Download!
–Download Free Demo:1z0-883 Demo PDF
100% Ensurepass Free Guaranteed!
–1z0-883 Dumps
EnsurePass | ExamCollection | Testking | |
---|---|---|---|
Lowest Price Guarantee | Yes | No | No |
Up-to-Dated | Yes | No | No |
Real Questions | Yes | No | No |
Explanation | Yes | No | No |
PDF VCE | Yes | No | No |
Free VCE Simulator | Yes | No | No |
Instant Download | Yes | No | No |
100-105 Dumps VCE PDF
200-105 Dumps VCE PDF
300-101 Dumps VCE PDF
300-115 Dumps VCE PDF
300-135 Dumps VCE PDF
300-320 Dumps VCE PDF
400-101 Dumps VCE PDF
640-911 Dumps VCE PDF
640-916 Dumps VCE PDF
70-410 Dumps VCE PDF
70-411 Dumps VCE PDF
70-412 Dumps VCE PDF
70-413 Dumps VCE PDF
70-414 Dumps VCE PDF
70-417 Dumps VCE PDF
70-461 Dumps VCE PDF
70-462 Dumps VCE PDF
70-463 Dumps VCE PDF
70-464 Dumps VCE PDF
70-465 Dumps VCE PDF
70-480 Dumps VCE PDF
70-483 Dumps VCE PDF
70-486 Dumps VCE PDF
70-487 Dumps VCE PDF
220-901 Dumps VCE PDF
220-902 Dumps VCE PDF
N10-006 Dumps VCE PDF
SY0-401 Dumps VCE PDF