Migrate from traditional replication to GTID

In this article, we are going to take a look at migrating from traditional replication to GTID,

we will discuss how to do it completely online. First, let’s discuss some GTID related configuration options. GTID mode determines that the server usage GTIDs or not, this affects not just replication binary login as well because the binary logs will have GTIDs in them. The enforce GTID consistency option ensures that the server only allows statements that are safe to execute in GTID mode. Statements that are unsafe to execute are like create table as select, there are a few more mostly around, to the gtid_owned valuable they can monitor the GTIDs on in-flight transactions. This is very useful when they are turning GTIDs off online.

Let’s discuss some and to be exact it’s just one GTID related status variable. The ONGOING_ANONYMOUS_TRANSACTION_ COUNT is the counterpart of GTID owned but in case of migration as about the ONGOING_ANONYMOUS_TRANSACTION_COUNT, It’s called anonymous transaction because it doesn’t have an identifier which is the GTID.

All operations need to be done on one of the nodes in replication topology and eventually on all the nodes. The best practice is to do the slave first and the master but in case of a lot of operations the order doesn’t really matter as long as they are carried out on each instance of the replication topology.

In this environment, I have three virtual machines, two of them are databases and one of them is sysbench machine to generate traffic so let’s start.

Master: 192.168.66.5

Slave:  192.168.66.7

On the sysbench node let’s run the prepared command to create a sysbench database, you can just copy-paste it from below.

sysbench \
--db-driver=mysql \
--mysql-user=sbtest_user \
--mysql_password= password \
--mysql-db=sbtest \
--mysql-host=192.168.66.5 \
--mysql-port=3306 \
--tables=16 \
--table-size=10000 \
/usr/share/sysbench/oltp_read_write.lua prepare

On the sysbench node runs some workload against the master which we were running for the duration of the task.

sysbench \
--db-driver=mysql \
--mysql-user=sbtest_user \
--mysql_password=password \
--mysql-db=sbtest \
--mysql-host=192.168.66.5 \
--mysql-port=3306 \
--tables=16 \
--table-size=10000 \
--threads=4 \
--time=0 \
--events=0 \
--rate=10 \
--report-interval=1 \
/usr/share/sysbench/oltp_read_write.lua run

Let’s start MySQL client on all of the nodes, all of the database nodes. Let’s check the show process list on the master so you can see this is been running here.

mysql> show processlist;
+----+-----------------+--------------------+--------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User            | Host               | db     | Command     | Time | State                                                         | Info             |
+----+-----------------+--------------------+--------+-------------+------+---------------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost          | NULL   | Daemon      | 2350 | Waiting on empty queue                                        | NULL             |
|  8 | root            | localhost          | sbtest | Query       |    0 | starting                                                      | show processlist |
| 15 | syncstndby      | 192.168.66.7:47894 | NULL   | Binlog Dump |  156 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 17 | sbtest_user     | 192.168.66.6:38130 | sbtest | Sleep       |    0 |                                                               | NULL             |
| 18 | sbtest_user     | 192.168.66.6:38132 | sbtest | Sleep       |    1 |                                                               | NULL             |
| 19 | sbtest_user     | 192.168.66.6:38134 | sbtest | Sleep       |    0 |                                                               | NULL             |
| 20 | sbtest_user     | 192.168.66.6:38136 | sbtest | Sleep       |    0 |                                                               | NULL             |
+----+-----------------+--------------------+--------+-------------+------+---------------------------------------------------------------+------------------+
7 rows in set (0.00 sec)

Okay from now on we will work with the salve first and the master.

So first we’ll set enforce_gtid_consistency = ‘warn’ on slave, to slave master.

Slave 192.168.66.7

set global enforce_gtid_consistency = 'warn';

Master 192.168.66.5

set global enforce_gtid_consistency = 'warn';

we are done here and we will check the MySQL error see error log this should be fine; you won’t see any error in the error log. The next steps is to run set global enforce_gtid_consistency=’on’ everywhere and then check the arrow again.

Slave 192.168.66.7

set global enforce_gtid_consistency='on';

Master 192.168.66.5

set global enforce_gtid_consistency='on';

So, the next step is setting global gtid_mode=’off_permissive’; so again I will start MySQL client and set it. And then we check the error log

Slave 192.168.66.7

set global gtid_mode='off_permissive';

Master  192.168.66.5

set global gtid_mode='off_permissive';

On each server we will set the gtid_mode=’on_permissive’; so it so that we generate GTID transactions at this point.

Slave 192.168.66.7

set global gtid_mode='on_permissive';

Master  192.168.66.5

set global gtid_mode='on_permissive';

So, it’s set everywhere. Let’s check the error logs

All right so now we will check if any of the nodes have ongoing anonymous transactions because if it has then when we set gtid_mode=’on’, the server no longer accepts anonymous transactions and we will get errors.

Slave 192.168.66.7

mysql> show status like 'ongoing_anonymous_transaction_count';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0     |
+-------------------------------------+-------+
1 row in set (0.22 sec)

Master 192.168.66.5

mysql> show status like 'ongoing_anonymous_transaction_count';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0     |
+-------------------------------------+-------+
1 row in set (0.04 sec)

So both servers don’t have which means that we are ready to turn on GTIDs. I will enable gtid_mode =on.

Master 192.168.66.5

mysql> set global gtid_mode='on';

Query OK, 0 rows affected (0.03 sec)

Slave 192.168.66.7

mysql> set global gtid_mode='on';

Query OK, 0 rows affected (0.03 sec)

Now on the slaves, we need to reinitialize the replication to use master-auto-position=1

Slave 192.168.66.7

stop slave;
change master to master_auto_position=1;
start slave;

So, what this does, this “change master” here, if the salve thread was already configured then if some parameter is not touched by the change master command, it will just be left at the current value.

Let’s check show slave status on the slaves and show master status on the master. everything I should be running fine.

mysql> show salve status\G;

mysql> show master status;

Now the migration is done:

As we know no upgrade is successful if you don’t have a rollback plan, to roll back please click on the below link to read the next article.

Roll Back to Traditional Replication From GTID

 411 views last month,  12 views today

About Mughees Ahmed

Over 2-year experience of Administration in Oracle, SQL Server, and MySQL databases using various tools and technologies. Keen on learning new database technologies having very good analytical skills. Working knowledge of Red Hat Linux, UNIX, Solaris and Windows Server 2012 is a valuable addition to my knowledge desk. KNOWLEDGE & SKILLS • Oracle Database Server 10g/11g/12c. • Oracle RAC, Data guard. • Oracle Golden Gate (Oracle to Oracle, Oracle to MySQL and MySQL to Oracle) • Oracle Enterprise Manager Grid Control, Toad, SQL developer. • SQL Server 2005/2008/2012/2016. • SQL Server Failover clustering, mirroring & log shipping, Always On availability groups. • MySQL 5 Administration, MySQL Workbench, MySQL Enterprise Monitor, SQLyog • MySQL NDB Cluster Installation,Administration. • MySQL Asynchronous/Semi-synchronous replication. • Oracle OEM Contact me on mughees52@gmail.com

Check Also

blank

Roll Back to Traditional Replication From GTID

If you want to roll back to old : # Now, we are going to …

Leave a Reply