Subscribe Us


Breaking

Recent In Voip

Popular

Comments

Recent

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; 








0 on: "How To create Mysql Replica in Linux "