Monday, September 24, 2012

Setting up Replication in MYSQL

Replication
    - The use of replication is in order to configure MYSQL database updated on a remote server whenever the local client is updated. Moreover it allows you to create an exact copy of a database to another server. This server you my call would be the Slave which takes all the updates from the Master

STEPS IN SETTING UP MYSQL:

1. Let us first configure the master
   1.1 Insert this lines in your my.ini or my.cnf:
                   skip-networking
                   bind-address=127.0.0.1

             Note: If this two lines is already at your my.ini or my.cnf file and has a comment tag (#)                     just remove it. (Ex. #skip-networking) or you can also skip this step.
   1.2 Again insert this into my.ini or my.cnf
                   log-bin=/var/log/mysql/mysql-bin.log
                   binlog-do-db=exampledb
                   server-id=1
    1.3 Then RESTART mysql
    1.4 Create a user with replication privileges
           >mysql -u root -p
           > grant replication slave on *.* to 'slave _user'@'%'identified by 'slave_pass';
           >flush privileges;
           >use exampledb;
           >flush tables with read lock
           >show master status;
           >quit;
      1.5 Write down the file, position and binlog_do_db

              master-host=192.168.1.1
              master-user=example_user
              master_password=example_password
              server-id=3
             master-connection-retry=60
             replicate-do-db=exampledb
      1.6 Two possibilities to get existing tables and data from exampledb from master to slave
             1.6.1 make a database dump
   
                 mysql -u root -proot --opt exampledv> c:\foldername\exampledb.sql

                Note: change the path to your prepared save location
                     1.6.1.1 Transfer this file to your slave server!

             1.6.1 load data from master
      1.7 Unlock tables in exampledb
                 >unlock tables;
                 >quit;

2. Let us no configure the slave
      2.1 create a database named after the database created in master
            >mysql -u root -proot -p
           >create database exampledb;
           >quit;
        2.2 Copy the exampledb data fro the master to slave
              >mysql -u root exampledb <C:\foldername\exampledb.sql
        2.3 Insert this in your my.ini or my.cnf file
             server-id=2
             master-host=192.168.1.2
              master=user=slave_user
             master-password=slave_password
           master-connect-retry=60
           replicate-do-db=exampledb

      (server-id can be any number as long as it is not the same as the server id of the master)

        2.4 Restart MySQL
        2.5 If load data from master;
                 >load data from master;
                >quit;
         2.6 check if exampledb is available on slave exampledb
         2.7 Finally we must do this

               >mysq; -u root -proot
               >slave stop
         2.8 Replace the values
            CHANGE MASTER TO
           master_host='192.168.1.2',
           master_user='slave_user',
           master_password='slave_post',
           master_log_file='mysql-bin.006',
           master_log_pos=183;
         2.9 Start Slave;
         2.10 quit;


SAMPLE PRESENTATION:

MASTER:

-----Sample my.ini

Create a user:
CREATE USER example IDENTIFIED BY '12345';

Show master status;




SLAVE:
Sample my.ini of slave
load data from master;




                  

No comments:

Post a Comment