MySQL Replication
MySQL has built-in replication, which enables data from one MySQL database server (known as a source) to be copied to one or more MySQL database servers (known as replicas). Replication is asynchronous by default; replicas do not need to be connected permanently to receive updates from a source. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.
Advantages of replication in MySQL include:
- Scale-out solutions – spreading the load among multiple replicas to improve performance. In this environment, all writes and updates must take place on the source server. Reads, however, may take place on one or more replicas. This model can improve the performance of writes (since the source is dedicated to updates), while dramatically increasing read speed across an increasing number of replicas.
- Data security – because the replica can pause the replication process, it is possible to run backup services on the replica without corrupting the corresponding source data.
- Analytics – live data can be created on the source, while the analysis of the information can take place on the replica without affecting the performance of the source.
- Long-distance data distribution – you can use replication to create a local copy of data for a remote site to use, without permanent access to the source.
For more information, see: https://dev.mysql.com/doc/refman/8.4/en/replication.html
Replication – Binary Logs
Whenever a statement that could change data (INSERT, UPDATE, DELETE) occurs on a MySQL instance, and if binary logging is enabled, these transactions/statements are written to what’s known as a binary log. (I said “could change data”, because you could have a SQL statement that doesn’t update or delete any rows, and this statement would still be written to the binary log). So, the MySQL Binary Logs contain every data-changing event on a server. Prior to MySQL version 5.6, replication was managed using MySQL binary logs and the position of the transaction within each binary log. As of MySQL 5.6, you have an option to use Global Transaction Identifiers (GTID’s) instead. Binary logs are the foundation of replication.
When writing data to the binary logs, there are three binary log formats – statement-based, row-based, or mixed (where MySQL will determine which method is more efficient when writing the transactions to the binary log).
Before you enable replication, you must copy the data from the source server over to the replica server, so that both servers start with the same data. Then, once you turn on replication, the replica will contact the source server and request all of the database changes that has happened since the replica last contacted the source server (or since the initial data dump and load). These database changes are stored in the binary logs. The correlation between binary logging formats and the terms used during replication are:
- When using statement-based binary logging, the source writes SQL statements to the binary log. Replication of the source to the replica works by executing the SQL statements on the replica. This is called statement-based replication (which can be abbreviated as SBR), which corresponds to the MySQL statement-based binary logging format.
- When using row-based logging, the source writes events to the binary log that indicate how individual table rows are changed. Replication of the source to the replica works by copying the events representing the changes to the table rows to the replica. This is called row-based replication (which can be abbreviated as RBR). Row-based logging is the default method (and in some replication topologies, row-based is the only option).
- You can also configure MySQL to use a mix of both statement-based and row-based logging, depending on which is most appropriate for the change to be logged. This is called mixed-format logging. When using mixed-format logging, a statement-based log is used by default. Depending on certain statements, and also the storage engine being used, the log is automatically switched to row-based in particular cases. Replication using the mixed format is referred to as mixed-based replication or mixed-format replication.
The MySQL server writes these data-changing transactions to a sequentially-numbered binary log file (mysql-bin.000001, mysql-bin.000002, etc.). Each transaction is given a position in the binary log – for example, a transaction may be written to the binary log named mysql-bin.000004 and occupies position 576. The next transaction will be appended to the same log file (unless the log file is full and a new binary log has been created) and will be assigned a new position – for example, 947. The replica server will contact the source server (via the I/O thread) and say “give me all of the transactions after a certain binary log and after a certain position within that log”. The replica then copies those transactions to a relay log on the replica. A relay log is a temporary log (much like the binary log). Another thread, the SQL thread, then applies those changes from the relay log to the replica’s databases. You can also enable the binary log on a replica, and have another replica use it as a source – but we won’t discuss that in this post.
You can look inside a binary log using the mysqlbinlog utility. If I create a database named “test”, and then take a look at the binary log, I would see something like this: (the output has been truncated to only show that transaction)
# mysqlbinlog binlog.000086 ... SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/; /*!80016 SET @@session.default_table_encryption=0*//*!*/; create database test /*!*/; ...
Also, you would see the position of the binary log via a line that looks like this (with the value of the end_log_pos being different for each transaction):
#250224 16:40:14 server id 1 end_log_pos 345 CRC32 0x7c1243c6 Query thread_id=9 exec_time=0 error_code=0 Xid = 4
Since each transaction has it’s own position in the binary log, the replica can tell the source what transactions it needs in order to stay current with the source instance.
Global Transaction Identifiers (GTID’s)
GTID’s were introduced in MySQL 5.6, and it simplifies replication management and makes it more robust. A GTID is a unique identifier assigned to every transaction committed on a MySQL server. The GTID is composed of two parts – a server UUID (a unique identifier for that server) followed by a transaction sequence number. For example: the GTID of 7d73b822-75e1-11ef-a4da-4455e16762b4:553 would be composed of that server’s UUID of 7d73b822-75e1-11ef-a4da-4455e16762b4 and 553 would be a single transaction for that server. The next GTID would then be 7d73b822-75e1-11ef-a4da-4455e16762b4:554 where we simply increment the last GTID suffix of 553 by one.
You can find the UUID of a server via this command:
mysql> show variables like 'server_uuid'; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | 7d73b822-75e1-11ef-a4da-4455e16762b4 | +---------------+--------------------------------------+ 1 row in set (0.01 sec)
Since each server has their own UUID, the format of the GTID ensures that every transaction across a replication topology is globally unique, and it doesn’t matter from which server the GTID originated.
Advantages of GTID’s over binary log position
The use of GTID’s has several advantages:
- Simplified failover – if the source or primary server fails, it is easier to promote a replica to be the new primary. And in cases where you have multiple replicas, you can see which replica contains the most current set of data from the original primary server, and then promote that replica to be the new primary.
- Server consistency – since every transaction has a unique UUID, it is easier to make sure the replicas apply these transactions in the correct order and avoid duplicate transactions.
- Multi-primary support – MySQL Group Replication allows for multiple write-servers, so GTID’s help to prevent conflicts as each transaction is uniquely traceable back to the source server.
- Easier troubleshooting – debugging replication issues is easier when you can track which transactions have been retrieved from the source server and which ones have been applied to the replica server. Skipping transactions is also easier, as you don’t have to guess the next position inside a binary log – you can simply skip a single (or more) transaction by setting the next GTID to be one greater than the failed transaction. You may use the mysqlbinlog utility to search a binary log for a single (or multiple) GTID.
If you want to change the server’s UUID, which would then change the GTID, then you can simply create a new UUID by issuing this command:
mysql> select UUID(); +--------------------------------------+ | UUID() | +--------------------------------------+ | b7e2cda6-f30c-11ef-a736-5178638a8911 | +--------------------------------------+ 1 row in set (0.00 sec)
Then you will need to update/replace the UUID which is stored in the auto.cnf file inside the MySQL data directory, and reboot the MySQL instance for the new UUID to become active. Note: You can not change the UUID on a MySQL HeatWave instance.
Basic Troubleshooting Replication with GTID’s
I am not going to cover how to setup replication, but this is how you use GTID’s to troubleshoot or to see the current status of a replica server.
From the replica, you can issue the “SHOW REPLICA STATUS” command, and you should see something like this towards the end of the output:
Retrieved_Gtid_Set: 2bdf2d84-f30e-11ef-a736-5178638a8911:1-25000
Executed_Gtid_Set: 2bdf2d84-f30e-11ef-a736-5178638a8911:1-24600
The first line, Retrieved_Gtid_Set, shows the GTID’s that have been retrieved from the source MySQL instance. In this example, you have retrieved 25000 transactions. The next line, Executed_Gtid_Set, shows the number of transactions that have been executed on the replica instance. In this example, the replica has applied 24600 transactions, which means the replica is 400 transactions behind the source database. If you have an write-active source instance, then every time you issue this command on the replica, the number of retrieved GTID’s will increase – and if replication is running without errors, the number of executed GTID’s will be increasing as well.
One thing to note – if you have executed any transactions on the replica database, those transactions will also appear in the Executed_Gtid_Set as well, so those two lines might look like this:
Retrieved_Gtid_Set: 2bdf2d84-f30e-11ef-a736-5178638a8911:1-25000 Executed_Gtid_Set: cb591b9a-f30e-11ef-a736-5178638a8911:1-12, 2bdf2d84-f30e-11ef-a736-5178638a8911:1-24600
Remember – the Retrieved_Gtid_Set only contains GTID’s from the source database, while the Executed_Gtid_Set will contain GTID’s from both the source and replica instances.
Since the UUID of the replica is cb591b9a-f30e-11ef-a736-5178638a8911, then from the output above, the GTID set of cb591b9a-f30e-11ef-a736-5178638a8911:1-12 shows that 12 transactions were applied to the replica database. These GTID’s should not break replication if you copied over the data from the source database after these transactions had already been applied to the replica. One frequent replication error is when a user is created on the replica, and then later on the source. Replication will break when that source “CREATE USER” transaction attempts to be applied on the replica, as the replica already has that user in the MySQL instance.
You may also go to the source instance, and issue the SHOW PRIMARY STATUS command to see the GTID status of the source:
*************************** 1. row *************************** File: mysql-bin.000004 Position: 943210 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set:2bdf2d84-f30e-11ef-a736-5178638a8911:1-26125
The Executed_Gtid_Set for the source is 2bdf2d84-f30e-11ef-a736-5178638a8911:1-26125, which shows that 26125 transactions have been applied to the source instance.
Since the replica had only retrieved 25000 GTID’s, the replica is behind the source database by 1125 transactions.
Finally, if you are using a multi-primary replication topology, such as Group Replication, each instance may have multiple different GTID sets in the output from the SHOW REPLICA STATUS:
Retrieved_Gtid_Set: 2bdf2d84-f30e-11ef-a736-5178638a8911:1-25000, 7a509bbe-f30f-11ef-a736-5178638a8911:1-14928 Executed_Gtid_Set: cb591b9a-f30e-11ef-a736-5178638a8911:1-12, 2bdf2d84-f30e-11ef-a736-5178638a8911:1-24600, 7a509bbe-f30f-11ef-a736-5178638a8911:1-13858
Finally, when using GTID’s and replication, it is necessary to understand which GTID’s (or UUID’s) belong to which server in your replication topology. MySQL replication using GTID’s is fairly simple to setup and use. Be sure that the source and replica instances are the same size – as far as CPU, RAM and storage space is concerned. You could attempt to use a smaller shape for the replica, but if you need the application to failover to the replica, it needs to the same power and capacity as the source instance. Once you have replication up and running, and if you don’t attempt to update any databases on the replica server, replication runs very smoothly.