Solutions
Latest Projects
Inovahire
Inovahire
Inovahire
Contact

We Know What Works!

Wide Web Way is the best web development company in the middle east targeting KSA, UAE, Jordan and Arab World in general. It has a wide range of tools and experienced team members to help you achieve your goals, whether you're expanding on an existing website, or just starting out.

How to create Replication on Mysql: Step by step

To created replication (master-slave) on ServerX slave-server (suppose that its IP is 192.168.1.4) and ServerY master-Server (suppose that its IP is 192.168.1.135), below is the procedure step by step:

  1. @ServerY (Master): add this to my.cnf:
    server-id               = 1
    log_bin                 = /var/log/mysql/mysql-bin.log
    expire_logs_days        = 10
    max_binlog_size         = 100M
    #For the greatest possible durability and consistency in a replication 
    #setup using InnoDB with transactions
    innodb_flush_log_at_trx_commit  =1
    sync_binlog                     =1
    
  2. @ServerX(Slave) add this
    server-id                   = 2
    log_bin                     = /var/log/mysql/mysql-bin.log
    log-slave-updates           =yes
    #set log-slave-update IF you want to chain replication servers, 
    #otherwise just drop it
    
  3. create slave users on ServerY
    mysql> grant replication slave on *.* to 'slave'@'192.168.1.4'  IDENTIFIED BY 'Mypass';
  4. copy DB from ServerY to ServerX
  5. run 
mysqladmin -uroot -pth3gat3sql shutdown
  6. run 
tar cf /tmp/MyDB.tar ./MyDB
  7. @ServerX run
    scp Username@192.168.1.35:/tmp/MyDB.tar /var/lib/mysql
    
tar -xvf MyDB.tar
  8. @ServerY: 
mysql>FLUSH TABLES WITH READ LOCK;

    mysql> show master status;

    Save the result of show master status
  9. @ServerX, Run run this query:
    
CHANGE MASTER TO
    MASTER_HOST='192.168.1.135',
    MASTER_USER='slave',
    MASTER_PASSWORD='MyDB',
    MASTER_LOG_FILE='mysql-bin.000003',
    MASTER_LOG_POS=106;
    the values of MASTER_LOG_FILE and MASTER_LOG_POS are those came from show master status on ServerY.
  10. now to be sure that your replication is fine
    
@ServerX:

    show slave status
    Slave_IO_Running and Slave_SQL_Running MUST be Yes for both.

 
 

you are allowed to use <a> , <i>, <b>,<ul>,<li> and <br> tags

Patricia said: What Replication is not at 2012-03-19 19:57:35
keep in mind that:
- Replication is not a backup policy. A mistyped DELETE statement will be replicated on the slave too, and you could end up with two, perfectly synchronized, empty databases. Replication can help protect against hardware failure though.
- Replication is not an answer to all performance problems. Although updates on the slave are more optimized than if you ran the updates normally, if you use MyISAM tables, table-locking will still occur, and databases under high-load could still struggle.
- Replication is not a guarantee that the slave will be in sync with the master at any one point in time. Even assuming the connection is always up, a busy slave may not yet have caught up with the master, so you can't simply interchange SELECT queries across master and slave servers.


Rohidas said: MySql Replication at 2013-04-17 08:02:03
Excellent post it's working fine. start and stop both server mysql services and try again it's working fine


Rohidas said: MySql Replication at 2013-04-17 08:02:19
Excellent post it's working fine. start and stop both server mysql services and try again it's working fine


 
Blog
Tips and Tricks from the
Web Development Experts