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’,
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.
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.
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?
SETGTID_NEXT=”aaa-bbb-ccc-ddd-eee: 3″; BEGIN;
SET GTID_NEXT=”AUTOMATIC”; START SLAVE
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
STOP SLAVE; BEGIN;
SET GTID_IGNORE=”aaa-bbb-ccc-ddd-eee: 3″; COMMIT;
STOP SLAVE; RESET SLAVE; BEGIN;
SKIP NEXT GTID; COMMIT;
Question No: 54
ROW-based replication has stopped working. You investigate the error log file and find the
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.
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
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
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.
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.
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
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;
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
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|