OurSQL Episode 176: Replication Everywhere

This week we talk about replication features in MariaDB 10. Ear Candy is a gotcha about PIVOT tables in MariaDB, and At the Movies is about the viability of transactions. Percona Live DB Hangops - every other Wednesay at noon Pacific time Upcoming MySQL events Training SkySQL Trainings Tungsten University trainings Oracle's MySQL Trainings Percona Trainings MariaDB 10.0 replication Episode 174 and Episode 175, where we talked about MariaDB's CONNECT storage engine MariaDB replication knowledge base MariaDB GTIDs Episode 128, where we talked about GTIDs in MySQL Using the BINLOG_GTID_POS() function to get GTID information:

SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 |      884 |              |                  |
+--------------------+----------+--------------+------------------+

SELECT BINLOG_GTID_POS('mariadb-bin.000001', 884);
+--------------------------------------------+
| BINLOG_GTID_POS('mariadb-bin.000001', 884) |
+--------------------------------------------+
| 1-3232249957-3                             |
+--------------------------------------------+
1 row in set (0.00 sec)
Here's one way to set replication in MariaDB to use GTIDs:
SET GLOBAL gtid_slave_pos='1-3232249957-3';
CHANGE MASTER TO  MASTER_HOST='192.168.56.101', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_USE_GTID=current_pos;
SHOW SLAVE STATUS now has 2 lines at the end for GTID-related fields:
SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000001
...
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 3232249957
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: Slave_Pos
                  Gtid_IO_Pos: 1-3232249957-3
1 row in set (0.00 sec)
Episode 69, where we talkeda bout MHA MariaDB multi-source replication - that is, having more than one master. Using multi-source replication:
SHOW SLAVE "M1" STATUS;
START SLAVE "M1";
STOP SLAVE "M1";
To apply commands to all streams - note the plural SLAVES:
SHOW SLAVES STATUS;
STOP SLAVES;
START ALL SLAVES;
<code>

<A href="https://blog.mozilla.org/it/2012/06/13/make-and-use-checklists-easily/">... SLAVE UNTIL syntax for switching masters using position-based replication</A>

To catch a slave up to a certain point, even if the slave has multiple masters:
<code>START SLAVE UNTIL master_gtid_pos="1-11-100,2-21-50"
MASTER_GTID_WAIT, including a comparison to MASTER_POS_WAIT Parallel replicaiton in MariaDB MariaDB development notes for parallel replication and group commits Ear Candy The PIVOT type of the CONNECT storage engine uses TCP/IP. Episode 175, PIVOT and the MariaDB CONNECT storage engine
MariaDB [connect_test]&gt; SHOW CREATE TABLE expense_pivot\G
*************************** 1. row ***************************
       Table: expense_pivot
Create Table: CREATE TABLE `expense_pivot` (
  `who` char(5) NOT NULL,
  `week` tinyint(1) NOT NULL,
  `Beer` decimal(5,2) NOT NULL `FLAG`=1,
  `Car` decimal(5,2) NOT NULL `FLAG`=1,
  `Food` decimal(5,2) NOT NULL `FLAG`=1
) ENGINE=CONNECT DEFAULT CHARSET=utf8 `TABLE_TYPE`='pivot' `TABNAME`='expenses'
1 row in set (0.00 sec)
MariaDB [connect_test]&gt; SHOW VARIABLES LIKE 'skip_networking';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | ON    |
+-----------------+-------+
1 row in set (0.00 sec)
MariaDB [connect_test]&gt; SELECT * FROM expense_pivot;
ERROR 1296 (HY000): Got error 174 '(2003) Can't connect to MySQL server on 'localhost' (111 "Connection refused")' from CONNECT
At the movies This week in At the Movies, we present Transactions: Over used, or just misunderstood? from the Strangeloop conference. Mark Little, a VP at RedHat, provides advice on when it is not recommended to use transactions and how to use transactions with Web Services, NoSQL, REST and mobile infrastructures. Feedback Facebook group Google+ page e-mail: podcast at technocation.org voicemail using phone/Skype: +1-617-674-2369 twitter: @oursqlcast or Tweet about @oursqlcast