HOW TO SETUP GTID MASTER SLAVE REPLICATION MYSQL 5.7

A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup. There is a 1-to-1 mapping between all transactions and all GTIDs. A GTID is represented as a pair of coordinates, separated by a colon character (:), as shown here:

GTID = source_id:transaction_idMySQL_gtid-master-slave

Advantages :
● Setting up MySQL replication is simple
● Consistency is guaranteed between master and slaves.
● Simple to determine inconsistency
● Fail-over process become much easier.

step.1. stop the both master and slave servers.

systemctl stop mysqld

step.2. configure the master server.

To enable GTID-based replication, each server must be started with GTID mode enabled by setting the gtid_mode variable to ON, and with the enforce_gtid_consistency variable enabled to ensure that only statements which are safe for GTID-based replication are logged.

vi /etc/my.cnf

add the following paramete under [mysql] section of my.cnf file

#master
server-id = 1
log-bin = mysql-bin
binlog_format = row
gtid-mode=ON
enforce-gtid-consistency
log-slave-updates

ESc:wq ENTER

step.3.Now start mysqld services

systemctl start mysqld

step.4. Checking master status

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

OR

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

and check gtid_executed display out blank

mysql> show global variables like 'gtid_executed';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_executed |       |
+---------------+-------+
1 row in set (0.53 sec)

step.5. now create a replication user for slave servers

mysql> create user 'rpl_user'@'%' identified by 'Rpluser@123';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'rpl_user'@'%';
Query OK, 0 rows affected (0.00 sec)


step.6.After create user checking master status,I am getting Executed_Gtid_Set

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 763
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 1e9e14b8-5479-11ea-ab42-000c29ba8011:1-3
1 row in set (0.00 sec)

mysql> show global variables like 'gtid_executed';
+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| gtid_executed | 1e9e14b8-5479-11ea-ab42-000c29ba8011:1-3 |
+---------------+------------------------------------------+
1 row in set (0.00 sec)

 step.7. Now taking full backup with mysqldump with master server

 mysql> mysqldump --all-databases -flush-privileges --single-transaction --flush-logs --triggers --routines --events --user=root  --password=XXXXXXXX > mysqlbackup_dump.sql;

 step.8.Transfer backup master to slave server

 [root@master ~]# scp mysqlbackup_dump.sql pankaj@192.168.145.128:.
 pankaj@192.168.145.128's password:
 mysqlbackup_dump.sql                                                                                           100%  830KB   2.3MB/s   00:00


After that working on salve servers

step.9. stop mysqld services

systemctl stop mysqld

step.10.configure the slave server

[root@slave ~]# vi /etc/my.cnf

Add the following parameter under [mysqld] section of my.cnf file

#slave
server-id = 2
log-bin = mysql-bin
relay-log = relay-log-server
read-only = ON
gtid-mode=ON
enforce-gtid-consistency
log-slave-updates

ESC:wq ENTER

step.11.start mysqld services

systemctl start mysqld

step.12.Checking status for the slave server and gtid_executed.

mysql> show slave status\G
Empty set (0.00 sec)

mysql> show global variables like 'gtid_executed';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_executed |       |
+---------------+-------+
1 row in set (0.03 sec)


step.13.Load the mysqldump in slave server.

mysql> source /home/pankaj/mysqlbackup_dump.sql ;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

after checking gtid_executed

mysql> show global variables like 'gtid_executed';
+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| gtid_executed | 1e9e14b8-5479-11ea-ab42-000c29ba8011:1-4 |
+---------------+------------------------------------------+
1 row in set (0.00 sec)


step.14.Execute the Change Master

Tell the slave to use the master with GTID based transactions as the replication data source, and to use GTID-based auto-positioning rather than file-based positioning.
mysql>CHANGE MASTER TO
MASTER_HOST = '54.89.xx.xx',
MASTER_PORT = 3306,
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'XXXXXXXXX',
MASTER_AUTO_POSITION = 1;

step.15.start slave server

start slave;

step.16.checking slave status


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                    Master_Host: 192.168.X.X
                    Master_User: repl_user
                     Master_Port: 3306
                 Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
     Read_Master_Log_Pos: 184224424
                 Relay_Log_File: relay-log-server.000006
                  Relay_Log_Pos: 182845119
     Relay_Master_Log_File: mysql-bin.000002
             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: 184224424
              Relay_Log_Space: 183090775
              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: ffb64763-5ed6-11ea-b7d9-525400615ff4
             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 more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: ffb64763-5ed6-11ea-b7d9-525400615ff4:3-246839
            Executed_Gtid_Set: 72b3a920-5ee7-11ea-b87b-e4434b359f7a:1-1121914,
f231b58b-3370-11e9-a458-8cec4b7a7fe5:1-10029,
ffb64763-5ed6-11ea-b7d9-525400615ff4:1-246839
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

note:- you can suucessfull step master slave replication

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Thank You

Comments

Post a Comment

Popular posts from this blog

MySQL Point in Time Recovery: How To Configure And How Does it Work?

Image
 MySQL Point in Time Recovery: How To Configure And How Does it Work? The point-in-time recovery allows you to restore a MySQL database to a specific time in the past. The point-in-time recovery relies on two key components: Full backup: This serves as the foundation for recovery, providing the starting state of the database. Binary logs: These binary log files record all changes made to the database, allowing you to replay those changes to a desired point. If you don’t have a full backup or binary logs enabled, you cannot carry the point-in-time recovery. We’ll illustrate how to perform the point-in-time recovery in MySQL to recover a database to a specific point in time. Checking binar log status:- We need login to mysql server and run given below command:- show global variables like 'log_bin' ; Show output for enable mysql-bin log. Creating a new sample database & taking a full backup:- We are going to login in mysql databse and creating new database and table.given ...
Read more

MySQL Replication Switchover: Step-by-Step Guide

Image
 MySQL Replication Switchover: Step-by-Step Guide MySQL replication is a powerful feature that allows you to create redundant copies of your database for improved availability and scalability. In certain scenarios, it becomes necessary to switch the roles of the master and slave servers in a replication setup. This article will guide you through the process of performing a MySQL replication switchover, ensuring a smooth transition without any data loss. Let’s dive into the step-by-step instructions. I have tow node one is master node and another is slave node.Given below details:- Current Master: 192.168.70.10 Current Slave : 192.168.70.11 Verifying the Replication:- Before initiating the switchover, it’s important to verify that replication is functioning correctly on the current Slave server (192.168.70.11). Execute the following queries to ensure the replication status: mysql> show slave status\G This command displays the slave’s status, including information such as replicat...
Read more

Mysql Commercial Mysqlbackup:How to take Differential or Incremental Backup and resotre using mysqlbackup utility in mysql 8.0.37 enterprise edition

Image
Mysql Commercial Mysqlbackup:How to take Differential or Incremental Backup and resotre using mysqlbackup utility in mysql 8.0.37 enterprise edition Incremental vs Differential Backup:- The main difference between incremental and differential backups is how they save space and time by storing only changed files. However, the effectiveness of incremental backups is very different from differential backups. Before we dive into the differences between Incremental and Differential backups let’s review Full Backups.  Are you still using Full Backups? In its simplest form, a full backup is the starting point for both Incremental and Differential backup strategies. A full backup, of course, backs up all the data on a partition or disk by copying all disk sectors with data to the backup image file.  A full backup is time-consuming and requires a lot of space, it’s usually used as part of a backup plan with long intervals, like once a week or a month. During this time, if anything goes...
Read more