MySQL Replication with Global Transaction Identifiers (GTID) hands-on

Posted on , updated on by Yannick Jaquier
MySQL

Table of contents

Preamble

My initial idea was more to test MySQL Replication switchover and failover but had a presentation of MySQL 5.6 new replication features and realized that I have never setup such environment myself. So decided to give a try and use Percona backup tool (XtraBackup) and classic mysqldump utility to create from scratch a slave instance.

Blog post has been done using MySQL 5.6.11 (binary release i.e. Linux – Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive), Oracle Enterprise Linux 6.4 64 bits and Percona XtraBackup 2.1.3. In below server1.domain.com is my master server and server2.domain.com the slave one. They are both virtual machine using non routable IP adresses.

Replication with GTID prerequisites

For better segregation I have decided to create a MySQL Replication dedicated account with minimum rights (using root would not be a good idea):

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repadmin'@'%' IDENTIFIED BY 'secure_password';
Query OK, 0 rows affected (0.04 sec)

And create a test database and table:

mysql> CREATE DATABASE replicationdb CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.03 sec)
 
mysql> use replicationdb
Database changed
 
mysql> CREATE TABLE test1(val int, descr varchar(50));
Query OK, 0 rows affected (0.23 sec)

Table I load with something like:

DELIMITER $$
 
DROP PROCEDURE IF EXISTS fill_test1;
 
CREATE PROCEDURE fill_test1()
BEGIN
  DECLARE count INT DEFAULT 1;
 
  WHILE count <= 20 DO
    INSERT INTO test1 VALUES(count,count);
    SET count=count+1;
  END WHILE;
END;
$$
 
DELIMITER ;

