Monday 15 July 2013

Mysql Master Slave Replication

How to Mysql Master Slave Replicate
Step1:- First Install Mysql Application after that we will configure /etc/my.cnf 
Step2:- Chnage on master my.cnf file for slave configuration
binlog-do-db=berkshire_new_cluster
binlog-ignore-db=berkshire_cms_live
server-id= 1
:wq!   Save file  
Step3:- Start mysql services on master server
# /etc/init.d/mysql.server restart
Step4:- create a user on Master server for replication with Grant Replication
# mysql -uroot -p
mysql> create GRANT REPLICATION SLAVE ON *.* TO 'user1'@'remotehostIP' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES; # for reload update or referesh
mysql> FLUSH TABLES WITH READ LOCK; # we have to restrict the write operation  on the master server by running this sql command on the master server.
Step5:- Now we will take dump of Master databases for replication.
# mysqldump -uroot -p berkshire_new_cluster > berkshire_new_cluster.sql
Step6:- Now move this dump file on slave server over SCP command like this.
# scp -r berkshire_new_cluster.sql slave-server-username@slave-server -ip:/home/
ON SLAVER SERVER CONFIGURATION
Step-1: First Install Mysql Application after that we will configure /etc/my.cnf file Step-2:   # vim /etc/my.cnf
server-id= 2
#master-connect-retry=60   (this line is already in file)
replicate-do-db=berkshire_new_cluster
:wq! Save file
Step3:- Start mysql services on Slave server 
# /etc/init.d/mysql.server restart
Step4:- Now go to Master server and check master status 

mysql -uroot -p
Enter password: **********

mysql> show master status; 
+------------------+----------+-----------------------+--------------------+ 
| File             | Position | Binlog_Do_DB          | Binlog_Ignore_DB   | 
+------------------+----------+-----------------------+--------------------+ 
| mysql-bin.000202 |      107 | berkshire_new_cluster | berkshire_cms_live | 
+------------------+----------+-----------------------+--------------------+ 
1 row in set (0.00 sec) 
Step5:- Now go to SLAVE server and execute below query for connecting master to slave.
mysql>> CHANGE MASTER TO MASTER_HOST='Master Server IP', MASTER_USER='user1', MASTER_PASSWORD='user1 passwd', MASTER_LOG_FILE='file number', MASTER_LOG_POS=position number; 
For Ex- mysql>> CHANGE MASTER TO MASTER_HOST='192.168.28.148', MASTER_USER='bi_replicator', MASTER_PASSWORD='*********', MASTER_LOG_FILE='mysql-bin.000202', MASTER_LOG_POS=107; 
mysql>> START SLAVE;
Now go to Master Server 
On master we could now release the read lock
mysql> UNLOCK TABLES;
Now go to Slave Server
To see the slave running status run the following command on the slave server
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.28.96
                  Master_User: user1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000042
          Read_Master_Log_Pos: 107
               Relay_Log_File: hostname-relay-bin.000042
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000042
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 107
              Relay_Log_Space: 556
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.06 sec)

Monday 8 July 2013

web based terminal in Ubuntu 12.04

How to Access web based terminal in Ubuntu 12.04

Step1: In First step we have to update apt source file

root@ubuntu:~# apt-get update

It will update the base system.

Step2: In Third step we will download & install shellinabox package

You can use any of below links as per your system architecture:-



For 32-bit

wget http://archive.ubuntu.com/ubuntu/pool/universe/s/shellinabox/shellinabox_2.14-1_i386.deb

For 64-bit

wget http://archive.ubuntu.com/ubuntu/pool/universe/s/shellinabox/shellinabox_2.14-1_amd64.deb


Step3: Now we need to install this packages with dpkg command.

 root@ubuntu:~#  dpkg -i shellinabox_2.14-1_i386.deb

Step4: we will check the service status

root@ubuntu:~#  /etc/init.d/shellinabox status
root@ubuntu:~# /etc/init.d/shellinabox stop

root@ubuntu:~# /etc/init.d/shellinabox status
Shell In A Box Daemon is not running



