Stream data from MySQL databases

This section contains information about:

  • The behavior of how Datastream handles data that's being pulled from a source MySQL database
  • The versions of MySQL database that Datastream supports
  • Known limitations for using MySQL database as a source
  • An overview of how to setup a source MySQL database so that data can be streamed from it to a destination

Behavior

This section describes the behavior of MySQL sources when you replicate data using Datastream. When you ingest data from MySQL databases, you can use binlog-based replication or global transaction identifier (GTID)-based replication. You select your CDC method when you create a stream.

Binlog-based replication

Datastream can use binary log files to keep a record of data changes in MySQL databases. The information contained in these log files is then replicated to the destination to reproduce the changes made on the source.

The key characteristics of binlog-based replication in Datastream are:

  • All databases or specific databases from a given MySQL source, as well as all tables from the databases or specific tables, can be selected.
  • All historical data is replicated.
  • All data manipulation language (DML) changes, such as inserts, updates, and deletes from the specified databases and tables, are replicated.
  • Only committed changes are replicated.

Global transaction identifier (GTID)-based replication

Datastream also supports global identifier (GTID)-based replication.

Global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on a MySQL source. This identifier is unique not only to the source on which it originated, but also across all servers in a given replication topology, as opposed to the binary log-based replication where each node in the database cluster maintains its own binlog files, with its own numbering. Maintaining separate binlog files and numbering might become an issue in the event of a failure or planned downtime, because the binlog continuity is broken and the binlog-based replication fails.

GTID-based replication supports failovers, self-managed database clusters, and continues to work irrespective of changes in the database cluster.

The key characteristics of GTID-based replication in Datastream are:

  • All databases or specific databases from a given MySQL source, as well as all tables from the databases or specific tables, can be selected.
  • All historical data is replicated.
  • All data manipulation language (DML) changes, such as inserts, updates, and deletes from the specified databases and tables, are replicated.
  • Only committed changes are replicated.
  • Seamless support for failovers.

Switch from binlog-based to GTID-based replication

If you want to update your stream and switch from binlog-based to GTID-based replication without the need to do a backfill, perform the following steps:

  1. Ensure that all requirements for GTID-based replication are satisfied. For more information, see Configure a source MySQL database.
  2. Optionally, create and run a test GTID-based stream. For more information, see Create a stream.
  3. Create a GTID-based stream. Don't start it yet.
  4. Stop application traffic to the source database.
  5. Pause the existing binlog-based stream. For more information, see Pause the stream.
  6. Wait for a few minutes to ensure that Datastream has caught up with the database. You can check this using the metrics in the Monitoring tab, on the Stream details page for your stream. The values for Data freshness and Throughput need to be 0.
  7. Start the GTID-based stream. For more information, see Start the stream.
  8. Resume traffic to the source database.

If performing a backfill isn't an issue, you can truncate your tables in BigQuery, delete the old stream, and start a new one with backfill. For more information about managing backfill, see Manage backfill for the objects of a stream.

Versions

Datastream supports the following versions of MySQL database:

  • MySQL 5.6
  • MySQL 5.7
  • MySQL 8.0

  • MySQL 8.4 (supported only for GTID-based replication)

Datastream supports the following types of MySQL database:

Best practices

This section describes recommended best practices for configuring your MySQL source for use with Datastream.

Use GTID for high availability setups

If your production MySQL source uses replicas or any other high availability configuration, use GTID-based replication.

Binlog file and position-based replication can break during a database failover because when the primary fails, the new primary has a different binlog history. In such a case, Datastream loses its position and it can't resume.

GTID assigns a unique ID to every transaction across your entire replication topology (primary and replicas). After a failover, Datastream can resume from the last GTID logged on the new primary, without needing to know the binlog file or the position.

Recommendation: for any production MySQL source with a replica or high availability configuration, using the GTID CDC method is mandatory for resilient and reliable data replication.

Properly size your read replica

If you configure Datastream to replicate from a read replica, you can encounter a double lag, which is a combination of MySQL replication lag (from primary to replica) and Datastream replication lag (from replica to destination). Read replicas are often provisioned with fewer resources (CPU, RAM, IOPS) than primaries to save costs, which can cause them to lag behind the primary during high-write periods.

Recommendation: when using a read replica as a source for Datastream, provision it with resources comparable to the primary, so that the replica can keep up with the primary's write throughput.

Increase throughput for the binlog CDC method

If you are using binlog-based replication and experience high latency due to large source write volumes generating binlog files faster than a single task can process, increase throughput by tuning the maxConcurrentCdcTasks parameter. This parameter controls the number of CDC tasks a stream runs in parallel. Increasing the value for this parameter allows Datastream to process more binlog files concurrently.

Recommendation: To determine the appropriate value for data freshness, monitor your MySQL server's binlog generation rate during peak hours. You can do this by observing the rate at which new binlog files are created and rotated in the MySQL data directory, or by using MySQL monitoring tools to track the growth of binary logs. If, for example, your source generates 10 binlog files per minute during peak times, setting maxConcurrentCdcTasks to a value like 10-15 allows Datastream to process these files in parallel, preventing a backlog.

You can increase maxConcurrentCdcTasks up to the maximum supported value of 50, provided that the load on the source database remains under control. For more information, see Stream concurrency controls.

Correctly size the max_allowed_packet parameter

The default