Configurar una base de datos MySQL autogestionada para CDC
Organízate con las colecciones
Guarda y clasifica el contenido según tus preferencias.
En esta página se describe cómo configurar la captura de datos de cambios (CDC) para transmitir datos desde una base de datos MySQL autogestionada a un destino admitido, como BigQuery o Cloud Storage.
Estas instrucciones también funcionan con bases de datos como MariaDB, Percona Server para MySQL o Alibaba Cloud PolarDB.
Verificar la configuración y el periodo de conservación del registro binario
Confirma que el registro binario esté configurado correctamente introduciendo el siguiente comando de MySQL:
SHOWGLOBALVARIABLESLIKE'%binlog_format%';
Verifica que la variable binlog_format tenga asignado el valor ROW.
Datastream no admite formatos de registro binario MIXED ni STATEMENT.
Confirma que el formato de fila del registro binario se ha definido como FULL introduciendo el siguiente comando de MySQL:
SHOWGLOBALVARIABLESLIKE'binlog_row_image';
Verifica que la opción de actualización de réplica del registro binario esté definida como ON introduciendo el siguiente comando de MySQL:
SHOWGLOBALVARIABLESLIKE'log_replica_updates';
En las versiones de MySQL anteriores a la 8.0.26, introduce el siguiente comando:
SHOWGLOBALVARIABLESLIKE'log_slave_updates';
Para comprobar que el periodo de retención del registro binario se ha definido en 604800 segundos (7 días), introduce el siguiente comando de MySQL:
[[["Es fácil de entender","easyToUnderstand","thumb-up"],["Me ofreció una solución al problema","solvedMyProblem","thumb-up"],["Otro","otherUp","thumb-up"]],[["Es difícil de entender","hardToUnderstand","thumb-down"],["La información o el código de muestra no son correctos","incorrectInformationOrSampleCode","thumb-down"],["Me faltan las muestras o la información que necesito","missingTheInformationSamplesINeed","thumb-down"],["Problema de traducción","translationIssue","thumb-down"],["Otro","otherDown","thumb-down"]],["Última actualización: 2025-09-10 (UTC)."],[[["\u003cp\u003eThis guide provides instructions for configuring Change Data Capture (CDC) for self-managed MySQL databases, including MariaDB, Percona Server, and Alibaba Cloud PolarDB.\u003c/p\u003e\n"],["\u003cp\u003eThe binary log must be configured with \u003ccode\u003eROW\u003c/code\u003e format, \u003ccode\u003eFULL\u003c/code\u003e row image, and replica updates enabled, using specific MySQL commands to verify these settings.\u003c/p\u003e\n"],["\u003cp\u003eThe binary log retention period must be set to 604800 seconds (7 days), and instructions are given to modify the \u003ccode\u003emy.cnf\u003c/code\u003e file to ensure this and other relevant settings.\u003c/p\u003e\n"],["\u003cp\u003eA Datastream user needs to be created and granted specific privileges, such as \u003ccode\u003eREPLICATION SLAVE\u003c/code\u003e, \u003ccode\u003eSELECT\u003c/code\u003e, and \u003ccode\u003eREPLICATION CLIENT\u003c/code\u003e, for CDC operations.\u003c/p\u003e\n"],["\u003cp\u003eSpecific commands are required to confirm and setup binlogs, as well as creating the Datastream user.\u003c/p\u003e\n"]]],[],null,["# Configure a self-managed MySQL database\n\nThe following sections cover how to configure CDC for a self-managed MySQL database.\nThese instructions also work for databases such as MariaDB, Percona Server for\nMySQL or Alibaba Cloud PolarDB.\n\nVerify the configuration and retention period of the binary log\n---------------------------------------------------------------\n\n1. Confirm that the binary log is configured correctly by entering the following MySQL command:\n\n SHOW GLOBAL VARIABLES LIKE '%binlog_format%';\n\n2. Verify that the value for the `binlog_format` variable is set to `ROW`.\n Datastream doesn't support `MIXED` or `STATEMENT` binary log\n formats.\n\n3. Confirm that the row format for the binary log is set to `FULL` by entering\n the following MySQL command:\n\n SHOW GLOBAL VARIABLES LIKE 'binlog_row_image';\n\n4. Verify that the replica updates option for the binary log is set to `ON` by\n entering the following MySQL command:\n\n SHOW GLOBAL VARIABLES LIKE 'log_replica_updates';\n\n For MySQL versions earlier than 8.0.26, enter the following command instead: \n\n SHOW GLOBAL VARIABLES LIKE 'log_slave_updates';\n\n5. Verify that the retention period of the binary log is set to `604800`\n seconds (`7` days) by entering the following MySQL command:\n\n SHOW GLOBAL VARIABLES LIKE 'binlog_expire_logs_seconds';\n\n For MySQL versions earlier than 8.0.3, enter the following command instead: \n\n SHOW GLOBAL VARIABLES LIKE 'expire_logs_days';\n\n | If your MySQL database isn't set to full row-based replication and a 7-day retention period, then you'll need to change the configuration of the binary log, as follows:\n | 1. Navigate to the `/etc/mysql/` directory.\n | 2. Using an editor, open the `my.cnf` file.\n | 3. Add the following lines to the file: \n |\n | ```bash\n | [mysqld]\n | log-bin=mysql-bin\n | server-id=1\n | binlog_format=ROW\n | log-replica-updates=1\n | # For MySQL versions earlier than 8.0.26, use log-slave-updates=true\n | # instead.\n | binlog_expire_logs_seconds=604800\n | # For MySQL versions earlier than 8.0.3, use expire_logs_days=7 instead.\n | max_allowed_packet=1G\n | # '1G' is the recommended value.\n | \n | ```\n | 4. For GTID-based replication, also add the following lines: \n |\n | ```\n | [mysqld]\n | gtid_mode=ON\n | enforce-gtid-consistency=ON\n | \n | ```\n | 5. Save your changes to the `my.cnf` file, and then close the file.\n | 6. Restart your MySQL server so that the changes you made can take effect.\n\nCreate a Datastream user\n------------------------\n\n1. To create a Datastream user, enter the following MySQL commands:\n\n ```sql\n CREATE USER 'datastream'@'%' IDENTIFIED BY '\u003cvar translate=\"no\"\u003eYOUR_PASSWORD\u003c/var\u003e';\n GRANT REPLICATION SLAVE, SELECT, REPLICATION CLIENT ON *.* TO 'datastream'@'%';\n FLUSH PRIVILEGES;\n ```\n\nWhat's next\n-----------\n\n- Learn more about how Datastream works with [MySQL sources](/datastream/docs/sources-mysql)."]]