root@ubuntu:~# /etc/init.d/shellinabox start

root@ubuntu:~# /etc/init.d/shellinabox status
Shell In A Box Daemon is running

root@ubuntu:~#


Step5: Now go to any Browser and open this server IP with port number 4200

eg: 192.168.28.96:4200
root login: root
Password:**********
Last login: Mon Jul 8 17:08:13 IST 2013 from 192.168.28.26 on pts/2
Welcome to Ubuntu 12.04.2 LTS (GNU/Linux 3.2.0-23-generic-pae i686)
 * Documentation:  https://help.ubuntu.com/                                                                                      
347 packages can be updated.
121 updates are security updates.
root@ubuntu:~$ ls
Changes.odt Desktop Dropbox GNUstep NetBeansProjects scripts Templates Untitled 1.pdf voicebackup
data Documents examples.desktop mozilla.pdf Pictures sites test.php Videos voice_bkp
DATA.ods Downloads Firefox_wallpaper.png Music Public svnco Ubuntu One VirtualBox VMs WCF.odt

root@ubuntu:~$

Sunday 7 July 2013

How To Setup MySql Cluster High Availability


Hi, We are going to setup Mysql Cluster over source code compilation And first we are going to install some dependence of Mysql.

root@machine01: ~#apt-get install bison libncurses5-dev libtool g++ gcc make cmake; 

#Extracting mysql

root@machine01: ~# tar xvfz mysql-cluster-gpl-7.2.8.tar.gz -C /usr/local/src
root@machine01: ~# chown -R root.root /usr/local/src//mysql-cluster-gpl-7.2.8/
root@machine01: ~# cd /usr/local/src//mysql-cluster-gpl-7.2.8

#Configuring  Mysql Cluster 

root@machine01:/usr/local/src/mysql-cluster-gpl-7.2.8# cmake .

#Installing Mysql cluster

root@machine01:/usr/local/src/mysql-cluster-gpl-7.2.8# make clean install 

# Adding mysql user and group

root@machine01:/usr/local/src/mysql-cluster-gpl-7.2.8# groupadd mysql; useradd -g mysql mysql

root@machine01:/usr/local/src/mysql-cluster-gpl-7.2.8# cd /usr/local/mysql 
root@machine01:/usr/local/mysql# cp support-files/my-medium.cnf /etc/my.cnf
root@machine01:/usr/local/mysql# chown -R mysql . ; chgrp -R mysql .

#Dumping essential data in mysql 

root@machine01:/usr/local/mysql./scripts/mysql_install_db --user=mysql
root@machine01:/usr/local/mysqlchown -R root . ; chown -R mysql data

#Putting mysql executables in PATH

root@machine01:/usr/local/mysql# cd /usr/local/mysql/bin/ 
root@machine01:/usr/local/mysql/bin/#  for file in *; do rm /usr/bin/$file; ln -s /usr/local/mysql/bin/$file /usr/bin/$file; done;

#Starting Mysql..

root@machine01:/usr/local/mysql/bin/# ./mysqld_safe --user=mysql

#Securing Mysql Cluster installation 

root@machine01:/usr/local/mysql/bin/# ./mysql_secure_installation

Note : After execution above cmd do the on screen instruction set root password, 
remove anonymous user, etc. [ i have set "y" for every instruction ]   

#Now log into mysql database
root@machine02:/usr/local/mysql/bin/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.27-ndb-7.2.8-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Note : Installation of the management node does not require the mysqld binary.
but i have installed on machine01. 
--------------------------------------------------------------------------------------------------------------------------------

#Configuration of management node on machine01.

root@machine01:~# mkdir /var/lib/mysql-cluster
root@machine01:~# cd  /var/lib/mysql-cluster
root@machine01:/var/lib/mysql-cluster# vim config.ini

 for our setup, The config.ini file should read as follows: 
