40-LINUX-SHELL-COMMANDS-FOR-BEGINNERS

Linux has more than 650 commands and every command has its own set of options all performing different operations. Going through each and every one of these commands will be a very tedious task. However limiting yourself to only a few of them is never an option. The trick here to learning all these commands, is to categories them according to their function. By doing this, you will know atleast the basic commands and have some measure of control over the Linux command line.When you go through these commands, you will be able to perform different function as per your need in the Linux command line.

• Basic Commands
• Editor
• User Account
• Network Commands
• Archive Commands
• Help Commands
• Package Management Utilities
• Process Commands 
• For further, Go to:  http://www.fortystones.com/40-linux-shell-commands-beginners/

STEPS TO CHECK WHEN MYSQL PERFORMANCE IS POOR

If your MySQL server performance is poor check the following details in your server.

1.Check the load average.

          ex: w 

Note:load average is not more than 1.

2.Check free memory.

          ex: free -m 

Check the available memory.

3.Check the updating running process.

          ex: top 

Check CPU utilization and Memory of the server.

4.Show processlist.

This statement shows you which threads are running and to see “too many connections” error message and want to find out what is going on.

5.See log error.

 Is there any error in the server.

6.See slow query log. 

Check which query is taking more time to execute.

Then,Optimizing the query response time by query tuning, proper  index creation and parameter tuning.

7.Hard disk performance.

KNOW ABOUT MYSQL BINLOG FORMATS

What is binlog?

           In MySQL the binary log record the events that, when ever the changes happens in the databases. (eg.create,delete,update,insert)

What is binlog formats?

          The format that has been used to record the changes of data in binlog.

What are the their types?

          There are three types of binlog formats.

                                         1) Statement

                                         2) Row

                                         3) Mixed

1) Statement

     It is the default binlog format for MySQL5.6.

     It records the events in SQL statement in binlog to read easily with
     mysqlbinlog.

    The binlog does not grow so fast than row format.

    Faster to recover from a backup.

how it works?

• set the binlog format to statement if you use older version of MySQL 5.6 in my.cnf file.
• restart MySQL 
• create a database example india.
• mysql> create database india;
  Query OK, 1 row affected (0.00 sec)
  
  mysql> use india;
  Database changed
  mysql> create table states(id int not null auto_increment,statename varchar(25),country varchar(25),primary key(id));
  Query OK, 0 rows affected (0.18 sec)
•  insert the details and use update query it must be affect all the rows in the column.
• Then flush the logs.
• use the old binlog before the new binlog.
• Now use mysqlbinlog to view the events recorded in binlog.
• Ex:

  shell> mysqlbinlog /path/mysql-bin.000007|less

• The insert and update query that recorded in mysqlbinlog is. 

