How To create Mysql Replica in Linux

Master-Slave Replication

Master DB Details:192.168.0.1

Slave DB Details:192.168.0.2

Install MySQL Server on both the servers: yum install mysql-server mysql

Phase1: Configure Master Server

1. vim /etc/my.cnf

2. Add below lines in the [mysqld] section

    server-id = 1

        binlog-do-db=(name of Database)

        relay-log = /var/lib/mysql/mysql-relay-bin

        relay-log-index = /var/lib/mysql/mysql-relay-bin.index

        log-error = /var/lib/mysql/mysql.err

        master-info-file = /var/lib/mysql/mysql-master.info

        relay-log-info-file = /var/lib/mysql/mysql-relay-log.info

        log-bin = /var/lib/mysql/mysql-bin

        expire_logs_days = 1

        max_binlog_size = 1G

3. Restart mysql service

4. Login to mysql as root user and create slave user for replication and grant privileges.

    mysql -u root -p

  

    mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '123';

    mysql> FLUSH PRIVILEGES;

    mysql> FLUSH TABLES WITH READ LOCK;

    mysql> SHOW MASTER STATUS;'

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 387      | pcfunda         |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

    mysql> quit;

5. Note down the File and Position as it will be used on slave server for replication purpose.

6. mysql> UNLOCK TABLES; // Remove the read lock on the tables

    mysql> quit;

Phase2: Configure Slave Server

1. vim /etc/my.cnf

2. Add below lines in the [mysqld] section

    server-id = 2

3. Restart mysql service

4. Login to mysql as root user and run below query

    mysql> CHANGE MASTER TO MASTER_HOST=' ',MASTER_USER='slaveuser', MASTER_PASSWORD='L0bjet_DeVeL0pEr$', MASTER_LOG_FILE='mysql-bin.000076', MASTER_LOG_POS=12253;

      

//MASTER_HOST=ip address of Master server

//MASRER_USER=slave user created at master server

//MASTER_PASSWORD=slave user password

//MASTER_LOG_FILE=Fil192.168.0.1e name noted from the output on the master server

//MASTER_LOG_POS=Position noted from the output on the master server

mysql> quit;

5. Restart mysql service

Setup done for replicating a database from master server to slave server.

In case we want to take replication of multiple databases then below changes are required

MASTER: add lines to my.cnf

=============================

binlog-do-db=database_name_1

binlog-do-db=database_name_2

binlog-do-db=database_name_3

SLAVE: add lines to my.cnf

=============================

replicate-do-db=database_name_1

replicate-do-db=database_name_2

replicate-do-db=database_name_3    

To see Master Server Status 

SHOW MASTER STATUS;

To see Slave server status 

SHOW SLAVE STATUS \G;