---------------------------------------------------------------------------------------------------------------------------------
# Example Ndbcluster storage engine config file.
#
[ndbd default]
NoOfReplicas= 3
MaxNoOfConcurrentOperations= 10000
DataMemory= 2048M
IndexMemory= 1024M
#TimeBetweenWatchDogCheck= 10000
DataDir= /usr/local/mysql
MaxNoOfAttributes=5000
MaxNoOfOrderedIndexes= 500
#ServerPort=2200
#FragmentLogFileSize=1073741824
[ndb_mgmd default]
DataDir= /var/lib/mysql-cluster

[ndb_mgmd]
HostName=192.168.28.28
[ndbd]
HostName=192.168.28.2
[ndbd]
HostName=192.168.28.3
[ndbd]
HostName=192.168.28.4

[mysqld]
HostName=192.168.28.2
[mysqld]
HostName=192.168.28.3
[mysqld]
HostName=192.168.28.4

# choose an unused port number
# in this configuration 63132, 63133, and 63134
# will be used
[tcp default]
#PortNumber= 63132


#Configuration of Data/SQL Nodes on machine02, machine02 and machine03

 For each data node and SQL node in our setup, my.cnf should look like this:

-------------------------------------------------------------------------------------------------------------------------------
 # Example MySQL config file for medium systems.


# The following options will be passed to all MySQL clients
[client]
#password    = your_password
port        = 3306
socket        = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port        = 3306
socket        = /tmp/mysql.sock

# Options for to access mysql cluster datanode
ndbcluster                                 # run NDB storage engine
ndb-connectstring=192.168.28.28           # location of management server


skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

server-id    = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysql_cluster]
# Options for ndbd process:
ndb-connectstring=192.168.28.28
-------------------------------------------------------------------------------------------------------------------------------

#Starting the MySQL Cluster

1 ) On the management host(machine01), issue the following command from the system shell to start the management node process

-------------------------------------------------------------------------------------------------------------------------------

root@machine01:~# /usr/local/mysql/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster --initial

-------------------------------------------------------------------------------------------------------------------------------

2 ) On each of the Data/SQL node hosts, run these commands to start the ndbd and mysql server process

--------------------------------------------------------------------------------------------------------------------------------
 root@machine02:~# /usr/local/mysql/bin/ndbd  --initial

root@machine02:~# /usr/local/mysql/support-files/mysql.server start

--------------------------------------------------------------------------------------------------------------------------------

#Now login into machine01 (Management Node )
--------------------------------------------------------------------------------------------------------------------------------

root@machine01:~#ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]    3 node(s)
id=2    @192.168.28.2 (mysql-5.5.27 ndb-7.2.8, Nodegroup: 0, Master)
id=3    @192.168.28.3 (mysql-5.5.27 ndb-7.2.8, Nodegroup: 0)
id=4    @192.168.28.4 (mysql-5.5.27 ndb-7.2.8, Nodegroup: 0)

[ndb_mgmd(MGM)]    1 node(s)
id=1    @192.168.28.28  (mysql-5.5.27 ndb-7.2.8)

[mysqld(API)]    3 node(s)
id=5 @192.168.28.2 (mysql-5.5.27 ndb-7.2.8)
id=6 @192.168.28.3  (mysql-5.5.27 ndb-7.2.8)
id=7 @192.168.28.4  (mysql-5.5.27 ndb-7.2.8)
--------------------------------------------------------------------------------------------------------------------------------

#######################################################################

Testing the mysql Cluster Setup

####################################################################### 

On either server machine01 , machine02 and machine03 enter the following commands:
----------------------------------------------------------------------------------------------------------------------------- 
root@machine02:~#mysql -uroot -p 
Enter password: 

mysql >  create database testdb;

mysql > use test;
mysql > CREATE TABLE cluster_test (i INT) ENGINE=NDBCLUSTER;
mysql > INSERT INTO cluster_test (i) VALUES (1);
mysql > SELECT * FROM cluster_test;
----------------------------------------------------------------------------------------------------------------------------

If this works, now go to the other server and run the same SELECT and see what you will get. Insert from that host and go back to previous host and see if it works. If it works then congratulations! 

