Thursday, 26 September 2013

MySQL Standby Creation (Master - Slave Replication)



Contents

. 0

MySQL standby Creation (Master - Slave Replication). 2

Environment Details: 2

Configuration Steps on Master Node. 2

Step 1: Mandatory  parameters (/etc/my.cnf). 2
Step 2: User Creation. 2
Step 3: Master Status. 3

Configuration Steps on Slave Node. 3

Step 4: Mandatory Parameters (/etc/my.cnf). 3
Step 5: Connection Testing (Slave to Master). 3
Step 6:  Configuration of Slave process. 3
Step 7:  Start Slave. 4
Step 8: Slave Status. 4
Step 9: Test the Replication. 6

Switchover (Slave to Master). 6

    Master Node. 6
    Slave Node. 6



MySQL standby Creation (Master - Slave Replication)

Environment Details:


Master Node
10.0.0.81
Master Node Hostname              
mysqlnode1.sukumar.com


Slave Node
10.0.0.82
Slave Node Hostname
mysqlnode2.sukumar.com

Configuration Steps on Master Node

Step 1: Mandatory  parameters (/etc/my.cnf)

                                                                       
Below Listed parameters are mandatory for Master node.  Make sure to set the unique server-id

[mysql@mysqlnode1 ~]$ cat /etc/my.cnf
[mysqld]
log-bin=/var/lib/mysql/mysql-bin
max_binlog_size=4096
binlog_format=row
socket=/var/lib/mysql/mysql.sock
server-id=1
binlog_do_db=demo
binlog-ignore-db=mysql
binlog-ignore-db=test

[client]
socket=/var/lib/mysql/mysql.sock

[mysqld_safe]
err-log=/var/log/mysqld-node1.log

Step 2: User Creation


Connect to Mysql and create dedicated user for replication. Grant the required privileges to connect from slave node.

mysql> grant replication slave on *.* to
    -> 'rep_user'@'10.0.0.82' identified by 'rep_user';
Query OK, 0 rows affected (0.00 sec)

mysql> select user, host from mysql.user
    -> where user='rep_user';
+----------+-----------+
| user     | host      |
+----------+-----------+
| rep_user | 10.0.0.82 |
+----------+-----------+
1 row in set (0.00 sec)

Step 3: Master Status


 Check the master status

mysql> show master status;
+------------------+----------+--------------+----------- --+-----------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set
+------------------+----------+--------------+--------------+-----------------+
| mysql-bin.000005 |      120 | demo         | mysql,test   |                  
+------------------+----------+--------------+--------------+-----------------+
1 row in set (0.00 sec)

Configuration Steps on Slave Node

Step 4: Mandatory Parameters (/etc/my.cnf)


 Below Listed parameters are mandatory for Slave node.  Make sure to set the unique server-id

mysql@mysqlnode2 ~]$ cat /etc/my.cnf
[mysqld]
log-bin=/var/lib/mysql/mysql2-bin
max_binlog_size=4096
binlog_format=row
socket=/var/lib/mysql/mysql.sock
server-id=2

[client]
socket=/var/lib/mysql/mysql.sock

Step 5: Connection Testing (Slave to Master)


Test the connection from slave node to Master node by using the below command.

mysql@mysqlnode2 ~]$ mysql -u rep_user -h mysqlnode1.sukumar.com -prep_user demo

Step 6:  Configuration of Slave process


This will configure slave and server will remember settings, so this replaces my.cnf settings in latest versions of MySQL server.
Note: Set the values appropriate with respect to the Master Node. Master_log_file and position values should be from master status on master node.

mysql@mysqlnode2 ~]$ mysql -u root -pwelcome123 demo

mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.81',
       MASTER_USER='rep_user',
       MASTER_PASSWORD='rep_user',
       MASTER_PORT=3306,
       MASTER_LOG_FILE='mysql-bin.000005',
       MASTER_LOG_POS=120,
       MASTER_CONNECT_RETRY=10;

Step 7:  Start Slave


Start the slave process with the below command.

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

If you are receiving “ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository” error. please reset the slave and proceed with step 6.

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.81', MASTER_USER='rep_user', MASTER_PASSWORD='rep_user', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=120, MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

Step 8: Slave Status


Make sure to check the below parameter status should be "YES" and the remaining values are appropriate.

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.81
                  Master_User: rep_user
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysqlnode2-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000005
             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: 120
              Relay_Log_Space: 461
              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:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 8a701d66-2119-11e3-9ab2-0689f6cf2c77
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0

Step 9: Test the Replication


Perform the transactions on master node and slave node will automatically be in sync.

Switchover (Slave to Master)


Master Node


FLUSH LOGS closes and reopens all log files. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file.

FLUSH LOGS;

Slave Node


Stop the slave process and reset the master. This will configure the master and it act according to my.cnf configuration settings.

STOP SLAVE;
RESET MASTER;

Post a Comment