Archive for July, 2009

MySQL Inernals

By April of 2000, with some encouragement and sponsorship from Slashdot, master-slave replication capability was added.The old nontransactional storage engine,ISAM, was reworked and released as MyISAM.

By early 2002 the MySQL/InnoDB combo was stable and instantly took MySQL to another level. Version 4.0 was finally declared production stable in March 2003.

One can identify the following modules in the server:

• Server Initialization Module
• Connection Manager
• Thread Manager
• Connection Thread
• User Authentication Module
• Access Control Module
• Parser
• Command Dispatcher
• Query Cache Module
• Optimizer
• Table Manager
• Table Modification Modules
• Table Maintenance Module
• Status Reporting Module
• Abstracted Storage Engine Interface (Table Handler)
• Storage Engine Implementations (MyISAM, InnoDB, MEMORY, Berkeley DB)
• Logging Module
• Replication Master Module
• Replication Slave Module
• Client/Server Protocol API
• Low-Level Network I/O API
• Core API

=> Interaction of the Core Modules

When the server is started on the command line, the Initialization Module takes control. It parses the configuration file and the command-line arguments, allocates global memory buffers, initializes global variables and structures, loads the access control tables, and performs a number of other initialization tasks. Once the initialization job is complete, the initialization Module passes control to the Connection Manager, which starts listening for connections from clients.

When a client connects to the database server, the Connection Manager performs a number of low-level network protocol tasks and then passes control to the Thread Manager, which in turn supplies a thread to handle the connection. Once the Connection Thread receives control, it first invokes the User Authentication Module. The credentials of the connecting user are verified, and the client may now issue requests.

The Connection Thread passes the request data to the Command Dispatcher. In MySQL server terminology, there are two types of client requests: a query and a command. A query is anything that has to go through the parser. A command is a request that can be executed without the need to invoke the parser.

The Command Dispatcher forwards queries to the Parser through the Query Cache Module. The Query Cache Module checks whether the query is of the type that can be cached, and if it is already exist ,then a previously computed cached result that is still valid will be displayed. If the Query Cache Module reports a miss, the query goes to the Parser, which will make a decision on how to transfer control based on the query type.

Select queries are forwarded to the Optimizer; updates, inserts, deletes, and table-creation and schema-altering queries go to the respective Table Modification Modules; queries that check, repair, update key statistics, or defragment the table go to the Table Maintenance module; queries related to replication go to the Replication Module; and status requests go to the Status Reporting Module.

At this point, each of the modules that will receive control from the Parser passes the list of tables involved in the query to the Access Control Module and then, upon success, to the Table Manager, which opens the tables and acquires the necessary locks. Now the table operation module is ready to proceed with its specific task and will issue a number of requests to the Abstracted Storage Engine Module for low-level operations such as inserting or updating a record, retrieving the records based on a key value, or performing an operation on the table level, such as repairing it or updating the index statistics.

If the low-level module has made a modification to the data in some way and if the binary update logging is enabled, the module will be responsible for asking the Logging Module to log the update event to the binary update log, sometimes known as the replication log, or, among MySQL developers and power users, the binlog.

Once the task is completed, the execution flow returns to the Connection Thread, which performs the necessary clean-up and waits for another query or command from the client. The session continues until the client issues the Quit command.

=> Replication

In addition to interacting with regular clients, a server may receive a command from a replication slave to continuously read its binary update log. This command will be handled by the Replication Master Module.

If the server is configured as a replication slave, the Initialization Module will call the Replication Slave Module, which in turn will start two threads, called the SQL Thread and the I/O thread. They take care of propagating updates that happened on the master to the slave. It is possible for the same server to be configured as both a master and a slave.


=> Server Initialization Module

• init_common_variables( )
• init_thread_environment( )
• init_server_components( )
• grant_init( ) in sql/
• init_slave( ) in sql/
• get_options( )

=> Connection Manager

• handle_connections_sockets( )

=> Thread Manager

• create_new_thread( )
• start_cached_thread( )

=> Connection Thread

• handle_one_connection( )

=> User Authentication Module

• acl_check_host( )
• create_random_string( )
• check_user( )
• acl_getroot( )

=> Access Control Module

• check_grant( )
• check_table_access( )
• check_grant_column( )
• acl_get( )

=> Parser

• mysql_parse( )
• yyparse( )

=> Command Dispatcher

• do_command( )
• dispatch_command( )

=> Query Cache Module

• Query_cache::store_query( )
• Query_cache::send_result_to_client( )

=> Optimizer

• mysql_select( )
• JOIN::prepare( )
• JOIN::optimize( )
• JOIN::exec( )
• make_join_statistics( )
• find_best_combination( )
• optimize_cond( )

=> Table Manager

• openfrm( )
• mysql_create_frm( )
• open_table( )
• open_tables( )
• open_ltable( )
• mysql_lock_table( )

=> Table Modification Modules

