Tech Notes:- Enabling back GTID based replication on-line on a server which was started with GTID mode on , did some transactions and then switched it to GTID mode off

This is a special scenario were two  MySQL 5.7 servers were started with GTID_MODE=ON, executed some transactions like creating users and granting privileges, and then the GTID_MODE was switched off on both.

Now we want to configure these servers as master-slave, enable the traditional method of replication and then later switch to GTID based replication.

Here are the challenges.

1. We already created some transactions(creating users) on the master which means we have some GTID based transactions already logged in the binary logs as you can see below from the master binary logs
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |      1037 |
| mysql-bin.000003 |      6798 |
| mysql-bin.000004 |       217 |
| mysql-bin.000005 |       217 |
| mysql-bin.000006 |       825 |
| mysql-bin.000007 |       217 |
| mysql-bin.000008 |       378 |
| mysql-bin.000009 |  11592653 |
| mysql-bin.000010 |  36338956 |
| mysql-bin.000011 |  68473681 |
| mysql-bin.000012 | 198652424 |
+------------------+-----------+
12 rows in set (0.04 sec)

mysql> show binlog events in 'mysql-bin.000001';

+------------------+-----+----------------+-----------+-------------+------------------------------------------+

| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                     |

+------------------+-----+----------------+-----------+-------------+------------------------------------------+

| mysql-bin.000001 |   4 | Format_desc    |        55 |         123 | Server ver: 5.7.30-33-log, Binlog ver: 4 |

| mysql-bin.000001 | 123 | Previous_gtids |        55 |         154 |                                          |

| mysql-bin.000001 | 154 | Stop           |        55 |         177 |                                          |

+------------------+-----+----------------+-----------+-------------+------------------------------------------+

3 rows in set (0.02 sec)

mysql> show binlog events in 'mysql-bin.000002';

+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+

| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                              |

+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+

| mysql-bin.000002 |    4 | Format_desc    |        55 |         123 | Server ver: 5.7.30-33-log, Binlog ver: 4                          |

| mysql-bin.000002 |  123 | Previous_gtids |        55 |         154 |                                                                   |

| mysql-bin.000002 |  154 | Gtid           |        55 |         219 | SET @@SESSION.GTID_NEXT= 'ab7c6739-be64-11ea-b478-080027fd2c56:1' |

| mysql-bin.000002 |  219 | Query          |        55 |         287 | BEGIN                                                             |

| mysql-bin.000002 |  287 | Table_map      |        55 |         459 | table_id: 99 (mysql.user)                                         |

| mysql-bin.000002 |  459 | Update_rows    |        55 |         793 | table_id: 99 flags: STMT_END_F                                    |

| mysql-bin.000002 |  793 | Query          |        55 |         862 | COMMIT                                                            |

| mysql-bin.000002 |  862 | Gtid           |        55 |         927 | SET @@SESSION.GTID_NEXT= 'ab7c6739-be64-11ea-b478-080027fd2c56:2' |

| mysql-bin.000002 |  927 | Query          |        55 |        1014 | FLUSH PRIVILEGES                                                  |

| mysql-bin.000002 | 1014 | Stop           |        55 |        1037 |                                                                   |

+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+

10 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000003';

+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+

| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                   |

+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+

| mysql-bin.000003 |    4 | Format_desc    |        55 |         123 | Server ver: 5.7.30-33-log, Binlog ver: 4                                                                                                               |

| mysql-bin.000003 |  123 | Previous_gtids |        55 |         194 | ab7c6739-be64-11ea-b478-080027fd2c56:1-2                                                                                                               |

| mysql-bin.000003 |  194 | Gtid           |        55 |         259 | SET @@SESSION.GTID_NEXT= 'ab7c6739-be64-11ea-b478-080027fd2c56:3'                                                                                      |

| mysql-bin.000003 |  259 | Query          |        55 |         464 | CREATE USER 'slave'@'db-%.example.com' IDENTIFIED WITH 'mysql_native_password' AS '*B60C3C82CCEBA73F2BDC69601B65DFBAB435A2DA'                 |

| mysql-bin.000003 |  464 | Gtid           |        55 |         529 | SET @@SESSION.GTID_NEXT= 'ab7c6739-be64-11ea-b478-080027fd2c56:4'                                                                                      |

| mysql-bin.000003 |  529 | Query          |        55 |         685 | GRANT REPLICATION SLAVE ON *.* TO 'slave'@'db-%.example.com'                                                                                  |

2. After we switched the GTID mode to off, we have anonymous transactions in the binlogs on master

mysql> show binlog events in 'mysql-bin.000006';

+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------+

| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                          |

+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------+

| mysql-bin.000006 |   4 | Format_desc    |        55 |         123 | Server ver: 5.7.30-33-log, Binlog ver: 4                                                                      |

