On Fri, May 3, 2013 at 7:45 AM, Kristian Nielsen <knielsen@knielsen-hq.org> wrote:

Maybe the deeper issue is that you would prefer a design where sequence number is assumed unique by itself (or (domain_id, sequence_number) if using multi-source). And the code is allowed to silently break if this assumption is violated by user?

This makes a lot of things simpler, of course. I did think a lot about this, and in the end I decided against it, because of numerous cases where this could make things harder for users that are perhaps not intimately aware with how GTID works. I am still hopeful that the current design can give the best of both worlds: something that "just works" in most cases for most users, and still provides what is needed for advanced users that can be expected to know what they are doing.

Now that you mentioned this I'm worried. Tell me please what will happen with MariaDB in the following situation. Let's say we have slave fully synced with master, the last GTID in binlogs (and I guess the value of @@global.gtid_pos) is 0-1-100. Now let's say there's a bug that didn't configure slave read-only, or for whatever other unwanted reason slave got disconnected from master and someone connected to slave and executed a binlogged transaction there. I guess it will be assigned GTID 0-2-101 (or it will be 1-2-1?). If someone checks value of @@global.gtid_pos at this point he will be able to notice the discrepancy between master and slave. But it seems what you are saying is if after that slave connects back to master he will be able to successfully replicate and will execute transactions 0-1-101, 0-1-102 etc. Slave will still carry information in the binlog about existence of transaction 0-2-101 but no one will be able to detect that from outside of mysql. And if later this slave becomes a master then it will push this extra transaction to all other servers. But if the binlog file with this transaction will be already purged by the time the slave becomes master then replication will be broken and no one will be able to connect to it as slave. Do I understand the situation correctly? Is this "works as intended"? Thank you, Pavel

On Fri, May 3, 2013 at 12:01 PM, Kristian Nielsen <knielsen@knielsen-hq.org> wrote:

...

slave fully synced with master, the last GTID in binlogs (and I guess the value of @@global.gtid_pos) is 0-1-100. Now let's say there's a bug that didn't configure slave read-only, or for whatever other unwanted reason slave got disconnected from master and someone connected to slave and executed a binlogged transaction there. I guess it will be assigned GTID 0-2-101 (or it will be 1-2-1?). If someone

Yes, 0-2-101.

Not related to the rest of email: are multiple replication domains generated only by setting gtid_domain_id variable? Are there situations when server can decide to put transaction into different domain by itself?

...

checks value of @@global.gtid_pos at this point he will be able to notice the discrepancy between master and slave. But it seems what you are saying is if after that slave connects back to master he will be able to successfully replicate and will execute transactions 0-1-101,

Actually, in this particular situation, you will get an error when the slave tries to reconnect. Because the last GTID executed on the slave is 0-2-101, and this is what the slave is asking the master to start from. But the master's binlog does not contain this GTID, so the master returns error to the slave.

On the other hand, if the slave was not stopped when the extra transaction was executed on the slave, replication will continue, and the slave will get in its binlog: ... 0-1-100, 0-2-101, 0-1-101, 0-1-102, ...

...

0-1-102 etc. Slave will still carry information in the binlog about existence of transaction 0-2-101

Yes, in the sense that slave has the ability to locate this transaction in its binlog.

...

but no one will be able to detect that from outside of mysql.

Well, SHOW BINLOG EVENTS will display it.

SHOW BINLOG EVENTS on all servers and then comparing them to make sure that they represent the same set of transactions... You don't seriously think that it's a good way of monitoring, do you?

...

And if later this slave becomes a master then it will push this extra transaction to all other servers.

Yes.

Probably this answer is incorrect, read below.

...

But if the binlog file with this transaction will be already purged by the time the slave becomes master then replication will be broken and no one will be able to connect to it as slave.

No. Why would there be problems to connect?

I am not sure I understand your concern here. At a guess, it sounds like your worry is that the presence of 0-2-101 in the binlog Gtid_list event will require all connecting slaves to know of this event, or they will fail to connect? That is not how it works. It is the other way around, presence of a GTID in the state of the connection slave requires that the master binlog knows of this event.

The GTIDs in the Gtid_list event are only used when searching for the right binlog to start from when a slave connects with the same domain_id and server_id. Otherwise they are ignored.

