DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • MySQL Multi-Source Replication
  • Raft in Tarantool: How It Works and How to Use It
  • Doris: Unifying SQL Dialects for a Seamless Data Query Ecosystem
  • Evaluating Performance Gains in MySQL Lock Scheduling Algorithms

Trending

  • Java's Quiet Revolution: Thriving in the Serverless Kubernetes Era
  • Event-Driven Architectures: Designing Scalable and Resilient Cloud Solutions
  • Recurrent Workflows With Cloud Native Dapr Jobs
  • AI, ML, and Data Science: Shaping the Future of Automation
  1. DZone
  2. Data Engineering
  3. Databases
  4. How to create/restore a slave using GTID replication in MySQL 5.6

How to create/restore a slave using GTID replication in MySQL 5.6

By 
Peter Zaitsev user avatar
Peter Zaitsev
·
Feb. 11, 13 · Interview
Likes (0)
Comment
Save
Tweet
Share
18.4K Views

Join the DZone community and get the full member experience.

Join For Free
This post comes from Miguel Angel Nieto at the MySQL Performance Blog.

MySQL 5.6 is GA! Now we have new things to play with and in my personal opinion the most interesting one is the new Global Transaction ID (GTID) support in replication. This post is not an explanation of what is GTID and how it works internally because there are many documents about that:

http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-concepts.html

One thing that worths to mention is that if you want GTID support log_slave_updates will need to be enabled in slave server and the performance impact should be taken in account.

Anyway, this post tends to be more practical, we will see how to create/restore new slaves from a master using GTID.

How to set up a new slave

The first thing that we need to know is that now Binary Logs and Position are not needed anymore with GTID enabled. Instead we need to know in which GTID is the master and set it on the slave. MySQL keeps two global variables with GTID numbers on it:

gtid_executed: it contains a representation of the set of all transaction logged in the binary log
gtid_purged: it contains a representation of the set of all transactions deleted from the binary log

