MYSQL SERVER

MySQL has the following technical specifications -

• • Flexible structure

  • High performance

  • Manageable and easy to use

  • Replication and high availability

  • Security and storage management

  • Drivers

  • Graphical Tools

  • MySQL Enterprise Monitor

  • MySQL Enterprise Security

  • JSON Support

  • Replication & High-Availability

  • Manageability and Ease of Use

  • OLTP and Transactions

  • Geo-Spatial Support

https://lalitvc.wordpress.com/2016/11/03/mysql-architecture-and-components/

What are the different tables present in MySQL?

There are many tables that remain present by default. But, MyISAM is the default database engine used in MySQL. There are five types of tables that are present:

• • MyISAM

  • Heap

  • Merge

  • INNO DB

  • ISAM

What is BLOB and TEXT in MySQL?

BLOB is an acronym stands for a large binary object. It is used to hold a variable amount of data.

There are four types of BLOB.

• 1. TINYBLOB

  2. BLOB

  3. MEDIUMBLOB

  4. LONGBLOB

The differences among all these are the maximum length of values they can hold.

TEXT is a case-insensitive BLOB. TEXT values are non-binary strings (character string). They have a character set, and values are stored, and compared based on the collation of the character set.

There are four types of TEXT.

• 1. TINYTEXT

  2. TEXT

  3. MEDIUMTEXT

  4. LONGTEXT

What is the difference between MySQL_connect and MySQL_pconnect?

Mysql_connect:

• 1. It opens a new connection to the database.

  2. Every time you need to open and close database connection, depending on the request.

  3. Opens page whenever it is loaded.

Mysql_pconnect:

• 1. In Mysql_pconnect, "p" stands for persistent connection so it opens the persistent connection.

  2. The database connection cannot be closed.

  3. It is more useful if your site has more traffic because there is no need to open and close connection frequently and every time when page is loaded.

What does "i_am_a_dummy flag" do in MySQL?

The "i_am_a_dummy flag" enables MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present. Hence it can save the programmer from deleting the entire table my mistake if he does not use WHERE clause.

How to change a password for an existing user via Mysqladmin?

Mysqladmin -u root -p password "newpassword".

What is SQLyog?

SQLyog program is the most popular GUI tool for admin. It is the most popular MySQL manager and admin tool. It combines the features of MySQL administrator, phpMyadmin and others MySQL front ends and MySQL GUI tools.

What is the default port of MySQL Server?

The default port of MySQL Server is 3306.

 How is MyISAM table stored?

MyISAM table is stored on disk in three formats.

• • '.frm' file : storing the table definition

  • '.MYD' (MYData): data file

  • '.MYI' (MYIndex): index file

What is MySQL data directory?

MySQL data directory is a place where MySQL stores its data. Each subdirectory under this data dictionary represents a MySQL database. By default the information managed my MySQL = server mysqld is stored in data directory.

What is InnoDB?

InnoDB is a storage database for SQL. The ACID-transactions are also provided in addition InnoDB also includes support for the foreign key. Initially owned by InnobaseOY now belongs to Oracle Corporation after it acquired the latter since 2005.

What is ISAM?

It is a system for file management developed by IBM which allows records to access sequentially or even randomly.

Main differences between INNODB and MYISAM commonly used storage engine