| mysql-bin.000006 | 123 | Previous_gtids |        55 |         194 | ab7c6739-be64-11ea-b478-080027fd2c56:1-29                                                                     |

| mysql-bin.000006 | 194 | Anonymous_Gtid |        55 |         259 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                          |

| mysql-bin.000006 | 259 | Query          |        55 |         444 | CREATE USER 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*0E840B1E4D3B2DDE222EA13524CA92099785A3E4' |

| mysql-bin.000006 | 444 | Anonymous_Gtid |        55 |         509 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                          |

| mysql-bin.000006 | 509 | Query          |        55 |         645 | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'                                                                  |

| mysql-bin.000006 | 645 | Anonymous_Gtid |        55 |         710 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                          |

| mysql-bin.000006 | 710 | Query          |        55 |         802 | flush privileges                                                                                              |

| mysql-bin.000006 | 802 | Stop           |        55 |         825 |    

                                                                                                           |

+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------+

9 rows in set (0.00 sec)

3. At this point, we are adding a slave which will connect and replicate using the traditional approach (MASTER_AUTO_POSITION = 0)

4. Now on both master and slave, we enable GTID mode

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: db-55.example.com

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000011

          Read_Master_Log_Pos: 16689209

               Relay_Log_File: mysqld-relay-bin.000001

                Relay_Log_Pos: 4

        Relay_Master_Log_File: mysql-bin.000011

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

Please note that the slave is now at mysql-bin.000011

5.Switch the method of replication to use GTID(MASTER_AUTO_POSITION = 1)

At this stage, we get an error in the slave

 Last_IO_Errno: 1236

                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate anonymous transaction when AUTO_POSITION = 1, at file ../binlogs/mysql-bin.000006, position 194.; the first event '' at 4, the last event read from '../binlogs/mysql-bin.000006' at 259, the last byte read from '../binlogs/mysql-bin.000006' at 259.'

               Last_SQL_Errno: 1396

               Last_SQL_Error: Error 'Operation CREATE USER failed for 'test@'127.0.0.1'' on query. Default database: ''. Query: 'CREATE USER 'test'@'127.0.0.1' IDENTIFIED WITH 'mysql_native_password' AS '*47FFA1D679CD8C02395ED76C3640A48AA3C3411C''