So now, the process is the following:

    • take a backup from the master and store the value of gtid_executed
    • restore the backup on the slave and set gtid_purged with the value of gtid_executed from the master

    The new mysqldump can do those tasks for us. Let’s see an example of how to take a backup from the master and restore it on the slave to set up a new replication server.

    master > show global variables like 'gtid_executed'; 
    +---------------+-------------------------------------------+
    | Variable_name | Value                                     |
    +---------------+-------------------------------------------+
    | gtid_executed | 9a511b7b-7059-11e2-9a24-08002762b8af:1-13 |
    +---------------+-------------------------------------------+
    
    master > show global variables like 'gtid_purged';
    +---------------+------------------------------------------+
    | Variable_name | Value                                    |
    +---------------+------------------------------------------+
    | gtid_purged   | 9a511b7b-7059-11e2-9a24-08002762b8af:1-2 |
    +---------------+------------------------------------------+

    Now we take a backup with mysqldump from the master:

    # mysqldump --all-databases --single-transaction --triggers --routines --host=127.0.0.1 --port=18675 --user=msandbox --password=msandbox > dump.sql

    It will contain the following line:

    # grep PURGED dump.sql
    SET @@GLOBAL.GTID_PURGED='9a511b7b-7059-11e2-9a24-08002762b8af:1-13';

    Therefore during the dump recover process on the slave it will set GTID_PURGED to the GTID_EXECUTED value from the master.

    So now, we just need to recover the dump and start the replication:

    slave1 > show global variables like 'gtid_executed';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | gtid_executed |       |
    +---------------+-------+
    
    slave1 > show global variables like 'gtid_purged';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | gtid_purged   |       |
    +---------------+-------+
    
    slave1 > slave1> source test.sql;
    [...]
    
    slave1 > show global variables like 'gtid_executed';
    +---------------+-------------------------------------------+
    | Variable_name | Value                                     |
    +---------------+-------------------------------------------+
    | gtid_executed | 9a511b7b-7059-11e2-9a24-08002762b8af:1-13 |
    +---------------+-------------------------------------------+
    
    slave1 > show global variables like 'gtid_purged';
    +---------------+-------------------------------------------+
    | Variable_name | Value                                     |
    +---------------+-------------------------------------------+
    | gtid_purged   | 9a511b7b-7059-11e2-9a24-08002762b8af:1-13 |
    +---------------+-------------------------------------------+

    The last step is to configure the slave using the auto-configuration method of GTID:

    slave1 > CHANGE MASTER TO MASTER_HOST="127.0.0.1", MASTER_USER="msandbox", MASTER_PASSWORD="msandbox", MASTER_PORT=18675, MASTER_AUTO_POSITION = 1;

    How to restore a slave in a bad and fast way

    Let’s imagine that our slave has been down for several days and the binary logs from the master have been purged. This is the error we are going to get:

    Slave_IO_Running: No
    Slave_SQL_Running: Yes
    Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

    So, let’s try to solve it. First we have the bad and fast way, that is, point to another GTID that the master has in the binary logs. First, we get the GTID_EXECUTED from the master:

    master > show global variables like 'GTID_EXECUTED';
    +---------------+-------------------------------------------+
    | Variable_name | Value                                     |
    +---------------+-------------------------------------------+
    | gtid_executed | 9a511b7b-7059-11e2-9a24-08002762b8af:1-14 |
    +---------------+-------------------------------------------+

    And we set it on the slave:

    slave> set global GTID_EXECUTED="9a511b7b-7059-11e2-9a24-08002762b8af:1-14"
    ERROR 1238 (HY000): Variable 'gtid_executed' is a read only variable

    Error! Remember, we get the GTID_EXECUTED from the master and set is as GTID_PURGED on the slave.

    slave1 > set global GTID_PURGED="9a511b7b-7059-11e2-9a24-08002762b8af:1-14";
    ERROR 1840 (HY000): GTID_PURGED can only be set when GTID_EXECUTED is empty.

    Error again, GTID_EXECUTED should be empty before changing GTID_PURGED manually but we can’t change it with SET because is a read only variable. The only way to change it is with reset master (yes, on a slave server):

    slave1> reset master;
    slave1 > show global variables like 'GTID_EXECUTED';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | gtid_executed |       |
    +---------------+-------+
    slave1 > set global GTID_PURGED="9a511b7b-7059-11e2-9a24-08002762b8af:1-14";
    slave1> start slave io_thread;
    slave1> show slave status\G
    [...]
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    [...]

    Now, if you don’t get any error like primary/unique key duplication then you can run the pt-table-checksum and pt-table-sync.

    How to restore a slave in a good and slow way

    The good way is mysqldump again. We take a dump from the master like we saw before and try to restore it on the slave:

    slave1 [localhost] {msandbox} ((none)) > source test.sql;
    [...]
    ERROR 1840 (HY000): GTID_PURGED can only be set when GTID_EXECUTED is empty.
    [...]

    Wop! It is important to mention that these kind of error messages can dissapear on the shell buffer because the restore of the dump will continue. Be cautious.

    Same problem again so same solution too:

    slave1> reset master;
    slave1> source test.sql;
    slave1> start slave;
    slave1> show slave status\G
    [...]
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    [...]

    Conclusion

    With the new GTID we need to change our minds. Now binary log and position is not something we need to take in account, gtid_executed and gtid_purged are our new friends. Xtrabackup still doesn’t support it but we are working on it. I will update this post and create a one when we publish a xtrabackup version with full support of GTID.


  • Replication (computing) MySQL master

    Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

    Opinions expressed by DZone contributors are their own.

    Related

    • MySQL Multi-Source Replication
    • Raft in Tarantool: How It Works and How to Use It
    • Doris: Unifying SQL Dialects for a Seamless Data Query Ecosystem
    • Evaluating Performance Gains in MySQL Lock Scheduling Algorithms

    Partner Resources

    ×

    Comments
    Oops! Something Went Wrong

    The likes didn't load as expected. Please refresh the page and try again.

    ABOUT US

    • About DZone
    • Support and feedback
    • Community research
    • Sitemap

    ADVERTISE

    • Advertise with DZone

    CONTRIBUTE ON DZONE

    • Article Submission Guidelines
    • Become a Contributor
    • Core Program
    • Visit the Writers' Zone

    LEGAL

    • Terms of Service
    • Privacy Policy

    CONTACT US

    • 3343 Perimeter Hill Drive
    • Suite 100
    • Nashville, TN 37211
    • support@dzone.com

    Let's be friends:

    Likes
    There are no likes...yet! 👀
    Be the first to like this post!
    It looks like you're not logged in.
    Sign in to see who liked this post!