• mysql_update( )
• mysql_multi_update( )
• mysql_insert( )
• mysql_create_table( )
• mysql_alter_table( )
• mysql_rm_table( )
• mysql_delete( )

=> Table Maintenance Module

• mysql_check_table( )
• mysql_repair_table( )
• mysql_backup_table( )
• mysql_restore_table( )
• mysql_optimize_table( )
• mysql_analyze_table( )

=> Status Reporting Module

• mysqld_list_processes( )
• mysqld_show( )
• mysqld_show_create( )
• mysqld_show_fields( )
• mysqld_show_open_tables( )
• mysqld_show_warnings( )
• show_master_info( )
• show_binlog_info( )

=> Storage Engine Implementations (MyISAM, InnoDB, MEMORY/HEAP, Berkeley DB, NDB)

=> Replication Master Module

• mysql_binlog_send( )

=> Replication Slave Module

• handle_slave_io( )
• handle_slave_sql( )


NIC Bonding



        The concept of NIC Bonding (or sometimes called NIC Teaming) is that you have two NICs bonded together to appear as if they are the same physical device. I.e. They will both present the same Hardware (MAC) address. This is accomplished through the ifenslave utility, which enables the kernel to see/use only one device.

NIC Bonding also be known as:

* “NIC Aggregation” (Mainly Linux world)
* “NIC Teaming” (Mainly Windows world)
* “Port Trunking” (Mainly hardware world)
        It provides increased bandwidth by merging the bandwidth of the individual ports/NICs.It’s primary use _is_ to increase a network link speed beyond the limits of any one single cable or port. Although It can also be used for failover, if one link dies all traffic to and from the machine should be routed to the remaining links.

        The “ifenslave” is a tool to attach and detach slave network interfaces to a bonding device. A bonding device will act like a normal Ethernet network device to the kernel, but will send out the packets via the slave devices using a simple round-robin scheduler. This allows you a simple load-balancing.

NIC Bonding Prerequiest :-

      You must have an bonding support in your kernel, so you can check with following.

$ modprobe -l | grep bonding

$ cat /boot/config-2.6.9-55.EL | grep -i bonding

NIC Bonding Configuration :-

(1) The “modprob.conf” file should include the followings 3 lines.

$ cat /etc/modprobe.conf

alias bond0 bonding
options bond0 max_bonds=2 miimon=100 mode=1
options bond1 miimon=100 mode=1

      max_bonds = ” The number of bonding devices to create for this instance of the bonding driver e.g., if max_bonds is 3, and the bonding driver is not already loaded, then bond0,
bond1 and bond2 will be created. The default value is 1.”

      miimon = ” Specifies the MII link monitoring frequency in milliseconds.This determines how often the link state of each slave is inspected for link failures. A value of zero
disables MII link monitoring. A value of 100 is a good starting point.”

      mode = ” Specifies one of the bonding policies. The default is balance-rr (round robin).”
      Possible values are:

0 = Round-robin policy: Transmit packets in sequential order from the first slave
through the last.

1 = Active-backup policy: Only one slave in the bond is active. A different slave
becomes active if, and only if, the active slave fails.

2 = Tries to balance traffic by splitting up outgoing packets between the adapters,
using the same one for each specific destination when possible.

3 = Broadcast policy: transmits everything on all slave interfaces. This mode
provides fault tolerance.

(2) You will have to add the following to do a working NIC Bonding.

$ vi /etc/sysconfig/network-scripts/ifcfg-bond0

## Now add the following things to the above file. ##


(3) Now just append the following lines to (eth0).

$ vi /etc/sysconfig/network-scripts/ifcfg-eth0

## nOW APPEND the following to the above file. ##


(4) Now just append the following lines to (eth1).


## nOW APPEND the following to the above file. ##


Secure SHELL (SSH) Hardening


(1) Change the default Port of ssh from (22) to other which is above (1024) and   the port must be free.
          Port 7824

(2) Change the PermitRootLogin setting to (no) . The default is yes.
          PermitRootLogin no

(3) Change PermitEmptyPasswords setting to (no).
          PermitEmptyPasswords no

(4) Change Protocol setting to “2” if it is “1”. The default value is “2”.
          Protocol 2

(5) Change LoginGraceTime to lower number. The default is 120  (second).
          LoginGraceTime 60

(6) Change OR Add the AddressFamily setting to “inet” (for IPv4 only) ,”inet6″ (for IPv6 only) and “any” (for both IPv4 and IPv6).
          AddressFamily inet

(7) Add ListenAddress to sshd_config if you want to run the SSH  services on particular address.
          ListenAddress xxx

(8) Change MaxAuthTries to 3 ( any one you want ). It specifies the maximum  number of authentication attempts permitted per connection. Once the number of failures reaches half this value, additional failures are logged. The default is 6.
          MaxAuthTries 3

(9) Add Banner to config file if you want to give some Message to all user who connect to your server through ssh.
          Banner /etc/

(10) Add or Change the AllowUsers , AllowGroups , DenyUsers and DenyGroups to make an access control list to allow or deny Login.