mysql database replication : How To Step by Step: master -slave replication

Here we will help you to setup master-slave replication between MySQL servers.

Setup Details:
Master Server: 10.0.10.12
Slave  Server: 10.0.10.18
Database: empmaster

1. Setup MySQL Master Server

  • Create an mysql account on Master server with REPLICATION SLAVE privileges to which replication client will connect to master.
  • Block write statement on all the tables, so not changes made after taking backup.
  • Edit MySQL configuration file and add the following lines under [mysqld] section.
  • Restart master mysql server to changes take effect.
  • Check the current binary log file name (File) and current offset (Position) value using following command.
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.0.10.18' IDENTIFIED BY 'secretpassword';
mysql> FLUSH PRIVILEGES;
mysql> use empmaster;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> exit;
# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog-do-db=empmaster
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

# service mysqld restart
mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 | empmaster         |                  |
+------------------+----------+--------------+------------------+

The above output is showing that the current binary file is using mysql-bin.000002 and offset value is107. Note down these values to use on slave server.

  • Take a backup of database and copy it to slave mysql server.
  • After completing backup remove the READ LOCK from tables, So that changes can be made.
  • Edit salve mysql configuration file and add following values under [mysqld] section.
# mysqldump -u root -p empmaster > empmaster.sql
# scp empmaster.sql 10.0.10.18:/opt/
mysql> UNLOCK TABLES;

2. Setup MySQL Slave Server

# vim /etc/my.cnf
[mysqld]
server-id=2
replicate-do-db=empmaster

server-id always be an non zero numeric value. These value will never be similar with other master and slave servers.

  • Restart mysql slave server, If you had already configured replication use –skip-slave-start in start to not to immediate connect to master server.
  • Restore database backup taken from master server.
  • Setup option values on slave server using following command.
  • Finally start the slave thread
  • Check the status of slave server.
# /etc/init.d/mysqld restart
# mysql -u root -p empmaster < empmaster.sql
mysql>  CHANGE MASTER TO MASTER_HOST='10.0.10.12',
    -> MASTER_USER='repl_user',
    -> MASTER_PASSWORD='secretpassword',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=107;
mysql> SLAVE START;
mysql> show slave status \G

*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.1.15
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: empmaster
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 107
              Relay_Log_Space: 107
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)
mysql>

MySQL Master-slave Replication has been configured successfully on your system and in working mode.

 

Related Posts

  • 62
    Some times MySQL replication creates problems and slave could not sync properly from master. It may cause with lots of reason. Here is a way to fix it.   Warning: After using this tutorial, All of your bin-log files will be deleted, So if you want, you may take a backup…
    Tags: slave, mysql, server, master, replication
  • 60
    Here we will help you to set up Master-Master replication between MySQL servers. In this setup if any changes made on either server will update on an other one. Setup Details: Master-1: 10.0.10.12 Master-2: 10.0.10.18 Database: empmaster Step 1. Set Up MySQL Master-1 Server Edit MySQL configuration file and add the following…
    Tags: mysql, master, server, replication, file, slave
  • 58
    Once replication has been started it should execute without requiring much regular administration. Depending on your replication environment, you will want to check the replication status of each slave periodically, daily, or even more frequently. Checking Replication Status: The most common task when managing a replication process is to ensure…
    Tags: slave, master, replication, sql, mysql
  • 54
    Of course, there are many aspects of MySQL replication, but my main focus will be the logistics – how replication events are written on the master, how they are transferred to the replication slave and then how they are applied there. Note that this is NOT a HOWTO setup replication,…
    Tags: replication, master, slave, sql
  • 48
    Here we are going to create a Master -> Master -> Slave -> Slave replications architecture. I am expecting here , you have MySQL installed and set-up as normal.       Master 1 will be known as Master 1 and Slave 2 with IP 10.1.1.1 Master 2 will be known as…
    Tags: slave, mysql, master

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: