MySQL interview questions

• How do you start and stop MySQL on Windows?

Command prompt.net start MySQL, net stop MySQL

• How do you start MySQL on Linux?

/etc/init.d/mysql start

• What’s the default port for MySQL Server?

3306

• Can you save your connection settings to a conf file?

Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it’s not readable by others

• How do you change a password for an existing user via mysqladmin?

mysqladmin -u root -p password "newpassword"

• Use mysqldump to create a copy of the database?

mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql

References:
http://www.techpreparation.com/computer-interview-questions/mysql-interview-questions-answers6.htm

• how to query date range?

mysql date range
and event_date between '2009-01-19' and '2009-01-25'
or
and event_date >='2009-01-19'
and event_date <='2009-01-25'

• how to connect remote mysql database?

mysql -h hostIP -u username -p  --database=databasename

• The BLOB and TEXT Types

A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold. The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four BLOB types and have the same maximum lengths and storage requirements. See Section 11.2, “Data Type Storage Requirements”.

BLOB values are treated as binary strings (byte strings). They have no character set, and sorting and comparison are based on the numeric values of the bytes in column values. TEXT values are treated as nonbinary strings (character strings). They have a character set, and values are sorted and compared based on the collation of the character set.

https://dev.mysql.com/doc/refman/5.0/en/blob.html

• What is the difference between BLOB AND TEXT?

A BLOB is a binary large object that can hold a variable amount of data. There are four types of BLOB –
    TINYBLOB
    BLOB
    MEDIUMBLOB and
    LONGBLOB
They all differ only in the maximum length of the values they can hold.

A TEXT is a case-insensitive BLOB. The four TEXT types
    TINYTEXT
    TEXT
    MEDIUMTEXT and
    LONGTEXT
They all correspond to the four BLOB types and have the same maximum lengths and storage requirements.
The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive for BLOB values and case-insensitive for TEXT values.
http://www.gointerviews.com/top-50-mysql-interview-questions-answers/#vGT8u8EJSFsxrp87.99

• What is the difference between primary key and candidate key?

Every row of a table is identified uniquely by primary key. There is only one primary key for a table.
Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.
http://www.gointerviews.com/top-50-mysql-interview-questions-answers/#vGT8u8EJSFsxrp87.99

• What does myisamchk do?

It compress the MyISAM tables, which reduces their disk or memory usage.
http://www.gointerviews.com/top-50-mysql-interview-questions-answers/#vGT8u8EJSFsxrp87.99

• What are federated tables?

Federated tables which allow access to the tables located on other databases on other servers.
http://www.gointerviews.com/top-50-mysql-interview-questions-answers/#vGT8u8EJSFsxrp87.99

• What is HEAP table?

Tables that are present in the memory are called as HEAP tables.
These tables are now more commonly known as memory tables.
These memory tables never have values with data type like “BLOB” or “TEXT”.
They use indexes which make them faster.
http://careerride.com/MySQL-HEAP-table.aspx

HEAP tables are present in memory and they are used for high speed storage on temporarybasis.
BLOB or TEXT fields are not allowed
Only comparison operators can be used =, <,>, = >,=<
AUTO_INCREMENT is not supported by HEAP tables
Indexes should be NOT NUL

http://www.gointerviews.com/top-50-mysql-interview-questions-answers/#vGT8u8EJSFsxrp87.99

• What are the different tables present in MySQL?

Total 5 types of tables are present:

    MyISAM
    Heap
    Merge
    INNO DB
    ISAM

MyISAM is the default storage engine as of MySQL .

• What is ISAM?

ISAM  is abbreviated as Indexed Sequential Access Method.
It was developed by IBM to store and retrieve data on secondary storage systems like tapes.

• What is InnoDB?

lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now.
http://www.gointerviews.com/top-50-mysql-interview-questions-answers/#vGT8u8EJSFsxrp87.99

• 13. Explain advantages of InnoDB over MyISAM? 

Row-level locking, transactions, foreign key constraints and crash recovery.

• 14. Explain advantages of MyISAM over InnoDB? 

