mysql replication

What is Mysql Replication

• Replication enables data from one MySQL database server (called the master) to be replicated to one or more MySQL database servers (slaves).


• Replication is asynchronous - your replication slaves do not need to be connected permanently to receive updates from the master,which means that updates can occur over long-distance connections and even temporary solutions such as a dial-up service.


• Depending on the configuration, you can replicate all databases, selected databases and even selected tables within a database.More..

What-is/Where-to use Of Mysql Replication

• Web apllication having larger(millions of) requests per day need to be served from multiple servers.


• To run application from Multiple Server we need to access
  
  
  
  • A. One Central Database (Having Very Good HW Support )
  
  
  • Or
  
  
  • B. Use Mysql Replication ( Replica of mysql database ) To Manage Replica of Master database on All slave we use Mysql Replication as Below Image
    
    Web Aplication Structure using mysql replication
    
    
    Load Balancer OR LVS (Linux Virtual Server) is special machine, which take request from client and forward it to any web client.
    
    
    Three Slave db with Web Client(Apache) run One site.
    
    Each Web client is identical and serv same website.
    
    All Insert / Update goes to master db server,
    This Updated / Modified data will be Replicatedto all slave db server.
    
    Each select statement goes to slave DB server.
    
  
  
  
  

---

• Steps To Set Mysql Replication


• Set Master to create binary log for all or Selected database.


• Create User on Mysql having Mysql replication priveleges( Login to master from slaves )


• Set Salve Configuration ( Select Databae/s to Replicate.List of Table for do-replicate, do-note-replicate )


• Set Replication ( Set Master-Server User,Psw,Logfile, Position )


• More Technical Details How to Set Up Replication

---

Usefull linux commands

• To start stop restart mysql
  
  shell_prompt>>/etc/init.d/mysql start
  
  or
  shell_prompt>>/etc/init.d/mysqld start
  
  Hint after typing "/etc/init.d/mysq" ( not l is missing) press tab key will display all installed mysql setup
  
  
  


• Find mysql Data-directory , config-file path, Error-log path
  
  

  
  shell_prompt>>ps -elf | grep mysql 
  --datadir=/var/lib/mysql         //Mysql Data Dir, here all Binary-Log, Database folders having( *.frm, *.MYD, *.MYI) 
  --defaults-file=/etc/my.cnf      //Mysql Config file 
  --log-error=/var/log/mysqld.log  //Mysql Error log file 
  

  
  


• 
  #open my.cnf in vi editor
  
  shell_prompt>>vi /etc/my.cnf
  
  in my.cnf file , comments starts with first character as #
  

Helpful vi commands

• Pressing Esc key editor gose in Command mode, Diffrent Comands are as follow.


• Esc i ->Insert Mode


• Esc u ->Undo


• Esc :w ->Save


• Esc :q! ->Quit Without save


• Esc :wq ->Saveand Quit


• Esc dd ->Delete Current Line


• Keybord Shortcuts in Insert Mode


• Selecting Text with Mouse


• Ctrl+Insert ->Copy


• Shift+Insert ->Paset