Are you preparing for Oracle MySQL 5.6 Database Administrator interview? Oracle MySQL 5.6 Database Administrator is the one who does the operations like creating, managing the database. It is not different to DBA, here MySQL is used as the query language. Candidates are supposed to have basic knowledge on the roles and responsibilities of database administrator. Many companies are in need of such people. There is less number of people who are skilled in database concepts. There is huge demand for this technology in the market. Opportunities are everywhere for this technology. Good knowledge on how the process is done will fetch you the job. Wisdomjobs framed Oracle MySQL 5.6 Database Administrator interview questions and answers to make it easier for your interview preparation.
The intensive validation of input data that some settings requires more time than if the validation is not done. While the performance impact is not that great, if you do not require such validation (perhaps your application already handles all of this), then MySQL gives you the option of leaving strict mode disabled. However, if you do require it, strict mode can provide such validation.
Yes ,A subquery is a SELECT statement within another statement.
All subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.
Here is an example of a subquery:
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
Yes. The query cache operates on the server level, caching complete result sets matched with the original query string. If an exactly identical query is made (which often happens, particularly in web applications), no parsing or execution is necessary; the result is sent directly from the cache. Various tuning options are available.
No. However, MySQL has an AUTO_INCREMENT system, which in MySQL 5.6 can also handle inserts in a multi-master replication setup. With the auto_increment_increment and auto_increment_offset system variables, you can set each server to generate auto-increment values that don't conflict with other servers. The auto_increment_increment value should be greater than the number of servers, and each server should have a unique offset.
Yes. MySQL is fully multi-threaded, and will make use of multiple CPUs, provided that the operating system supports them.
When using LinuxThreads, you should see a minimum of three mysqld(mysqld, also known as MySQL Server, is the main program that does most of the work in a MySQL installation.
MySQL Server manages access to the MySQL data directory that contains databases and tables. The data directory is also the default location for other information such as log files and status files). processes running.
These are in fact threads. There is one thread for the LinuxThreads manager, one thread to handle connections, and one thread to handle alarms and signals.
Yes. All current MySQL versions support transactions. The InnoDB storage engine offers full ACID transactions with row-level locking, multi-versioning, nonlocking repeatable reads, and all four SQL standard isolation levels.
The NDB storage engine supports the READ COMMITTED transaction isolation level only.
Server SQL modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers. The MySQL Server apply these modes individually to different clients.
Yes. MySQL 5.6 supports two types of stored routines, stored procedures and stored functions.
Nondeterministic (random) or time-based actions embedded in stored procedures may not replicate properly. By their very nature, randomly produced results are not predictable and cannot be exactly reproduced, and therefore, random actions replicated to a slave will not mirror those performed on a master. Declaring stored functions to be DETERMINISTIC or setting the log_bin_trust_function_creators system variable to 0 will not allow random-valued operations to be invoked.
In addition, time-based actions cannot be reproduced on a slave because the timing of such actions in a stored procedure is not reproducible through the binary log used for replication. It records only DML events and does not factor in timing constraints.
Finally, nontransactional tables for which errors occur during large DML actions (such as bulk inserts) may experience replication issues in that a master may be partially updated from DML activity, but no updates are done to the slave because of the errors that occurred. A workaround is for a function's DML actions to be carried out with the IGNORE keyword so that updates on the master that cause errors are ignored and updates that do not cause errors are replicated to the slave.
Yes. Because a slave server has authority to execute any statement read from a master's binary log, special security constraints exist for using stored functions with replication. If replication or binary logging in general (for the purpose of point-in-time recovery) is active, then MySQL DBAs have two security options open to them:
Any user wishing to create stored functions must be granted the SUPER privilege.
Alternatively, a DBA can set the log_bin_trust_function_creators system variable to 1, which enables anyone with the standard CREATE ROUTINE privilege to create stored functions.
MySQL records each DML event that occurs in a stored procedure and replicates those individual actions to a slave server. The actual calls made to execute stored procedures are not replicated.
Stored functions that change data are logged as function invocations, not as the DML events that occur inside each function.
Yes, creation of stored procedures and functions carried out through normal DDL statements on a master server are replicated to a slave, so the objects will exist on both servers. ALTER and DROP statements for stored procedures and functions are also replicated.
Yes. However, you cannot perform transactional operations within a stored function.
You might consider disabling the change buffer if the entire data set fits within the InnoDB buffer pool, if you have relatively few secondary indexes, or if you are using solid-state storage, where random reads are about as fast as sequential reads. Before making configuration changes, it is recommended that you run tests using a representative workload to determine if disabling the change buffer provides any benefit.
The change buffer is a feature designed to reduce random I/O to secondary indexes as indexes grow larger and no longer fit in the InnoDB buffer pool. Generally, the change buffer should be used when the entire data set does not fit into the buffer pool, when there is substantial DML activity that modifies secondary index pages, or when there are lots of secondary indexes that are regularly changed by DML activity.
Updated pages are flushed by the same flushing mechanism that flushes the other pages that occupy the buffer pool.
When a page is read into the buffer pool, buffered changes are merged upon completion of the read, before the page is made available.
Change buffer merging is performed as a background task. The innodb_io_capacity parameter sets an upper limit on the I/O activity performed by InnoDB background tasks such as merging data from the change buffer.
A change buffer merge is performed during crash recovery. Changes are applied from the change buffer (in the system tablespace) to leaf pages of secondary indexes as index pages are read into the buffer pool.
The change buffer is fully durable and will survive a system crash. Upon restart, change buffer merge operations resume as part of normal operations.
A full merge of the change buffer can be forced as part of a slow server shutdown using --innodb-fast-shutdown=0.
The current size of the change buffer is reported by SHOW ENGINE INNODB STATUS G, under the INSERT BUFFER AND ADAPTIVE HASH INDEX heading. For example:
INSERT BUFFER AND ADAPTIVE HASH INDEX
Ibuf: size 1, free list len 0, seg size 2, 0 merges
Relevant data points include:
size: The number of pages used within the change buffer. Change buffer size is equal to seg size - (1 + free list len). The 1 + value represents the change buffer header page.
seg size: The size of the change buffer, in pages.
Prior to the introduction of the innodb_change_buffer_max_size configuration option in MySQL 5.6, the maximum size of the on-disk change buffer in the system tablespace was 1/3 of the InnoDB buffer pool size.
In MySQL 5.6 and later, the innodb_change_buffer_max_size configuration option defines the maximum size of the change buffer as a percentage of the total buffer pool size. By default, innodb_change_buffer_max_size is set to 25. The maximum setting is 50.
InnoDB does not buffer an operation if it would cause the on-disk change buffer to exceed the defined limit.
Change buffer pages are not required to persist in the buffer pool and may be evicted by LRU operations.
No. The change buffer only supports secondary indexes. Clustered indexes, full-text indexes, and spatial indexes are not supported. Full-text indexes have their own caching mechanism.
Buffering secondary index changes when secondary index pages are not in the buffer pool avoids expensive random access I/O operations that would be required to immediately read in affected index pages from disk. Buffered changes can be applied later, in batches, as pages are read into the buffer pool by other read operations.
INSERT, UPDATE, and DELETE operations can modify secondary indexes. If an affected index page is not in the buffer pool, the changes can be buffered in the change buffer.
Oracle MySQL 5.6 Database Administrator Related Tutorials
|Oracle 10g Tutorial||Python Tutorial|
|C++ Tutorial||C Tutorial|
|SQL Server 2008 Tutorial||Oracle DBA Tutorial|
|Oracle 11g Tutorial||Database Testing Tutorial|
|H2 Database Tutorial|
Oracle MySQL 5.6 Database Administrator Related Interview Questions
|Oracle 10g Interview Questions||Python Interview Questions|
|C++ Interview Questions||C Interview Questions|
|SQL Server 2008 Interview Questions||Oracle DBA Interview Questions|
|Oracle 11g Interview Questions||Database Testing Interview Questions|
|MYSQL DBA Interview Questions||Database Design Interview Questions|
|Database Administration Interview Questions||Oracle DBA Troubleshooting Interview Questions|
|H2 Database Interview Questions||Advanced Linux Interview Questions|
Oracle MySQL 5.6 Database Administrator Related Practice Tests
|Oracle 10g Practice Tests||Python Practice Tests|
|C++ Practice Tests||C Practice Tests|
|SQL Server 2008 Practice Tests||Oracle DBA Practice Tests|
|Oracle 11g Practice Tests||Database Testing Practice Tests|
|MYSQL DBA Practice Tests||Database Design Practice Tests|
|Database Administration Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.