How to Setup MySQL Replication

MySQL is very easy to setup and requires minimum of time and resources. In this tutorial we will setup a simple Master - Slave replication.

In this example we will use the following servers bih001(10.000.1.189)/Master and bih002(10.000.1.210)/Slave.

Follow the steps that are required to configure the MySQL replication environments.
  • Step 1: Edit the configuration files & start the MySQL Servers.
Add the following lines to your Master server in our case bih001 
 [mysqld]

server_id           = 2
log_bin             = /var/lib/mysql/mysql-bin.log
log_bin_index       = /var/lib/mysql/mysql-bin.log.index
relay_log           = /var/lib/mysql/mysql-relay-bin
relay_log_index     = /var/lib/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

auto_increment_increment = 2
bind-address = 0.0.0.0
auto_increment_offset = 1
When editing the configuration file /etc/mysql/my.cnf and be sure to use different server ids for the hosts
  • Step 2: Add the following lines to your Slave server in our case bih002.
 [mysqld]

server_id           = 3
log_bin             = /var/lib/mysql/mysql-bin.log
log_bin_index       = /var/lib/mysql/mysql-bin.log.index
relay_log           = /var/lib/mysql/mysql-relay-bin
relay_log_index     = /var/lib/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

auto_increment_increment = 2
bind-address = 0.0.0.0
auto_increment_offset = 1
Make sure the paths are correct.
  • Step 3:Start your MySQL servers with the new configuration
 [root@bih002 ~]# /etc/init.d/mysqld start
Starting mysqld:                                           [  OK  ]
  • Step 4:Create Replication User
Create an account on the master server that the slave server can use to connect. This account must be given the REPLICATION SLAVE privilege. 
 GRANT REPLICATION SLAVE ON *.* TO REPLICATION@'10.000.1.210' IDENTIFIED BY 'password';
 FLUSH PRIVILEGES;
  • Step 5:Find the master_log_file & master_log_pos
Now you need to find the master_log_file & master_log_pos that will be used for the Initialize Replication command . 
 mysql SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+

| mysql-bin.000004 |      383 |              |                  |
+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)
  • Step 5:Initialize Replication
Connect to the SLAVE server and initiate replication using the following script. Where: MASTER_HOST: the IP or host-name of the master server, in this example blue or 10.000.1.189. MASTER_USER: this is the user we granted the REPLICATION SLAVE privilege to in Step 4. MASTER_PASSWORD: this is the password we assigned to REPLICATION user in Step 4 MASTER_LOG_FILE: is mysql-bin.000004 (wouldn't be empty if there were existing writes to be picked up from the master) MASTER_LOG_POS: is 383(would likely be different if there were existing writes to be picked up from the master) 
 mysqlCHANGE MASTER TO master_host='10.000.1.189', master_port=3306, master_user='replication',
              master_password='password', master_log_file='mysql-bin.000004', master_log_pos=383;
  • Step 6:Start replication process
Connect to the slave server and issue the following command: 
  mysqlstart slave;
To check the status of the replication you can use the commands: 
mysql show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.000.1.189
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 383
               Relay_Log_File: mysql-relay-bin.000008
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          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: 383
              Relay_Log_Space: 551
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

mysql
Also create objects on the master and check if they are created on to the slave server as well.

Copyright 2020. All Rights Reserved