You can check master status and GTID position with:

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 |      191 |              |                  | 770d3753-c6e4-11e2-8e78-080027d93e15:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
 
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1730 |
| mysql-bin.000002 |       941 |
+------------------+-----------+
2 rows in set (0.00 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    |         1 |         120 | Server ver: 5.6.11-log, Binlog ver: 4                             |
| mysql-bin.000002 | 120 | Previous_gtids |         1 |         191 | 770d3753-c6e4-11e2-8e78-080027d93e15:1-6                          |
| mysql-bin.000002 | 191 | Gtid           |         1 |         239 | SET @@SESSION.GTID_NEXT= '770d3753-c6e4-11e2-8e78-080027d93e15:7' |
| mysql-bin.000002 | 239 | Query          |         1 |         307 | BEGIN                                                             |
| mysql-bin.000002 | 307 | Table_map      |         1 |         367 | table_id: 153 (replicationdb.test1)                               |
| mysql-bin.000002 | 367 | Write_rows     |         1 |         410 | table_id: 153 flags: STMT_END_F                                   |
| mysql-bin.000002 | 410 | Xid            |         1 |         441 | COMMIT /* xid=374 */                                              |
| mysql-bin.000002 | 441 | Gtid           |         1 |         489 | SET @@SESSION.GTID_NEXT= '770d3753-c6e4-11e2-8e78-080027d93e15:8' |
| mysql-bin.000002 | 489 | Query          |         1 |         557 | BEGIN                                                             |
| mysql-bin.000002 | 557 | Table_map      |         1 |         617 | table_id: 153 (replicationdb.test1)                               |
| mysql-bin.000002 | 617 | Write_rows     |         1 |         660 | table_id: 153 flags: STMT_END_F                                   |
| mysql-bin.000002 | 660 | Xid            |         1 |         691 | COMMIT /* xid=379 */                                              |
| mysql-bin.000002 | 691 | Gtid           |         1 |         739 | SET @@SESSION.GTID_NEXT= '770d3753-c6e4-11e2-8e78-080027d93e15:9' |
| mysql-bin.000002 | 739 | Query          |         1 |         807 | BEGIN                                                             |
| mysql-bin.000002 | 807 | Table_map      |         1 |         867 | table_id: 153 (replicationdb.test1)                               |
| mysql-bin.000002 | 867 | Write_rows     |         1 |         910 | table_id: 153 flags: STMT_END_F                                   |
| mysql-bin.000002 | 910 | Xid            |         1 |         941 | COMMIT /* xid=1867 */                                             |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
17 rows in set (0.00 sec)

The typical variables to setup for replication are (when using the new GTID functionality), I use non-default binlog_format = row as MySQL 5.6 has apparently been drastically improved for this most used replication format (as Oracle say). In below whether you are on server1 or server2 you must adapt server_id and report_host variables:

log-bin = /mysql/logs/mysql01/mysql-bin
server-id = 1 | 2
relay_log =  /mysql/logs/mysql01/relay-bin
binlog_format = row
gtid_mode = on
log_slave_updates = true
enforce_gtid_consistency = true
master_info_repository = table
relay_log_info_repository = table
sync_master_info = 1 # Never do this on a production server, default value = 10000
master-verify-checksum = on
slave-sql-verify-checksum = on
report_host = server1.domain.com | server2.domain.com
report_port = 3326

To ease testing I’m adding the three alias in profile of my mysql Linux account:

alias start_mysql01='cd /mysql/software/mysql01/; ./bin/mysqld_safe --defaults-file=/mysql/software/mysql01/conf/my.cnf &'
alias stop_mysql01="/mysql/software/mysql01/bin/mysqladmin --defaults-file=/mysql/software/mysql01/conf/my.cnf --user=root --password=`cat ~mysql/.root_password` shutdown"
alias mysql01='/mysql/software/mysql01/bin/mysql --defaults-file=/mysql/software/mysql01/conf/my.cnf --user=root --password=`cat ~mysql/.root_password`'

The .root_password file is in home directory of mysql Linux account:

[mysql@server1 ~]$ ll .root_password
-r-------- 1 mysql dba 16 May 31 17:11 .root_password

As a reminder I personally use the below MySQL directory naming convention:

DirectoryUsed for
/mysql/data01/mysql01Strore MyISAM and InnoDB files, dataxx directories can also be created to spread I/O
/mysql/dump/mysql01All log files (slow log, error log, general log, …)
/mysql/logs/mysql01All binary logs (log-bin, relay_log)
/mysql/software/mysql01MySQL binaries (the my.cnf file is then stored in a conf subdirectory, as well as socket and pid files)

This MySQL directories naming convention should allow you to have multiple MySQL instance running on same server (mysql01, mysql02 and so on). Please note it is slightly different from what has been nicely presented by George Trujillo in his Installing MySQL 5.1 on Solaris 10 using MOCA post. MOCA stands for MySQL Optimal Configuration Architecture (MOCA) and I like this name… 😀

To use binary edition of XtraBackup 2.1.3 I have installed openssl098e-0.9.8e-17.0.1.el6_2.2.x86_64 to correct bellow error:

xtrabackup_56: error while loading shared libraries: libssl.so.6: cannot open shared object file: No such file or directory

Using XtraBackup

In this scenario the slave server has only MySQL binaries, nothing more, we will transfer a full database backup done with:

[mysql@server1 ~]$ innobackupex --user=root --password=secure_password --defaults-file=/mysql/software/mysql01/conf/my.cnf /tmp

Remark
Doing MySQL InnoDB partial backup, means backup only few specific database(s) (–databases) or only restricted list of tables, is possible only if you use the innodb_file_per_table system parameter. Copying the partial backed up InnoDB file would overwrite the already existing one and so create inconsistency (not to say data loss)…

The important file to notice here is xtrabackup_binlog_info in backup directory:

[mysql@server1 tmp]$ cat /tmp/2013-06-03_15-30-08/xtrabackup_binlog_info
mysql-bin.000002        191             770d3753-c6e4-11e2-8e78-080027d93e15:1-6

You must then prepare it:

[mysql@server1 ~]$ innobackupex --user=root --password=secure_password --defaults-file=/tmp/2013-06-03_15-30-08/backup-my.cnf --apply-log /tmp/2013-06-03_15-30-08

Remark
Even if appealing using compress option has a constraint when preparing the backup, as clearly stated by Percona:

Before you can prepare the backup you’ll need to uncompress all the files with qpress.

The error message you get is :

xtrabackup: ########################################################
xtrabackup: # !!WARNING!!                                          #
xtrabackup: # The transaction log file is corrupted.               #
xtrabackup: # The log was not applied to the intended LSN!         #
xtrabackup: ########################################################

So I preferred not to compress it to avoid fighting in installing qpress…

Then, archive and transfer the file to your slave server (server2 in my case):

[mysql@server1 tmp]$ tar cvf backup.tar 2013-06-03_15-30-08
[mysql@server1 tmp]$ scp backup.tar server2:/tmp
[mysql@server2 tmp]$ tar xvf backup.tar

To restore it use:

[mysql@server1 ~]$ innobackupex --user=root --password=secure_password --defaults-file=/mysql/software/mysql01/conf/my.cnf --copy-back /tmp/2013-06-03_15-30-08

Remark
The target directory must be empty before proceeding or you get the following error message:

Warning: xtrabackup: ignoring option '--innodb_file_per_table' due to invalid value 'off'
Original data directory '/mysql/data01/mysql01' is not empty! at /mysql/software/percona-xtrabackup-2.1.3/bin/innobackupex line 582.

It is written in Percona documentation:

Note: The datadir must be empty; XtraBackup –copy-back option will not copy over existing files. Also it’s important to note that MySQL server needs to be shut down before restore is performed. You can’t restore to a datadir of a running mysqld instance (except when importing a partial backup).

As it is explained in Percona documentation in How to create a new (or repair a broken) GTID based slave article, you need to set gtid_purged before creating the replication:

[mysql@server2 tmp]$ cat /tmp/2013-06-03_15-30-08/xtrabackup_binlog_info
mysql-bin.000002        191             770d3753-c6e4-11e2-8e78-080027d93e15:1-6
mysql> SET GLOBAL gtid_purged="770d3753-c6e4-11e2-8e78-080027d93e15:1-6";
Query OK, 0 rows affected (0.03 sec)
 
mysql> change master to master_host='server1.domain.com', master_port=3326, master_user='repadmin', master_password='secure_password', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.14 sec)
 
