MariadB Galera: Attaching an asynchronous Slave using GTID

| Keine Kommentare | Keine TrackBacks

Galera the synchronous Master-Master replication is quite popular. It is used by Percona XtraDB Cluster, MariaDB Galera Cluster and even patched MySQL binaries exist. Quite often you want to add a slave to a Galera Cluster. This is going to work quite well. All you need is at least configure log_bin, log_slave_updates and server_id on the designated Masters and attach your Slave.

GTID@MariaDB


Even you can use traditional (non GTID) replication. Using non GTID replication is a hassle. As you need to search for the right offset on the new Master to attach your Slave on.

Using GTID promises to be easier. As you simply switch to the new Master and the replication framework finds the new position based on the GTiD automatically.

As a fact we have two GTID implementations

  • GTID@MySQL/Percona
  • GTID@MariaDB

There are already blogpost about attaching a Slave to a Galera Cluster not using GTID.

And even using GTID@MySQL

We are going to provide a post using GITD@MariaDB :)

We assume there is already a running Galera Cluster. Building one is already explained:

Both are quite similar :D

In opposite to the blog please use wsrep_sst_method=xtrabackup-v2. The current MariaDB release 10.0.12-MariaDB-1~trusty-wsrep-log has a bug preventing you to use wsrep_sst_method=rsync

Additional Configuration on the Galera nodes


[mysqld]
log_bin
log_slave_updates
server_id        = 1

log_bin activates the binlog, while log_slave_updates make sure to write all transactions replicated via Galera into that binlog. On didactic purpose we set the server_id on the same value on all Galera nodes.

Configuring the Slave


[mysqld]
binlog_format      = ROW
log_bin
log_slave_updates
server_id          = 2

GTID@Mariadb still lacks good operational integration. So building a slave is done not using GTID.

There is a fix for mysqldump and a patch for xtrabackup exists.

Attaching a Slave


MariaDB replicates - in opposite to MySQL - always the GTID. So we first attach the slave using mysqldump (master-data) and are going to have a running replication:

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.3.93
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysqld-bin.000002
          Read_Master_Log_Pos: 537
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 536
        Relay_Master_Log_File: mysqld-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 537
              Relay_Log_Space: 834
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
             Master_Server_Id: 1
                   Using_Gtid: No
                  Gtid_IO_Pos:

Switching to replication using GTID is quite simple:

slave> stop slave;
slave> change master to master_use_gtid=slave_pos;
slave> start slave;
slave> show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.3.93
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysqld-bin.000002
          Read_Master_Log_Pos: 873
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 694
        Relay_Master_Log_File: mysqld-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 873
              Relay_Log_Space: 992
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
        Seconds_Behind_Master: 0
             Master_Server_Id: 1
                   Using_Gtid: Slave_Pos
                  Gtid_IO_Pos: 0-1-3

Check out the last two lines:)

Failover


On failover we attach the slave to another Master.

 slave> STOP SLAVE;
 slave> CHANGE MASTER TO MASTER_HOST="$NEW_HOST";
 slave> START SLAVE;

Check Master_Host and be exited we don't need to care about Master_Log_File and Master_Log_Pos.

 slave> show slave status\G
 *************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.0.3.189
                   Master_User: replication
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: mysqld-bin.000007
           Read_Master_Log_Pos: 77357
                Relay_Log_File: mysqld-relay-bin.000002
                 Relay_Log_Pos: 46594
         Relay_Master_Log_File: mysqld-bin.000007
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
           Exec_Master_Log_Pos: 77357
               Relay_Log_Space: 46892
               Until_Condition: None
         Seconds_Behind_Master: 0
              Master_Server_Id: 1
                    Using_Gtid: Slave_Pos
                   Gtid_IO_Pos: 0-1-504

GTID promises things to be much easier. As a fact I don't like the way GTID@MariaDB works with Galera replication.

Im gonna tell why in my next blog post :)

Viel Spaß

Erkan :)

Keine TrackBacks

TrackBack-URL: http://linsenraum.de/mt/mt-tb.cgi/337

Jetzt kommentieren

Über diese Seite

Diese Seite enthält einen einen einzelnen Eintrag von erkan vom 28.07.14 14:17.

My talks about MySQL, Galera and LXC (and friends) [UPDATE] ist der vorherige Eintrag in diesem Blog.

Galera Cluster using GTID: MySQL vs. MariaDB ist der nächste Eintrag in diesem Blog.

Aktuelle Einträge finden Sie auf der Startseite, alle Einträge in den Archiven.

Seiten

Powered by Movable Type 4.23-en