Restoring Slave when GTID is enabled on master

This is a quick post on issues I faced while restoring a slave when GTID was enabled on master.

I have master created few days back and now I am trying to create a slave. I have GTID enabled on master.

Master status:

root [mysql] >show master status \G
*************************** 1. row ***************************
             File: bin_log.000002
         Position: 16682
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:1-59
1 row in set (0.00 sec)
root [mysql] >show variables like '%gtid%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| enforce_gtid_consistency | ON        |
| gtid_executed            |           |
| gtid_mode                | ON        |
| gtid_next                | AUTOMATIC |
| gtid_owned               |           |
| gtid_purged              |           |
+--------------------------+-----------+
6 rows in set (0.00 sec)

Step 1) Take backup from master

mysqldump -u root -p --databases deo fctest nosql --set-gtid-purged=off > /u01/backups/mysql_backup.sql

I used above command to take backup of master.

Note that I used –set-gtid-purged=off. If GTID is enabled on master and we are creating a slave, we want slave to start reading from the point when this backup is created. Because all previous information will be restored and available on slave as part of this backup restore.

If you want slave to start reading from this point and not from the beginning you should NOT set this variable to off. I will explain the significance of this variable later in the post.

Step 2) Install and create MySQL instance

Refer to my previous post https://avdeo.com/2014/02/18/installing-mysql/ for the same

Step 3) SCP backup file to slave server

slave-host.example.com$ scp master-host:/u01/backups/mysql_backup.sql /u01/backups/mysql_backup.sql

Step 4) Import backup into slave

slave-host.example.com$ mysql -u root -p </u01/backups/mysql_backup.sql

Step 5) Start Slave

We have to point slave to our master and then start slave.

When GTID is enabled, you can use MASTER_AUTO_POSITION=1 and slave should automatically start applying from correct GTID

mysql> CHANGE MASTER TO MASTER_HOST = 'master-host.example.com', MASTER_PORT = 3306, MASTER_USER = 'replicate', MASTER_PASSWORD = 'welcome', MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.12 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master-host.example.com
                  Master_User: replicate
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin_log.000002
          Read_Master_Log_Pos: 16682
               Relay_Log_File: relay_log.000002
                Relay_Log_Pos: 547
        Relay_Master_Log_File: bin_log.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1133
                   Last_Error: Error 'Can't find any matching row in the user table' on query. Default database: 'mysql'. Query: 'SET PASSWORD FOR 'root'@'master-host.example.com'='*DF216F57F1F2066124E1AA5491D995C3CB57E4C2''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 341
              Relay_Log_Space: 17086
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1133
               Last_SQL_Error: Error 'Can't find any matching row in the user table' on query. Default database: 'mysql'. Query: 'SET PASSWORD FOR 'root'@'master-host.example.com'='*DF216F57F1F2066124E1AA5491D995C3CB57E4C2''
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
                  Master_UUID: f7718b80-c237-11e4-baa8-a0369f370a52
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 150304 08:03:47
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:1-59
            Executed_Gtid_Set: c21f28d2-c243-11e4-baf5-2c600c20dba4:1-59,
f7718b80-c237-11e4-baa8-a0369f370a52:1
                Auto_Position: 1
1 row in set (0.00 sec)

Now, if we see above slave status output, its showing error in SQL thread. SQL that is failing is Query: ‘SET PASSWORD FOR ‘root’@’master-host.example.com’=’*DF216F57F1F2066124E1AA5491D995C3CB57E4C2”

This is the first DML that was run on master. Its failing because master-host.example.com entry is not present in mysql.user table.  This is because its a slave on slave host and so root user entry will be present corresponding to slave host only.

Never the less, it should not be running DMLs from the beginning because we have restored everything until GTID 1-59.

Significance of –set-gtid-purged=off

The reason its applying transactions from the beginning is because we used –set-gtid-purged=off while taking backup.

When we are using GTID, 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

If we don’t use –set-gtid-purged=off in mysqldump, these global variables are automatically set in dump file created by mysqldump. So that when we apply that dump file on slave it will set these variables and slave will start pulling from GTID 60 onwards (in our example).

Fixing the issue

So at this point we have a backup which does not have GTID information. Lets see how to fix this issue manually.

I tried setting GTID_NEXT to 60 by first executing the transaction on master and make GTID 60 on master.

mysql> set gtid_next = "f7718b80-c237-11e4-baa8-a0369f370a52:60";
Query OK, 0 rows affected (0.00 sec)

mysql> begin; commit;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> set gtid_next = "AUTOMATIC";
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

But this didn’t work.

We know that master is done until GTID 59 and we have those transaction in backup and slave also have them because backup was restored completely.

Lets try to set GTID_PURGED to remove those transactions from BINLOG

mysql> SET GLOBAL gtid_purged="f7718b80-c237-11e4-baa8-a0369f370a52:1:59";
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

Its saying that GTID_EXECUTED should be empty. Lets check whats the current value and try to set to empty

mysql> show global variables like 'gtid_executed';
+---------------+--------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                |
+---------------+--------------------------------------------------------------------------------------+
| gtid_executed | c21f28d2-c243-11e4-baf5-2c600c20dba4:1-59,
f7718b80-c237-11e4-baa8-a0369f370a52:1:59 |
+---------------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL gtid_executed = "";
ERROR 1238 (HY000): Variable 'gtid_executed' is a read only variable

We cannot assign empty value to gtid_executed variable because its read-only and since we restored the backup gtid_executed is updated till the point this instance has executed the transactions.

Only way to fix this is using reset master on slave

When we issue reset master on slave, it will reset GTID_EXECUTED and make it null. After that we can set GTID_PURGED till 1-59 and slave can start applying from 60

mysql> reset master;
Query OK, 0 rows affected (0.07 sec)

mysql> show global variables like 'GTID_EXECUTED';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_executed |       |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set global GTID_PURGED="f7718b80-c237-11e4-baa8-a0369f370a52:1-59";
Query OK, 0 rows affected (0.07 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master-host.example.com
                  Master_User: replicate
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin_log.000002
          Read_Master_Log_Pos: 16953
               Relay_Log_File: relay_log.000004
                Relay_Log_Pos: 442
        Relay_Master_Log_File: bin_log.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: 16953
              Relay_Log_Space: 1202
              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: 2
                  Master_UUID: f7718b80-c237-11e4-baa8-a0369f370a52
             Master_Info_File: mysql.slave_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: f7718b80-c237-11e4-baa8-a0369f370a52:1-60
            Executed_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:1-60
                Auto_Position: 1
1 row in set (0.00 sec)

This fixed the issue.

Hope this helps !!

References:

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

 

Advertisements