top of page

[MySQL 101] Configure MySQL Replication GTID Master-Master

Updated: Jun 29, 2022

After the articles of MySQL in series MySQL 101 (basic):

In this article, we will stay here to talk about how to configure MySQL Replication Master - Master using GTID.

This kind of Master-Master replication enable users to write & read concurrently to both nodes, different from Master-Slave which just allow RW on Master and Read-Only on Slave.

There is no evidence about poor data integrity of MySQL Master - Master replication, it could be good or bad; but until now this mechanism is the most popular way to have things done.


TL;DR:

  • The same configuration as Master-Slave Replication but every node is master (co-master I guess).

  • Support either binlog or GTID.

  • Still asynchronous mechanism, it is not the same synchronous as NDB cluster or Galera Cluster.

  • Pros: increase HA in system and reduce cost when failover.

  • Cons: MUST ALWAYS asure that WRITE action just occurs on 1 node at point of time. If you can NOT sure this, please consider other solution.

  • Must associate to database proxy, such as: keepalived + lvs/haproxy, proxySQL, maxScale.

Notes

The ideas of this quite simple, instead of every node is just Master or Slave, we configure a node is Master and Slave at the same time, you could set a node as Active, others are Backup. Once incident happen, just point your application database drive to backup node, there is no action needed on MySQL as in Master-Slave. When combining with Keepalived + LVS/HAprosy, the failover is fully automatic (not fully transparent).

Hence, this Master-Master mechanism is just MySQL asynchronous and there is no write-integrity assurance. Thus, we recommend you using this just on small to medium database system. With larger database (workload), you should consider MySQL synchronous solution, such as: Galera Cluster, NDB Cluster, etc.


GTID is stand for Global Transaction ID. Every single change in database system occuring on particular node will generate an ID with format <node_id>:<transaction_id>. GTID will base on that ID on node to create global transaction id to make a unique status of data.


Step 1: Enable GTID on both nodes

Add these lines to /etc/my.cnf:

gtid_mode=on
binlog_format = MIXED
enforce_gtid_consistency = ON
gtid_mode = ON
log_slave_updates = ON
relay_log_info_repository = TABLE
relay_log_recovery = 1
relay_log_purge = 1

Restart mysql & check again configured gtid_mode:

$ sudo systemctl restart mysql

$ mysql -e 'show global variables like "gtid_mode"'
+-----------------------+
| Variable_name | Value |
+-----------------------+
| gtid_mode     | ON    |
+-----------------------+

Step 2: Backup data and restore on both nodes

Check the article [MySQL 101] Backup & Restore to do backup.


To backup all databases:

$ mysqldump --all-databases > backup.sql

You could use another tool, such as: Xtrabackup.

Copy file to to other nodes and restore:

$ scp source-ip:/backup.sql ./
$ mysql -e "reset master"
$ mysql < backup.sql

Step 3: Configure GTID replication

Do these below steps on all nodes:

mysql> change master to master_host="<IP/Hostname>",
> master_port=3306,
> master_user="<replication_user>",
> master_password="<password>",
> master_auto_position=1;

mysql> start slave;

mysql> set global read_only=0;
  • <IP/Hostname>: IP or host name of source node. If you use host name, do not configure skip-name-resolve and skip-host-cache in /etc/my.cnf.

  • <replication_user>: user has granted Replication Slave on source node. This is a recommendation for security best practice, you can ignore it.

  • master_auto_position: flag to tell mysql that replication using GTID without declare binlog file and log position on source node.

Check replication configuration again with command:

$ mysql -e "show slave status\G" | grep Running

If both Slave_IO_Running and Slave_SQL_Running is set to YES, you are congratulated. If not, check mysql log to troubleshoot.


Repeat step 3 on all nodes.


1,143 views0 comments

Related Posts

See All

Comentarios

Obtuvo 0 de 5 estrellas.
Aún no hay calificaciones

Agrega una calificación
Stationary photo

Be the first to know

Subscribe to our newsletter to receive news and updates.

Thanks for submitting!

Follow us
bottom of page