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 = 1Restart 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.sqlYou 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.sqlStep 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 RunningIf 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.
Comments