Loading...
 
[Zobrazit/Skrýt nabídky vlevo]
[Zobrazit/Skrýt nabídky vpravo]

MariaDB 10 - GTID based replication

MariaDB 10+ provides GTID mechanism: https://mariadb.com/kb/en/mariadb/global-transaction-id/

How to use it to (single)master-slave replication:

master:

  • CREATE USER 'replicator'@'31.170.176.1' IDENTIFIED BY 'xxxx300';
  • GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'31.170.176.1';
  • FLUSH PRIVILEGES;
  • setup Percona repo and install percona-xtrabackup. See more at https://www.percona.com/doc/percona-server/5.5/installation/yum_repo.html#f1
  • create full backup: innobackupex --user=root --password=yyyyuuuu /data/backup/maria/
  • get gtid info from file xtrabackup_binlog_info located inside of the full backup, e.g. 0-1-1893. Fallback variant: MariaDB (none)> SELECT BINLOG_GTID_POS('<binlog file, e.g. achiles-db-binlog.000001>', <binlog position>);
  • make sure that slave has the same ibdata settings as the master
  • make sure that server-id is correctly set for master and slave
  • copy backup to slave and place it to /var/lib/mysql/ and change permission to mysql:mysql


slave:

  • unpack backup (tar -i for stream tar) and copy files to mysql dir (/var/lib/mysql)
  • recover the backup using innobackupex --apply-log /var/lib/mysql
  • set appropriate master GTID: MariaDB (none)> SET GLOBAL gtid_slave_pos = "<gtid>";
  • MariaDB (none)> CHANGE MASTER TO master_host="<master IP>",master_user="replicator", master_password="xxxyyy",master_use_gtid=slave_pos;



NOTE:

If you change for example "gtid_domain_id" on master with connected replica, you can see mutiple gtid_current_pos like this:

MariaDB [(none)]> show variables like '%gtid%';
+------------------------+------------------+
| Variable_name          | Value            |
+------------------------+------------------+
| gtid_binlog_pos        | 0-1-115,5903-1-1 |
| gtid_binlog_state      | 0-1-115,5903-1-1 |
| gtid_current_pos       | 0-1-115,5903-1-1 |
| gtid_domain_id         | 5903             |
| gtid_ignore_duplicates | OFF              |
| gtid_seq_no            | 0                |
| gtid_slave_pos         |                  |
| gtid_strict_mode       | ON               |
| last_gtid              | 5903-1-1         |
+------------------------+------------------+
9 rows in set (0.00 sec)


You can "repair" this by invoking "RESET MASTER" command. This will break replication, if allready exist. You have to know what you are doing!!


Created by darek. Last Modification: Pondělí 05 of září, 2016 16:33:54 CEST by darek.