Wednesday 8 May 2013

MySql DBA interview question answer


MySQL DBA Interview Questions and Answers - Series 1



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.

  1. How to view a list of MySQL users and their privileges
mysql> select user from mysql.user;

  1. 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


87. How to add a new column in a Table

Answer: ALTER TABLE `RHCVA` ADD `roll_no` INT UNSIGNED NOT NULL

88. How to ALTER Table. If you need to change data type or any other schema.
Answer: ALTER TABLE `RHCVA` CHANGE `id_n` `id_n` SMALLINT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT 

Thursday 18 October 2012


         How to Install Jira on Ubuntu 12.04 LTS

Step 1: Go to Download Page of Jira over below URL



URL: http://www.atlassian.com/software/jira/download
as per you Operating systems and O/S Architech like 32bit/ 64bit you can Download



Step 2: Before the Jira Installation we required sun-java6-jre sun-java6-plugin . So that at first we will install java. For java plz go through this url...
http://expertredhat.blogspot.in/2012/10/how-to-installation-of-sun-java6-jdk.html

Step 3: Now install Jira
# sudo ./atlassian-jira-5.1.4-x32.bin



















Now go to browser and type :   http://localhost:8080/























































































































Wednesday 17 October 2012

How to installation of sun-java6-jdk, sun-java6-jre, sun-java6-plugin on ubuntu 12.04 LTS

How to installation of sun-java6-jdk, sun-java6-jre, sun-java6-plugin on ubuntu           12.04 LTS


This repository is available for: Natty Oneiric Precise:

To Install this PPA:
  • sudo apt-add-repository ppa:flexiondotorg/java
  • sudo apt-get update
  • sudo apt-get install sun-java6-jre sun-java6-plugin

Note: if PPA Add doesn't work type below cmd on terminal

sudo apt-get install python-software-properties

After that try to add PPA 

Thursday 20 September 2012

How to install Zabbix 2.0 on ubuntu 12.04 LTS


Pre-Compiling and Installing Zabbix Server


  1. Install Zabbix server by entering the following command:
    sudo apt-get install zabbix-server-mysql
  2. Install the Zabbix web interface by entering the following command:
    sudo apt-get install zabbix-frontend-php
  3. Install the Zabbix agent by entering the following command:
    sudo apt-get install zabbix-agent
  4. Now Restart the Zabbix Server and Zabbix agent Serveices on Server by entering the following command:
    sudo /etc/init.d/zabbix-server restart
    sudo /etc/init.d/zabbix-agent restart
Zabbix is now installed and running on your Linode.

5.Open up a browser and point it to http://YourLinodeIP/zabbix to continue with the installation, where YourLinodeIP is the IP address of your Linode.
      Eg: https://192.168.28.100/zabbix


If you find any error with login GUI interface with admin please check your Mysql database. and reset Password of Admin with MD5. go through below screen-shoot.
# mysql  -uroot   -p
  Passwd: *********
mysql> show databases;
mysql> use zabbix;
mysql> show tables;
mysql> DESC users;
mysql> UPDATE users SET passwd=MD5('zabbix login password') WHERE name='Zabbix';




Now Restart the Apache, Mysql and Zabbix  Services and now try to login.
# sudo /etc/init.d/apache2 restart
# sudo /etc/init.d/mysql restart
# sudo /etc/init.d/zabbix-server  restart 
# sudo /etc/init.d/zabbix-agent  restart 


Now Installation of Zabbix Agent on that server who want to Monitor.
    sudo apt-get install zabbix-agent
     Now configure zabbix-agent configuration file.
    Sudo  vim  /etc/zabbix/zabbix-agented.conf
     


















Now Restart Zabbix Agent Services:

sudo  /etc/init.d/zabbix-agent  restart

Now go to Zabbix Server and Add Zabbix Agent Server .


Now Create Graph Who Want to Monitor That Server's 


         NOW YOU CAN KEEP MAXIMUM UP TIME OF YOUR SERVER'S