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;