Much more conservative approach to disk space management – each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.

• How are the tables handled in the case of MERGE engine and with partionining?

The extremely large datasets can be stored using MERGE engine or by partitioned tables.
The MERGE engine is similar to implementation of a VIEW concept for all tables with something similar to UNION ALL.
This will have quick inserts and deletes from the datasets, but not as efficient as partitions in storage aspects.

• Are there any security flaws in MySQL that you are aware of?

The one main security flaw is in creating the database names. The database names that are starting with the word “test” are by default taken as experimental tables meant for test purposes. So it is given the access permissions so that everyone can access. This has to be taken care of while naming the databases.

• What is the difference between mysql and mysqli interfaces?

The mysqli interface is meant for the Object oriented library access from the PHP.

• What is the console option meant for?

The console option is used to redirect the logs and print outputs of the mysql engine to the console. If it is not used then the mysql engine redirects output to the .err log files. This can represent a considerable overhead to the server when we are dealing with busy system with huge databases.

• How can you back up and restore the databases in MySQL?

The mysqldump is used with the < and > symbols to back up and restore the specific database. The exact usage is
shell> mysqldump –user=username –password=password databasename > backupfile.sql
Shell> mysqldump –user=username –password=password databasename < backupfile.sql
The created .sql file internally has all the statements that have to executed to create the specific database from scratch. We can also have the values altered using editors over this text formatted file before retoring it onto another machine.

http://www.totalinterviewquestions.com/2010/12/17/mysql-dba-interview-questions-and-answers-part1-425/

• 1. Describe MySQL architecture.

Answer: MySQL has got the logical architecture as follows
A. Connection Manager
B. Query Optimizer
C. Pluggable Engines.

• 2. What are the major differences between MySQL 5.1 and 5.5?

Answer: The Major difference I know is that the default engine was myisam in 5.1 and innodb in 5.5

• 3. What are the different database engines available in MySQL? (explain)

Answer: Following are the highly used engines available in mysql

A. MyISAM
B. INNODB
C. Memory
D. Federated
E. CSV

mysql> show engines;
----------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables
| MyISAM             | YES     | MyISAM storage engine
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)
| CSV                | YES     | CSV storage engine
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables    
| ARCHIVE            | YES     | Archive storage engine
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys
| PERFORMANCE_SCHEMA | YES     | Performance Schema
                | NO           | NO   | NO         |
+--------------------+---------+---------------------

http://dba-diary.blogspot.com/2012/12/mysql-dba-interview-questions-and.html

• 4. What are the major differences between MyISAM and InnoDB?

Answer: Following are the differences between InnoDB and MyISAM
A. MyISAM does not support transactions whereas InnoDB does.
B. InnoDB supports multi versioning concurrency control.
C. MyISAM uses table level locks and InnoDB uses row level locking.

• 10. How do you find out slow queries in mysql?

Answer : By enabling the slow query log as follows
SET GLOBAL slow_query_log = 1;

• 13. If the mysql server is performing slow than how to find out the process which is causing problem.

Answer : show processlist

• 22. How to take incremental backup in MySQL?

Answer : Using percona xtrabackup

• 26. What do you do if the data disk is full?

Answer : if the data disk is full then create a soft link and move the .frm and .idb files to the linked location.

http://dba-diary.blogspot.com/2012/12/mysql-dba-interview-questions-and.html

•  What are ENUMs used for in MySQL?

 You can limit the possible values that go into the table.


(tested mysql 5.6 community server)
CREATE TABLE months(month ENUM ('January','February', 'March'));
INSERT INTO months VALUES ('April');
INSERT INTO months VALUES ('March');
select * from months;

CREATE TABLE Test(NY ENUM('Y','N') DEFAULT 'N',

Size ENUM('S','M','L','XL','XXL'),

Color ENUM('Black','Red','White'));

Insert into Test (NY, Size, Color) values ('Y','S','Black');

select * from Test;

Insert into Test (NY, Size, Color) values ('Y','S','yellow');

http://mysqlboss.blogspot.com/