mysql> SHOW WARNINGS;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                        |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                         |
| Note  | 1760 | Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives. |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

In a classical implementation (MySQL 5.5 and below) the command would have been something like:

change master to master_host='server1.domain.com', master_port=3326, master_user='repadmin', master_password='secure_password',  master_log_file='mysql-bin.000002', master_log_pos=191;

Where master log file and log position would come from (please note that your master server must be read only while you are exporting the figures otherwise you are simply not able to specify a consistent master log bin position) xtrabackup_binlog_info file that on top of GTID value also contains current master log filename and log position:

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

Remark
Using master_info_repository = table is not making things really much secure as even if the password is not hard written in master.info it is clearly written in mysql.slave_master_info table:

mysql> SELECT user_name, user_password FROM mysql.slave_master_info;
+-----------+-----------------+
| User_name | User_password   |
+-----------+-----------------+
| repadmin  | secure_password |
+-----------+-----------------+
1 row in set (0.00 sec)

Once the slave has been created start it with (I/O and SQL threads):

mysql> START SLAVE;
Query OK, 0 rows affected (0.02 sec)

Get the status of the slave with:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
.
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
.
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
.

Then if you start inserting/deleting/updating rows in replicationdb.test1 table on server1.domain.com you will see them replicated on server2.domain.com (almost instantly for me).

When changing password on master host I used flush privileges command which stopped my replication (!!):

Error 'Cannot execute statements with implicit commit inside a transaction when @@SESSION.GTID_NEXT != AUTOMATIC or @@SESSION.GTID_NEXT_LIST != NULL.' on query. Default database: ''. Query: 'flush privileges'

Apparently corrected in MySQL 5.6.12 so clearly 5.6 is not yet mature (at time of writing this post). I have tried to solve bypassing the GTID of the flush privileges command on my slave:

mysql> show slave status\G
*************************** 1. row ***************************
.
           Retrieved_Gtid_Set: 770d3753-c6e4-11e2-8e78-080027d93e15:1-9
            Executed_Gtid_Set: 770d3753-c6e4-11e2-8e78-080027d93e15:1-7,