• # at 1457
  #130213 18:05:44 server id 1  end_log_pos 1589  Query   thread_id=1     exec_time=0     error_code=0
  SET TIMESTAMP=1360758944/*!*/;
  insert into states (statename,country)values('Chattisgarh','India')
  # at 1934
  #130213 18:05:55 server id 1  end_log_pos 1838  Query   thread_id=1     exec_time=0    error_code=0
  SET TIMESTAMP=1360758955/*!*/;
  insert into states (statename,country)values('Delhi','India')
  # at 1934
  #130213 18:11:03 server id 1 end_log_pos 2037 Query thread_id=1 exec_time=0 error_code=0
  SET TIMESTAMP=1360759263/*!*/;
  update states set country='Great India'
  /*!*/;
  # at 2037
  #130213 18:11:03 server id 1 end_log_pos 2064 Xid = 24
  COMMIT/*!*/;
  # at 2064
  #130213 18:11:56 server id 1 end_log_pos 2107 Rotate to mysql-bin.000011 pos: 4
  DELIMITER ;
  # End of log file
  ROLLBACK /* added by mysqlbinlog */;
  /*!50003 COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 

In this the events are recorded in SQL statement.So it is very easily to read when point time recovery.

2) Row

     It records each row of modification in binlog.

     It will record each and every thing that create,delete,update in the datas.

     The binlog grow faster than statement format.

• set the binlog format to row format in my.cnf file.
• restart MySQL 
• create a database example india.
• mysql> create database india;
  Query OK, 1 row affected (0.00 sec)
  
  mysql> use india;
  Database changed
  mysql> create table states(id int not null auto_increment,statename varchar(25),country varchar(25),primary key(id));
  Query OK, 0 rows affected (0.18 sec)
• Insert the details and use update query it must be affect all the rows in the column.
• Then flush the logs.
• use the old binlog before the new binlog.
• Now use mysqlbinlog to view the events recorded in binlog.
• Ex:

  shell>mysqlbinlog /path/mysql-bin.000007|less

• The insert and update query that recorded in mysqlbinlog is. 

• # at 1514
  # at 1566
  #130213 17:20:26 server id 1 end_log_pos 1566 Table_map: `india`.`states` mapped to number 34
  #130213 17:20:26 server id 1 end_log_pos 1850 Update_rows: table id 34 flags: STMT_END_F
  BINLOG '
  An4bURMBAAAANAAAAB4GAAAAACIAAAAAAAEABWluZGlhAAZzdG
  0ZXMAAwMPDwQZABkABg==
  An4bURgBAAAAHAEAADoHAAAAACIAAAAAAAEAA///+AEAAAANQW
  5kcmgUHJhZGVzaAVJbmRpYfgB
  AAAADUFuZHJhIFByYWRlc2gLR3JlYXQgSW5kaWH4AgAAABFBcn
  VuYWNoYWwgUHJhZGVzaAVJbmRp
  YfgCAAAAEUFydW5hY2hhbCBQcmFkZXNoC0dyZWF0IEluZGlh+AM
  AAAAFQXNzYW0FSW5kaWH4AwAA
  AAVBc3NhbQtHcmVhdCBJbmRpYfgEAAAABUJpaGFyBUluZGlh+AQ
  AAAAFQmloYXILR3JlYXQgSW5k
  aWH4BQAAAAxDaGhhdHRpc2dhcmgFSW5kaWH4BQAAAAxDaGhh
  dHRpc2dhcmgLR3JlYXQgSW5kaWE=
  '/*!*/;
  # at 1850
  #130213 17:20:26 server id 1 end_log_pos 1877 Xid = 19
  COMMIT/*!*/;

• we cant understand when we use  mysqlbinlog /path/mysql-bin.000007 for row format. so to view and identify the row format in binlog.There are two ways to read row format in binlog.

• --verbose,-v: It Reconstruct row events and display them as commented SQL statements. If this option is given twice, the output includes comments to indicate column data types and some metadata. 

•  --base64-output=decode-rows: This option determines when events should be displayed encoded as base-64 strings using  BINLOG statements.

•  Eg
  

  shell>mysqlbinlog -v /path/mysql-bin.000007|less

   or

  shell>mysqlbinlog -v --base64-output=decode-rows mysql-bin.000007|less

• The insert query that recorded in mysqlbinlog is. 

• # at 517
  # at 569
  #130213 17:15:18 server id 1 end_log_pos 569 Table_map: `india`.`states` mapped to number 34
  #130213 17:15:18 server id 1 end_log_pos 623 Write_rows: table id 34 flags: STMT_END_F
  BINLOG '
  znwbURMBAAAANAAAADkCAAAAACIAAAAAAAEABWluZGlhAAZzdGF0Z
  XMAAwMPDwQZABkABg==
  znwbURcBAAAANgAAAG8CAAAAACIAAAAAAAEAA//4AQAAAA1BbmRyY
  SBQcmFkZXNoBUluZGlh
  '/*!*/;
  ### INSERT INTO india.states
  ### SET
  ### @1=1
  ### @2='Andra Pradesh'
  ### @3='India'
  # at 623
  #130213 17:15:18 server id 1 end_log_pos 650 Xid = 12
  COMMIT/*!*/; 
  -->
  # at 719
  # at 771
  #130213 17:15:52 server id 1 end_log_pos 771 Table_map: `india`.`states` mapped to number 34
  #130213 17:15:52 server id 1 end_log_pos 829 Write_rows: table id 34 flags: STMT_END_F
  BINLOG '
  8HwbURMBAAAANAAAAAMDAAAAACIAAAAAAAEABWluZGlhAAZzdGF
  0ZXMAAwMPDwQZABkABg==
  8HwbURcBAAAAOgAAAD0DAAAAACIAAAAAAAEAA//4AgAAABFBcnVu
  YWNoYWwgUHJhZGVzaAVJbmRp
  YQ==
  '/*!*/;
  ### INSERT INTO india.states
  ### SET
  ### @1=2
  ### @2='Arunachal Pradesh'
  ### @3='India'
  # at 829
  #130213 17:15:52 server id 1 end_log_pos 856 Xid = 13
  COMMIT/*!*/;

      Here the insert statement are recorded in row format.


      The update query that recorded in mysqlbinlog is. 