This is exactly my concern currently (wasn't initially). So let's say as you said above extra transaction was executed when slave was connected, or (more probable case if possible) when IO thread was already disconnected but SQL thread didn't catch up yet. In both cases extra transaction somehow got there in the middle of the stream, and so the last GTID is the valid one, existing on the master. So slave is able to connect to master again and everything works fine. A couple of days passed without change of the master, slave already deleted the binlog file with transaction 0-2-101. Somewhere on the transaction 0-1-10000 the server 2 becomes master... Hm... Looking at the code it seems that at this point all slaves should be able to continue to replicate from server 2 just fine, although result of transaction 0-2-101 will still exist only on server 2 and nowhere else. Which means that your answer above is incorrect (as long as slaves connecting to server 2 are at state 0-1-101 or higher). If this is true then I'm relieved and have no more concerns... Pavel

Hi Kristian, Thanks. I will have a look as soon as I have some time. I need just a piece of information: is this syntax applicable to the current binaries, or do I need to recompile after a specific revision in the code? Cheers Giuseppe On Monday, May 6, 2013 at 15:55 , Kristian Nielsen wrote:

Kristian Nielsen <knielsen@knielsen-hq.org (mailto:knielsen@knielsen-hq.org)> writes:

...

I will try to get some initial docs written up by the end of the week.

Giuseppe, here (attached) is my first stab at documentation for MariaDB GTID. Thanks for prodding me to get this done, even if it was a very gentle prodding :-)

Daniel, do you think you can get these formatted properly and included in some appropriate place in the Knowledgebase? And feel free to fix any problems you find in the text on the way, of course.

I will then make sure to keep this updated as the code progresses.

This is just a first stab, I am sure there is much that is incomplete and I will work to extend it as needed (comments welcome if anything is found missing).

- Kristian. A. MariaDB global transaction ID

From version 10.0, MariaDB supports global transaction IDs for replication.

MariaDB replication in general works as follows: On a master server, all updates to the database (DML and DDL) are written into the binary log as binlog events. A slave server connects to the master and reads the binlog events, then applies the events locally to replicate the same changes as done on the master. A server can be both a master and a slave at the same time, and it is thus possible for binlog events to replicated through multiple levels of servers.

A slave server keeps track of the position in the master's binlog of the last event applied on the slave. This allows the slave server to re-connect and resume from where it left off after replication has been temporarily stopped. It also allows to disconnect from the master server and connect to a different server to resume replication from a new master, as long as the new master has the proper binlog events, and the new master connection starts replicationg at the appropriate point in the binlogs.

Global transaction ID introduces a new event attached to each event group in the binlog. (An event group is a collection of events that are always applied as a unit. They are best thought of as a "transaction", though they also include non-transactional DML statements, as well as DDL). As an event group is replicated from master server to slave server, the global transaction ID is preserved. Since the ID is globally unique across the entire group of servers, this makes it easy to uniquely identify the same binlog events on different servers that replicate each other (this was not easily possible before MariaDB 10.0).

Using global transaction ID provides two main benefits:

1. Easy to change a slave server to connect to and replicate from a different master server.

The slave remembers the global transaction ID of the last event group applied from the old master. This makes it easy to know where to resume replication on the new master, since the global transaction IDs are know throughout the entire replication hierarchy. This is not the case when using old-style replication; in this case the slave knows only the specific file name and offset of the old master server of the last event applied. There is no simple way to guess from this the correct file name and offset on a new master.

2. The state of the slave is recorded in a crash-safe way.

