1/8
MySQL
Q1. What are some of the advantages of using MySQL?
· Flexibility: MySQL runs on all operating systems
· Power: MySQL focuses on performance
· Enterprise-Level SQL Features: MySQL had for some time been lacking in advanced
features such as subqueries, views, and stored procedures.
· Full-Text Indexing and Searching
· Query Caching: This helps enhance the speed of MySQL greatly
· Replication: One MySQL server can be duplicated on another, providing numerous
advantages
· Configuration and Security
· Cluster
Q2. How can you interact with MySQL?
There are three main ways you can interact with MySQL:
· using a command line
· via a web interface
· through a programming language
Q3. What are the types of relationships used in MySQL?
There are three categories of relationships in MySQL:
· One-to-One: Usually, when two items have a one-to-one relationship, you just include them
as columns in the same table.
· One-to-Many: One-to-many (or many-to-one) relationships occur when one row in one
table is linked to many rows in another table.
· Many-to-Many: In a many-to-many relationship, many rows in one table are linked to many
rows in another table. To create this relationship, add a third table containing the same key
column from each of the other tables
Q4. Can you explain the logical architecture of MySQL?
The top layer contains the services most network-based client/server tools or servers need
such as connection handling, authentication, security, and so forth.
The second layer contains much of MySQL’s brains. This has the code for query parsing,
analysis, optimization, caching, and all the built-in functions.
2/8
The third layer contains the storage engines that are responsible for storing and retrieving
the data stored in MySQL.
Q5. MySQL supported storage engines:
· InnoDB.
· MyISAM.
· Memory.
· CSV.
· Merge.
· Archive.
· Federated.
· Blackhole.
Q6. What is the Isolation Level in MySQL?
Isolation (I) is one of the properties from ACID. It defines how each transaction is isolated
from other transactions and is a critical component of application design. As per the
SQL:1992 standard, InnoDB has four types of Isolation levels. Below, I have listed the types
in order, and each transaction isolation level provides better consistency compared to the
previous one.
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ – ( MySQL’s DEFAULT )
SERIALIZABLE
3/8
Q7. What are the difference between MyISAM and InnoDB.
- MyISAM does no longer support transactions, however InnoDB supports transaction
- MyISAM helps table level locking, how ever InnoDB supports Row-level locking
- MySQAM helps full text search, Innodb full text search is not very mature.
- MyISAM designed for the need for speed but InnoDB designed for most performance.
- MyISAM does not aid overseas keys, but InnoDB helps foreign Keys.
- We can commit and rollback with InnoDB not in MyISAM
- MyISAM does no longer assist ACID but Innodb does.
Q8. MySQL InnoDB tuning parameters.
innodb_buffer_pool_size
innodb_log_file_size
innodb_flush_log_at_trx_commit
innodb_flush_method
innodb_file_per_table
innodb_buffer_pool_instances
Q9. What is the package MySQL-shared-compat-5.6.11-2.el6.x86_64.rpm for. When would
you use it?
It is used for backward compatibility during upgrades
Q10. Which backup methods do you know of?
The backup method i know is below cold backup
mysqldump
mysqlhotcopy (It's only for myisam)
Hot backup : xtradb backup, mysql enterprise backup
4/8
Q11. What do you need for a Point-in-Time-Recovery? How do you perform a PiTR?
All the changes in the database can be backed up by backup tool like mysqldump. But the
current
active transaction which is going will be in the binary logs
The backup can be restore from the backup file. But the current transaction can only be
fetched by
point in time recovery tool mysqlbinlog. It can also take incremental backup based on date.
Log
file and log position based.
Q12. What is the most important server variable to configure the MyISAM Storage Engine?
What is it used
for and how would you size this server variable?
MyISAM Storage Engine: Key_buffer_size
It is used for myisam indexes. This variable should be sized based on total myism data size
5/8