MySQL master - slave replication setup

 Hi guys, I hope you are doing great. Today here I am going to explain about MySQL master – slave replication setup.

For the setup I have two servers clota-labl01 and clota-labl02. Here on clota-labl01 I am going to setup master and clota-labl02 as slave DB.

Connect master server…

++++++++++++++++++++

clota-labl01

++++++++++++++++++++

 

my.cnf configuration…

=================

[mysqld]

server-id=100

bind-address=192.168.0.158

log_bin=mysql-bin

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

relay-log=/var/log/mysql_slave/relay-bin

## "relay-log" is only for slave. We can skip if the server is serving as primary.

 

Note: log_bin parameter, enables binary logging. With binary logging enabled, the server logs all statements that change data to the binary log, which is used for backup and replication. The binary log is a sequence of files with a base name and numeric extension

Backup dump creation…

mysqldump -u root -p --source-data=2 -f  i3 > /tmp/i3_final_bkp__111.sql

 

create a user for replication…

Create USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'Lucknow@123';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

 

Check master position..

mysql> show master status \G

*************************** 1. row ***************************

        File: mysql-bin.000002

        Position: 3230

                                Binlog_Do_DB:

                                Binlog_Ignore_DB:

                                Executed_Gtid_Set:

 

 

Connect Slave Server

++++++++++++++++++++

clota-labl02

++++++++++++++++++++

my.cnf configuration…

my.cnf

=================

[mysqld]

server-id=101

bind-address=192.168.0.159

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

log_bin=mysql_bin_log

relay-log=/var/log/mysql_slave/relay-bin

replicate-do-table=i3.all_india_PO_list

replicate-do-table=i3.emp

 

Restore DB dump...

mysql -u root -p i3 -vv < /root/i3_final_bkp__111.sql

 

Setup replication….

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.158',MASTER_USER='repl', MASTER_PASSWORD='Lucknow@123', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=3230, source_port=3306 ;

 

Start Stave 

mysql> start slave;

 

Check Replication Status 

mysql> show replica status \G

*************************** 1. row ***************************

             Replica_IO_State: Waiting for source to send event

                  Source_Host: 192.168.0.158

                  Source_User: repl

                  Source_Port: 3306

                Connect_Retry: 60

              Source_Log_File: mysql-bin.000002

          Read_Source_Log_Pos: 3230

               Relay_Log_File: relay-bin.000002

                Relay_Log_Pos: 1894

        Relay_Source_Log_File: mysql-bin.000002

           Replica_IO_Running: Yes

          Replica_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table: i3.all_india_PO_list,i3.emp

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Source_Log_Pos: 3230

              Relay_Log_Space: 2546

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Source_SSL_Allowed: No

           Source_SSL_CA_File:

           Source_SSL_CA_Path:

              Source_SSL_Cert:

            Source_SSL_Cipher:

               Source_SSL_Key:

        Seconds_Behind_Source: 0

Source_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Source_Server_Id: 100

                  Source_UUID: b7efa411-d738-11ec-93b7-000c29dc1823

             Source_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates

           Source_Retry_Count: 86400

                  Source_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Source_SSL_Crl:

           Source_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position: 0

         Replicate_Rewrite_DB:

                 Channel_Name:

           Source_TLS_Version:

       Source_public_key_path:

        Get_Source_public_key: 0

            Network_Namespace:

 

Every thing looks great here.

Important Replication Commands...

To know the master position:- 

show master status \G

Output 

File: mysql_bin_log.000002

Position: 157

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

Set Replication:-

CHANGE REPLICATION 
SOURCE TO 
SOURCE_HOST='source_ip_address',
SOURCE_PORT = port_num,
SOURCE_USER='replica_account_name', 
SOURCE_PASSWORD='REPLICA_PASSWORD', 
SOURCE_LOG_FILE='log_file_name', 
SOURCE_LOG_POS=log_position;

START REPLICA;

SHOW REPLICA STATUS \G;

STOP REPLICA;

RESET REPLICA;

Makes the replica forget its position in the source's binary log.

RESET REPLICA ALL;

Reset replication.

 Thank you for reading the blog till end.

 --By Abhishek Yadav...

Stay In Touch: