WHAT IS DATABASE:
A database is a collection of
information
that is organized so that it can easily be accessed, managed, and
updated.
1. What is mysql.
Mysql is an most papular open source
RDBMS(Relational Database Management System).It is using SQL
(Structured Query Language). Any one can download it and easy to use
and manage.
2.What are the features of Mysql.
1.Cross-platform support.
2.Wide range of application programming interfaces.
3. Stored procedures,Triggers,Cursor.
3.What is the default port for MySQL Server
3306.
4. Why Mysql Widely used.
Mysql is open source and free to use .Also it is reliable and
fast.
5. What is DDL, DML and DCL ?
DDL:- DDL stands for Data Definition Language.Its
deals with database schemas and descriptions of how the data should
reside in the database.Example:- CREATE TABLE or ALTER TABLE,
DML:- DML stands for data manipulation
Language.Its deals with SELECT, INSERT, UPDATE , DELETE etc.
DCL:- DCL stand for Data Control Language.Its
contain commands like GRANT,Revoke etc.
6. Difference between FLOAT, DOUBLE and REAL.
Float:- FLOATs store floating point numbers with
8 place accuracy and take up 4 bytes.
Double:- DOUBLEs store floating point numbers
with 16 place accuracy and take up 8 bytes.
Real:- Real contain 8 place accuracy and take 4
bytes.
7. What are the Joins in Mysql.
Joins are used in an MySql statement to query data from two or
more tables, based on a relationship between certain columns in these
tables.
8. Explain Different Joins in MySql.
The different joins in mysql are:-
Inner Join:- Return rows when there is at least
one match in both tables.
Left Join:- Return all rows from the left
table, even if there are no matches in the right table.
Right Join:- Return all rows from the right
table, even if there are no matches in the left table.
Full Join:- Return rows when there is a match in
one of the tables.
9. Is Mysql query is case sensitive.
No,mysql queries are not case sensitive.
10. What are all the Common MySql Function.
NOW():- Returns the current date and time as one
value.
CURRDATE() :- Returns the current date or
time.
CURRTIME() :- Returns the current date or
time.
CONCAT(X, Y) :- Concatenates two string
values to create a single string output. Mostly used to combine two
or more fields into one single field.
DATEDIFF(X, Y) :- The difference between
two dates.
11. Explain DISTINCT Statement in MySql.
In table some column may be contain the duplicates value but you
want to show only same value once not again and again.he DISTINCT
keyword can be used to return only distinct (different) values not
have two same values.
Example:-
SELECT DISTINCT Column_name FROM table_name;
12. How MySQL Optimizes DISTINCT.
DISTINCT is converted to a GROUP BY on all columns and it will be
combined with ORDER BY clause.
Example query:-
SELECT DISTINCT table1.a FROM table1,table2 where
table1.a=table2.a;
13. Difference between CHAR and VARCHAR.
CHAR column length is fixed to the length that is declared while
creating table. The length value ranges from 1 and 255.Varchar get
space according to data interred in column.
14. Difference between CHAR_LENGTH and LENGTH
The CHAR_LENGTH for character count and LENGTH for byte count.
15.How get second highest salary from database
table.
For getting second highest salary you will use limit.Example as:-
select salary from table order by salary desc limit 1,1;
16. What are HEAP tables.
HEAP tables are in-memory. They are usually used for high-speed
temporary storage.
No TEXT or BLOB fields are allowed within HEAP tables.
Only use the comparison operators = and <=>.
HEAP tables do not support AUTO_INCREMENT.
Indexes must be NOT NULL.
17. How do you concatenate strings in MySQL.
CONCAT (string1, string2, string3)
18. How to control the max size of a HEAP table.
Using max_heap_table_size.
19. How many columns can be used for creating
Index.
Maximum of 16 indexed columns can be created for any table.
20. Different string types available for
column in database.
SET
BLOB
ENUM
VARCHAR
TEXT
CHAR
21. 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.
Thee COUNT(*)s execute slower than in MyISAM due to tablespace
complexity
22. Advantages of InnoDB over MyISAM.
Transactions
Row-level locking
Crash recovery
Foreign key constraints
23. Where MyISAM table will be stored and also
give their formats of storage.
Each MyISAM table is stored on disk in three formats:-
1. The data file has a ‘.MYD’ (MYData) extension.
2. The ‘.frm’ file stores the table definition.
3. The index file has a ‘.MYI’ (MYIndex) extension.
24. How do you get a portion of a string?
SELECT SUBSTR(title, 1, 10) from user.
25. How to get current MySQL version.
Using SELECT VERSION ();
26. Difference between mysql and mysqli
interfaces in PHP.
mysqli is the object-oriented version of mysql library functions.
27. What storage engines are used in MySQL.
Storage engines are called table types and data is stored in files
using various techniques.
Like Indexing,Locking levels,Capabilities and functions.
28. What do _ and % mean inside LIKE statement?
_ is exactly one character.
% corresponds to 0 or more characters.
29. 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.
30. What are the different table present in
MYsql?
MyISAM:- This is default. Based on Indexed
Sequntial Access Method. The above SQL will create a MyISA table.
HEAP : - Fast data access, but will loose data if
there is a crash. Cannot have BLOB, TEXT & AUTO INCRIMENT fields.
ISAM :- Based on Indexed Sequntial Access
Method.
InoDB :- Supports Transactions using COMMIT
& ROLLBACK
BDB :- Supports Transactions using COMMIT &
ROLLBACK. Slower that others.
31. Difference between mysql_fetch_array and
mysql_fetch_object.
mysql_fetch_object :- Fetch a result row as an
OBJECT.
mysql_fetch_array :- Fetch a result row as an
associative ARRAY, a numeric array, or both
32. How do you start MySQL on Linux.
/etc/init.d/mysql start
33. How do you login to MySql using Unix shell.
Using command
# [mysql dir]/bin/mysql -h hostname -u <UserName> -p
<password>
34. How do you change a password for an existing
user via mysqladmin.
Using command
mysqladmin -u root -p password “newpassword”
35. What does myisamchk do.
It compress the MyISAM tables, which reduces their disk or memory
usage.
36. What are federated tables.
Federated tables which allow access to the tables located on other
databases on other servers.
37. How can you see all indexes defined for a
table.
SHOW INDEX FROM <tablename>;
38. What are ENUMs used for in MySQL.
You can limit the possible values that go into the table.
CREATE TABLE months (month ENUM ‘January’, ‘February’,
‘March’,…);
INSERT months VALUES (’April’);
39. How to Use mysqldump to create a copy of the
database.
mysqldump -h mysqlhost -u username -p mydatabasename >
dbdump.sql
40. How can we get the number of rows affected by
query.
SELECT COUNT (ID) FROM users;
41. How to display top 100 rows from table.
Using query
SELECT * table_name FROM LIMIT 0,100
42. Difference between BLOB AND TEXT.
BLOB:- A BLOB is a binary large object that can
hold a variable amount of data.
There are 4 types of BLOB.
Tinyblob ,Blob, Mediumblob and Longblob.
TEXT:- A TEXT is a case-insensitive BLOB.
There are also 4 types of text:-
TINYTEXT,TEXT,MEDIUMTEXT ,LONGTEXT
43. 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.
44. What is InnoDB.
lnnoDB is a transaction safe storage engine developed by Innobase
Oy which is a Oracle Corporation now.
45. Difference between NOW() and CURRENT_DATE().
NOW () command is used to show current year,month,date with
hours,minutes and seconds.
CURRENT_DATE() shows current year,month and date only.
46. How do I find out all databases starting with
‘user’ to which I have access to.
SHOW DATABASES LIKE ‘user%’;
47. How would you change a table to InnoDB.
ALTER TABLE user ENGINE innodb;
48. How do you convert a string to UTF-8.
SELECT (user USING utf8);
49. How would you delete a column.
ALTER TABLE user DROP user_id.
50. How many TRIGGERS are allowed in MySql table.
The various triggers allowed in mysql are:-
BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE
51. How do you get the month from a timestamp.
SELECT MONTH(user_timestamp) from user_questions;
52. Difference between the LIKE and REGEXP
operators.
Like are expressed using %.
For example:-
SELECT * FROM user WHERE user_name LIKE “%b”;
REGEXP are expressed using ^.
For example:-
SELECT * FROM user WHERE user_name REGEXP “^b”;
53. Difference between Unix timestamps and MySQL
timestamps.
Unix timestamps are stored as 32-bit integers.
MySQL timestamps are stored in a similar manner, but represented
in readable YYYY-MM-DD HH:MM:SS format.
54. What is the difference between MyISAM Static
and MyISAM Dynamic.
In MyISAM static all the fields will have fixed width. The Dynamic
MyISAM table will have fields like TEXT, BLOB, etc. to accommodate
the data types with various lengths.
MyISAM Static would be easier to restore in case of corruption.
How to view a list of MySQL
users and their privileges
mysql> select user from
mysql.user;
How to create user in Mysql /
with previliges
mysql>
create user 'arman'@'localhost' IDENTIFIED BY 'password';
mysql> grant
all privileges on berkshire_new_cluster.* to barkat@'%' identified by
'password'
57. Describe MySQL
architecture.
Answer: MySQL has got the logical architecture
as follows
A. Connection Manager
B. Query Optimizer
C.
Pluggable Engines.
58. 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
59. 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
60. 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.
61. Which engine
would you prefer for production OLTP environment and why?
Answer:
InnoDB (to be transaction safe)
62. What are the best
installation practices for MySQL?
Answer: there are following
installation methods available
A. Binary Installation
B.
RPM Installation
C. Source Code compilation
after
installation you should change the location of bin logs and
datafiles on the different physical disks.
63. Which RAID
level is best suited for MySQL?
Answer: RAID 10
64. How
do you upgrade from one mysql version to another mysql
version?
Answer: create a slave on newer version and
change it to MASTER.
65. How many types of logs are there in
mysql?
Answer: General Log, Error Log, Binary Log and Slow
Query Log
66. How do you find out slow queries in
mysql?
Answer : By enabling the slow query log as follows
SET
GLOBAL slow_query_log = 1;
67. How do you go through the
MySQL slow query log?
Answer : slow query log might be very
huge in size and query could be listed thousand times. to summarize
the slow query log in a very informative way there is third party
tool available 'pt-query-digest' which is a part of percona tool kit
freely downloadable.
68. How do you check the uptime of a
mysql server?
Answer : following command gives you the system
status
status
69. If the mysql server is
performing slow than how to find out the process which is causing
problem.
Answer : show processlist
70. What do you do
if the end user complains about the performance of the DB?
Answer
: show processlist will show the processes which taking resources at
db server and the do the rest diagnosis.
71. What do you do
about the slow queries?
Answer : study the explain plan and
create necessary indexes if required.
72. Where do you change
the performance parameters of mysql and by default where is the file
located on a unix system?
Answer : my.cnf. this file is
available under /etc/my.cnf
73. Which are the important
performance parameters for MyISAM and InnoDB?
Answer : For
MyISAM
key_cache_size
thread_cache_size
tmp_table_size
max_heap_table_size
read_buffer_size
query_cache
For
InnoDB
innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_log_file_size
innodb_log_buffer_size
tmp_table_size
max_heap_table_size
table_open_cache
74.
Should we have Query_cache enabled?
Answer : in many caches
yes.
75. what are the disadvantages of having a big Query cache
size?
Answer : query cache puts an additional overhead on the
database. it required the db to work on invalidating the queries
from the query cache.
76. what should be the optimum size of
InnoDB buffer cache?
Answer : it should be the 70-80% of the
memory available.
77. How do you backup InnoDB
tables?
Answer : there are two ways of taking backup
1.
mysqldump with --single-transaction
2. xtrabackup (part of
percona)
78. How to take incremental backup in MySQL?
Answer
: Using percona xtrabackup
79. Can the database be renamed in
MySQL?
Answer : No.
80. How to check the table
fragmentation and resolve if the fragmentation is found?
Answer
: following query will list all the fragmented tabes
SELECT
TABLE_SCHEMA, TABLE_NAME, CONCAT(ROUND(data_length / ( 1024 * 1024
), 2), 'MB') DATA, CONCAT(ROUND(data_free / ( 1024 * 1024 ),
2), 'MB')FREE from information_schema.TABLES where TABLE_SCHEMA NOT
IN ('information_schema','mysql') and Data_free < 0;
then
run the following command on tables given by the query
alte
table < table returned from pervious query >
engine=innodb;
81. How to change the root password if the
root password is lost?
Answer : start the Db with
--skip-grants-table. change the password and restart the db in
normal mode.
82. 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.
83.
How to take consistent backup in mysql?
Answer : take the
backup at slave after stopping the slave.
84. How do you
restrict the users in mysql?
Answer : by grant and
revoke commands.
85. what is advantage of having file per
table parameter enabled?
Answer : point # 26 can only
be done in file per table is enabled at innodb level.
86. How
do you setup replication?
Answer : I will post a separate
BLOG for this.. Keep reading