The slave keeps track of its current position (the global transaction ID of the last transaction applied) in a system table mysql.rpl_slave_state. If this table is using a transactional storage engine (such as InnoDB, which is the default), then updates to the state is done in the same transaction as the updates to the data. This makes the state crash-safe; if the slave server crashes, crash recovery on restart will make sure that the recorded replication position matches what changes were actually replicated. This is not the case for old-style replication, where the state is recorded in a file relay-log.info (http://relay-log.info), which is updated independently of the actual data changes and can easily get out of sync if the slave server crashes. (This works for DML to transactional tables; non-transactional tables and DDL in general are not crash-safe in MariaDB.)

Because of these two benefits, it is generally recommended to use global transaction ID for any replication setups based on MariaDB 10.0 or later. However, old-style replication continues to work as always, so there is no pressing need to change existing setups. Global transaction ID integrates smoothly with old-style replication, and the two can be used freely together in the same replication hierarchy. There is no special configuration needed of the server to start using global transaction ID. However, it must be explicitly set for a slave server with the appropriate CHANGE MASTER option; by default old-style replication is used by a replication slave, to maintain backwards compatibility.

B. The concept of global transaction ID

A global transaction ID, or GTID for short, consists of three numbers separated with dashes '-'. For example:

0-1-10

- The first number 0 is the domain ID, which is specific for global transaction ID (more on this below). It is a 32-bit unsigned integer.

- The second number is the server ID, the same as is also used in old-style replication. It is a 32-bit unsigned integer.

- The third number is the sequence number. This is a 64-bit unsigned integer that is monotonically increasing for each new event group logged into the binlog.

The server ID is set to the server ID of the server where the event group is first logged into the binlog. The sequence number is increased on a server for every event group logged. Since server IDs must be unique for every server, this makes the (server_id, sequence_number) pair, and hence the whole GTID, globally unique.

B.1. The domain ID

When events are replicated from a master server to a slave server, the events are always logged into the slave's binlog in the same order that they were read from the master's binlog. Thus, if there is only ever a single master server receiving (non-replication) updates at a time, then the binlog order will be idential on every server in the replication hierarchy.

This consistent binlog order is used by the slave to keep track of its current position in the replication. Basically, the slave remembers the GTID of the last event group replicated from the master. When reconnecting to a master, whether the same one or a new one, it sends this GTID position to the master, and the master starts sending events from the first event after the corresponding event group.

However, if user updates are done independently on multiple servers at the same time, then in general it is not possible for binlog order to be identical across all servers. This can happen when using multi-source replication, with multi-master ring topologies, or just if manual updates are done on a slave that is replicating from active master. If the binlog order is different on the new master from the order on the old master, then it is not sufficient for the slave to keep track of a single GTID to completely record the current state.

The domain ID, the first component of the GTID, is used to handle this.

In general, the binlog is not a single ordered stream. Rather, it consists of a number of different streams, each one identified by its own domain ID. Within each stream, GTIDs always have the same order in every server binlog. However, different streams can be interleaved in different ways on different servers.

A slave server then keeps track of its replication position by recording the last GTID applied within each replication stream. When connecting to a new master, the slave can start replication from a different point in the binlog for each domain ID.

For more details on using multi-master setups and multiple domain IDs, see section "F. Using global transaction with multi-source replication and other multi-master setups".

Simple replication setups only have a single master being updated by the application at any one time. In such setups, there is only a single replication stream needed. Then domain ID can be ignored, and left as the default of 0 on all servers.

C. Using global transaction ID

In MariaDB 10.0, global transaction ID is enabled automaticall. Each event group logged to the binlog receives a GTID event, as can be seen with mysqlbinlog or SHOW BINLOG EVENTS.

The slave automatically keeps track of the GTID of the last applied event group, as can be seen from the gtid_pos variable:

SELECT @@GLOBAL.gtid_pos 0-1-1

When a slave connects to a master, it can use either global transaction ID or old-style filename/offset to decide where in the master binlogs to start replicating from. To use global transaction ID, use the master_use_gtid option of CHANGE MASTER:

CHANGE MASTER TO master_use_gtid = 1, master_host = 'my_master', ...

When the slave is then later started with START SLAVE, it will send the value of @@GLOBAL.gtid_pos to the master and start replication from the corresponding point in the master binlogs.

Even when a slave is configured to connect with the old-style binlog filename and offset (CHANGE MASTER TO master_log_file=..., master_log_pos=...), it will still keep track of the current GTID position in @@GLOBAL.gtid_pos. This means that an existing slave previously configured and running can be changed to connect with GTID (to the same or a new master) simply with:

CHANGE MASTER TO master_use_gtid = 1

The slave remembers that master_use_gtid=1 was specified and will use it also for subsequent connects, until it is explicitly changed by specifying master_log_file/pos=... or master_use_gtid=0. The current value can be seen as the field Using_Gtid of SHOW SLAVE STATUS:

SHOW SLAVE STATUS Using_Gtid: 1

The slave server internally uses the table mysql.rpl_slave_state to store the GTID position (and so preserve the value of @@GLOBAL.gtid_pos across server restarts). After upgrading a server to 10.0, it is necessary to run mysql_upgrade_db (as always) to get the table created.

In order to be crash-safe, this table must use a transactional storage engine such as InnoDB. When MariaDB is first installed (or upgraded to 10.0), the table is created using the default storage engine - which itself defaults to InnoDB. If there is a need to change the storage engine for this table (to make it transactional on a system configured with MyISAM as the default storage engine, for example), use ALTER TABLE:

ALTER TABLE mysql.rpl_slave_state ENGINE = InnoDB

The table mysql.rpl_slave_state should not be modified in any other way. In particular, do not try to update the rows in the table to change the slave's idea of the current GTID position; instead use

SET GLOBAL gtid_pos = '0-1-1'

The actual slave GTID position, and thus the value of @@GLOBAL.gtid_pos, is the result of a combination of the contents of the mysql.rpl_slave_state and the contents of the slave binlog (if any, eg. if --log-slave-updates is enabled). This allows to use the same CHANGE MASTER TO ... MASTER_USE_GTID=1 command to connect a server as slave to a new master, regardless of whether the server was acting as a slave or a master before.

D. Setting up a new slave server with global transaction ID

Setting up a new replication slave server with global transaction ID is not much different from setting up an old-style slave. The basic steps are:

1. Setup the new server and load it with the initial data.

2. Start the slave replicating from the appropriate point in the master's binlog.

D.1. Start with empty server, replicate all binary logs

The simplest way for testing purposes is probably to setup a new, empty slave server and replicate all of the master's binlogs from the start (this is usually not feasible in a realistic production setup, as the initial binlog files will probably have been purged or take too long to apply).

The slave server is installed in the normal way. By default, the GTID position for a newly installed server is empty, which makes the slave replicate from the start of the master's binlogs. But if the slave was used for other purposes before, the initial position can be explicitly set to empty first:

SET GLOBAL gtid_pos = "";

Next, point the slave to the master with CHANGE MASTER. Specify master_host etc. as usual. But instead of specifying master_log_file and master_log_pos manually, use master_use_gtid=1 to have GTID do it automatically:

CHANGE MASTER TO master_host="127.0.0.1", master_port=3310, master_user="root", master_use_gtid=1; START SLAVE;

D.2. Setting up a new slave from a backup

The normal way to set up a new replication slave is to restore a backup from an existing server (whether master or slave) as the new server, then point it to start replication from the appropriate position in the master's binlog.

It is important that the position at which replication is started corresponds exactly to the state of the data at the point in time that the backup was taken. Otherwise, the slave can end up with different data than the master because of transactions missing or duplicated.

Two common ways to take a backup are XtraBackup and mysqldump. Both of these can provide the current binlog position of the backup in a non-blocking way. Of course, if there are no writes to the server being backed up during the backup process, then a simple SHOW MASTER STATUS will give the correct position.

Once the current binlog position for the backup has been obtained, in the form of a binlog file name and offset, the corresponding GTID position can be obtained from BINLOG_GTID_POS() on the server that was backed up:

SELECT BINLOG_GTID_POS("master-bin.000001", 600);

The new slave can now be started replicating by setting the correct @@gtid_pos, issuing CHANGE MASTER to point to the master server, and starting the slave threads:

SET GLOBAL gtid_pos = "0-1-2"; CHANGE MASTER TO master_host="127.0.0.1", master_port=3310, master_user="root", master_use_gtid=1; START SLAVE;

This method is particularly useful when setting up a new slave from a backup of the master. Remember to ensure that the value of server_id for the new server is different from that of any other server (this is set in my.cnf).

If the backup was taken of an existing slave server, then it already has the correct GTID position stored in the table mysql.rpl_slave_state (provided that the backup includes this table and is consistent with changes to other tables, of course). In this case, there is no need to explicitly look up the GTID position on the old server and set it on the new slave - it will be already correctly loaded from mysql.rpl_slave_state. This however does not work if the backup was taken of the master - because then the current GTID position is contained in the binlog, not in mysql.rpl_slave_state.

D.3. Switching an existing old-style slave to use GTID.

If there is already an existing slave running using old-style binlog filename/offset position, then this can be changed to use GTID directly. This can be useful for upgrades for example, or where there are already tools to setup new slaves using old-style binlog positions.

When a slave connects to a master using old-style binlog positions, and the master supports GTID (ie. is MariaDB 10.0.2 or bigger), then the slave automatically downloads the GTID position at connect and updates it during replication. Thus, once a slave has connected to the GTID-aware master at least once, it can be switched to using GTID without any other actions needed;

STOP SLAVE; CHANGE MASTER TO master_host="127.0.0.1", master_port=3310, master_user="root", master_use_gtid=1; START SLAVE;

(A later version will probably add a way to setup the slave so that it will connect with old-style binlog file/offset the first time, and automatically switch to using GTID on subsequent connects.)

E. Changing a slave to replicate from a different master

Once replication is running with GTID (master_use_gtid=1), the slave can be pointed to a new master simply by specifying in CHANGE MASTER the new master_host (and if required master_port, master_user, and master_password):

STOP SLAVE; CHANGE MASTER TO master_host='127.0.0.1', master_port=3312; START SLAVE;

The slave has a record of the GTID of the last applied transaction from the old master, and since GTIDs are identical across all servers in a replication hierarchy, the slave will just continue from the appropriate point in the new master's binlog.

It is important to understand how this change of masters work. The binlog is an ordered stream of events (or multiple streams, one per replication domain, see section "F. Using global transaction with multi-source replication and other multi-master setups"). Events within the stream are always applied in the same order on every slave that replicates it. The MariaDB GTID relies on this ordering, so that it is sufficient to remember just a single point within the stream. Since event order is the same on every server, switching to the point of the same GTID in the binlog of another server will give the same result.

This translates into some responsibility for the user. The MariaDB GTID replication is fully asynchronous, and fully flexible in how it can be configured. This makes it possible to use it in ways where the assumption that binlog sequence is the same on all servers is violated. In such cases, when changing master, GTID will still attempt to continue at the point of current GTID in the new binlog.

The most common way that binlog sequence gets different between servers is when the user/DBA does updates directly on a slave server (and these updates are written into the slaves binlog). This results in events in the slaves binlog that are not present on the master or any other slaves. This can be avoided by setting the session variable sql_log_bin false while doing such updates, so they do not go into the binlog.

It is normally best to avoid any differences in binlogs between servers. That being said, MariaDB replication is designed for maximum flexibility, and there can be valid reasons for introducing such differences from time to time. It this case, it just needs to be understood that the GTID position is a single point in each binlog stream (one per replication domain), and how this affects the users particular setup.

Differences can also occur when two masters are active at the same time in a replication hierarchy. This happens when using a multi-master ring. But it can also occur in a simple master-slave setup, during switch to a new master, if changes on the old master is not allowed to fully replicate to all slave servers before switching master. Normally, to switch master, first writes to the old master should be stopped, then one should wait for all changes to be replicated to the new master, and only then should writes begin on the new master. Deliberately using multiple active masters is also supported, this is described in the next section.

F. Using global transaction with multi-source replication and other multi-master setups

MariaDB global transaction ID supports having multiple masters active at the same time. Typically this happens with either multi-source replication or multi-master ring setups.

In such setups, each active master must be configured with its own distinct replication domain ID, gtid_domain_id. The binlog will then in effect consists of multiple independent streams, one per active master. Within one replication domain, binlog order is always the same on every server. But two different streams can be interleaved differently in different server binlogs.

The GTID position of a given slave is then not a single GTID. Rather, it becomes the GTID of the last event group applied for each value of domain ID, in effect the position reached in each binlog stream. When the slave connects to a master, it can continue from one stream in a different binlog position than another stream. Since order within one stream is consistent across all servers, this is sufficient to always be able to continue replicationat the correct point in any new master server(s).

Domain IDs are assigned by the DBA, according to the need of the application. The default value of @@GLOBAL.gtid_domain_id is 0. This is appropriate for most replication setups, where only a single master is active at a time. The MariaDB server will never by itself introduce new domain_id values into the binlog.

When using multi-source replication, where a single slave connects to multiple masters at the same time, each such master should be configured with its own distict domain ID.

Similarly, in a multi-master ring topology, where all master in the ring are updated by the application concurrently (with some mechanism to avoid conflicts), a distict domain ID should be configured for each server (In a multi-master ring where the application is careful to only do updates on one master at a time, a single domain ID is sufficient).

Normally, a slave server should not receive direct updates (as this creates binlog differences compared to the master). Thus it does not matter what value of gtid_domain_id is set on a slave, though it may make sense to make it the same as the master (if not using multi-master) to make it easy to promote the slave as a new master. Of course, if a slave is itself an active master, as in a multi-master ring topology, the domain ID should be set according to the server's role as active master.

Note that domain ID and server ID are distinct concepts. It is possible to use a different domain ID on each server, but this is normally not desirable. It makes the current GTID position (@@global.gtid_pos) more complicated to understand and work with, and looses the concept of a single ordered binlog stream across all servers. It is recommended only to configure as many domain IDs as there are master servers actively being updated by the application at the same time.

It is not an error in itself to configure domain IDs incorrectly (for example, not configuring them at all). For example, this will be typical in an upgrade scenario where a multi-master ring using 5.5 is upgraded to 10.0. The ring will continue to work as before even though everything is configured to use the default domain ID 0. It is even possible to use GTID for replication between the servers. However, case must be taken when switching a slave to a different master. If the binlog order between the old and the new master differs, then a single GTID position to start replication from in the new master's binlog may not be sufficient.

G. New syntax for global transaction ID

G.1. CHANGE MASTER

CHANGE MASTER has a new option, master_use_gtid=[0|1]. When enabled (set to 1), the slave will connect to the master using the GTID position. When disabled, the old-style binlog filename/offset position is used to decide where to start replicating when connecting.

The value of master_use_gtid is saved across server restarts (in master.info (http://master.info)). The current value can be seen as the field Using_Gtid in the output of SHOW SLAVE STATUS.

G.2. BINLOG_GTID_POS().

The BINLOG_GTID_POS() function takes as input an old-style binlog position in the form of a file name and a file offset. It looks up the position in the current binlog, and returns a string representation of the corresponding GTID position. If the position is not found in the current binlog, NULL is returned.

H. New system variables for global transaction ID

H.1. gtid_pos

This variable is the current GTID position of a slave server.

It can be set by the user to change the current replication position. This requires all slave threads to be stopped first. Note that the position is shared among all slave connections when using multi-source replication. To set position for two masters, one using replication domain 1 and another replication domain 2, set a GTID for both domains, for example:

SET GLOBAL gtid_pos = "1-10-100,2-20-500";

The variable value is updated whenever an event group is replicated on a slave, and whenever something is logged to the binlog on the master.

Note that the value of the variable is the result of whatever event happened last, either slave replication or master binlogging, per replication domain. It is an error to set it to something that conflicts with what is in the binlog. This means that to completely reset a slave server (RESET SLAVE and delete all tables), it is also necessary to RESET MASTER before @@GLOBAL.gtid_pos can be cleared (if binlogging is enabled on the slave). This is in any case necessary to avoid incorrect binlog on the slave.

Name: gtid_pos Type: String Scope: global Privileged: yes Dynamic: yes

H.2. gtid_domain_id

This variable is used to decide which replication domain new GTIDs are logged in for a master server. See section "F. Using global transaction with multi-source replication and other multi-master setups" for details.

This variable can also be set on the session level. This is used by mysqlbinlog to preserve the domain ID of GTID events.

Name: gtid_domain_id Type: 32-bit unsigned integer Scope: global and session Privileged: yes Dynamic: yes

H.3. server_id

Server_id can be set on the session level to change which server_id value is logged in binlog events (both GTID and other events). This is used by mysqlbinlog to preserve the server ID of GTID events.

Name: server_id Type: 32-bit unsigned integer Scope: global and session Privileged: yes Dynamic: yes

H.3. gtid_seq_no

gtid_seq_no can be set on the session level to change which sequence number is logged in the following GTID event. This is used by mysqlbinlog to preserve the sequence number of GTID events.

Name: gtid_seq_no Type: 64-bit unsigned integer Scope: session only Privileged: yes Dynamic: yes