The reason for this error is that when we tried to switch to GTID based replication, the slave started reading all the binlogs and started with the transactions from binlogs which were already replicated using the traditional method(note that the slave was at mysql-bin.000011 before switching.

Now, how do we fix this?

First find out the UUID of the master by issuing the below command on the master which should match the master UUID in show slave status

mysql> show variables like '%uuid%';

+---------------+--------------------------------------+

| Variable_name | Value                                |

+---------------+--------------------------------------+

| server_uuid   | ab7c6739-be64-11ea-b478-080027fd2c56 |

+---------------+--------------------------------------+

1 row in set (0.01 sec)

Now, look at the show slave status

mysql> show slave status\G

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

               Slave_IO_State:

                  Master_Host: db-55.example.com

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000006

          Read_Master_Log_Pos: 194

               Relay_Log_File: mysqld-relay-bin.000003

                Relay_Log_Pos: 898

        Relay_Master_Log_File: mysql-bin.000003

             Slave_IO_Running: No

            Slave_SQL_Running: No

              Replicate_Do_DB:

          Replicate_Ignore_DB: mysql,information_schema

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 1396

                   Last_Error: Error 'Operation CREATE USER failed for 'test'@'127.0.0.1'' on query. Default database: ''. Query: 'CREATE USER 'test'@'127.0.0.1' IDENTIFIED WITH 'mysql_native_password' AS '*47FFA1D679CD8C02395ED76C3640A48AA3C3411C''

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 685

              Relay_Log_Space: 9652

              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: 1236

                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate anonymous transaction when AUTO_POSITION = 1, at file ../binlogs/mysql-bin.000006, position 194.; the first event '' at 4, the last event read from '../binlogs/mysql-bin.000006' at 259, the last byte read from '../binlogs/mysql-bin.000006' at 259.'

               Last_SQL_Errno: 1396

               Last_SQL_Error: Error 'Operation CREATE USER failed for 'test@'127.0.0.1'' on query. Default database: ''. Query: 'CREATE USER 'test'@'127.0.0.1' IDENTIFIED WITH 'mysql_native_password' AS '*47FFA1D679CD8C02395ED76C3640A48AA3C3411C''

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 55

                  Master_UUID: ab7c6739-be64-11ea-b478-080027fd2c56

             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: 200706 13:19:15

     Last_SQL_Error_Timestamp: 200706 13:19:15

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set: ab7c6739-be64-11ea-b478-080027fd2c56:1-29

            Executed_Gtid_Set: 846dc749-bf34-11ea-852d-08002749f9b4:1-29, 

ab7c6739-be64-11ea-b478-080027fd2c56:1-4:30-53010

                Auto_Position: 1

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

mysql> show master status;

+------------------+----------+--------------+--------------------------+----------------------------------------------------------------------------------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set                                                                            |

+------------------+----------+--------------+--------------------------+----------------------------------------------------------------------------------------------+

| mysql-bin.000010 |  2060774 |              | mysql,information_schema | 846dc749-bf34-11ea-852d-08002749f9b4:1-29,

ab7c6739-be64-11ea-b478-080027fd2c56:1-4:30-53010 |

+------------------+----------+--------------+--------------------------+----------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

Here 846dc749-bf34-11ea-852d-08002749f9b4:1-29 --> Last slave GTID executed

           ab7c6739-be64-11ea-b478-080027fd2c56:1-4:30-53010 --> Last GTID executed on master

In order to fix the problem, we need to mark these as purged in slave.so that the slave continues with the next set of GTIDs. On slave run the below commands.

Please note that running reset master on slave would also delete all binary log files listed in the index file, resets the binary log index file to be empty, and creates a new binary log file.

mysql> reset master;

Query OK, 0 rows affected (0.01 sec)

mysql> set global GTID_PURGED='846dc749-bf34-11ea-852d-08002749f9b4:1-29,ab7c6739-be64-11ea-b478-080027fd2c56:1-53010';

Query OK, 0 rows affected (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

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

               Slave_IO_State: Queueing master event to the relay log

                  Master_Host: db-55.example.com

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000011

          Read_Master_Log_Pos: 17944864

               Relay_Log_File: mysqld-relay-bin.000007

                Relay_Log_Pos: 781024

        Relay_Master_Log_File: mysql-bin.000011

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB: mysql,information_schema

           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: 17469779

              Relay_Log_Space: 1256570

              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: 955

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: 55

                  Master_UUID: ab7c6739-be64-11ea-b478-080027fd2c56

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: System lock

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set: ab7c6739-be64-11ea-b478-080027fd2c56:1-29:53011-56993

            Executed_Gtid_Set: 846dc749-bf34-11ea-852d-08002749f9b4:1-29,

ab7c6739-be64-11ea-b478-080027fd2c56:1-55487

                Auto_Position: 1

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

Auto Clutch - Dump That Clutch Pedal

Sensor and system switch on gear knob

I own a Maruti Wagon R 2006 model and has driven it almost 120000 KM. It was during 2012 that I came to know about the technology to get rid of your clutch pedal and the main advantage is we can free our left leg.I went for the one which is manufactured by Automate India. This one is working well so far except that I had to change the cable associated with this system ones.

One of the important questions people ask me about this system is what happens on uphill road if we have to stop in the middle and then move again. Actually the system works almost similar to Automatic cars except that we have to change gears.It has a button on the gear knob which is a sensor. When pressed the sensor will get activated which in turn will trigger the associated motor that controls the clutch.So when you want to change the gear, all you have to do is to make sure that your hand is pressing the sensor on the gear knob so that the clutch gets activated and when you remove your hands from the gear know the clutch get deactivated.The clutch also gets activated when you apply breaks and gets deactivated when you release the break.The speed with which the clutch gets activated can be adjusted using a knob supplied with the system. Now coming to the question of stoppage at uphill,once you stop the car, you may have to apply hand brakes, put into first gear and then slowly release the hand break while you accelerate. There is no option for you to idle the car in half clutch.

Clutch speed adjusting knob

Now coming to the advantages with this system

1. Your left leg is always free. Some people develop muscle/knee pain on left legs while driving long distances . The system is very good for such people as you don't need to bother about pressing the clutch anymore.

2.The system doesn't interfere with the existing manual clutch. You may switch off or switch on the system and continue to use or disregard the manual clutch anytime even while you are driving which means even if the system fails, you can continue with the manual system and drive the vehicle.

3. There is no noise associated with this system except the beep while you switch the systems.

4. Fuel economy of you vehicle can increase slightly(I have not noticed it much) in theory as the time you hold the clutch is very less when compared to manual system.

5. The vehicle does not require any modification and there is no approvals required from RTO(as on 6/21/2017)

System components inside the hood

6. The system can be ported from one vehicle to another.

7. Not much maintenance cost as compared to automatic cars.

Examining the disadvantages

1.We just have limited number of service centers.So this system drops to be a nice to have feature.as this automatic system cannot be reliable for someone who is solely depended on this for driving.

2.Changing just a cable which is just a 30 minute job costed me Rs1200/- which means that the service center is charging as per their wish and you don't have a choice.