MySQL Multi Source Master Slave Replication using GTID

-
MySQL gtid replication


We are using GTID-based master-slave replication. In this replication, both master and slave should have the same GTID. This blog post aims to provide a step-by-step guide to help you set up MySQL Replication using GTIDs and help you replicate your MySQL data with ease.

Prerequisite

Master Source 1
  1. Channel name: DEVELOPMENT
  2. Database: mydb1
  3. Enable Replication For Tables
    1. testing1
    2. testing2
    3. testing3
  4. Mysql Server Running: 192.168.0.201:3307
Master Source 2
  1. Channel name: PRODUCTION
  2. Database: mydb1
  3. Enable Replication For Tables
    1. testing4
    2. testing5
    3. testing6
  4. Mysql Server Running: 192.168.0.201:3308
Slave
  1. Mysql Server Running: 192.168.0.209:3306

Configuration of  masters(192.168.0.201:3307/192.168.0.201:3308) and slave (192.168.0.209:3306)

my.cnf config for master (192.168.0.201:3307)



my.cnf config for master (192.168.0.201:3308)



my.cnf config for slave (192.168.0.209:3306)



Steps for taking a dump and restoring it

Run the following commands in both masters 192.168.0.201:3307 and 192.168.0.201:3308
    1. Dump the database on the master: mysqldump --set-gtid-purged=OFF -u root -p mydb1 > backup.sql
    2. Restore the database on the slave: 
    CREATE DATABASE mydb1;
    mysql -u root -p mydb1 < backup.sql

    Step to enable replication on the master

    Run the following commands in both masters 192.168.0.201:3307 and 192.168.0.201:3308
    1. Check GTID is ON: SHOW VARIABLES LIKE 'gtid_mode';
    2. Create a replication user on the master: CREATE USER 'replication_user'@'192.168.0.209' IDENTIFIED WITH mysql_native_password BY 'password';
    3. Grant replication privileges to the user: GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'192.168.0.209'; FLUSH PRIVILEGES;
    4. Show the master status: SHOW MASTER STATUS\G;
    5. Disable WRITE operation on master till we enable replication: SET @@GLOBAL.read_only = ON;

    Steps to enable replication on the slave

    Run the following commands in the slave 192.168.0.209:3306 server node.
      1. Check GTID is ON: SHOW VARIABLES LIKE 'gtid_mode';
      2. Stop and reset the slave on the slave server: 
        STOP REPLICA FOR CHANNEL "DEVELOPMENT"; 
        RESET REPLICA FOR CHANNEL "DEVELOPMENT";
      3. Add GTID on the slave for channel DEVELOPMENT: SET GLOBAL gtid_purged='ccb0d141-dc54-11ee-923c-0242c0a8b005:1-33'; <!--- Should be same as master 192.168.0.201:3307 --->
      4. Change the master settings on the slave for channel DEVELOPMENT: CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.0.201', SOURCE_PORT=3307, SOURCE_USER='replication_user', SOURCE_PASSWORD='password', SOURCE_AUTO_POSITION=1 FOR CHANNEL "DEVELOPMENT";
      5. Create table filters for replication for DEVELOPMENT channel: CHANGE REPLICATION FILTER REPLICATE_DO_TABLE = (mydb1.testing1, mydb1.testing2, mydb1.testing3) FOR CHANNEL "DEVELOPMENT";
      6. Start the slave for the specified channel: START REPLICA FOR CHANNEL "DEVELOPMENT";
      7. Show the slave status for the specified channel: SHOW REPLICA STATUS FOR CHANNEL "DEVELOPMENT"\G;
      8. Add GTID on the slave for channel PRODUCTION: SET GLOBAL gtid_purged='180c0583-e1e0-11ee-84fa-0242c0a84003:1-107'; <!--- Should be same as master 192.168.0.201:3308 --->
      9. Change the master settings on the slave for channel PRODUCTION: CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.0.201', SOURCE_PORT=3308, SOURCE_USER='replication_user', SOURCE_PASSWORD='password', SOURCE_AUTO_POSITION=1 FOR CHANNEL "PRODUCTION";
      10. Create table filters for replication for the PRODUCTION channel: CHANGE REPLICATION FILTER REPLICATE_DO_TABLE = (mydb1.testing4, mydb1.testing5, mydb1.testing6) FOR CHANNEL "PRODUCTION";
      11. Start the slave for the specified channel: START REPLICA FOR CHANNEL "PRODUCTION";
      12. Show the slave status for the specified channel: SHOW REPLICA STATUS FOR CHANNEL "PRODUCTION"\G;
      13. Disable Read Only permission on both master: SET @@GLOBAL.read_only = OFF;


      To verify replication working

      1. Check masters GTID present in slave: show global variables like 'gtid_executed'; <!---Should be same as master--->
      2. Get detailed information about the replication applier status, including GTID-related information.
        SELECT * FROM performance_schema.replication_applier_status_by_worker;
      3. Check replication_user is present in the master: SELECT User, Host FROM mysql.user WHERE User = 'replication_user';

      Thank you! 😀 Hope you like my bog post.

      Comments

      Post a Comment

      Please give us your valuable feedback

      Popular posts from this blog

      How to pass parameters in webhook?

      -
      Image
      Hey everyone 😊, Today I want to share another interesting thing which I found in StackOverflow and that is how to pass parameters in webhook. Those who don't know about webhook I advise you to google about it before going further reading.  So for demonstration purpose, I am going to use Jenkins and Generic Webhook plugin . I created a job and called it testing  and select Generic Webhook  Trigger . Thereafter I add Request Parameter   and in the request parameter I added the name of the request which is name Thereafter I added a token to specify that this job can only be triggered. After this, I select Execute Shell from Build  and write the following code  After doing this add webhook URL in bitbucket repository or  GitHub repository with the parameter. The URL is look something like this  Webhook URL:- https://[YOUR_JENKINS_URL]/generic-webhook-trigger/invoke?token=trigger&name=Svastikkka  The configuration will something like this After saving my webhook configuration I
      Read more

      Access and modify all the resources of our Wiki.js using WikiJS API

      -
      Image
      Hi everyone 😎 , Today we are going to see how to access or modify WikiJS resources using WikiJS API. WikiJS exposes GraphQL API to access and modify the stuff according to our requirements. So In this blog post, we going to see  How to set up WikiJS's local environment using docker. How to generate an API token in Wikijs. Using API tokens how to access WikiJS resources and how to modify them. First Local Setup using docker Here I am using docker to set up my local isolated WikiJS environment. Below is my docker-compose.yml file written by WikiJS contributors. To build the WikiJS environment we just need to run the following command  docker-compose up -df  [PATH_OF_DOCKER_COMPOSE_YML_FILE] To check the environment is in a running state run the following command docker ps Second Generate API token After setup our local environment we are ready to create our initial Administrator account in our WikiJS. 1. On the browser type localhost:80 2. Create an Administrator account  Email: loc
      Read more

      Fahrenheit to Celsius

      -
      Level EASY Given three values - Start Fahrenheit Value (S), End Fahrenheit value (E) and Step Size (W), you need to convert all Fahrenheit values from Start to End at the gap of W, into their corresponding Celsius values and print the table. Input Format : 3 integers - S, E and W respectively Output Format : Fahrenheit to Celsius conversion table. One line for every Fahrenheit and corresponding Celsius value. On Fahrenheit value and its corresponding Celsius value should be separate by tab ("\t") Constraints : 0 <= S <= 80 S <= E <= 900 0 <= W <= 40 Sample Input 1: 0 100 20 Sample Output 1: 0 -17 20 -6 40 4 60 15 80 26 100 37 Sample Input 2: 20 119 13 Sample Output 2: 20 -6 33 0 46 7 59 15 72 22 85 29 98 36 111 43 Explanation For Input 2: We need need to start calculating the Celsius values for each of the Fahrenheit Value which starts from 20. So starting from 20 which is the given Fahrenheit start
      Read more