• BINLOG '
  
  An4bURMBAAAANAAAAB4GAAAAACIAAAAAAAEABWluZGlhAAZzdGF0
  ZXMAAwMPDwQZABkABg==
  An4bURgBAAAAHAEAADoHAAAAACIAAAAAAAEAA///+AEAAAANQW5k
  cmEgUHJhZGVzaAVJbmRpYfgB 
  AAAADUFuZHJhIFByYWRlc2gLR3JlYXQgSW5kaWH4AgAAABFBcnVuYW
  NoYWwgUHJhZGVzaAVJbmRp
  YfgCAAAAEUFydW5hY2hhbCBQcmFkZXNoC0dyZWF0IEluZGlh+AMAAA
  AFQXNzYW0FSW5kaWH4AwAA
  AAVBc3NhbQtHcmVhdCBJbmRpYfgEAAAABUJpaGFyBUluZGlh+AQAAA
  AFQmloYXILR3JlYXQgSW5k
  aWH4BQAAAAxDaGhhdHRpc2dhcmgFSW5kaWH4BQAAAAxDaGhhdHR
  pc2dhcmgLR3JlYXQgSW5kaWE=
  '/*!*/;
  ### UPDATE india.states
  ### WHERE
  ### @1=1
  ### @2='Andra Pradesh'
  ### @3='India'
  ### SET
  ### @1=1
  ### @2='Andra Pradesh'
  ### @3='Great India'
  ### UPDATE india.states
  ### WHERE
  ### @1=2
  ### @2='Arunachal Pradesh'
  ### @3='India'
  ### SET
  ### @1=2
  ### @2='Arunachal Pradesh'
  ### @3='Great India'
  ### UPDATE india.states
  ### WHERE
  ### @1=3
  ### @2='Assam'
  ### @3='India'
  ### SET
  ### SET
  ### @1=3
  ### @2='Assam'
  ### @3='Great India'
  ### UPDATE india.states
  ### WHERE
  ### @1=4
  ### @2='Bihar'
  ### @3='India'
  ### SET
  ### @1=4
  ### @2='Bihar'
  ### @3='Great India'
  ### UPDATE india.states
  ### WHERE
  ### @1=5
  ### @2='Chhattisgarh'
  ### @3='India'
  ### SET
  ### @1=5
  ### @2='Chhattisgarh'
  ### @3='Great India'
  # at 1850
  #130213 17:20:26 server id 1 end_log_pos 1877 Xid = 19
  COMMIT/*!*/;
  # at 1877
  #130213 17:25:01 server id 1 end_log_pos 1896 Stop
  DELIMITER ;
  # End of log file
  ROLLBACK /* added by mysqlbinlog */;
  /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  (END)

     Here the update query was recorded by each and every row affected in the
     table.

     So that binlog will grow faster than statement format.

3) Mixed

     It combines of both statement and row format.

     In this statement format is the default one for mixed format.

     The row format will change automatically when it needs.

     More details : http://dev.mysql.com/doc/refman/5.1/en/binary-log-mixed.html

STEPS TO DO FOR MASTER SLAVE REPLICATION IN MySQL

What is Replication?

          

      It is a process that allows you to easily maintain multiple copies of a MySQL data by copied automatically from a master to a slave database. 

Steps to do Replications:

1.Stop the running applications in master server

2.See master status.

Note the binlog file and position in notepad for slave server

3.Create a user for replication in master

4.Change the server id,port number and socket.

5.In slave give as,

            Ex: Change master to master_user='replica',

                                              master_host='localhost',

                                              master_password='replica',

                                              master_log_file='mysql-bin.000015',

                                              master_log_position=107,

                                              port=3306;

6. Start the replication by,

       

           Ex: start slave;

7.Import the master server databases to slave server now

     

   

Thank you.