97e23f6a-cc52-11e2-b1e1-08002776e125:1-2
                Auto_Position: 1
1 row in set (0.00 sec)

From Executed_Gtid_Set value (transactions from 1 to 7) and Retrieved_Gtid_Set value (transactions from 1 to 9) we can understand that the failed transactions number is 8 in my case. So the below procedure to inject and empty transaction with same GTID:

mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)
 
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)
 
mysql> set gtid_next='770d3753-c6e4-11e2-8e78-080027d93e15:8';
Query OK, 0 rows affected (0.00 sec)
 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set gtid_next='AUTOMATIC';
Query OK, 0 rows affected (0.00 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

Which would work for any other failed standard transactions but not for this particular statement. As it is a bug corrected in 5.6.12 I have just re-instantiated my replication starting from same test table status (easy in my case as I have only one table) and using reset master (on both nodes) and reset slave all on my slave…

Using mysqldump

In this scenario the slave server has, obviously, the MySQL binaries and you have issued the installation command to create the miniumum required:

[mysql@server2 mysql01]$ ./scripts/mysql_install_db --datadir=/mysql/data01/mysql01

To ease mysqldump usage, added in my.cnf file:

[mysqldump]
quick
max_allowed_packet = 16M
port = 3326
socket = /mysql/software/mysql01/conf/mysql.sock

Then export replicationdb database from master server using:

[mysql@server1 tmp]$ /mysql/software/mysql01/bin/mysqldump --defaults-file=/mysql/software/mysql01/conf/my.cnf --user=root --password=`cat ~mysql/.root_password` --single-transaction --databases replicationdb > backup.sql
Warning: Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

Remark:
–defaults-file must be first argument ! Or you hit a bug that I thought was corrected…

The GTID trick is here (and of course implemented in mysqldump tool):

[mysql@server1 tmp]$ grep -i gtid backup.sql
-- GTID state at the beginning of the backup
SET @@GLOBAL.GTID_PURGED='770d3753-c6e4-11e2-8e78-080027d93e15:1-8';

Then transfer the dump file to your slave server and load it with something like:

mysql> \. /tmp/backup.sql

I initially got the below error:

ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

But my gtid_executed variable is empty:

mysql> show variables like '%gtid%';
+--------------------------+------------------------------------------+
| Variable_name            | Value                                    |
+--------------------------+------------------------------------------+
| enforce_gtid_consistency | ON                                       |
| gtid_executed            |                                          |
| gtid_mode                | ON                                       |
| gtid_next                | AUTOMATIC                                |
| gtid_owned               |                                          |
| gtid_purged              | 770d3753-c6e4-11e2-8e78-080027d93e15:1-6 |
+--------------------------+------------------------------------------+
6 rows in set (0.00 sec)

The error message is confusing and a bug has already been opened. In fact gtid_purged must also be empty to be able to set it, the only solution as referenced in official documentation is to use below command:

mysql> DROP DATABASE replicationdb;
Query OK, 1 row affected (0.07 sec)
 
mysql> RESET MASTER;
Query OK, 0 rows affected (0.06 sec)
 
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.01 sec)

Once done you can reload the dump file that will complete with no error and finally setup replication:

mysql> change master to master_host='server1.domain.com', master_port=3326, master_user='repadmin', master_password='secure_password', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.16 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
 
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
.
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
.
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
.

Again with a MySQL 5.5 and below implementation you would have used something like:

change master to master_host='server1.domain.com', master_port=3326, master_user='repadmin', master_password='secure_password',  master_log_file='mysql-bin.000002', master_log_pos=1025;

Where master log file and log position would come from (please not that your master server will be read only while you are exporting the figures):

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

Semi-synchronous replication

The only thing to keep in mind is how it works in the background. In no way it is a 2 phase commit implementation and the master simply wait one slave (even if you have many) to acknowledge it has received the transaction. In other words the master does not wait one of the slave to apply the transaction. Nothing much to say on implementation, it is well described in official documentation and I have the ideal configuration. Plugins files are on disk:

[root@server1 plugin]# pwd
/mysql/software/mysql01/lib/plugin
[root@server1 plugin]# ll *semi*
-rwxr-xr-x 1 mysql dba 412130 Apr  5 15:33 semisync_master.so
-rwxr-xr-x 1 mysql dba 250302 Apr  5 15:33 semisync_slave.so

Then load them at MySQL level with:

mysql> show variables like '%dynamic%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+
1 row in set (0.00 sec)
 
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.04 sec)
 
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.03 sec)
 
mysql> show plugins;
.
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
| rpl_semi_sync_slave        | ACTIVE   | REPLICATION        | semisync_slave.so  | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
44 rows in set (0.00 sec)

This add below variables that were not present before:

mysql> show variables like 'rpl%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
| rpl_semi_sync_slave_enabled        | OFF   |
| rpl_semi_sync_slave_trace_level    | 32    |
+------------------------------------+-------+
6 rows in set (0.01 sec)

Change the below global parameters on both server (always thinking of a switchover/failover):

mysql> set global rpl_semi_sync_master_enabled=on;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global rpl_semi_sync_slave_enabled=on;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'rpl%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
| rpl_semi_sync_slave_enabled        | ON    |
| rpl_semi_sync_slave_trace_level    | 32    |
+------------------------------------+-------+
6 rows in set (0.00 sec)

Then stop/start slave io_thread to activate the new configuration:

mysql> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)
 
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)

Then usage remains unchanged, so how to check semi-synchronous replication is active. The command is shared by master and slave(s):

mysql> SHOW STATUS LIKE '%semi%';
+--------------------------------------------+--------+
| Variable_name                              | Value  |
+--------------------------------------------+--------+
| Rpl_semi_sync_master_clients               | 1      |
| Rpl_semi_sync_master_net_avg_wait_time     | 83867  |
| Rpl_semi_sync_master_net_wait_time         | 335471 |
| Rpl_semi_sync_master_net_waits             | 4      |
| Rpl_semi_sync_master_no_times              | 1      |
| Rpl_semi_sync_master_no_tx                 | 1      |
| Rpl_semi_sync_master_status                | ON     |
| Rpl_semi_sync_master_timefunc_failures     | 0      |
| Rpl_semi_sync_master_tx_avg_wait_time      | 54449  |
| Rpl_semi_sync_master_tx_wait_time          | 108898 |
| Rpl_semi_sync_master_tx_waits              | 2      |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0      |
| Rpl_semi_sync_master_wait_sessions         | 0      |
| Rpl_semi_sync_master_yes_tx                | 2      |
| Rpl_semi_sync_slave_status                 | OFF    |
+--------------------------------------------+--------+
15 rows in set (0.00 sec)

On master server you check Rpl_semi_sync_master_status, Rpl_semi_sync_master_yes_tx (number of successful acknowledgments) and Rpl_semi_sync_master_no_tx (number of unsuccessful acknowledgments). On slaves you check Rpl_semi_sync_slave_status.

To simulate a move from semi-synchronous to asynchronous you can stop your slave and issue a transaction (autocommit to ON by default) and wait for rpl_semi_sync_master_timeout milliseconds (10 seconds by default):

mysql> insert into replicationdb.test1 values(26,'26');
Query OK, 1 row affected (10.01 sec)

When restarting the slave, situation is back to normal with no action…

How to remove replication

Stop the slave first:

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.02 sec)

Reset the slave using ALL option (MySQL >= 5.6.7):

mysql> RESET SLAVE ALL;
Query OK, 0 rows affected (0.08 sec)

MySQL replication has gone:

mysql> SHOW SLAVE STATUS\G
Empty set (0.00 sec)

If you want this slave to be ready for another instantiation from master, and so setting gtid_purged, you may need to issue below statement (even if gtid_purged looks empty you may be forced to reset master):

mysql> reset master;
Query OK, 0 rows affected (0.11 sec)
 
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)

Remark:
I have also deleted the relay binary logs files to be 100% sure (relay_log_basename variable).

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

One thought on “MySQL Replication with Global Transaction Identifiers (GTID) hands-on”

  1. Pingback: Getting started with Tungsten Replicator and MySQL replication

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>