Replication of MySQL Server

For Master to Slave Replication do following steps:

1. vi /etc/my.cnf and add following lines
For ServerA
        server-id = 5
        replicate-same-server-id = 0
        auto-increment-increment = 2
        auto-increment-offset = 1
        relay-log = /var/lib/mysql/serverA-relay-bin
        relay-log-index = /var/lib/mysql/serverA-relay-bin.index
        log-error = /var/log/mysql/mysql.err
        master-info-file = /var/lib/mysql/mysql-master.info
        relay-log-info-file = /var/lib/mysql/serverA-relay-log.info
        log-bin = /var/lib/mysql/ServerA-bin

For ServerB
        server-id = 6
        replicate-same-server-id = 0
        auto-increment-increment = 2
        auto-increment-offset = 2
        relay-log = /var/lib/mysql/serverB-relay-bin
        relay-log-index = /var/lib/mysql/serverB-relay-bin.index
        log-error = /var/log/mysql/mysql.err
        master-info-file = /var/lib/mysql/mysql-master.info
        relay-log-info-file = /var/lib/mysql/serverB-relay-log.info
        log-bin = /var/lib/mysql/ServerB-bin

ServerA will start at 1 and increment 2 always giving odd numbers

this will prevent the autoincrement field having duplicates when cross replicating.

2. Enable /disable binary logging from command line ( SET sql_log_bin = { 0 or 1 }

4. Create Mysql replication User Accounts on both the Servers .
       
        mysql > grant replication slave on *.* to ‘replication’@<ip> identified by ‘password’;
        mysql > flush privileges;

5. Now time to Lock the database and dump the Database.
       
        mysql> FLUSH TABLES WITH READ LOCK;
        mysql> show master status;
        note:- record value of binary log position and binary log name. 
       

6. Now open a new Terminal/Console of ServerA
       
        mysqldump –all-databases –lock-all-tables  > dbdump.db

7. Now go to the first ServerA Terminal/Console
        mysql> UNLOCK TABLES;

8. Now go to the ServerB, Import the dumped database file from the ServerA.
    mysql -p
    mysql>    stop slave;
    mysql>    CHANGE MASTER TO MASTER_HOST=’masterservername or ip’,
                    MASTER_LOG_FILE=’binarylogname’,MASTER_USER=’replication_username’,
                    MASTER_PASSWORD=’replication_user_password’,
                    MASTER_LOG_POS=’binarylogposition from step  above’;
    mysql>    start slave;

From the above steps we will be able to Configuration Master to Slave Replication.

In order to Configuration Master to Master Replication,  following additional steps.

9. In the ServerB
    mysql> show master status;
    note:- record value of binary log position and binary log name. 

10. in the ServerA
        mysql> stop slave;
        mysql> CHANGE MASTER TO MASTER_HOST=’masterservername or ip’,
                    MASTER_LOG_FILE=’binarylogname’,MASTER_USER=’replication_username’,
                    MASTER_PASSWORD=’replication_user_password’,
                    MASTER_LOG_POS=’binarylogposition from step  above’;
        mysql> start slave;

In this way we will be able to Configuration Master to Master Replication.